Enforce scope properly for attachment and other tables

[grantfitzsimmons]

https://github.com/specify/specify6/blame/8c853f91b76ff7633c8318aff5008e1822ca10a0/src/edu/ku/brc/specify/datamodel/Attachment.java

This is a mess, collectionmemberid integer fields are not the way to go

[maxpatiiuk]

Can confirm it's a giant mess.
TLDR: leave it be the way it is, and when sp7 is no longer inprisoned by sp6, re-engineer the scoping from scratch.

Findings:
Some tables have a direct relationship to scoping tables (CollectionObject, Collection, Discipline, Division, Institution). Scoping for those is enforced in sp6. Scoping for them is enforced in sp7 only in query results and when going to that record directly by the URL - not enforced in other places.
There is an exception for query builder. The query builder scopes to Division only for some tables. See f79e70c

All of the below scoping is enforced in sp6 only using some custom logic for every case. None of this is implemented in sp7 (unless mentioned otherwise):

1. Children tables inherit scope from the parent (Accession Authorization is scoped to the same level as Accession)

2. Attachments are scoped using scopeType (represents id of the) and scopeId fields (represents the ID of the record in that scope). Note, the scopeId may refer to the record that no longer exists as delete blockers don't check for those (since scopeId is just a numeric field.

3. SpPermission table stores sp6 permissions. It has it's own weird solution for scoping.

4. There is an SpPrincipal table. RecordSet, AppResource, SpPermission, Workbench tables have relationship to it. The table appears to be used for permissions or scoping or both.

5. The following tables have a collectionMemberId integer field:

   [
     "Borrow",
     "BorrowAgent",
     "BorrowMaterial",
     "BorrowReturnMaterial",
     "CollectingEventAttachment",
     "CollectingEventAttr",
     "CollectingTripAttachment",
     "CollectionObject",
     "CollectionObjectAttachment",
     "CollectionObjectAttr",
     "CollectionObjectAttribute",
     "CollectionObjectCitation",
     "CollectionObjectProperty",
     "Container",
     "DNASequence",
     "DNASequencingRun",
     "Determination",
     "DeterminationCitation",
     "InfoRequest",
     "MaterialSample",
     "OtherIdentifier",
     "Preparation",
     "PreparationAttachment",
     "PreparationAttr",
     "PreparationAttribute",
     "PreparationProperty",
     "Project",
     "RecordSet",
     "SpExportSchemaMapping",
     "SpFieldValueDefault",
     "SpSymbiotaInstance",
     "VoucherRelationship"
   ]

   From what we can tell, that was a scoping solution inherited from Specify 5. The collectionMemberId does not consistency constraints so it may refer to collections that no longer exist.

   Specify 7 appears to be checking this field in tree stats and query builder scoping, but only for some tables.

Impact:
Scoping is not enforced consistently between tables, let alone between sp6 and sp7.
Sp6 has way too many messy ways to do scoping.
The best solution would be to leave things be until sp6 support is dropped, at which point there can be a database migration that fixes scoping for all tables.

Proposed ideal solutions for scoping:

Simple

If need to support dynamic scoping (scoping to different levels for different relationships), do what SpAppResourceDir table does and have optional relationships to collection or division. Then, if collection relationship is not null, then it is scoped to collection. Else, If division is not null, then it is scoped to division. Else, it is not scoped.

If table is always scoped to the same level, then just have a required relationship to collection or whatever it is.

Advanced

Have a Scope table, where each row could have an optional relationship to all scoping tables as separate columns.
Then, each record in the database that needs to be scoped, just has a relationship to the Scope record that contains the desired scope (has relationship to desired collection/discipline/division).

