analytics/informix/extend/sts.2.00.FC1/objects.sql

-- Generated by BladeSmith 4.20.FC1  DO NOT MODIFY. --

execute procedure ifx_allow_newline('t');

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "sts_subtracktable table",
	"%SYSBLDUSER%", 0,
	"
create table sts_subtracktable (
        subtrack_id             SERIAL,
        subtrack_name           VARCHAR(128) PRIMARY KEY,
        base_table_name         VARCHAR(128),
        ts_column_name          VARCHAR(128),
        flags                   INTEGER,
        parameters              BSON,
        ts_data_first_timestamp DATETIME YEAR TO SECOND,
        ts_data_lag_to_current  INTERVAL DAY TO SECOND,
        task_nschsessions       INTEGER,
        task_frequency          INTERVAL DAY TO SECOND,
        task_start_time   	DATETIME HOUR TO SECOND,
        task_data_interval_to_process INTERVAL DAY TO SECOND
	) lock mode row;
create unique index sts_subtrackable_id_idx on sts_subtracktable(subtrack_id);
create unique index sts_subtrackable_ts_idx on sts_subtracktable(base_table_name,ts_column_name);
",
	"f",
	"
CREATE PROCEDURE sts_subtracktable_check()
    DEFINE n integer;
    DEFINE c integer;
    DEFINE sts lvarchar;
    DEFINE stn varchar(128);
	
    SELECT count(*) INTO n FROM sts_subtracktable;
    IF n > 0 THEN
	LET sts = ' ';
	LET c = 0;
	FOREACH subtrack FOR SELECT subtrack_name INTO stn FROM sts_subtracktable
		IF ( c > 0) THEN
			LET sts = sts || ', ';
		END IF;
		LET sts = sts || stn;
		LET c = c + 1;
	END FOREACH;
	RAISE EXCEPTION -746, 0, 'The following subtrack definitions still exist:'|| sts;
    END IF;
END PROCEDURE;
EXECUTE PROCEDURE sts_subtracktable_check();
DROP PROCEDURE sts_subtracktable_check();

drop table sts_subtracktable;
",
	"f"
);


insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "sts_defaultparameterstable table",
	"%SYSBLDUSER%", 0,
	"
create table sts_defaultparameterstable (
        parameters              BSON);
",
	"f",
	"
drop table sts_defaultparameterstable;

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "sts_instancetable table",
	"%SYSBLDUSER%", 0,
	"create table sts_instancetable (
        instance_id             bigint primary key,
        subtrack_id             integer) lock mode row;
",
	"f",
	"drop table sts_instancetable;

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackCreate",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackCreate(
        base_table_name         VARCHAR(128),
        ts_column_name          VARCHAR(128),
        subtrack_name           VARCHAR(128),
        flags                   INTEGER DEFAULT NULL,
        parameters              BSON DEFAULT NULL,
        ts_data_first_timestamp DATETIME YEAR TO SECOND DEFAULT NULL,
        ts_data_lag_to_current  INTERVAL DAY TO SECOND DEFAULT NULL,
        task_nschsessions       INTEGER DEFAULT NULL,
        task_frequency          INTERVAL DAY TO SECOND DEFAULT NULL,
        task_start_time   	DATETIME HOUR TO SECOND DEFAULT NULL,
        task_data_interval_to_process  INTERVAL DAY TO SECOND DEFAULT NULL)
    returns VARCHAR(128)
    with (handlesnulls, stack=131072)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackCreate)'
    language c;
grant execute on function STS_SubtrackCreate(VARCHAR(128), VARCHAR(128), VARCHAR(128), INTEGER, BSON, 
DATETIME YEAR TO SECOND, INTERVAL DAY TO SECOND, INTEGER, INTERVAL DAY TO SECOND, DATETIME HOUR TO SECOND, INTERVAL DAY TO SECOND)
to public;

