Refactor Business Rules

[melton-jason]

Priorities

• (Part of) Rewrite Business rules, resourceApi.js and collectionApi.js to TypeScript #2621
• The backend has many uniqueness rules the front end does not respect
  • Ideally, the backend and frontend would look in the same place for business rules
• Allow dismissing resource save business rule errors #2869
• Make sure tests are up to date/create new tests when necessary

After the Priorities have been addressed, any of the following issues (in no particular order) should be considered for fixing, organized by Frontend/Backend/Both:

Frontend

• Gene sequence not counting Comp, total residues and ambiguous #436

Backend

• Repository Agreement business rule trickiness #133
• Both name and title should be unique for tree def items #2325
• Attempting to delete an attachment record that has attachmentlocation = null throws an exception. #775
• Taxon.isAccepted should be set to true if the taxon is preferred. #1054
• WB Validation exception: get() returned more than one Picklist -- it returned 2 #1064
• Attachment business rules can cause exception on resource deletion, if attachment server is not configured. #126

Both

• BusinessRuleException when deleting and re-adding a collector with the same agent #2980
• IntegrityError thrown, blaming collPrepUniqueId when adding a duplicate barcode #2886
• Add business rule for user name uniqueness. #2260

[maxpatiiuk]

All very important things. Thanks for doing this!
If you have any questions don't hesitate

[melton-jason]

In addition to refactoring, there may be many Uniqueness Rules enforced at a database level but not enforced in Specify.
When a value in the field violates such a rule, it throws a SQLAlchemy Integrity Error.

Here are the rules I have identified thus far:

• Preparation.barCode
• CollectionObject.UniqueIdentifier
• CollectingEvent.UniqueIdentifier

The majority of these would be very easy to add as uniqueness rules, would prevent data loss from error dialogs, and give instant feed back when the field is changed that the value is violating a uniqueness rule.

The hard part is scouring through the database indices to find the unique fields.

[maxpatiiuk]

Run grep UNIQUE _blank.sql on specify database dump:

blank.sql_:/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
blank.sql_:  UNIQUE KEY `Role` (`Role`,`AgentID`,`AccessionID`),
blank.sql_:  UNIQUE KEY `AgentID` (`AgentID`,`OrderNumber`),
blank.sql_:  UNIQUE KEY `AppraisalNumber` (`AppraisalNumber`),
blank.sql_:  UNIQUE KEY `ReferenceWorkID` (`ReferenceWorkID`,`AgentID`),
blank.sql_:  UNIQUE KEY `Role` (`Role`,`AgentID`,`BorrowID`),
blank.sql_:  UNIQUE KEY `DisciplineID` (`DisciplineID`,`UniqueIdentifier`),
blank.sql_:  UNIQUE KEY `CollectionID` (`CollectionID`,`UniqueIdentifier`),
blank.sql_:  UNIQUE KEY `CollectionID_2` (`CollectionID`,`CatalogNumber`),
blank.sql_:  UNIQUE KEY `AgentID` (`AgentID`,`CollectingEventID`),
blank.sql_:  UNIQUE KEY `Role` (`Role`,`AgentID`,`DeaccessionID`),
blank.sql_:  UNIQUE KEY `ReferenceWorkID` (`ReferenceWorkID`,`DeterminationID`),
blank.sql_:  UNIQUE KEY `Role` (`Role`,`AgentID`,`DisposalID`),
blank.sql_:  UNIQUE KEY `AgentID` (`AgentID`,`DNASequenceID`),
blank.sql_:  UNIQUE KEY `AgentID` (`AgentID`,`CollectingTripID`),
blank.sql_:  UNIQUE KEY `Role` (`Role`,`GiftID`,`AgentID`),
blank.sql_:  UNIQUE KEY `OrderNumber` (`OrderNumber`,`GroupID`),
blank.sql_:  UNIQUE KEY `Role` (`Role`,`LoanID`,`AgentID`),
blank.sql_:  UNIQUE KEY `DisciplineID` (`DisciplineID`,`UniqueIdentifier`),
blank.sql_:  UNIQUE KEY `ReferenceWorkID` (`ReferenceWorkID`,`LocalityID`),
blank.sql_:  UNIQUE KEY `AgentID` (`AgentID`,`DNASequenceID`),
blank.sql_:  UNIQUE KEY `CollectionMemberID` (`CollectionMemberID`,`BarCode`),
blank.sql_:  UNIQUE KEY `Name` (`Name`),
blank.sql_:/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

