Database enhancements for Documents [2021:HPR]

[Brian Lewis]

1. Primary key is on a Guid, but is clustered. This is a big No-No - because the table has to get rewritten every time a record is inserted, in order to keep the records in key order. Clustered index are best used when new records will always go on the end - ie an Identity key - or when you generally retrieve sets of records in key order ( e.g. some date/time key)
2. Add a field docCloudRemoved to track when the google drive version of a document is deleted. Without this, any purge routine will continue to try to delete the same records.
3. Add an index on docCloudID for faster retrieval on google file ID. This index should carry docCloudRemoved so that can be accessible to prugre routine without an actual data read.
4. Add inspID to DocumentLinks_ to explicitly track documents associated to an inspection. Currently documents relating to an inspection are found by extracting the RemotePath of each Photo in the inspection Xml, then using that to find the document ( ie photo) via docCloudId. Note the inspection ID and school No can be written on the same DocumentLinks_ record. i.e. deleting the inspection would automatically remove the photo from the school’s list as well.
5. Create InspectionLinks view, similar to SchoolLinks, TeacherLinks etc; i.e. using inspID on DocumentLinks_

‌

[Brian Lewis]

task(documents): Enhancements to database storage of documents and links

Resolves #1091

→ <<cset 75937e688d6a>>

[Brian Lewis]

Also implemented DELETE Cascades on the relations to DocumentLinks_