-- This is a script that creates Informix CMO repository;
-- Copyright (c) 1999-2012 Advanced Global Systems, Ltd.;
-- All rights reserved. This module contains proprietary information of;
-- Advanced Global Systems and should be treated as confidential.;

CREATE TABLE T_SERVERS(
 F_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY ,
 F_TIME TIMESTAMP,
 F_HOST VARCHAR(128),
 F_SERVER VARCHAR(128),
 F_DBSNAME VARCHAR(128),
 F_ROOTDBS VARCHAR(128),
 F_VERSION VARCHAR(15),
 F_TYPE SMALLINT,
 F_DESCR LONG VARCHAR,
 F_LABEL VARCHAR(128),
 F_USER VARCHAR(128),
 F_DIGEST VARCHAR(255),
 F_SOURCE SMALLINT,
 F_CFG_DIGEST VARCHAR(255)
);

CREATE TABLE T_EXPLTREE(
 F_HOST VARCHAR(128),
 F_SERVER VARCHAR(128),
 F_DBSNAME VARCHAR(128)

);

CREATE TABLE T_CHECKS (
    F_ID INT NOT NULL,
    CONSTRID INT,
    TYPE CHAR(1),
    SEQNO SMALLINT,
    CHECKTEXT CHAR(32)
);

CREATE TABLE T_COLUMNS (
    F_ID INT NOT NULL,
    COLNAME VARCHAR(128),
    TABID INT,
    COLNO SMALLINT,
    COLTYPE SMALLINT,
    COLLENGTH SMALLINT,
    COLMIN INT,
    COLMAX INT,
    EXTENDED_ID INT,
    C_TYPE CHAR(1),
    C_DEFAULT LONG VARCHAR
    
);

CREATE TABLE T_CONSTRAINTS (
    F_ID INT NOT NULL,
    CONSTRID INT,
    CONSTRNAME VARCHAR(128),
    OWNER CHAR(32),
    TABID INT,
    CONSTRTYPE CHAR(1),
    IDXNAME VARCHAR(128)

);


CREATE TABLE T_FRAGMENTS (
    F_ID INT NOT NULL,
    FRAGTYPE CHAR(1),
    TABID INT,
    INDEXNAME VARCHAR(128),
    COLNO INT,
    PARTN INT,
    STRATEGY CHAR(1),
    LOCATION CHAR(1),
    SERVERNAME VARCHAR(128),
    EVALPOS INT,
    EXPRTEXT LONG VARCHAR ,
    FLAGS INT,
    DBSPACE VARCHAR(128),
    LEVELS SMALLINT,
    NPUSED INT,
    NROWS INT,
    CLUST INT,
    PARTITION VARCHAR(128)
);

CREATE TABLE T_INDEXES (
	F_ID INT NOT NULL,
    IDXNAME VARCHAR(128),
    OWNER VARCHAR(32),
    TABID INT,
    IDXTYPE CHAR(1),
    CLUSTERED CHAR(1),
    PART1 SMALLINT,
    PART2 SMALLINT,
    PART3 SMALLINT,
    PART4 SMALLINT,
    PART5 SMALLINT,
    PART6 SMALLINT,
    PART7 SMALLINT,
    PART8 SMALLINT,
    PART9 SMALLINT,
    PART10 SMALLINT,
    PART11 SMALLINT,
    PART12 SMALLINT,
    PART13 SMALLINT,
    PART14 SMALLINT,
    PART15 SMALLINT,
    PART16 SMALLINT,
    LEVELS SMALLINT,
    LEAVES INT,
    NUNIQUE INT,
    CLUST INT,
    INDEXKEYS VARCHAR(255),
    AM_NAME VARCHAR(128)
   		
);

CREATE TABLE T_PROCBODY (
    F_ID INT NOT NULL,
    PROCID INT,
    DATAKEY CHAR(1),
    SEQNO INT,
    DATA VARCHAR(256),
    DATALENGTH INT
);

CREATE TABLE T_PROCEDURES (
    F_ID INT NOT NULL,
    PROCNAME VARCHAR(128),
    OWNER CHAR(32),
    PROCID INT,
    MODE CHAR(1),
    NUMARGS INT,
    ISPROC CHAR(1),
    SPECIFICNAME VARCHAR(128),
    EXTERNALNAME VARCHAR(255),
    PARAMSTYLE CHAR(1),
    LANGID INT,
    PARAMTYPES VARCHAR(32000),
    CLASS CHAR(18)
 
);

