analytics/configuration/schemas/hts/hsql/HTS1_CREATE_Hsql.sql

--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: HTS
--    
--    (c) Copyright IBM Corp. 2003, 2010.
--    
--    US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    

--
--  Modifications:
--
--  1) Change NCCOG  to the desired database name.
--  2) Note: CCSID is an estimated value, your
--           requirements may differ.

-------------------------------------------------------------------------------
-- HTS_CREATE_Hsql.sql         *  DO NOT EDIT  *          Data Store Version: 2
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$200-- tables create begin
-------------------------------------------------------------------------------
CREATE TABLE HTS_ATTACHMENT (attachmentId VARCHAR(50) NOT NULL, accessType VARCHAR(250) NOT NULL, attachURI VARCHAR(50), attachedAt BIGINT NOT NULL, attachedBy VARCHAR(250) NOT NULL, contentType VARCHAR(250) NOT NULL, lang VARCHAR(10), linkId VARCHAR(50), name VARCHAR(250) NOT NULL, value VARCHAR(50), PRIMARY KEY (attachmentId));
CREATE TABLE HTS_CALLBACK (callbackId VARCHAR(50) NOT NULL, action VARCHAR(250), address VARCHAR(250), credential VARCHAR(250), operation VARCHAR(250), PRIMARY KEY (callbackId));
CREATE TABLE HTS_CALLBACKPARAM (callbackParamId VARCHAR(50) NOT NULL, qname VARCHAR(250), value VARCHAR(50), callbackId VARCHAR(50), PRIMARY KEY (callbackParamId));
CREATE TABLE HTS_DEADLINE (deadlineId VARCHAR(50) NOT NULL, deadlineType VARCHAR(50) NOT NULL, timeForExpression VARCHAR(250), timeUntilExpression VARCHAR(250), PRIMARY KEY (deadlineId));
CREATE TABLE HTS_DEADLINE_INSTANCE (deadlineInstanceId VARCHAR(50) NOT NULL, deadlineSignature VARCHAR(250), timeout BIGINT, taskId VARCHAR(50), PRIMARY KEY (deadlineInstanceId));
CREATE TABLE HTS_ESCALATION (escalationId VARCHAR(50) NOT NULL, conditionExpression VARCHAR(250), localNotificationId VARCHAR(50), PRIMARY KEY (escalationId));
CREATE TABLE HTS_ESCALATION_HUMANROLES (escalationHumanRolesId VARCHAR(50) NOT NULL, fromExpression VARCHAR(250), roleName VARCHAR(50) NOT NULL, userId VARCHAR(50), PRIMARY KEY (escalationHumanRolesId));
CREATE TABLE HTS_ESCALATION_TO_PART (toPartId VARCHAR(50) NOT NULL, expression VARCHAR(250), name VARCHAR(250), PRIMARY KEY (toPartId));
CREATE TABLE HTS_FOLDER (folderId VARCHAR(50) NOT NULL, folderName VARCHAR(125), folderType VARCHAR(250), taskId VARCHAR(50), userId VARCHAR(50), PRIMARY KEY (folderId));
CREATE TABLE HTS_HUMANROLE (humanRoleId VARCHAR(50) NOT NULL, roleName VARCHAR(50) NOT NULL, taskId VARCHAR(50), userId VARCHAR(50), PRIMARY KEY (humanRoleId));
CREATE TABLE HTS_IMPORT (importId VARCHAR(50) NOT NULL, importType VARCHAR(250) NOT NULL, location VARCHAR(250) NOT NULL, namespace VARCHAR(250), PRIMARY KEY (importId));
CREATE TABLE HTS_LOCAL_NOTIFICATION (localNotificationId VARCHAR(50) NOT NULL, localNotification VARCHAR(250), priorityExpression VARCHAR(250), PRIMARY KEY (localNotificationId));
CREATE TABLE HTS_L_DEADLINE_ESCALATION (deadlineId VARCHAR(50), escalationId VARCHAR(50));
CREATE TABLE HTS_L_ESCALATION_ESCHUMANROLES (escalationId VARCHAR(50), escalationHumanRolesId VARCHAR(50));
CREATE TABLE HTS_L_LOCNOTE_ESCHUMANROLES (escalationId VARCHAR(50), escalationHumanRolesId VARCHAR(50));
CREATE TABLE HTS_L_LOCNOTE_ESCTOPARTS (localNotificationId VARCHAR(50), toPartId VARCHAR(50));
CREATE TABLE HTS_L_POST_TASKS (postId VARCHAR(50), taskId VARCHAR(50));
CREATE TABLE HTS_L_PRESEL_PRESDESC (presentationElementsId VARCHAR(50), presentationDescriptionId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_DEADLINES (taskClassId VARCHAR(50), deadlineId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_ESCALATION (taskClassId VARCHAR(50), escalationId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_IMPORT (taskClassId VARCHAR(50), importId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PRESELDESC (taskClassId VARCHAR(50), presentationElementsDescId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PRESELNAME (taskClassId VARCHAR(50), presentationElementsNameId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PRESELPARAM (taskClassId VARCHAR(50), presentationElementsParamsId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PRESELSUBJ (taskClassId VARCHAR(50), presentationElementsSubjId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PROPERTY (taskClassId VARCHAR(50), propertyId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_PURGECNSTR (taskClassId VARCHAR(50), purgeConstraintId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_RENDERING (taskClassId VARCHAR(50), renderingId VARCHAR(50));
CREATE TABLE HTS_L_TASKCLASS_SUBSCRIBE (taskClassId VARCHAR(50), taskSubscriptionId VARCHAR(50));
CREATE TABLE HTS_L_TASKUSER_DISPLAYNAME (userId VARCHAR(50), displayNameId VARCHAR(50));
CREATE TABLE HTS_L_TASK_PRESEL (taskId VARCHAR(50), presentationElementsId VARCHAR(50));
CREATE TABLE HTS_L_TASK_READBY (taskId VARCHAR(50), taskReadById VARCHAR(50));
CREATE TABLE HTS_L_TASK_TASK (taskId VARCHAR(50), childTskId VARCHAR(50));
CREATE TABLE HTS_L_TASK_THREAD (taskId VARCHAR(50), threadId VARCHAR(50));
CREATE TABLE HTS_L_TASK_UNSUBSCRIBE (taskId VARCHAR(50), subscriptionId VARCHAR(50));
CREATE TABLE HTS_MESSAGE (messageId VARCHAR(50) NOT NULL, message VARCHAR(50), messageType VARCHAR(20) NOT NULL, name VARCHAR(50), taskId VARCHAR(50), PRIMARY KEY (messageId));
CREATE TABLE HTS_POST (postId VARCHAR(50) NOT NULL, createDate BIGINT NOT NULL, pagetext VARCHAR(50), sticky BIT, title VARCHAR(250), visible BIT NOT NULL, threadId VARCHAR(50), userId VARCHAR(50), parentId VARCHAR(50), PRIMARY KEY (postId));
CREATE TABLE HTS_PRESENTATIONDESCRIPTION (presentationDescriptionId VARCHAR(50) NOT NULL, contentType VARCHAR(250) NOT NULL, description VARCHAR(50) NOT NULL, PRIMARY KEY (presentationDescriptionId));
CREATE TABLE HTS_PRESENTATIONELEMENTS (presentationElementsId VARCHAR(50) NOT NULL, lang VARCHAR(50) NOT NULL, name VARCHAR(250), subject VARCHAR(250), PRIMARY KEY (presentationElementsId));
CREATE TABLE HTS_PRESENTATIONELEMENTS_DESC (presentationElementsDescId VARCHAR(50) NOT NULL, contentType VARCHAR(50) NOT NULL, description VARCHAR(250), lang VARCHAR(50) NOT NULL, PRIMARY KEY (presentationElementsDescId));
CREATE TABLE HTS_PRESENTATIONELEMENTS_NAME (presentationElementsNameId VARCHAR(50) NOT NULL, lang VARCHAR(50) NOT NULL, name VARCHAR(250) NOT NULL, PRIMARY KEY (presentationElementsNameId));
CREATE TABLE HTS_PRESENTATIONELEMENTS_SUBJ (presentationElementsSubjId VARCHAR(50) NOT NULL, lang VARCHAR(50) NOT NULL, subject VARCHAR(250) NOT NULL, PRIMARY KEY (presentationElementsSubjId));
CREATE TABLE HTS_PRESENTELEMENTS_PARAMS (presentationElementsParamsId VARCHAR(50) NOT NULL, expression VARCHAR(250) NOT NULL, name VARCHAR(50) NOT NULL, type VARCHAR(50) NOT NULL, PRIMARY KEY (presentationElementsParamsId));
CREATE TABLE HTS_RENDERING (renderingId VARCHAR(50) NOT NULL, renderUri VARCHAR(250), renderValue VARCHAR(50), type VARCHAR(50) NOT NULL, typeNamespace VARCHAR(250), typePrefix VARCHAR(50), PRIMARY KEY (renderingId));
CREATE TABLE HTS_TASK (taskId VARCHAR(50) NOT NULL, activationTime BIGINT, completedOn BIGINT, createdOn BIGINT NOT NULL, defaultLocale VARCHAR(10) NOT NULL, expirationTime BIGINT, name VARCHAR(250), operation VARCHAR(250) NOT NULL, priority INTEGER, isSkipable BIT NOT NULL, status VARCHAR(250) NOT NULL, taskClassVersion VARCHAR(10) NOT NULL, taskType VARCHAR(250) NOT NULL, timezoneId VARCHAR(50), modCount INTEGER, callbackId VARCHAR(50), actualOwner VARCHAR(50), createdBy VARCHAR(50), taskInitiator VARCHAR(50), PRIMARY KEY (taskId));
CREATE TABLE HTS_TASKCLASS (taskClassId VARCHAR(50) NOT NULL, activationTime VARCHAR(125), delegationType VARCHAR(50), expirationTime VARCHAR(125), folders VARCHAR(50), name VARCHAR(250), operation VARCHAR(250) NOT NULL, outcome VARCHAR(250), paramsExpressionLanguage VARCHAR(50) NOT NULL, peopleAssignments VARCHAR(50), portType VARCHAR(250) NOT NULL, priority VARCHAR(125), responseOperation VARCHAR(250), responsePortType VARCHAR(250), taskIconPath VARCHAR(125), taskType VARCHAR(250) NOT NULL, versionId VARCHAR(3), PRIMARY KEY (taskClassId));
CREATE TABLE HTS_TASKCLASS_PROPERTY (propertyId VARCHAR(50) NOT NULL, name VARCHAR(250), value VARCHAR(250), PRIMARY KEY (propertyId));
CREATE TABLE HTS_TASKSUBSCRIPTION (taskSubscriptionId VARCHAR(50) NOT NULL, subscriptionExpression VARCHAR(250), subscriptionType VARCHAR(250), PRIMARY KEY (taskSubscriptionId));
CREATE TABLE HTS_TASKUSER (userId VARCHAR(50) NOT NULL, isGroup BIT, logicalPeoplegroup VARCHAR(10) NOT NULL, logicalPeoplegroupRef VARCHAR(250) NOT NULL, userToken VARCHAR(250), PRIMARY KEY (userId));
CREATE TABLE HTS_TASK_PURGE_CONSTRAINT (purgeConstraintId VARCHAR(50) NOT NULL, purgeConstraintClass VARCHAR(250), purgeConstraintValue VARCHAR(50), PRIMARY KEY (purgeConstraintId));
CREATE TABLE HTS_TASK_READ_BY (taskReadById VARCHAR(50) NOT NULL, taskReadLevel INTEGER NOT NULL, userId VARCHAR(50), PRIMARY KEY (taskReadById));
CREATE TABLE HTS_THREAD (threadId VARCHAR(50) NOT NULL, createDate BIGINT NOT NULL, isOpen BIT NOT NULL, replycount INTEGER NOT NULL, title VARCHAR(250), type VARCHAR(50), visible BIT NOT NULL, PRIMARY KEY (threadId));
CREATE TABLE HTS_UNSUBSCRIPTION (subscriptionId VARCHAR(50) NOT NULL, event VARCHAR(250) NOT NULL, subscriptionState VARCHAR(250) NOT NULL, userId VARCHAR(50), PRIMARY KEY (subscriptionId));
CREATE TABLE HTS_USER_DISPLAY_NAME (displayNameId VARCHAR(50) NOT NULL, dateAdded BIGINT, locale VARCHAR(50), name VARCHAR(250), PRIMARY KEY (displayNameId));
CREATE TABLE HTS_VERSION (versionId VARCHAR(50) NOT NULL, dbVersionId INTEGER NOT NULL, PRIMARY KEY (versionId));



-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE INDEX I_HTS_PRM_HTSCALLBACK ON HTS_CALLBACKPARAM (callbackId);
CREATE INDEX I_HTS_TNC_HTSTASK ON HTS_DEADLINE_INSTANCE (taskId);
CREATE INDEX I_HTS_LTN_HTSLOCALNOTIFICATION ON HTS_ESCALATION (localNotificationId);
CREATE INDEX I_HTS_RLS_HTSTASKUSER ON HTS_ESCALATION_HUMANROLES (userId);
CREATE INDEX I_HTS_LDR_FOLDERNAME ON HTS_FOLDER (folderName);
CREATE INDEX I_HTS_LDR_HTSTASK ON HTS_FOLDER (taskId);
CREATE INDEX I_HTS_LDR_HTSTASKUSER ON HTS_FOLDER (userId);
CREATE INDEX I_HTS_NRL_HTSTASK ON HTS_HUMANROLE (taskId);
CREATE INDEX I_HTS_NRL_HTSTASKUSER ON HTS_HUMANROLE (userId);
CREATE INDEX I_HTS_LTN_DEADLINEID ON HTS_L_DEADLINE_ESCALATION (deadlineId);
CREATE INDEX I_HTS_LTN_ELEMENT ON HTS_L_DEADLINE_ESCALATION (escalationId);
CREATE INDEX I_HTS_RLS_ELEMENT ON HTS_L_ESCALATION_ESCHUMANROLES (escalationHumanRolesId);
CREATE INDEX I_HTS_RLS_ESCALATIONID ON HTS_L_ESCALATION_ESCHUMANROLES (escalationId);
CREATE INDEX I_HTS_RLS_ELEMENT1 ON HTS_L_LOCNOTE_ESCHUMANROLES (escalationHumanRolesId);
CREATE INDEX I_HTS_RLS_ESCALATIONID1 ON HTS_L_LOCNOTE_ESCHUMANROLES (escalationId);
CREATE INDEX I_HTS_RTS_ELEMENT ON HTS_L_LOCNOTE_ESCTOPARTS (toPartId);
CREATE INDEX I_HTS_RTS_LOCALNOTIFICATIONID ON HTS_L_LOCNOTE_ESCTOPARTS (localNotificationId);
CREATE INDEX I_HTS_SKS_ELEMENT ON HTS_L_POST_TASKS (taskId);
CREATE INDEX I_HTS_SKS_POSTID ON HTS_L_POST_TASKS (postId);
CREATE INDEX I_HTS_DSC_ELEMENT ON HTS_L_PRESEL_PRESDESC (presentationDescriptionId);
CREATE INDEX I_HTS_DSC_PRESENTATIONELEMENTSID ON HTS_L_PRESEL_PRESDESC (presentationElementsId);
CREATE INDEX I_HTS_LNS_ELEMENT ON HTS_L_TASKCLASS_DEADLINES (deadlineId);
CREATE INDEX I_HTS_LNS_TASKCLASSID ON HTS_L_TASKCLASS_DEADLINES (taskClassId);
CREATE INDEX I_HTS_LTN_ELEMENT1 ON HTS_L_TASKCLASS_ESCALATION (escalationId);
CREATE INDEX I_HTS_LTN_TASKCLASSID ON HTS_L_TASKCLASS_ESCALATION (taskClassId);
CREATE INDEX I_HTS_PRT_ELEMENT ON HTS_L_TASKCLASS_IMPORT (importId);
CREATE INDEX I_HTS_PRT_TASKCLASSID ON HTS_L_TASKCLASS_IMPORT (taskClassId);
CREATE INDEX I_HTS_DSC_ELEMENT1 ON HTS_L_TASKCLASS_PRESELDESC (presentationElementsDescId);
CREATE INDEX I_HTS_DSC_TASKCLASSID ON HTS_L_TASKCLASS_PRESELDESC (taskClassId);
CREATE INDEX I_HTS_LNM_ELEMENT ON HTS_L_TASKCLASS_PRESELNAME (presentationElementsNameId);
CREATE INDEX I_HTS_LNM_TASKCLASSID ON HTS_L_TASKCLASS_PRESELNAME (taskClassId);
CREATE INDEX I_HTS_PRM_ELEMENT ON HTS_L_TASKCLASS_PRESELPARAM (presentationElementsParamsId);
CREATE INDEX I_HTS_PRM_TASKCLASSID ON HTS_L_TASKCLASS_PRESELPARAM (taskClassId);
CREATE INDEX I_HTS_SBJ_ELEMENT ON HTS_L_TASKCLASS_PRESELSUBJ (presentationElementsSubjId);
CREATE INDEX I_HTS_SBJ_TASKCLASSID ON HTS_L_TASKCLASS_PRESELSUBJ (taskClassId);
CREATE INDEX I_HTS_RTY_ELEMENT ON HTS_L_TASKCLASS_PROPERTY (propertyId);
CREATE INDEX I_HTS_RTY_TASKCLASSID ON HTS_L_TASKCLASS_PROPERTY (taskClassId);
CREATE INDEX I_HTS_STR_ELEMENT ON HTS_L_TASKCLASS_PURGECNSTR (purgeConstraintId);
CREATE INDEX I_HTS_STR_TASKCLASSID ON HTS_L_TASKCLASS_PURGECNSTR (taskClassId);
CREATE INDEX I_HTS_RNG_ELEMENT ON HTS_L_TASKCLASS_RENDERING (renderingId);
CREATE INDEX I_HTS_RNG_TASKCLASSID ON HTS_L_TASKCLASS_RENDERING (taskClassId);
CREATE INDEX I_HTS_CRB_ELEMENT1 ON HTS_L_TASKCLASS_SUBSCRIBE (taskSubscriptionId);
CREATE INDEX I_HTS_CRB_TASKCLASSID ON HTS_L_TASKCLASS_SUBSCRIBE (taskClassId);
CREATE INDEX I_HTS_YNM_ELEMENT ON HTS_L_TASKUSER_DISPLAYNAME (displayNameId);
CREATE INDEX I_HTS_YNM_USERID ON HTS_L_TASKUSER_DISPLAYNAME (userId);
CREATE INDEX I_HTS_RSL_ELEMENT ON HTS_L_TASK_PRESEL (presentationElementsId);
CREATE INDEX I_HTS_RSL_TASKID ON HTS_L_TASK_PRESEL (taskId);
CREATE INDEX I_HTS_DBY_ELEMENT ON HTS_L_TASK_READBY (taskReadById);
CREATE INDEX I_HTS_DBY_TASKID ON HTS_L_TASK_READBY (taskId);
CREATE INDEX I_HTS_TSK_ELEMENT ON HTS_L_TASK_TASK (childTskId);
CREATE INDEX I_HTS_TSK_TASKID ON HTS_L_TASK_TASK (taskId);
CREATE INDEX I_HTS_HRD_ELEMENT ON HTS_L_TASK_THREAD (threadId);
CREATE INDEX I_HTS_HRD_TASKID ON HTS_L_TASK_THREAD (taskId);
CREATE INDEX I_HTS_CRB_ELEMENT ON HTS_L_TASK_UNSUBSCRIBE (subscriptionId);
CREATE INDEX I_HTS_CRB_TASKID ON HTS_L_TASK_UNSUBSCRIBE (taskId);
CREATE INDEX I_HTS_SSG_TASK ON HTS_MESSAGE (taskId);
CREATE INDEX I_HTS_PST_HTSTASKUSER ON HTS_POST (userId);
CREATE INDEX I_HTS_PST_HTSTHREAD ON HTS_POST (threadId);
CREATE INDEX I_HTS_PST_PARENTHTSPOST ON HTS_POST (parentId);
CREATE INDEX I_HTS_TSK_ACTUALOWNER ON HTS_TASK (actualOwner);
CREATE INDEX I_HTS_TSK_CREATEDBY ON HTS_TASK (createdBy);
CREATE INDEX I_HTS_TSK_HTSCALLBACK ON HTS_TASK (callbackId);
CREATE INDEX I_HTS_TSK_TASKINITIATOR ON HTS_TASK (taskInitiator);
CREATE INDEX I_HTS__BY_USER ON HTS_TASK_READ_BY (userId);
CREATE INDEX I_HTS_PTN_USER ON HTS_UNSUBSCRIPTION (userId);


-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------




-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------




-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------






-------------------------------------------------------------------------------
--	END HTS_CREATE_Hsql.sql        *  DO NOT EDIT  *
-------------------------------------------------------------------------------