Rem Rem $Header: sdows.sql 06-may-2008.06:19:38 mhorhamm Exp $ Rem Rem sdows.sql Rem Rem Copyright (c) 2005, 2008, Oracle. All rights reserved. Rem Rem NAME Rem sdows.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem mhorhamm 05/05/08 - Do not partition OLS_DIR_BUSINESSES Rem sravada 02/14/08 - add exception handles Rem mhorhamm 03/29/07 - Partitioning might not be selected Rem mhorhamm 03/13/07 - Move some statements to sdows2.sql Rem mhorhamm 03/08/07 - Add node Rem mhorhamm 02/27/07 - Shorten trigger and column names, for compatibility with Rem Workspace manager Rem mhorhamm 01/12/07 - app user-related roles not required, any more Rem mhorhamm 01/08/07 - Create trigger for business table to make sure Rem businesses are geocoded Rem mhorhamm 10/11/06 - Add table Rem mhorhamm 10/09/06 - Change column type Rem mhorhamm 10/06/06 - Add grant Rem mhorhamm 10/05/06 - Add table for conference requests Rem mhorhamm 09/22/06 - Add roles determining whether a user allows proxy Rem authentication or app user mgm Rem mhorhamm 08/21/06 - Rearrange and add drops of tables Rem mhorhamm 08/07/06 - Move config to test file Rem mhorhamm 08/04/06 - Config ACL Rem mhorhamm 05/31/06 - Change MapViewer URL Rem mhorhamm 03/07/06 - Load plb files, instead of sql Rem mhorhamm 03/02/06 - Move user creation to test scripts Rem mhorhamm 03/01/06 - Partition businesses table Rem mhorhamm 01/27/06 - Use MDSYS, instead of spatial_ws_proxy Rem mhorhamm 01/25/06 - Alter YP structure Rem mhorhamm 01/17/06 - First drop user Rem mhorhamm 12/29/05 - Add column to OpenLS YP tables Rem mhorhamm 12/27/05 - Drop one of the WS users Rem mhorhamm 11/21/05 - Add Application User Management Rem mhorhamm 11/07/05 - Add guest user for SpatialWS Rem mhorhamm 09/28/05 - Change Router IP Rem mhorhamm 08/30/05 - Drop objects, first Rem mhorhamm 05/18/05 - Formalize the geocoder and yp being on localhost Rem mhorhamm 05/17/05 - Return exception as OpenLS error Rem mhorhamm 03/28/05 - Created Rem SELECT 'Spatial WS ...' "Msg" FROM DUAL; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- OpenLS schema --------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SELECT 'OpenLS schema' "Msg" FROM DUAL; alter session set current_schema=mdsys; declare begin begin execute immediate ' drop TABLE mdsys.OLS_DIR_SYNONYMS'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OLS_DIR_CATEGORIZATIONS'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OLS_DIR_BUSINESSES'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OLS_DIR_CATEGORIES'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OLS_DIR_CATEGORY_TYPES'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OLS_DIR_BUSINESS_CHAINS'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OpenLS_Namespaces'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OpenLS_Classifications'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OpenLS_XPaths'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OpenLS_Nodes'; exception when others then return; end; begin execute immediate ' drop TABLE mdsys.OpenLSServices'; exception when others then return; end; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OPENLSSERVICES'; if(rec_count = 0) then stm := ' CREATE TABLE mdsys.OpenLSServices ( SERVICE VARCHAR2(32) NOT NULL UNIQUE CONSTRAINT OpenLSServiceDomain CHECK( SERVICE IN ( ''Location Utility Service'', ''Presentation Service'', ''Route Service'', ''Directory Service'')), URL VARCHAR2(128), VERSION VARCHAR2(16) NOT NULL)'; execute immediate stm; end if; end; / INSERT INTO mdsys.OpenLSServices (SERVICE, URL, VERSION) VALUES ('Location Utility Service', 'http://localhost', '1.0'); INSERT INTO mdsys.OpenLSServices (SERVICE, URL, VERSION) VALUES ('Presentation Service', 'http://some.server:7777/mapviewer/omserver', '1.0'); INSERT INTO mdsys.OpenLSServices (SERVICE, URL, VERSION) VALUES ('Route Service', 'http://some.server:7777/routeserver/servlet/RouteServerServlet', '1.0'); INSERT INTO mdsys.OpenLSServices (SERVICE, URL, VERSION) VALUES ('Directory Service', 'http://localhost', '1.0'); -------------------------------------------------------------------------------- declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OPENLS_NODES'; if(rec_count = 0) then stm := ' CREATE TABLE mdsys.OpenLS_Nodes ( name VARCHAR2(32) NOT NULL, version VARCHAR2(32) NOT NULL, OpenLS XMLTYPE NOT NULL)'; execute immediate stm; end if; end; / ALTER TABLE mdsys.OpenLS_Nodes ADD (CONSTRAINT OpenLS_Nodes_Prim PRIMARY KEY(name, version) USING INDEX); declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OPENLS_XPATHS'; if(rec_count = 0) then stm := ' CREATE TABLE mdsys.OpenLS_XPaths ( name VARCHAR2(32) NOT NULL, version VARCHAR2(32) NOT NULL, XPath VARCHAR2(1024) NOT NULL)'; execute immediate stm; end if; end; / ALTER TABLE mdsys.OpenLS_XPaths ADD (CONSTRAINT OpenLS_Paths_Prim PRIMARY KEY(name, version) USING INDEX); declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OPENLS_CLASSIFICATIONS'; if(rec_count = 0) then stm := ' CREATE TABLE mdsys.OpenLS_Classifications ( criterion VARCHAR2(32) NOT NULL, classification VARCHAR2(32) NOT NULL, version VARCHAR2(32) NOT NULL, characteristic_XPath VARCHAR2(1024) NOT NULL)'; execute immediate stm; end if; end; / ALTER TABLE mdsys.OpenLS_Classifications ADD (CONSTRAINT OpenLS_Cl_Prim PRIMARY KEY(criterion, classification, version) USING INDEX); declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OPENLS_NAMESPACES'; if(rec_count = 0) then stm := ' CREATE TABLE mdsys.OpenLS_Namespaces( shortcut VARCHAR2(8), version VARCHAR2(32), uri VARCHAR2(4000))'; execute immediate stm; end if; end; / ALTER TABLE mdsys.OpenLS_Namespaces ADD (CONSTRAINT OpenLS_Na_Prim PRIMARY KEY(shortcut, version) USING INDEX); -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- OpenLS package defs --------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SELECT 'OpenLS package defs' "Msg" FROM DUAL; @@sdowso.sql @@sdowsog.sql @@sdowsom.sql @@sdowsor.sql @@sdowsoy.sql -- @@sdowss.sql -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- OpenLS-directory-related ---------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SELECT 'OpenLS-directory-related' "Msg" FROM DUAL; --host imp system/manager fromuser=OpenLSYP touser=OpenLSYP file=tmdwsolsdir.dmp -- Create empty tables --------------------------------------------------------- -- Business chains, such as McDonald's declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_BUSINESS_CHAINS'; if(rec_count = 0) then stm := ' create table ols_dir_business_chains ( chain_id number primary key, chain_name varchar2(128))'; execute immediate stm; end if; end; / -- categorization systems such as NAICS or SIC: declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_CATEGORY_TYPES'; if(rec_count = 0) then stm := ' CREATE TABLE OLS_DIR_CATEGORY_TYPES ( CATEGORY_TYPE_ID NUMBER PRIMARY KEY, CATEGORY_TYPE_NAME VARCHAR2(128) NOT NULL, PARAMETERS XMLTYPE)'; execute immediate stm; end if; end; / -- categories, such as "Chinese Restaurants": declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_CATEGORIES'; if(rec_count = 0) then stm := ' CREATE TABLE OLS_DIR_CATEGORIES ( CATEGORY_ID VARCHAR2(32) NOT NULL, CATEGORY_TYPE_ID NUMBER NOT NULL, CATEGORY_NAME VARCHAR2(128) NOT NULL, PARENT_ID VARCHAR2(32), PARAMETERS XMLTYPE, PRIMARY KEY (CATEGORY_ID, CATEGORY_TYPE_ID), constraint olsfk1 FOREIGN KEY (PARENT_ID, CATEGORY_TYPE_ID) REFERENCES OLS_DIR_CATEGORIES(CATEGORY_ID, CATEGORY_TYPE_ID), constraint olsfk2 FOREIGN KEY (CATEGORY_TYPE_ID) REFERENCES OLS_DIR_CATEGORY_TYPES(CATEGORY_TYPE_ID))'; execute immediate stm; end if; end; / -- actual businesses: declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_BUSINESSES'; if(rec_count = 0) then stm := ' CREATE TABLE OLS_DIR_BUSINESSES ( BUSINESS_ID NUMBER PRIMARY KEY, BUSINESS_NAME VARCHAR2(128) NOT NULL, CHAIN_ID NUMBER, DESCRIPTION VARCHAR2(1024), PHONE VARCHAR2(64), COUNTRY VARCHAR2(64) NOT NULL, Country_Subdivision VARCHAR2(128), Country_Secondary_Subdiv VARCHAR2(128), Municipality VARCHAR2(128), Municipality_Subdivision VARCHAR2(128), POSTAL_CODE VARCHAR2(32) NOT NULL, POSTAL_CODE_EXT VARCHAR2(32), STREET VARCHAR2(128) NOT NULL, INTERSECTING_STREET VARCHAR2(128), BUILDING VARCHAR2(64), PARAMETERS XMLTYPE, GEOM MDSYS.SDO_GEOMETRY, constraint olsfk3 FOREIGN KEY (CHAIN_ID) REFERENCES ols_dir_business_chains(CHAIN_ID))'; execute immediate stm; end if; end; / alter session set events '14524 trace name context off'; -- categorizations: N:M mappings between businesses and categories: declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_CATEGORIZATIONS'; if(rec_count = 0) then stm := ' CREATE TABLE OLS_DIR_CATEGORIZATIONS ( BUSINESS_ID NUMBER, CATEGORY_ID VARCHAR2(32), CATEGORY_TYPE_ID NUMBER, CATEGORIZATION_TYPE VARCHAR2(8) DEFAULT ''EXPLICIT'', USER_SPECIFIC_CATEG VARCHAR2(32) DEFAULT NULL, PARAMETERS XMLTYPE, CONSTRAINT olspk1 PRIMARY KEY(business_id, category_id, category_type_id) USING INDEX, CONSTRAINT CAT_TYPE_CONSTR CHECK(CATEGORIZATION_TYPE IN (''EXPLICIT'', ''IMPLICIT'')), -- constraint olsfk4 FOREIGN KEY (BUSINESS_ID) REFERENCES OLS_DIR_BUSINESSES(BUSINESS_ID), constraint olsfk5 FOREIGN KEY (CATEGORY_ID, CATEGORY_TYPE_ID) REFERENCES OLS_DIR_CATEGORIES(CATEGORY_ID, CATEGORY_TYPE_ID))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'OLS_DIR_SYNONYMS'; if(rec_count = 0) then stm := ' CREATE TABLE OLS_DIR_SYNONYMS ( standard_name VARCHAR2(128), category VARCHAR2(128), aka VARCHAR2(128))'; execute immediate stm; end if; end; / INSERT INTO OLS_DIR_CATEGORY_TYPES ( CATEGORY_TYPE_ID, CATEGORY_TYPE_NAME) VALUES ( 1, 'SIC'); INSERT INTO OLS_DIR_CATEGORY_TYPES ( CATEGORY_TYPE_ID, CATEGORY_TYPE_NAME) VALUES ( 2, 'NAICS'); -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- OpenLS package bodies ------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SELECT 'OpenLS package bodies' "Msg" FROM DUAL; @@prvtwso.plb @@prvtwsog.plb @@prvtwsom.plb @@prvtwsor.plb @@prvtwsoy.plb -- @@prvtwss.plb show errors; alter package SDO_OLS compile body; show errors; alter package SDO_OLS_LOCUTL compile body; show errors; alter package SDO_OLS_PRESENTATION compile body; show errors; alter package SDO_OLS_ROUTE compile body; show errors; alter package SDO_OLS_DIRECTORY compile body; show errors; alter session set current_schema=mdsys; SELECT 'Spatial WS finished' "Msg" FROM DUAL; COMMIT; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- Conference-related ---------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_WS_CONFERENCE'; if(rec_count = 0) then stm := ' create table mdsys.sdo_ws_conference ( conference_id varchar2(128) not null, request xmltype not null, constraint sdo_ws_conf_pk primary key (conference_id))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_WS_CONFERENCE_RESULTS'; if(rec_count = 0) then stm := ' create table mdsys.sdo_ws_conference_results ( conference_id varchar2(128) not null, result xmltype not null, constraint sdo_ws_conf_res__pk primary key (conference_id))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_WS_CONFERENCE_PARTICIPANTS'; if(rec_count = 0) then stm := ' create table mdsys.sdo_ws_conference_participants ( conference_id varchar2(128) not null, participant varchar2(128) not null, has_approved number not null, constraint sdo_ws_conf_part_pk primary key (conference_id, participant), constraint sdo_ws_conf_part_fk foreign key (conference_id) references mdsys.sdo_ws_conference(conference_id))'; execute immediate stm; end if; end; / declare begin begin execute immediate 'create sequence mdsys.sdo_ws_conference_ids start with 1 nocache'; exception when others then NULL; end; end; / grant select on mdsys.sdo_ws_conference_ids to public; grant select, insert, update, delete on mdsys.sdo_ws_conference to public; grant select, insert, update, delete on mdsys.sdo_ws_conference_results to public; grant select, insert, update, delete on mdsys.sdo_ws_conference_participants to public;