CREATE TABLE T_TABLES (
    F_ID INT NOT NULL,
    TABNAME VARCHAR(128),
    OWNER CHAR(32),
    PARTNUM INT,
    TABID INT,
    ROWSIZE SMALLINT,
    NCOLS SMALLINT,
    NINDEXES SMALLINT,
    NROWS INT,
    CREATED DATE,
    VERSION INT,
    TABTYPE CHAR(1),
    LOCKLEVEL CHAR(1),
    NPUSED INT,
    FEXTSIZE INT,
    NEXTSIZE INT,
    FLAGS SMALLINT,
    SITE VARCHAR(128),
    DBNAME VARCHAR(128),
    TYPE_XID INT,
    AM_ID INT,
   DBSPACE VARCHAR(255)
);



CREATE TABLE T_BLOBS (
    F_ID INT NOT NULL,
    SPACENAME VARCHAR(128),
    TYPE CHAR(1),
    TABID INT,
    COLNO SMALLINT
);


CREATE TABLE T_XTDTYPES (
    F_ID INT NOT NULL,
    EXTENDED_ID INT,
    DOMAIN CHAR(1),
    MODE CHAR(1),
    OWNER CHAR(32),
    NAME VARCHAR(128),
    TYPE SMALLINT,
    SOURCE INT,
    MAXLEN INT,
    LENGTH INT,
    BYVALUE CHAR(1),
    CANNOTHASH CHAR(1),
    ALIGN SMALLINT,
    LOCATOR INT,
    F_ROWID INT
);

CREATE TABLE T_XTDDESC (
    F_ID INT NOT NULL,
    EXTENDED_ID INT,
    SEQNO SMALLINT,
    DESCRIPTION LONG VARCHAR
);

CREATE TABLE T_ATTRTYPES (
    F_ID INT NOT NULL,
    EXTENDED_ID INT,
    SEQNO SMALLINT,
    LEVELNO SMALLINT,
    PARENT_NO SMALLINT,
    FIELDNAME VARCHAR(128),
    FIELDNO SMALLINT,
    TYPE SMALLINT,
    LENGTH SMALLINT,
    XTD_TYPE_ID INT
);


CREATE TABLE T_OPCLASSES (
    F_ID INT NOT NULL,
    OPCLASSNAME VARCHAR(128),
    OWNER CHAR(32),
    AMID INT,
    OPCLASSID INT,
    OPS LONG VARCHAR,
    SUPPORT LONG VARCHAR
);


CREATE TABLE T_REFERENCES (
    F_ID INT NOT NULL,
    CONSTRID INT,
    C_PRIMARY INT,
    PTABID INT,
    UPDRULE CHAR(1),
    DELRULE CHAR(1),
    MATCHTYPE CHAR(1),
    PENDANT CHAR(1)
);


CREATE TABLE T_COLATTRIBS (
    F_ID INT NOT NULL,
    TABID INT,
    COLNO SMALLINT,
    EXTENTSIZE INT,
    FLAGS INT,
    FLAGS1 INT,
    SBSPACE VARCHAR(128)
);


CREATE TABLE T_VIEWS (
  F_ID INT NOT NULL,
    TABID INT,
    SEQNO SMALLINT,
    VIEWTEXT VARCHAR(256),
    DATALENGTH INT
);


CREATE TABLE T_TRIGGERS (
	 F_ID INT NOT NULL,
    TRIGID INT,
    TRIGNAME VARCHAR(128),
    OWNER CHAR(32),
    TABID INT,
    EVENT CHAR(1),
    OLD VARCHAR(128),
    NEW VARCHAR(128),
    MODE CHAR(1)
 
);



CREATE TABLE T_TRIGBODY (
	 F_ID INT NOT NULL,
    TRIGID INT,
    DATAKEY CHAR(1),
    SEQNO INT,
    DATA VARCHAR(256),
    DATALENGTH INT
);

CREATE TABLE T_SYNONYMS (
    F_ID INT NOT NULL,
    OWNER CHAR(32),
    SYNNAME VARCHAR(128),
    CREATED DATE,
    TABID INT
);

CREATE TABLE T_SYNTABLE (
   F_ID INT NOT NULL,
    TABID INT,
    SERVERNAME VARCHAR(128),
    DBNAME VARCHAR(128),
    OWNER CHAR(32),
    TABNAME VARCHAR(128),
    BTABID INT
);

CREATE TABLE T_ROLEAUTH (
 F_ID INT NOT NULL,
    ROLENAME CHAR(32),
    GRANTEE CHAR(32),
    IS_GRANTABLE CHAR(1),
    F_ROWID INT	
);

CREATE TABLE T_USERS (
  F_ID INT NOT NULL,
    USERNAME CHAR(32),
    USERTYPE CHAR(1),
    PRIORITY SMALLINT,
    PASSWORD CHAR(16)
);

CREATE TABLE T_TABAUTH (
 F_ID INT NOT NULL,
    GRANTOR CHAR(32),
    GRANTEE CHAR(32),
    TABID INT,
    TABAUTH CHAR(9),
    F_ROWID INT
);

