analytics/informix/extend/TSPMatch.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%", 7, "searchrow",
	"%SYSBLDUSER%", 0,
	"create row type searchrow
(
	tsid    lvarchar,
	startts	datetime year to fraction(5),
	endts	datetime year to fraction(5),
	score	double precision
);

",
	"f",
	"drop row type searchrow 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%", 71, "searchrow",
	"%SYSBLDUSER%", 0,
	"grant usage on type searchrow to public;

",
	"f",
	"",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	71, "searchrow",
	"%SYSBLDUSER%",
	7, "searchrow",
	"%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, "install table",
	"%SYSBLDUSER%", 0,
	"
create table TS_INDEX_MAPPING(
indexid varchar(255),
tstabname varchar(128),
tsattrname varchar(128),
tsattrindex integer,
tspkvalue varchar(255),
idxtabname varchar(150),
idxtype integer,
idxmetatype integer,
start_dt datetime year to fraction(5),
end_dt datetime year to fraction(5),
highbound float,
lowbound float,
uniterror float,
slidewinlen integer,
aggwinlen integer,
parlen integer,
featuredim integer,
PRIMARY KEY (indexid) constraint PK_TS_INDEX_MAPPING
);

",
	"f",
	"
drop table TS_INDEX_MAPPING;

",
	"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, "ts_index_mapping_bsoncol",
	"%SYSBLDUSER%", 0,
	"ALTER TABLE ts_index_mapping ADD bsonid integer default 0;
",
	"f",
"
	create procedure cleanupbson()
	define sql_txt lvarchar;
	define del_txt lvarchar;
	define ts_tabname lvarchar;

	let sql_txt = ""SELECT idxtabname from ts_index_mapping where bsonid > 0"";

	PREPARE sql_stmt FROM sql_txt;
	DECLARE sql_cur CURSOR FOR sql_stmt;
	OPEN sql_cur;

	WHILE (1 = 1)
	FETCH sql_cur INTO ts_tabname;
	IF (SQLCODE != 0) THEN
	    EXIT WHILE;
	END IF;

	let del_txt = ""drop table if exists "" || ts_tabname;
	execute immediate del_txt;

	END WHILE
	CLOSE sql_cur;
	FREE sql_cur;
	FREE sql_stmt;
	END PROCEDURE;
	
	execute procedure cleanupbson();
	DROP PROCEDURE IF EXISTS cleanupbson;
	delete from ts_index_mapping where bsonid > 0;
	ALTER TABLE ts_index_mapping DROP bsonid;
",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "ts_index_mapping_bsoncol",
	"%SYSBLDUSER%",
	0, "install table",
	"%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, "bson_mapping_table",
	"%SYSBLDUSER%", 0,
"
create table TS_BSON_MAPPING(
colno   integer,
bsonkey varchar(255),
bsonid  serial,
PRIMARY KEY (colno, bsonkey) constraint PK_TS_BSON_MAPPING
);
",
	"f",
"
	drop table TS_BSON_MAPPING;
",
	"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, "install spls",
	"%SYSBLDUSER%", 0,
	"
CREATE FUNCTION DeleteIndexTable(idxtabname lvarchar)
RETURNING INTEGER;
define deleteidxtabsql lvarchar;
define deleteidxcfgsql lvarchar;
define  idxcfgtabname lvarchar;

let  idxcfgtabname = ""TS_INDEX_MAPPING"";

let deleteidxtabsql = ""drop table if exists "" || idxtabname ;
execute immediate deleteidxtabsql;

let deleteidxcfgsql = ""delete from "" || idxcfgtabname || "" where idxtabname = "" || ""'"" || idxtabname || ""'"";
execute immediate deleteidxcfgsql ;

RETURN 0;

END FUNCTION;

create procedure TSPIndexCleanup()
define sql_txt lvarchar;
define tabname_val lvarchar;
define ts_tabname lvarchar;
define res integer;

let res = 0;
let sql_txt = ""SELECT substring_index(tabname,'ts_index_', -1) FROM systables WHERE tabname matches 'ts_index*' and tabname <> 'ts_index_mapping'"";

PREPARE sql_stmt FROM sql_txt;
DECLARE sql_cur CURSOR FOR sql_stmt;
OPEN sql_cur;

WHILE (1 = 1)
    FETCH sql_cur INTO tabname_val;
    IF (SQLCODE != 0) THEN
       EXIT WHILE;
    END IF; 
    let ts_tabname = ""TS_Index_"" || tabname_val;
    let res = DeleteIndexTable(ts_tabname);

END WHILE
CLOSE sql_cur;
FREE sql_cur;
FREE sql_stmt;

let sql_txt = ""delete from TS_BSON_MAPPING"" ;
execute immediate sql_txt;

END PROCEDURE;

",
        "f",
        "
DROP FUNCTION IF EXISTS DeleteIndexTable;
DROP PROCEDURE IF EXISTS TSPIndexCleanup;

",
        "f" 
);

insert into sysbldobjdepends
(
        bld_id,
        obj_kind,
        obj_signature,
        obj_owner,
        need_obj_kind,
        need_obj_signature,
        need_obj_owner)
        values
