-- SQL script for generation of some handy readonly-views of CERA Core 2.3
--     used at PIK by the ORACLEform "readMDB.fmb".
-- -------------------------------------------------------------------------
create or replace view READ_ENTRY as
select ENTRY.entry_id, entry_name, entry_acronym,
       creation_date, review_date, future_review_date,
       project_acronym, progress_descr, summary, entry_type_descr,
       accuracy_report, consistency_report, completeness_report,
       horizontal_acc_report, vertical_acc_report
  from ENTRY, CAMPAIGN, PROJECT, PROGRESS, SUMMARY, QUALITY, ENTRY_TYPE
 where ENTRY.entry_id      = CAMPAIGN.entry_id (+)
   and   CAMPAIGN.project_id = PROJECT.project_id (+)
   and ENTRY.progress_id   = PROGRESS.progress_id (+)
   and ENTRY.summary_id    = SUMMARY.summary_id (+)
   and ENTRY.quality_id    = QUALITY.quality_id (+)
   and ENTRY.entry_type_id = ENTRY_TYPE.entry_type_id (+)
       ;
grant  SELECT on READ_ENTRY to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_CONTA as
select entry_name, ENTRY.entry_id, contact_type, 
       institute_acronym||'-'||department_acronym||': '||place ||', '||country||
                       '; '||institute_name||' - '||street    institute_info,
       INSTITUTE.institute_id,
       last_name||', '||first_name||', '||institute_acronym      person_info,
       PERSON.person_id
  from ENTRY, CONTACT, CONTACT_TYPE, PERSON, INSTITUTE
 where CONTACT.contact_type_id = CONTACT_TYPE.contact_type_id (+)
   and CONTACT.person_id       = PERSON.person_id (+)
   and CONTACT.institute_id    = INSTITUTE.institute_id (+)
   and ENTRY.entry_id          = CONTACT.entry_id
       ;
grant  SELECT on READ_CONTA to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_REFER as
select entry_name, ENTRY.entry_id, ref_type_descr, 
       authors||': '||title||'('||citation_type||', '||publisher||
                                  '), '||publication           citation_info,
       CITATION.citation_id
  from ENTRY, REFERENCE, REF_TYPE, CITATION, CITATION_TYPE, PRESENTATION
 where REFERENCE.ref_type_id   = REF_TYPE.ref_type_id (+)
   and REFERENCE.citation_id   = CITATION.citation_id (+)
   and   CITATION.citation_type_id = CITATION_TYPE.citation_type_id (+)
   and   CITATION.presentation_id  = PRESENTATION.presentation_id (+)
   and ENTRY.entry_id          = REFERENCE.entry_id
       ;
grant  SELECT on READ_REFER to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_PARAM as
select entry_name, PARAMETER.entry_id, 
       topic_name, unit_name, aggregation_descr, reference_method,
       modification_date, access_structure_descr,
       STORAGE1.storage_name storage1,  STORAGE2.storage_name storage2,
       STORAGE3.storage_name storage3,  STORAGE4.storage_name storage4
  from ENTRY, PARAMETER, TOPIC, UNIT, AGGREGATION, SPATIAL_DATA_ORG,
       DATA_ACCESS, ACCESS_STRUCTURE,
       STORAGE1, STORAGE2, STORAGE3, STORAGE4
 where PARAMETER.topic_id      = TOPIC.topic_id (+)
   and PARAMETER.unit_id       = UNIT.unit_id (+)
   and PARAMETER.aggregation_id= AGGREGATION.aggregation_id (+)
   and PARAMETER.spatial_data_org_id = spatial_data_org.spatial_data_org_id (+)
   and PARAMETER.data_access_id      = DATA_ACCESS.data_access_id
   and DATA_ACCESS.access_structure_id = ACCESS_STRUCTURE.access_structure_id (+)
   and DATA_ACCESS.storage1_id       = STORAGE1.storage_id (+)
   and DATA_ACCESS.storage2_id       = STORAGE2.storage_id (+)
   and DATA_ACCESS.storage3_id       = STORAGE3.storage_id (+)
   and DATA_ACCESS.storage4_id       = STORAGE4.storage_id (+)
   and ENTRY.entry_id          = PARAMETER.entry_id
       ;
