Project

General

Profile

task #9291

Clear all unnecesary TypeDesignation.citation entities and citationDetails

Added by Andreas Kohlbecker 6 months ago. Updated 4 days ago.

Status:
Feedback
Priority:
Highest
Category:
data
Target version:
Start date:
11/10/2020
Due date:
% Done:

40%

Severity:
normal
Tags:

Description

This ticket should be solved after #9290 is being released and deployed to phycobank

This is the query to which has been used to create the report for #9290. The update statement can be based on that:


SELECT
    tn.uuid as TaxonName_uuid, tn.nameCache as TaxonName_nameCache, 
    tn_r.titleCache as TaxonName_nomRef, tn_r.refType as TaxonName_nomRef_type, tn_r.uuid as TaxonName_nomRef_uuid, 
    ts.titleCache as TypeStatus_label, ts.id as TypeStatus_id, 
    tdb_r.titleCache as TypeDesignation_citation, tdb_r.refType as TypeDesignation_citation_type, tdb_r.uuid as TypeDesignation_citation_uuid, 
    tdb.uuid as TypeDesignation_uuid 
FROM TypeDesignationBase tdb
LEFT JOIN DefinedTermBase ts ON tdb.typeStatus_id = ts.id
JOIN TaxonName_TypeDesignationBase tntdb on tdb.id = tntdb.typedesignations_id 
JOIN TaxonName tn on tn.id = tntdb.TaxonName_id
LEFT JOIN Reference tn_r on tn_r.id = tn.nomenclaturalReference_id 
LEFT JOIN Reference tdb_r on tdb_r.id = tdb.citation_id 
WHERE tdb.citation_id IS NOT NULL 
    AND ts.id NOT IN (853 ,839 ,840 ,843 ,850 ,846 ,852 ,847 ,839 ,2108 ,2110 ,2111 ,2112) 
    AND tdb.citation_id <> tn.nomenclaturalReference_id 
ORDER BY tdb_r.uuid 

Related issues

Related to Edit - bug #9290: stop setting Registration.citation as citation for TypeDesignations in all type editors by default Closed 11/10/2020

History

#1 Updated by Andreas Kohlbecker 6 months ago

  • Related to bug #9290: stop setting Registration.citation as citation for TypeDesignations in all type editors by default added

#2 Updated by Andreas Kohlbecker 6 months ago

  • File TaxonName and Typedesignation citations unequal_diff.xlsx added
  • Description updated (diff)

#3 Updated by Andreas Kohlbecker 6 months ago

the Excel file belongs to the other ticket - cleaning up here ....

#4 Updated by Andreas Kohlbecker 6 months ago

  • File deleted (TaxonName and Typedesignation citations unequal_diff.xlsx)

#5 Updated by Andreas Kohlbecker 3 months ago

  • Target version changed from Release 5.19 to Release 5.21

#6 Updated by Andreas Kohlbecker 3 months ago

  • Priority changed from Priority14 to Highest

#7 Updated by Andreas Müller 2 months ago

  • Target version changed from Release 5.21 to Release 5.22

#8 Updated by Andreas Kohlbecker 9 days ago

select statement simplified and updated to current model version

SELECT
    tn.uuid as TaxonName_uuid, tn.nameCache as TaxonName_nameCache, 
    ts.titleCache as TypeStatus_label, ts.id as TypeStatus_id, 
    tdb_r.titleCache as TypeDesignation_citation, tdb_r.refType as TypeDesignation_citation_type, tdb_r.uuid as TypeDesignation_citation_uuid, 
    tdb.uuid as TypeDesignation_uuid 
FROM TypeDesignationBase tdb
LEFT JOIN DefinedTermBase ts ON tdb.typeStatus_id = ts.id
JOIN TaxonName_TypeDesignationBase tntdb on tdb.id = tntdb.typedesignations_id 
JOIN TaxonName tn on tn.id = tntdb.TaxonName_id
LEFT JOIN OriginalSourceBase osb on osb.id = tdb.designationSource_id 
LEFT JOIN Reference tdb_r on tdb_r.id = osb.citation_id 
WHERE osb.citation_id IS NOT NULL 
   AND ts.id NOT IN (853 ,839 ,840 ,843 ,850 ,846 ,852 ,847 ,839 ,2108 ,2110 ,2111 ,2112) 
