Inconsistent caching of protected location_id

[JimBacon]

In commit e335ab2, the cache_occurrences_functional.location_id is set to null when any of the following are true:

• the occurrence is marked confidential (an administrator wants to restrict communication about the record)
• the occurrence is marked sensitive (the species needs protecting)
• the sample is marked private (the site location needs protecting)

The first inconsistency is that this in only done on update, not on insert.
The second inconsistency is that this is only applied to cache_occurrences_functional and not cache_samples_functional

The inconsistency needs resolving. Nullifying the location_id does not feel right to me but reversing the afforementioned commit would presumably lead to protected information being revealed in places we cannot predict or control so is not really an option.

[johnvanbreda]

Why does nullifying location_id not feel right to you? I think that's the best solution.

Both the samples and occurrences cache do nullify location_id for sensitive records for both insert and update - the nullification is done in a separate query after the main operation though, presumably to improve performance. For example see $config['samples']['update']['functional_sensitive']. I agree about the confidential records and private locations not being consistent though.

[JimBacon]

I guess nullifying the location_id feels like breaking the referential integrity of the cache table. I haven't considered how else you might achieve whatever is required!

In passing I note that, since the cache tables have evolved beyond just flattened copies of the data in the tables they are derived from, it would be helpful if we added column descriptions to the cache tables.

I hadn't spotted the additional queries like $config['samples']['update']['functional_sensitive'].

I've stumbled on this problem in relation to confidential records, some of which have a cache_occurrences_functional.location_id and some of which don't.