Rem
Rem $Header: sdocswmd.sql 14-dec-2007.14:02:18 rchatter Exp $
Rem
Rem sdocswmd.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdocswmd.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rchatter    01/03/06 - add priv to spatial_csw_admin role 
Rem    rchatter    10/18/05 - add record type id seq 
Rem    rchatter    10/10/05 - modify record metadata
Rem    rchatter    08/22/05 - record view map 
Rem    rchatter    08/15/05 - add record type mappings table 
Rem    rchatter    08/10/05 - add brief/summary metadata 
Rem    rchatter    07/15/05  - add cache syn related metadata tables 
Rem    rchatter    07/12/05  - modify metadata tables 
Rem    rchatter    06/09/05 -  add timestamp path metadata 
Rem    rchatter    05/12/05 - rchatter_csw_impl_txn_1
Rem    rchatter    05/03/05 - Created
Rem


declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RECORD_TYPES$';
  if(rec_count = 0) then
    stmt := 'create table MDSYS.CSW_Record_Types$( RecordTypeId number primary key, 
		  TypeName     varchar2(80),
		  TypeNameNS     varchar2(2083),
		  describeRecord XMLType,
		  datapointer varchar2(61),
		  idPaths MDSYS.StringList, 
		  spatialPaths MDSYS.StringList,
		  tsPaths MDSYS.StringList, 
		  lastUpdateTS timestamp(6),
		  briefXSLPattern XMLType DEFAULT NULL, 
		  summaryXSLPattern XMLType DEFAULT NULL, 
		  dcmiXSLPattern XMLType DEFAULT NULL, 
		  srsPaths MDSYS.StringList, 
		  srsNs varchar2(2083), 
                  srsNsAlias varchar2(80),
		  rtXSDRefId varchar2(80), 
		  numCols mdsys.StringList ,
                  idxPaths mdsys.StringList ,
                  idxPathTypes mdsys.StringList,
		  xtidxCreated     varchar2(1),
		  idExtractorType varchar2(20) CONSTRAINT sdo_csw_idExtType_1 CHECK ( idExtractorType IN (''XPATH'', ''USER_FUNC'', ''NONE'')),
		  isGML3 varchar2(1), 
		  spatialArrList mdsys.StringListList, 
		  spatialPathsSRSNSList mdsys.StringListList,
                  spatialArrPathsSRSNSList mdsys.StringListList) ';
    execute immediate stmt;
    begin
    stmt := ' alter table  MDSYS.CSW_Record_Types$  add constraint unique_csw_rt_cond UNIQUE (TypeNameNS, TypeName) ';
    execute immediate stmt;
    exception when others then null; end;
  end if;
end;
/




declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_DOMAININFO$';
  if(rec_count = 0) then
     stmt := ' create table MDSYS.CSW_DomainInfo$ (RecordTypeId number references MDSYS.CSW_Record_Types$(recordTypeId) on delete cascade, 	
		PropertyName varchar2 (100),
    	        ParameterName varchar2 (100),
		pValue MDSYS.StringList) ';
     execute immediate stmt;
  end if;
end;
/


declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_CAPABILITIESINFO$';
  if(rec_count = 0) then
     stmt := 'create table  MDSYS.CSW_CapabilitiesInfo$ (updateSequence number, 
		  CAPABILITIESINFOTMPL XMLType,  -- template
		  LASTCAPABILITYINFOUPDTS timestamp(6))';
   execute immediate stmt;
  end if;
end;
/


declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_HARVESTMETADATA$';
  if(rec_count = 0) then
     stmt := 'create table  MDSYS.CSW_HarvestMetadata$ (harvestReqId number, 
		     harvestInfo XMLType)';
     execute immediate stmt;
  end if;
end;
/


declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RECORDTABLEMDUPDATED$';
  if(rec_count = 0) then
    stmt :=
        'create table MDSYS.CSW_recordTableMDUpdated$(
                           recordTypeNSUrl varchar2(2083),
                           recordTypeName varchar2(80),
                           updateTS timestamp(6)) ';
    execute immediate stmt;
  end if;
end;
/



declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RECORDTABLEUPDATED$';
  if(rec_count = 0) then
    stmt :=
        'create table MDSYS.CSW_recordTableUpdated$(
                           recordTypeNSUrl varchar2(2083),
                           recordTypeName varchar2(80),
			   rowList RowPointerList,
                           updateTS timestamp(6))  nested table rowList store as ro_table ';
    execute immediate stmt;
  end if;
end;
/

declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RECORDDOMAINUPDATED$';
  if(rec_count = 0) then
    stmt :=
        'create table MDSYS.CSW_recordDomainUpdated$(
                           recordTypeId number,
                           updateTS timestamp(6)) ';
    execute immediate stmt;
  end if;
end;
/


declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RECORDVIEWMAP$';
  if(rec_count = 0) then
    stmt :=
        'create table MDSYS.CSW_RECORDVIEWMAP$ (
                           recordTypeNS varchar2(2083),
                           viewSrcName varchar2(80),
                           targetTypeName varchar2(80),
			   mapInfo XMLType,
			   mapType varchar2(20)) ';
    execute immediate stmt;
  end if;
end;
/

declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_RTXSDINFO$';
  if(rec_count = 0) then
    stmt :=
        'create table MDSYS.CSW_rtXSDInfo$(
                           rtXSDRefId varchar2(80) primary key,
                           rtDef XMLType) ';
    execute immediate stmt;
  end if;
end;
/

declare
  stmt VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'CSW_PLUGIN_MAP$';
  if(rec_count = 0) then
    stmt :=
        'create table mdsys.csw_plugin_map$ (typeNS varchar2(2083), typeName varchar2(80), pluginname  varchar2(1000))';                       
    execute immediate stmt;
  end if;
end;
/

show errors;


grant all on  MDSYS.CSW_RECORD_TYPES$ to spatial_csw_admin;
grant all on  MDSYS.CSW_DOMAININFO$ to spatial_csw_admin;
grant all on  MDSYS.CSW_CAPABILITIESINFO$ to spatial_csw_admin;
grant all on  MDSYS.CSW_HARVESTMETADATA$ to spatial_csw_admin;
grant all on  MDSYS.CSW_RECORDTABLEMDUPDATED$ to spatial_csw_admin;
grant all on  MDSYS.CSW_RECORDTABLEUPDATED$ to spatial_csw_admin;
grant all on  MDSYS.CSW_RECORDDOMAINUPDATED$ to spatial_csw_admin;
grant all on  MDSYS.CSW_RECORDVIEWMAP$ to spatial_csw_admin;
grant all on  MDSYS.CSW_rtXSDInfo$ to spatial_csw_admin;
grant all on  MDSYS.CSW_PLUGIN_MAP$ to spatial_csw_admin;
grant all on MDSYS.USER_SDO_GEOM_METADATA to spatial_csw_admin;

grant all on  MDSYS.CSW_RECORD_TYPES$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_DOMAININFO$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_CAPABILITIESINFO$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_HARVESTMETADATA$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_RECORDTABLEMDUPDATED$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_RECORDTABLEUPDATED$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_RECORDDOMAINUPDATED$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_RECORDVIEWMAP$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_rtXSDInfo$ to spatial_csw_admin_usr;
grant all on  MDSYS.CSW_PLUGIN_MAP$ to spatial_csw_admin_usr;
grant all on MDSYS.USER_SDO_GEOM_METADATA to spatial_csw_admin_usr;




grant select on  MDSYS.CSW_RECORD_TYPES$ to csw_usr_role;
grant select on  MDSYS.CSW_DOMAININFO$ to csw_usr_role;
grant select on  MDSYS.CSW_CAPABILITIESINFO$ to csw_usr_role;
grant select on  MDSYS.CSW_HARVESTMETADATA$ to csw_usr_role;
grant select on  MDSYS.CSW_RECORDTABLEMDUPDATED$ to csw_usr_role;
grant select on  MDSYS.CSW_RECORDTABLEUPDATED$ to csw_usr_role;
grant select on  MDSYS.CSW_RECORDDOMAINUPDATED$ to csw_usr_role;
grant select on  MDSYS.CSW_RECORDVIEWMAP$ to csw_usr_role;
grant select on  MDSYS.CSW_rtXSDInfo$ to csw_usr_role;
grant select on  MDSYS.CSW_PLUGIN_MAP$ to csw_usr_role;