Rem
Rem $Header: sdolrsmd.sql 25-feb-2005.09:57:21 sravada Exp $
Rem
Rem sdolrsmd.sql
Rem
Rem Copyright (c) 2001, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdolrsmd.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    sravada     02/25/05 - bug 4206128 
Rem    sravada     07/29/04 - add exception handlers 
Rem    sravada     04/20/04 - bug 3575747 
Rem    sravada     12/17/02 - remove drop index
Rem    sravada     04/26/02 - remove dba views
Rem    jcwang      10/03/01 - Merged jcwang_lrs_metadata
Rem    jcwang      09/21/01 - Created
Rem

--
-- Metadata for LRS Geometry Layer
--

-- drop  public synonym user_sdo_lrs_metadata;
-- drop  public synonym  all_sdo_lrs_metadata;

declare
begin
  begin
   execute immediate 
 ' Create Table SDO_LRS_METADATA_TABLE (
  SDO_OWNER 		VARCHAR2(32),
  SDO_TABLE_NAME  	VARCHAR2(32) NOT NULL,
  SDO_COLUMN_NAME	VARCHAR2(32) NOT NULL,
  SDO_DIM_POS		NUMBER 	     NOT NULL,			
  SDO_DIM_UNIT         	VARCHAR2(32),
  CHECK (SDO_DIM_POS = 3 OR SDO_DIM_POS = 4),	 
  CONSTRAINT 		unique_tables
    PRIMARY KEY (SDO_OWNER, SDO_TABLE_NAME,SDO_COLUMN_NAME)) ';
  exception when others then NULL;
 end;
end;
/

-- drop view USER_SDO_LRS_METADATA;
Create or replace  View USER_SDO_LRS_METADATA AS
SELECT 	SDO_TABLE_NAME 	TABLE_NAME, 
	SDO_COLUMN_NAME COLUMN_NAME,
	SDO_DIM_POS 	DIM_POS, 
	SDO_DIM_UNIT 	DIM_UNIT
FROM SDO_LRS_METADATA_TABLE,
     (select sys_context('userenv', 'CURRENT_SCHEMA') username from dual)
WHERE  sdo_owner = username;

-- drop view ALL_SDO_LRS_METADATA;
Create or replace  View ALL_SDO_LRS_METADATA AS
SELECT 	SDO_OWNER 	OWNER, 
	SDO_TABLE_NAME 	TABLE_NAME, 
	SDO_COLUMN_NAME COLUMN_NAME,
	SDO_DIM_POS 	DIM_POS, 
	SDO_DIM_UNIT 	DIM_UNIT
FROM SDO_LRS_METADATA_TABLE;

declare
begin
  begin
  execute immediate  
  ' CREATE INDEX SDO_LRS_META_IDX ON 
     SDO_LRS_METADATA_TABLE(SDO_OWNER,SDO_TABLE_NAME) ';
   exception when others then NULL;
  end;
end;
/	

grant select,insert,delete,update on user_sdo_lrs_metadata to public;

grant select on all_sdo_lrs_metadata to public;

create or replace  public synonym user_sdo_lrs_metadata for 
   mdsys.user_sdo_lrs_metadata;

create or replace  public synonym all_sdo_lrs_metadata for 
  mdsys.all_sdo_lrs_metadata;


-- SDO_LRS_METADATA_TABLE triggers

CREATE OR REPLACE TRIGGER SDO_LRS_TRIG_INS
INSTEAD OF INSERT ON user_sdo_lrs_metadata
REFERENCING NEW AS n
FOR EACH ROW
declare
 user_name 	varchar2(32);
 stmt  		varchar2(2048);
 vcount 	INTEGER;
BEGIN

  EXECUTE IMMEDIATE
  'SELECT user FROM dual' into user_name;

  if ( (instr(:n.table_name, ' ') > 0) OR 
       (instr(:n.table_name, '''') > 0)  ) then
   mderr.raise_md_error('MD', 'SDO', -13223,
               :n.table_name||'.'||:n.column_name);
   end if;
                                                                                
  if ( (instr(:n.column_name, ' ') > 0) OR
   (instr(:n.column_name, '''') > 0)  ) then
   mderr.raise_md_error('MD', 'SDO', -13223,
               :n.table_name||'.'||:n.column_name);
   end if;

/*                                                                                
  stmt :=  'SELECT count(*) FROM SDO_LRS_METADATA_TABLE ' ||
  ' WHERE sdo_owner = '''   || UPPER(user_name) || '''  ' ||
  '  AND  sdo_table_name = '''  || UPPER(replace(:n.table_name,'''','')) 
   || ''' ' ||
 ' AND  sdo_column_name = ''' || UPPER(replace(:n.column_name,'''',''))|| ''' ';
  */

  stmt :=  'SELECT count(*) FROM SDO_LRS_METADATA_TABLE ' ||
  ' WHERE sdo_owner = :owner  AND  sdo_table_name =  :tab ' ||
  ' AND  sdo_column_name = :col ';

 EXECUTE IMMEDIATE stmt INTO vcount
    USING  UPPER(user_name), UPPER(:n.table_name), UPPER(:n.column_name) ;

  IF vcount = 0 THEN
    INSERT INTO sdo_lrs_metadata_table values
             (UPPER(user_name), UPPER(:n.table_name), UPPER(:n.column_name), :n.dim_pos, UPPER(:n.dim_unit));
  ELSE
   mderr.raise_md_error('MD', 'SDO', -13223,
           user_name||'.'||:n.table_name);
 END IF;
END;
/
show errors;


CREATE OR REPLACE  TRIGGER SDO_LRS_TRIG_DEL
INSTEAD OF DELETE ON user_sdo_lrs_metadata
REFERENCING OLD AS n
FOR EACH ROW
declare
 user_name 	varchar2(32);
 stmt  		varchar2(2048);
 vcount 	INTEGER;
BEGIN
 
  EXECUTE IMMEDIATE
  'SELECT user FROM dual' into user_name;

    DELETE FROM  sdo_lrs_metadata_table
    WHERE SDO_OWNER   = user_name
      AND SDO_TABLE_NAME  = UPPER(:n.table_name)
      AND SDO_COLUMN_NAME = UPPER(:n.column_name);
END;
/
show errors;


CREATE  OR REPLACE TRIGGER SDO_LRS_TRIG_UPD
INSTEAD OF UPDATE ON user_sdo_lrs_metadata
REFERENCING OLD AS old NEW AS n
FOR EACH ROW
declare
 user_name 	varchar2(32);
 stmt  		varchar2(2048);
 vcount 	INTEGER;
BEGIN
 
  EXECUTE IMMEDIATE
  'SELECT user FROM dual' into user_name;

    UPDATE sdo_lrs_metadata_table
    SET (SDO_TABLE_NAME, SDO_COLUMN_NAME, SDO_DIM_POS, SDO_DIM_UNIT)  =
     (SELECT UPPER(:n.table_name), UPPER(:n.column_name),:n.dim_pos, UPPER(:n.dim_unit) FROM DUAL)
    WHERE SDO_OWNER   	  = UPPER(user_name)
      AND SDO_TABLE_NAME  = UPPER(:old.table_name)
      AND SDO_COLUMN_NAME = UPPER(:old.column_name);
END;
/
show errors;

/
show errors;