-- -------------------------------------------------------------
-- -------------------------------------------------------------
-- loadCHECK.sql:
-- This SQL script is to check the content of the loading layer
--   (=tables NEW_* and LOAD_*).
--   It should be run before the data is transferred to the CERA
--   tables (by loadCHECKED*.sql).
-- Errors are grouped in levels as:
--   INFormation: just info
--   WARnings:    something "not nice" happened
--   ERRors:      something "unlawful" happened
--   FATals:      something "unlawful" happened, that should have 
--                been prevented by CERA. This leads to the 
--                suspicion, that your CERA installation might 
--                be corrupt; check carefully for the reason!
--                                                ft, 1999.07.15
-- -------------------------------------------------------------
-- -------------------------------------------------------------
-- -------------------------------------------------------------
--
-- A: PREREQUISITES
-- ----------------
-- A.0: starting log
spool loadCHECK
--
-- A.1: deleting old information:
drop table LOAD_ERROR_MESSAGES;
drop table LOAD_ERROR_LIST;
--
-- A.2: generating the list of possible errors to look for
create table LOAD_ERROR_LIST (
             err             varchar(6)     primary key, 
             explanation     varchar(250));
--
insert into LOAD_ERROR_LIST values ('INFLIN',
 'this line is an informatory entry only');
insert into LOAD_ERROR_LIST values ('WARNUL',
 'a NULL value does not make sense here; it will be replaced by 0, >not filled< or SEQ.nextval');
insert into LOAD_ERROR_LIST values ('ERRNUL',
 'a NULL value does not make sense here; check manually');
insert into LOAD_ERROR_LIST values ('FATNUL',
 'a NULL value should not appear here; column should be protected against NULL input by >NOT NULL< in the creation statement');
insert into LOAD_ERROR_LIST values ('WARDUB',
 'a similar item already exists; probably it does not make sense to add this row to the LOV');
insert into LOAD_ERROR_LIST values ('ERRDUB',
 'this item is doubled in this table; delete one version of it');
insert into LOAD_ERROR_LIST values ('FATDUB',
 'these items should not be identical in this table; re-select the item from the pull-down LOV; if the problem remains check your CERA installation for the reason');
insert into LOAD_ERROR_LIST values ('FATILL',
 'an illegal item has been found!');
--
-- A.3: generating the error message table      
create table LOAD_ERROR_MESSAGES (
             stmt_no       number(4),
             entry_id      number(15),
             entry_acronym varchar(31),
             tab_name      varchar(30), 
             err           varchar(6) references LOAD_ERROR_LIST,
             tab_row       varchar(100) );
--
-- A.4: Making one test entry into the error table LOAD_ERROR_MESSAGES
insert into LOAD_ERROR_MESSAGES values(0,0,'none','none','INFLIN','this line does not indicate any error and is just for testing'); 
--
-- A.5: generating a common view of these two tables
create or replace view LOAD_ERRORS as
   select ROWNUM-1 Count, stmt_no,
          substr(LOAD_ERROR_LIST.err,1,3)||'-'||substr(LOAD_ERROR_LIST.err,4,3) error,
          tab_name, tab_row, LOAD_ERROR_LIST.explanation
     from LOAD_ERROR_LIST, LOAD_ERROR_MESSAGES
    where LOAD_ERROR_LIST.err = LOAD_ERROR_MESSAGES.err;
