-- These sql is to check the integrity of the hierarchical tables
-- =====================================================================
--
--
spool checkHierarchies
--
-- ---------------------------------------------------------------------
--
-- integrity constraint: all topic_pointers must refer to existing topic_ids
-- here no topic is an allowed result
--
SELECT topic_id,topic_name,topic_pointer,topic_level
  from TOPIC
 where topic_pointer not in (select topic_id from TOPIC);
--
--
-- integrity constraint of level hierarchy: level(TOPIC1) = level(TOPIC) - 1
-- = topic_level equals to the previous level incremented by 1.
-- here only the root topic is an allowed result
-- 
SELECT  TOPIC.topic_id,substr( TOPIC.topic_name,1,30), TOPIC.topic_level,
       TOPIC1.topic_id,substr(TOPIC1.topic_name,1,25),TOPIC1.topic_level
  from TOPIC,TOPIC1
 where TOPIC.topic_pointer    =  TOPIC1.topic_id
   and TOPIC.topic_level - 1  <> TOPIC1.topic_level;
-- =====================================================================
-- 
-- integrity constraint: all location_pointers must refer to existing location_ids
-- = location_level equals to the previous level incremented by 1.
-- here no location is an allowed result
--
SELECT location_id,location_name,location_pointer,location_level
  from LOCATION
 where location_pointer not in (select location_id from LOCATION);
--
--
-- integrity constraint of level hierarchy: level(LOCATION1) = level(LOCATION) - 1
-- here only the root location is an allowed result
--
SELECT  LOCATION.location_id,substr( LOCATION.location_name,1,30), LOCATION.location_level,
       LOCATION1.location_id,substr(LOCATION1.location_name,1,25),LOCATION1.location_level
  from LOCATION,LOCATION1
 where LOCATION.location_pointer    =  LOCATION1.location_id
   and LOCATION.location_level - 1  <> LOCATION1.location_level;
-- ---------------------------------------------------------------------
spool off
