analytics/informix/etc/xpg4_is.sql

{ ************************************************************************* }
{                                                                           }
{  Licensed Materials - Property of IBM and/or HCL                                     }
{                                                                           }
{  IBM Informix Dynamic Server                                              }
{  (c) Copyright IBM Corporation 1996, 2008 All rights reserved.            }
{  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.             }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{   Title:      xpg4_is.sql                                                 }
{                                                                           }
{ ************************************************************************* }

{ THE FOLLOWING COLTYPE NUMBERS are referenced in the store procedures:

     coltype	SQLtype		     coltype    SQLtype
     -------	--------	     -------    --------
	0	CHAR			 8	MONEY
	1	SMALLINT		10	DATETIME
	2	INTEGER			11	BYTE
	3	FLOAT			12	TEXT
	4	SMALLFLOAT		13	VARCHAR
	5	DECIMAL			14	INTERVAL
	6	SERIAL			15	SQLNCHAR
	7 	DATE			16	SQLVNCHAR
					17	SQLINT8
					18	SQLSERIAL8
					19	SQLSET
					20	SQLMULTISET
					21	SQLLIST         
					22	SQLROW
					23	SQLCOLLECTION
					24	SQLROWREF
					40	SQLUDTVAR
					41	SQLUDTFIXED
					42	SQLREFSER8
					52	SQLBIGINT
					53	SQLBIGSERIAL
}

{ INFORMATION SCHEMA FOR TRI-STAR WITH X/OPEN XPG4 COMPLIANCE }
	
{ Utility Procedures }

{----------------------------------------------------------------------}
{------creating supporting procedures used by views--------------------}
{----------------------------------------------------------------------}
create procedure 'informix'.get_null()
returning char;
	return null;
end procedure
document 
	'Procedure get_null() returns a null value when it is called',
	'Synopsis: get_null() takes no arguments';
	 
{----------------------------------------------------------------------}

create procedure 'informix'.ansitabtype(inftype char(1))
returning char(18);
	if (inftype = 'T') then
		return 'BASE TABLE';
	elif (inftype = 'V') then
		return 'VIEW';
	else
		return 'INTERNAL ERROR';
	end if;
end procedure
document
	'Returns table type with ansi conventions',
	'Synopsis: ansitabtype(char(1)) returns char(18)';

{----------------------------------------------------------------------}

create procedure 'informix'.ansinullable(coltype smallint)
returning char(3);
	if (coltype >= 256) then
	    return 'NO';
	else
	    return 'YES';
	end if;
end procedure
document
	'returns if the column is nullable or not',
	'Synopsis: ansinullable(smallint) returns char(3)';
	
{----------------------------------------------------------------------}

create procedure 'informix'.ansicoltype(coltype smallint, collength smallint)
returning char(18);

	define largest, smallest int;

	if (coltype >= 256) then
	    let coltype = coltype - 256;
	end if;

	if (coltype = 0) then
	    return 'CHARACTER';
	elif (coltype = 1) then
	    return 'SMALLINT';
	elif (coltype = 2) then
	    return 'INTEGER';
	elif (coltype = 3) then
	    return 'FLOAT';
	elif (coltype = 4) then
	    return 'SMALLFLOAT';
	elif (coltype = 5) then
	    return 'DECIMAL';
  	elif (coltype = 6) then
  	    return 'SERIAL';
	elif (coltype = 7) then
	    return 'DATE';
  	elif (coltype = 8) then
  	    return 'MONEY';
	elif (coltype = 10) then
	    return 'DATETIME';
  	elif (coltype = 11) then
  	    return 'BYTE';
  	elif (coltype = 12) then
  	    return 'TEXT';
	elif (coltype = 13) then
	    return 'CHARACTER VARYING';
  	elif (coltype = 14) then
  	    return 'INTERVAL';
	elif (coltype = 15) then
  	    return 'NCHAR';
	elif (coltype = 16) then
  	    return 'NVCHAR';
	elif (coltype = 17) then
  	    return 'INTEGER8';
	elif (coltype = 18) then
  	    return 'SERIAL8';
	elif (coltype = 19) then
  	    return 'SET';
	elif (coltype = 20) then
  	    return 'MULTISET';
	elif (coltype = 21) then
  	    return 'LIST';
	elif (coltype = 22) then
  	    return 'ROW';
	elif (coltype = 23) then
  	    return 'COLLECTION';
	elif (coltype = 24) then
  	    return 'ROWREF';
	elif (coltype = 40) then
  	    return 'OPAQUE VARIABLE';
	elif (coltype = 41) then
  	    return 'OPAQUE FIXED';
	elif (coltype = 42) then
  	    return 'REFSERIAL8';
	elif (coltype = 52) then
  	    return 'BIGINT';
	elif (coltype = 53) then
  	    return 'BIGSERIAL';
	else
	    return 'UNKNOWN DATA TYPE';
	end if;
