--==============================================================
-- DBMS name:      INFORMIX SQL 9.x
-- Created on:     2006-9-1 13:14:39
--==============================================================


drop table ACLENTRIES_2;

drop table ACLS_2;

drop table ACLS_WITH_RESOURCE_1;

drop table ALIASES_1;

drop table CATALOGVERSION_2;

drop table COMPLETEDPUBLISHTABLE_2;

drop table COMPLETEDRESULTTABLE_3;

drop table COMPLETEDTABLE_4;

drop table DRESULT_2;

drop table DYNAMICRESULT_1;

drop table EXTENDACLENTRIES_2;

drop table FOLDERS_3;

drop table GROUPPROFILES_1;

drop table GROUPS_1;

drop table GUPROFILES_1;

drop table NODES_3;

drop table REPORTVERSION_2;

drop table RESOURCEMAP_1;

drop table RESOURCENODES_3;

drop table RESULTVERSION_2;

drop table RESULT_2;

drop table RGPROFILES_1;

drop table ROLEPROFILES_2;

drop table ROLES_2;

drop table SCHDLASTEXETIME_1;

drop table TBL_IDGENRATOR_JINFONET;

drop table TRIGGERS_1;

drop table UPDATEDETAILS_1;

drop table USERPROFILES_2;

drop table USERS_3;

drop table VERSIONNODES_2;

drop table TASKCONTEXT;

drop table DYNAMICNODE_2;