-- -------------------------------------------------------------
-- -------------------------------------------------------------
-- B: CHECKING
-- -----------
-- -------------------------------------------------------------
-- B.1: CHECKING the NEW_* contents (for comments see the subfiles)
-- --------------------------------
@loadCHECKnew_id ACCESS_CONSTRAINT
@loadCHECKnew_ch ACCESS_CONSTRAINT CONSTRAINT_DESCR
-- - - - - - - - -
@loadCHECKnew_id ACCESS_STRUCTURE
@loadCHECKnew_ch ACCESS_STRUCTURE ACCESS_STRUCTURE_NAME
-- unchecked:    ACCESS_STRUCTURE_DESCR
-- - - - - - - - -
@loadCHECKnew_id ACCESS_TYPE
@loadCHECKnew_ch ACCESS_TYPE ACCESS_ACRONYM
-- unchecked:    ACCESS_DESCR
-- - - - - - - - -
@loadCHECKnew_id AGGREGATION
@loadCHECKnew_ch AGGREGATION AGGREGATION_DESCR
-- - - - - - - - -
@loadCHECKnew_id CITATION
@loadCHECKnew_ch CITATION TITLE
-- unchecked:    AUTHORS
-- unchecked:    PUBLICATION
-- unchecked:    PUBLISHER
-- unchecked:    EDITOR
-- unchecked:    PUBLICATION_DATE
-- unchecked:    COUNTRY
-- unchecked:    STATE
-- unchecked:    PLACE
-- unchecked:    EDITION
-- unchecked:    ACCESS_SPEC
@loadCHECKnew_in CITATION presentation_descr PRESENTATION
@loadCHECKnew_in CITATION citation_type CITATION_TYPE
-- - - - - - - - -
@loadCHECKnew_id CITATION_TYPE
@loadCHECKnew_ch CITATION_TYPE CITATION_TYPE
-- unchecked:    CITATION_TYPE_DESCR
-- - - - - - - - -
@loadCHECKnew_id CONNECT_TYPE
@loadCHECKnew_ch CONNECT_TYPE CONNECT_TYPE
-- unchecked:    CONNECT_DESCR
-- - - - - - - - -
@loadCHECKnew_id CONTACT_TYPE
@loadCHECKnew_ch CONTACT_TYPE CONTACT_TYPE
-- - - - - - - - -
@loadCHECKnew_id CURRENTNESS_REF
@loadCHECKnew_ch CURRENTNESS_REF currentness_ref_descr
-- - - - - - - - -
@loadCHECKnew_id ENTRY_TYPE
@loadCHECKnew_ch ENTRY_TYPE entry_type
-- unchecked:    ENTRY_TYPE_DESCR
-- - - - - - - - -
@loadCHECKnew_id FEES
@loadCHECKnew_ch FEES fees_acronym
@loadCHECKnew_ch FEES fees_descr
-- - - - - - - - -
@loadCHECKnew_id FORMAT
@loadCHECKnew_ch FORMAT format_acronym
@loadCHECKnew_ch FORMAT format_descr
-- - - - - - - - -
@loadCHECKnew_id GENERAL_KEY
@loadCHECKnew_ch GENERAL_KEY general_key
-- - - - - - - - -
@loadCHECKnew_id HOR_COORD_SYS
@loadCHECKnew_ch HOR_COORD_SYS sys_descr
-- - - - - - - - -
@loadCHECKnew_id INSTITUTE
@loadCHECKnew_nn INSTITUTE institute_name
-- unchecked:    INSTITUTE_ACRONYM
-- unchecked:    DEPARTMENT_NAME
-- unchecked:    DEPARTMENT_ACRONYM
-- unchecked:    COUNTRY
-- unchecked:    STATE_OR_PROVINCE
-- unchecked:    PLACE
-- unchecked:    STREET
-- unchecked:    STREET_POSTAL_CODE
-- unchecked:    POBOX
-- unchecked:    POBOX_POSTAL_CODE
-- unchecked:    URL
-- unchecked:    ADDITIONAL_INFO
-- - - - - - - - -
@loadCHECKnew_id LOCATION
@loadCHECKnew_ch LOCATION location_name
-- unchecked:    LOCATION_DESCR
@loadCHECKnew_pt LOCATION
@loadCHECKnew_nn LOCATION location_level
@loadCHECKnew_nn LOCATION ref_date
@loadCHECKnew_nn LOCATION min_lat
@loadCHECKnew_nn LOCATION max_lat
@loadCHECKnew_nn LOCATION min_lon
@loadCHECKnew_nn LOCATION max_lon
@loadCHECKnew_lc 
-- - - - - - - - -
@loadCHECKnew_id MODEL
@loadCHECKnew_in MODEL model_type_id MODEL_TYPE
@loadCHECKnew_ch MODEL model_acronym
@loadCHECKnew_ch MODEL model_name
-- unchecked:    MODEL_DESCR
-- - - - - - - - -
@loadCHECKnew_id MODEL_TYPE
@loadCHECKnew_ch MODEL_TYPE model_type_acronym
@loadCHECKnew_ch MODEL_TYPE model_type_name
-- unchecked:    MODEL_TYPE model_type_descr
-- - - - - - - - -
@loadCHECKnew_id PERSON
-- unchecked:    FIRST_NAME
-- unchecked:    SECOND_NAME
@loadCHECKnew_nn PERSON LAST_NAME
-- unchecked:    TITLE
@loadCHECKnew_in PERSON institute_id INSTITUTE
-- unchecked:    TELEPHONE
-- unchecked:    FAX
-- unchecked:    URL
-- unchecked:    EMAIL
-- - - - - - - - -
@loadCHECKnew_id PRESENTATION
@loadCHECKnew_ch PRESENTATION presentation_descr
-- - - - - - - - -
@loadCHECKnew_id PROGRESS
-- unchecked:    PROGRESS_DESCR
-- - - - - - - - -
@loadCHECKnew_id PROJECT
@loadCHECKnew_ch PROJECT project_acronym
@loadCHECKnew_ch PROJECT project_name
-- unchecked:    PROJECT_DESCR
-- - - - - - - - -
@loadCHECKnew_id REF_TYPE
@loadCHECKnew_ch REF_TYPE ref_type_descr
-- - - - - - - - -
@loadCHECKnew_id SPATIAL_DATA_ORG
@loadCHECKnew_ch SPATIAL_DATA_ORG reference_method
-- - - - - - - - -
@loadCHECKnew_id STORAGE
@loadCHECKnew_ch STORAGE storage_name
-- - - - - - - - -
-- @loadCHECKnew_id TEST
-- @loadCHECKnew_ch TEST FEES_ACRONYM
-- @loadCHECKnew_ch TEST FEES_DESCR
-- - - - - - - - -
@loadCHECKnew_id TOPIC
@loadCHECKnew_ch TOPIC topic_name
@loadCHECKnew_ch TOPIC topic_acronym
-- unchecked:    TOPIC_DESCR
@loadCHECKnew_pt TOPIC
@loadCHECKnew_nn TOPIC topic_level
-- - - - - - - - -
@loadCHECKnew_id UNIT
@loadCHECKnew_ch UNIT unit_name
@loadCHECKnew_ch UNIT unit_acronym
-- unchecked:    UNIT unit_descr
-- - - - - - - - -
@loadCHECKnew_id USE_CONSTRAINT
@loadCHECKnew_ch USE_CONSTRAINT constraint_descr
-- - - - - - - - -
@loadCHECKnew_id VER_COORD_SYS
@loadCHECKnew_ch VER_COORD_SYS SYS_DESCR
--
--
-- -------------------------------------------------------------
-- B.2: CHECKING the LOAD_* contents (for comments see the subfiles)
-- ---------------------------------
-- LOAD_ENTRY:
@loadCHECKload_id 
@loadCHECKload_nnf ENTRY ENTRY_NAME
@loadCHECKload_nnw ENTRY ENTRY_ACRONYM
@loadCHECKload_nnw ENTRY CREATION_DATE
@loadCHECKload_nnw ENTRY REVIEW_DATE
@loadCHECKload_nnw ENTRY FUTURE_REVIEW_DATE
@loadCHECKload_lch ENTRY PROJECT project_acronym
@loadCHECKload_lch ENTRY PROGRESS progress_descr
@loadCHECKload_nnf ENTRY summary_id
@loadCHECKload_nnw ENTRY SUMMARY
@loadCHECKload_nnf ENTRY quality_id
@loadCHECKload_nnw ENTRY ACCURACY_REPORT
@loadCHECKload_nnw ENTRY CONSISTENCY_REPORT
@loadCHECKload_nnw ENTRY COMPLETENESS_REPORT
@loadCHECKload_nnw ENTRY HORIZONTAL_ACC_REPORT
@loadCHECKload_nnw ENTRY VERTICAL_ACC_REPORT
@loadCHECKload_lch ENTRY ENTRY_TYPE entry_type
-- unchecked:      ENTRY ENTRY_TYPE entry_type_descr
-- LOAD_CONTA:
@loadCHECKload_eid CONTA
@loadCHECKload_lch CONTA CONTACT_TYPE contact_type
@loadCHECKload_lid CONTA INSTITUTE
-- unchecked:      CONTA INSTITUTE_INFO
@loadCHECKload_lid CONTA PERSON
-- unchecked:      CONTA PERSON_INFO
-- LOAD_REFER:
@loadCHECKload_eid REFER
@loadCHECKload_lch REFER REF_TYPE ref_type_descr
@loadCHECKload_lid REFER CITATION
-- unchecked:      REFER CITATION_INFO
-- LOAD_PARAM:
@loadCHECKload_eid PARAM
@loadCHECKload_lch PARAM TOPIC topic_name
@loadCHECKload_lch PARAM UNIT unit_name
@loadCHECKload_lch PARAM AGGREGATION aggregation_descr
@loadCHECKload_lch PARAM SPATIAL_DATA_ORG reference_method
-- unchecked:      PARAM DATA_ACCESS_ID
@loadCHECKload_lch PARAM ACCESS_STRUCTURE access_structure_name
-- unchecked:      PARAM ACCESS_STRUCTURE access_structure_descr
@loadCHECKload_lcx PARAM storage1 STORAGE storage_name
@loadCHECKload_lcx PARAM storage2 STORAGE storage_name
@loadCHECKload_lcx PARAM storage3 STORAGE storage_name
@loadCHECKload_lcx PARAM storage4 STORAGE storage_name
@loadCHECKload_nnw PARAM MODIFICATION_DATE
-- LOAD_COVER:
@loadCHECKload_eid COVER
@loadCHECKload_lch COVER LOCATION location_name
@loadCHECKload_nnf COVER SPATIAL_COVERAGE_ID
@loadCHECKload_nnf COVER MIN_LAT
@loadCHECKload_nnf COVER MAX_LAT
@loadCHECKload_nnf COVER MIN_LON
@loadCHECKload_nnf COVER MAX_LON
-- unchecked:      COVER MIN_ALTITUDE
@loadCHECKload_lcx COVER min_alt_unit UNIT unit_acronym
-- unchecked:      COVER MAX_ALTITUDE
@loadCHECKload_lcx COVER max_alt_unit UNIT unit_acronym
@loadCHECKload_nnf COVER TEMPORAL_COVERAGE_ID
@loadCHECKload_nnf COVER START_YEAR
-- unchecked:      COVER START_MONTH
-- unchecked:      COVER START_DAY
@loadCHECKload_nnf COVER STOP_YEAR
-- unchecked:      COVER STOP_MONTH
-- unchecked:      COVER STOP_DAY
@loadCHECKload_lch COVER CURRENTNESS_REF currentness_ref_descr
-- LOAD_DISTR:
@loadCHECKload_eid DISTR
@loadCHECKload_nnw DISTR data_size
@loadCHECKload_lch DISTR FORMAT format_acronym
@loadCHECKload_lch DISTR ACCESS_TYPE access_acronym
@loadCHECKload_lch DISTR FEES fees_acronym
@loadCHECKload_lcx DISTR access_constraint_descr ACCESS_CONSTRAINT constraint_descr
@loadCHECKload_lcx DISTR use_constraint_descr USE_CONSTRAINT constraint_descr
-- LOAD_SPATI:
@loadCHECKload_eid SPATI
@loadCHECKload_lcx SPATI hor_sys_descr HOR_COORD_SYS sys_descr
@loadCHECKload_lcx SPATI ver_sys_descr VER_COORD_SYS sys_descr
-- LOAD_PROCE:
@loadCHECKload_eid PROCE
@loadCHECKload_nnf PROCE PROCESS_DESCR
@loadCHECKload_nnw PROCE PROCESS_DATE
@loadCHECKload_lid PROCE PERSON
-- unchecked:      PROCE PERSON_INFO
-- LOAD_KEY_C:
@loadCHECKload_eid KEY_C
@loadCHECKload_lch KEY_C GENERAL_KEY general_key
-- LOAD_MODEL:
@loadCHECKload_eid MODEL
@loadCHECKload_lch MODEL MODEL model_acronym
@loadCHECKload_lch MODEL MODEL model_name
@loadCHECKload_lch MODEL MODEL_TYPE model_type_acronym
@loadCHECKload_lch MODEL TOPIC topic_name
@loadCHECKload_nnw MODEL value_number
@loadCHECKload_lch MODEL UNIT unit_name
@loadCHECKload_nnw MODEL value_string
@loadCHECKload_nnw MODEL calculation_start
--
-- -------------------------------------------------------------
-- -------------------------------------------------------------
-- C: AFTERBURNER
-- --------------
-- -------------------------------------------------------------
-- C.1: finally showing results...
select substr(entry_id||': '||entry_acronym,1,30) Entry_id_acronym,
       substr(tab_name,1,15)                 Tab_Name,
       substr(err,1,3)||'-'||substr(err,4,3) Err_Type,
       substr(tab_row,1,70)                  Additional_Info
  from LOAD_ERROR_MESSAGES;
-- C.2: ...commiting the results...
commit;
-- C.3: ...and stopping log
spool off
--
-- The END of loadCHECK
