-- SQL script for generation of CERA views
--     (The "(+)" are not necessary, when no NULL fields are present)
-- --------------------------------------------------------------- 1: GET_ENTRY
create or replace view GET_ENTRY as
select ENTRY.entry_id, entry_name, entry_acronym,
       creation_date, review_date, future_review_date,
       progress_descr, summary, entry_type,
       accuracy_report, consistency_report, completeness_report,
       horizontal_acc_report, vertical_acc_report
  from ENTRY, PROGRESS, SUMMARY, ENTRY_TYPE, QUALITY
 where 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 GET_ENTRY to PUBLIC;
-- --------------------------------------------------------------- 2: GET_CONNE
create or replace view GET_CONNE as
select ENTRY.entry_id  id1, ENTRY.entry_name  name1, ENTRY.entry_acronym acro1,
       ENTRY1.entry_id id2, ENTRY1.entry_name name2, ENTRY1.entry_acronym acro2,
       ENTRY1.creation_date,  connect_type,   entry_type
  from ENTRY, ENTRY1, ENTRY_CONNECT, CONNECT_TYPE, ENTRY_TYPE
 where ENTRY.entry_id              = ENTRY_CONNECT.gen_entry_id
   and ENTRY_CONNECT.spec_entry_id = ENTRY1.entry_id    
   and ENTRY1.entry_type_id        = ENTRY_TYPE.entry_type_id
   and ENTRY_CONNECT.connect_type_id = CONNECT_TYPE.connect_type_id
       ;
grant  SELECT on GET_CONNE to PUBLIC;
-- --------------------------------------------------------------- 3: GET_KEY_C
create or replace view GET_KEY_C as
select entry_id, general_key
  from KEY_CONNECT, GENERAL_KEY
 where KEY_CONNECT.general_key_id    = GENERAL_KEY.general_key_id
       ;
grant  SELECT on GET_KEY_C to PUBLIC;
-- --------------------------------------------------------------- 4: GET_CAMPA
create or replace view GET_CAMPA as
select entry_id, project_acronym, project_name
  from CAMPAIGN, PROJECT
 where CAMPAIGN.project_id    = PROJECT.project_id
       ;
grant  SELECT on GET_CAMPA to PUBLIC;
-- --------------------------------------------------------------- 5: GET_PARAM
create or replace view GET_PARAM as
select entry_id, topic_name, topic_acronym, unit_name, unit_acronym, 
       aggregation_descr, reference_method
  from PARAMETER, TOPIC, UNIT, AGGREGATION, SPATIAL_DATA_ORG
 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 (+)
       ;
grant  SELECT on GET_PARAM to PUBLIC;
-- --------------------------------------------------------------- 6: GET_REFER
create or replace view GET_REFER as
select entry_id, ref_type_descr, title, authors, publication, publisher, editor,
       publication_date, country, state, place, edition, access_spec,
       presentation_descr, citation_type_descr, 
       authors||': '||title||'('||citation_type||', '||publisher||
                                  '), '||publication           citation_info
  from 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 (+)
       ;
grant  SELECT on GET_REFER to PUBLIC;
-- --------------------------------------------------------------- 7: GET_CONTA
create or replace view GET_CONTA as
select entry_id, contact_type, 
       INSTITUTE.institute_acronym||'-'||INSTITUTE.department_acronym||
         ' ('||INSTITUTE.place ||')' institute_info,
       INSTITUTE.institute_name, INSTITUTE.department_name, INSTITUTE.country, 
       INSTITUTE.place, INSTITUTE.street, INSTITUTE.street_postal_code, 
       INSTITUTE.pobox, INSTITUTE.pobox_postal_code, INSTITUTE.url inst_url, 
       first_name||' '||last_name||' ('||INSTITUTE1.institute_acronym||')' person_info,
       second_name, title, PERSON.url pers_url, telephone, email
  from CONTACT, CONTACT_TYPE, PERSON, INSTITUTE, INSTITUTE1
 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 PERSON.institute_id     = INSTITUTE1.institute_id (+)
       ;
grant  SELECT on GET_CONTA to PUBLIC;
-- --------------------------------------------------------------- 8: GET_COVER
create or replace view GET_COVER as
select COVERAGE.entry_id,
       SPATIAL_COVERAGE.min_lat, SPATIAL_COVERAGE.max_lat, 
       SPATIAL_COVERAGE.min_lon, SPATIAL_COVERAGE.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 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 (+)
       ;
grant  SELECT on GET_COVER to PUBLIC;
-- --------------------------------------------------------------- 9: GET_LOCAT
create or replace view GET_LOCAT as
select LOCATION_CONNECT.entry_id, 
       location_name, ref_date, min_lat, max_lat, min_lon, max_lon
  from LOCATION_CONNECT, LOCATION
 where LOCATION_CONNECT.location_id = LOCATION.location_id
       ;
grant  SELECT on GET_LOCAT to PUBLIC;
-- -------------------------------------------------------------- 10: GET_SPATI
create or replace view GET_SPATI as
select entry_id, HOR_COORD_SYS.sys_descr hor_sys_descr, 
                 VER_COORD_SYS.sys_descr ver_sys_descr
  from 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 (+)
       ;
grant  SELECT on GET_SPATI to PUBLIC;
-- -------------------------------------------------------------- 11: GET_DISTR
create or replace view GET_DISTR as
select 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 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 (+)
       ;
grant  SELECT on GET_DISTR to PUBLIC;
-- -------------------------------------------------------------- 12: GET_PROCE
create or replace view GET_PROCE as
select entry_id, process_descr, process_date,
       last_name||', '||first_name||', '||institute_acronym person_info
  from PROCESS_STEP, PERSON, INSTITUTE
 where PROCESS_STEP.person_id        = PERSON.person_id (+)
   and   PERSON.institute_id           = INSTITUTE.institute_id (+)
       ;
grant  SELECT on GET_PROCE to PUBLIC;
-- -------------------------------------------------------------- 13: GET_DACCE
create or replace view GET_DACCE as
select distinct entry_id, topic_acronym, modification_date, access_structure_descr,
       STORAGE1.storage_name storage1,  STORAGE2.storage_name storage2, 
       STORAGE3.storage_name storage3,  STORAGE4.storage_name storage4
  from PARAMETER, TOPIC, DATA_ACCESS, ACCESS_STRUCTURE,
       STORAGE1, STORAGE2, STORAGE3, STORAGE4
 where PARAMETER.topic_id            = TOPIC.topic_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 (+)
       ;
grant  SELECT on GET_DACCE to PUBLIC;
