--
--
--                                GBase.
-- 
--                           PROPRIETARY DATA
--
--  Licensed Material - Property Of GBase
-- 
--  "Restricted Materials of GBase"
-- 
--  GBase Client SDK
-- 
--  Copyright GBASE  2008. All rights reserved.
-- 
-- ****************************************************************************
--

-- coledbp.sql - creates GBasedbt OLE DB Provider support objects
-- DBA runs this file (against sysmaster only) to create SQL objects
-- provider needs to connect to any database on the server

--Run the whole thing in transaction
Begin work;

-- versions of the server and OLE DB support
create table gbasedbt.oledbversion(
	srvrver varchar(18),
	oledbver smallint, -- minor
	oleobjsmodtime datetime year to fraction,
	nullc char(1));
insert into gbasedbt.oledbversion(srvrver, oledbver)
	select owner, 3 from systables where tabname = ' VERSION';
revoke all on gbasedbt.oledbversion from public;
grant select on gbasedbt.oledbversion to public;

-- table types
create table gbasedbt.oledbtabtypes(
	typecode char(1),
	issystem varchar(5),
	typedesc varchar(18),
	primary key(typecode, issystem));
insert into gbasedbt.oledbtabtypes values( 'T', 'False', 'TABLE' );
insert into gbasedbt.oledbtabtypes values( 'T', 'True', 'SYSTEM TABLE' );
insert into gbasedbt.oledbtabtypes values( 'V', 'False', 'VIEW' );
insert into gbasedbt.oledbtabtypes values( 'V', 'True', 'VIEW' );
insert into gbasedbt.oledbtabtypes values( 'P', 'False', 'SYNONYM' );
insert into gbasedbt.oledbtabtypes values( 'S', 'False', 'SYNONYM' );
revoke all on gbasedbt.oledbtabtypes from public;
grant select on gbasedbt.oledbtabtypes to public;

-- bookmark by table type and fragmentation
create table gbasedbt.oledbbmk(
	tabtype char(1),
	fragmented smallint,
	withrowids smallint,
	hasbmk varchar(5),
	bmktype int,
	bmkdtype smallint,
	bmkmaxlen int,
	primary key(tabtype, fragmented, withrowids));
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk,
		bmktype, bmkdtype, bmkmaxlen)
	values('T', 0, 0, 'True', 1, 3, 4);
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk,
		bmktype, bmkdtype, bmkmaxlen)
	values('T', -1, 1, 'True', 1, 3, 4);
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk)
	values('T', -1, 0, 'False');
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk) 
	values('V', 0, 0, 'False');
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk) 
	values('P', 0, 0, 'False');
insert into gbasedbt.oledbbmk(tabtype, fragmented, withrowids, hasbmk) 
	values('S', 0, 0, 'False');
revoke all on gbasedbt.oledbbmk from public;
grant select on gbasedbt.oledbbmk to public;

-- data types (types in <> are not included in PROVIDER_TYPES)
create table gbasedbt.oledbtypes(
	typename varchar(128),
	datatype smallint,
	columnsize int,
	literalprefix varchar(40),
	literalsuffix varchar(40),
	createparams varchar(18),
	isnullable varchar(5),
	casesensitive varchar(5),
	searchable int,
	unsignedattribute varchar(5),
	fixedprecscale varchar(5),
	autouniquevalue varchar(5),
	minimumscale smallint,
	maximumscale smallint,
	islong varchar(5),
	bestmatch varchar(5),
	is_fixedlength varchar(5),
	xstid int, -- xid shifted + tid
	qualLen smallint,
	udonly varchar(5),
	primary key(xstid, qualLen));
    
insert into gbasedbt.oledbtypes values ( 
	'character', 129, 32767, '''', '''', 'length', 'True', 'True', 
	4, null, 'False', 'False', null, null, 'False', 'True', 'True', 0, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'smallint', 2, 5, null, null, null, 'True', 'False',
	3, 'False', 'True', 'False', null, null, 'False', 'True', 'True', 1, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'integer', 3, 10, null, null, null, 'True', 'False',
	3, 'False', 'True', 'False', null, null, 'False', 'True', 'True', 2, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'float', 5, 15, null, null, null, 'True', 'False',
	3, 'False', 'False', 'False', null, null, 'False', 'True', 'True', 3, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'smallfloat', 4, 6, null, null, null, 'True', 'False', 
	3, 'False', 'False', 'False', null, null, 'False', 'True', 'True', 4, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'decimal', 131, 32, null, null, 'precision', 'True', 'False',
	3, 'False', 'False', 'False', null, null, 'False', 'True', 'False', 5, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'serial', 3, 10, null, null, null, 'False', 'False',
	3, 'False', 'True', 'True', null, null, 'False', 'False', 'True', 6, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'date', 133, 4, '''', '''', null, 'True', 'False',
	3, null, 'False', 'False', null, null, 'False', 'True', 'True', 7, 0, 'False' );
