execute procedure ifx_allow_newline('t');

-- 8.1x -> 8.20 upgrade: replace old metadata_tables create_sql with new
-- version to avoid a drop 8.1x/add 8.20 sequence, as this would cause
-- spatial_references table to be dropped & recreated.

update sysbldobjects set create_sql = 
	"------------------------------------------------------------------------
-- SE_setup_tables
--
-- This procedure creates the spatial_references and geometry_columns
-- tables if they do not already exist.
--
-- This procedure replaces the 8.1x version of SE_setup_tables, which
-- copied entries from the sde database's spatial_references and
-- geometry_columns tables (if they exist) into the current database's
-- tables.  This was to allow migration from SDE3.0.2.2 to ArcSde8.1.
-- Direct migration is no longer supported, you must first register
-- spatial datablade version 8.1x.
------------------------------------------------------------------------

CREATE PROCEDURE SE_setup_tables()

DEFINE tab_exists INT;
LET tab_exists = 0;

SELECT count(*) INTO tab_exists FROM informix.systables
   WHERE tabname='spatial_references';

IF (tab_exists = 0) THEN
   -- Metadata tables do not exist; fresh install
   CREATE TABLE sde.spatial_references
   (
       srid               integer       NOT NULL,
       description        varchar(64),    
       auth_name          varchar(255),
       auth_srid          int,
       falsex             float         NOT NULL,
       falsey             float         NOT NULL,
       xyunits            float         NOT NULL,
       falsez             float         NOT NULL,
       zunits             float         NOT NULL,
       falsem             float         NOT NULL,
       munits             float         NOT NULL,
       srtext             char(2048)    NOT NULL,
     PRIMARY KEY (srid) 
         CONSTRAINT sde.sp_ref_pk
   );

   CREATE TABLE sde.geometry_columns
   (
       f_table_catalog    varchar(32)  NOT NULL,
       f_table_schema     varchar(32)  NOT NULL,
       f_table_name       varchar(128) NOT NULL,
       f_geometry_column  varchar(128) NOT NULL,
       storage_type       integer,
       geometry_type      integer      NOT NULL,
       coord_dimension    integer,
       srid               integer      NOT NULL,
     PRIMARY KEY (f_table_catalog, f_table_schema,
                  f_table_name, f_geometry_column)
         CONSTRAINT sde.geocol_pk,
     FOREIGN KEY(srid) REFERENCES sde.spatial_references(srid)
         CONSTRAINT sde.geocol_fk
   );

   CREATE VIEW sde.spatial_ref_sys AS
     SELECT srid, auth_name, auth_srid, srtext FROM sde.spatial_references;

   create opaque type SE_Metadata
     (internallength = variable, alignment = 8);

   create table SE_MetadataTable
   (
    smd SE_Metadata
   );

   revoke all on SE_MetadataTable from public;

END IF

END PROCEDURE;  -- SE_setup_tables

EXECUTE PROCEDURE SE_setup_tables();

DROP PROCEDURE SE_setup_tables();