[maxpatiiuk]

not as helpful without table names, but you can probably open a database dump in an editor and search for UNIQUE

also, maybe there is a way to detect in Python whether a database field is unique?
Also, I belive the schema XML may have indication of what fields are unique - if it does, using that would be the best solution (as it would update automatically as new fields are added to schema)

[melton-jason]

I have a SQL script which lists the table names index names, and columns on which to enforce uniqueness, so the hard part is mostly done.

Yes, there is probably a way in Python to detect unique/indexed fields. The best place to look for this might be in stored_queries/build_models.py as that heavily uses SQLAlchemy to build the models on top of the Django models built from the schema xml.

The schema XML does have an attribute for each field stating whether it is unique or not, but I don't think it provides information on what the field should be unique in. Does it?

[melton-jason]

Here are a list of all Unique keys in the Specify7 database:

(Note that there may be some discrepancies between databases, as discovered in #2886 (comment))

TableName	IndexName	Unique Columns
accessionagent	Role	Role, AgentID, AccessionID
agentspecialty	AgentID	AgentID, OrderNumber
appraisal	AppraisalNumber	AppraisalNumber
author	ReferenceWorkID	ReferenceWorkID, AgentID
auth_group	name	name
auth_group_permissions	auth_group_permissions_group_id_permission_id_0cd325b0_uniq	group_id, permission_id
auth_permission	auth_permission_content_type_id_codename_01ab375a_uniq	content_type_id, codename
borrowagent	Role	Role, AgentID, BorrowID
collectingevent	DisciplineID	DisciplineID, UniqueIdentifier
collectionobject	CollectionID	CollectionID, UniqueIdentifier
collectionobject	CollectionID_2	CollectionID, CatalogNumber
collector	AgentID	AgentID, CollectingEventID
deaccessionagent	Role	Role, AgentID, DeaccessionID
determinationcitation	ReferenceWorkID	ReferenceWorkID, DeterminationID
determiner	AgentID	AgentID, DeterminationID
disposalagent	Role	Role, AgentID, DisposalID
django_content_type	django_content_type_app_label_model_76bd3d3b_uniq	app_label, model
extractor	AgentID	AgentID, DNASequenceID
fundingagent	AgentID	AgentID, CollectingTripID
giftagent	Role	Role, GiftID, AgentID
groupperson	OrderNumber	OrderNumber, GroupID
loanagent	Role	Role, LoanID, AgentID
locality	DisciplineID	DisciplineID, UniqueIdentifier
localitycitation	ReferenceWorkID	ReferenceWorkID, LocalityID
pcrperson	AgentID	AgentID, DNASequenceID
preparation	CollectionMemberID	CollectionMemberID, BarCode
specifyuser	Name	Name

Here is the SQL script:

select stat.table_name,
       stat.index_name,
       group_concat(stat.column_name
       order by stat.seq_in_index separator ', ') as columns
from information_schema.statistics stat
join information_schema.table_constraints tco
       on stat.table_schema = tco.table_schema
       and stat.table_name = tco.table_name
       and stat.index_name = tco.constraint_name
where stat.non_unique = 0
       and stat.table_schema = 'specify'
       and tco.constraint_type = 'UNIQUE'
group by stat.table_schema,
       stat.table_name,
       stat.index_name,
       tco.constraint_type
order by stat.table_name;