Then, imagine an advanced use case: when running a query, and for example want to scope results to two queries (e.x, because user said they don't want scoring, but they only have access to two collections), you just get ID of all the records in the Scope table that match your desired scope, and then filter all query results to records where scopeId relationship leads to one of the Scoes that matches the desired scope.

This solution can be easily extended in the future ot add more scoping by just adding new relationship to the Scope table.

What is more, the Scope table solution can be texted to accommodate customizing the scoping hierarchy (#77)

[maxpatiiuk]

Update:
A list of resolved scoping paths that sp7 front-end recognizes -

specify7/specifyweb/frontend/js_src/lib/components/DataModel/__tests__/__snapshots__/specifyModel.test.ts.snap

Lines 1029 to 1217 in 368eddf

	exports[`tableScoping 1`] = `
	{
	"Accession": "division",
	"AccessionAgent": "accession > division",
	"AccessionAttachment": "accession > division",
	"AccessionAuthorization": "accession > division",
	"AccessionCitation": "accession > division",
	"Address": "agent > division",
	"AddressOfRecord": undefined,
	"Agent": "division",
	"AgentAttachment": "agent > division",
	"AgentGeography": "agent > division",
	"AgentIdentifier": "agent > division",
	"AgentSpecialty": "agent > division",
	"AgentVariant": "agent > division",
	"Appraisal": undefined,
	"Attachment": undefined,
	"AttachmentImageAttribute": undefined,
	"AttachmentMetadata": undefined,
	"AttachmentTag": undefined,
	"AttributeDef": "discipline",
	"Author": undefined,
	"AutoNumberingScheme": undefined,
	"Borrow": undefined,
	"BorrowAgent": undefined,
	"BorrowAttachment": undefined,
	"BorrowMaterial": undefined,
	"BorrowReturnMaterial": undefined,
	"CollectingEvent": "discipline",
	"CollectingEventAttachment": "collectingEvent > discipline",
	"CollectingEventAttr": "collectingEvent > discipline",
	"CollectingEventAttribute": "discipline",
	"CollectingEventAuthorization": "collectingEvent > discipline",
	"CollectingTrip": "discipline",
	"CollectingTripAttachment": "collectingTrip > discipline",
	"CollectingTripAttribute": "discipline",
	"CollectingTripAuthorization": "collectingTrip > discipline",
	"Collection": "discipline",
	"CollectionObject": "collection",
	"CollectionObjectAttachment": "collectionObject",
	"CollectionObjectAttr": "collectionObject",
	"CollectionObjectAttribute": undefined,
	"CollectionObjectCitation": "collectionObject",
	"CollectionObjectProperty": "collectionObject",
	"CollectionRelType": undefined,
	"CollectionRelationship": undefined,
	"Collector": "division",
	"CommonNameTx": "taxon > definition > discipline",
	"CommonNameTxCitation": "commonNameTx > taxon > definition > discipline",
	"ConservDescription": "collectionObject",
	"ConservDescriptionAttachment": "conservDescription > collectionObject",
	"ConservEvent": "conservDescription > collectionObject",
	"ConservEventAttachment": "conservEvent > conservDescription > collectionObject",
	"Container": undefined,
	"DNAPrimer": undefined,
	"DNASequence": "collectionObject",
	"DNASequenceAttachment": "dnaSequence > collectionObject",
	"DNASequencingRun": "dnaSequence > collectionObject",
	"DNASequencingRunAttachment": "dnaSequencingRun > dnaSequence > collectionObject",
	"DNASequencingRunCitation": "sequencingRun > dnaSequence > collectionObject",
	"DataType": undefined,
	"Deaccession": undefined,
	"DeaccessionAgent": undefined,
	"DeaccessionAttachment": undefined,
	"Determination": "collectionObject",
	"DeterminationCitation": "determination > collectionObject",
	"Determiner": "determination > collectionObject",
	"Discipline": "division",
	"Disposal": undefined,
	"DisposalAgent": undefined,
	"DisposalAttachment": undefined,
	"DisposalPreparation": undefined,
	"Division": undefined,
	"ExchangeIn": "division",
	"ExchangeInAttachment": "exchangeIn > division",
	"ExchangeInPrep": "discipline",
	"ExchangeOut": "division",
	"ExchangeOutAttachment": "exchangeOut > division",
	"ExchangeOutPrep": "discipline",
	"Exsiccata": undefined,
	"ExsiccataItem": "collectionObject",
	"Extractor": "dnaSequence > collectionObject",
	"FieldNotebook": "collection",
	"FieldNotebookAttachment": "fieldNotebook > collection",
	"FieldNotebookPage": "discipline",
	"FieldNotebookPageAttachment": "fieldNotebookPage > discipline",
	"FieldNotebookPageSet": "discipline",
	"FieldNotebookPageSetAttachment": "fieldNotebookPageSet > discipline",
	"FundingAgent": "division",
	"GeoCoordDetail": undefined,
	"Geography": undefined,
	"GeographyTreeDef": undefined,
	"GeographyTreeDefItem": undefined,
	"GeologicTimePeriod": undefined,
	"GeologicTimePeriodTreeDef": undefined,
	"GeologicTimePeriodTreeDefItem": undefined,
	"Gift": "discipline",
	"GiftAgent": "discipline",
	"GiftAttachment": "gift > discipline",
	"GiftPreparation": "discipline",
	"GroupPerson": "division",
	"InfoRequest": undefined,
	"Institution": undefined,
	"InstitutionNetwork": undefined,
	"Journal": undefined,
	"LatLonPolygon": "locality > discipline",
	"LatLonPolygonPnt": "latLonPolygon > locality > discipline",
	"LithoStrat": undefined,
	"LithoStratTreeDef": undefined,
	"LithoStratTreeDefItem": undefined,
	"Loan": "discipline",
	"LoanAgent": "discipline",
	"LoanAttachment": "loan > discipline",
	"LoanPreparation": "discipline",
	"LoanReturnPreparation": "discipline",
	"Locality": "discipline",
	"LocalityAttachment": "locality > discipline",
	"LocalityCitation": "discipline",
	"LocalityDetail": "locality > discipline",
	"LocalityNameAlias": "discipline",
	"MaterialSample": "preparation > collectionObject",
	"MorphBankView": undefined,
	"OtherIdentifier": "collectionObject",
	"PaleoContext": "discipline",
	"PcrPerson": "dnaSequence > collectionObject",
	"Permit": undefined,
	"PermitAttachment": undefined,
	"PickList": "collection",
	"PickListItem": "pickList > collection",
	"PrepType": "collection",
	"Preparation": "collectionObject",
	"PreparationAttachment": "preparation > collectionObject",
	"PreparationAttr": "preparation > collectionObject",
	"PreparationAttribute": undefined,
	"PreparationProperty": "preparation > collectionObject",
	"Project": undefined,
	"RecordSet": undefined,
	"RecordSetItem": undefined,
	"ReferenceWork": undefined,
	"ReferenceWorkAttachment": undefined,
	"RepositoryAgreement": "division",
	"RepositoryAgreementAttachment": "repositoryAgreement > division",
	"Shipment": "discipline",
	"SpAppResource": "spAppResourceDir > collection",
	"SpAppResourceData": "spAppResource > spAppResourceDir > collection",
	"SpAppResourceDir": "collection",
	"SpAuditLog": undefined,
	"SpAuditLogField": undefined,
	"SpExportSchema": "discipline",
	"SpExportSchemaItem": "spExportSchema > discipline",
	"SpExportSchemaItemMapping": "exportSchemaItem > spExportSchema > discipline",
	"SpExportSchemaMapping": undefined,
	"SpFieldValueDefault": undefined,
	"SpLocaleContainer": "discipline",
	"SpLocaleContainerItem": "container > discipline",
	"SpLocaleItemStr": undefined,
	"SpPermission": undefined,
	"SpPrincipal": undefined,
	"SpQuery": undefined,
	"SpQueryField": undefined,
	"SpReport": "appResource > spAppResourceDir > collection",
	"SpSymbiotaInstance": undefined,
	"SpTaskSemaphore": "collection",
	"SpVersion": undefined,
	"SpViewSetObj": "spAppResourceDir > collection",
	"SpVisualQuery": undefined,
	"SpecifyUser": undefined,
	"Storage": undefined,
	"StorageAttachment": undefined,
	"StorageTreeDef": undefined,
	"StorageTreeDefItem": undefined,
	"Taxon": "definition > discipline",
	"TaxonAttachment": "taxon > definition > discipline",
	"TaxonAttribute": undefined,
	"TaxonCitation": "taxon > definition > discipline",
	"TaxonTreeDef": "discipline",
	"TaxonTreeDefItem": "treeDef > discipline",
	"TreatmentEvent": "collectionObject",
	"TreatmentEventAttachment": "treatmentEvent > collectionObject",
	"VoucherRelationship": "collectionObject",
	"Workbench": undefined,
	"WorkbenchDataItem": undefined,
	"WorkbenchRow": undefined,
	"WorkbenchRowExportedRelationship": undefined,
	"WorkbenchRowImage": undefined,
	"WorkbenchTemplate": undefined,
	"WorkbenchTemplateMappingItem": undefined,
	}
	`;

Only direct relationships to scoping table are currently enforced in forms and query builder (i.e, Accession: 'division').
This is to preserve the compatibility with the previous behavior.
We can consider changing that in the future.

Back-end query builder also looks at collectionMemberId field. Front-end does not. All other non-relationship scoping fields are ignored by both front-end and back-end.

[maxpatiiuk]

Okay, it get's worse. Turns out collection object has both collection relationship and collection id field

query builder bakc-end to look at collection relationship first (in this case, can't guarantee all)

front-end happens to look at collection id field (in this case, can't guarantee all)

[melton-jason]

Okay, it get's worse. Turns out collection object has both collection relationship and collection id field

query builder bakc-end to look at collection relationship first (in this case, can't guarantee all)

front-end happens to look at collection id field (in this case, can't guarantee all)

There are 32 tables which have 'CollectionMemberID' column. Preferably, we would be consistent in the way we manage scoping like this, but it will have to wait until we break compatibility with Specify 6.

For reference, the 32 tables are:

SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema="specify" AND column_name="collectionmemberid";

+----------------------------+
| preparationattr |
| borrowagent |
| project |
| otheridentifier |
| container |
| dnasequence |
| collectingtripattachment |
| borrowreturnmaterial |
| materialsample |
| preparation |
| collectionobjectattachment |
| borrow |
| spfieldvaluedefault |
| collectionobjectproperty |
| collectingeventattachment |
| dnasequencingrun |
| spsymbiotainstance |
| preparationattachment |
| voucherrelationship |
| collectingeventattr |
| spexportschemamapping |
| collectionobject |
| determinationcitation |
| preparationproperty |
| collectionobjectattribute |
| determination |
| recordset |
| collectionobjectattr |
| collectionobjectcitation |
| inforequest |
| borrowmaterial |
| preparationattribute |
+----------------------------+