EDIT

task #9650

Updated by Andreas Müller almost 3 years ago

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) ) 'BS') 
 ORDER BY r.refType 
 ; 

 SELECT tn.id, tn.uuid, tn.titleCache, tn.nameCache, tn.genusOrUninomial, tn.* 
 FROM TaxonName tn 
 WHERE tn.protectedtitlecache = 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.* r.* 
 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 ; 
 ~~~

Back