analytics/informix/etc/convTovNoSQL1210.sql

/* Run this script to make a database that was created in */ 
/* 12.10.xC1 JSON compatible. You must run this script if */
/* you are upgrading to 12.10.xC2 or any future fix pack. */

/*---------- NoSQL built-in UDT -------------------------*/

create opaque type informix.longlvarchar
        (
        internallength=variable,
        maxlen=4096,
        alignment=4,
        cannothash
        );

create opaque type informix.json
        (
        internallength=variable,
        maxlen=4096,
        alignment=4
        );

create opaque type informix.bson
        (
        internallength=variable,
        maxlen=4096,
        alignment=4
        );

/*---------- longlvarchar built-in UDT -------------------------*/

create implicit cast(lvarchar as longlvarchar with informix.llvc_in);
create implicit cast(longlvarchar as lvarchar with informix.llvc_out);
create explicit cast(longlvarchar as sendrecv with informix.llvc_send);
create implicit cast(sendrecv as longlvarchar with informix.llvc_recv);
create implicit cast(impexp as longlvarchar with informix.llvc_import_txt);
create explicit cast(longlvarchar as impexp with informix.llvc_export_txt);
create implicit cast(impexpbin as longlvarchar with informix.llvc_import_bin);
create explicit cast(longlvarchar as impexpbin with informix.llvc_export_bin);

create dba function informix.llvc_in(lvarchar) returns longlvarchar
with (not variant, parallelizable)
external name '(llvc_input)' language c;

create dba function informix.llvc_out(longlvarchar) returns lvarchar
with  (not variant, parallelizable)
external name '(llvc_output)' language c;

create dba function informix.llvc_send(longlvarchar) returns sendrecv
with (not variant, parallelizable)
external name '(llvc_send)' language c;

create dba function informix.llvc_recv(sendrecv) returns longlvarchar
with  (not variant, parallelizable)
external name '(llvc_recv)' language c;

create dba function informix.llvc_import_txt(impexp) returns longlvarchar
with (not variant, parallelizable)
external name '(llvc_import_txt)' language c;

create dba function informix.llvc_export_txt(longlvarchar) returns impexp
with (not variant, parallelizable)
external name '(llvc_export_txt)' language c;

create dba function informix.llvc_import_bin(impexpbin) returns longlvarchar
with (not variant, parallelizable)
external name '(llvc_import_bin)' language c;

create dba function informix.llvc_export_bin(longlvarchar) returns impexpbin
with (not variant, parallelizable)
external name '(llvc_export_bin)' language c;

create dba function informix.streamwrite(informix.stream, informix.longlvarchar) 
returns informix.integer 
external name '(llvc_streamwrite)' language c;

create dba function informix.streamread(informix.stream, out informix.longlvarchar) 
returns informix.integer 
external name '(llvc_streamread)' language c;

create dba function informix.assign(longlvarchar) returns longlvarchar
external name '(llvc_assign)' language c;

create dba procedure informix.destroy(longlvarchar)
external name '(llvc_destroy)' language c
end procedure;

create dba function informix.deepcopy(longlvarchar) returns longlvarchar
external name '(llvc_deep_copy)' language c;

create function informix.compare(informix.longlvarchar, informix.longlvarchar)
returns integer
external name '(llvc_cmp)' language c not variant;

create function informix.equal(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_eq)' language c not variant;

create function informix.notequal(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_ne)' language c not variant;

create function informix.greaterthan(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_gt)' language c not variant;

create function informix.lessthan(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_lt)' language c not variant;

create function informix.greaterthanorequal(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_ge)' language c not variant;

create function informix.lessthanorequal(informix.longlvarchar, informix.longlvarchar)
returns informix.boolean
external name '(llvc_le)' language c not variant;

