Rem Rem $Header: sdo/admin/sdowfsmd.sql /main/35 2009/09/18 01:39:16 bkazar Exp $ Rem Rem sdowfsmd.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdowfsmd.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem bkazar 09/01/09 - fixing WFS_CapabilitiesInfo$ for upgrade Rem bkazar 08/26/09 - modify WFS_FeatureType$ table (for WFS 1.1) Rem bkazar 07/09/09 - modify WFS_CapabilitiesInfo$ table (for WFS 1.1) Rem rchatter 12/27/05 - update WFS_featureTableMDUpdated Rem rchatter 10/11/05 - make md table names internal Rem rchatter 09/16/05 - add SPATIALPATHARRSEP Rem rchatter 08/30/05 - update Feature metadata table Rem rchatter 07/19/05 - modify capability related metadata Rem rchatter 06/28/05 - modify column metadata Rem rchatter 05/26/05 - add timestamp path metadata Rem rchatter 05/18/05 - add cache data sync related tables Rem rchatter 05/10/05 - Rem rchatter 04/26/05 - add compound key metadata for doc based Rem feature types Rem rchatter 04/22/05 - modify capability related metadata Rem rchatter 04/06/05 - add metadata table for nested sdo geom objects Rem rchatter 04/04/05 - add metadata for xmltype col support Rem rchatter 03/30/05 - extend WFS_FeatureType Rem rchatter 03/18/05 - add support doc-based featuretype Rem rchatter 03/08/05 - add support for varrays/nested_tables Rem rchatter 01/11/05 - add capability related metadata Rem rchatter 01/04/05 - add feature type tag attr table Rem rchatter 12/27/04 - add FeatureType Attr table Rem rchatter 12/21/04 - rchatter_wfs_impl_txn_1 Rem rchatter 12/21/04 - Created Rem declare stmt VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'WFS_FEATURETYPE$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureType$(featureTypeId number primary key, featureTypeName varchar2(80), featureDesc XMLType, -- conforming to FeatureTypeType xsd describeFeatureType XMLType, -- for any xsd for describe Feature Type -- this field could be automatically populated -- and materialized for each feature type from -- the feature instance table definition. lastDescribeFeatureTypeGenTS timestamp(6), lastFeatureUpdateTS timestamp(6), namespacePrefix varchar2(20), namespaceUrl varchar2(2083), schemaLocation varchar2(2083), keyCols varchar2(4000), dataPointer varchar2(61), -- pointer to the instance storage for this type isDocBased varchar2(1), spatialCols StringList, mandatoryCols StringList, tsCols StringList, docIdPaths StringList, spatialPathArrSep StringList, primarySpatialCol varchar2(4000), featureMemberName varchar2(2200), srsNs varchar2(2083), srsNsAlias varchar2(80), ftXSDRefId varchar2(80), isMTableView varchar2(1), vTableList mdsys.StringList, vKeyColsList mdsys.StringList , tKeyColsList mdsys.StringList , numCols mdsys.StringList , idxPaths mdsys.StringList , idxPathTypes mdsys.StringList, xtidxCreated varchar2(1), featureCollectionName varchar2(2200), formattedkeyCols mdsys.StringList, formattedtkeyColList mdsys.StringListList, isGML3 varchar2(1), spatialArrList mdsys.StringListList, spatialPathsSRSNSList mdsys.StringListList, spatialArrPathsSRSNSList mdsys.StringListList ) '; execute immediate stmt; begin stmt := ' alter table MDSYS.WFS_FeatureType$ add constraint unique_wfs_ft_cond UNIQUE (namespaceUrl, featureTypeName) '; execute immediate stmt; exception when others then null; end; end if; begin stmt := ' alter table MDSYS.WFS_FeatureType$ add (WFS_VERSION VARCHAR2(20)) '; -- updated when new version of WFS is supported. execute immediate stmt; exception when others then null; end; end; / declare stmt VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'WFS_FEATURETYPETAGS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeTags$ (featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, tagName varchar2(80), tagSeqNo integer, tagType varchar2(80), tagTypeNsUrl varchar2(2083), columnName varchar2(30), isNullable varchar2(1), dataLength number, isMandatory varchar2(1), dbTypeName varchar2(80))'; 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 = 'WFS_FEATUREINSTANCEMETADATA$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureInstanceMetadata$(featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, FeatureId varchar2(80), isLocked varchar2(1), lockExpiry number, lockAcquireTime Timestamp, lockId varchar2(80))'; 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 = 'WFS_CAPABILITIESINFO$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_CapabilitiesInfo$( lastCapabilityInfoUpdTS timestamp(6), -- updated when any capability related metadata is updated CAPABILITIESINFOTMPL XMLType)'; -- updated when any capability info (in xml) is generated execute immediate stmt; end if; begin stmt := 'alter table MDSYS.WFS_CapabilitiesInfo$ add (WFS_VERSION VARCHAR2(20))'; -- updated when new version of WFS is supported. execute immediate stmt; exception when others then null; end; end; / declare stmt VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'WFS_FEATURETYPECOMPLEXTAGS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeComplexTags$ (xml_typename varchar2(80), xml_typenameNsUrl varchar2(2083), xml_typetagname varchar2(80), xml_typetagseqno integer, xml_typetagtype varchar2(80), xml_typetagtypeNsUrl varchar2(2083), db_attrname varchar2(80), db_attrtype varchar2(80), isNullable varchar2(1), dataLength number, db_arrtype varchar2(80), -- this is populated only for collection types isArrayType varchar2(1), arrLength number)'; -- -1 for nested_tables 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 = 'WFS_FEATURETYPEATTRS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeAttrs$ (featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, attrName varchar2(80), attrSeqNo integer, attrType varchar2(80), attrTypeNsUrl varchar2(2083), columnName varchar2(30), isNullable varchar2(1), dataLength number, isMandatory varchar2(1), dbTypeName varchar2(80))'; 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 = 'WFS_FEATURETYPESIMPLETAGATTRS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeSimpleTagAttrs$ (featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, tagName varchar2(80), attrName varchar2(80), attrSeqNo integer, attrType varchar2(80), attrTypeNsUrl varchar2(2083), columnName varchar2(30), isNullable varchar2(1), dataLength number, isMandatory varchar2(1), dbTypeName varchar2(80))'; 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 = 'WFS_FTTYPECOMPLEXTAGATTRS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FtTypeComplexTagAttrs$ (xml_typename varchar2(80), xml_typenameNsUrl varchar2(2083), xml_typetagname varchar2(80), attrName varchar2(80), attrSeqno integer, attrType varchar2(80), attrTypeNsUrl varchar2(2083), db_attrname varchar2(80), db_attrtype varchar2(80), isNullable varchar2(1), dataLength number)'; 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 = 'WFS_PROC_MAP$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_PROC_MAP$ (ARRTYPENAME varchar2(80), PACKAGENAME varchar2(30))'; 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 = 'WFS_FEATURETYPEXMLCOLINFO$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeXMLCOLINFO$(id number primary key, featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, ftnamespaceUrl varchar2(2083), xsdDesc xmltype, xsdWrapperDesc xmltype, -- generated derived col columnName varchar2(30), objpathInfo MDSYS.StringList, lastUpdateTS timestamp(6), lastWrapperGenerateTS 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 = 'WFS_FEATURETYPENESTEDSDOS$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_FeatureTypeNestedSDOs$( featureTypeId number references MDSYS.WFS_FeatureType$(featureTypeId) on delete cascade, objnamespace varchar2(30), objName varchar2(30), objNameIndex number) '; 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 = 'WFS_FEATURETABLEMDUPDATED$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_featureTableMDUpdated$( featureTypeNSUrl varchar2(2083), featureTypeName 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 = 'WFS_FEATURETABLEUPDATED$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_featureTableUpdated$( featureTypeNSUrl varchar2(2083), featureTypeName varchar2(80), rowList RowPointerList, updateTS timestamp(6)) nested table rowList store as r_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 = 'WFS_FTXSDINFO$'; if(rec_count = 0) then stmt := 'create table MDSYS.WFS_ftXSDInfo$( ftXSDRefId varchar2(80) primary key, ftDef XMLType) '; execute immediate stmt; end if; end; / grant all on MDSYS.WFS_FeatureType$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeTags$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureInstanceMetadata$ to spatial_wfs_admin; grant all on MDSYS.WFS_CapabilitiesInfo$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeAttrs$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeComplexTags$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeSimpleTagAttrs$ to spatial_wfs_admin; grant all on MDSYS.WFS_FtTypeComplexTagAttrs$ to spatial_wfs_admin; grant all on MDSYS.WFS_PROC_MAP$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeXMLCOLINFO$ to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureTypeNestedSDOs$ to spatial_wfs_admin; grant all on MDSYS.WFS_featureTableMDUpdated$ to spatial_wfs_admin; grant all on MDSYS.WFS_featureTableUpdated$ to spatial_wfs_admin; grant all on MDSYS.WFS_ftXSDInfo$ to spatial_wfs_admin; grant all on MDSYS.USER_SDO_GEOM_METADATA to spatial_wfs_admin; grant all on MDSYS.WFS_FeatureType$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeTags$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureInstanceMetadata$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_CapabilitiesInfo$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeAttrs$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeComplexTags$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeSimpleTagAttrs$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FtTypeComplexTagAttrs$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_PROC_MAP$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeXMLCOLINFO$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_FeatureTypeNestedSDOs$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_featureTableMDUpdated$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_featureTableUpdated$ to spatial_wfs_admin_usr; grant all on MDSYS.WFS_ftXSDInfo$ to spatial_wfs_admin_usr; grant all on MDSYS.USER_SDO_GEOM_METADATA to spatial_wfs_admin_usr; grant select on MDSYS.WFS_FeatureType$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeTags$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureInstanceMetadata$ to wfs_usr_role; grant select on MDSYS.WFS_CapabilitiesInfo$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeAttrs$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeComplexTags$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeSimpleTagAttrs$ to wfs_usr_role; grant select on MDSYS.WFS_FtTypeComplexTagAttrs$ to wfs_usr_role; grant select on MDSYS.WFS_PROC_MAP$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeXMLCOLINFO$ to wfs_usr_role; grant select on MDSYS.WFS_FeatureTypeNestedSDOs$ to wfs_usr_role; grant select on MDSYS.WFS_featureTableMDUpdated$ to wfs_usr_role; grant select on MDSYS.WFS_featureTableUpdated$ to wfs_usr_role; grant select on MDSYS.WFS_ftXSDInfo$ to wfs_usr_role; grant select on MDSYS.USER_SDO_GEOM_METADATA to wfs_usr_role;