Rem
Rem $Header: sdo/admin/sdovis3dschema.sql /main/2 2008/11/17 08:28:54 mhorhamm Exp $
Rem
Rem sdovis3dschema.sql
Rem
Rem Copyright (c) 2008, Oracle and/or its affiliates.All rights reserved. 
Rem
Rem    NAME
Rem      sdovis3dschema.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    mhorhamm    09/24/08 - SDO Vis3d schema
Rem    mhorhamm    09/24/08 - Created
Rem

---- Add 3d Viz schema
Rem  Defines the metadata tables and views for SDO_LIGHTSOURCES, 
Rem   SDO_ANIMATIONS, SDO_VIEWFRAMES, SDO_SCENES, SDO_3DTHEMES
Rem   and SDO_3DTXFMS

alter session set current_schema=MDSYS;

declare
begin
  begin
   execute immediate 
'Create Table SDO_LIGHTSOURCEs_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  TYPE VARCHAR2(10),
  DEFINITION CLOB NOT NULL,
    CONSTRAINT unique_lightsources
     PRIMARY KEY (SDO_OWNER, NAME)) ';
   exception when others then NULL;
  end;
end;
/

Create or replace  View USER_SDO_LIGHTSOURCEs AS
SELECT NAME, DESCRIPTION, TYPE, DEFINITION
FROM SDO_LIGHTSOURCEs_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create or replace  View ALL_SDO_LIGHTSOURCEs AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION, TYPE, DEFINITION
FROM SDO_LIGHTSOURCEs_TABLE;


declare
begin
  begin
   execute immediate 
'Create Table SDO_ANIMATIONS_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  DEFINITION CLOB NOT NULL,
    CONSTRAINT unique_animations
     PRIMARY KEY (SDO_OWNER, NAME)) ';
   exception when others then NULL;
  end;
end;
/

Create or replace  View USER_SDO_ANIMATIONS AS
SELECT NAME, DESCRIPTION,DEFINITION
FROM SDO_ANIMATIONS_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create or replace  View ALL_SDO_ANIMATIONS AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION,DEFINITION
FROM SDO_ANIMATIONS_TABLE;


declare
begin
  begin
   execute immediate 
'Create Table SDO_VIEWFRAMES_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  SCENE_NAME VARCHAR2(32) NOT NULL,
  DEFINITION CLOB NOT NULL,
    CONSTRAINT unique_viewframes
     PRIMARY KEY (SDO_OWNER, NAME)) ';
   exception when others then NULL;
  end;
end;
/

Create or replace  View USER_SDO_VIEWFRAMES AS
SELECT NAME, DESCRIPTION, SCENE_NAME, DEFINITION
FROM SDO_VIEWFRAMES_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create or replace  View ALL_SDO_VIEWFRAMES AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION, SCENE_NAME, DEFINITION
FROM SDO_VIEWFRAMES_TABLE;

declare
begin
  begin
   execute immediate 
'Create Table SDO_SCENES_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  DEFINITION  CLOB NOT NULL,
  CONSTRAINT unique_scenes
    PRIMARY KEY (SDO_OWNER, NAME)) ';
   exception when others then NULL;
  end;
end;
/


-- drop view USER_SDO_SCENES;
Create or replace  View USER_SDO_SCENES AS
SELECT NAME, DESCRIPTION,DEFINITION
FROM SDO_SCENES_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create or replace  View ALL_SDO_SCENES AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION,DEFINITION
FROM SDO_SCENES_TABLE;

declare
begin
  begin
   execute immediate 
' Create Table SDO_3DTHEMES_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  BASE_TABLE  VARCHAR2(64) NOT NULL,
  THEME_COLUMN  VARCHAR2(2048) NOT NULL,
  STYLE_COLUMN  VARCHAR2(32) NOT NULL,
  THEME_TYPE	VARCHAR2(32),
  DEFINITION CLOB NOT NULL,
    CONSTRAINT unique_3dthemes
        PRIMARY KEY (SDO_OWNER, NAME)) ';
   execute immediate 
  'CREATE INDEX SDO_3DTHEMES_IDX ON SDO_3DTHEMES_TABLE(SDO_OWNER,BASE_TABLE) ';
   exception when others then NULL;
  end;
end;
/