grant  execute on function informix.llvc_in(lvarchar) to public as informix; 
grant  execute on function informix.llvc_out(longlvarchar) to public as informix;
grant  execute on function informix.llvc_send(longlvarchar) to public as informix; 
grant  execute on function informix.llvc_recv(sendrecv) to public as informix;
grant  execute on function informix.llvc_import_txt(impexp) to public as informix; 
grant  execute on function informix.llvc_export_txt(longlvarchar) to public as informix; 
grant  execute on function informix.llvc_import_bin(impexpbin) to public as informix;
grant  execute on function informix.llvc_export_bin(longlvarchar) to public as informix; 
grant  execute on function informix.assign(longlvarchar) to public as informix;
grant  execute on procedure informix.destroy(longlvarchar) to public as informix;
grant  execute on function informix.deepcopy(longlvarchar) to public as informix;
grant  execute on function informix.compare(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.equal(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.notequal(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.greaterthan(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.lessthan(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.greaterthanorequal(longlvarchar, longlvarchar) to public as informix;
grant  execute on function informix.lessthanorequal(longlvarchar, longlvarchar) to public as informix;

/*---------- json built-in UDT -------------------------*/

create implicit cast(lvarchar as json with informix.json_in);
create implicit cast(json as lvarchar with informix.json_out);
create explicit cast(json as sendrecv with informix.json_send);
create implicit cast(sendrecv as json with informix.json_recv);
create implicit cast(impexp as json with informix.json_import_txt);
create explicit cast(json as impexp with informix.json_export_txt);
create implicit cast(impexpbin as json with informix.json_import_bin);
create explicit cast(json as impexpbin with informix.json_export_bin);

create dba function informix.json_in(lvarchar) returns json
with (not variant, parallelizable)
external name '(json_input)' language c;

create dba function informix.json_out(json) returns lvarchar
with  (not variant, parallelizable)
external name '(json_out)' language c;

create dba function informix.json_send(json) returns sendrecv
with (not variant, parallelizable)
external name '(json_send)' language c;

create dba function informix.json_recv(sendrecv) returns json
with  (not variant, parallelizable)
external name '(json_recv)' language c;

create dba function informix.json_import_txt(impexp) returns json
with (not variant, parallelizable)
external name '(json_import_txt)' language c;

create dba function informix.json_export_txt(json) returns impexp
with (not variant, parallelizable)
external name '(json_export_txt)' language c;

create dba function informix.json_import_bin(impexpbin) returns json
with (not variant, parallelizable)
external name '(json_import_bin)' language c;

create dba function informix.json_export_bin(json) returns impexpbin
with (not variant, parallelizable)
external name '(json_export_bin)' language c;

create dba function informix.streamwrite(informix.stream, informix.json) 
returns informix.integer 
external name '(json_streamwrite)' language c;

create dba function informix.streamread(informix.stream, out informix.json) 
returns informix.integer 
external name '(json_streamread)' language c;

create dba function informix.assign(json) returns json
external name '(json_assign)' language c;

create dba procedure informix.destroy(json)
external name '(json_type_destroy)' language c
end procedure;

create dba function informix.deepcopy(json) returns json
external name '(json_deep_copy)' language c;

create function informix.compare(informix.json, informix.json)
returns integer
external name '(json_cmp)' language c not variant;

create function informix.equal(informix.json, informix.json)
returns informix.boolean
external name '(json_eq)' language c not variant;

create function informix.notequal(informix.json, informix.json)
returns informix.boolean
external name '(json_ne)' language c not variant;

grant  execute on function informix.json_in(lvarchar) to public as informix; 
grant  execute on function informix.json_out(json) to public as informix;
grant  execute on function informix.json_send(json) to public as informix; 
grant  execute on function informix.json_recv(sendrecv) to public as informix;
grant  execute on function informix.json_import_txt(impexp) to public as informix; 
grant  execute on function informix.json_export_txt(json) to public as informix; 
grant  execute on function informix.json_import_bin(impexpbin) to public as informix;
grant  execute on function informix.json_export_bin(json) to public as informix; 
grant  execute on function informix.assign(json) to public as informix;
grant  execute on procedure informix.destroy(json) to public as informix;
grant  execute on function informix.deepcopy(json) to public as informix;
grant  execute on function informix.compare(json, json) to public as informix;
grant  execute on function informix.equal(json, json) to public as informix;
grant  execute on function informix.notequal(json, json) to public as informix;

/*---------- bson built-in UDT -------------------------*/

create implicit cast(lvarchar as bson with informix.bson_in);
create implicit cast(bson as lvarchar with informix.bson_out);
create explicit cast(bson as sendrecv with informix.bson_send);
create implicit cast(sendrecv as bson with informix.bson_recv);
create implicit cast(impexp as bson with informix.bson_import_txt);
create explicit cast(bson as impexp with informix.bson_export_txt);
create implicit cast(impexpbin as bson with informix.bson_import_bin);
create explicit cast(bson as impexpbin with informix.bson_export_bin);

create dba function informix.bson_in(lvarchar) returns bson
with (not variant, parallelizable)
external name '(bson_input)' language c;

create dba function informix.bson_out(bson) returns lvarchar
with  (not variant, parallelizable)
external name '(bson_output)' language c;

create dba function informix.bson_send(bson) returns sendrecv
with (not variant, parallelizable)
external name '(bson_send)' language c;

create dba function informix.bson_recv(sendrecv) returns bson
with  (not variant, parallelizable)
external name '(bson_recv)' language c;

create dba function informix.bson_import_txt(impexp) returns bson
with (not variant, parallelizable)
external name '(bson_import_txt)' language c;

create dba function informix.bson_export_txt(bson) returns impexp
with (not variant, parallelizable)
external name '(bson_export_txt)' language c;

create dba function informix.bson_import_bin(impexpbin) returns bson
with (not variant, parallelizable)
external name '(bson_import_bin)' language c;

create dba function informix.bson_export_bin(bson) returns impexpbin
with (not variant, parallelizable)
external name '(bson_export_bin)' language c;

create dba function informix.streamwrite(informix.stream, informix.bson) 
returns informix.integer 
external name '(bson_streamwrite)' language c;

create dba function informix.streamread(informix.stream, out informix.bson) 
returns informix.integer 
external name '(bson_streamread)' language c;

create dba function informix.assign(bson) returns bson
external name '(bson_assign)' language c;

create dba procedure informix.destroy(bson)
external name '(bson_type_destroy)' language c
end procedure;

create dba function informix.deepcopy(bson) returns bson
external name '(bson_deep_copy)' language c;

create function informix.bson_extract(informix.bson, informix.lvarchar)
returns informix.bson
external name '(bson_extract)' language c not variant;

create function informix.compare(informix.bson, informix.bson)
returns integer
external name '(bson_cmpr)' language c not variant;

grant  execute on function informix.bson_in(lvarchar) to public as informix; 
grant  execute on function informix.bson_out(bson) to public as informix;
grant  execute on function informix.bson_send(bson) to public as informix; 
grant  execute on function informix.bson_recv(sendrecv) to public as informix;
grant  execute on function informix.bson_import_txt(impexp) to public as informix; 
grant  execute on function informix.bson_export_txt(bson) to public as informix; 
grant  execute on function informix.bson_import_bin(impexpbin) to public as informix;
grant  execute on function informix.bson_export_bin(bson) to public as informix; 
grant  execute on function informix.assign(bson) to public as informix;
grant  execute on procedure informix.destroy(bson) to public as informix;
grant  execute on function informix.deepcopy(bson) to public as informix;
grant  execute on function informix.bson_extract(bson, lvarchar) to public as informix;
grant  execute on function informix.compare(bson, bson) to public as informix;

create dba function informix.jsonoutput(informix.bson) returning informix.json
external name '(json_output)'
language c not variant;
grant  execute on function informix.jsonoutput(bson) to public as informix;

create dba function informix.bson_valid(informix.bson) returning informix.lvarchar
external name '(bson_valid)'
language c not variant;
grant  execute on function informix.bson_valid(informix.bson) to public as informix;

create explicit cast(informix.bson as informix.json with informix.jsonoutput); 
create implicit cast (informix.json as informix.bson with 
		informix.json_to_bson); 

create dba function informix.json_to_bson(informix.json) returning informix.bson
        external name '(json_to_bson_output)'
        language c not variant;
grant  execute on function informix.json_to_bson(json) to public as informix;
create dba function informix.ifx_shard_delete(informix.lvarchar, informix.lvarchar,
	informix.lvarchar)
	returning integer
	external name '(ifx_shard_delete)'
	language C not variant;
grant execute on function informix.ifx_shard_delete(informix.lvarchar,
	informix.lvarchar, informix.lvarchar)
	to public as informix;
create function informix.ifx_Collection_Create(informix.lvarchar, 
      informix.lvarchar, informix.lvarchar default NULL)
      returning integer
      with (handlesnulls)
      external name '(json_collection_create)'
      language C;
grant execute on function informix.ifx_Collection_Create(informix.lvarchar, 
      informix.lvarchar, informix.lvarchar ) 
      to public as informix;

create function informix.ifx_Collection_Drop( informix.lvarchar, 
      informix.lvarchar default NULL)
      returning integer
      with (handlesnulls)
      external name '(json_collection_drop)'
      language C;
grant execute on function informix.ifx_Collection_Drop( informix.lvarchar, 
      informix.lvarchar )
      to public as informix;

create function informix.ifx_Collection_Index_Create(
      informix.lvarchar, informix.lvarchar,
      informix.lvarchar, informix.lvarchar, informix.lvarchar,
      informix.INTEGER DEFAULT 0, informix.lvarchar DEFAULT NULL)
      returning integer
      with (handlesnulls)
      external name '(json_collection_index_create)'
      language C;
grant execute on function informix.ifx_Collection_Index_Create(
      informix.lvarchar, informix.lvarchar, informix.lvarchar,
      informix.lvarchar, informix.lvarchar, informix.INTEGER, 
      informix.lvarchar )
      to public as informix;

create function informix.ifx_Collection_Index_Drop( 
      informix.lvarchar, informix.lvarchar default NULL)
      returning integer
      with (handlesnulls)
      external name '(json_collection_index_drop)'
      language C;
grant execute on function informix.ifx_Collection_Index_Drop( 
      informix.lvarchar, informix.lvarchar) 
      to public as informix;

create function informix.ifx_explain(informix.lvarchar)
    returns lvarchar
    external name '(sql_sqexplain_single_statement)'
    language C;
grant execute on function informix.ifx_explain(informix.lvarchar)
      to public as informix;
create function informix.bson_explain(informix.lvarchar)
    returns bson
    external name '(bson_sqexplain_single_statement)'
    language C;
grant execute on function informix.bson_explain(informix.lvarchar)
      to public as informix;

create procedure informix.dummyrev_1210XC1()
end procedure;