ORDER BY ts.titleCache

#9 Updated by Andreas Kohlbecker 9 days ago

All type designations that do not have a state like below must not have a designationSource:

// NameTypeDesignationStatus
LECTOTYPE
SUBSEQUENT_DESIGNATION
SUBSEQUENT_MONOTYPY
PRESENT_DESIGNATION
// SpecimenTypeDesignationStatus 
SECOND_STEP_NEOTYPE
SECOND_STEP_LECTOTYPE
PARALECTOTYPE
ISOEPITYPE
ISONEOTYPE
ISOLECTOTYPE
EPITYPE
NEOTYPE
LECTOTYPE
select DTYPE, id, titleCache from DefinedTermBase dtb 
   where id in (853 ,839 ,840 ,843 ,850 ,846 ,852 ,847 ,848 ,839 ,2108 ,2110 ,2111 ,2112) 

#10 Updated by Andreas Kohlbecker 9 days ago

  • Status changed from New to Feedback
  • Assignee changed from Andreas Kohlbecker to Andreas Müller
  • % Done changed from 0 to 40

@Andreas M.: Could you please double check this sql script?

CREATE TEMPORARY TABLE temp_ds_cleanup(
    type_designation_id int(11),
    original_ource_id int(11)
);

INSERT INTO temp_ds_cleanup(type_designation_id, original_ource_id) 
    SELECT tdb.id, osb.id FROM TypeDesignationBase tdb
    LEFT JOIN OriginalSourceBase osb on osb.id = tdb.designationSource_id 
    WHERE tdb.designationSource_id IS NOT NULL 
        AND tdb.typeStatus_id NOT IN (853 ,839 ,840 ,843 ,850 ,846 ,852 ,847 ,848 ,839 ,2108 ,2110 ,2111 ,2112);

-- SELECT * FROM temp_ds_cleanup;

UPDATE TypeDesignationBase tdb SET tdb.designationSource_id = NULL WHERE tdb.id IN select tdc.type_designation_id FROM temp_ds_cleanup AS tdc;

DELETE FROM OriginalSourceBase osb WHERE osb.id in select tdc.original_ource_id FROM temp_ds_cleanup AS tdc;

DROP TEMPORARY TABLE temp_ds_cleanup;

#11 Updated by Andreas Müller 8 days ago

  • Assignee changed from Andreas Müller to Andreas Kohlbecker

If the condition from #note-9 is correct the query looks ok except for the fact that it does not include AUD tables which makes AUD and NON-AUD tables inconsistent.

Probably it is enough to run the UPDATE and the DELTE statement also on AUD tables and therefore pretend that the false citations never existed. But you know the specifics of this ticket better and maybe I am wrong.

#12 Updated by Andreas Kohlbecker 8 days ago

  • Assignee changed from Andreas Kohlbecker to Andreas Müller

wouldn't it be anyway much better to do such cleanup tasks in general via the DAO layer?

#13 Updated by Andreas Müller 8 days ago

  • Assignee changed from Andreas Müller to Andreas Kohlbecker

Andreas Kohlbecker wrote:

wouldn't it be anyway much better to do such cleanup tasks in general via the DAO layer?

This depends from case to case. In general I agree that if possible using hibernate and not SQL is preferred to be on the save side with AUD tables and other caching issues. So, of cours, you could also write a script using service or persistence layer.
In some cases, like model updates, it is very difficult to do so as the model is out-of-sync then.

For the given case it is definetely possible to write such a script if you want.

#14 Updated by Andreas Kohlbecker 8 days ago

  • Assignee changed from Andreas Kohlbecker to Andreas Müller

Ok, for model updates it may be problematic. I rather thought on all other taks, like data cleaning and so on, when I said "in general".
What is your personal policy for these taks? Do you accept inconsistent AUD tables or do you write java clean up apps in almost all cases?

I am asking since we should have a common strategy.

#15 Updated by Andreas Müller 4 days ago

  • Assignee changed from Andreas Müller to Andreas Kohlbecker
  • Target version changed from Release 5.22 to Release 5.23

we have discussed this

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)