(
        "%SYSBLDNAME%",
        0, "install spls",
        "%SYSBLDUSER%",
        0, "install 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, "install spls",
        "%SYSBLDUSER%",
        7, "searchrow",
        "%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, "install udrs",
        "%SYSBLDUSER%", 0,
        "
create function TSDropPatternIndex (TimeSeries,lvarchar,lvarchar,lvarchar,integer) returns integer
    external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(TSDropPatternIndex)""
    language c not variant;

grant execute on function TSDropPatternIndex (TimeSeries,lvarchar,lvarchar,lvarchar,integer) to public;

create function tsp_createworker (TimeSeries,lvarchar,datetime year to fraction(5),datetime year to fraction(5),integer default 0)   returns integer  with (HANDLESNULLS)
    external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(TSP_CreateWorker)""
    language c not variant;

create function TSCreatePatternIndex (TimeSeries,lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,lvarchar,integer default 0)	returns integer with (HANDLESNULLS)
    external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(TSCreatePatternIndex)""
    language c not variant;

grant execute on function TSCreatePatternIndex (TimeSeries,lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),lvarchar,lvarchar,integer) to public;

create function getPatternFromTS(TimeSeries, datetime year to fraction(5), datetime year to fraction(5), lvarchar) returns list (double precision not null)
	external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(getPatternFromTS)""
	language c not variant;

create function TSPatternMatch(TimeSeries,lvarchar,lvarchar,lvarchar,datetime year to fraction(5),datetime year to fraction(5),list (double precision not null), integer, double precision, double precision) returns list (searchrow not null)
    external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(TSPatternMatch)""
    language c not variant;

",
	"f",
	"
DROP FUNCTION IF EXISTS tsp_createworker;
DROP FUNCTION IF EXISTS TSCreatePatternIndex;
DROP FUNCTION IF EXISTS TSDropPatternIndex;
DROP FUNCTION IF EXISTS getPatternFromTS;
DROP FUNCTION IF EXISTS TSPatternMatch;


",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	0, "install udrs",
	"%SYSBLDUSER%",
	0, "install 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, "install udrs",
	"%SYSBLDUSER%",
	7, "searchrow",
	"%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%", 7, "patternrow",
	"%SYSBLDUSER%", 0,
	"create row type patternrow
(
	value	double precision
);

",
	"f",
	"drop row type patternrow 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%", 71, "patternrow",
	"%SYSBLDUSER%", 0,
	"grant usage on type patternrow to public;

",
	"f",
	"",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
	values
(
	"%SYSBLDNAME%",
	71, "patternrow",
	"%SYSBLDUSER%",
	7, "patternrow",
	"%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%", 5, "TSPMatchRelease ()",
        "%SYSBLDUSER%", 0,
        "create function TSPMatchRelease ()
returns lvarchar with (not variant, handlesnulls)
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(TSPMatchRelease)"" language c;

",
        "f",
        "drop function TSPMatchRelease ();

",
        "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%", 69, "TSPMatchRelease ()",
        "%SYSBLDUSER%", 0,
        "grant execute on function TSPMatchRelease () to public;

",
        "f",
        "",
        "f"
);

insert into sysbldobjdepends
(
        bld_id,
        obj_kind,
        obj_signature,
        obj_owner,
        need_obj_kind,
        need_obj_signature,
        need_obj_owner)
        values
(
        "%SYSBLDNAME%",
        69, "TSPMatchRelease ()",
        "%SYSBLDUSER%",
        5, "TSPMatchRelease ()",
        "%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, "debugtrace",
	"%SYSBLDUSER%", 0,
	"insert into informix.systraceclasses(name) values('TSPM_DEBUG');
",
	"f",
	"delete from informix.systraceclasses where name='TSPM_DEBUG';

",
	"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%", 5, "TSPMatchSetTraceLevel (lvarchar)",
	"%SYSBLDUSER%", 0,
	"create function TSPMatchSetTraceLevel (lvarchar)
returns int
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(tspm_tracelevel_set)"" language c;

",
	"f",
	"drop function TSPMatchSetTraceLevel (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%", 69, "TSPMatchSetTraceLevel (lvarchar)",
	"%SYSBLDUSER%", 0,
	"grant execute on function TSPMatchSetTraceLevel (lvarchar) to public;

",
	"f",
	"",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
values
(
	"%SYSBLDNAME%",
	69, "TSPMatchSetTraceLevel (lvarchar)",
	"%SYSBLDUSER%", 
	5, "TSPMatchSetTraceLevel (lvarchar)",
	"%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%", 5, "TSPMatchSetTraceFile (lvarchar)",
	"%SYSBLDUSER%", 0,
	"create function TSPMatchSetTraceFile (lvarchar)
returns int
external name ""$INFORMIXDIR/extend/%SYSBLDDIR%/TSPMatch.bld(tspm_tracefile_set)"" language c;

",
	"f",
	"drop function TSPMatchSetTraceFile (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%", 69, "TSPMatchSetTraceFile (lvarchar)",
	"%SYSBLDUSER%", 0,
	"grant execute on function TSPMatchSetTraceFile (lvarchar) to public;

",
	"f",
	"",
	"f"
);

insert into sysbldobjdepends
(
	bld_id,
	obj_kind,
	obj_signature,
	obj_owner,
	need_obj_kind,
	need_obj_signature,
	need_obj_owner)
values
(
	"%SYSBLDNAME%",
	69, "TSPMatchSetTraceFile (lvarchar)",
	"%SYSBLDUSER%", 
	5, "TSPMatchSetTraceFile (lvarchar)",
	"%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, "sysbldregistered",
	"%SYSBLDUSER%", 0,
	"insert into sysbldregistered
	(bld_id)
values
(
	""%SYSBLDNAME%""
);

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

",
	"f"
);