Geek asks and answers

How to create a shadow table in Informix

I was working on an audit module that requires me to create a huge list of shadow tables from the source table. I was thinking of writing a stored procedure that could generate a shadow table from another table. This table can be any table, it can be table_A, which consists of three fields of any data type or table_B, which consists of 10 fields of any data type and which can be based on the passage of the parameter in the storage procedure.

I know that I can get the list of fields available for a specific table_A from syscolumns and systables as follows:

select * from syscolumns where tabid = (select tabid from systables where tabname='table_A')

A list of fields returned from this operator appears, say field_A, after which I rename this field_A to create 2 new fields, which are pre_field_A and post_field_A, and then use these two new fields to create the shadow table shadow_table_A. Apply this concept to the rest to the fields. No need to worry about the data type of the field, because this information already exists in syscolumns, and I can just reset it to the shadow table.

I'm currently stuck on how to maintain a list of values ​​returned from the above statement, because usually any table will consist of several fields. Can this be done using an array? or any alternative solution to create a shadow table for audit purposes?

+3
source share
2

, :

SELECT *
  FROM "informix".systables AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabname = 'table_a';

, , table_A , , DELIMIDENT, , . , ; .

, . . , , , , , DB-Schema (dbschema), . SYSTEM, , , - . , . - .

IBM Informix Dynamic Server 11.70, CREATE {audit} TABLE, . , , FOREACH , , . . / . , tabname - , , c_colno, c_colname c_typename - (as cts, short 'create table statement' pad):

LET cts = 'CREATE TABLE ' || tabname || '(';
LET pad = '';
FOREACH SELECT c.colno, c.colname, type_name(c.coltype, c.collength)
          INTO c_colno, c_colname, c_typename
          FROM "informix".systables AS t
          JOIN "informix".syscolumns AS c
            ON t.tabid = c.tabid
         WHERE t.tabname = tabname
         ORDER BY c.colno
    LET cts = cts || pad || 'pre_'  || c_colname || ' ' || c_coltype;
    LET cts = cts || ',' || 'post_' || c_colname || ' ' || c_coltype;
    LET pad = ',';
END FOREACH;
LET cts = cts || ');';

, NOT NULL , , , .

+3

JonathanLeffler answer type_name:

    CREATE PROCEDURE type_name(coltype INTEGER, colsize INTEGER)
            RETURNING VARCHAR(128);
            DEFINE toRet                    VARCHAR(128);

            DEFINE size_5                   VARCHAR(5);
            DEFINE decimal_p, decimal_s     INTEGER;
            DEFINE decimal_t                VARCHAR(16);
            DEFINE varchar_m, varchar_n     INTEGER;
            DEFINE varchar_t                VARCHAR(16);

            LET size_5 = '(' || TRIM(CAST(colsize AS CHAR(5))) || ')'; 
            -- Precision
            LET decimal_p = TRUNC(colsize/256);
            -- Scale
            LET decimal_s = colsize - 256 * decimal_p;
            -- Decimal total
            LET decimal_t = '(' || TRIM(CAST(decimal_p as VARCHAR(8))) || ',' || TRIM(CAST(decimal_s as VARCHAR(8))) || ')';

            -- VARCHAR(M,N)
            LET varchar_n = decimal_p;
            LET varchar_m = decimal_s;
            LET varchar_t = '(' || TRIM(CAST(varchar_m as VARCHAR(8))) || ',' || TRIM(CAST(varchar_n as VARCHAR(8))) || ')';

            SELECT
                CASE coltype
                    WHEN 0   THEN 'char' || size_5 
                    WHEN 1   THEN 'smallint'
                    WHEN 2   THEN 'integer'
                    WHEN 3   THEN 'float'
                    WHEN 4   THEN 'smallfloat'
                    WHEN 5   THEN 'decimal' || decimal_t
                    WHEN 6   THEN 'serial'
                    WHEN 7   THEN 'date'
                    WHEN 8   THEN 'money' || decimal_t
                    WHEN 9   THEN 'null'
                    WHEN 10  THEN 'DATETIME YEAR TO FRACTION(3)'
                    WHEN 11  THEN 'byte'
                    WHEN 12  THEN 'TEXT'
                    WHEN 13  THEN 'VARCHAR' || varchar_t
                    WHEN 14  THEN 'INTERVAL'
                    WHEN 15  THEN 'NCHAR' || size_5
                    WHEN 16  THEN 'NVARCHAR' || varchar_t
                    WHEN 17  THEN 'INT8'
                    WHEN 18  THEN 'SERIAL8'
                    WHEN 19  THEN 'SET'
                    WHEN 20  THEN 'MULTISET'
                    WHEN 21  THEN 'LIST'
                    WHEN 22  THEN 'ROW'
                    WHEN 23  THEN 'COLLECTION'
                    WHEN 24  THEN 'ROWDEF'
                    WHEN 40  THEN 'LVARCHAR'  || size_5 
                    WHEN 256 THEN 'CHAR' || size_5 || ' NOT NULL'
                    WHEN 257 THEN 'SMALLINT NOT NULL'
                    WHEN 258 THEN 'INTEGER NOT NULL'
                    WHEN 259 THEN 'FLOAT NOT NULL'
                    WHEN 260 THEN 'SMALLFLOAT NOT NULL'
                    WHEN 261 THEN 'DECIMAL' || decimal_t || ' NOT NULL'
                    WHEN 262 THEN 'SERIAL NOT NULL'
                    WHEN 263 THEN 'DATE NOT NULL'
                    WHEN 264 THEN 'MONEY' || decimal_t || ' NOT NULL'
                    WHEN 265 THEN 'null NOT NULL'
                    WHEN 266 THEN 'DATETIME YEAR TO FRACTION(3) NOT NULL'
                    WHEN 267 THEN 'BYTE NOT NULL'
                    WHEN 268 THEN 'TEXT NOT NULL'
                    WHEN 269 THEN 'VARCHAR' || varchar_t || ' NOT NULL'
                    WHEN 270 THEN 'INTERVAL NOT NULL'
                    WHEN 271 THEN 'nchar(' || size_5 || ') NOT NULL'
                    WHEN 272 THEN 'nvarchar' || varchar_t || ' NOT NULL'
                    WHEN 273 THEN 'int8 NOT NULL'
                    WHEN 274 THEN 'serial8 NOT NULL'
                    WHEN 275 THEN 'set NOT NULL'
                    WHEN 276 THEN 'multiset NOT NULL'
                    WHEN 277 THEN 'list NOT NULL'
                    WHEN 278 THEN 'row NOT NULL'
                    WHEN 279 THEN 'collection NOT NULL'
                    WHEN 280 THEN 'rowdef NOT NULL'
                    WHEN 296 THEN 'LVARCHAR'  || varchar_t || ' NOT NULL'
                    ELSE 'ERROR'
                END datatype
            INTO toRet
            FROM systables
            WHERE tabid = 1;
            IF toRet = 'ERROR' THEN
               RAISE EXCEPTION -746, 0, 'Unknow datatype ' || coltype;
            END IF

            RETURN toRet;
    END PROCEDURE;

.

+2

More articles:


All Articles