Artenquiz AnyObject cleanup
Artenquiz uses an AnyObject table that is the superclass of most other tables. The id of AnyObject is always identical to the id of Character, etc.
When programming is perfect, no cleanup is necessary. However, after bugs it may be necessary to use the following queries to find and fix bugs:
--Any id occurring twice? Select T.id, Count(*) FROM ( Select id from CategoricalCharacterData UNION ALL Select id from `Character` UNION ALL Select id from CharacterDataStatus UNION ALL Select id from CharacterState UNION ALL Select id from CharacterTree UNION ALL Select id from CharacterTreeNode UNION ALL Select id from DataStatus_Enum UNION ALL Select id from Description UNION ALL Select id from Frequency UNION ALL Select id from GeographicArea UNION ALL Select id from Modifier UNION ALL Select id from Project UNION ALL Select id from QuantitativeCharacterData UNION ALL Select id from Resource UNION ALL Select id from Scope UNION ALL Select id from StatisticalMeasure_Enum UNION ALL Select id from Taxon UNION ALL Select id from TaxonRank_Enum UNION ALL Select id from TaxonSet UNION ALL Select id from TextCharacterData ) T Group by T.id Having Count(*) > 1 Order by Count(*) DESC
--To check which AnyObjects are no longer used do --1. reset: UPDATE AnyObject Set is_present=0 --2. set present: UPDATE AnyObject INNER JOIN ( Select id from CategoricalCharacterData UNION ALL Select id from `Character` UNION ALL Select id from CharacterDataStatus UNION ALL Select id from CharacterState UNION ALL Select id from CharacterTree UNION ALL Select id from CharacterTreeNode UNION ALL Select id from DataStatus_Enum UNION ALL Select id from Description UNION ALL Select id from Frequency UNION ALL Select id from GeographicArea UNION ALL Select id from Modifier UNION ALL Select id from Project UNION ALL Select id from QuantitativeCharacterData UNION ALL Select id from Resource UNION ALL Select id from Scope UNION ALL Select id from StatisticalMeasure_Enum UNION ALL Select id from Taxon UNION ALL Select id from TaxonRank_Enum UNION ALL Select id from TaxonSet UNION ALL Select id from TextCharacterData ) T ON T.id = AnyObject.id SET is_present=1 --Optionally verify unused: SELECT * FROM AnyObject WHERE is_present=0 --Now delete unused AnyObjects: DELETE FROM AnyObject WHERE is_present=0
Any object ids that are missing in the AnyObjects table?
SELECT * FROM AnyObject RIGHT JOIN ( Select id from CategoricalCharacterData UNION ALL Select id from `Character` UNION ALL Select id from CharacterDataStatus UNION ALL Select id from CharacterState UNION ALL Select id from CharacterTree UNION ALL Select id from CharacterTreeNode UNION ALL Select id from DataStatus_Enum UNION ALL Select id from Description UNION ALL Select id from Frequency UNION ALL Select id from GeographicArea UNION ALL Select id from Modifier UNION ALL Select id from Project UNION ALL Select id from QuantitativeCharacterData UNION ALL Select id from Resource UNION ALL Select id from Scope UNION ALL Select id from StatisticalMeasure_Enum UNION ALL Select id from Taxon UNION ALL Select id from TaxonRank_Enum UNION ALL Select id from TaxonSet UNION ALL Select id from TextCharacterData ) T ON T.id = AnyObject.id WHERE AnyObject.id is NULL
In our case the result set was empty, indicating no AnyObject entries missing.
ELSE: Write an INSERT QUERY, adding the missing ones!
