--==============================================================
-- DBMS name:      IBM DB2 UDB 8.x Common Server
-- Created on:     2006-9-1 13:34:32
--==============================================================


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;

--==============================================================
-- Table: ACLENTRIES_2
--==============================================================
create table ACLENTRIES_2
(
   ENTRY_ID             INTEGER                not null,
   ACL_ID               INTEGER                not null,
   PRINCIPAL_TYPE       SMALLINT               not null,
   PRINCIPAL_NAME       VARCHAR(64)            not null,
   IS_POSITIVE          SMALLINT               not null,
   PERMISSIONS          INTEGER                not null,
   constraint "P_Key_1" primary key (ENTRY_ID)
);

--==============================================================
-- Table: ACLS_2
--==============================================================
create table ACLS_2
(
   ID                   INTEGER                not null,
   "NAME"               VARCHAR(64)            not null,
   CREATE_NAME          VARCHAR(32)            not null,
   CREATE_TIME          BIGINT,
   constraint "P_Key_1" primary key (ID)
);

--==============================================================
-- Table: ACLS_WITH_RESOURCE_1
--==============================================================
create table ACLS_WITH_RESOURCE_1
(
   ID                   INTEGER                not null,
   RESOURCE_PATH        VARCHAR(256)           not null,
   VERSION_NUMBER       INTEGER                not null,
   ACL_NAME             VARCHAR(64)            not null,
   CREATE_NAME          VARCHAR(32)            not null,
   CREATE_TIME          BIGINT,
   constraint "P_Key_1" primary key (ID)
);

--==============================================================
-- Table: ALIASES_1
--==============================================================
create table ALIASES_1
(
   ID                   INTEGER                not null,
   PRINCIPAL_NAME       VARCHAR(128)           not null,
   PRINCIPAL_TYPE       SMALLINT               not null,
   ALIAS_NAME           VARCHAR(256)           not null,
   PARENT_NAME          VARCHAR(256),
   MAP_RESOURCE         VARCHAR(256)           not null,
   HIDDEN               SMALLINT               not null,
   constraint "A_Key_1" unique (ID)
);

--==============================================================
-- Table: CATALOGVERSION_2
--==============================================================
create table CATALOGVERSION_2
(
   VERSIONNUMBER        INTEGER                not null,
   VERSIONNAME          VARCHAR(128),
   RESOURCEID           INTEGER                not null,
   CREATOR              VARCHAR(128),
   CREATEDTIME          BIGINT                 not null,
   MODIFIER             VARCHAR(128),
   MODIFIEDTIME         BIGINT                 not null,
   CATALOGFILE          VARCHAR(128)           not null,
   ID                   BIGINT                 not null,
   constraint "A_Key_1" unique (ID)
);

--==============================================================
-- Table: COMPLETEDPUBLISHTABLE_2
--==============================================================
create table COMPLETEDPUBLISHTABLE_2
(
   ID                   INTEGER                not null,
   RECORDID             VARCHAR(40)            not null,
   SHEETNAME            VARCHAR(256)           not null,
   DISPLAYNAME          VARCHAR(256),
   PUB_TYPE             VARCHAR(64)            not null
);

--==============================================================
-- Table: COMPLETEDRESULTTABLE_3
--==============================================================
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(256)           not null
);

--==============================================================
-- Table: COMPLETEDTABLE_4
--==============================================================
create table COMPLETEDTABLE_4
(
   ID                   INTEGER                not null,
   RECORDID             VARCHAR(128),
   TASKID               VARCHAR(128)           not null,
   TASKCLASS            VARCHAR(256)           not null,
   CATFILE              VARCHAR(256),
   RPTFILE              VARCHAR(256),
   PARAMFILE            VARCHAR(256),
   RESULTFILE           VARCHAR(512),
   ERROR                VARCHAR(256),
   ENGMSG               VARCHAR(512),
   SUBMITTER            VARCHAR(128),
   COMPLETEDTIME        BIGINT,
   FORMATEXISTS         INTEGER,
   FORMATSUCCESSFUL     INTEGER,
   VERSIONID            INTEGER,
   LAUNCHTYPE           INTEGER,
   ISSUCCESSFUL         SMALLINT               not null,
   TASKNAME             VARCHAR(128),
   ISMISSED             SMALLINT               not null,
   SCHEDULETRIGGER      VARCHAR(80),
   CONDITIONSLOGIC      VARCHAR(16),
   REPORTNAMES          VARCHAR(512)
);

