Difference between revisions of "Artenquiz AnyObject cleanup"

From Biowikifarm Metawiki
Jump to: navigation, search
(Created page with "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 perfec...")
 
m (as syntaxhighlight)
 
Line 3: Line 3:
 
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:
 
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?
+
<syntaxhighlight lang="mysql">
 +
-- Any id occurring twice?
 
Select T.id, Count(*) FROM (
 
Select T.id, Count(*) FROM (
−
Select id from CategoricalCharacterData UNION ALL
+
  Select id from CategoricalCharacterData UNION ALL
−
Select id from `Character` UNION ALL
+
  Select id from `Character` UNION ALL
−
Select id from CharacterDataStatus UNION ALL
+
  Select id from CharacterDataStatus UNION ALL
−
Select id from CharacterState UNION ALL
+
  Select id from CharacterState UNION ALL
−
Select id from CharacterTree UNION ALL
+
  Select id from CharacterTree UNION ALL
−
Select id from CharacterTreeNode UNION ALL
+
  Select id from CharacterTreeNode UNION ALL
−
Select id from DataStatus_Enum UNION ALL
+
  Select id from DataStatus_Enum UNION ALL
−
Select id from Description UNION ALL
+
  Select id from Description UNION ALL
−
Select id from Frequency UNION ALL
+
  Select id from Frequency UNION ALL
−
Select id from GeographicArea UNION ALL
+
  Select id from GeographicArea UNION ALL
−
Select id from Modifier UNION ALL
+
  Select id from Modifier UNION ALL
−
Select id from Project UNION ALL
+
  Select id from Project UNION ALL
−
Select id from QuantitativeCharacterData UNION ALL
+
  Select id from QuantitativeCharacterData UNION ALL
−
Select id from Resource UNION ALL
+
  Select id from Resource UNION ALL
−
Select id from Scope UNION ALL
+
  Select id from Scope UNION ALL
−
Select id from StatisticalMeasure_Enum UNION ALL
+
  Select id from StatisticalMeasure_Enum UNION ALL
−
Select id from Taxon UNION ALL
+
  Select id from Taxon UNION ALL
−
Select id from TaxonRank_Enum UNION ALL
+
  Select id from TaxonRank_Enum UNION ALL
−
Select id from TaxonSet UNION ALL
+
  Select id from TaxonSet UNION ALL
−
Select id from TextCharacterData ) T
+
  Select id from TextCharacterData ) T
 
Group by T.id
 
Group by T.id
 
Having Count(*) > 1
 
Having Count(*) > 1
 
Order by Count(*) DESC
 
Order by Count(*) DESC
 +
</syntaxhighlight>
  
−
----------
+
<syntaxhighlight lang="mysql">
−
--To check which AnyObjects are no longer used do
+
-- To check which AnyObjects are no longer used do
−
--1. reset:
+
-- 1. reset:
 
UPDATE  AnyObject Set is_present=0
 
UPDATE  AnyObject Set is_present=0
−
--2. set present:
+
-- 2. set present:
 
UPDATE AnyObject INNER JOIN (
 
UPDATE AnyObject INNER JOIN (
−
Select id from CategoricalCharacterData UNION ALL
+
  Select id from CategoricalCharacterData UNION ALL
−
Select id from `Character` UNION ALL
+
  Select id from `Character` UNION ALL
−
Select id from CharacterDataStatus UNION ALL
+
  Select id from CharacterDataStatus UNION ALL
−
Select id from CharacterState UNION ALL
+
  Select id from CharacterState UNION ALL
−
Select id from CharacterTree UNION ALL
+
  Select id from CharacterTree UNION ALL
−
Select id from CharacterTreeNode UNION ALL
+
  Select id from CharacterTreeNode UNION ALL
−
Select id from DataStatus_Enum UNION ALL
+
  Select id from DataStatus_Enum UNION ALL
−
Select id from Description UNION ALL
+
  Select id from Description UNION ALL
−
Select id from Frequency UNION ALL
+
  Select id from Frequency UNION ALL
−
Select id from GeographicArea UNION ALL
+
  Select id from GeographicArea UNION ALL
−
Select id from Modifier UNION ALL
+
  Select id from Modifier UNION ALL
−
Select id from Project UNION ALL
+
  Select id from Project UNION ALL
−
Select id from QuantitativeCharacterData UNION ALL
+
  Select id from QuantitativeCharacterData UNION ALL
−
Select id from Resource UNION ALL
+
  Select id from Resource UNION ALL
−
Select id from Scope UNION ALL
+
  Select id from Scope UNION ALL
−
Select id from StatisticalMeasure_Enum UNION ALL
+
  Select id from StatisticalMeasure_Enum UNION ALL
−
Select id from Taxon UNION ALL
+
  Select id from Taxon UNION ALL
−
Select id from TaxonRank_Enum UNION ALL
+
  Select id from TaxonRank_Enum UNION ALL
−
Select id from TaxonSet UNION ALL
+
  Select id from TaxonSet UNION ALL
−
Select id from TextCharacterData ) T
+
  Select id from TextCharacterData ) T
 