CREATE TABLE TBL_IDGENRATOR_JINFONET (TF_NAME VARCHAR(64) NOT NULL,NEXT_ID INTEGER NOT NULL, PRIMARY KEY(TF_NAME));
CREATE TABLE UPDATEDETAILS_1 (UPDATE_ITEM VARCHAR(32) NOT NULL,UPDATE_STATE INTEGER NOT NULL, PRIMARY KEY(UPDATE_ITEM));
CREATE TABLE NODES_3 (ID INTEGER NOT NULL,NAME VARCHAR(64) NOT NULL,CREATORNAME VARCHAR(32) NOT NULL,CREATETIME INT8 NOT NULL,PARENTID INTEGER NOT NULL,NODETYPE INTEGER NOT NULL,ACLID INTEGER,DESCRIPTION VARCHAR(255), ENABLELINKEDCAT INTEGER, LINKEDCAT VARCHAR(255), USEINHERITED INTEGER);
CREATE TABLE FOLDERS_3 (ID INTEGER NOT NULL,REALPATH VARCHAR(255),OWNERNAME VARCHAR(32),MAXVERSIONAMOUNT INTEGER NOT NULL,ARCHIVENEWVERSION INTEGER NOT NULL,ENABLEARCHIVEPOLICY INTEGER NOT NULL,IS_SHARED INTEGER NOT NULL,ENABLEDYNAMICRESOURCE INTEGER NOT NULL);
CREATE TABLE RESOURCENODES_3 (ID INTEGER NOT NULL,RESOURCETYPE INTEGER NOT NULL,REALPATH VARCHAR(255),MAXVERSIONAMOUNT INTEGER NOT NULL,ARCHIVENEWVERSION INTEGER NOT NULL,ENABLEARCHIVEPOLICY INTEGER NOT NULL,PROFILENAME VARCHAR(255));
CREATE TABLE VERSIONNODES_2 (ID INTEGER NOT NULL,RESOURCEID INTEGER NOT NULL,VERSIONNUMBER INTEGER NOT NULL,EXPIRATIONTIME INT8 NOT NULL);
CREATE TABLE ALIASES_1 (ID INTEGER NOT NULL,PRINCIPAL_NAME VARCHAR(128) NOT NULL,PRINCIPAL_TYPE INTEGER NOT NULL,ALIAS_NAME VARCHAR(255) NOT NULL,PARENT_NAME VARCHAR(255),MAP_RESOURCE VARCHAR(255) NOT NULL,HIDDEN INTEGER NOT NULL, UNIQUE(ID));
CREATE TABLE CATALOGVERSION_2 (VERSIONNUMBER INTEGER NOT NULL,VERSIONNAME VARCHAR(128),RESOURCEID INTEGER NOT NULL,CREATOR VARCHAR(128),CREATEDTIME INT8 NOT NULL,MODIFIER VARCHAR(128),MODIFIEDTIME INT8 NOT NULL,CATALOGFILE VARCHAR(128) NOT NULL,ID INT8 NOT NULL, UNIQUE(ID));
CREATE TABLE REPORTVERSION_2 (VERSIONNUMBER INTEGER NOT NULL,VERSIONNAME VARCHAR(128),RESOURCEID INTEGER NOT NULL,CREATOR VARCHAR(128),CREATETIME INT8 NOT NULL,MODIFIER VARCHAR(128),MODIFIEDTIME INT8 NOT NULL,REPORTFILE VARCHAR(128) NOT NULL,ID INT8 NOT NULL, UNIQUE(RESOURCEID,ID));
CREATE TABLE RESULTVERSION_2 (ID INT8 NOT NULL,VERSIONNUMBER INTEGER NOT NULL,VERSIONNAME VARCHAR(128) NOT NULL,RESOURCEID INTEGER NOT NULL,CREATOR VARCHAR(128),CREATEDTIME INT8 NOT NULL,MODIFIER VARCHAR(128),MODIFIEDTIME INT8 NOT NULL,CATALOGVERNUM INTEGER NOT NULL,CATALOGRESNUM INTEGER NOT NULL,REPORTVERNUM INTEGER NOT NULL,REPORTRESNUM INTEGER NOT NULL,PARAMFILE VARCHAR(128),RESULTFILE VARCHAR(128) NOT NULL,RESULTFMTS INT8 NOT NULL,LAUNCHTYPE INTEGER NOT NULL,TASKID VARCHAR(128),EXPIRATIONTIME INT8 NOT NULL,EXPIRE INTEGER NOT NULL,EXPIREATIONMETHOD INTEGER,SPECIFIEDFOLDER VARCHAR(128),REPORTAMOUNT INTEGER, UNIQUE(ID,RESOURCEID));
CREATE TABLE RESULT_2 (RESOURCEID INT8 NOT NULL,VERSIONNUMBER INT8 NOT NULL,REPORT VARCHAR(128) NOT NULL,REPORTDISPLAYNAME VARCHAR(128),RESULT VARCHAR(128) NOT NULL,VIEWEDFORMATS INT8,PLSRESULTFMTS INT8,SECCONSTRAINTFMTS INT8);
CREATE TABLE DYNAMICNODE_2 (NAME VARCHAR(128) NOT NULL,ROOT VARCHAR(128) NOT NULL,REALPATH VARCHAR(255) NOT NULL,NODETYPE INTEGER NOT NULL,ENABLEARCHIVEPOLICY INTEGER NOT NULL,DELETED INTEGER NOT NULL,ACLID INTEGER,DESCRIPTION VARCHAR(255),MAXVERSIONAMOUNT INTEGER;ENABLELINKEDCAT INTEGER, LINKEDCAT VARCHAR(255), USEINHERITED INTEGER);
CREATE TABLE DYNAMICRESULT_1 (ID INTEGER NOT NULL,REPORTNAME VARCHAR(128) NOT NULL,VERSIONNUMBER INTEGER NOT NULL,CREATOR VARCHAR(128) NOT NULL,CREATEDTIME INT8 NOT NULL,CATALOGVERNUM INTEGER NOT NULL,CATALOGRESNUM INTEGER NOT NULL,REPORTVERNUM INTEGER NOT NULL,REPORTRESNUM INTEGER NOT NULL,PARAMFILE VARCHAR(128),RESULTFILE VARCHAR(128) NOT NULL,RESULTFMTS INT8 NOT NULL,LAUNCHTYPE INTEGER NOT NULL,TASKID VARCHAR(128),EXPIRATIONTIME INT8 NOT NULL,EXPIRE INTEGER NOT NULL,EXPIRATIONMETHOD INTEGER,REPORTAMOUNT INTEGER);
CREATE TABLE DRESULT_2 (REPORTSET VARCHAR(128),VERSIONNUMBER INT8 NOT NULL,REPORT VARCHAR(128) NOT NULL,REPORTDISPLAYNAME VARCHAR(128),RESULT VARCHAR(128) NOT NULL,VIEWEDFORMATS INT8,PLSRESULTFMTS INT8,SECCONSTRAINTFMTS INT8);
CREATE TABLE RESOURCEMAP_1 (RESOURCEID INTEGER NOT NULL,RESOURCENAME VARCHAR(128) NOT NULL,RESOURCETYPE INTEGER NOT NULL);
CREATE TABLE USERS_3 (NAME VARCHAR(32) NOT NULL,FULL_NAME VARCHAR(128),DESCRIPTION VARCHAR(128),EMAIL VARCHAR(128),CREATE_TIME INT8 NOT NULL,USER_PSWD VARCHAR(128),BUILDIN INTEGER NOT NULL,ENABLE INTEGER NOT NULL,MIN_PS_LENGTH INTEGER,LAST_MODIFY_TIME INT8,NEVER_EXPIRE INTEGER NOT NULL,EXPIRE_TIME INTEGER,ACCOUNT_DISABLE INTEGER NOT NULL,ACCESS_EVENT_SUC INTEGER NOT NULL,ACCESS_EVENT_FAIL INTEGER NOT NULL,MANAGEMENT_EVENT_SUC INTEGER NOT NULL,MANAGEMENT_EVENT_FAIL INTEGER NOT NULL,NO_AUDIT INTEGER NOT NULL,AUTH_TYPE INTEGER NOT NULL,LDAP_TYPE INTEGER, PRIMARY KEY(NAME));
CREATE TABLE USERPROFILES_2 (USER_NAME VARCHAR(32),ROLE_NAME VARCHAR(32));
CREATE TABLE ROLES_2 (NAME VARCHAR(32) NOT NULL,BUILDIN INTEGER NOT NULL,DESCRIPTION VARCHAR(32),ROLE_TYPE INTEGER NOT NULL, PRIMARY KEY(NAME));
CREATE TABLE ACLS_2 (ID INTEGER NOT NULL,NAME VARCHAR(64) NOT NULL,CREATE_NAME VARCHAR(32) NOT NULL,CREATE_TIME INT8, PRIMARY KEY(ID));
CREATE TABLE ACLENTRIES_2 (ENTRY_ID INTEGER NOT NULL,ACL_ID INTEGER NOT NULL,PRINCIPAL_TYPE INTEGER NOT NULL,PRINCIPAL_NAME VARCHAR(64) NOT NULL,IS_POSITIVE INTEGER NOT NULL,PERMISSIONS INTEGER NOT NULL, PRIMARY KEY(ENTRY_ID));
CREATE TABLE ROLEPROFILES_2 (ROLE_NAME VARCHAR(32),SUB_ROLE_NAME VARCHAR(32));
CREATE TABLE GROUPS_1 (NAME VARCHAR(32) NOT NULL,BUILDIN INTEGER NOT NULL,DESCRIPTION VARCHAR(32),GROUP_TYPE INTEGER NOT NULL, PRIMARY KEY(NAME));
CREATE TABLE GROUPPROFILES_1 (GROUP_NAME VARCHAR(32),SUB_GROUP_NAME VARCHAR(32));
CREATE TABLE RGPROFILES_1 (ROLE_NAME VARCHAR(32),GROUP_NAME VARCHAR(32));
CREATE TABLE GUPROFILES_1 (GROUP_NAME VARCHAR(32),USER_NAME VARCHAR(32));
CREATE TABLE EXTENDACLENTRIES_2 (PRINCIPAL_NAME VARCHAR(64) NOT NULL,PRINCIPAL_TYPE INTEGER NOT NULL,PERMISSIONS INTEGER NOT NULL, UNIQUE(PRINCIPAL_NAME,PRINCIPAL_TYPE));
CREATE TABLE COMPLETEDTABLE_4 (ID INTEGER NOT NULL,RECORDID VARCHAR(128),TASKID VARCHAR(128) NOT NULL,TASKCLASS VARCHAR(255) NOT NULL,CATFILE VARCHAR(255),RPTFILE VARCHAR(255),PARAMFILE VARCHAR(255),RESULTFILE VARCHAR(255),ERROR VARCHAR(255),ENGMSG VARCHAR(255),SUBMITTER VARCHAR(128),COMPLETEDTIME INT8,FORMATEXISTS INTEGER,FORMATSUCCESSFUL INTEGER,VERSIONID INTEGER,LAUNCHTYPE INTEGER,ISSUCCESSFUL INTEGER NOT NULL,TASKNAME VARCHAR(128),ISMISSED INTEGER NOT NULL,SCHEDULETRIGGER VARCHAR(80),CONDITIONSLOGIC VARCHAR(16),REPORTNAMES VARCHAR(255));
CREATE TABLE COMPLETEDPUBLISHTABLE_2 (ID INTEGER NOT NULL,RECORDID VARCHAR(40) NOT NULL,SHEETNAME VARCHAR(255) NOT NULL,DISPLAYNAME VARCHAR(255),PUB_TYPE VARCHAR(64) NOT NULL);
CREATE TABLE COMPLETEDRESULTTABLE_3 (PUBLISHID INTEGER NOT NULL,RECORDID VARCHAR(40) NOT NULL,PUB_TYPE VARCHAR(128) NOT NULL,STATUS INTEGER NOT NULL,DETAIL VARCHAR(255) NOT NULL);
CREATE TABLE TRIGGERS_1 (TRIGGER_NAME VARCHAR(80) NOT NULL,DESCRIPTION VARCHAR(255),TRIGGER_TYPE INTEGER NOT NULL,TRIGGER_STATE INTEGER NOT NULL,PREV_FIRE_TIME INT8,NEXT_FIRE_TIME INT8, PRIMARY KEY(TRIGGER_NAME));
CREATE TABLE SCHDLASTEXETIME_1 (TASKID VARCHAR(40) NOT NULL,LASTEXETIME INT8 NOT NULL);
CREATE TABLE ACLS_WITH_RESOURCE_1 (ID INTEGER NOT NULL,RESOURCE_PATH VARCHAR(255) NOT NULL,VERSION_NUMBER INTEGER NOT NULL,ACL_NAME VARCHAR(64) NOT NULL,CREATE_NAME VARCHAR(32) NOT NULL,CREATE_TIME INT8, PRIMARY KEY(ID));