--==============================================================
-- Table: DRESULT_2
--==============================================================
create table DRESULT_2
(
   REPORTSET            VARCHAR(128),
   VERSIONNUMBER        BIGINT                 not null,
   REPORT               VARCHAR(128)           not null,
   REPORTDISPLAYNAME    VARCHAR(128),
   "RESULT"             VARCHAR(128)           not null,
   VIEWEDFORMATS        BIGINT,
   PLSRESULTFMTS        BIGINT,
   SECCONSTRAINTFMTS    BIGINT
);

--==============================================================
-- Table: DYNAMICNODE_2
--==============================================================
create table DYNAMICNODE_2
(
   "NAME"               VARCHAR(128)           not null,
   ROOT                 VARCHAR(128)           not null,
   REALPATH             VARCHAR(256)           not null,
   NODETYPE             SMALLINT               not null,
   ENABLEARCHIVEPOLICY  SMALLINT               not null,
   DELETED              SMALLINT               not null,
   ACLID                INTEGER,
   DESCRIPTION          VARCHAR(256),
   MAXVERSIONAMOUNT     INTEGER,
   ENABLELINKEDCAT      SMALLINT, 
   LINKEDCAT            VARCHAR(256), 
   USEINHERITED         SMALLINT
);

--==============================================================
-- Table: DYNAMICRESULT_1
--==============================================================
create table DYNAMICRESULT_1
(
   ID                   INTEGER                not null,
   REPORTNAME           VARCHAR(128)           not null,
   VERSIONNUMBER        INTEGER                not null,
   CREATOR              VARCHAR(128)           not null,
   CREATEDTIME          BIGINT                 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           BIGINT                 not null,
   LAUNCHTYPE           INTEGER                not null,
   TASKID               VARCHAR(128),
   EXPIRATIONTIME       BIGINT                 not null,
   EXPIRE               SMALLINT               not null,
   EXPIRATIONMETHOD     INTEGER,
   REPORTAMOUNT         INTEGER
);

--==============================================================
-- Table: EXTENDACLENTRIES_2
--==============================================================
create table EXTENDACLENTRIES_2
(
   PRINCIPAL_NAME       VARCHAR(64)            not null,
   PRINCIPAL_TYPE       SMALLINT               not null,
   PERMISSIONS          INTEGER                not null,
   constraint "A_Key_1" unique (PRINCIPAL_NAME, PRINCIPAL_TYPE)
);

--==============================================================
-- Table: FOLDERS_3
--==============================================================
create table FOLDERS_3
(
   ID                   INTEGER                not null,
   REALPATH             VARCHAR(256),
   OWNERNAME            VARCHAR(32),
   MAXVERSIONAMOUNT     INTEGER                not null,
   ARCHIVENEWVERSION    SMALLINT               not null,
   ENABLEARCHIVEPOLICY  SMALLINT               not null,
   IS_SHARED            SMALLINT               not null,
   ENABLEDYNAMICRESOURCE SMALLINT               not null
);

--==============================================================
-- Table: GROUPPROFILES_1
--==============================================================
create table GROUPPROFILES_1
(
   GROUP_NAME           VARCHAR(32),
   SUB_GROUP_NAME       VARCHAR(32)
);

--==============================================================
-- Table: GROUPS_1
--==============================================================
create table GROUPS_1
(
   "NAME"               VARCHAR(32)            not null,
   BUILDIN              SMALLINT               not null,
   DESCRIPTION          VARCHAR(32),
   GROUP_TYPE           INTEGER                not null,
   constraint "P_Key_1" primary key ("NAME")
);

--==============================================================
-- Table: GUPROFILES_1
--==============================================================
create table GUPROFILES_1
(
   GROUP_NAME           VARCHAR(32),
   USER_NAME            VARCHAR(32)
);

