Rem Copyright (c) 1998, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      relate.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    yhu         04/18/11 - rollback changes to point masks.
Rem    yhu         04/12/11 - change 000100111 from TOUCH to ON
Rem    yhu         06/25/10 - bug 9853256: add more masks in mdmask_array
Rem    sravada     12/19/06 - bug 5700474
Rem    sravada     02/14/06 - ad two new masks for lines with no boundary 
Rem    sravada     07/29/04 - add exception handlers 
Rem    sravada     12/12/03 - bug 3270382 
Rem    sravada     09/04/02 - bug 2479348
Rem    jsharma     09/16/98 - Do not drop table
Rem    gvincent    08/24/98 - fix invoker rights
Rem    ranwei      05/14/98 - Add masks for Arc/Composite types
Rem    pfwang      04/16/98 - Change synonym sdo_relate to sdo_relate_table
Rem    pfwang      04/09/98 - Add function package to control modifications
Rem    ranwei      04/06/98 - Add masks for Arc types
Rem    pfwang      03/31/98 - drop table first
Rem    pfwang      01/19/98 - Relate mask versus name lookup table
Rem    pfwang      01/19/98 - Created
Rem
Rem
Rem ###########################################################################
Rem #                                                                         #
Rem # Notes: If there are any changes made to md$relate, please make          #
Rem #        corresponding changes to mdmask_array in mdidx.c                 #
Rem #                                                                         #
Rem ###########################################################################

declare
begin
  begin
    execute immediate 
    ' drop table md$relate' ;
   exception when others then NULL;
  end;
end;
/

create table md$relate (
	sdo_mask   char(9) CONSTRAINT pk_sdo_mask PRIMARY KEY,
	sdo_relation varchar2(30),
	definer      varchar2(30)
);

declare
 cnt integer;
begin 
  cnt := 0 ;
  select count(*) into cnt from md$relate ;
  if cnt = 0 then 
insert into md$relate values ('000011011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('010111111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('011111011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('011111111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('001011111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('001111011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('001111111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('010011111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('010111011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('011011111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('010110001', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('001011011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('000011111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('011011011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('000111111', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('000111011', 'OVERLAPBDYDISJOINT', 'MDSYS');
insert into md$relate values ('010011011', 'OVERLAPBDYDISJOINT', 'MDSYS');

insert into md$relate values ('110111111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('111111011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('111111111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('100011011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('101011111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('101111011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('110011111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('110111011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('100011111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('100111011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('100111111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('101011011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('101111111', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('110011011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('111011011', 'OVERLAPBDYINTERSECT', 'MDSYS');
insert into md$relate values ('111011111', 'OVERLAPBDYINTERSECT', 'MDSYS');

insert into md$relate values ('100001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('101001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('101001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('001101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('001101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('010001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('010101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('010101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('011001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('011101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('011101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('100001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('101101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('110001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('110101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('100101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('100101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('101101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('110001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('110101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('111001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('111001111', 'TOUCH', 'MDSYS');
insert into md$relate values ('111101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('111101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('010001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('000101011', 'TOUCH', 'MDSYS');
insert into md$relate values ('011001011', 'TOUCH', 'MDSYS');
insert into md$relate values ('000101111', 'TOUCH', 'MDSYS');
insert into md$relate values ('111001001', 'TOUCH', 'MDSYS');
insert into md$relate values ('010001001', 'TOUCH', 'MDSYS');
insert into md$relate values ('011001001', 'TOUCH', 'MDSYS');

insert into md$relate values ('100111001', 'COVERS', 'MDSYS');
insert into md$relate values ('101111001', 'COVERS', 'MDSYS');
insert into md$relate values ('101011001', 'COVERS', 'MDSYS');
insert into md$relate values ('011111001', 'COVERS', 'MDSYS');
insert into md$relate values ('111011001', 'COVERS', 'MDSYS');
insert into md$relate values ('111111001', 'COVERS', 'MDSYS');
insert into md$relate values ('011011001', 'COVERS', 'MDSYS');

insert into md$relate values ('110010011', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('110010111', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('100010111', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('110110111', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('010110111', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('100110111', 'COVEREDBY', 'MDSYS');
insert into md$relate values ('000110111', 'COVEREDBY', 'MDSYS');

insert into md$relate values ('001111001', 'CONTAINS', 'MDSYS');
insert into md$relate values ('001011001', 'CONTAINS', 'MDSYS');
insert into md$relate values ('000111001', 'CONTAINS', 'MDSYS');
insert into md$relate values ('000011001', 'CONTAINS', 'MDSYS');

insert into md$relate values ('010010111', 'INSIDE', 'MDSYS');
insert into md$relate values ('000010111', 'INSIDE', 'MDSYS');
insert into md$relate values ('010010011', 'INSIDE', 'MDSYS');
insert into md$relate values ('000010011', 'INSIDE', 'MDSYS');

insert into md$relate values ('001001111', 'DISJOINT', 'MDSYS');
insert into md$relate values ('001001011', 'DISJOINT', 'MDSYS');
insert into md$relate values ('000001011', 'DISJOINT', 'MDSYS');
insert into md$relate values ('000001111', 'DISJOINT', 'MDSYS');

insert into md$relate values ('100010001', 'EQUAL', 'MDSYS');
insert into md$relate values ('000010001', 'EQUAL', 'MDSYS');

insert into md$relate values ('100100111', 'ON', 'MDSYS');
insert into md$relate values ('000100011', 'ON', 'MDSYS');
insert into md$relate values ('000100111', 'ON', 'MDSYS');
end if ;
end ;
/

create or replace view sdo_relatemask_table 
as select sdo_mask, sdo_relation from md$relate;
-- drop public synonym sdo_relatemask_table;
create or replace  public synonym 
sdo_relatemask_table for mdsys.sdo_relatemask_table;
grant select on sdo_relatemask_table to public;
commit;


create or replace package sdo_relate_mask authid definer as
procedure insert_mask(mask      in varchar2,
                 relation  in varchar2);

procedure update_mask(mask      in varchar2,
                 relation  in varchar2);

procedure delete_mask(mask      in varchar2);
end sdo_relate_mask;
/
show errors;

create or replace package body sdo_relate_mask as
procedure insert_mask(mask      in varchar2,
                      relation  in varchar2) is
begin
  insert into md$relate values(mask,nls_upper(relation),user);
  commit;
end insert_mask;

procedure update_mask(mask      in varchar2,
                 relation  in varchar2) is
owner varchar2(30);
begin
  select definer into owner from md$relate where sdo_mask=mask;
  if (owner <> user) then
    mderr.raise_md_error('MD','SDO',-13108,'Not definer');
	 return;
  end if;
  update md$relate set sdo_relation=nls_upper(relation) where sdo_mask=mask;
  commit;
end update_mask;

procedure delete_mask(mask      in varchar2) is
owner varchar2(30);
begin
  select definer into owner from md$relate where sdo_mask=mask;
  if (owner <> user) then
    mderr.raise_md_error('MD','SDO',-13108,'Not definer');
	 return;
  end if;
  delete from md$relate where sdo_mask=mask;
  commit;
end delete_mask;
end sdo_relate_mask;
/
show errors;

grant execute on sdo_relate_mask to public;
create or replace   public synonym sdo_relate_mask for mdsys.sdo_relate_mask;

commit;