--==============================================================
-- Table: TASKCONTEXT
-- For profiling
--==============================================================
CREATE TABLE TASKCONTEXT (
	ID 					INTEGER NOT NULL,
	VS_RPT_NAME 		VARCHAR(255),
	VI_TSK_TYPE 		INTEGER,
	VI_TSK_STATUS 		INTEGER,
	TS_TSK_SUBMIT 		DATETIME YEAR TO FRACTION,
	TS_ENG_START 		DATETIME YEAR TO FRACTION,
	TS_ENG_EXITS 		DATETIME YEAR TO FRACTION,
	TS_TSK_COMPLETE 	DATETIME YEAR TO FRACTION,
	TC_TSK_TOTAL 		INT8,
	TC_TSK_WAITED 		INT8,
	TC_ENG_TOTAL 		INT8,
	TC_ENG_LOAD 		INT8,
	TC_ENG_EXPORT 		INT8,
	TC_ENG_OTHER 		INT8,
	TC_TSK_OTHER 		INT8,
	NC_RPT_PAGES 		INT8,
	NC_RPT_FORMATS 		INT8,
	NC_ENG_CONCURRENT 	INT8,
	NC_QUE_LENGTH 		INT8, 
	PRIMARY KEY(ID)
);

--==============================================================
-- INITDATA
--==============================================================
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('NODES_3_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('CATALOGVERSION_2_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('REPORTVERSION_2_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('RESULTVERSION_2_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('ACLS_2_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('ACLENTRIES_2_ENTRY_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('COMPLETEDTABLE_4_ID',0);
INSERT INTO TBL_IDGENRATOR_JINFONET (TF_NAME,NEXT_ID) VALUES ('ACLS_WITH_RESOURCE_1_ID',0);