--==============================================================
-- Table: NODES_3
--==============================================================
create table NODES_3
(
   ID                   INTEGER                not null,
   "NAME"               VARCHAR(64)            not null,
   CREATORNAME          VARCHAR(32)            not null,
   CREATETIME           BIGINT                 not null,
   PARENTID             INTEGER                not null,
   NODETYPE             SMALLINT               not null,
   ACLID                INTEGER,
   DESCRIPTION          VARCHAR(256),
   ENABLELINKEDCAT      SMALLINT, 
   LINKEDCAT            VARCHAR(256), 
   USEINHERITED         SMALLINT
);

--==============================================================
-- Table: REPORTVERSION_2
--==============================================================
create table REPORTVERSION_2
(
   VERSIONNUMBER        INTEGER                not null,
   VERSIONNAME          VARCHAR(128),
   RESOURCEID           INTEGER                not null,
   CREATOR              VARCHAR(128),
   CREATETIME           BIGINT                 not null,
   MODIFIER             VARCHAR(128),
   MODIFIEDTIME         BIGINT                 not null,
   REPORTFILE           VARCHAR(128)           not null,
   ID                   BIGINT                 not null,
   constraint "A_Key_1" unique (RESOURCEID, ID)
);

--==============================================================
-- Table: RESOURCEMAP_1
--==============================================================
create table RESOURCEMAP_1
(
   RESOURCEID           INTEGER                not null,
   RESOURCENAME         VARCHAR(128)           not null,
   RESOURCETYPE         SMALLINT               not null
);

--==============================================================
-- Table: RESOURCENODES_3
--==============================================================
create table RESOURCENODES_3
(
   ID                   INTEGER                not null,
   RESOURCETYPE         SMALLINT               not null,
   REALPATH             VARCHAR(256),
   MAXVERSIONAMOUNT     INTEGER                not null,
   ARCHIVENEWVERSION    SMALLINT               not null,
   ENABLEARCHIVEPOLICY  SMALLINT               not null,
   PROFILENAME             VARCHAR(256)
);

--==============================================================
-- Table: RESULTVERSION_2
--==============================================================
create table RESULTVERSION_2
(
   ID                   BIGINT                 not null,
   VERSIONNUMBER        INTEGER                not null,
   VERSIONNAME          VARCHAR(128)           not null,
   RESOURCEID           INTEGER                not null,
   CREATOR              VARCHAR(128),
   CREATEDTIME          BIGINT                 not null,
   MODIFIER             VARCHAR(128),
   MODIFIEDTIME         BIGINT                 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           BIGINT                 not null,
   LAUNCHTYPE           INTEGER                not null,
   TASKID               VARCHAR(128),
   EXPIRATIONTIME       BIGINT                 not null,
   EXPIRE               SMALLINT               not null,
   EXPIREATIONMETHOD    INTEGER,
   SPECIFIEDFOLDER      VARCHAR(128),
   REPORTAMOUNT         INTEGER,
   constraint "A_Key_1" unique (ID, RESOURCEID)
);

--==============================================================
-- Table: RESULT_2
--==============================================================
create table RESULT_2
(
   RESOURCEID           BIGINT                 not null,
   VERSIONNUMBER        BIGINT                 not null,
   REPORT               VARCHAR(128)           not null,
   REPORTDISPLAYNAME    VARCHAR(128),
   "RESULT"             VARCHAR(128)           not null,
   VIEWEDFORMATS        BIGINT,
   PLSRESULTFMTS        BIGINT,
   SECCONSTRAINTFMTS    BIGINT
);

--==============================================================
-- Table: RGPROFILES_1
--==============================================================
create table RGPROFILES_1
(
   ROLE_NAME            VARCHAR(32),
   GROUP_NAME           VARCHAR(32)
);

--==============================================================
-- Table: ROLEPROFILES_2
--==============================================================
create table ROLEPROFILES_2
(
   ROLE_NAME            VARCHAR(32),
   SUB_ROLE_NAME        VARCHAR(32)
);

--==============================================================
-- Table: ROLES_2
--==============================================================
create table ROLES_2
(
   "NAME"               VARCHAR(32)            not null,
   BUILDIN              SMALLINT               not null,
   DESCRIPTION          VARCHAR(32),
   ROLE_TYPE            INTEGER                not null,
   constraint "P_Key_1" primary key ("NAME")
);