"
where obj_signature = 'metadata_tables.sql'
and ( bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.2%');


-- 8.1x -> 8.20 upgrade: replace old metadata_tables drop_sql with new
-- version to avoid a drop 8.1x/add 8.20 sequence, as this would cause
-- spatial_references table to be dropped & recreated.

update sysbldobjects set drop_sql = 
	"drop table sde.geometry_columns;
drop table sde.spatial_references;
drop table SE_MetadataTable;
drop type SE_Metadata restrict;

"
where obj_signature = 'metadata_tables.sql'
and ( bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');


-- 8.1x or 8.20 -> 8.20 latest upgrade: SE_metadata type is moved to
-- metadata_tables.sql to leave SE_MetadataTable not to be dropped and 
-- & recreated.

update sysbldobjects set drop_sql =
"
drop cast (SE_Metadata as lvarchar);
drop cast (SE_Metadata as sendrecv);
drop cast (SE_Metadata as impexp);
drop cast (SE_Metadata as impexpbin);
drop cast (lvarchar as SE_Metadata);
drop cast (sendrecv as SE_Metadata);
drop cast (impexp as SE_Metadata);
drop cast (impexpbin as SE_Metadata);
drop function SE_MetadataIn (lvarchar);
drop function SE_MetadataOut (SE_Metadata);
drop function SE_MetadataRecv (sendrecv);
drop function SE_MetadataSend (SE_Metadata);
drop function SE_MetadataImpT (impexp);
drop function SE_MetadataExpT (SE_Metadata);
drop function SE_MetadataImpB (impexpbin);
drop function SE_MetadataExpB (SE_Metadata);
drop procedure Destroy (SE_Metadata);
drop function LOhandles (SE_Metadata);
drop function SE_MetadataDump(int);

"
where obj_signature = 'pdqfuncs.sql'
and ( bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');

-- 8.1x or 8.20.x -> 8.20 latest upgrade: not allow SE_MetadataTable
-- to be dropped and re-created.

delete from sysbldobjects
where obj_signature = 'pdqtable.sql'
and (bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');

delete from sysbldobjdepends
where obj_signature = 'pdqtable.sql'
and need_obj_signature = 'pdqfuncs.sql'
and (bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');

update sysbldobjdepends set need_obj_signature = 'pdqfuncs.sql'
where obj_signature like 'pdqinit%' and need_obj_signature = 'pdqtable.sql'
and (bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');

update sysbldobjects set drop_sql =
"
drop procedure SE_MetadataInit();
drop function SE_MetadataPrep();
drop function Assign (SE_Metadata);

"
where obj_signature like 'pdqinit%'
and (bld_id like 'spatial.8.1%' or bld_id like 'spatial.8.20%');


-- 8.1x -> 8.20 upgrade: remove 'types needs metadata_tables' dependency
-- so that, when changing metadata_tables, the types don't get dropped.

delete from sysbldobjdepends
where obj_signature = 'types.sql'
and need_obj_signature = 'metadata_tables.sql'
and bld_id like 'spatial.8.1%';


-- 8.1x -> 8.20 upgrade: droptables.sql script goes away, so prevent
-- spatial_references & geometry_columns tables from getting dropped.

delete from sysbldobjects
where obj_signature = 'droptables.sql'
and bld_id like 'spatial.8.1%';

delete from sysbldobjdepends
where obj_signature = 'droptables.sql'
and need_obj_signature = 'metadata_tables.sql'
and bld_id like 'spatial.8.1%';

-- 8.1x -> 8.20 upgrade: replace old viewtable create_sql with new version
-- to avoid a drop 8.1x/add 8.20 sequence, as this would cause se_views
-- table to be dropped & recreated.

update sysbldobjects set create_sql = 
	"CREATE PROCEDURE create_se_views()
   DEFINE tab_exists INT;

   SELECT count(*) INTO tab_exists FROM informix.systables
      WHERE tabname='se_views';

   IF (tab_exists = 0) THEN
      CREATE TABLE se_views
      (
          view_name    varchar(128) NOT NULL,
          view_col     varchar(128) NOT NULL,
          real_tabname varchar(128) NOT NULL,
          real_colname varchar(128) NOT NULL
      );
   END IF

END PROCEDURE;

EXECUTE PROCEDURE create_se_views();
DROP PROCEDURE create_se_views();

"
where obj_signature = 'viewtable.sql'
and bld_id like 'spatial.8.1%';


-- 8.1x -> 8.20 upgrade: replace old spreftriggers drop_sql with new
-- version to allow upgrades to succeed even if spatial_references
-- table triggers are missing.

update sysbldobjects set drop_sql = 
"create procedure SE_DropSpRefTriggers()
   begin
   on exception in (-634) end exception with resume
      drop trigger SE_SpRefInsTrig;
      drop trigger SE_SpRefUpdTrig;
      drop trigger SE_SpRefDelTrig;
   end
end procedure;

execute procedure SE_DropSpRefTriggers();
drop procedure SE_DropSpRefTriggers();

drop procedure SE_SpRefBeforeTrig();
drop procedure SE_SpRefAfterTrig();
"
where obj_signature = 'spreftriggers.sql'
and bld_id like 'spatial.8.1%';


-- upgrade to 8.21.xC3 has to replace the op function but not op class
update sysbldobjects set create_sql =
	"create function ST_Overlaps(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Overlaps)'
   language c;
create function ST_Equals(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Equals)'
   language c;
create function ST_Contains(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable, commutator = ST_Within)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Contains)'
   language c;
create function ST_Within(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable, commutator = ST_Contains)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Within)'
   language c;
create function SE_EnvelopesIntersect(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(SE_EnvelopesIntersect)'
   language c;
create function ST_Touches(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable, percall_cost=5000)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Touches)'
   language c;
create function ST_Crosses(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable, percall_cost=1000)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Crosses)'
   language c;
create function ST_Intersects(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_Intersects)'
   language c;
create function ST_OrderingEquals(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(ST_OrderingEquals)'
   language c;
create function SE_Nearest(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(SE_NearestStratFunc)'
   language c;
create function SE_Nearest(ST_Geometry,ST_Geometry,int) returns float
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(SE_Nearest)'
   language c;
create function SE_NearestBBox(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(SE_NearestStratFunc)'
   language c;
create function SE_NearestBbox(ST_Geometry,ST_Geometry,int) returns float
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(SE_NearestBBox)'
   language c;
create function Equal(ST_Geometry,ST_Geometry) returns boolean
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(GeometryEqual)'
   language c;

create function rtUnion(ST_Geometry,ST_Geometry,ST_Geometry) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_Curve,ST_Curve,ST_Curve) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_GeomCollection,ST_GeomCollection,ST_GeomCollection) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_LineString,ST_LineString,ST_LineString) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_MultiCurve,ST_MultiCurve,ST_MultiCurve) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_MultiLineString,ST_MultiLineString,ST_MultiLineString) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_MultiPoint,ST_MultiPoint,ST_MultiPoint) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_MultiPolygon,ST_MultiPolygon,ST_MultiPolygon) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_MultiSurface,ST_MultiSurface,ST_MultiSurface) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_Point,ST_Point,ST_Point) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_Polygon,ST_Polygon,ST_Polygon) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtUnion(ST_Surface,ST_Surface,ST_Surface) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeUnion)'
   language c;
