task #6226
closedDelete orphaned key nodes
100%
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
Updated by Andreas Müller over 7 years ago
SELECT *
FROM PolytomousKeyNode pkn
WHERE pkn.key_id NOT IN (SELECT id FROM PolytomousKey);
Updated by Andreas Müller over 7 years ago
- Target version changed from CDM UML 4.1 to Release 4.6
Updated by Andreas Müller over 7 years ago
- Target version changed from Release 4.6 to CDM UML 4.7
Updated by Andreas Müller almost 7 years ago
- Priority changed from New to Highest
Updated by Andreas Müller almost 7 years ago
Also need to delete orphaned key statements
Updated by Andreas Müller almost 7 years ago
- Description updated (diff)
- Status changed from New to Resolved
- % Done changed from 0 to 50
Updated by Andreas Müller almost 7 years ago
- Copied to bug #6705: Remove remaining orphaned PolytomousKeyNodes added
Updated by Andreas Müller almost 7 years 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