Project

General

Profile

Actions

task #9650

open

task #4338: [MASTER] Handle abbreviated title and protected cache correctly in TaxEditor

Update all titleCaches after upgrade of cache strategies and formatters

Added by Andreas Müller 12 months ago. Updated 5 months ago.

Status:
In Progress
Priority:
Highest
Category:
data
Target version:
Start date:
Due date:
% Done:

30%

Estimated time:
Severity:
normal
Tags:

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

Related to EDIT - task #9364: Cleanup authors with multiple & and protected names and teamsClosedAndreas Müller

Actions
Related to EDIT - task #9658: Cleanup authors with protected names and authorsNewAndreas Müller

Actions
Related to EDIT - bug #9649: Specimens sometimes have empty titleCache instead of class<UUID>ClosedAndreas Müller

Actions
Actions #1

Updated by Andreas Müller 12 months ago

  • Description updated (diff)
Actions #2

Updated by Andreas Müller 11 months ago

  • Related to task #9364: Cleanup authors with multiple & and protected names and teams added
Actions #3

Updated by Andreas Müller 11 months ago

  • Related to task #9658: Cleanup authors with protected names and authors added
Actions #4

Updated by Andreas Müller 11 months ago

  • % Done changed from 0 to 20

Updated all databases except for col, euromed, euromed_caucasus and vibrant_index names/taxa/specimen

Actions #5

Updated by Andreas Müller 11 months ago

  • Description updated (diff)
Actions #6

Updated by Andreas Müller 11 months ago

  • Description updated (diff)
  • % Done changed from 20 to 0
Actions #7

Updated by Andreas Müller 11 months ago

  • Description updated (diff)
Actions #8

Updated by Andreas Müller 11 months ago

  • Description updated (diff)
Actions #9

Updated by Andreas Müller 11 months ago

  • Related to bug #9649: Specimens sometimes have empty titleCache instead of class<UUID> added
Actions #10

Updated by Andreas Müller 11 months ago

  • Status changed from New to In Progress
  • Priority changed from New to Highest
  • % Done changed from 0 to 30
Actions #11

Updated by Andreas Müller 11 months ago

  • Target version changed from Release 5.25 to Release 5.34
Actions #12

Updated by Andreas Müller 6 months ago

Updated all caches for databases on integration server

Actions #14

Updated by Andreas Müller 5 months ago

last full cache updates: (e.g. for country labels)

additivity_ontology: 2021-12-09
algaterra_old: 2021-12-08
algaterra_new: 2021-12-08
asteraceae: 2021-12-08
bromeliaceae: 2021-12-08
campanulaceae: 2021-12-08
amaranthaceae: 2021-12-08
nepenthaceae: 2021-12-08
caryophyllales: 2021-12-08
caryophyllales_spp: 2021-12-08
casearia: 2021-12-09
cichorieae: 2021-12-09
corvidae: 2021-12-09
cyprus: 2021-12-09
diptera: 2021-12-09
edaphobase:
euromed:
euromed_caucasus:
fdac:
flora-cuba: 2021-12-08
flora_greece: 2021-12-09
flora_guianas: 2021-12-09
flora_malesiana:
flora_malesiana_clean: 2021-12-09
flora_malesiana_prospective: 2021-12-09
flora_uzbekistan: 2021-12-09
flore_gabon: 2021-12-09
globis: 2021-12-09
greece_bupleurum: 2021-12-09
lichenes: 2021-12-09
myristicaceae: 2021-12-09
oxalis: 2021-12-09
palmae: 2021-12-09
phycobank: 2021-12-09
piB_ants: 2021-12-09
piB_campylopus: 2021-12-09
piB_chenopodium: 2021-12-09
piB_eupolybothrus:
rl_animalia: 2021-12-09
rl_animalia_final: 2021-12-09
rl_armeria_demo: 2021-12-09
rl_german_sl: 2021-12-09
rl_mammalia: 2021-12-09
rl_moose: 2021-12-09
rl_plantae: 2021-12-09
rl_standardliste: 2021-12-09
rubiaceae_mexico: 2021-12-09
salvador: 2021-12-09
vibrant:

Actions

Also available in: Atom PDF