insert into gbasedbt.oledbtypes values(
	'money', 6, 32, null, null, 'precision,scale', 'True', 'False',
	3, 'False', 'True', 'False', 0, 32, 'False', 'True', 'True', 8, 0, 'False' );
insert into gbasedbt.oledbtypes values (
	'datetime year to day', 133, 25, '''', '''', null, 'True', 'False',
	3, null, 'False', 'False', null, null, 'False', 'True', 'True', 10, 2052, 'False' );
insert into gbasedbt.oledbtypes values (
	'datetime year to fraction(5)', 135, 25, '''', '''', null, 'True', 'False',
	3, null, 'False', 'False', null, null, 'False', 'True', 'True', 10, 4879, 'False' );
insert into gbasedbt.oledbtypes values (
	'datetime hour to second', 134, 25, '''', '''', null, 'True', 'False',
	3, null, 'False', 'False', null, null, 'False', 'True', 'True', 10, 1642, 'False' );
insert into gbasedbt.oledbtypes values (
	'<byte>', 128, 2147483647, null, null, null, 'False', 'False',
	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 11, 0, 'False' );
insert into gbasedbt.oledbtypes values (
	'<text>', 129, 2147483647, null, null, null, 'False', 'False',
	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 12, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'varchar', 129, 255, '''', '''', 'max length', 'True', 'True',  
	4, null, 'False', 'False', null, null, 'False', 'False', 'False', 13, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'interval day(9) to fraction(5)', 129, 24, null, null, null, 'True', 'False',
	3, null, 'True', 'False', 1, 5, 'False', 'False', 'True', 14, 5199, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'interval year(9) to month', 129, 12, null, null, null, 'True', 'False',
	3, null, 'True', 'False', 1, 2, 'False', 'False', 'True', 14, 2818, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'nchar', 129, 32767, '''', '''', 'length', 'True', 'True',
	4, null, 'False', 'False', null, null, 'False', 'False', 'True', 15, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'nvarchar', 129, 255, '''', '''', 'max length', 'True', 'True', 
	4, null, 'False',  'False', null, null, 'False', 'False', 'False', 16, 0, 'False' );
insert into gbasedbt.oledbtypes values ( 
	'int8', 20, 19, null, null, null, 'True', 'False',
	3, 'False', 'True', 'False', null, null, 'False', 'True', 'True', 17, 0, 'True' );
