-- SQL script for generation of CERA Core 2.4
--     here: institute specific views and grants
-- ----------------------------------------------
-- 
-- first some handy views to facilitate queries and display:
--   (some of them are used for the CERA ORACLE Forms at PIK)
-- ---------------------------------------------------------
--
-- View to get the records of table CITATION more concentrated
create or replace view CITAT as
select LTRIM(REPLACE(authors||':'||title||' ('||publication_date||') '||publication,'n/a')) c_name,
       citation_id, title, authors, publication, publisher, editor, publication_date, country,
       state, place, edition, access_spec, CITATION.presentation_id, presentation_descr,
       CITATION.citation_type_id, citation_type
  from CITATION, CITATION_TYPE, PRESENTATION
 where CITATION.presentation_id    = PRESENTATION.presentation_id
   and CITATION.citation_type_id   = CITATION_TYPE.citation_type_id;       
--
-- View to get the records of table INSTITUTE more concentrated
create or replace view INSTI as
select institute_name||'-'||department_name||' ('||country||' '||place||')' i_name,
       institute_id, institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, street, street_postal_code, 
       pobox, pobox_postal_code, url, additional_info
  from INSTITUTE;
--
-- View to get the records of table PERSON more concentrated
create or replace view PERSO as
select LTRIM(REPLACE(title||' '||first_name||' '||last_name||' ('||institute_acronym||')','n/a ')) p_name,
       LTRIM(REPLACE(last_name||', '||title||' '||first_name||' ('||institute_acronym||')','n/a ')) pl_name,
       person_id, first_name, second_name, last_name, title, PERSON.institute_id, telephone, fax, email, 
       PERSON.url, institute_acronym, i_name
  from PERSON, INSTI
 where INSTI.institute_id    = PERSON.institute_id;
--
-- View to get the records of table SPATIAL_COVERAGE more concentrated
create or replace view SPATI_COVER as
select 'N:'||max_lat||' S:'||min_lat||' E:'||max_lon||' W:'||min_lon NSEWbound,
             max_lat|| ';' ||min_lat|| ';' ||max_lon|| ';' ||min_lon hor_bound,
       min_lat, max_lat, min_lon, max_lon,
       'u:'||max_altitude||' '||UNIT2.unit_acronym||' l:'||min_altitude||' '||UNIT1.unit_acronym ULbound,
             max_altitude||' '||UNIT2.unit_acronym|| ';' ||min_altitude||' '||UNIT1.unit_acronym ver_bound,
       min_altitude, UNIT1.unit_acronym min_alt_unit_acr, UNIT1.unit_id min_alt_unit_id,
       max_altitude, UNIT2.unit_acronym max_alt_unit_acr, UNIT2.unit_id max_alt_unit_id,
       spatial_coverage_id
  from SPATIAL_COVERAGE, UNIT1, UNIT2
 where min_alt_unit_id               = UNIT1.unit_id
   and max_alt_unit_id               = UNIT2.unit_id;
--
-- View to get the records of table TEMPORAL_COVERAGE more concentrated
create or replace view TEMPO_COVER as
select start_year||'.'||start_month||'.'||start_day start_date,
       stop_year ||'.'||stop_month ||'.'||stop_day  stop_date,
       start_year, start_month, start_day, stop_year, stop_month, stop_day, 
       TEMPORAL_COVERAGE.currentness_ref_id, currentness_ref_descr,
       temporal_coverage_id
  from TEMPORAL_COVERAGE, CURRENTNESS_REF
 where TEMPORAL_COVERAGE.currentness_ref_id = CURRENTNESS_REF.currentness_ref_id;
-- ----------------------------------------------
create or replace view INVESTIGATOR as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=1 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view ORIGINATOR as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=2 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view DISTRIBUTOR as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=3 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view TECHNICAL_CON as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=4 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view METADATA_CON as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=5 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view COPYRIGHT_CON as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=6 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view OWNER as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=7 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- -----------------------------------
create or replace view INHOUSE_CON as
 select
       entry_id, 
       LTRIM(REPLACE(title||' '||first_name||' '||last_name,'n/a ')) name,
       first_name, last_name, title, telephone,    
       institute_name||'-'||department_name||', '||place iname,
       institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, 
       street, street_postal_code, pobox, pobox_postal_code, 
       institute.url, additional_info
   from 
       CONTACT, PERSON, INSTITUTE
  where
       contact_type_id=8 and
       contact.institute_id = institute.institute_id and
       contact.person_id    = person.person_id;
-- ------------------------------- 