--==============================================================
-- Table: SCHDLASTEXETIME_1
--==============================================================
create table SCHDLASTEXETIME_1
(
   TASKID               VARCHAR(40)            not null,
   LASTEXETIME          BIGINT                 not null
);

--==============================================================
-- Table: TBL_IDGENRATOR_JINFONET
--==============================================================
create table TBL_IDGENRATOR_JINFONET
(
   TF_NAME              VARCHAR(64)            not null,
   NEXT_ID              INTEGER                not null,
   constraint "P_Key_1" primary key (TF_NAME)
);

--==============================================================
-- Table: TRIGGERS_1
--==============================================================
create table TRIGGERS_1
(
   TRIGGER_NAME         VARCHAR(80)            not null,
   DESCRIPTION          VARCHAR(255),
   TRIGGER_TYPE         SMALLINT               not null,
   TRIGGER_STATE        SMALLINT               not null,
   PREV_FIRE_TIME       BIGINT,
   NEXT_FIRE_TIME       BIGINT,
   constraint "P_Key_1" primary key (TRIGGER_NAME)
);

--==============================================================
-- Table: UPDATEDETAILS_1
--==============================================================
create table UPDATEDETAILS_1
(
   UPDATE_ITEM          VARCHAR(32)            not null,
   UPDATE_STATE         SMALLINT               not null,
   constraint "P_Key_1" primary key (UPDATE_ITEM)
);

--==============================================================
-- Table: USERPROFILES_2
--==============================================================
create table USERPROFILES_2
(
   USER_NAME            VARCHAR(32),
   ROLE_NAME            VARCHAR(32)
);

--==============================================================
-- Table: USERS_3
--==============================================================
create table USERS_3
(
   "NAME"               VARCHAR(32)            not null,
   FULL_NAME            VARCHAR(128),
   DESCRIPTION          VARCHAR(128),
   EMAIL                VARCHAR(128),
   CREATE_TIME          BIGINT                 not null,
   USER_PSWD            VARCHAR(128),
   BUILDIN              SMALLINT               not null,
   ENABLE               SMALLINT               not null,
   MIN_PS_LENGTH        INTEGER,
   LAST_MODIFY_TIME     BIGINT,
   NEVER_EXPIRE         SMALLINT               not null,
   EXPIRE_TIME          INTEGER,
   ACCOUNT_DISABLE      SMALLINT               not null,
   ACCESS_EVENT_SUC     SMALLINT               not null,
   ACCESS_EVENT_FAIL    SMALLINT               not null,
   MANAGEMENT_EVENT_SUC SMALLINT               not null,
   MANAGEMENT_EVENT_FAIL SMALLINT               not null,
   NO_AUDIT             SMALLINT               not null,
   AUTH_TYPE            INTEGER                not null,
   LDAP_TYPE            INTEGER,
   constraint "P_Key_1" primary key ("NAME")
);

--==============================================================
-- Table: VERSIONNODES_2
--==============================================================
create table VERSIONNODES_2
(
   ID                   INTEGER                not null,
   RESOURCEID           INTEGER                not null,
   VERSIONNUMBER        INTEGER                not null,
   EXPIRATIONTIME       BIGINT                 not null
);


--==============================================================
-- 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 		TIMESTAMP,
	TS_ENG_START 		TIMESTAMP,
	TS_ENG_EXITS 		TIMESTAMP,
	TS_TSK_COMPLETE 	TIMESTAMP,
	TC_TSK_TOTAL 		BIGINT,
	TC_TSK_WAITED 		BIGINT,
	TC_ENG_TOTAL 		BIGINT,
	TC_ENG_LOAD 		BIGINT,
	TC_ENG_EXPORT 		BIGINT,
	TC_ENG_OTHER 		BIGINT,
	TC_TSK_OTHER 		BIGINT,
	NC_RPT_PAGES 		BIGINT,
	NC_RPT_FORMATS 		BIGINT,
	NC_ENG_CONCURRENT 	BIGINT,
	NC_QUE_LENGTH 		BIGINT, 
	PRIMARY KEY(ID)
);



--=================================================================
--  Init Data
--=================================================================
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);