[Guided Setup] - Duplicate pick lists (with the same name) are being created upon database creation

[grantfitzsimmons]

This is primarily a Specify 6 issue, but I am creating this in the Specify 7 repository to bring attention to this report.

Reported By: Matthew Cruz at The University of Michigan

Aug 30, 2023:

Potential opportunity to clean up Herbarium Pickist for TypeStatus.

Details:

OOTB S6.8.03 + v7.8.13
Botany Discipline
Picklists

1. TypeStatus - 38 items
2. TypeStatus - 6 items

Determinations.typeStatusName uses the TypeStatus picklist with 6 items.

S7 does not even show the 38-item version in the list so I am not sure how someone could even use it anymore. I wonder though if it is still present in the OOTB configs because it needs to be present for legacy usage - maybe older collections refer to the 38-item picklist?

This could explain a huge number of the issues we've had with #1064. I'm so glad Matthew brought this to our attention!

See here:
Instance 1:
In the config/botany/picklist.xml:
https://github.com/specify/specify6/blob/945d28f00288d2d64979848fb2fb63fda34bdd1b/config/botany/picklist.xml#L13-L22

Instance 2:
In the config/common/picklist.xml:
https://github.com/specify/specify6/blob/945d28f00288d2d64979848fb2fb63fda34bdd1b/config/common/picklist.xml#L166-L207

This leads to default pick lists being duplicated in the database upon first configuration. 🤯

Running queries on a number of databases verify that this picklist in particular is duplicated quite a bit.

Some more background for developers: These XML files are used to create the picklists for each discipline. The common picklist.xml defines the shared pick lists created when any discipline is made, the discipline-specific picklist.xml creates discipline-specific ones. When there is an overlap in named picklists between these two, duplication occurs.

[acwhite211]

Hey, I've been investigating picklist duplicates in a few databases.

Here is the query I'm using to identify picklist duplicates

select c.CollectionName, pl.Name, pl.TableName, count(*) as pl_count
from picklist pl
join collection c on c.UserGroupScopeId = pl.CollectionID
group by pl.Name, pl.TableName, c.CollectionName
having count(*) > 1
order by pl_count desc;

CollectionName	Name	TableName	pl_count
DNA & Tissue Test	TypeStatus		2
Chelicerata and other Arthropoda Test	TypeStatus		2
Botany (recent) New Coll	TypeStatus		2
Mollusca and Crustacea Test	TypeStatus		2
Hymenoptera and Neuropterida	TypeStatus		2
Lepidoptera	TypeStatus		2
Invertebrate Zoology Lepidoptera New Coll	TypeStatus		2
Invertebrate Zoology Orthoptera New Coll	TypeStatus		2
Invertebrate Zoology Coleoptera New Coll	TypeStatus		2
Invertebrate Zoology Trichoptera New Coll	TypeStatus		2
Invertebrate Zoology Hymenoptera New Coll	TypeStatus		2
Marine invertebrates test	TypeStatus		2
Historical context	TypeStatus		2
Herbar	TypeStatus		2

Here is the query with picklistitem

select c.CollectionName, pli.Title, pl.Name, pl.TableName, count(*) as pli_count
from picklistitem pli
join picklist pl on pl.PickListID = pli.PickListID
join collection c on c.UserGroupScopeId = pl.CollectionID
group by pli.Title, pl.Name, pl.TableName, c.CollectionName
having count(*) > 1
order by pli_count desc;

Here are the results when we filter for just botany:

CollectionName	Title	Name	TableName	pli_count
Botany (recent) New Coll	Type	TypeStatus		2
Botany (recent) New Coll	Paratype	TypeStatus		2
Botany (recent) New Coll	Cotype	TypeStatus		2
Botany (recent) New Coll	Syntype	TypeStatus		2
Botany (recent) New Coll	Holotype	TypeStatus		2

Let me know if using the pl.Name, pl.TableName, c.CollectionName fields is how we want to identify duplicates for picklist, or if there are some other fields we should group by as well? @grantfitzsimmons

The picklist.xml config files that were in Specify 6 are not in the Specify 7 config directory, so the duplicate picklist definitions in separate xml files seems isolated to Specify 6.
https://github.com/search?q=repo%3Aspecify%2Fspecify7%20picklist.xml&type=code
https://github.com/search?q=repo%3Aspecify%2Fspecify7+picklist+language%3AXML&type=code&l=XML

Do we want to try to remove existing duplicate picklist records? We would need to be careful to make sure that we only delete duplicate picklist that are used/referenced anywhere.

If we want to prevent this picklist duplicate issue when users create new disciplines in Specify 6, we would need to edit the xml config files and push a new release of Specify 6. Probably don't want to do that, so we would be need remove any duplicates that are causing issues.