-- Grants
grant SELECT on CAMPAIGN          to PUBLIC with grant option;
grant SELECT on KEY_CONNECT       to PUBLIC with grant option;
grant SELECT on ENTRY_CONNECT     to PUBLIC with grant option;
grant SELECT on REFERENCE         to PUBLIC with grant option;
grant SELECT on CONTACT           to PUBLIC with grant option;
grant SELECT on LOCATION_CONNECT  to PUBLIC with grant option;
grant SELECT on COVERAGE          to PUBLIC with grant option;
grant SELECT on PARAMETER         to PUBLIC with grant option;
grant SELECT on SPATIAL_REFERENCE to PUBLIC with grant option;

grant SELECT on SPATIAL_COVERAGE  to PUBLIC with grant option;
grant SELECT on TEMPORAL_COVERAGE to PUBLIC with grant option;
grant SELECT on CITATION          to PUBLIC with grant option;
grant SELECT on DISTRIBUTION      to PUBLIC with grant option;
grant SELECT on PROCESS_STEP      to PUBLIC with grant option;
grant SELECT on ENTRY             to PUBLIC with grant option;
grant SELECT on SUMMARY           to PUBLIC with grant option;
grant SELECT on QUALITY           to PUBLIC with grant option;

grant SELECT on LOCATION          to PUBLIC with grant option;

grant SELECT on UNIT              to PUBLIC with grant option;
grant SELECT on PROJECT           to PUBLIC with grant option;
grant SELECT on PERSON            to PUBLIC with grant option;
grant SELECT on INSTITUTE         to PUBLIC with grant option;
grant SELECT on GENERAL_KEY       to PUBLIC with grant option;

grant SELECT on ACCESS_CONSTRAINT to PUBLIC with grant option;
grant SELECT on ACCESS_TYPE       to PUBLIC with grant option;
grant SELECT on AGGREGATION       to PUBLIC with grant option;
grant SELECT on CITATION_TYPE     to PUBLIC with grant option;
grant SELECT on CONNECT_TYPE      to PUBLIC with grant option;
grant SELECT on CONTACT_TYPE      to PUBLIC with grant option;
grant SELECT on CURRENTNESS_REF   to PUBLIC with grant option;
grant SELECT on ENTRY_TYPE        to PUBLIC with grant option;
grant SELECT on FEES              to PUBLIC with grant option;
grant SELECT on FORMAT            to PUBLIC with grant option;
grant SELECT on HOR_COORD_SYS     to PUBLIC with grant option;
grant SELECT on PRESENTATION      to PUBLIC with grant option;
grant SELECT on PROGRESS          to PUBLIC with grant option;
grant SELECT on REF_TYPE          to PUBLIC with grant option;
grant SELECT on SPATIAL_DATA_ORG  to PUBLIC with grant option;
grant SELECT on TOPIC             to PUBLIC with grant option;
grant SELECT on USE_CONSTRAINT    to PUBLIC with grant option;
grant SELECT on VER_COORD_SYS     to PUBLIC with grant option;

-- Grants on views
grant SELECT on INSTI             to PUBLIC with grant option;
grant SELECT on PERSO             to PUBLIC with grant option;
grant SELECT on INVESTIGATOR      to PUBLIC with grant option;
grant SELECT on ORIGINATOR        to PUBLIC with grant option;
grant SELECT on DISTRIBUTOR       to PUBLIC with grant option;
grant SELECT on TECHNICAL_CON     to PUBLIC with grant option;
grant SELECT on METADATA_CON      to PUBLIC with grant option;
grant SELECT on COPYRIGHT_CON     to PUBLIC with grant option;
grant SELECT on OWNER             to PUBLIC with grant option;
grant SELECT on INHOUSE_CON       to PUBLIC with grant option;

-- Grants on Modules: DATA_ORG
grant SELECT on DATA_ORG          to PUBLIC with grant option;
grant SELECT on SPACE             to PUBLIC with grant option;
grant SELECT on POINT_SET         to PUBLIC with grant option;
grant SELECT on SCALE             to PUBLIC with grant option;
grant SELECT on TIME              to PUBLIC with grant option;
grant SELECT on POINT_CONNECT     to PUBLIC with grant option;
grant SELECT on SCALE_CONNECT     to PUBLIC with grant option;
grant SELECT on TIME_CONNECT      to PUBLIC with grant option;
grant SELECT on DIM_TYPE          to PUBLIC with grant option;
grant SELECT on POINT             to PUBLIC with grant option;
grant SELECT on POSITION          to PUBLIC with grant option;
grant SELECT on MOMENT            to PUBLIC with grant option;
-- Grants on Modules: DATA_ACCESS
grant SELECT on DATA_ACCESS       to PUBLIC with grant option;
grant SELECT on ACCESS_STRUCTURE  to PUBLIC with grant option;
grant SELECT on STORAGE           to PUBLIC with grant option;
grant SELECT on REC_STRUCTURE     to PUBLIC with grant option;

-- that's it