create function STS_SubtrackCreateSupport(
        base_table_name         VARCHAR(128),
        ts_column_name          VARCHAR(128),
        subtrack_name           VARCHAR(128),
        flags                   INTEGER,
        parameters              BSON,
        ts_data_first_timestamp DATETIME YEAR TO SECOND,
        ts_data_lag_to_current  INTERVAL DAY TO SECOND)
    returns VARCHAR(128)
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackCreateSupport)'
    language c;
grant execute on function STS_SubtrackCreateSupport(VARCHAR(128), VARCHAR(128), VARCHAR(128), INTEGER, BSON,
	DATETIME YEAR TO SECOND, INTERVAL DAY TO SECOND)
to public;

create function STS_SetDefaultParameters(
        parameters              BSON DEFAULT NULL)
    returns INTEGER
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SetDefaultParameters)'
    language c;
grant execute on function STS_SetDefaultParameters(BSON)
to public;
",
	"f",
	"
drop function STS_SubtrackCreate(VARCHAR(128), VARCHAR(128), VARCHAR(128), INTEGER, BSON, 
DATETIME YEAR TO SECOND, INTERVAL DAY TO SECOND, INTEGER, INTERVAL DAY TO SECOND, DATETIME HOUR TO SECOND, INTERVAL DAY TO SECOND);

drop function STS_SubtrackCreateSupport(VARCHAR(128), VARCHAR(128), VARCHAR(128), INTEGER, BSON,
	DATETIME YEAR TO SECOND, INTERVAL DAY TO SECOND);

drop function STS_SetDefaultParameters(BSON);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_InstanceId",
	"%SYSBLDUSER%", 0,
	"
create function STS_InstanceId(
	ts			TimeSeries)
    returns bigint
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_InstanceId)'
    language c;
grant execute on function STS_InstanceId(TimeSeries) to public;

-- internal udr
create function STS_UnlockInstanceId(
	ts			TimeSeries)
    returns integer
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_UnlockInstanceId)'
    language c;
grant execute on function STS_UnlockInstanceId(TimeSeries) to public;
",
	"f",
	"
drop function STS_InstanceId(TimeSeries);
drop function STS_UnlockInstanceId(TimeSeries);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackDestroy",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackDestroy(
        subtrack_name         VARCHAR(128))
    returns integer
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackDestroy)'
    language c;
grant execute on function STS_SubtrackDestroy(VARCHAR(128))
to public;

",
	"f",
	"drop function STS_SubtrackDestroy(VARCHAR(128));
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackDropTable",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackDropTable(
        subtrack_name         VARCHAR(128))
    returns integer
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackDropTable)'
    language c;
grant execute on function STS_SubtrackDropTable(VARCHAR(128))
to public;

",
	"f",
	"drop function STS_SubtrackDropTable(VARCHAR(128));
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackBuild",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackBuild(
        subtrack_name         VARCHAR(128),
	flags		      INTEGER DEFAULT NULL)
    returns bigint
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackBuild)'
    language c;
grant execute on function STS_SubtrackBuild(VARCHAR(128), INTEGER)
to public;

",
	"f",
	"drop function STS_SubtrackBuild(VARCHAR(128), INTEGER);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackBuildTask",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackBuildTask(
        subtrack_name           VARCHAR(128),
        flags                   INTEGER DEFAULT NULL,
        task_session_n          INTEGER DEFAULT NULL,
        task_nschsessions       INTEGER DEFAULT NULL,
        task_data_interval_to_process  INTERVAL DAY TO SECOND DEFAULT NULL)
    returns bigint
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackBuildTask)'
    language c;
grant execute on function STS_SubtrackBuildTask(VARCHAR(128), INTEGER, INTEGER, INTEGER, INTERVAL DAY TO SECOND)
to public;

