Better Project metadata for querying and curation

[mkoo]

Right now we have no way of knowing who created or last edited a Project, which is important for a variety of reasons. One is end of year reporting on collection activity.

I would even like to have some indicator of the primary collection a Project is associated with (e.g., MVZ Birds, MSB Mamm, etc) This could be included in the title of course as a best practice (e.g., we start project titles with "MVZ Archives:" ) but that is cumbersome, denormalizing and frustrating.

We should discuss if we need other curatorial controls on who can edit as well such as toggles like on locality (accepted, verified)

Once decisions are made on how, this would allow searches on Projects to be narrowed or filterd on collections which would facilitate queries.

Related to #2567

[dustymc]

who created or last edited a Project,

Seems reasonable.

One is end of year reporting on collection activity.

I have NO idea how this is related. CollectionA gets a loan request, makes a project, eventually CollectionZ sends the same person samples for the same reasons, they should just use the same project. CollectionZ's stats depend on usage, not who created the project.

some indicator of the primary collection a Project is associated with

Count the linked data? If there are none, the project does nothing anyway (and I'll eventually argue for automated deletion of those - I can use created data too!)

Ugh...

[campmlc]

Suggest adding "project created by" to project agents
Also: "project institution"

[campmlc]

"project created by"
"project last edited by"
?

[dustymc]

who created or last edited a Project,

NOT metadata pulled from environment; these will be new values in http://arctos.database.museum/info/ctDocumentation.cfm?table=CTPROJECT_AGENT_ROLE

[dustymc]

from #2541 (comment)

AWG suggestion

• make 100-character description hard requirement
• autopopulate created by, last edited by - "require" them in the forms to the extent possible
• eventually rebuild UI to sorta-maybe-hide admin agent roles

[dustymc]

Documentation: discourage HTML in project title.

[dustymc]

See also #2190 - it's big enough to not be merged here, but should be considered in the UI rebuild this is likely to require

Edit: ditto #2191

[dustymc]

todo:

• list of projects that have <100 characters in description; these MUST be padded out before implementing rule
• list of projects that have no linked transactions - suggest review for delete

[Jegelewicz]

these MUST be padded out before implementing rule

I suggest we give everyone a time frame for padding them intentionally, after which we add some text that purposefully pads them in a way that lets us find them:

This project was created with a description that included less than 100 characters. To remove this text, increase the project description to greater than 100 characters.

Adds greater than 100 characters to any project description.

[campmlc]

Agree with adding required text.

…

On Thu, Apr 2, 2020 at 1:31 PM Teresa Mayfield-Meyer < ***@***.***> wrote: * UNM-IT Warning:* This message was sent from outside of the LoboMail system. Do not click on links or open attachments unless you are sure the content is safe. (2.3) these MUST be padded out before implementing rule I suggest we give everyone a time frame for padding them intentionally, after which we add some text that purposefully pads them in a way that lets us find them: This project was created with a description that included less than 100 characters. To remove this text, increase the project description to greater than 100 characters. Adds greater than 100 characters to any project description. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2570 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADQ7JBEP6SBMHMFJTROICFDRKTR2BANCNFSM4LWWFP7A> .

[dustymc]

Here's a list of projects with a short description and/or no associated transactions. "Tooshort" projects will be subject to #2570 (comment). "Doesnothing" projects will be discussed later; DLM recommends automatic deletion after some reasonable period of time (2 years???).

temp_funky_project.csv.zip

code

drop table temp_funky_project;

create table temp_funky_project as select project_name,PROJECT_DESCRIPTION, project_id, 'https://arctos.database.museum/project/' || project_id as project_link,'tooshort' as problem from project where length(PROJECT_DESCRIPTION) <= 100;

alter table temp_funky_project modify problem varchar2(222);

insert into temp_funky_project (
	project_name, 
	PROJECT_DESCRIPTION,
	project_id, 
	project_link, 
	problem 
) (
	select 
		project_name, 
		PROJECT_DESCRIPTION,
		project_id, 
		'https://arctos.database.museum/project/' || project_id ,
		'doesnothing' 
	from 
		project 
	where 
		-- no transactions
		project_id not in (select project_id from project_trans) and 
		-- exclude the stuff we already have
		length(PROJECT_DESCRIPTION) > 100
);

alter table temp_funky_project add loan_collections varchar2(4000);
alter table temp_funky_project add accn_collections varchar2(4000);
alter table temp_funky_project add annotation_count varchar2(4000);
alter table temp_funky_project add doi_count varchar2(4000);
alter table temp_funky_project add tax_count varchar2(4000);
alter table temp_funky_project add agents varchar2(4000);

declare
	lc varchar2(4000);
	ac varchar2(4000);
	c number;
	s varchar2(20);
	anoct number;
	dc number;
	tc number;
	ag varchar2(4000);
begin
	for r in (select * from temp_funky_project ) loop
		lc:=null;
		s:=null;
		for x in (
			select distinct 
				guid_prefix 
			from 
				collection, 
				trans, 
				loan, 
				project_trans
			where
				collection.collection_id=trans.collection_id and
				trans.transaction_id=loan.transaction_id and
				loan.transaction_id=project_trans.transaction_id and
				project_trans.project_id=r.project_id
		) loop
			lc:=lc||s||x.guid_prefix;
			s:='; ';
		end loop;

		ac:=null;
		s:=null;
		for x in (
			select distinct 
				guid_prefix 
			from 
				collection, 
				trans, 
				accn, 
				project_trans
			where
				collection.collection_id=trans.collection_id and
				trans.transaction_id=accn.transaction_id and
				accn.transaction_id=project_trans.transaction_id and
				project_trans.project_id=r.project_id
		) loop
			ac:=ac||s||x.guid_prefix;
			s:='; ';
		end loop;

		select count(*) into anoct from annotations where project_id=r.project_id;
		select count(*) into dc from doi where project_id=r.project_id;
		select count(*) into tc from project_taxonomy where project_id=r.project_id;

		ag:=null;
		s:=null;
		for x in (select getPreferredAgentName(agent_id) pn from PROJECT_AGENT where project_id=r.project_id) loop
			ag:=ag||s||x.pn;
			s:='; ';
		end loop;

		update temp_funky_project set
			loan_collections=lc,
			accn_collections=ac,
			annotation_count=anoct,
			doi_count=dc,
			tax_count=tc,
			agents=ag
		 where project_id=r.project_id;

	end loop;
end;
/

[dustymc]

Add to search: uses stuff from institution

[dustymc]

AWG says go

[dustymc]

Entered/edited in next release.