create function rtSize(ST_Geometry,float) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeSize)'
   language c;
create function rtInter(ST_Geometry,ST_Geometry,ST_Geometry) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeInter)'
   language c;
create function rtSFCbits(ST_Geometry,pointer) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeSFCbits)'
   language c;
create function rtObjLength(ST_Geometry,pointer) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeObjLength)'
   language c;
create function rtSFCvalue(ST_Geometry,integer,pointer) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeSFCvalue)'
   language c;
create function rtSetUnion(ST_Geometry,integer,pointer) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeSetUnion)'
   language c;
create function rtreeInfo(ST_Geometry,pointer,pointer,pointer) returns integer
   with (not variant, parallelizable)
   external name '$INFORMIXDIR/extend/%SYSBLDDIR%/spatial.bld(RtreeInfo)'
   language c;

grant execute on function ST_Overlaps(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Equals(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Contains(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Within(ST_Geometry,ST_Geometry) to public;
grant execute on function SE_EnvelopesIntersect(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Intersects(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Touches(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_Crosses(ST_Geometry,ST_Geometry) to public;
grant execute on function ST_OrderingEquals (ST_Geometry,ST_Geometry) to public;
grant execute on function SE_Nearest(ST_Geometry,ST_Geometry) to public;
grant execute on function SE_Nearest(ST_Geometry,ST_Geometry,int) to public;
grant execute on function SE_NearestBBox(ST_Geometry,ST_Geometry) to public;
grant execute on function SE_NearestBBox(ST_Geometry,ST_Geometry,int) to public;
grant execute on function Equal(ST_Geometry,ST_Geometry) to public;

grant execute on function rtUnion(ST_Geometry,ST_Geometry,ST_Geometry) to public;
grant execute on function rtUnion(ST_Curve,ST_Curve,ST_Curve) to public;
grant execute on function rtUnion(ST_GeomCollection,ST_GeomCollection,ST_GeomCollection) to public;
grant execute on function rtUnion(ST_LineString,ST_LineString,ST_LineString) to public;
grant execute on function rtUnion(ST_MultiCurve,ST_MultiCurve,ST_MultiCurve) to public;
grant execute on function rtUnion(ST_MultiLineString,ST_MultiLineString,ST_MultiLineString) to public;
grant execute on function rtUnion(ST_MultiPoint,ST_MultiPoint,ST_MultiPoint) to public;
grant execute on function rtUnion(ST_MultiPolygon,ST_MultiPolygon,ST_MultiPolygon) to public;
grant execute on function rtUnion(ST_MultiSurface,ST_MultiSurface,ST_MultiSurface) to public;
grant execute on function rtUnion(ST_Point,ST_Point,ST_Point) to public;
grant execute on function rtUnion(ST_Polygon,ST_Polygon,ST_Polygon) to public;
grant execute on function rtUnion(ST_Surface,ST_Surface,ST_Surface) to public;
grant execute on function rtSize(ST_Geometry,float) to public;
grant execute on function rtInter(ST_Geometry,ST_Geometry,ST_Geometry) to public;
grant execute on function rtSFCbits(ST_Geometry,pointer) to public;
grant execute on function rtObjLength(ST_Geometry,pointer) to public;
grant execute on function rtSFCvalue(ST_Geometry,integer,pointer) to public;
grant execute on function rtSetUnion(ST_Geometry,integer,pointer) to public;
grant execute on function rtreeInfo(ST_Geometry,pointer,pointer,pointer) to public;

create opclass ST_Geometry_ops for rtree 
	strategies(ST_Overlaps,Equal,ST_Contains,ST_Within,SE_EnvelopesIntersect,ST_Intersects,ST_Touches,ST_Crosses,ST_OrderingEquals,SE_Nearest,SE_NearestBBox,ST_Equals)
	support(rtUnion,rtSize,rtInter,rtSFCbits,rtObjLength,rtSFCvalue,rtSetUnion);

"
where obj_signature = 'opclass.sql'
and bld_id like 'spatial.8.21%';


update sysbldobjects set drop_sql =
	"
drop opclass ST_Geometry_ops restrict;

drop function ST_Overlaps(ST_Geometry,ST_Geometry);
drop function ST_Contains(ST_Geometry,ST_Geometry);
drop function ST_Equals(ST_Geometry,ST_Geometry);
drop function ST_Within(ST_Geometry,ST_Geometry);
drop function SE_EnvelopesIntersect(ST_Geometry,ST_Geometry);
drop function ST_Intersects(ST_Geometry,ST_Geometry);
drop function ST_Touches(ST_Geometry,ST_Geometry);
drop function ST_Crosses(ST_Geometry,ST_Geometry);
drop function ST_OrderingEquals(ST_Geometry,ST_Geometry);
drop function SE_Nearest(ST_Geometry,ST_Geometry);
drop function SE_Nearest(ST_Geometry,ST_Geometry,int);
drop function SE_NearestBBox(ST_Geometry,ST_Geometry);
drop function SE_NearestBBox(ST_Geometry,ST_Geometry,int);
drop function Equal(ST_Geometry,ST_Geometry);

drop function rtUnion(ST_Geometry,ST_Geometry,ST_Geometry);
drop function rtUnion(ST_Curve,ST_Curve,ST_Curve);
drop function rtUnion(ST_GeomCollection,ST_GeomCollection,ST_GeomCollection);
drop function rtUnion(ST_LineString,ST_LineString,ST_LineString);
drop function rtUnion(ST_MultiCurve,ST_MultiCurve,ST_MultiCurve);
drop function rtUnion(ST_MultiLineString,ST_MultiLineString,ST_MultiLineString);
drop function rtUnion(ST_MultiPoint,ST_MultiPoint,ST_MultiPoint);
drop function rtUnion(ST_MultiPolygon,ST_MultiPolygon,ST_MultiPolygon);
drop function rtUnion(ST_MultiSurface,ST_MultiSurface,ST_MultiSurface);
drop function rtUnion(ST_Point,ST_Point,ST_Point);
drop function rtUnion(ST_Polygon,ST_Polygon,ST_Polygon);
drop function rtUnion(ST_Surface,ST_Surface,ST_Surface);
drop function rtSize(ST_Geometry,float);
drop function rtInter(ST_Geometry,ST_Geometry,ST_Geometry);
drop function rtObjLength(ST_Geometry,pointer);
drop function rtSFCbits(ST_Geometry,pointer);
drop function rtSFCvalue(ST_Geometry,integer,pointer);
drop function rtSetUnion(ST_Geometry,integer,pointer);
drop function rtreeInfo(ST_Geometry,pointer,pointer,pointer);

"
where obj_signature = 'opclass.sql'
and bld_id like 'spatial.8.21%';