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!