end procedure
document
	'returns the column data type',
	'Synopsis: ansicoltype(smallint returns char(18))';

{----------------------------------------------------------------------}

create procedure 'informix'.ansimaxlen(coltype smallint, collength smallint)
returning int;

	if (coltype >= 256) then
	    let coltype = coltype - 256;
	end if;

	if (coltype = 0) then
	    return collength;
	elif (coltype = 13) or (coltype = 16) then
	    return (collength - (trunc(collength / 256))*256);
	else
	    return NULL;
	end if;
end procedure
document
	'returns the maximum length of character oriented column',
	'Synopsis: ansimaxlen(smallint, smallint) returns int';

{----------------------------------------------------------------------}

create procedure 'informix'.ansinumprec(coltype smallint, collength smallint)
returning int;

	{ FLOAT and SMALLFLOAT precisions are in bits }

	if (coltype >= 256) then
	    let coltype = coltype - 256;
	end if;

	if (coltype = 1) then				-- smallint
	    return 5;
	elif (coltype = 2) or (coltype = 6) then	-- int
	    return 10;
	elif (coltype = 3) then				-- float
	    return 64;
	elif (coltype = 4) then				-- smallfloat
	    return 32;
	elif (coltype = 5) or (coltype = 8) then	-- decimal
	    return (trunc(collength / 256));
	elif (coltype = 17) or (coltype = 18) then	-- int8
	    return 19;
	elif (coltype = 52) or (coltype = 53) then	-- bigint
	    return 19;
	else
	    return NULL;
	end if;
end procedure
document
	'returns the precision of a numeric column',
	'Synopsis: ansinumprec(smallint, smallint) returns int';

{----------------------------------------------------------------------}

create procedure 'informix'.ansinumprecradix( coltype smallint)
returning int;

	if (coltype >= 256) then
	    let coltype = coltype - 256;
	end if;

	if (coltype = 1) or (coltype = 2) or 
	   (coltype = 5) or (coltype = 6) or
	   (coltype = 8) or (coltype = 17) or 
	   (coltype = 18) or (coltype = 52) or 
	   (coltype = 53) then
	    return 10;
	elif (coltype = 3) or (coltype = 4) then
	    return 2;
	else
	    return NULL;
	end if;
end procedure
document
	'returns the precision radix of a numeric column',
	'Synopsis: ansinumprecradix(smallint) returns int';

{----------------------------------------------------------------------}

create procedure 'informix'.ansinumscale(coltype smallint, collength smallint)
returning int;

	if (coltype >= 256) then
	    let coltype = coltype - 256;
	end if;

	if (coltype = 1) or (coltype = 2) or 
	   (coltype = 6) then
	    return 0;
	elif (coltype = 5) or (coltype = 8) then
	    return (collength - ((trunc(collength / 256))*256));
	else
	    return NULL;
	end if;