Create or replace  View USER_SDO_3DTHEMES AS
SELECT NAME, DESCRIPTION, BASE_TABLE, THEME_COLUMN, STYLE_COLUMN, THEME_TYPE, DEFINITION
FROM SDO_3DTHEMES_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create  or replace View ALL_SDO_3DTHEMES AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION, BASE_TABLE, 
                  THEME_COLUMN, STYLE_COLUMN, THEME_TYPE, DEFINITION
FROM SDO_3DTHEMES_TABLE
WHERE
(exists
   (select table_name from all_tables
    where table_name=base_table
      and owner = sdo_owner
    union all
      select table_name from all_object_tables
      where table_name=base_table
      and owner = sdo_owner
    union all
    select view_name table_name from all_views
    where  view_name=base_table
      and owner = sdo_owner));

-- Txfm view: Type can be:
--         AFFINE_<x> x being Translate, Rotate, Scale,.... affine types
--         COORDINATE_TRANSFORM (src_srid, tgt_srid) are used
--       or SERIES_TXFM (the txfmids varray is used)
declare
begin
  begin
   execute immediate 
' Create Table SDO_3DTXFMS_TABLE (
  SDO_OWNER VARCHAR2(32) default sys_context(''userenv'', ''CURRENT_SCHEMA''),
  NAME  VARCHAR2(32) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  TYPE       VARCHAR2(32) ,
  AFFINE_PARAMETERS SDO_NUMBER_ARRAY, -- numbers for Affine txfms, row-major
  COORD_TXFM_SRC_SRID   NUMBER,
  COORD_TXFM_TGT_SRID   NUMBER,
  TXFM_SERIES_IDS SDO_NUMBER_ARRAY,
    CONSTRAINT unique_3dtxfms
        PRIMARY KEY (SDO_OWNER, NAME)) ';
   exception when others then NULL;
  end;
end;
/

Create or replace  View USER_SDO_3DTXFMS AS
SELECT NAME, DESCRIPTION, 
TYPE, AFFINE_PARAMETERS, COORD_TXFM_SRC_SRID,
COORD_TXFM_TGT_SRID, TXFM_SERIES_IDS 
FROM SDO_3DTXFMS_TABLE
WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA');

Create  or replace View ALL_SDO_3DTXFMS AS
SELECT SDO_OWNER OWNER, NAME, DESCRIPTION, 
TYPE, AFFINE_PARAMETERS, COORD_TXFM_SRC_SRID,
COORD_TXFM_TGT_SRID, TXFM_SERIES_IDS 
FROM SDO_3DTXFMS_TABLE;

-- Grants,....

grant select,insert,delete,update on user_sdo_lightsources to public;
grant select,insert,delete,update on user_sdo_animations to public;
grant select,insert,delete,update on user_sdo_viewframes to public;
grant select,insert,delete,update on user_sdo_scenes to public;
grant select,insert,delete,update on user_sdo_3dthemes to public;
grant select,insert,delete,update on user_sdo_3dtxfms to public;

grant select on all_sdo_lightsources to public;
grant select on all_sdo_animations to public;
grant select on all_sdo_viewframes to public;
grant select on all_sdo_scenes to public;
grant select on all_sdo_3dthemes to public;
grant select on all_sdo_3dtxfms to public;

create  or replace public synonym user_sdo_lightsources 
for mdsys.user_sdo_lightsources;
create  or replace public synonym user_sdo_animations 
for mdsys.user_sdo_animations;
create  or replace public synonym user_sdo_viewframes 
for mdsys.user_sdo_viewframes;
create  or replace public synonym user_sdo_scenes for mdsys.user_sdo_scenes;
create  or replace public synonym user_sdo_3dthemes for mdsys.user_sdo_3dthemes;
create  or replace public synonym user_sdo_3dtxfms for mdsys.user_sdo_3dtxfms;

create  or replace public synonym all_sdo_lightsources 
for mdsys.all_sdo_lightsources;
create  or replace public synonym all_sdo_animations 
for mdsys.all_sdo_animations;
create  or replace public synonym all_sdo_viewframes for mdsys.all_sdo_viewframes;
create  or replace public synonym all_sdo_scenes for mdsys.all_sdo_scenes;
create  or replace public synonym all_sdo_3dthemes for mdsys.all_sdo_3dthemes;
create  or replace public synonym all_sdo_3dtxfms for mdsys.all_sdo_3dtxfms;