Actions
task #9650
opentask #4338: [MASTER] Handle abbreviated title and protected cache correctly in TaxEditor
Update all titleCaches after upgrade of cache strategies and formatters
Start date:
Due date:
% Done:
70%
Estimated time:
Severity:
normal
Description
Before the databases need to be checked for data dirty data which could be overwritten by cache update (e.g. references in palmweb).
Useful sql are:
Find potential issues in AgentBase, Reference and TaxonBase
SELECT ab.titleCache, ab.nomenclaturaltitle, ab.* FROM AgentBase ab WHERE ab.DTYPE = 'Person' AND ab.protectedtitlecache = 0 AND (ab.familyName IS NULL OR LTRIM(ab.familyName = '')) AND ab.titleCache <> ab.nomenclaturaltitle; SELECT ab.titleCache, ab.nomenclaturaltitle, ab.* FROM AgentBase ab WHERE ab.DTYPE = 'Team' AND (ab.protectedtitlecache =0 OR ab.protectednomenclaturaltitlecache =0 ) AND ab.id NOT IN (SELECT MN.team_id FROM AgentBase_AgentBase MN ) ; SELECT r.refType, r.titleCache, r.uuid, r.* FROM Reference r WHERE r.protectedtitlecache = 0 AND (r.title IS NULL OR LTRIM(r.title = '')) AND (r.abbrevTitle IS NULL OR LTRIM(r.abbrevTitle = '')) AND r.refType <> 'ART' AND r.refType <> 'BS' AND !(r.refType = 'GEN' AND r.inreference_id IS NOT NULL) ORDER BY r.refType; SELECT r.refType, inRef.refType, r.titleCache, r.uuid, r.* FROM Reference r LEFT JOIN Reference inRef ON r.inreference_id = inRef.id WHERE r.protectedtitlecache = 0 AND (inRef.title IS NULL OR LTRIM(inRef.title = '')) AND (inRef.abbrevTitle IS NULL OR LTRIM(inRef.abbrevTitle = '')) AND (r.refType = 'ART' OR r.refType = 'BS' OR (r.refType = 'GEN' AND r.inreference_id IS NOT NULL) ) ORDER BY r.refType ; SELECT tn.id, tn.uuid, tn.titleCache, tn.nameCache, tn.genusOrUninomial, tn.* FROM TaxonName tn WHERE tn.protectedtitlecache = 0 AND protectednamecache = 0 AND (tn.genusoruninomial IS NULL OR LTRIM(tn.genusoruninomial = ''))
Test which data has been updated (set @rev to latest revision before update) :
SELECT * FROM AuditEvent ev ORDER BY ev.revisionnumber DESC; SET @rev = 4427; SELECT ab.id, ab.REV, ab.REVTYPE, ab.titleCache, ab.nomenclaturaltitle, ab.* FROM AgentBase_AUD ab WHERE ab.id IN ( SELECT aa.id FROM AgentBase_AUD aa WHERE aa.REV > @rev ) ORDER BY ab.id, ab.REV; SELECT r.id, r.REV, r.REVTYPE, r.titleCache, r.abbrevTitleCache, r.* FROM Reference_AUD r WHERE r.id IN ( SELECT ab.id FROM Reference_AUD ab WHERE ab.REV > @rev) ORDER BY r.id, r.REV ; SELECT r.id, r.REV, r.REVTYPE, r.titleCache, r.* FROM TaxonBase_AUD r WHERE r.id IN ( SELECT ab.id FROM TaxonBase_AUD ab WHERE ab.REV > @rev) ORDER BY r.id, r.REV ; SELECT r.id, r.REV, r.REVTYPE, r.titleCache, r.fullTitleCache, osb.citation_id refId, r.*, osb.id, osb.* FROM TaxonName_AUD r LEFT JOIN OriginalSourceBase osb ON osb.sourcedName_id = r.id WHERE r.id IN ( SELECT ab.id FROM TaxonName_AUD ab LEFT JOIN OriginalSourceBase osb ON osb.sourcedName_id = ab.id WHERE ab.REV > @rev) ORDER BY r.id, r.REV ; SELECT r.id, r.REV, r.REVTYPE, r.titleCache, r.* FROM SpecimenOrObservationBase_AUD r WHERE r.id IN ( SELECT ab.id FROM SpecimenOrObservationBase_AUD ab WHERE ab.REV > @rev) ORDER BY r.id, r.REV ;
Also sometimes helpful:
SELECT * FROM OriginalSourceBase osb WHERE osb.sourcedName_id = 543 OR osb.citation_id = 55568 ; SELECT r.inreference_id, r.* FROM Reference_AUD r WHERE r.id IN (368) ORDER BY r.id, r.REV ; SELECT * FROM TaxonName tn WHERE tn.problemstarts > 0 AND tn.parsingproblem NOT IN (2,4) ORDER BY tn.id ; SELECT * FROM TaxonName_AUD n WHERE n.id IN ( 9865)
Related issues
Actions