Project

General

Profile

task #6226

Delete orphaned key nodes

Added by Andreas Müller about 2 years ago. Updated over 1 year ago.

Status:
Closed
Priority:
Highest
Category:
data
Target version:
Start date:
11/28/2016
Due date:
% Done:

100%

Severity:
normal

Description

As deletion did not work correctly for long time we do still have PolytomousKeyNodes that do reference a PolytomousKey that does not exist anymore in the database (due to missing referential integrity checking).

These need to be deleted via update script.

The same may be true for other tree nodes (TaxonNode, FeatureNode) => checked this, taxon node and feature node do not seem to be critical, didn't find any example, maybe taxon nodes where deleted previously already


Related issues

Copied to Edit - bug #6705: Remove remaining orphaned PolytomousKeyNodes New 06/08/2017

Associated revisions

Revision 38dc3727 (diff)
Added by Andreas Müller over 1 year ago

fix #6226 delete orphaned keys and key statements

History

#1 Updated by Andreas Müller about 2 years ago

SELECT *
FROM PolytomousKeyNode pkn
WHERE pkn.key_id NOT IN (SELECT id FROM PolytomousKey);

#2 Updated by Andreas Müller about 2 years ago

  • Target version changed from CDM UML 4.1 to Release 4.6

#3 Updated by Andreas Müller about 2 years ago

  • Target version changed from Release 4.6 to CDM UML 4.7

#4 Updated by Andreas Müller over 1 year ago

  • Priority changed from New to Highest

#5 Updated by Andreas Müller over 1 year ago

Also need to delete orphaned key statements

#6 Updated by Andreas Müller over 1 year ago

  • Tracker changed from bug to task

#7 Updated by Andreas Müller over 1 year ago

  • Description updated (diff)
  • Status changed from New to Resolved
  • % Done changed from 0 to 50

#8 Updated by Andreas Müller over 1 year ago

  • Copied to bug #6705: Remove remaining orphaned PolytomousKeyNodes added

#9 Updated by Andreas Müller over 1 year ago

  • Status changed from Resolved to Closed

I checked with

SELECT *
FROM PolytomousKeyNode pkn
WHERE pkn.key_id NOT IN (SELECT id FROM PolytomousKey);

SELECT *
FROM PolytomousKeyNode pkn
WHERE pkn.question_id NOT IN (SELECT ks.id FROM KeyStatement ks) or 
 pkn.statement_id NOT IN (SELECT ks.id FROM KeyStatement ks);

 SELECT *
 FROM KeyStatement ks
 WHERE id NOT IN (SELECT MN.KeyStatement_id FROM KeyStatement_LanguageString MN );

 SELECT *
 FROM KeyStatement_LanguageString MN
 WHERE MN.label_id NOT IN (SELECT id FROM LanguageString LS ) ;

didn't find any unlinked records except for those being there before already (e.g. in cyprus KeyStatement.id = 2613 which has not LanguageString) => deleted this in production as it is not in the key anymore.

So there are some more orphaned key nodes that have no parent but are also no root nodes. This is handled in new #6705

#10 Updated by Andreas Müller over 1 year ago

  • % Done changed from 50 to 100

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)