end procedure
document
	'returns the scale of a numeric column',
	'Synopsis: ansinumscale(smallint, smallint) returns int';

{----------------------------------------------------------------------}

create procedure 'informix'.ansidatprec(coltype smallint, collength smallint)
returning int;

	{ if the column is nullable then coltype = coltype+256 }

	if (coltype = 7 or coltype = 263) then
	    return 0;
	elif (coltype = 10 or coltype = 266) then
	    let collength = collength - 16*trunc(collength/16) - 10;
	    if (collength > 0) then
		return collength;
	    else
		return 0;
	    end if;
	else
	    return NULL;
	end if;
end procedure
document
	'returns the date precision for a datetime column',
	'Synopsis: ansidatprec(smallint, smallint) returns int';

{----------------------------------------------------------------------}

create procedure 'informix'.se_or_ol()
returning char(2);
define num int;

   { methodology : 
      TO determine whether a database is SE or OL :
      If 'informix'.sysblobs is not found, then it's SE, else it's OL, 
   }
    on exception in (-206)	-- Table not found
	return 'SE';
    end exception

    select max(tabid) into num from 'informix'.sysblobs; -- max to ensure 1 row
    return 'OL';

end procedure
document
	'returns the engine type of the server',
	'Synopsis: se_or_ol() returns char(2)';

{----------------------------------------------------------------------}

create procedure 'informix'.is_log_ansi()
returning char, char;			-- log_flag, ansi_flag
define errornum int;

   { methodology : 
      To determine whether logging or not (obviously non-ANSI database)
      issue begin transaction: 
      if it succeeds or returns -535, there's logging,
      if it returns -256, no logging,

      TO determine whether a database is ANSI or not :
      create a systables(we know it already exists), with different username.
      If it's allowed, it's ANSI.  Drop the table then return.
      If it's not allowed, may be the table really does exist!, if so, it's
      ansi, if not, it's not ANSI.
   }
    on exception in (-256)	-- Transaction not available
	return 'N', 'N';
    end exception

    on exception in (-206)	-- Table not found
	return 'Y', 'N';
    end exception

    on exception in (-535, -310) -- Already in Transaction, Table already exist
	set errornum			
    end exception with resume

    let errornum = 0;			
    begin work;
    if (errornum == 0) then		
        rollback;			
    end if;
    -- at this point, we know database is created with logging 

    create table 'DuMmYuSr'.systables(c1 char);	-- create a dummy table
    if (errornum = -310) then
	let errornum = (select count(*) from 'DuMmYuSr'.systables);
    else
	drop table 'DuMmYuSr'.systables;
    end if;
    return 'Y', 'Y';

end procedure
document
	'returns the logging and ansi DB information',
	'Synopsis: is_log_ansi() returns char, char';

{----------------------------------------------------------------------}

create dba procedure 'informix'.insert_sql_lang()

    { only DSA online has conformance information }

    if (se_or_ol() = 'OL') then
	insert into xSQL_LANGUAGES values('X/OPEN SQL','1992','XPG4',
				'YES', 'ONLINE DSA', 'EMBEDDED', 'C');
	insert into xSQL_LANGUAGES values('X/OPEN SQL','1992','XPG4',
				'YES', 'ONLINE DSA', 'EMBEDDED', 'COBOL');
	insert into xSQL_LANGUAGES values('ANSI X3.135','1992','ENTRY',
				'YES', 'ONLINE DSA', 'EMBEDDED', 'C');
	insert into xSQL_LANGUAGES values('ANSI X3.135','1992','ENTRY',
				'YES', 'ONLINE DSA', 'EMBEDDED', 'COBOL');
    end if;
end procedure
document
	'populate xSQL_LANGUAGES table for OL only, nothing for SE',
	'Synopsis: insert_sql_lang() returns nothing';

{----------------------------------------------------------------------}