",
	"f",
	"drop function STS_SubtrackBuildTask(VARCHAR(128), INTEGER, INTEGER, INTEGER, INTERVAL DAY TO SECOND);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackUpdate",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackUpdate(
        ts                    TimeSeries,
	begin_time            DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time              DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	flags		      INTEGER DEFAULT NULL)
    returns bigint
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackUpdate)'
    language c;
grant execute on function STS_SubtrackUpdate(TimeSeries, DATETIME YEAR TO FRACTION(5), DATETIME YEAR TO FRACTION(5), INTEGER)
to public;

",
	"f",
	"drop function STS_SubtrackUpdate(TimeSeries, DATETIME YEAR TO FRACTION(5), DATETIME YEAR TO FRACTION(5), INTEGER);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackAlterFirstTimeStamp",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackAlterFirstTimeStamp(
        subtrack_name           VARCHAR(128),
        ts_data_first_timestamp DATETIME YEAR TO SECOND DEFAULT NULL)
    returns integer
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackAlterFirstTimeStamp)'
    language c;
grant execute on function STS_SubtrackAlterFirstTimeStamp(VARCHAR(128), DATETIME YEAR TO SECOND)
to public;
",
	"f",
	"
drop function STS_SubtrackAlterFirstTimeStamp(VARCHAR(128), DATETIME YEAR TO SECOND);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackAlterLagToCurrent",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackAlterLagToCurrent(
        subtrack_name           VARCHAR(128),
        ts_data_lag_to_current  INTERVAL DAY TO SECOND DEFAULT NULL)
    returns integer
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackAlterLagToCurrent)'
    language c;
grant execute on function STS_SubtrackAlterLagToCurrent(VARCHAR(128), INTERVAL DAY TO SECOND)
to public;
",
	"f",
	"
drop function STS_SubtrackAlterLagToCurrent(VARCHAR(128), INTERVAL DAY TO SECOND);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_SubtrackAlterProcessing",
	"%SYSBLDUSER%", 0,
	"create function STS_SubtrackAlterProcessing(
        subtrack_name           VARCHAR(128),
        flags                   INTEGER DEFAULT NULL,
        task_nschsessions       INTEGER DEFAULT NULL,
        task_frequency          INTERVAL DAY TO SECOND DEFAULT NULL,
        task_start_time   	DATETIME HOUR TO SECOND DEFAULT NULL,
        task_data_interval_to_process  INTERVAL DAY TO SECOND DEFAULT NULL)
    returns integer
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_SubtrackAlterProcessing)'
    language c;
grant execute on function STS_SubtrackAlterProcessing(VARCHAR(128), INTEGER, 
INTEGER, INTERVAL DAY TO SECOND, DATETIME HOUR TO SECOND, INTERVAL DAY TO SECOND)
to public;
",
	"f",
	"
drop function STS_SubtrackAlterProcessing(VARCHAR(128), INTEGER, 
INTEGER, INTERVAL DAY TO SECOND, DATETIME HOUR TO SECOND, INTERVAL DAY TO SECOND);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_InsertInstanceIntoSubtrack",
	"%SYSBLDUSER%", 0,
	"
create procedure STS_InsertInstanceIntoSubtrack(name varchar(128), instid bigint);
	define id integer;
	define sql lvarchar;

	select a.subtrack_id into id from sts_subtracktable a where a.subtrack_name = name;
	IF id IS NOT NULL THEN
		let sql = 'insert into sts_instancetable(instance_id,subtrack_id) values(' || instid || ', ' || id || ');';
		execute immediate sql;
	END IF;
end procedure;
grant execute on procedure STS_InsertInstanceIntoSubtrack(varchar(128), bigint)
to public;

",
	"f",
	"drop procedure STS_InsertInstanceIntoSubtrack(varchar(128), bigint);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_DeleteInstanceFromSubtrack",
	"%SYSBLDUSER%", 0,
	"