grant  SELECT on READ_PARAM to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_COVER as
select entry_name, ENTRY.entry_id,
       min_lat, max_lat, min_lon, max_lon,
       min_altitude,   UNIT1.unit_acronym  min_alt_unit, 
       max_altitude,   UNIT2.unit_acronym  max_alt_unit,
       start_year, start_month, start_day,
        stop_year,  stop_month,  stop_day,
       currentness_ref_descr
  from ENTRY, COVERAGE, SPATIAL_COVERAGE, TEMPORAL_COVERAGE, CURRENTNESS_REF, UNIT1, UNIT2
 where COVERAGE.spatial_coverage_id  = SPATIAL_COVERAGE.spatial_coverage_id (+)
   and   SPATIAL_COVERAGE.min_alt_unit_id     = UNIT1.unit_id (+)
   and   SPATIAL_COVERAGE.max_alt_unit_id     = UNIT2.unit_id (+)
   and COVERAGE.temporal_coverage_id = TEMPORAL_COVERAGE.temporal_coverage_id (+)
   and   TEMPORAL_COVERAGE.currentness_ref_id = CURRENTNESS_REF.currentness_ref_id (+)
   and ENTRY.entry_id          = COVERAGE.entry_id
       ;
grant  SELECT on READ_COVER to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_LOCAT as
select entry_name, ENTRY.entry_id,
       location_name, min_lat, max_lat, min_lon, max_lon
  from ENTRY, LOCATION_CONNECT, LOCATION
 where LOCATION_CONNECT.location_id  = LOCATION.location_id (+)
   and ENTRY.entry_id          = LOCATION_CONNECT.entry_id
       ;
grant  SELECT on READ_LOCAT to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_DISTR as
select entry_name, ENTRY.entry_id, data_size, format_acronym, access_acronym, fees_acronym, 
       ACCESS_CONSTRAINT.constraint_descr   access_constraint_descr, 
          USE_CONSTRAINT.constraint_descr      use_constraint_descr
  from ENTRY, DISTRIBUTION, FORMAT, ACCESS_TYPE, FEES, ACCESS_CONSTRAINT, USE_CONSTRAINT
 where DISTRIBUTION.format_id        = FORMAT.format_id (+)
   and DISTRIBUTION.access_type_id   = ACCESS_TYPE.access_type_id (+)
   and DISTRIBUTION.fees_id          = FEES.fees_id (+)
   and DISTRIBUTION.access_constraint_id = ACCESS_CONSTRAINT.access_constraint_id (+)
   and DISTRIBUTION.use_constraint_id= USE_CONSTRAINT.use_constraint_id (+)
   and ENTRY.entry_id                = DISTRIBUTION.entry_id
       ;
grant  SELECT on READ_DISTR to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_SPATI as
select entry_name, ENTRY.entry_id, HOR_COORD_SYS.sys_descr hor_sys_descr, 
                 VER_COORD_SYS.sys_descr ver_sys_descr
  from ENTRY, SPATIAL_REFERENCE, HOR_COORD_SYS, VER_COORD_SYS
 where SPATIAL_REFERENCE.hor_coord_sys_id = HOR_COORD_SYS.hor_coord_sys_id (+)
   and SPATIAL_REFERENCE.ver_coord_sys_id = VER_COORD_SYS.ver_coord_sys_id (+)
   and ENTRY.entry_id                     = SPATIAL_REFERENCE.entry_id
       ;
grant  SELECT on READ_SPATI to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_PROCE as
select entry_name, ENTRY.entry_id, process_descr, process_date,
       last_name||', '||first_name||', '||institute_acronym person_info,
       PERSON.person_id
  from ENTRY, PROCESS_STEP, PERSON, INSTITUTE
 where PROCESS_STEP.person_id        = PERSON.person_id (+)
   and   PERSON.institute_id           = INSTITUTE.institute_id (+)
   and ENTRY.entry_id                = PROCESS_STEP.entry_id
       ;
grant  SELECT on READ_PROCE to PUBLIC;
-- -------------------------------------------------------------------------
create or replace view READ_KEY_C as
select entry_name, ENTRY.entry_id, general_key, GENERAL_KEY.general_key_id
  from ENTRY, KEY_CONNECT, GENERAL_KEY
 where KEY_CONNECT.general_key_id    = GENERAL_KEY.general_key_id
   and ENTRY.entry_id                = KEY_CONNECT.entry_id
       ;
grant  SELECT on READ_KEY_C to PUBLIC;
-- -------------------------------------------------------------------------
-- the following view is only for CERA Module MODEL_INPUT ------------------
create or replace view READ_MODEL as
select entry_name,          ENTRY.entry_id, 
       model_type_acronym,  model_type_name,   model_type_descr,
       model_acronym,            model_name,        model_descr,
       topic_acronym,       value_number,          unit_acronym, 
       value_string,        calculation_start
  from ENTRY, MODEL_INPUT, MODEL, MODEL_TYPE, TOPIC, UNIT
 where ENTRY.entry_id                = MODEL_INPUT.entry_id
   and TOPIC.topic_id                = MODEL_INPUT.topic_id
   and UNIT.unit_id                  = MODEL_INPUT.unit_id
   and MODEL_INPUT.model_id          = MODEL.model_id
   and MODEL.model_type_id           = MODEL_TYPE.model_type_id
       ;
grant  SELECT on READ_MODEL to PUBLIC;