insert into gbasedbt.oledbtypes values ( 
	'serial8', 20, 19, null, null, null, 'False', 'False',
	3, 'False', 'True', 'True', null, null, 'False', 'False', 'True', 18, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'lvarchar', 129, 2048, '''', '''', null, 'True', 'True',
	4, null, 'False', 'False', null, null, 'False', 'False', 'False', 296, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'boolean', 11, 1, 'sysmaster:gbasedbt.oledbmakebool(''', ''')', null, 'True', 'False',
	3, null, 'False', 'False', null, null, 'False', 'True', 'True', 1321, 0, 'True' );
insert into gbasedbt.oledbtypes values( 
	'<blob>', 128, 2147483647, null, null, null, 'True', 'False',
	1, null, 'False', 'False', null, null, 'True', 'False', 'False', 2601, 0, 'True' );
insert into gbasedbt.oledbtypes values( 
	'<clob>', 129, 2147483647, null, null, null, 'True', 'False',
	1, null, 'False', 'False', null, null, 'True', 'False', 'False', 2857, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'<udtvar>', 128, 0, '''', '''', null, 'True', 'True',
	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 40, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'<udtfixed>', 128, 0, '''', '''', null, 'True', 'False',
	1, null, 'False', 'False', null, null, 'False', 'False', 'True', 41, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'<set>', 12, 0, '''', '''', null, 'True', 'True',
	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 19, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'<multiset>', 12, 0, '''', '''', null, 'True', 'True',
	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 20, 0, 'True' );
insert into gbasedbt.oledbtypes values (
	'<list>', 12, 0, '''', '''', null, 'True', 'True',
	1, null, 'False', 'False', null, null, 'False', 'True', 'False', 21, 0, 'True' );
insert into gbasedbt.oledbtypes values (
    	'<row>', 12, 0, '''', '''', null, 'True', 'True',
    	1, null, 'False', 'False', null, null, 'False', 'False', 'False', 22, 0, 'True' );

create procedure gbasedbt.insertoledbtypes()

    define maj_ver integer;
    define min_ver integer;

    -- Get the IDS version    
    select dbinfo('version', 'major') into maj_ver from systables WHERE tabid = 1;
    
    select dbinfo('version', 'minor') into min_ver from systables where tabid = 1;
    
    -- BIGINT/BIGSERIAL is not supported below 11.50 server
    if ( ( maj_ver = 11 and min_ver >= 50) or (maj_ver > 11) ) then
    
        insert into gbasedbt.oledbtypes values (
    	    'bigint', 20, 19, null, null, null, 'True', 'False',
    	    3, 'False', 'True', 'False', null, null, 'False', 'True', 'True', 23, 0, 'True' );
        insert into gbasedbt.oledbtypes values ( 
    	    'bigserial', 20, 19, null, null, null, 'False', 'False',
    	    3, 'False', 'True', 'True', null, null, 'False', 'False', 'True', 24, 0, 'True' );
    
    end if;

end procedure;

execute procedure gbasedbt.insertoledbtypes();

drop procedure gbasedbt.insertoledbtypes;

revoke all on gbasedbt.oledbtypes from public;
grant select on gbasedbt.oledbtypes to public;

-- storage and OLE objects (used only with UD)
create table gbasedbt.oledboleobjects(
	database varchar(32) not null,
	owner varchar(32) not null,
	name varchar(128) not null,
	comadapter varchar(5) not null,	
	persiststorage varchar(5) not null,		
	progid varchar(128),
	primary key(database, owner, name));
revoke all on gbasedbt.oledboleobjects from public;
grant select on gbasedbt.oledboleobjects to public;

-- privilege types
create table gbasedbt.oledbprivtypes(
	code char(1),
	name varchar(16),
	is_grantable varchar(5),
	primary key(code));
insert into gbasedbt.oledbprivtypes values ('s', 'SELECT', 'False');
insert into gbasedbt.oledbprivtypes values ('S', 'SELECT', 'True');
insert into gbasedbt.oledbprivtypes values ('u', 'UPDATE', 'False');
insert into gbasedbt.oledbprivtypes values ('U', 'UPDATE', 'True');
insert into gbasedbt.oledbprivtypes values ('i', 'INSERT', 'False');
insert into gbasedbt.oledbprivtypes values ('I', 'INSERT', 'True');
insert into gbasedbt.oledbprivtypes values ('d', 'DELETE', 'False');
insert into gbasedbt.oledbprivtypes values ('D', 'DELETE', 'True');
insert into gbasedbt.oledbprivtypes values ('r', 'REFERENCES', 'False');
insert into gbasedbt.oledbprivtypes values ('R', 'REFERENCES', 'True');
revoke all on gbasedbt.oledbprivtypes from public;
grant select on gbasedbt.oledbprivtypes to public;

-- constraint types
create table gbasedbt.oledbconstrtypes(
	code char(1),
	name varchar(16),
	isprimary varchar(5),
	isunique varchar(5),
	primary key(code));
insert into gbasedbt.oledbconstrtypes values('C', 'CHECK', 'False', 'False');
insert into gbasedbt.oledbconstrtypes values('N', 'CHECK', 'False', 'False');
insert into gbasedbt.oledbconstrtypes values('P', 'PRIMARY KEY', 'True', 'True');
insert into gbasedbt.oledbconstrtypes values('F', 'FOREIGN KEY', 'False', 'False');
insert into gbasedbt.oledbconstrtypes values('U', 'UNIQUE', 'False', 'True');
revoke all on gbasedbt.oledbconstrtypes from public;
grant select on gbasedbt.oledbconstrtypes to public;

-- constraint deferral status (we consider filtering non-deferred)
create table gbasedbt.oledbconstrdefers(
	objstate char(1),
	isdeferred varchar(5),
	deferrable varchar(5),
	issystab varchar(5),
	code smallint,
	primary key(objstate, issystab));
insert into gbasedbt.oledbconstrdefers values('D', 'True', 'True', 'False', 2);
insert into gbasedbt.oledbconstrdefers values('E', 'False', 'True', 'False', 3);
insert into gbasedbt.oledbconstrdefers values('E', 'False', 'False', 'True', 1);
insert into gbasedbt.oledbconstrdefers values('F', 'False', 'True', 'False', 1);
revoke all on gbasedbt.oledbconstrdefers from public;
grant select on gbasedbt.oledbconstrdefers to public;

-- usage types
create table gbasedbt.oledbusagetypes(
	tabname char(18),
	usagetype varchar(18),
	primary key(tabname));
insert into gbasedbt.oledbusagetypes values( ' GL_CTYPE', 'CHARACTER SET' );
insert into gbasedbt.oledbusagetypes values( ' GL_COLLATE', 'COLLATION' );
revoke all on gbasedbt.oledbusagetypes from public;
grant select on gbasedbt.oledbusagetypes to public;

-- procedure parameter ordinals
create table gbasedbt.oledbordinals(
	ordinal int,
	primary key(ordinal));
insert into gbasedbt.oledbordinals values( 1 );
insert into gbasedbt.oledbordinals values( 2 );
insert into gbasedbt.oledbordinals values( 3 );
insert into gbasedbt.oledbordinals values( 4 );
insert into gbasedbt.oledbordinals values( 5 );
insert into gbasedbt.oledbordinals values( 6 );
insert into gbasedbt.oledbordinals values( 7 );
insert into gbasedbt.oledbordinals values( 8 );
insert into gbasedbt.oledbordinals values( 9 );
insert into gbasedbt.oledbordinals values( 10 );
insert into gbasedbt.oledbordinals values( 11 );
insert into gbasedbt.oledbordinals values( 12 );
insert into gbasedbt.oledbordinals values( 13 );
insert into gbasedbt.oledbordinals values( 14 );
insert into gbasedbt.oledbordinals values( 15 );
insert into gbasedbt.oledbordinals values( 16 );
insert into gbasedbt.oledbordinals values( 17 );
insert into gbasedbt.oledbordinals values( 18 );
insert into gbasedbt.oledbordinals values( 19 );
insert into gbasedbt.oledbordinals values( 20 );
insert into gbasedbt.oledbordinals values( 21 );
insert into gbasedbt.oledbordinals values( 22 );
insert into gbasedbt.oledbordinals values( 23 );
insert into gbasedbt.oledbordinals values( 24 );
insert into gbasedbt.oledbordinals values( 25 );
insert into gbasedbt.oledbordinals values( 26 );
insert into gbasedbt.oledbordinals values( 27 );
insert into gbasedbt.oledbordinals values( 28 );
insert into gbasedbt.oledbordinals values( 29 );
insert into gbasedbt.oledbordinals values( 30 );
insert into gbasedbt.oledbordinals values( 31 );
insert into gbasedbt.oledbordinals values( 32 );
revoke all on gbasedbt.oledbordinals from public;
grant select on gbasedbt.oledbordinals to public;

-- True if system table, else False
create procedure gbasedbt.oledbissystab(tabid int)
returning varchar(5);
if tabid < 100 then return 'True';
else return 'False';
end if;
end procedure;

-- True if the value is not null, else False
create procedure gbasedbt.oledbisnotnull(val char(1)) 
returning varchar(5);
if val is null then return 'False';
else return 'True';
end if;
end procedure;

-- default
create procedure gbasedbt.oledbcoldef(deftype char(1), deftext char(127))
returning varchar(127);
if deftype = 'U' then return 'USER';
elif deftype = 'C' then return 'CURRENT';
elif deftype = 'N' then return null;
elif deftype = 'T' then return 'TODAY';
elif deftype = 'S' then return DBSERVERNAME;
else return trim(deftext);
end if;
end procedure;

-- True if nullable, else False
create procedure gbasedbt.oledbcolnullable(coltype smallint)
returning varchar(5);
if mod(coltype, 512) < 256 then return 'True';
else return 'False';
end if;
end procedure;

-- length if var-len type, else null
create procedure gbasedbt.oledbcollen(xstid int, coltype smallint, oledbtype smallint, collength int)
returning int;
define actual_len int;
define class1 int;
define class2 int;
if oledbtype in (8, 129, 130) then 
	if oledbtype  = 129 AND (mod (coltype, 256) = 14) then
		let actual_len = TRUNC(collength/256);
		let class1 = TRUNC( (MOD(collength, 256)) / 16 );
		let class2 = TRUNC ( MOD(MOD(collength, 256), 16));

		if ((class1 = 0 and class2 = 2) or (class1 = 4 and class2 = 6) or (class1 = 8 and class2 = 10) or (class1 = 6 and class2 = 8 ) or (class1 = 10 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15)))then
			let actual_len = actual_len + 2;
		else 
			if (class1 = class2) then 
				let actual_len = actual_len + 1;
			else 
				if ((class1 = 4 and class2 = 8 ) or (class1 = 6 and class2 = 10) or (class1 = 8 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15))) then
					let actual_len = actual_len + 3;
				else 
					if ((class1 = 4 and class2 = 10) or (class1 = 6 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15))) then
						let actual_len = actual_len + 4;
					else 
						if ((class1 = 4 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15))) then
							let actual_len= actual_len + 5;
						else
							if ((class1 = 10 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15)) or (class1 = 12 and (class2 = 11 or class2 = 12 or class2 = 13 or class2 = 14 or class2 = 15))) then
								let actual_len = actual_len + 2;
							end if;
						end if;
					end if;
				end if;
			end if;
		end if;

		return actual_len;	
	else
        -- VARCHAR (13)
        if oledbtype  = 129 AND (mod (coltype, 256) = 13) then
            let actual_len = (collength - (trunc(collength / 256))*256);
            return actual_len;
        else
            return collength;
        end if;
	end if;
else return null;
end if;
end procedure;

-- precision if appropriate, else null
create procedure gbasedbt.oledbcolprec(oledbtype smallint, collength smallint)
returning smallint;
if oledbtype in (131, 139, 6) then 
	if (mod(collength, 256)=255) then
		if ((2 * TRUNC(collength / 256)) < 32 ) then
			return 2 * (TRUNC(collength / 256));
		else
			return 32;
		end if;
	else
		return collength / 256; 
	end if;
elif oledbtype = 2 then return 5;
elif oledbtype = 3 then return 10;
elif oledbtype = 20 then return 19;
elif oledbtype = 4 then return 7;
elif oledbtype = 5 then return 15;
else return null;
end if;
end procedure;

-- scale if appropriate, else null
create procedure gbasedbt.oledbcolscale1(xstid int, collength smallint)
returning smallint;
define topart smallint;
if xstid in (8, 5) then
	if (mod(collength, 256) = 255) then
		if ((collength / 256) < 16 ) then
			return TRUNC(collength / 256);
		else
			return (12 +((32-TRUNC(collength /256)) / 4));
		end if;
	else
		return mod(collength, 256);
	end if;
elif xstid = 14 then
	let topart = mod(collength, 16);
	if( topart < 11 ) then
		return 0;
	else
		return topart - 10;
	end if;
else
	return null;
end if;
end procedure;

-- scale if appropriate, else null
-- deprecated as of 1.2
create procedure gbasedbt.oledbcolscale(oledbtype smallint, collength smallint)
returning smallint;
define colprec smallint;
if oledbtype = 131 then
	let colprec = collength / 256;
	return collength - colprec * 256;
else 
	return null;
end if;
end procedure;

-- Get the correct datatype. For MONEY datatype, if scale >4 then the 
-- correct type is DBTYPE_NUMERIC (131)
create procedure gbasedbt.oledbtype1(oledbtype smallint, scale smallint )
returning smallint;
define col_type smallint;
if oledbtype = 6 and scale >4 then
	 let col_type = 131;
else
    let col_type = oledbtype;
end if;
return col_type;
end procedure;


-- DBCOLUMNFLAGS
create procedure gbasedbt.oledbcolflags(xstid int, coltype int)
returning int;
define flags int;
let flags = 24; -- fixed and maybe writable
if mod(coltype, 512) < 256 and xstid <> 6 and xstid <> 18 and xstid <> 24 then 
	let flags = flags + 96; -- col accepts nulls
end if;

if xstid in (2601, 2857, 11, 12) then 
	let flags = flags + 112; -- smartblob, long and var-len
elif xstid in ( 13,16, 19, 20, 21, 22, 40, 296) then
	let flags = flags - 16; -- var-len
end if;
return flags;  
end procedure;

-- xid shifted + tid, suitable for match with oledbtypes
-- pretends that decimal is float if decasr8 = 1
create procedure gbasedbt.oledbmakexstid1(tid smallint, xid smallint, 
	decasr8 smallint)
returning int;
if 256 <= mod(tid, 512) then let tid = tid - 256; end if;
if 2048 < mod(tid, 4096) then let tid = tid - 2048; end if;
if tid = 16425 then -- distinct of boolean
	return 1321;
elif tid = 8232 then -- distinct of lvarchar
	return 296;
elif tid = 4118 then -- named row
	return 22;
elif tid in (19, 20, 21, 22) then -- unnamed complex
	return tid;
elif tid = 40 and xid <> 1 then -- udtvar
	return 40;
elif tid = 41 and xid not in (5, 10, 11) then -- udtfixed
	return 41;
elif decasr8 = 1 and tid = 5 and xid = 0 then
	return 3; 
else
	return 256 * xid + tid;
end if;
end procedure;

-- xid shifted + tid, suitable for match with oledbtypes
-- deprecated as of 1.2
create procedure gbasedbt.oledbmakexstid(tid smallint, xid smallint)
returning int;
if 256 < mod(tid, 512) then let tid = tid - 256; end if;
if 2048 < mod(tid, 4096) then let tid = tid - 2048; end if;
if tid = 16425 then -- distinct of boolean
	return 1321;
elif tid = 8232 then -- distinct of lvarchar
	return 296;
elif tid = 4118 then -- named row
	return 22;
elif tid in (19, 20, 21, 22) then -- unnamed complex
	return tid;
elif tid = 40 and xid <> 1 then -- udtvar
	return 40;
elif tid = 41 and xid not in (5, 10, 11) then -- udtfixed
	return 41;
else
	return 256 * xid + tid;
end if;
end procedure;

create procedure gbasedbt.oledbMatchQualInfo(colType smallint, sysColLen smallint, oleTypLen smallint)
returning int;
if mod(colType, 256) = 10 then
   if (
        (sysColLen in (1024, 1538, 2052, 546, 1060, 580)
           AND oleTypLen = 2052) or
        (sysColLen in (614, 1128, 1642, 648, 1162, 682)
           AND oleTypLen = 1642) or
        (sysColLen in (2566, 3080, 3594, 3851, 4108, 4365, 4622, 4879, 
                       1574, 2088, 2602, 2859, 3116, 3373, 3630, 3887, 
                       1094, 1608, 2122, 2379, 2636, 2893, 3150, 3407, 
                       1899, 2156, 2413, 2670, 2927, 
                       1419, 1676, 1933, 2190, 2447, 
                       939,  1196, 1453, 1710, 1967, 
                       459,  716,  973,  1230, 1487)
           AND oleTypLen = 4879)
	  )
   then
      return 1;
   else
      return 0;
   end if;
elif mod(colType, 256) = 14 then
   if (
   -- Year- Month interval
        (sysColLen in (256, 512, 768, 1024, 1280, 1536, 1792, 2048, 2304,
		       770, 1026, 1282, 1538, 1794, 2050, 2306, 2562,
		       2818, 290, 546, 802, 1058, 1314, 1570, 1826, 2082,
 		       2338)
           AND oleTypLen = 2818) or
   -- Day-time interval  
        (sysColLen in (324, 580, 836, 1092, 1348, 1604, 1860, 2116, 2372, 
		       838, 1094, 1350, 1606, 1862, 2118, 2374, 2630, 2886,
		       1352, 1608, 1864, 2120, 2376, 2632, 2888, 3144, 3400, 
		       1866, 2122, 2378, 2634, 2890, 3146, 3402, 3658, 3914,
		       2123, 2379, 2635, 2891, 3147, 3403, 3659, 3915, 4171,
		       2380, 2636, 2892, 3148, 3404, 3660, 3916, 4172, 4428,
		       2637, 2893, 3149, 3405, 3661, 3917, 4173, 4429, 4685,
		       2894, 3150, 3406, 3662, 3918, 4174, 4430, 4686, 4942,
		       3151, 3407, 3663, 3919, 4175, 4431, 4687, 4943, 5199,
		       358, 614, 870, 1126, 1382, 1638, 1894, 2150, 2406, 
		       872, 1128, 1384, 1640, 1896, 2152, 2408, 2664, 2920,
		       1386, 1642,1898, 2154, 2410, 2666, 2922, 3178, 3434,
		       1643, 1899, 2155, 2411, 2667, 2923, 3179, 3435, 3691,
		       1900, 2156, 2412, 2668, 2924, 3180, 3436, 3692, 3948,
		       2157, 2413, 2669, 2925, 3181, 3437, 3693,3949, 4205,
		       2414, 2670, 2926, 3182, 3438, 3694, 3950, 4206, 4462,
		       2671, 2927, 3183, 3439, 3695, 3951, 4207, 4463, 4719,
		       392, 648, 904, 1160, 1416, 1672, 1928, 2184, 2440,
		       906, 1162, 1418, 1674, 1930, 2186, 2442, 2698, 2954,
  		       1163, 1419, 1675, 1931, 2187, 2443, 2699, 2955, 3211,
		       1420, 1676, 1932, 2188, 2444, 2700, 2956, 3212,3468,
		       1677, 1933, 2189, 2445, 2701, 2957, 3213, 3469, 3725,
		       1934, 2190, 2446, 2702, 2958, 3214, 3470, 3726, 3982,
		       2191, 2447, 2703, 2959, 3215, 3471, 3727, 3983, 4239,
		       426, 682, 938, 1194, 1450, 1706, 1962, 2218, 2474,
		       683, 939, 1195, 1451, 1707, 1963, 2219, 2475, 2731,
		       940, 1196, 1452, 1708, 1964, 2220, 2476, 2732, 2988,
		       1197, 1453, 1709, 1965, 2221, 2477, 2733, 2989, 3245,
		       1454, 1710, 1966, 2222, 2478, 2734, 2990, 3246, 3502,
		       1711, 1967, 2223, 2479, 2735, 2991, 3247, 3503, 3759,
		       459, 716, 973, 1230, 1487)
           AND oleTypLen = 5199)
	  )
   then
      return 1;
   else
      return 0;
   end if;
else
   return 1;
end if;

end procedure;
	
-- constraint rule
create procedure gbasedbt.oledbconstrrule(code char(1))
returning varchar(10);
if code = 'C' then return 'CASCADE';
else return 'RESTRICT';
end if;
end procedure;

-- ordinal if in the key, else 0
create procedure gbasedbt.oledbkeypart(
	part1 int, part2 int, part3 int, part4 int, part5 int, part6 int, 
	part7 int, part8 int, part9 int, part10 int, part11 int, part12 int, 
	part13 int, part14 int, part15 int, part16 int, val int)
returning int;
if abs(part1) = val then return 1;
elif abs(part2) = val then return 2;
elif abs(part3) = val then return 3;
elif abs(part4) = val then return 4;
elif abs(part5) = val then return 5;
elif abs(part6) = val then return 6;
elif abs(part7) = val then return 7;
elif abs(part8) = val then return 8;
elif abs(part9) = val then return 9;
elif abs(part10) = val then return 10;
elif abs(part11) = val then return 11;
elif abs(part12) = val then return 12;
elif abs(part13) = val then return 13;
elif abs(part14) = val then return 14;
elif abs(part15) = val then return 15;
elif abs(part16) = val then return 16;
else return 0;
end if;
end procedure;

-- alternate if value is null else value
create procedure gbasedbt.oledbnvl(v varchar(127), a varchar(127))
returning varchar(127);
if (v is null) then return a;
else return v;
end if;
end procedure;

-- True if contains with check option, else False
-- deprecated as of 1.1
create procedure gbasedbt.oledbwithcheck(
	v1 char(64), v2 char(64), v3 char(64), v4 char(64))
returning varchar(5);
if (v1 like '%with_check_option%' or v2 like '%with_check_option%' 
	or v3 like '%with_check_option%' or v4 like '%with_check_option%')
then return 'True';
else return 'False';
end if;
end procedure;

-- 1.1 replacement for oledbwithcheck
create procedure gbasedbt.oledbhaswcheck(
	v1 char(64), v2 char(64), v3 char(64), v4 char(64),
	v5 char(64), v6 char(64), v7 char(64), v8 char(64),
	v9 char(64), v10 char(64), v11 char(64), v12 char(64),
	v13 char(64), v14 char(64), v15 char(64), v16 char(64))
returning varchar(5);
if (v1 like '%with_check_option%' or v2 like '%with_check_option%' 
	or v3 like '%with_check_option%' or v4 like '%with_check_option%'
	or v5 like '%with_check_option%' or v6 like '%with_check_option%'
	or v7 like '%with_check_option%' or v8 like '%with_check_option%'
	or v9 like '%with_check_option%' or v10 like '%with_check_option%'
	or v11 like '%with_check_option%' or v12 like '%with_check_option%'
	or v13 like '%with_check_option%' or v14 like '%with_check_option%'
	or v15 like '%with_check_option%' or v16 like '%with_check_option%')
then return 'True';
else return 'False';
end if;
end procedure;

-- True if index is clustered, else False
create procedure gbasedbt.oledbidxclustered(c char(1))
returning varchar(5);
if c = 'C' then return 'True';
else return 'False';
end if;
end procedure;

-- True if index is integrated, else False
create procedure gbasedbt.oledbidxintegr(tablecols int, part1 int, part2 int,
part3 int, part4 int, part5 int, part6 int, part7 int, part8 int, part9
int, part10 int, part11 int, part12 int, part13 int, part14 int, part15
int, part16 int)
returning varchar(5);
define indxcols int;
let indxcols=0;
if part1=0 then let indxcols=0;
elif part2=0 then let indxcols=1;
elif part3=0 then let indxcols=2; 
elif part4=0 then let indxcols=3; 
elif part5=0 then let indxcols=4; 
elif part6=0 then let indxcols=5; 
elif part7=0 then let indxcols=6; 
elif part8=0 then let indxcols=7; 
elif part9=0 then let indxcols=8; 
elif part10=0 then let indxcols=9; 
elif part11=0 then let indxcols=10; 
elif part12=0 then let indxcols=11; 
elif part13=0 then let indxcols=12; 
elif part14=0 then let indxcols=13; 
elif part15=0 then let indxcols=14;
elif part16=0 then let indxcols=15; 
else  let indxcols=16; 
end if;
if indxcols = tablecols then return 'True';
else return 'False';
end if;
end procedure;

-- DBPROPVAL_IN_DISALLOWNULL if primary key, else DBPROPVAL_IN_IGNORENULL
create procedure gbasedbt.oledbidxnulls(isprimary varchar(5))
returning int;
if isprimary = 'True' then return 1;
else return 2;
end if;
end procedure;

-- DB_COLLATION_DESC if desc, else DB_COLLATION_ASC
create procedure gbasedbt.oledbidxcoll( part1 int, part2 int, part3 int, part4
int, part5 int, part6 int, part7 int, part8 int, part9 int, part10 int,
part11 int, part12 int, part13 int, part14 int, part15 int, part16 int, val
int )
returning int;
define part int;
let part = 0;
if abs(part1) = val then let part = part1;
elif abs(part2) = val then let part = part2;
elif abs(part3) = val then let part = part3;
elif abs(part4) = val then let part = part4;
elif abs(part5) = val then let part = part5;
elif abs(part6) = val then let part = part6;
elif abs(part7) = val then let part = part7;
elif abs(part8) = val then let part = part8;
elif abs(part9) = val then let part = part9;
elif abs(part10) = val then let part = part10;
elif abs(part11) = val then let part = part11;
elif abs(part12) = val then let part = part12;
elif abs(part13) = val then let part = part13;
elif abs(part14) = val then let part = part14;
elif abs(part15) = val then let part = part15;
elif abs(part16) = val then let part = part16;
else return null;
end if;
if part < 0  then return 2; else return 1; end if;
end procedure;

-- True if index is unique, else False
create procedure gbasedbt.oledbidxunique(code char(1))
returning varchar(5);
if code = 'U' then return 'True';
else return 'False';
end if;
end procedure;

-- make character convertible to boolean from number or OLE boolean string
create procedure gbasedbt.oledbmakebool( v varchar(5) )
returning char(1);
if v is null then return null;
elif v = '1' or v = 'True' or v = 't' then return 't';
elif v = '0' or v = 'False' or v = 'f' then return 'f';
else raise exception -1260;
end if;
end procedure;

-- concatenate 16 check texts
create procedure gbasedbt.oledbconcatcheck(
	v1 char(32), v2 char(32), v3 char(32), v4 char(32),
	v5 char(32), v6 char(32), v7 char(32), v8 char(32),
	v9 char(32), v10 char(32), v11 char(32), v12 char(32),
	v13 char(32), v14 char(32), v15 char(32), v16 char(32))
returning char(512);
define s char(512);
if v1 is null then let v1 = ''; end if;
if v2 is null then let v2 = ''; end if;
if v3 is null then let v3 = ''; end if;
if v4 is null then let v4 = ''; end if;
if v5 is null then let v5 = ''; end if;
if v6 is null then let v6 = ''; end if;
if v7 is null then let v7 = ''; end if;
if v8 is null then let v8 = ''; end if;
if v9 is null then let v9 = ''; end if;
if v10 is null then let v10 = ''; end if;
if v11 is null then let v11 = ''; end if;
if v12 is null then let v12 = ''; end if;
if v13 is null then let v13 = ''; end if;
if v14 is null then let v14 = ''; end if;
if v15 is null then let v15 = ''; end if;
if v16 is null then let v16 = ''; end if;
let s = v1 || v2 || v3 || v4 || v5 || v6 || v7 || v8 || v9
	|| v10 || v11 || v12 || v13 || v14 || v15 || v16;
return s;
end procedure;

-- concatenate 16 view texts
create procedure gbasedbt.oledbconcatview(
	v1 char(64), v2 char(64), v3 char(64), v4 char(64),
	v5 char(64), v6 char(64), v7 char(64), v8 char(64),
	v9 char(64), v10 char(64), v11 char(64), v12 char(64),
	v13 char(64), v14 char(64), v15 char(64), v16 char(64))
returning char(1024);
define s char(1024);
if v1 is null then let v1 = ''; end if;
if v2 is null then let v2 = ''; end if;
if v3 is null then let v3 = ''; end if;
if v4 is null then let v4 = ''; end if;
if v5 is null then let v5 = ''; end if;
if v6 is null then let v6 = ''; end if;
if v7 is null then let v7 = ''; end if;
if v8 is null then let v8 = ''; end if;
if v9 is null then let v9 = ''; end if;
if v10 is null then let v10 = ''; end if;
if v11 is null then let v11 = ''; end if;
if v12 is null then let v12 = ''; end if;
if v13 is null then let v13 = ''; end if;
if v14 is null then let v14 = ''; end if;
if v15 is null then let v15 = ''; end if;
if v16 is null then let v16 = ''; end if;
let s = v1 || v2 || v3 || v4 || v5 || v6 || v7 || v8 || v9
	|| v10 || v11 || v12 || v13 || v14 || v15 || v16;
return s;
end procedure;

-- concatenate 16 procedure texts
create procedure gbasedbt.oledbconcatproc(
	v1 char(256), v2 char(256), v3 char(256), v4 char(256),
	v5 char(256), v6 char(256), v7 char(256), v8 char(256),
	v9 char(256), v10 char(256), v11 char(256), v12 char(256),
	v13 char(256), v14 char(256), v15 char(256), v16 char(256))
returning char(4096);
define s char(4096);
if v1 is null then let v1 = ''; end if;
if v2 is null then let v2 = ''; end if;
if v3 is null then let v3 = ''; end if;
if v4 is null then let v4 = ''; end if;
if v5 is null then let v5 = ''; end if;
if v6 is null then let v6 = ''; end if;
if v7 is null then let v7 = ''; end if;
if v8 is null then let v8 = ''; end if;
if v9 is null then let v9 = ''; end if;
if v10 is null then let v10 = ''; end if;
if v11 is null then let v11 = ''; end if;
if v12 is null then let v12 = ''; end if;
if v13 is null then let v13 = ''; end if;
if v14 is null then let v14 = ''; end if;
if v15 is null then let v15 = ''; end if;
if v16 is null then let v16 = ''; end if;
let s = v1 || v2 || v3 || v4 || v5 || v6 || v7 || v8 || v9
	|| v10 || v11 || v12 || v13 || v14 || v15 || v16;
return s;
end procedure;



grant execute on oledbMatchQualInfo to public; 
grant execute on oledbcoldef to public; 
grant execute on oledbcolflags to public; 
grant execute on oledbcollen to public; 
grant execute on oledbcolnullable to public; 
grant execute on oledbcolprec to public; 
grant execute on oledbcolscale to public; 
grant execute on oledbcolscale1 to public;
grant execute on oledbtype1 to public; 
grant execute on oledbconcatcheck to public; 
grant execute on oledbconcatproc to public; 
grant execute on oledbconcatview to public; 
grant execute on oledbconstrrule to public; 
grant execute on oledbhaswcheck to public; 
grant execute on oledbidxclustered to public; 
grant execute on oledbidxcoll to public; 
grant execute on oledbidxintegr to public; 
grant execute on oledbidxnulls to public; 
grant execute on oledbidxunique to public; 
grant execute on oledbisnotnull to public; 
grant execute on oledbissystab to public; 
grant execute on oledbkeypart to public; 
grant execute on oledbmakebool to public; 
grant execute on oledbmakexstid to public; 
grant execute on oledbmakexstid1 to public; 
grant execute on oledbnvl to public; 
grant execute on oledbwithcheck to public;

-- Commit the work if everthing goes fine
commit;