create procedure STS_DeleteInstanceFromSubtrack(subtrack_name varchar(128), instid bigint);
	define sql lvarchar;

	let sql = 'delete from sts_instancetable where instance_id = ' || instid || ' and subtrack_id = (select subtrack_id from sts_subtracktable where subtrack_name = ''' || subtrack_name || ''')';
	execute immediate sql;
	let sql = 'delete from ' || subtrack_name || ' where instance_id = ' || instid;
	execute immediate sql;
end procedure;
grant execute on procedure STS_DeleteInstanceFromSubtrack(varchar(128), bigint)
to public;

",
	"f",
	"drop procedure STS_DeleteInstanceFromSubtrack(varchar(128), bigint);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "query functions 1",
	"%SYSBLDUSER%", 0,
	"
create function STS_GetPosition (
	ts TimeSeries,
	tstamp datetime year to fraction(5))
    returns ST_Point 
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetPosition)' language c;

create function STS_GetLastPosition (
	ts TimeSeries)
    returns ST_Point
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetLastPosition)' language c;

create function STS_GetCompactTrajectory(
	ts			TimesEries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns ST_Geometry
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetCompactTrajectory)' language c;

create function STS_GetFirstTimeByPoint(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	point	 		ST_Point DEFAULT NULL,
	radius			float DEFAULT NULL,
	uom			varchar(128) DEFAULT NULL)
    returns datetime year to fraction(5)
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetFirstTimeByPoint)' language c;

create function STS_GetFirstTimeByRegion(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry	 	ST_Geometry DEFAULT NULL)
    returns datetime year to fraction(5)
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetFirstTimeByRegion)' language c;

create function STS_GetLocIntersect(
	subtrack_name	VARCHAR(128),
	begin_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
	returns ROW
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetLocIntersect)' language c;

create function STS_GetLocWithin(
	subtrack_name		VARCHAR(128),
	begin_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns ROW
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetLocWithin)' language c;

create function STS_GetLocStationaryWithin(
	subtrack_name		VARCHAR(128),
	begin_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns ROW
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetLocStationaryWithin)' language c;

create function STS_GetLocNearest(
	subtrack_name		VARCHAR(128),
	begin_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry		ST_Geometry DEFAULT NULL,
	max_distance		FLOAT DEFAULT NULL,
	uom			VARCHAR(128) DEFAULT NULL,
	max_nearest_count	INTEGER DEFAULT NULL)
    returns ROW
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetLocNearest)' language c;

create function STS_GetTrajectory(
	ts		TimeSeries,
	begin_time 	DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time	DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns ST_Geometry
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetTrajectory)' language c;

grant execute on function STS_GetPosition (
    TimeSeries,
    datetime year to fraction(5)) to public;

grant execute on function STS_GetLastPosition (
    TimeSeries) to public;

grant execute on function STS_GetCompactTrajectory(
    timeseries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;

grant execute on function STS_GetFirstTimeByPoint(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Point,
    float,
    varchar(128)) to public;

grant execute on function STS_GetFirstTimeByRegion(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_GetLocIntersect(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_GetLocWithin(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_GetLocStationaryWithin(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_GetLocNearest(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry,
    FLOAT,
    VARCHAR(128),
    INTEGER
) to public;

grant execute on function STS_GetTrajectory(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;",
	"f",
	"
drop function STS_GetPosition (
    TimeSeries,datetime year to fraction(5));
drop function STS_GetLastPosition (
    TimeSeries);

drop function STS_GetCompactTrajectory(
    timeseries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));

drop function STS_GetFirstTimeByPoint(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Point,
    float,
    varchar(128));

drop function STS_GetFirstTimeByRegion(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_GetLocIntersect(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_GetLocWithin(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_GetLocStationaryWithin(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_GetLocNearest(
    VARCHAR(128),
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry,
    FLOAT,
    VARCHAR(128),
    INTEGER);

drop function STS_GetTrajectory(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "query functions 2",
	"%SYSBLDUSER%", 0,
	"
create function STS_TrajectoryCross(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_TrajectoryCross)' language c;

create function STS_TrajectoryIntersect(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_TrajectoryIntersect)' language c;

create function STS_TrajectoryWithin(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_TrajectoryWithin)' language c;

create function STS_TrajectoryDistance(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	point 			ST_Point DEFAULT NULL,
	max_distance		FLOAT DEFAULT NULL,
	uom			VARCHAR(128) DEFAULT NULL)
    returns FLOAT
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_TrajectoryDistance)' language c;

create function STS_Speed(
	begin_time		DATETIME YEAR TO FRACTION(5),
	end_time		DATETIME YEAR TO FRACTION(5),
	geometry 		ST_Geometry,
	uom			varchar(128) DEFAULT NULL)
    returns FLOAT
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_Speed)' language c;

grant execute on function STS_TrajectoryCross(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_TrajectoryIntersect(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_TrajectoryWithin(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_TrajectoryDistance(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Point,
    FLOAT,
    varchar(128)) to public;

grant execute on function STS_Speed(
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry,
    varchar(64)) to public;",
	"f",
	"
drop function STS_TrajectoryCross(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_TrajectoryIntersect(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_TrajectoryWithin(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_TrajectoryDistance(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Point,
    FLOAT,
    varchar(128));

drop function STS_Speed(
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry,
    varchar(64));",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "query functions 3",
	"%SYSBLDUSER%", 0,
	"
create function STS_IsStationary(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_IsStationary)' language c;

create function STS_IsStationary(
	ts			TimeSeries,
	geometry 		ST_Geometry DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_IsStationaryWithGeometry)' language c;

create function STS_HasNoData(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_HasNoData)' language c;

create function STS_HasNoSignal(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_HasNoSignal)' language c;

create function STS_HasInterruptedSignal(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns boolean
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_HasInterruptedSignal)' language c;

create row type STS_StationaryPosition_r(
	begin_time 		DATETIME YEAR TO FRACTION(5),
	end_time 		DATETIME YEAR TO FRACTION(5),
	position 		ST_Point);

create function STS_GetStationaryPosition(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry 		ST_Geometry DEFAULT NULL)
    returns STS_StationaryPosition_r
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetStationaryPosition)' language c;

create function STS_GetStationaryPosition(
	ts			TimeSeries,
	geometry 		ST_Geometry)
    returns STS_StationaryPosition_r
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetStationaryPositionWithGeometry)' language c;

create row type STS_NoDataTimeRange_r(
	begin_time 		DATETIME YEAR TO FRACTION(5),
	end_time 		DATETIME YEAR TO FRACTION(5),
	state	 		integer);

create function STS_GetNoDataTimeRange(
	ts			TimeSeries,
	begin_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	end_time 		DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
    returns STS_NoDataTimeRange_r
    with (iterator, handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_GetNoDataTimeRange)' language c;

grant execute on function STS_IsStationary(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_IsStationary(
    TimeSeries,
    ST_Geometry) to public;

grant execute on function STS_HasNoData(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;

grant execute on function STS_HasNoSignal(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;

grant execute on function STS_HasInterruptedSignal(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;

grant execute on function STS_GetStationaryPosition(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry) to public;

grant execute on function STS_GetStationaryPosition(
    TimeSeries,
    ST_Geometry) to public;

grant execute on function STS_GetNoDataTimeRange(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5)) to public;

",
	"f",
	"
drop function STS_IsStationary(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_IsStationary(
    TimeSeries,
    ST_Geometry);

drop function STS_HasNoData(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));

drop function STS_HasNoSignal(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));

drop function STS_HasInterruptedSignal(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));

drop function STS_GetStationaryPosition(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    ST_Geometry);

drop function STS_GetStationaryPosition(
    TimeSeries,
    ST_Geometry);

drop function STS_GetNoDataTimeRange(
    TimeSeries,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5));

drop row type STS_NoDataTimeRange_r restrict;
drop row type STS_StationaryPosition_r restrict;

",
	"f"
);

insert into sysbldobjects
        (bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
            create_can_fail, drop_sql, drop_can_fail)
values
(
        "%SYSBLDNAME%", 0, "insert into trace tables",
        "%SYSBLDUSER%", 0,
        "
INSERT INTO systraceclasses(name) VALUES ('STSBuild');
INSERT INTO systraceclasses(name) VALUES ('STSQuery');

",
        "f",
        "DELETE FROM systraceclasses WHERE name = 'STSBuild';
DELETE FROM systraceclasses WHERE name = 'STSQuery';

",
        "f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_Set_Trace (lvarchar,lvarchar)",
	"%SYSBLDUSER%", 0,
	"
create procedure STS_Set_Trace (lvarchar,lvarchar) with (not variant)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_Set_Trace)' language c;

grant execute on procedure STS_Set_Trace (lvarchar,lvarchar) to public;

",
	"f",
	"drop procedure STS_Set_Trace (lvarchar,lvarchar);

",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "STS_Set_Trace (lvarchar,lvarchar)",
	"%SYSBLDUSER%",
	0, "insert into trace tables",
	"%SYSBLDUSER%"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "STS_Release ()",
	"%SYSBLDUSER%", 0,
	"
create function STS_Release ()
    returns lvarchar
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_Release)' language c;

    grant execute on function STS_Release () to public;
",
	"f",
	"drop function STS_Release ();

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "backward compatiblility",
	"%SYSBLDUSER%", 0,
	"
create function STS_Init (
  ts_tabname varchar(128), 
  task_frequency interval day to second DEFAULT NULL,
  task_starttime datetime hour to second DEFAULT NULL,
  ts_starttime datetime year to second DEFAULT NULL,
  ts_interval_to_process interval day to second DEFAULT NULL,
  ts_interval_to_avoid interval day to second DEFAULT NULL)
returns integer with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_init)"" language c;

create function STS_Cleanup (
  ts_tabname varchar(128) DEFAULT NULL)
returns integer with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_cleanup)"" language c;

create function STS_GetCompactTrajectory (
	table_name LVARCHAR, 
	obj_id LVARCHAR, 
	ts TimeSeries,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL)
returns lvarchar with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_getcompacttrajectory)"" language c;

create function STS_GetFirstTimeByPoint (
	table_name LVARCHAR,
	obj_id LVARCHAR,
	ts TimeSeries,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns datetime year to fraction(5) with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_getfirsttimebypoint)"" language c;

create function STS_TrajectoryWithin (
	table_name LVARCHAR,
	obj_id LVARCHAR,
	ts_colname LVARCHAR,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns boolean with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_trajectorywithin)"" language c;

create function STS_TrajectoryIntersect (
	table_name LVARCHAR,
	obj_id LVARCHAR,
	ts_colname LVARCHAR,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns boolean with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_trajectoryintersect)"" language c;

create function STS_TrajectoryCross (
	table_name LVARCHAR,
	obj_id LVARCHAR,
	ts_colname LVARCHAR,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns boolean with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_trajectorycross)"" language c;

create function STS_TrajectoryDistance (
	table_name LVARCHAR,
	obj_id LVARCHAR,
	ts_colname LVARCHAR,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	uom LVARCHAR DEFAULT NULL)
returns float with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_trajectorydistance)"" language c;

create function STS_GetIntersectSet (
	table_name LVARCHAR,
	ts_colname LVARCHAR,
	starttime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	endtime DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns set(lvarchar not null) with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_getintersectset)"" language c;

create function STS_GetLocWithinSet (
	table_name LVARCHAR,
	ts_colname LVARCHAR,
	tstamp DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns set(lvarchar not null) with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_getlocwithinset)"" language c;

create function STS_GetNearestObject (
	table_name LVARCHAR,
	ts_colname LVARCHAR,
	tstamp DATETIME YEAR TO FRACTION(5) DEFAULT NULL,
	geometry LVARCHAR DEFAULT NULL,	
	max_distance REAL DEFAULT NULL,
	uom LVARCHAR DEFAULT NULL)
returns lvarchar with (handlesnulls, stack=131072)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(sts_bc_getnearestobject)"" language c;

grant execute on function STS_Init (varchar(128), interval day to second, datetime hour to second, datetime year to second, interval day to second, interval day to second) to public;
grant execute on function STS_Cleanup (varchar(128)) to public;
grant execute on function STS_GetCompactTrajectory (lvarchar,lvarchar,TimeSeries,datetime year to fraction(5),datetime year to fraction(5)) to public;
grant execute on function STS_GetFirstTimeByPoint (lvarchar,lvarchar,TimeSeries,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_TrajectoryWithin (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_TrajectoryIntersect (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_TrajectoryCross (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_TrajectoryDistance (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,lvarchar) to public;
grant execute on function STS_GetIntersectSet (lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_GetLocWithinSet (lvarchar,lvarchar,datetime year to fraction(5),lvarchar,real,lvarchar) to public;
grant execute on function STS_GetNearestObject (lvarchar,lvarchar,datetime year to fraction(5),lvarchar,real,lvarchar) to public;
",
	"f",
	"
drop function STS_Init (varchar(128), interval day to second, datetime hour to second, datetime year to second, interval day to second, interval day to second);
drop function STS_Cleanup (varchar(128));
drop function STS_GetCompactTrajectory (lvarchar,lvarchar,TimeSeries,datetime year to fraction(5),datetime year to fraction(5));
drop function STS_GetFirstTimeByPoint (lvarchar,lvarchar,TimeSeries,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_TrajectoryWithin (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_TrajectoryIntersect (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_TrajectoryCross (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_TrajectoryDistance (lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,lvarchar);
drop function STS_GetIntersectSet (lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_GetLocWithinSet (lvarchar,lvarchar,datetime year to fraction(5),lvarchar,real,lvarchar);
drop function STS_GetNearestObject (lvarchar,lvarchar,datetime year to fraction(5),lvarchar,real,lvarchar);
",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "phase1 upgrade",
	"%SYSBLDUSER%", 0,
	"
create procedure sts_phase1_upgrade()
    with (handlesnulls)
    external name '$INFORMIXDIR/extend/%SYSBLDDIR%/sts.bld(STS_Phase1_upgrade)'
    language c;
grant execute on procedure sts_phase1_upgrade()
to public;

create procedure sts_phase1_drop();
	define tn varchar(128);
	define sql lvarchar;
	define c integer;

	execute immediate ""create temp table sts_temp_table_count (rc integer);"";
	execute immediate ""create temp table sts_temp_table_all (name varchar(128));"";
	execute immediate ""create temp table sts_temp_table_bad (name varchar(128));"";
	execute immediate ""create temp table sts_temp_phase1_subtracks (name varchar(128));"";
	execute immediate ""delete from sts_temp_phase1_subtracks;"";
	execute immediate ""insert into sts_temp_table_all select replace(trim(tabname)::varchar(128), '_subtrack', '') from systables where tabname matches '*_subtrack';"";
	execute immediate ""insert into sts_temp_table_all select replace(trim(tabname)::varchar(128), '_lasttime', '') from systables where tabname matches '*_lasttime';"";
	execute immediate ""insert into sts_temp_phase1_subtracks select unique(name) from sts_temp_table_all"";

	foreach tabname for select name into tn from sts_temp_phase1_subtracks
		execute immediate ""delete from sts_temp_table_count;"";
		execute immediate ""insert into sts_temp_table_count select count(*)::integer from systables where tabname = '""||tn||""';"";
		select rc into c from sts_temp_table_count;
		if (c != 1) then
			let sql = ""insert into sts_temp_table_bad values ('""||tn||""');"";
			execute immediate sql;
		end if;

		execute immediate ""delete from sts_temp_table_count;"";
		execute immediate ""insert into sts_temp_table_count select count(*)::integer from systables where tabname = '""||tn||""_subtrack';"";
		select rc into c from sts_temp_table_count;
		if (c != 1) then
			let sql = ""insert into sts_temp_table_bad values ('""||tn||""');"";
			execute immediate sql;
		end if;

		execute immediate ""delete from sts_temp_table_count;"";
		execute immediate ""insert into sts_temp_table_count select count(*)::integer from systables where tabname = '""||tn||""_lasttime';"";
		select rc into c from sts_temp_table_count;
		if (c != 1) then
			let sql = ""insert into sts_temp_table_bad values ('""||tn||""');"";
			execute immediate sql;
		end if;
	end foreach;
	let sql = ""delete from sts_temp_phase1_subtracks where name in (select unique(name) from sts_temp_table_bad);"";
	execute immediate sql;
	foreach tabname for select name into tn from sts_temp_phase1_subtracks
		execute immediate ""drop table if exists ""||tn||""_lasttime;"";
		execute immediate ""drop table if exists ""||tn||""_subtrack;"";
	end foreach;
	execute immediate ""execute procedure sts_phase1_upgrade();"";
	execute immediate ""drop table sts_temp_table_count;"";
	execute immediate ""drop table sts_temp_table_all;"";
	execute immediate ""drop table sts_temp_table_bad;"";
	execute immediate ""drop table sts_temp_phase1_subtracks;"";
end procedure;
execute procedure sts_phase1_drop();
drop procedure sts_phase1_drop();
drop procedure sts_phase1_upgrade();
",
	"f",
	"
",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "sts_subtracktable table",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "sts_subtracktable table",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "sts_defaultparameterstable table",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "sts_initargumentstable table",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "sts_instancetable table",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackCreate",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_InstanceId",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackDestroy",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackDropTable",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackBuild",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackBuildTask",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackUpdate",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_SubtrackAlterProcessing",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_InsertInstanceIntoSubtrack",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_DeleteInstanceFromSubtrack",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "insert into trace tables",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "STS_Set_Trace (lvarchar,lvarchar)",
	"%SYSBLDUSER%"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "phase1 upgrade",
	"%SYSBLDUSER%",
	0, "backward compatiblility",
	"%SYSBLDUSER%"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 15, "ISTS",
	"%SYSBLDUSER%", 0,
	"insert into sysbldiprovided
	(bldi_id, bld_id)
values
(
	'ISTS',
	'%SYSBLDNAME%'
);

",
	"f",
	"delete from sysbldiprovided
	where bldi_id = 'ISTS'
	and bld_id = '%SYSBLDNAME%';

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 15, "Ispatial",
	"%SYSBLDUSER%", 0,
	"insert into sysbldirequired
	(bldi_id, bld_id)
values
(
	'Ispatial',
	'%SYSBLDNAME%'
);

",
	"f",
	"delete from sysbldirequired
	where bldi_id = 'Ispatial'
	and bld_id = '%SYSBLDNAME%';

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 15, "ITimeSeries",
	"%SYSBLDUSER%", 0,
	"insert into sysbldirequired
	(bldi_id, bld_id)
values
(
	'ITimeSeries',
	'%SYSBLDNAME%'
);

",
	"f",
	"delete from sysbldirequired
	where bldi_id = 'ITimeSeries'
	and bld_id = '%SYSBLDNAME%';

",
	"f"
);

insert into sysbldobjects
	(bld_id, obj_kind, obj_signature, obj_owner, sequence, create_sql,
	    create_can_fail, drop_sql, drop_can_fail)
values
(
	"%SYSBLDNAME%", 0, "sysbldregistered",
	"%SYSBLDUSER%", 0,
	"insert into sysbldregistered
	(bld_id)
values
(
	'%SYSBLDNAME%'
);

",
	"f",
	"delete from sysbldregistered
	where bld_id = '%SYSBLDNAME%';

",
	"f"
);