CREATE TABLE T_PROCAUTH (
 F_ID INT NOT NULL,
    GRANTOR CHAR(32),
    GRANTEE CHAR(32),
    PROCID INT,
    PROCAUTH CHAR(1),
    F_ROWID INT

);


CREATE TABLE T_FRAGAUTH (
 F_ID INT NOT NULL,
    GRANTOR CHAR(32),
    GRANTEE CHAR(32),
    TABID INT,
    FRAGMENT VARCHAR(128),
    FRAGAUTH CHAR(6),
    F_ROWID INT
);


CREATE TABLE T_COLAUTH (
    F_ID INT NOT NULL,
    GRANTOR CHAR(32),
    GRANTEE CHAR(32),
    TABID INT,
    COLNO SMALLINT,
    COLAUTH CHAR(3),
    F_ROWID INT
);

CREATE TABLE T_INHERITS (
    F_ID INT NOT NULL,
    CHILD INT,
    PARENT INT,
    CLASS CHAR(1)
);


CREATE TABLE T_SEQUENCES (
    F_ID INT NOT NULL,
    SEQID INT,
    TABID INT,
    START_VAL BIGINT,
    INC_VAL BIGINT,
    MIN_VAL BIGINT,
    MAX_VAL BIGINT,
    CYCLE CHAR(1),
    RESTART_VAL BIGINT,
    CACHE INT,
    T_ORDER CHAR(1)
);

CREATE TABLE T_CONFIG (
    F_ID INT NOT NULL,
    CF_ID INT NOT NULL,
    CF_NAME VARCHAR(128),
    CF_FLAGS INT,
    CF_ORIGINAL LONG VARCHAR,
    CF_EFFECTIVE LONG VARCHAR,
    CF_DEFAULT LONG VARCHAR
);

CREATE TABLE T_OBJSTATE (
  F_ID INT NOT NULL,
    OBJTYPE CHAR(1),
    OWNER CHAR(32),
    NAME VARCHAR(128),
    TABID INT,
    STATE CHAR(1)
);

CREATE TABLE T_SYSFILES(
  F_ID INT NOT NULL,
  F_NAME VARCHAR(1024),
  F_CONTENT LONG VARCHAR , 
  F_DIGEST VARCHAR(255)	
);

CREATE TABLE T_META(
 NAME VARCHAR(255),
 VALUE VARCHAR(255)
);

CREATE TABLE T_OPCLSTR (
 F_ID  INT NOT NULL,
    OWNER VARCHAR(32) NOT NULL,
    CLSTRNAME VARCHAR(128) NOT NULL,
    CLSTRSIZE INT,
    TABID INT NOT NULL,
    BLOBCOL1 SMALLINT,
    BLOBCOL2 SMALLINT,
    BLOBCOL3 SMALLINT,
    BLOBCOL4 SMALLINT,
    BLOBCOL5 SMALLINT,
    BLOBCOL6 SMALLINT,
    BLOBCOL7 SMALLINT,
    BLOBCOL8 SMALLINT,
    BLOBCOL9 SMALLINT,
    BLOBCOL10 SMALLINT,
    BLOBCOL11 SMALLINT,
    BLOBCOL12 SMALLINT,
    BLOBCOL13 SMALLINT,
    BLOBCOL14 SMALLINT,
    BLOBCOL15 SMALLINT,
    BLOBCOL16 SMALLINT,
    CLSTRKEY1 SMALLINT,
    CLSTRKEY2 SMALLINT,
    CLSTRKEY3 SMALLINT,
    CLSTRKEY4 SMALLINT,
    CLSTRKEY5 SMALLINT,
    CLSTRKEY6 SMALLINT,
    CLSTRKEY7 SMALLINT,
    CLSTRKEY8 SMALLINT,
    CLSTRKEY9 SMALLINT,
    CLSTRKEY10 SMALLINT,
    CLSTRKEY11 SMALLINT,
    CLSTRKEY12 FLOAT,
    CLSTRKEY13 SMALLINT,
    CLSTRKEY14 SMALLINT,
    CLSTRKEY15 SMALLINT,
    CLSTRKEY16 SMALLINT,
    CLSTRKEY1__6634 FLOAT
);


CREATE TABLE T_AGGREGATES (
 F_ID  INT NOT NULL,
    NAME VARCHAR(128) NOT NULL,
    OWNER CHAR(32) NOT NULL,
    AGGID INT,
    INIT_FUNC VARCHAR(128),
    ITER_FUNC VARCHAR(128),
    COMBINE_FUNC VARCHAR(128),
    FINAL_FUNC VARCHAR(128),
    HANDLESNULLS CHAR(1)
   
);