create dba procedure 'informix'.insert_srv_info()
define log_flag, ansi_flag char;
define engine_type char(2);

    let engine_type = se_or_ol(); 
    if (engine_type = 'SE') then	
	insert into xSERVER_INFO values('ENGINE TYPE','Standard Engine');
	insert into xSERVER_INFO values('ROW_LENGTH', '32511');
    else
	insert into xSERVER_INFO values('ENGINE TYPE','Online DSA');
	insert into xSERVER_INFO values('ROW_LENGTH', '32767');
    end if;

    insert into xSERVER_INFO values('IDENTIFIER_LENGTH', '128');
    insert into xSERVER_INFO values('USERID_LENGTH','32');
    insert into xSERVER_INFO values('COLLATION_SEQ','ISO 8859-1');

    { TXN_ISOLATION depends on whether there's logging, and/or ansi database }
    let log_flag, ansi_flag = is_log_ansi(); 

    if (engine_type = 'SE') or (log_flag = 'N') then
	insert into xSERVER_INFO values('TXN_ISOLATION', 'READ UNCOMMITTED'); 
    elif (log_flag = 'Y') and (ansi_flag = 'N') then
	insert into xSERVER_INFO values('TXN_ISOLATION', 'READ COMMITTED'); 
    elif (ansi_flag = 'Y') then
	insert into xSERVER_INFO values('TXN_ISOLATION', 'SERIALIZABLE'); 
    end if;

end procedure
document
	'populate xSERVER_INFO table',
	'Synopsis: insert_srv_info() returns nothing';

{----------------------------------------------------------------------}
{----------create views------------------------------------------------}
{----------------------------------------------------------------------}

create view 'informix'.TABLES(
		TABLE_SCHEMA, 					-- 1
		TABLE_NAME, 					-- 2
		TABLE_TYPE, 					-- 3
		REMARKS)					-- 4
		as
	select 	trim(ST.owner), 				-- 1
		trim(ST.tabname),				-- 2
		trim('informix'.ansitabtype(tabtype)),		-- 3
		'informix'.get_null()				-- 4
	from	'informix'.systables ST
	where	ST.tabtype in ('T', 'V') and
		(
			ST.owner = USER
			or 
			USER = 'informix'
			or
			exists 
				( -- DBA user
				select * from 'informix'.sysusers
				where username = USER and usertype = 'D'
				)
			or
			exists 
				(
				select * from 'informix'.systabauth STA 
				where 	ST.tabid = STA.tabid and 
					STA.grantee in (USER, 'public')
				)
		);

{----------------------------------------------------------------------}

create view 'informix'.COLUMNS(
		TABLE_SCHEMA, 						  --  1
		TABLE_NAME,						  --  2
		COLUMN_NAME, 						  --  3
		ORDINAL_POSITION,					  --  4
		DATA_TYPE, 						  --  5
		CHAR_MAX_LENGTH, 					  --  6
		NUMERIC_PRECISION, 					  --  7
		NUMERIC_PREC_RADIX, 					  --  8
		NUMERIC_SCALE, 						  --  9
		DATETIME_PRECISION, 					  -- 10
		IS_NULLABLE, 						  -- 11
		REMARKS)						  -- 12
		as
	select	trim(ST.owner),						  --  1
		trim(ST.tabname),					  --  2
		trim(SC.colname), 					  --  3
		SC.colno,						  --  4
		trim('informix'.ansicoltype(SC.coltype, SC.collength)),	  --  5
		'informix'.ansimaxlen(SC.coltype, SC.collength),  	  --  6
		'informix'.ansinumprec(SC.coltype, SC.collength), 	  --  7
		'informix'.ansinumprecradix(SC.coltype),		  --  8
		'informix'.ansinumscale(SC.coltype, SC.collength),	  --  9
		'informix'.ansidatprec(SC.coltype, SC.collength), 	  -- 10
		trim('informix'.ansinullable(SC.coltype)), 	  	  -- 11
		'informix'.get_null()			  	 	  -- 12
	from	'informix'.systables ST,
		'informix'.syscolumns SC
	where		
		ST.tabtype in ('T', 'V') and
		ST.tabid = SC.tabid and 
		(
			ST.owner = USER
			or
			USER = 'informix'
			or
			exists 
				( -- DBA user
				select * from 'informix'.sysusers
				where username = USER and usertype = 'D'
				)
			or
			exists 
				(  -- entire table is granted
				select * from 'informix'.systabauth STA 
				where 	ST.tabid = STA.tabid and 
					STA.grantee in (USER, 'public') and
					STA.tabauth <> '--*-----'
				)
			or	
			exists 
				(  -- one column is granted
				select * from 'informix'.syscolauth SCA
				where 	ST.tabid = SCA.tabid and 
					SCA.colno = SC.colno and
					SCA.grantee in (USER, 'public')
				)
		);

create table 'informix'.xSQL_LANGUAGES(
			SOURCE 			char(254) NOT NULL,
			SOURCE_YEAR 		char(254),
			CONFORMANCE 		char(254),
			INTEGRITY 		char(254),
			IMPLEMENTATION 		char(254),
			BINDING_STYLE 		char(254),
			PROGRAMMING_LANG	char(254));

execute procedure 'informix'.insert_sql_lang();

create view 'informix'.SQL_LANGUAGES(
			SOURCE, 
			SOURCE_YEAR,
			CONFORMANCE,
			INTEGRITY,
			IMPLEMENTATION,
			BINDING_STYLE,
			PROGRAMMING_LANG) 
		as select
			trim(SOURCE), 
			trim(SOURCE_YEAR),
			trim(CONFORMANCE),
			trim(INTEGRITY),
			trim(IMPLEMENTATION),
			trim(BINDING_STYLE),
			trim(PROGRAMMING_LANG) 
		from 'informix'.xSQL_LANGUAGES;

create table 'informix'.xSERVER_INFO(
			SERVER_ATTRIBUTE	char(254) NOT NULL,
			ATTRIBUTE_VALUE		char(254));

execute procedure 'informix'.insert_srv_info();

create view 'informix'.SERVER_INFO(
			SERVER_ATTRIBUTE,
			ATTRIBUTE_VALUE)
		as select
			trim(SERVER_ATTRIBUTE),
			trim(ATTRIBUTE_VALUE)
		from 'informix'.xSERVER_INFO;

{----------------------------------------------------------------------}
{--------- setting proper privileges ----------------------------------}
{----------------------------------------------------------------------}

grant execute on 'informix'.get_null to public 
	with grant option as 'informix'; 
grant execute on 'informix'.ansitabtype to public 
	with grant option as 'informix';
grant execute on 'informix'.ansinullable to public 
	with grant option as 'informix';
grant execute on 'informix'.ansicoltype to public 
	with grant option as 'informix';
grant execute on 'informix'.ansimaxlen to public 
	with grant option as 'informix';
grant execute on 'informix'.ansinumprec to public 
	with grant option as 'informix';
grant execute on 'informix'.ansinumprecradix to public 
	with grant option as 'informix';
grant execute on 'informix'.ansinumscale to public 
	with grant option as 'informix';
grant execute on 'informix'.ansidatprec to public 
	with grant option as 'informix';
revoke all on 'informix'.TABLES from public;
grant select on 'informix'.TABLES to public 
	with grant option as 'informix';
revoke all on 'informix'.COLUMNS from public; 
grant select on 'informix'.COLUMNS to public 
	with grant option as 'informix';
revoke all on 'informix'.SQL_LANGUAGES from public;
grant select on 'informix'.SQL_LANGUAGES to public 
	with grant option as 'informix';
revoke all on 'informix'.SERVER_INFO from public; 
grant select on 'informix'.SERVER_INFO to public 
	with grant option as 'informix';