ON T.id = AnyObject.id
 
ON T.id = AnyObject.id
 
SET  is_present=1
 
SET  is_present=1
−
--Optionally verify unused:
+
-- Optionally verify unused:
 
SELECT * FROM AnyObject WHERE is_present=0
 
SELECT * FROM AnyObject WHERE is_present=0
−
--Now delete unused AnyObjects:
+
-- Now delete unused AnyObjects:
 
DELETE FROM AnyObject WHERE is_present=0
 
DELETE FROM AnyObject WHERE is_present=0
−
 
+
</syntaxhighlight>
−
 
+
−
----------------
+
  
 
Any object ids that are missing in the AnyObjects table?
 
Any object ids that are missing in the AnyObjects table?
−
 
+
<syntaxhighlight lang="mysql">
 
SELECT * FROM AnyObject RIGHT JOIN (
 
SELECT * FROM AnyObject RIGHT JOIN (
−
Select id from CategoricalCharacterData UNION ALL
+
  Select id from CategoricalCharacterData UNION ALL
−
Select id from `Character` UNION ALL
+
  Select id from `Character` UNION ALL
−
Select id from CharacterDataStatus UNION ALL
+
  Select id from CharacterDataStatus UNION ALL
−
Select id from CharacterState UNION ALL
+
  Select id from CharacterState UNION ALL
−
Select id from CharacterTree UNION ALL
+
  Select id from CharacterTree UNION ALL
−
Select id from CharacterTreeNode UNION ALL
+
  Select id from CharacterTreeNode UNION ALL
−
Select id from DataStatus_Enum UNION ALL
+
  Select id from DataStatus_Enum UNION ALL
−
Select id from Description UNION ALL
+
  Select id from Description UNION ALL
−
Select id from Frequency UNION ALL
+
  Select id from Frequency UNION ALL
−
Select id from GeographicArea UNION ALL
+
  Select id from GeographicArea UNION ALL
−
Select id from Modifier UNION ALL
+
  Select id from Modifier UNION ALL
−
Select id from Project UNION ALL
+
  Select id from Project UNION ALL
−
Select id from QuantitativeCharacterData UNION ALL
+
  Select id from QuantitativeCharacterData UNION ALL
−
Select id from Resource UNION ALL
+
  Select id from Resource UNION ALL
−
Select id from Scope UNION ALL
+
  Select id from Scope UNION ALL
−
Select id from StatisticalMeasure_Enum UNION ALL
+
  Select id from StatisticalMeasure_Enum UNION ALL
−
Select id from Taxon UNION ALL
+
  Select id from Taxon UNION ALL
−
Select id from TaxonRank_Enum UNION ALL
+
  Select id from TaxonRank_Enum UNION ALL
−
Select id from TaxonSet UNION ALL
+
  Select id from TaxonSet UNION ALL
−
Select id from TextCharacterData ) T
+
  Select id from TextCharacterData ) T
 
ON T.id = AnyObject.id
 
ON T.id = AnyObject.id
 
WHERE AnyObject.id is NULL
 
WHERE AnyObject.id is NULL
 +
</syntaxhighlight>
  
 
In our case the result set was empty, indicating no AnyObject entries missing.
 
In our case the result set was empty, indicating no AnyObject entries missing.

Latest revision as of 17:18, 3 February 2015

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!


Retrieved from "https://biowikifarm.net/metawiki/index.php?title=Artenquiz_AnyObject_cleanup&oldid=5148"