CREATE TABLE T_AMS (
 F_ID  INT NOT NULL,
    AM_NAME VARCHAR(128) NOT NULL,
    AM_OWNER CHAR(32),
    AM_ID INT NOT NULL,
    AM_TYPE CHAR(1),
    AM_SPTYPE CHAR(3),
    AM_KEYSCAN INT,
    AM_UNIQUE INT,
    AM_CLUSTER INT,
    AM_ROWIDS INT,
    AM_READWRITE INT,
    AM_PARALLEL FLOAT,
    AM_COSTFACTOR FLOAT,
    AM_CREATE INT,
    AM_DROP INT,
    AM_OPEN INT,
    AM_CLOSE INT,
    AM_INSERT INT,
    AM_DELETE FLOAT,
    AM_UPDATE FLOAT,
    AM_STATS FLOAT,
    AM_SCANCOST FLOAT,
    AM_CHECK FLOAT,
    AM_BEGINSCAN INT,
    AM_ENDSCAN INT,
    AM_RESCAN INT,
    AM_GETNEXT INT,
    AM_GETBYID INT,
    AM_BUILD INT,
    AM_INIT INT,
    AM_TRUNCATE INT
    
);

CREATE TABLE T_TABAMDATA (
 F_ID  INT NOT NULL,
    AM_PARAM VARCHAR(255),
    AM_SPACE VARCHAR(128),
    TABID INT NOT NULL
);


CREATE INDEX ix_objstate2 ON t_objstate(f_id, tabid, name , owner, objtype);
CREATE INDEX ix_proc ON t_procedures(f_id, procid);
CREATE INDEX ix_tabs ON t_tables(f_id, tabid);
CREATE INDEX ix_const ON t_constraints(f_id, tabid, constrname);
CREATE INDEX ix_trigbody ON t_trigbody(f_id, trigid);
CREATE INDEX ix_viewbody ON t_views(f_id, tabid);
CREATE INDEX ix_columns ON t_columns(f_id, tabid);
CREATE INDEX ix_inx ON t_indexes(f_id, tabid);
CREATE INDEX ix_procbody ON t_procbody(f_id, procid);

-- @upgrade_2_7 annotation to upgrade to version 2.7;
CREATE TABLE t_diff_objects(
         objectid     INT NOT NULL GENERATED ALWAYS AS IDENTITY,
         host         VARCHAR(128) NOT NULL,
         server       VARCHAR(128) NOT NULL,
         dbsname      VARCHAR(128) NOT NULL,
         name         VARCHAR(255) NOT NULL,
         owner        VARCHAR(128) NOT NULL,
         objecttype   SMALLINT NOT NULL,
         versionid    INT NOT NULL,
         primary key(host, server, dbsname, name, owner, objecttype, 
            versionid));

CREATE INDEX ix_t_diff_objec812 ON t_diff_objects(
        objectid);
        
CREATE TABLE t_diff_reports(
         reportid      INT NOT NULL GENERATED ALWAYS AS IDENTITY,
         old_obj_id    INT,
         new_obj_id    INT,
         tmstamp       TIMESTAMP,
         options       INT,
         status		   SMALLINT,
         groupid 	   INT,		
         scriptid      INT,
         report_hash   CHAR(32),
         jobname	   VARCHAR(128),
         primary key(reportid)
         
    );

CREATE INDEX ix_t_diff_repor812 ON t_diff_reports(
        old_obj_id);

CREATE INDEX ix_t_diff_repor861 ON t_diff_reports(
        new_obj_id);
    

CREATE INDEX ix_t_diff_repor862 ON t_diff_reports(
        groupid);

CREATE INDEX ix_t_diff_repor863 ON t_diff_reports(
        tmstamp);

        
        
CREATE TABLE t_diff_scripts(
         scriptid      INT NOT NULL GENERATED ALWAYS AS IDENTITY,
         xmlzip_report   BLOB(256 K));


 CREATE TABLE t_alert_reports(
         alertid      VARCHAR(20),
         reportid	  INT	
 );
  
 CREATE INDEX ix_t_alert_report1 ON t_alert_reports(
        alertid);
        
-- @upgrade_2_8 annotation to upgrade to version 2.8;
CREATE TABLE t_diff_exec_err(
	reportid INT,
	errorCode INT,
	errors LONG VARCHAR
);

CREATE INDEX t_diff_exec_err1 ON t_diff_exec_err(
        reportid);
        
-- @upgrade_2_9;
 CREATE INDEX ix_t_diff_repor718 ON t_diff_reports(
        jobname);

-- @upgrade_3_0;        
CREATE INDEX ix_t_diff_objec99 ON t_diff_objects(
        objecttype);

 -- @upgrade_3_3;
 ALTER TABLE t_columns ADD colattr INT DEFAULT -1;   