DeerTick

2010-10-04T10:18:00.000-07:00

-- ---------------------------------------------------------------------------

-- Schema generated Jun 10, 2010 10:26:39 PM by SQLGen, ver SQLGen 1.1.0, freemarker 2.3.15

-- ---------------------------------------------------------------------------

-- Schema for Oracle

-- ---------------------------------------------------------------------------

-- jiveCommunity: Container table for community data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCommunity (

communityID INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

displayName VARCHAR2(255 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

lft INTEGER NOT NULL,

rgt INTEGER NOT NULL,

localeCode VARCHAR2(32) NULL,

workflowID INTEGER NULL,

modDfltThreadVal INTEGER NOT NULL,

modDfltMsgVal INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveCommunity_pk PRIMARY KEY (communityID)

);

CREATE INDEX jvCmmnty_lft_idx ON jiveCommunity (lft);

CREATE INDEX jvCmmnty_rgt_idx ON jiveCommunity (rgt);

-- ---------------------------------------------------------------------------

-- jiveCommunityProp: Properties for communities

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCommunityProp (

communityID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jCPp_pk PRIMARY KEY (communityID,name)

);

ALTER TABLE jiveCommunityProp ADD CONSTRAINT jCP_cID_fk FOREIGN KEY (communityID) REFERENCES jiveCommunity INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveContainerAprvr: Container approvers.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContainerAprvr (

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

userID INTEGER NOT NULL,

CONSTRAINT jiveCAprvr_pk PRIMARY KEY (containerType,containerID,userID)

);

-- ---------------------------------------------------------------------------

-- jiveThread: Container table for forum thread data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveThread (

threadID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

rootMessageID INTEGER NOT NULL,

modValue INTEGER NOT NULL,

rewardPoints INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveThread_pk PRIMARY KEY (threadID)

);

CREATE INDEX jvThrd_ctrID_idx ON jiveThread (containerType,containerID);

CREATE INDEX jvThrd_mdVl_idx ON jiveThread (modValue);

CREATE INDEX jvThrd_cDate_idx ON jiveThread (creationDate ASC);

CREATE INDEX jvThrd_mDate_idx ON jiveThread (modificationDate DESC);

CREATE INDEX jvThrd_cDmdVl_dx ON jiveThread (containerType,containerID,modValue);

CREATE INDEX jvThrd_root_idx ON jiveThread (rootMessageID);

-- ---------------------------------------------------------------------------

-- jiveThreadProp: Properties for threads.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveThreadProp (

threadID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveThreadProp_pk PRIMARY KEY (threadID,name)

);

ALTER TABLE jiveThreadProp ADD CONSTRAINT jTP_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveMessage: Container data for forum message data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveMessage (

messageID INTEGER NOT NULL,

parentMessageID INTEGER NULL,

threadID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

userID INTEGER NULL,

subject VARCHAR2(255 char) NULL,

body CLOB NULL,

modValue INTEGER NOT NULL,

rewardPoints INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)

);

CREATE INDEX jvMssg_thrd_idx ON jiveMessage (threadID ASC);

CREATE INDEX jvMssg_usrID_idx ON jiveMessage (userID ASC);

CREATE INDEX jvMssg_mdVle_idx ON jiveMessage (modValue);

CREATE INDEX jvMssg_cDate_idx ON jiveMessage (creationDate ASC);

CREATE INDEX jvMssg_mDate_idx ON jiveMessage (modificationDate DESC);

CREATE INDEX jvMssg_prntID_idx ON jiveMessage (parentMessageID ASC);

CREATE INDEX jvMssg_cIDcTmD_idx ON jiveMessage (containerID,containerType,modificationDate);

ALTER TABLE jiveMessage ADD CONSTRAINT jM_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveMessageProp: Properties for messages.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveMessageProp (

messageID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveMessageProp_pk PRIMARY KEY (messageID,name)

);

ALTER TABLE jiveMessageProp ADD CONSTRAINT jMP_msgID_fk FOREIGN KEY (messageID) REFERENCES jiveMessage INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAnnounce: Container table for announcement data

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAnnounce (

announcementID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NULL,

userID INTEGER NOT NULL,

subject VARCHAR2(255 char) NOT NULL,

body CLOB NOT NULL,

startDate INTEGER NOT NULL,

endDate INTEGER NULL,

status INTEGER NOT NULL,

minorCreateEdit INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jiveAnnounce_pk PRIMARY KEY (announcementID)

);

CREATE INDEX jvAnnnc_sDate_idx ON jiveAnnounce (startDate);

CREATE INDEX jvAnnnc_eDate_idx ON jiveAnnounce (endDate);

CREATE INDEX jvAnnnc_cntnr_idx ON jiveAnnounce (objectType,objectID);

CREATE INDEX jvAnnnc_user_idx ON jiveAnnounce (userID);

-- ---------------------------------------------------------------------------

-- jiveAnnounceProp: Properties for announcements.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAnnounceProp (

announcementID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveAProp_pk PRIMARY KEY (announcementID,name)

);

ALTER TABLE jiveAnnounceProp ADD CONSTRAINT jAP_aID_fk FOREIGN KEY (announcementID) REFERENCES jiveAnnounce INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePMessage: Container table for private message data.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePMessage (

pMessageID INTEGER NOT NULL,

ownerID INTEGER NOT NULL,

senderID INTEGER NULL,

recipientID INTEGER NULL,

subject VARCHAR2(255 char) NULL,

body CLOB NULL,

readStatus INTEGER NOT NULL,

folderID INTEGER NOT NULL,

pMessageDate INTEGER NOT NULL,

status INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

sentDate INTEGER NULL,

CONSTRAINT jivePMessage_pk PRIMARY KEY (pMessageID)

);

CREATE INDEX jvPMssg_recpnt_idx ON jivePMessage (recipientID);

CREATE INDEX jvPMssg_owner_idx ON jivePMessage (ownerID);

-- ---------------------------------------------------------------------------

-- jivePMessageProp: Properties for private messages.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePMessageProp (

pMessageID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jivePMProp_pk PRIMARY KEY (pMessageID,name)

);

ALTER TABLE jivePMessageProp ADD CONSTRAINT jPMP_msgID_fk FOREIGN KEY (pMessageID) REFERENCES jivePMessage INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePMessageFldr: Private message folders.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePMessageFldr (

folderID INTEGER NOT NULL,

userID INTEGER NOT NULL,

name VARCHAR2(255) NULL,

CONSTRAINT jivePMFldr_pk PRIMARY KEY (folderID,userID)

);

-- ---------------------------------------------------------------------------

-- jiveUser: Container table for user data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUser (

userID INTEGER NOT NULL,

username VARCHAR2(100 char) UNIQUE NOT NULL,

passwordHash VARCHAR2(64) NOT NULL,

name VARCHAR2(100 char) NULL,

firstName VARCHAR2(100 char) NULL,

lastName VARCHAR2(100 char) NULL,

nameVisible INTEGER NOT NULL,

email VARCHAR2(100) NOT NULL,

emailVisible INTEGER NOT NULL,

userEnabled INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

lastLoggedIn INTEGER DEFAULT 0 NOT NULL,

lastProfileUpdate INTEGER DEFAULT 0 NOT NULL,

isExternal INTEGER DEFAULT 0 NOT NULL,

federated INTEGER DEFAULT 0 NOT NULL,

visible INTEGER DEFAULT 1 NOT NULL,

status INTEGER DEFAULT 6 NOT NULL,

CONSTRAINT jiveUser_pk PRIMARY KEY (userID)

);

CREATE INDEX jiveUser_cDate_idx ON jiveUser (creationDate ASC);

CREATE INDEX jiveUser_eEx_idx ON jiveUser (visible,userEnabled,isExternal);

-- ---------------------------------------------------------------------------

-- jiveUserPerm: User permissions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserPerm (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

userID INTEGER NOT NULL,

permissionType INTEGER NOT NULL,

permission INTEGER NOT NULL

);

CREATE INDEX jvUsrPrm_objct_idx ON jiveUserPerm (objectType,objectID);

CREATE INDEX jvUsrPrm_usrID_idx ON jiveUserPerm (userID ASC);

-- ---------------------------------------------------------------------------

-- jiveUserProp: Properties for users.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserProp (

userID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveUserProp_pk PRIMARY KEY (userID,name)

);

CREATE INDEX jvUsrProp_uid_idx ON jiveUserProp (userID);

-- ---------------------------------------------------------------------------

-- jiveGroup: Container table for group data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGroup (

groupID INTEGER NOT NULL,

name VARCHAR2(100 char) UNIQUE NOT NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveGroup_pk PRIMARY KEY (groupID)

);

CREATE INDEX jvGrp_cDate_idx ON jiveGroup (creationDate ASC);

-- ---------------------------------------------------------------------------

-- jiveGroupPerm: Group permissions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGroupPerm (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

groupID INTEGER NOT NULL,

permissionType INTEGER NOT NULL,

permission INTEGER NOT NULL

);

CREATE INDEX jvGrpPrm_objct_idx ON jiveGroupPerm (objectType,objectID);

CREATE INDEX jvGrpPrm_grpID_idx ON jiveGroupPerm (groupID ASC);

-- ---------------------------------------------------------------------------

-- jiveGroupProp: Properties for groups.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGroupProp (

groupID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupID,name)

);

-- ---------------------------------------------------------------------------

-- jiveGroupUser: Container table for specifying the users in a group.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGroupUser (

groupID INTEGER NOT NULL,

userID INTEGER NOT NULL,

administrator INTEGER NOT NULL,

CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupID,userID,administrator)

);

CREATE INDEX jGU_userID_idx ON jiveGroupUser (userID ASC);

-- ---------------------------------------------------------------------------

-- jiveGroupUserHist: History table for specifying the users in a group.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGroupUserHist (

groupUserID INTEGER NOT NULL,

groupID INTEGER NOT NULL,

userID INTEGER NOT NULL,

administrator INTEGER NOT NULL,

retiredDate INTEGER NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jvGroupUserHst_pk PRIMARY KEY (groupUserID)

);

CREATE INDEX jGUHst_userID_idx ON jiveGroupUserHist (userID ASC);

-- ---------------------------------------------------------------------------

-- jiveID: ID Sequence table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveID (

idType INTEGER NOT NULL,

id INTEGER NOT NULL,

CONSTRAINT jiveID_pk PRIMARY KEY (idType)

);

-- ---------------------------------------------------------------------------

-- jiveProperty: System wide Jive properties.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProperty (

name VARCHAR2(255 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveProperty_pk PRIMARY KEY (name)

);

-- ---------------------------------------------------------------------------

-- jiveLocalizedProp: Localized system wide Jive properties.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLocalizedProp (

name VARCHAR2(100 char) NOT NULL,

propValue CLOB NOT NULL,

locale VARCHAR2(100) NOT NULL,

CONSTRAINT jiveLProp_pk PRIMARY KEY (name,locale)

);

-- ---------------------------------------------------------------------------

-- jiveModeration: Audit table for moderation events.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveModeration (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

userID INTEGER NULL,

modDate INTEGER NOT NULL,

modValue INTEGER NOT NULL

);

CREATE INDEX jvMdrtn_objtID_idx ON jiveModeration (objectID ASC);

CREATE INDEX jvMdrtn_objtTyp_dx ON jiveModeration (objectType);

CREATE INDEX jvMdrtn_userID_idx ON jiveModeration (userID ASC);

-- ---------------------------------------------------------------------------

-- jiveWatch: Container table for user watch data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWatch (

userID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

watchType INTEGER NOT NULL,

expirable INTEGER NOT NULL,

CONSTRAINT jiveWatch_pk PRIMARY KEY (userID,objectID,objectType,watchType)

);

CREATE INDEX jvWtch_usrID_idx ON jiveWatch (userID);

CREATE INDEX jvWtch_objtID_idx ON jiveWatch (objectID);

CREATE INDEX jvWtch_objtTyp_idx ON jiveWatch (objectType);

CREATE INDEX jvWtch_cmb_idx ON jiveWatch (objectType,objectID,watchType);

-- ---------------------------------------------------------------------------

-- jiveAttachment: Attachment definition table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachment (

attachmentID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NULL,

documentID VARCHAR2(255 char) NULL,

fileName VARCHAR2(255 char) NOT NULL,

fileSize INTEGER NOT NULL,

contentType VARCHAR2(100) NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveAttachment_pk PRIMARY KEY (attachmentID)

);

CREATE INDEX jvAtthmnt_objt_idx ON jiveAttachment (objectType,objectID);

CREATE INDEX jvAtthmnt_dID_idx ON jiveAttachment (documentID);

-- ---------------------------------------------------------------------------

-- jiveAttachmentProp: Properties for attachments.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachmentProp (

attachmentID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveAttPrp_pk PRIMARY KEY (attachmentID,name)

);

ALTER TABLE jiveAttachmentProp ADD CONSTRAINT jAttPrp_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAttachmentDL: Statistical data table for attachment downloads.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachmentDL (

attachmentID INTEGER NOT NULL,

downloadDate INTEGER NOT NULL,

downloadComplete INTEGER NOT NULL

);

CREATE INDEX jAttDL_aID_idx ON jiveAttachmentDL (attachmentID);

-- ---------------------------------------------------------------------------

-- jiveUserRoster: User Rosters.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserRoster (

userID INTEGER NOT NULL,

subUserID INTEGER NOT NULL,

CONSTRAINT jiveUserRoster_pk PRIMARY KEY (userID,subUserID)

);

-- ---------------------------------------------------------------------------

-- jiveReadTracker: Read tracking data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveReadTracker (

userID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

readDate INTEGER NOT NULL,

CONSTRAINT jiveReadTracker_pk PRIMARY KEY (userID,objectType,objectID)

);

-- ---------------------------------------------------------------------------

-- jiveRatingType: Rating types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveRatingType (

score INTEGER NOT NULL,

description VARCHAR2(1000) NOT NULL,

CONSTRAINT jiveRatingType_pk PRIMARY KEY (score)

);

-- ---------------------------------------------------------------------------

-- jiveRating: Rating data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveRating (

objectID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

userID INTEGER NULL,

score INTEGER NOT NULL,

creationDate INTEGER NULL,

modificationDate INTEGER NULL

);

CREATE INDEX jvRtng_usrID_idx ON jiveRating (userID);

CREATE INDEX jvRtng_ID_Type_idx ON jiveRating (objectID,objectType);

ALTER TABLE jiveRating ADD CONSTRAINT jiveRtg_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePoll: Poll data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePoll (

pollID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

userID INTEGER NULL,

name VARCHAR2(255 char) NOT NULL,

description CLOB NULL,

pollMode INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

startDate INTEGER NOT NULL,

endDate INTEGER NOT NULL,

expireDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jivePoll_pk PRIMARY KEY (pollID)

);

CREATE INDEX jvPll_ID_Type_idx ON jivePoll (objectID,objectType);

CREATE INDEX jivePoll_cDate_idx ON jivePoll (creationDate);

CREATE INDEX jivePoll_mDate_idx ON jivePoll (modificationDate DESC);

CREATE INDEX jivePoll_sDate_idx ON jivePoll (startDate DESC);

CREATE INDEX jivePoll_eDate_idx ON jivePoll (endDate DESC);

-- ---------------------------------------------------------------------------

-- jivePollOption: Poll options table.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePollOption (

optionID INTEGER NOT NULL,

pollID INTEGER NOT NULL,

optionIndex INTEGER NOT NULL,

optionText VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jivePollOpt_pk PRIMARY KEY (optionID)

);

ALTER TABLE jivePollOption ADD CONSTRAINT jivePllOp_pID_fk FOREIGN KEY (pollID) REFERENCES jivePoll INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePollVote: Poll vote data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePollVote (

optionID INTEGER NOT NULL,

userID INTEGER NULL,

guestID VARCHAR2(255 char) NULL,

voteDate INTEGER NOT NULL

);

ALTER TABLE jivePollVote ADD CONSTRAINT jivePllVt_pID_fk FOREIGN KEY (optionID) REFERENCES jivePollOption INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveViewCount: Object view counts.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveViewCount (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

viewCount INTEGER DEFAULT 0,

CONSTRAINT jiveViewCount_pk PRIMARY KEY (objectType,objectID)

);

-- ---------------------------------------------------------------------------

-- jiveStatusLevel: Status levels.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveStatusLevel (

statusLevelID INTEGER NOT NULL,

name VARCHAR2(255 char) UNIQUE NOT NULL,

description VARCHAR2(1000 char) NULL,

imagePath VARCHAR2(255 char) NOT NULL,

largeImagePath VARCHAR2(255 char) NULL,

minPoints INTEGER NOT NULL,

maxPoints INTEGER NOT NULL,

groupID INTEGER NOT NULL,

CONSTRAINT jiveStatusLevel_pk PRIMARY KEY (statusLevelID)

);

-- ---------------------------------------------------------------------------

-- jiveAvatar: Avatar data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAvatar (

avatarID INTEGER NOT NULL,

modValue INTEGER NOT NULL,

ownerID INTEGER NULL,

CONSTRAINT jiveAvatar_pk PRIMARY KEY (avatarID)

);

CREATE INDEX jvAvtr_ownerID_idx ON jiveAvatar (ownerID);

-- ---------------------------------------------------------------------------

-- jiveAvatarProp: Properties for avatars.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAvatarProp (

avatarID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveAvatarProp_pk PRIMARY KEY (avatarID,name)

);

ALTER TABLE jiveAvatarProp ADD CONSTRAINT jAtrPrp_aID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAvatarUser: Avatar/user association table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAvatarUser (

avatarID INTEGER NOT NULL,

userID INTEGER NOT NULL,

CONSTRAINT jiveAvatarUser_pk PRIMARY KEY (avatarID,userID)

);

CREATE INDEX jAU_uID_idx ON jiveAvatarUser (userID);

ALTER TABLE jiveAvatarUser ADD CONSTRAINT jAtrUsr_aID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveQuestion: Question data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveQuestion (

threadID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

resolutionDate INTEGER NULL,

resolutionState INTEGER NOT NULL,

CONSTRAINT jiveQuestion_pk PRIMARY KEY (threadID)

);

CREATE INDEX jvQstn_cdate_idx ON jiveQuestion (creationDate);

CREATE INDEX jvQstn_rdate_idx ON jiveQuestion (resolutionDate);

CREATE INDEX jvQstn_state_idx ON jiveQuestion (resolutionState);

CREATE INDEX jvQstn_uID_st_idx ON jiveQuestion (userID,resolutionState);

ALTER TABLE jiveQuestion ADD CONSTRAINT jiveQuest_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveQuestionProp: Properties for questions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveQuestionProp (

threadID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveQProp_pk PRIMARY KEY (threadID,name)

);

ALTER TABLE jiveQuestionProp ADD CONSTRAINT jiveQuestP_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAnswer: Answer data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAnswer (

threadID INTEGER NOT NULL,

messageID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

userID INTEGER NULL,

helpfulAnswer INTEGER NOT NULL,

correctAnswer INTEGER NOT NULL,

CONSTRAINT jiveAnswer_pk PRIMARY KEY (threadID,messageID)

);

CREATE INDEX jvAnswr_usr_idx ON jiveAnswer (userID);

ALTER TABLE jiveAnswer ADD CONSTRAINT jiveAnswer_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveAnswer ADD CONSTRAINT jiveAnswer_mID_fk FOREIGN KEY (messageID) REFERENCES jiveMessage INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveThemeMap: Mapping table for theme mapping data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveThemeMap (

theme VARCHAR2(32 char) NOT NULL,

mapType VARCHAR2(16) NOT NULL,

numValue INTEGER NULL,

stringValue VARCHAR2(100 char) NULL

);

-- ---------------------------------------------------------------------------

-- jiveGateway: Gateway data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGateway (

gatewayID INTEGER NOT NULL,

communityID INTEGER NOT NULL,

gatewayType VARCHAR2(10 char) NOT NULL,

importEnabled INTEGER NOT NULL,

exportEnabled INTEGER NOT NULL,

disableCutOffDate INTEGER NULL,

CONSTRAINT jiveGateway_pk PRIMARY KEY (gatewayID)

);

-- ---------------------------------------------------------------------------

-- jiveGatewayProp: Properties for gateways.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGatewayProp (

gatewayID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveGatewayProp_pk PRIMARY KEY (gatewayID,name)

);

ALTER TABLE jiveGatewayProp ADD CONSTRAINT jiveGP_gID_fk FOREIGN KEY (gatewayID) REFERENCES jiveGateway INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBan: Ban data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBan (

banID INTEGER NOT NULL,

banType INTEGER NOT NULL,

bannedUserID INTEGER NULL,

ipAddress VARCHAR2(15 char) NULL,

adminID INTEGER NOT NULL,

banLevel INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

expirationDate INTEGER NULL,

comments VARCHAR2(50 char) NULL,

CONSTRAINT jiveBan_pk PRIMARY KEY (banID)

);

-- ---------------------------------------------------------------------------

-- jiveAbuse: Abuse data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAbuse (

abuseID INTEGER NOT NULL,

abuseType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

objecttype INTEGER NOT NULL,

userID INTEGER NOT NULL,

reportDate INTEGER NOT NULL,

comments VARCHAR2(255 char) NULL,

resolved INTEGER NULL,

CONSTRAINT jiveAbuse_pk PRIMARY KEY (abuseID)

);

-- ---------------------------------------------------------------------------

-- jiveWatchSettings: Watch settings data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWatchSettings (

userID INTEGER NOT NULL,

receiveHtmlEmails INTEGER DEFAULT 1 NOT NULL,

receiveEmails INTEGER NOT NULL,

batchFrequency INTEGER NOT NULL,

lastBatchEmail INTEGER NULL,

includePostContent INTEGER DEFAULT 1 NOT NULL,

CONSTRAINT jiveWSttngs_pk PRIMARY KEY (userID)

);

-- ---------------------------------------------------------------------------

-- jiveTypeWatchConf: holds user content type auto watch preferences

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTypeWatchConf (

userid INTEGER NOT NULL,

objecttype INTEGER NOT NULL,

watchType VARCHAR2(100) NOT NULL,

enabled INTEGER NOT NULL

);

-- ---------------------------------------------------------------------------

-- jiveProfileSecLvl: Profile field security level definitions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProfileSecLvl (

levelID INTEGER NOT NULL,

nameKey VARCHAR2(128) NOT NULL,

strategyName VARCHAR2(128) NOT NULL,

enabled INTEGER DEFAULT 1 NOT NULL,

CONSTRAINT jivePSecLvl_pk PRIMARY KEY (levelID)

);

-- ---------------------------------------------------------------------------

-- jiveProfileField: Profile field data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProfileField (

fieldID INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

isRequired INTEGER NOT NULL,

isFilterable INTEGER NOT NULL,

isSearchable INTEGER NOT NULL,

isVisibleToUsers INTEGER NOT NULL,

isVisibleToGuests INTEGER NOT NULL,

isEditable INTEGER NOT NULL,

fieldType INTEGER NOT NULL,

idx INTEGER NOT NULL,

isDefault INTEGER NOT NULL,

extManaged INTEGER NOT NULL,

extMapping VARCHAR2(2048) NULL,

isList INTEGER DEFAULT 0 NOT NULL,

regIdx INTEGER DEFAULT -1 NOT NULL,

defaultSecLvlID INTEGER DEFAULT 1001 NOT NULL,

CONSTRAINT jivePField_pk PRIMARY KEY (fieldID)

);

CREATE UNIQUE INDEX jivePField_nm_idx ON jiveProfileField (name);

ALTER TABLE jiveProfileField ADD CONSTRAINT jivePField_lID_fk FOREIGN KEY (defaultSecLvlID) REFERENCES jiveProfileSecLvl INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveProfileFldOpt: Profile field option data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProfileFldOpt (

fieldID INTEGER NOT NULL,

fieldValue VARCHAR2(255 char) NOT NULL,

idx INTEGER NOT NULL,

defaultOption INTEGER NOT NULL

);

ALTER TABLE jiveProfileFldOpt ADD CONSTRAINT jivePFO_fID_fk FOREIGN KEY (fieldID) REFERENCES jiveProfileField INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveProfileFldSec: Profile field security level data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProfileFldSec (

fieldID INTEGER NOT NULL,

levelID INTEGER NOT NULL

);

CREATE UNIQUE INDEX jivePFSL_uk_idx ON jiveProfileFldSec (fieldID,levelID);

ALTER TABLE jiveProfileFldSec ADD CONSTRAINT jivePFSL_fID_fk FOREIGN KEY (fieldID) REFERENCES jiveProfileField INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveProfileFldSec ADD CONSTRAINT jivePFSL_lID_fk FOREIGN KEY (levelID) REFERENCES jiveProfileSecLvl INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUserProfile: User profile data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserProfile (

userID INTEGER NOT NULL,

fieldID INTEGER NOT NULL,

levelID INTEGER NULL,

value VARCHAR2(3500 char) NULL,

primaryVal INTEGER NOT NULL

);

CREATE INDEX jUP_uID_fID_idx ON jiveUserProfile (userID,fieldID);

ALTER TABLE jiveUserProfile ADD CONSTRAINT jiveUP_fID_fk FOREIGN KEY (fieldID) REFERENCES jiveProfileField INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUserProfile ADD CONSTRAINT jiveUP_lID_fk FOREIGN KEY (levelID) REFERENCES jiveProfileSecLvl INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveEmailToken: User email token data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveEmailToken (

userID INTEGER NOT NULL,

tokenValue INTEGER NOT NULL,

tokenDate INTEGER NOT NULL,

CONSTRAINT jiveEmailToken_pk PRIMARY KEY (userID,tokenValue,tokenDate)

);

-- ---------------------------------------------------------------------------

-- jiveDraft: Draft data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDraft (

draftID INTEGER NOT NULL,

draftType INTEGER NOT NULL,

objectType INTEGER NULL,

objectID INTEGER NOT NULL,

userID INTEGER NULL,

subject VARCHAR2(255 char) NULL,

body CLOB NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveDraft_pk PRIMARY KEY (draftID)

);

CREATE INDEX jvDrft_usrID_idx ON jiveDraft (userID);

CREATE INDEX jvDrft_mDate_idx ON jiveDraft (modificationDate);

CREATE INDEX jvDrft_objtD_idx ON jiveDraft (objectID);

-- ---------------------------------------------------------------------------

-- jiveDraftProp: Properties for drafts.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDraftProp (

draftID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveDraftProp_pk PRIMARY KEY (draftID,name)

);

ALTER TABLE jiveDraftProp ADD CONSTRAINT jiveDP_dID_fk FOREIGN KEY (draftID) REFERENCES jiveDraft INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveLinkObject: Link data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLinkObject (

linkKey VARCHAR2(255 char) NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

CONSTRAINT jiveLinkObject_pk PRIMARY KEY (linkKey)

);

-- ---------------------------------------------------------------------------

-- jiveVersion: Version data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveVersion (

name VARCHAR2(50 char) NOT NULL,

version INTEGER NOT NULL,

CONSTRAINT jiveVersion_pk PRIMARY KEY (name)

);

-- ---------------------------------------------------------------------------

-- jiveBlog: Blog data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBlog (

blogID INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

displayName VARCHAR2(255 char) NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

cmModeration INTEGER NULL,

tbModeration INTEGER NULL,

cmNotification INTEGER NULL,

tbNotification INTEGER NULL,

cmAuthentication INTEGER NULL,

feedEnabled INTEGER NULL,

feedFullContent INTEGER NULL,

pingOverride INTEGER NULL,

pingServices CLOB NULL,

containerType INTEGER DEFAULT 17 NOT NULL,

containerID INTEGER DEFAULT -2 NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveBlog_pk PRIMARY KEY (blogID)

);

CREATE INDEX jiveBlg_d_idx ON jiveBlog (displayName);

CREATE INDEX jiveBlg_ctID_idx ON jiveBlog (containerID,containerType);

-- ---------------------------------------------------------------------------

-- jiveContainerBlog: Blog/container association table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContainerBlog (

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

blogID INTEGER NOT NULL,

CONSTRAINT jiveCBlg_pk PRIMARY KEY (containerType,containerID,blogID)

);

ALTER TABLE jiveContainerBlog ADD CONSTRAINT jiveCBlg_bID_fk FOREIGN KEY (blogID) REFERENCES jiveBlog INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBlogProp: Properties for blogs.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBlogProp (

blogID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveBlogProp_pk PRIMARY KEY (blogID,name)

);

ALTER TABLE jiveBlogProp ADD CONSTRAINT jiveBPrp_bID_fk FOREIGN KEY (blogID) REFERENCES jiveBlog INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBlogPost: Blog post data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBlogPost (

blogpostID INTEGER NOT NULL,

blogID INTEGER NOT NULL,

userID INTEGER NOT NULL,

subject VARCHAR2(255 char) NULL,

permalink VARCHAR2(255 char) NULL,

body CLOB NULL,

status INTEGER NOT NULL,

commentStatus INTEGER NOT NULL,

trackbacksEnabled INTEGER NOT NULL,

publishDate INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

minorEdit INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jiveBlgPst_pk PRIMARY KEY (blogpostID)

);

CREATE INDEX jiveBlgPst_b_idx ON jiveBlogPost (blogID);

CREATE INDEX jiveBlgPst_u_idx ON jiveBlogPost (userID);

CREATE INDEX jiveBlgPst_p_idx ON jiveBlogPost (permalink);

CREATE INDEX jiveBlgPst_c_idx ON jiveBlogPost (creationDate);

CREATE INDEX jiveBlgPst_m_idx ON jiveBlogPost (modificationDate);

ALTER TABLE jiveBlogPost ADD CONSTRAINT jiveBPst_bID_fk FOREIGN KEY (blogID) REFERENCES jiveBlog INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBlogPostProp: Properties for blog posts.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBlogPostProp (

blogpostID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveBPPrp_pk PRIMARY KEY (blogpostID,name)

);

ALTER TABLE jiveBlogPostProp ADD CONSTRAINT jiveBPP_bpID_fk FOREIGN KEY (blogpostID) REFERENCES jiveBlogPost INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveTrackback: Trackback data table for blogs.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTrackback (

trackbackID INTEGER NOT NULL,

title VARCHAR2(100 char) NULL,

excerpt CLOB NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

parentObjectType INTEGER NULL,

parentObjectID INTEGER NULL,

url VARCHAR2(255 char) NULL,

senderName VARCHAR2(100 char) NULL,

senderObjectType INTEGER DEFAULT -1 NOT NULL,

senderObjectID INTEGER DEFAULT -1 NOT NULL,

ip VARCHAR2(15 char) NULL,

moderated INTEGER NOT NULL,

status INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveTrkbk_pk PRIMARY KEY (trackbackID)

);

CREATE INDEX jiveTrkbk_p_idx ON jiveTrackback (parentObjectID,parentObjectType);

CREATE INDEX jiveTrkbk_o_idx ON jiveTrackback (objectID,objectType);

CREATE INDEX jiveTrkbk_c_idx ON jiveTrackback (creationDate);

CREATE INDEX jiveTrkbk_m_idx ON jiveTrackback (modificationDate);

-- ---------------------------------------------------------------------------

-- jiveTrackbackProp: Properties for trackbacks.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTrackbackProp (

trackbackID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveTProp_pk PRIMARY KEY (trackbackID,name)

);

ALTER TABLE jiveTrackbackProp ADD CONSTRAINT jiveTP_tID_fk FOREIGN KEY (trackbackID) REFERENCES jiveTrackback INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveTag: Tag data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTag (

tagID INTEGER NOT NULL,

tagname VARCHAR2(100 char) NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveTag_pk PRIMARY KEY (tagID)

);

CREATE INDEX jiveTag_t_idx ON jiveTag (tagname);

CREATE INDEX jiveTag_c_idx ON jiveTag (creationDate);

-- ---------------------------------------------------------------------------

-- jiveObjectTag: Tag/object association table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjectTag (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

tagID INTEGER NOT NULL,

creationDate INTEGER NOT NULL

);

CREATE INDEX jiveObjectTag_idx ON jiveObjectTag (objectID,tagID,objectType);

CREATE INDEX jvObjTg_typtag_idx ON jiveObjectTag (objectType,tagID);

CREATE INDEX jvObjTg_type_idx ON jiveObjectTag (objectType);

-- ---------------------------------------------------------------------------

-- jiveCommunityTag: Tag/object/user association table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCommunityTag (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

userID INTEGER NOT NULL,

tagID INTEGER NOT NULL,

creationDate INTEGER NOT NULL

);

CREATE UNIQUE INDEX jCT_key_idx ON jiveCommunityTag (objectID,userID,tagID,objectType);

ALTER TABLE jiveCommunityTag ADD CONSTRAINT jCT_tID_fk FOREIGN KEY (tagID) REFERENCES jiveTag INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveTagCloud: Container/tag cloud association table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTagCloud (

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

taggableType INTEGER NOT NULL,

tagID INTEGER NOT NULL,

tagCount INTEGER NOT NULL,

CONSTRAINT jiveTagCloud_pk PRIMARY KEY (containerType,containerID,taggableType,tagID)

);

CREATE INDEX jvTgCld_cTcID_idx ON jiveTagCloud (containerType,containerID);

-- ---------------------------------------------------------------------------

-- jiveDataType: Datatype definition table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDataType (

dataTypeID INTEGER NOT NULL,

dataType VARCHAR2(20) UNIQUE NOT NULL,

CONSTRAINT jiveDataType_pk PRIMARY KEY (dataTypeID)

);

-- ---------------------------------------------------------------------------

-- jiveFieldType: Document Field types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFieldType (

fieldTypeID INTEGER NOT NULL,

name VARCHAR2(20 char) UNIQUE NOT NULL,

displayName VARCHAR2(100 char) UNIQUE NOT NULL,

description VARCHAR2(1000 char) NULL,

dataTypeID INTEGER NOT NULL,

formatter VARCHAR2(255) NULL,

validator VARCHAR2(255) NULL,

modifiable INTEGER NOT NULL,

CONSTRAINT jiveFieldType_pk PRIMARY KEY (fieldTypeID)

);

ALTER TABLE jiveFieldType ADD CONSTRAINT jFT_dtID_fk FOREIGN KEY (dataTypeID) REFERENCES jiveDataType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveFieldFrmtProp: Properties for field formatters.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFieldFrmtProp (

fieldTypeID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveFFPrp_pk PRIMARY KEY (fieldTypeID,name)

);

ALTER TABLE jiveFieldFrmtProp ADD CONSTRAINT jFFPrp_ftID_fk FOREIGN KEY (fieldTypeID) REFERENCES jiveFieldType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveFieldVldtrProp: Properties for field validators.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFieldVldtrProp (

fieldTypeID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jFVldtrPrp_pk PRIMARY KEY (fieldTypeID,name)

);

ALTER TABLE jiveFieldVldtrProp ADD CONSTRAINT jFVPrp_frID_fk FOREIGN KEY (fieldTypeID) REFERENCES jiveFieldType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveField: Field data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveField (

fieldID INTEGER NOT NULL,

fieldTypeID INTEGER NOT NULL,

name VARCHAR2(100 char) UNIQUE NOT NULL,

displayName VARCHAR2(100 char) UNIQUE NOT NULL,

description VARCHAR2(1000 char) NULL,

defValue VARCHAR2(255 char) NULL,

alterable INTEGER NOT NULL,

searchable INTEGER NOT NULL,

required INTEGER NOT NULL,

visible INTEGER NOT NULL,

filterable INTEGER NOT NULL,

maxSize INTEGER NOT NULL,

fRowVersion INTEGER NULL,

CONSTRAINT jiveField_pk PRIMARY KEY (fieldID)

);

ALTER TABLE jiveField ADD CONSTRAINT jiveField_fType_fk FOREIGN KEY (fieldTypeID) REFERENCES jiveFieldType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveFieldOption: Field option data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFieldOption (

optionID INTEGER NOT NULL,

fieldID INTEGER NOT NULL,

optionValue VARCHAR2(255 char) NOT NULL,

optionIndex INTEGER NOT NULL,

defaultOption INTEGER NOT NULL,

CONSTRAINT jiveFieldOption_pk PRIMARY KEY (optionID)

);

ALTER TABLE jiveFieldOption ADD CONSTRAINT jFO_fID_fk FOREIGN KEY (fieldID) REFERENCES jiveField INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePresenter: Presention information data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePresenter (

presenterID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

template CLOB NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jivePresenter_pk PRIMARY KEY (presenterID)

);

-- ---------------------------------------------------------------------------

-- jiveDocType: Document type data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocType (

typeID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

presenterID INTEGER NULL,

validator VARCHAR2(255 char) NULL,

typeIndex INTEGER NOT NULL,

CONSTRAINT jiveDocType_pk PRIMARY KEY (typeID)

);

-- ---------------------------------------------------------------------------

-- jiveDocTypeProp: Properties for document types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocTypeProp (

typeID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveDocTypeProp_pk PRIMARY KEY (typeID,name)

);

ALTER TABLE jiveDocTypeProp ADD CONSTRAINT jDTPrp_tID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveTypeVldtrProp: Properties for document type validators.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTypeVldtrProp (

typeID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveTVProp_pk PRIMARY KEY (typeID,name)

);

ALTER TABLE jiveTypeVldtrProp ADD CONSTRAINT jTVPrp_tID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocElementType: Document element types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocElementType (

elementTypeID INTEGER NOT NULL,

name VARCHAR2(20 char) UNIQUE NOT NULL,

CONSTRAINT jDET_pk PRIMARY KEY (elementTypeID)

);

-- ---------------------------------------------------------------------------

-- jiveDocTypeElement: Document type elements.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocTypeElement (

elementID INTEGER NOT NULL,

documentTypeID INTEGER NOT NULL,

elementTypeID INTEGER NOT NULL,

elementIndex INTEGER NOT NULL,

fRowVersion INTEGER NULL,

CONSTRAINT jDTE_pk PRIMARY KEY (elementID,documentTypeID)

);

ALTER TABLE jiveDocTypeElement ADD CONSTRAINT jDTE_dtID_fk FOREIGN KEY (documentTypeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveDocTypeElement ADD CONSTRAINT jDTE_etID_fk FOREIGN KEY (elementTypeID) REFERENCES jiveDocElementType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveLabelElement: Label elements.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLabelElement (

elementID INTEGER NOT NULL,

documentTypeID INTEGER NOT NULL,

value VARCHAR2(255 char) NULL,

CONSTRAINT jLE_pk PRIMARY KEY (elementID,documentTypeID)

);

-- ---------------------------------------------------------------------------

-- jiveSectionElement: Section elements.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSectionElement (

elementID INTEGER NOT NULL,

parentID INTEGER NOT NULL,

documentTypeID INTEGER NOT NULL,

elementTypeID INTEGER NOT NULL,

elementIndex INTEGER NOT NULL,

CONSTRAINT jSE_pk PRIMARY KEY (elementID,parentID,documentTypeID)

);

ALTER TABLE jiveSectionElement ADD CONSTRAINT jSE_pID_dtID_fk FOREIGN KEY (parentID,documentTypeID) REFERENCES jiveDocTypeElement (elementID,documentTypeID) INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocument: Document data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocument (

internalDocID INTEGER NOT NULL,

userID INTEGER NOT NULL,

documentID VARCHAR2(255 char) UNIQUE NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

typeID INTEGER NOT NULL,

editPolicy INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

expirationDate INTEGER NOT NULL,

recommended INTEGER NOT NULL,

meanRating INTEGER NOT NULL,

readCount INTEGER NOT NULL,

commentStatus INTEGER NOT NULL,

trackbacksEnabled INTEGER NOT NULL,

workflowID INTEGER NULL,

versionID INTEGER NOT NULL,

CONSTRAINT jiveDocument_pk PRIMARY KEY (internalDocID)

);

CREATE INDEX jvDcmnt_exprtn_idx ON jiveDocument (expirationDate);

CREATE INDEX jvDcmnt_cTctit_idx ON jiveDocument (containerType,containerID,internalDocID);

ALTER TABLE jiveDocument ADD CONSTRAINT jD_typeID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocumentProp: Properties for documents.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocumentProp (

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveDPrp_pk PRIMARY KEY (internalDocID,name,versionID)

);

ALTER TABLE jiveDocumentProp ADD CONSTRAINT jDPrp_ilDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveApprovalWrkflw: Workflow approval table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveApprovalWrkflw (

workflowID INTEGER NOT NULL,

objID INTEGER NOT NULL,

typeID INTEGER NOT NULL,

userID INTEGER NOT NULL,

approved INTEGER NOT NULL,

rejected INTEGER NOT NULL,

creationTime INTEGER NOT NULL,

approvalTime INTEGER NULL,

requestMessage VARCHAR2(255 char) NULL,

responseMessage VARCHAR2(255 char) NULL,

queuedType INTEGER NOT NULL,

CONSTRAINT jAWrkflw_pk PRIMARY KEY (workflowID,objID,typeID,userID)

);

-- ---------------------------------------------------------------------------

-- jiveDocVersion: Document version table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocVersion (

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

state VARCHAR2(20) NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

language CHAR(2) NOT NULL,

title VARCHAR2(255 char) NOT NULL,

summary CLOB NULL,

minorVersion INTEGER NOT NULL,

status INTEGER NOT NULL,

recordID INTEGER NOT NULL,

CONSTRAINT jiveDocVersion_pk PRIMARY KEY (versionID,internalDocID)

);

CREATE INDEX jDV_iDocID_stt_idx ON jiveDocVersion (internalDocID,state);

CREATE INDEX jDV_cDate_idx ON jiveDocVersion (creationDate);

CREATE INDEX jDV_mDate_idx ON jiveDocVersion (modificationDate);

CREATE INDEX jDV_title_idx ON jiveDocVersion (title);

CREATE UNIQUE INDEX jDV_ID_idx ON jiveDocVersion (recordID);

ALTER TABLE jiveDocVersion ADD CONSTRAINT jDV_ilDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocumentBody: The body of the document.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocumentBody (

bodyID INTEGER NOT NULL,

bodyText CLOB NULL,

fileName VARCHAR2(255 char) NULL,

fileSize INTEGER NULL,

contentType VARCHAR2(100) NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveDBody_pk PRIMARY KEY (bodyID)

);

-- ---------------------------------------------------------------------------

-- jiveDocBodyVersion: Document body versions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocBodyVersion (

bodyID INTEGER NOT NULL,

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

CONSTRAINT jiveDBV_pk PRIMARY KEY (bodyID,internalDocID,versionID)

);

CREATE INDEX jiveDBVvIdiDId_idx ON jiveDocBodyVersion (internalDocID,versionID);

ALTER TABLE jiveDocBodyVersion ADD CONSTRAINT jDBV_bID_fk FOREIGN KEY (bodyID) REFERENCES jiveDocumentBody INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveDocBodyVersion ADD CONSTRAINT jDBV_iDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveFieldValue: Field values.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFieldValue (

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

fieldID INTEGER NOT NULL,

numValue INTEGER NULL,

strValue VARCHAR2(3500 char) NULL

);

CREATE INDEX jFV_id_idx ON jiveFieldValue (internalDocID,versionID,fieldID);

CREATE INDEX jFV_nVal_idx ON jiveFieldValue (numValue);

-- ---------------------------------------------------------------------------

-- jiveDocVersionCmmt: Comments on document versions.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocVersionCmmt (

vCommentID INTEGER NOT NULL,

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

comments CLOB NOT NULL,

CONSTRAINT jiveDVCmmt_pk PRIMARY KEY (vCommentID)

);

ALTER TABLE jiveDocVersionCmmt ADD CONSTRAINT jDVCmmt_iDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocumentAudit: Document audit trail (not currently used).

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocumentAudit (

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

userID INTEGER NULL,

alterDate INTEGER NOT NULL,

alterType INTEGER NOT NULL,

fieldID INTEGER NULL,

oldValue CLOB NULL

);

-- ---------------------------------------------------------------------------

-- jiveComment: Comments.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveComment (

commentID INTEGER NOT NULL,

parentCommentID INTEGER NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

parentObjectType INTEGER NULL,

parentObjectID INTEGER NULL,

userID INTEGER NULL,

name VARCHAR2(100 char) NULL,

email VARCHAR2(100 char) NULL,

url VARCHAR2(255 char) NULL,

ip VARCHAR2(15 char) NULL,

body CLOB NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

moderated INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jivecomment_pk PRIMARY KEY (commentID)

);

CREATE INDEX jCmmt_p_idx ON jiveComment (parentObjectType,parentObjectID);

CREATE INDEX jCmmt_o_idx ON jiveComment (objectType,objectID);

CREATE INDEX jCmmt_userID_idx ON jiveComment (userID);

CREATE INDEX jCmmt_cDate_idx ON jiveComment (creationDate);

CREATE INDEX jCmmt_mDate_idx ON jiveComment (modificationDate);

-- ---------------------------------------------------------------------------

-- jiveCommentProp: Properties for comments.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCommentProp (

commentID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveCommentProp_pk PRIMARY KEY (commentID,name)

);

ALTER TABLE jiveCommentProp ADD CONSTRAINT jCmmPrp_cmtID_fk FOREIGN KEY (commentID) REFERENCES jiveComment INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveImage: Image data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveImage (

imageID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NULL,

fileName VARCHAR2(255 char) NOT NULL,

fileSize INTEGER NOT NULL,

contentType VARCHAR2(50) NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveImage_pk PRIMARY KEY (imageID)

);

CREATE INDEX jvImg_objt_idx ON jiveImage (objectType,objectID);

-- ---------------------------------------------------------------------------

-- jiveDraftImage: Draft/Image assocation table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDraftImage (

draftID INTEGER NOT NULL,

imageID INTEGER NOT NULL,

CONSTRAINT jiveDraftImage_pk PRIMARY KEY (draftID,imageID)

);

ALTER TABLE jiveDraftImage ADD CONSTRAINT jiveDI_dID_fk FOREIGN KEY (draftID) REFERENCES jiveDraft INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveDraftImage ADD CONSTRAINT jiveDI_iID_fk FOREIGN KEY (imageID) REFERENCES jiveImage INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAttachSettings: Attachment settings.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachSettings (

typeID INTEGER NOT NULL,

enabled INTEGER NOT NULL,

maxSize INTEGER NOT NULL,

maxNumber INTEGER NOT NULL,

allowByDefault INTEGER NOT NULL,

CONSTRAINT jAttS_pk PRIMARY KEY (typeID)

);

ALTER TABLE jiveAttachSettings ADD CONSTRAINT jAttS_typeID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAttachTypes: Allowed/Disallowed attachment content types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachTypes (

typeID INTEGER NOT NULL,

allowedType VARCHAR2(50 char) NULL,

disallowedType VARCHAR2(50 char) NULL

);

ALTER TABLE jiveAttachTypes ADD CONSTRAINT jAttT_typeID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAttachVersion: Version table for document attachments.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAttachVersion (

attachmentID INTEGER NOT NULL,

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

CONSTRAINT jAttVersion_pk PRIMARY KEY (attachmentID,internalDocID,versionID)

);

ALTER TABLE jiveAttachVersion ADD CONSTRAINT jAttV_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveAttachVersion ADD CONSTRAINT jAttV_iDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveImageVersion: Version table for document images.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveImageVersion (

imageID INTEGER NOT NULL,

internalDocID INTEGER NOT NULL,

versionID INTEGER NOT NULL

);

ALTER TABLE jiveImageVersion ADD CONSTRAINT jIV_iID_fk FOREIGN KEY (imageID) REFERENCES jiveImage INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveImageVersion ADD CONSTRAINT jIV_iDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocBodyConfig: Setting data for document bodies.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocBodyConfig (

typeID INTEGER NOT NULL,

enabled INTEGER NOT NULL,

maxSize INTEGER NOT NULL,

allowByDefault INTEGER NOT NULL,

CONSTRAINT jDBConfig_pk PRIMARY KEY (typeID)

);

ALTER TABLE jiveDocBodyConfig ADD CONSTRAINT jDBC_typeID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDocBodyTypes: Allowed/Disallowed binary body content types.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocBodyTypes (

typeID INTEGER NOT NULL,

allowedType VARCHAR2(50 char) NULL,

disallowedType VARCHAR2(50 char) NULL

);

ALTER TABLE jiveDocBodyTypes ADD CONSTRAINT jDBT_typeID_fk FOREIGN KEY (typeID) REFERENCES jiveDocType INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveOSPropEntry: OSWorkflow property table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveOSPropEntry (

globalKey VARCHAR2(150 char) NULL,

itemKey VARCHAR2(150 char) NULL,

itemType INTEGER NULL,

stringValue VARCHAR2(255 char) NULL,

dateValue INTEGER NULL,

dataValue BLOB NULL,

floatValue FLOAT NULL,

numberValue INTEGER NULL

);

CREATE UNIQUE INDEX jOSPE_key_idx ON jiveOSPropEntry (globalKey,itemKey,itemType);

-- ---------------------------------------------------------------------------

-- jiveWFEntry: OSWorkflow entry table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWFEntry (

id INTEGER NOT NULL,

name VARCHAR2(20 char) NULL,

state INTEGER NULL,

CONSTRAINT jiveWFEntry_pk PRIMARY KEY (id)

);

-- ---------------------------------------------------------------------------

-- jiveWFCurrentStep: OSWorkflow workflow current step table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWFCurrentStep (

id INTEGER NOT NULL,

entryID INTEGER NULL,

stepID INTEGER NULL,

actionID INTEGER NULL,

owner VARCHAR2(100 char) NULL,

startDate TIMESTAMP NULL,

finishDate TIMESTAMP NULL,

dueDate TIMESTAMP NULL,

status VARCHAR2(20) NULL,

caller VARCHAR2(100) NULL,

CONSTRAINT jWFCS_pk PRIMARY KEY (id)

);

CREATE INDEX jWFCS_eID_idx ON jiveWFCurrentStep (entryID);

CREATE INDEX jWFCS_o_idx ON jiveWFCurrentStep (owner);

CREATE INDEX jWFCS_c_idx ON jiveWFCurrentStep (caller);

ALTER TABLE jiveWFCurrentStep ADD CONSTRAINT jWFCS_entID_fk FOREIGN KEY (entryID) REFERENCES jiveWFEntry (id);

-- ---------------------------------------------------------------------------

-- jiveWFHistoryStep: OSWorkflow workflow history table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWFHistoryStep (

id INTEGER NOT NULL,

entryID INTEGER NULL,

stepID INTEGER NULL,

actionID INTEGER NULL,

owner VARCHAR2(100 char) NULL,

startDate TIMESTAMP NULL,

finishDate TIMESTAMP NULL,

dueDate TIMESTAMP NULL,

status VARCHAR2(20 char) NULL,

caller VARCHAR2(100 char) NULL,

CONSTRAINT jWFHStep_pk PRIMARY KEY (id)

);

CREATE INDEX jWFHStep_eID_idx ON jiveWFHistoryStep (entryID);

CREATE INDEX jWFHStep_o_idx ON jiveWFHistoryStep (owner);

CREATE INDEX jWFHStep_c_idx ON jiveWFHistoryStep (caller);

ALTER TABLE jiveWFHistoryStep ADD CONSTRAINT jWFHisStp_entID_fk FOREIGN KEY (entryID) REFERENCES jiveWFEntry (id);

-- ---------------------------------------------------------------------------

-- jiveWFCurrStepPrev: OSWorkflow previous workflow step table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWFCurrStepPrev (

id INTEGER NOT NULL,

previousID INTEGER NOT NULL,

CONSTRAINT jWFCSPrev_pk PRIMARY KEY (id,previousID)

);

CREATE INDEX jWFCSPv_id_idx ON jiveWFCurrStepPrev (id);

CREATE INDEX jWFCSPv_pid_idx ON jiveWFCurrStepPrev (previousID);

ALTER TABLE jiveWFCurrStepPrev ADD CONSTRAINT jWFCrStpPv_id_fk FOREIGN KEY (id) REFERENCES jiveWFCurrentStep (id);

ALTER TABLE jiveWFCurrStepPrev ADD CONSTRAINT jWFCrStpPv_pid_fk FOREIGN KEY (previousID) REFERENCES jiveWFHistoryStep (id);

-- ---------------------------------------------------------------------------

-- jiveWFHistStepPrev: OSWorkflow history step table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWFHistStepPrev (

id INTEGER NOT NULL,

previousID INTEGER NOT NULL,

CONSTRAINT jWFHisStpPv_pk PRIMARY KEY (id,previousID)

);

CREATE INDEX jWFHisStpPv_id_idx ON jiveWFHistStepPrev (id);

CREATE INDEX jWFHisStpPv_pid_ix ON jiveWFHistStepPrev (previousID);

ALTER TABLE jiveWFHistStepPrev ADD CONSTRAINT jWFHisStpPv_id_pk FOREIGN KEY (id) REFERENCES jiveWFHistoryStep (id);

ALTER TABLE jiveWFHistStepPrev ADD CONSTRAINT jWFHisStpPv_pid_fk FOREIGN KEY (previousID) REFERENCES jiveWFHistoryStep (id);

-- ---------------------------------------------------------------------------

-- jiveTagSet: Tag set data table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTagSet (

tagSetID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

CONSTRAINT jiveTagSet_pk PRIMARY KEY (tagSetID)

);

-- ---------------------------------------------------------------------------

-- jiveTagSetMap: Association table for tag sets and tags.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTagSetMap (

tagSetID INTEGER NOT NULL,

tagID INTEGER NOT NULL,

CONSTRAINT jiveTagSetMap_pk PRIMARY KEY (tagSetID,tagID)

);

ALTER TABLE jiveTagSetMap ADD CONSTRAINT jiveTSM_tsID_fk FOREIGN KEY (tagSetID) REFERENCES jiveTagSet;

-- ---------------------------------------------------------------------------

-- jiveObjectTagSet: Association table for tag sets and jive content objects.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjectTagSet (

objectID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

tagSetID INTEGER NOT NULL,

CONSTRAINT jiveObjectTS_pk PRIMARY KEY (objectType,objectID,tagSetID)

);

ALTER TABLE jiveObjectTagSet ADD CONSTRAINT jive_tsID_fk FOREIGN KEY (tagSetID) REFERENCES jiveTagSet;

-- ---------------------------------------------------------------------------

-- jiveStatusLevelPnt: Status level points.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveStatusLevelPnt (

pointID INTEGER NOT NULL,

userID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

points INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

code VARCHAR2(20 char) NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveStatLvlPnt_pk PRIMARY KEY (pointID)

);

CREATE INDEX jiveSLP_userID_idx ON jiveStatusLevelPnt (userID);

CREATE INDEX jiveSLP_crtdte_idx ON jiveStatusLevelPnt (creationDate);

CREATE INDEX jiveSLP_cntnr_idx ON jiveStatusLevelPnt (containerType,containerID);

CREATE INDEX jvSLP_cntuser_idx ON jiveStatusLevelPnt (containerType,containerID,userID);

-- ---------------------------------------------------------------------------

-- jiveStatLvlScen: Status level scenarios.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveStatLvlScen (

scenarioID INTEGER NOT NULL,

i18nKey VARCHAR2(100) NOT NULL,

code VARCHAR2(20) UNIQUE NOT NULL,

points INTEGER NOT NULL,

enabled INTEGER NOT NULL,

CONSTRAINT jiveStatLvlScen_pk PRIMARY KEY (scenarioID)

);

-- ---------------------------------------------------------------------------

-- jiveWikiLink: Wiki link tracking table.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWikiLink (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

targetLinkType INTEGER NOT NULL,

targetObjectType INTEGER NULL,

targetObjectID INTEGER NULL,

targetAttachmentID INTEGER NULL,

targetURL VARCHAR2(3500) NULL,

targetText VARCHAR2(3500) NULL,

targetDescription VARCHAR2(3500) NULL,

targetAnchorText VARCHAR2(3500) NULL,

creationDate INTEGER DEFAULT 0 NOT NULL

);

CREATE INDEX jiveWL_oTypId_idx ON jiveWikiLink (objectType,objectID);

CREATE INDEX jiveWL_tTypId_idx ON jiveWikiLink (targetObjectType,targetObjectID);

-- ---------------------------------------------------------------------------

-- jiveDocCollab: Document collaboration.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDocCollab (

internalDocID INTEGER NOT NULL,

userID INTEGER NOT NULL,

collaboratorType INTEGER NOT NULL,

CONSTRAINT jiveDocCollab_pk PRIMARY KEY (internalDocID,userID,collaboratorType)

);

ALTER TABLE jiveDocCollab ADD CONSTRAINT jDC_iDocID_fk FOREIGN KEY (internalDocID) REFERENCES jiveDocument;

-- ---------------------------------------------------------------------------

-- jiveUserIMHandle: User IM handles.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserIMHandle (

userID INTEGER NOT NULL,

imHandle VARCHAR2(100) NOT NULL,

imType INTEGER NOT NULL,

CONSTRAINT jiveUIH_pk PRIMARY KEY (userID,imType)

);

ALTER TABLE jiveUserIMHandle ADD CONSTRAINT jUID_uID_fk FOREIGN KEY (userID) REFERENCES jiveUser;

-- ---------------------------------------------------------------------------

-- jiveStat: Statistics.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveStat (

statID INTEGER NOT NULL,

communityID INTEGER NOT NULL,

statDate INTEGER NOT NULL,

statValue FLOAT NOT NULL,

CONSTRAINT jiveStat_pk PRIMARY KEY (statID,communityID,statDate)

);

-- ---------------------------------------------------------------------------

-- jiveActivity: A table that stores all recent activity happening in the application.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveActivity (

activityID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

activityType INTEGER NOT NULL,

userID INTEGER NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveActivity_pk PRIMARY KEY (activityID)

);

CREATE INDEX jiveActiv_cntr_idx ON jiveActivity (containerType,containerID);

-- ---------------------------------------------------------------------------

-- jiveActivityHistry: A table that stores aggregated activity counts

-- ---------------------------------------------------------------------------

CREATE TABLE jiveActivityHistry (

historyID INTEGER NOT NULL,

userID INTEGER NOT NULL,

viewCount INTEGER NULL,

createCount INTEGER NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveActivHist_pk PRIMARY KEY (historyID)

);

-- ---------------------------------------------------------------------------

-- jivePopularity: A table that stores archived content popularity scores for generating popular content

-- ---------------------------------------------------------------------------

CREATE TABLE jivePopularity (

popularityID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

score INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jivePopularity_pk PRIMARY KEY (popularityID)

);

CREATE INDEX jivePopul_cntr_pk ON jivePopularity (containerType,containerID);

-- ---------------------------------------------------------------------------

-- jivePluginProp: A table that contents contains custom properties stored for plugins

-- ---------------------------------------------------------------------------

CREATE TABLE jivePluginProp (

pluginName VARCHAR2(100) NOT NULL,

propName VARCHAR2(100) NOT NULL,

propValue VARCHAR2(3500) NOT NULL,

CONSTRAINT jivePluginProp_pk PRIMARY KEY (pluginName,propName)

);

-- ---------------------------------------------------------------------------

-- jiveBridge: Table for bridged CS instances.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBridge (

bridgeID INTEGER NOT NULL,

url VARCHAR2(255 char) NOT NULL,

name VARCHAR2(255 char) NOT NULL,

description VARCHAR2(255 char) NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

disabled INTEGER NOT NULL,

engineID INTEGER DEFAULT -1 NOT NULL,

CONSTRAINT jiveBridge_pk PRIMARY KEY (bridgeID)

);

-- ---------------------------------------------------------------------------

-- jiveBridgeOption: Table for bridge options: opensearch, widgets, bridged content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBridgeOption (

bridgeID INTEGER NOT NULL,

type INTEGER NOT NULL,

localOrigin INTEGER DEFAULT 0 NOT NULL,

state INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jBridgeOpt_pk PRIMARY KEY (bridgeID,type,localOrigin)

);

ALTER TABLE jiveBridgeOption ADD CONSTRAINT jBridgeOpt_bID_fk FOREIGN KEY (bridgeID) REFERENCES jiveBridge INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBridgeUser: Table for bridge users

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBridgeUser (

uuid VARCHAR2(128) NOT NULL,

userID INTEGER NOT NULL,

remoteUserID INTEGER NOT NULL,

remoteUsername VARCHAR2(100) NOT NULL,

CONSTRAINT jBridgeUsr_pk PRIMARY KEY (uuid,remoteUserID)

);

-- ---------------------------------------------------------------------------

-- jiveWidget: Installed widget information

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWidget (

widgetID INTEGER NOT NULL,

className VARCHAR2(255 char) NOT NULL,

type VARCHAR2(255 char) NULL,

bridgeID INTEGER NULL,

remoteWidgetID INTEGER NULL,

CONSTRAINT jWidget_pk PRIMARY KEY (widgetID)

);

CREATE UNIQUE INDEX jWidget_uk_idx ON jiveWidget (className);

-- ---------------------------------------------------------------------------

-- jiveWidgetFrame: Container table for Widget frames

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWidgetFrame (

frameID INTEGER NOT NULL,

widgetID INTEGER NOT NULL,

parentObjectType INTEGER NOT NULL,

parentObjectID INTEGER NOT NULL,

containerID INTEGER NOT NULL,

frameIndex INTEGER NOT NULL,

published INTEGER NOT NULL,

CONSTRAINT jWidgetF_pk PRIMARY KEY (frameID)

);

ALTER TABLE jiveWidgetFrame ADD CONSTRAINT jWidgetF_wID_fk FOREIGN KEY (widgetID) REFERENCES jiveWidget INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveWidgetFrmProp: Properties for widget frames

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWidgetFrmProp (

frameID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue CLOB NOT NULL,

CONSTRAINT jWidgetFP_pk PRIMARY KEY (frameID,name)

);

ALTER TABLE jiveWidgetFrmProp ADD CONSTRAINT jWidgetFP_fID_fk FOREIGN KEY (frameID) REFERENCES jiveWidgetFrame INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveWLayout: Installed widget layout information

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWLayout (

layoutID INTEGER NOT NULL,

className VARCHAR2(255 char) NOT NULL,

disabled INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jiveWLayout_pk PRIMARY KEY (layoutID)

);

-- ---------------------------------------------------------------------------

-- jiveWLayoutFrame: Container table for Widget Layout frames

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWLayoutFrame (

layoutID INTEGER NOT NULL,

parentObjectType INTEGER NOT NULL,

parentObjectID INTEGER NOT NULL,

published INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jiveWLayoutF_pk PRIMARY KEY (parentObjectType,parentObjectID,published)

);

ALTER TABLE jiveWLayoutFrame ADD CONSTRAINT jiveWLaytF_lID_fk FOREIGN KEY (layoutID) REFERENCES jiveWLayout INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveWidgetType: Categories for Widgets.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWidgetType (

widgetID INTEGER NOT NULL,

typeClass VARCHAR2(255 char) NOT NULL,

typeEnum VARCHAR2(255 char) NOT NULL,

CONSTRAINT jGadgetType_pk PRIMARY KEY (widgetID,typeClass,typeEnum)

);

ALTER TABLE jiveWidgetType ADD CONSTRAINT jWidgetType_wid_fk FOREIGN KEY (widgetID) REFERENCES jiveWidget INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveWidgetCat: Categories for Widgets.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveWidgetCat (

widgetID INTEGER NOT NULL,

categoryClass VARCHAR2(255 char) NOT NULL,

categoryEnum VARCHAR2(255 char) NOT NULL,

CONSTRAINT jGadgetCat_pk PRIMARY KEY (widgetID,categoryClass,categoryEnum)

);

ALTER TABLE jiveWidgetCat ADD CONSTRAINT jWidgetCat_wid_fk FOREIGN KEY (widgetID) REFERENCES jiveWidget INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveGadget: Contains meta-information surrounding OpenSocial Gadgets.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGadget (

widgetID INTEGER NOT NULL,

creator INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jGadget_pk PRIMARY KEY (widgetID)

);

ALTER TABLE jiveGadget ADD CONSTRAINT jGadget_wid_fk FOREIGN KEY (widgetID) REFERENCES jiveWidget INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveOSAppData: Used to track which users of App Data for a given OpenSocial application

-- ---------------------------------------------------------------------------

CREATE TABLE jiveOSAppData (

widgetID INTEGER NOT NULL,

userID INTEGER NOT NULL,

CONSTRAINT jOSAD_pk PRIMARY KEY (widgetID,userID)

);

ALTER TABLE jiveOSAppData ADD CONSTRAINT jOSAD_wid_fk FOREIGN KEY (widgetID) REFERENCES jiveWidget INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveSearchEngine: Installed OpenSearch engine information

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSearchEngine (

engineID INTEGER NOT NULL,

shortName VARCHAR2(255 char) NOT NULL,

description VARCHAR2(2000 char) NULL,

descriptorURL VARCHAR2(2000 char) NULL,

iconURL VARCHAR2(2000 char) NULL,

searchContentType VARCHAR2(100 char) NULL,

searchHttpMethod VARCHAR2(10 char) DEFAULT 'GET',

searchURL VARCHAR2(2000 char) NOT NULL,

externalSearchURL VARCHAR2(2000 char) NOT NULL,

queryTestTerm VARCHAR2(40 char) DEFAULT 'test',

maxResultCount INTEGER DEFAULT 20 NOT NULL,

authenticated INTEGER DEFAULT 0 NOT NULL,

username VARCHAR2(40 char) NULL,

password VARCHAR2(40 char) NULL,

enabled INTEGER NOT NULL,

CONSTRAINT jSearchEngine_pk PRIMARY KEY (engineID)

);

-- ---------------------------------------------------------------------------

-- jiveUserRelGraph: A container for a set of relationships.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserRelGraph (

graphID INTEGER NOT NULL,

graphTypeID INTEGER DEFAULT 1 NOT NULL,

name VARCHAR2(255 char) NOT NULL,

enabled INTEGER DEFAULT 1 NOT NULL,

labelPeer VARCHAR2(40 char) NULL,

labelManager VARCHAR2(40 char) NULL,

labelDirRpt VARCHAR2(40 char) NULL,

approvalsEnabled INTEGER DEFAULT 0 NOT NULL,

peerChoiceAllowed INTEGER DEFAULT 0 NOT NULL,

mgrChoiceAllowed INTEGER DEFAULT 0 NOT NULL,

drptChoiceAllowed INTEGER DEFAULT 0 NOT NULL,

reflexive INTEGER DEFAULT 1 NOT NULL,

CONSTRAINT jiveUsrRelGrph_pk PRIMARY KEY (graphID)

);

-- ---------------------------------------------------------------------------

-- jiveUsrRelGrApr: A set of users who approve all new graph relationships.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUsrRelGrApr (

graphID INTEGER NOT NULL,

userID INTEGER NOT NULL

);

ALTER TABLE jiveUsrRelGrApr ADD CONSTRAINT jvUsrRelGA_fk_usID FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUsrRelGrApr ADD CONSTRAINT jvUsrRelGA_fk_grID FOREIGN KEY (graphID) REFERENCES jiveUserRelGraph INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUsrRelGrNtf: A set of users who are notified of graph relationship changes.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUsrRelGrNtf (

graphID INTEGER NOT NULL,

userID INTEGER NOT NULL

);

ALTER TABLE jiveUsrRelGrNtf ADD CONSTRAINT jvUsrRelNT_fk_usID FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUsrRelGrNtf ADD CONSTRAINT jvUsrRelNT_fk_grID FOREIGN KEY (graphID) REFERENCES jiveUserRelGraph INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUserRel: Historical relationships between users

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserRel (

relationshipID INTEGER NOT NULL,

userID INTEGER NOT NULL,

relatedUserID INTEGER NOT NULL,

relnshipTypeID INTEGER DEFAULT 2 NOT NULL,

state VARCHAR2(20) DEFAULT 'pending_approval' NOT NULL,

creationDate INTEGER NOT NULL,

retirementDate INTEGER DEFAULT 0,

graphID INTEGER NOT NULL,

workflowID INTEGER NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveUsrRel_pk PRIMARY KEY (relationshipID)

);

CREATE UNIQUE INDEX jvUsrRel_uk_idx ON jiveUserRel (userID,relatedUserID,creationDate,graphID);

ALTER TABLE jiveUserRel ADD CONSTRAINT jvUsrRel_fk_usrID FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUserRel ADD CONSTRAINT jvUsrRel_fk_rlUsID FOREIGN KEY (relatedUserID) REFERENCES jiveUser (userID) INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUserRel ADD CONSTRAINT jvUsrRel_fk_rlGrID FOREIGN KEY (graphID) REFERENCES jiveUserRelGraph INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUsrRelList: User-specific lists of relationships

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUsrRelList (

listID INTEGER NOT NULL,

name VARCHAR2(200) NOT NULL,

userID INTEGER NOT NULL,

labelStyle VARCHAR2(200) NULL,

CONSTRAINT jvUsrRelLst_pk PRIMARY KEY (listID)

);

ALTER TABLE jiveUsrRelList ADD CONSTRAINT jvURLst_fk_userID FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUsrRelListMap: Maps user relationship lists to relationships

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUsrRelListMap (

listID INTEGER NOT NULL,

relationshipID INTEGER NOT NULL

);

CREATE UNIQUE INDEX jvUsrRelLM_uk_idx ON jiveUsrRelListMap (listID,relationshipID);

ALTER TABLE jiveUsrRelListMap ADD CONSTRAINT jvURLstLM_fk_lstID FOREIGN KEY (listID) REFERENCES jiveUsrRelList INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUsrRelListMap ADD CONSTRAINT jvURLstLM_fk_relID FOREIGN KEY (relationshipID) REFERENCES jiveUserRel INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUserStatus: User status information

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserStatus (

userStatusID INTEGER NOT NULL,

userID INTEGER NOT NULL,

userStatusText CLOB NULL,

creationDate INTEGER NOT NULL,

containerID INTEGER DEFAULT -1 NOT NULL,

containerType INTEGER DEFAULT -1 NOT NULL,

modificationDate INTEGER DEFAULT 0 NOT NULL,

status INTEGER DEFAULT 2 NOT NULL,

latitude FLOAT DEFAULT -1 NOT NULL,

longitude FLOAT DEFAULT -1 NOT NULL,

CONSTRAINT jiveUserStatus_pk PRIMARY KEY (userStatusID)

);

-- ---------------------------------------------------------------------------

-- jiveContentMeta: A means to link one jiveObject to another as meta content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContentMeta (

id INTEGER NOT NULL,

contentId INTEGER NOT NULL,

contentType INTEGER NOT NULL,

parentId INTEGER NOT NULL,

parentType INTEGER NOT NULL,

CONSTRAINT jCntntMta_pk PRIMARY KEY (id)

);

CREATE INDEX jCntntMta_pnt_idx ON jiveContentMeta (parentId,parentType);

-- ---------------------------------------------------------------------------

-- jiveContentMtProp: Properties for content meta

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContentMtProp (

id INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jCntntMtaProp_pk PRIMARY KEY (id,name)

);

ALTER TABLE jiveContentMtProp ADD CONSTRAINT jCntntMtaPrp_id_fk FOREIGN KEY (id) REFERENCES jiveContentMeta INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveProject: Container table for work space data.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProject (

projectID INTEGER NOT NULL,

parentObjectType INTEGER NOT NULL,

parentObjectID INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

startDate INTEGER NOT NULL,

dueDate INTEGER NULL,

status INTEGER NOT NULL,

latestStatusID INTEGER NULL,

dueDateNull INTEGER NOT NULL,

CONSTRAINT jivePjt_pk PRIMARY KEY (projectID)

);

CREATE INDEX jivePjt_ddn_idx ON jiveProject (dueDate,dueDateNull);

-- ---------------------------------------------------------------------------

-- jiveProjectProp: Properties for work spaces

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProjectProp (

projectID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jPjtP_pk PRIMARY KEY (projectID,name)

);

ALTER TABLE jiveProjectProp ADD CONSTRAINT jPjtP_wsID_fk FOREIGN KEY (projectID) REFERENCES jiveProject INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePCheckPoint: Container table for work space checkPoint data.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePCheckPoint (

checkPointID INTEGER NOT NULL,

projectID INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

dueDate INTEGER NOT NULL,

CONSTRAINT jivePCP_pk PRIMARY KEY (checkPointID)

);

ALTER TABLE jivePCheckPoint ADD CONSTRAINT jivePCP_fk FOREIGN KEY (projectID) REFERENCES jiveProject INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePTask: Container table for work space data.

-- ---------------------------------------------------------------------------

CREATE TABLE jivePTask (

taskID INTEGER NOT NULL,

parentTaskID INTEGER DEFAULT 0 NOT NULL,

parentObjectType INTEGER NOT NULL,

parentObjectID INTEGER NOT NULL,

subject VARCHAR2(3500 char) NOT NULL,

body VARCHAR2(3500 char) NULL,

userID INTEGER NULL,

assignorID INTEGER NULL,

ownerID INTEGER NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

dueDate INTEGER NULL,

dueDateNull INTEGER NOT NULL,

completed INTEGER NOT NULL,

CONSTRAINT jivePTask_pk PRIMARY KEY (taskID)

);

CREATE INDEX jivePTask_ddn_idx ON jivePTask (dueDate,dueDateNull);

CREATE INDEX jivePTask_prnt_idx ON jivePTask (parentTaskID);

-- ---------------------------------------------------------------------------

-- jivePTaskProp: Properties for work space checkPoints

-- ---------------------------------------------------------------------------

CREATE TABLE jivePTaskProp (

taskID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jPTaskP_pk PRIMARY KEY (taskID,name)

);

ALTER TABLE jivePTaskProp ADD CONSTRAINT jPTaskP_tID_fk FOREIGN KEY (taskID) REFERENCES jivePTask INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAuditLog: Audit messages resulting from admin activity

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAuditLog (

auditMessageID INTEGER NOT NULL,

userID INTEGER NOT NULL,

timestamp INTEGER NOT NULL,

description VARCHAR2(1024 char) NOT NULL,

node VARCHAR2(512) NOT NULL,

details VARCHAR2(2048) NULL,

CONSTRAINT jiveAudit_pk PRIMARY KEY (auditMessageID)

);

-- ---------------------------------------------------------------------------

-- jiveReference: Jive Reference Table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveReference (

referenceID INTEGER NOT NULL,

refererObjectID INTEGER NOT NULL,

refererObjectType INTEGER NOT NULL,

refObjectID INTEGER NOT NULL,

refObjectType INTEGER NOT NULL,

CONSTRAINT jiveReference_pk PRIMARY KEY (referenceID)

);

CREATE UNIQUE INDEX jRefUniq_key_idx ON jiveReference (refererObjectID,refererObjectType,refObjectID,refObjectType);

-- ---------------------------------------------------------------------------

-- jiveContentBackup: Content backup table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContentBackup (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

versionID INTEGER NOT NULL,

body CLOB NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveCntBkp_pk PRIMARY KEY (objectID,objectType,versionID)

);

CREATE INDEX jiveCntBkp_idx ON jiveContentBackup (objectID);

-- ---------------------------------------------------------------------------

-- jiveSGroup: Container table for social groups.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSGroup (

groupID INTEGER NOT NULL,

groupType INTEGER NOT NULL,

name VARCHAR2(255 char) NOT NULL,

displayName VARCHAR2(255 char) NOT NULL,

description VARCHAR2(1000 char) NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveSGroup_pk PRIMARY KEY (groupID)

);

CREATE INDEX jvUsr_dsplynm_idx ON jiveSGroup (displayName);

-- ---------------------------------------------------------------------------

-- jiveSGroupProp: Properties for social groups

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSGroupProp (

groupID INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jSGroupP_pk PRIMARY KEY (groupID,name)

);

ALTER TABLE jiveSGroupProp ADD CONSTRAINT jSGroupP_gID_fk FOREIGN KEY (groupID) REFERENCES jiveSGroup INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveSGroupMember: Member table for social groups.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSGroupMember (

groupID INTEGER NOT NULL,

userID INTEGER NOT NULL,

memberType INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

membershipID INTEGER NOT NULL,

workflowID INTEGER NULL,

CONSTRAINT jiveSGMem_pk PRIMARY KEY (groupID,userID)

);

CREATE UNIQUE INDEX jvGrpMem_mID_idx ON jiveSGroupMember (membershipID);

ALTER TABLE jiveSGroupMember ADD CONSTRAINT jSGMem_gID_fk FOREIGN KEY (groupID) REFERENCES jiveSGroup INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveSGroupMember ADD CONSTRAINT jSGMem_uID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveEntitlement: Entitlement data for Jive SBS objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveEntitlement (

entitlementID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

userID INTEGER NULL,

groupID INTEGER NULL,

contentType INTEGER NOT NULL,

entitlementMask INTEGER NOT NULL,

creationDate INTEGER NULL,

modificationDate INTEGER NULL,

CONSTRAINT jvUsrEnt_pk PRIMARY KEY (entitlementID)

);

CREATE INDEX jvUsrEnt_objct_idx ON jiveEntitlement (objectID,objectType,contentType);

CREATE INDEX jvUsrEnt_usrID_idx ON jiveEntitlement (userID ASC);

CREATE INDEX jvUsrEnt_grpID_idx ON jiveEntitlement (groupID ASC);

-- ---------------------------------------------------------------------------

-- jiveExtContext: Extended Context

-- ---------------------------------------------------------------------------

CREATE TABLE jiveExtContext (

contextId INTEGER NOT NULL,

type INTEGER NOT NULL,

name VARCHAR2(100) NOT NULL,

CONSTRAINT jiveExtCntxt PRIMARY KEY (contextId)

);

CREATE UNIQUE INDEX jExtCtx_nmtp_idx ON jiveExtContext (type,name);

-- ---------------------------------------------------------------------------

-- jivePermLevel: Permission levels

-- ---------------------------------------------------------------------------

CREATE TABLE jivePermLevel (

permLevelId INTEGER NOT NULL,

name VARCHAR2(25) NULL,

system INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jivePrmLvl_pk PRIMARY KEY (permLevelId)

);

-- ---------------------------------------------------------------------------

-- jiveCustomPermLvl: Custom Permission Levels

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCustomPermLvl (

permLevelId INTEGER NOT NULL,

creatorId INTEGER NULL,

description VARCHAR2(255) NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL

);

ALTER TABLE jiveCustomPermLvl ADD CONSTRAINT jvCstPrmLvl_prmLvlId_fk FOREIGN KEY (permLevelId) REFERENCES jivePermLevel INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveObjPermLvl: Permission levels available for objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjPermLvl (

objPermLevelId INTEGER NOT NULL,

name VARCHAR2(25) NOT NULL,

description VARCHAR2(255) NULL,

objType INTEGER NULL,

lvlType VARCHAR2(25) NULL,

CONSTRAINT jvObjPrmLvl_pk PRIMARY KEY (objPermLevelId)

);

-- ---------------------------------------------------------------------------

-- jiveObjPermLvlExt: Extension information for object permission levels

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjPermLvlExt (

objPermLevelId INTEGER NOT NULL,

creationDate INTEGER DEFAULT 0 NOT NULL,

extender INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jvObjPrmLvlXt_pk PRIMARY KEY (extender,objPermLevelId)

);

ALTER TABLE jiveObjPermLvlExt ADD CONSTRAINT jvObjPrmLvlXt_prmLvlId_fk FOREIGN KEY (objPermLevelId) REFERENCES jiveObjPermLvl INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjPermLvlExt ADD CONSTRAINT jvObjPrmLvlXt_ext_fk FOREIGN KEY (extender) REFERENCES jiveExtContext (contextId) INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveObjPermLvlMap: Mapping between object permission levels and permission levels

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjPermLvlMap (

permLevelId INTEGER NOT NULL,

objPermLevelId INTEGER NOT NULL,

creationDate INTEGER DEFAULT 0 NOT NULL,

extender INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jvObjPrmLvlMp_pk PRIMARY KEY (permLevelId,objPermLevelId,extender)

);

ALTER TABLE jiveObjPermLvlMap ADD CONSTRAINT jvObjPrmLvlMp_prmLvlId_fk FOREIGN KEY (permLevelId) REFERENCES jivePermLevel INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjPermLvlMap ADD CONSTRAINT jvObjPrmLvlMp_objPrmLvlId_fk FOREIGN KEY (objPermLevelId) REFERENCES jiveObjPermLvl INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjPermLvlMap ADD CONSTRAINT jvObjPrmLvlMp_ext_fk FOREIGN KEY (extender) REFERENCES jiveExtContext (contextId) INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDefObjPermLvl: Default object permission levels granted to all objects of a particular type when applied

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDefObjPermLvl (

objPermLevelId INTEGER NOT NULL,

objType INTEGER NULL

);

CREATE UNIQUE INDEX jvDfObjPrmLvl_idx ON jiveDefObjPermLvl (objPermLevelId,objType);

ALTER TABLE jiveDefObjPermLvl ADD CONSTRAINT jvDfObjPrmLvl_objPrmLvlId_fk FOREIGN KEY (objPermLevelId) REFERENCES jiveObjPermLvl INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveEntitlementMsk: Bit masks used by entitlements

-- ---------------------------------------------------------------------------

CREATE TABLE jiveEntitlementMsk (

mask INTEGER NOT NULL,

name VARCHAR2(25) NOT NULL,

CONSTRAINT jvEnttlmntMsk_pk PRIMARY KEY (mask)

);

-- ---------------------------------------------------------------------------

-- jiveObjPermLvlMsk: Maps masks to perm levels

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjPermLvlMsk (

objPermLevelId INTEGER NOT NULL,

mask INTEGER NOT NULL,

creationDate INTEGER DEFAULT 0 NOT NULL,

extender INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT jvObjPrmLvlMsk_pk PRIMARY KEY (objPermLevelId,mask,extender)

);

ALTER TABLE jiveObjPermLvlMsk ADD CONSTRAINT jvObjPrmLvlMsk_objPrmLvlId_fk FOREIGN KEY (objPermLevelId) REFERENCES jiveObjPermLvl INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjPermLvlMsk ADD CONSTRAINT jvObjPrmLvlMsk_msk_fk FOREIGN KEY (mask) REFERENCES jiveEntitlementMsk INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjPermLvlMsk ADD CONSTRAINT jvObjPrmLvlMsk_ext_fk FOREIGN KEY (extender) REFERENCES jiveExtContext (contextId) INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePermLvlMsk: Maps masks and object types to perm levels

-- ---------------------------------------------------------------------------

CREATE TABLE jivePermLvlMsk (

permLevelId INTEGER NOT NULL,

mask INTEGER NOT NULL,

objType INTEGER NULL

);

CREATE UNIQUE INDEX jPLvlMk_plmkot_idx ON jivePermLvlMsk (permLevelId,mask,objType);

ALTER TABLE jivePermLvlMsk ADD CONSTRAINT jvPrmLvlMsk_prmLvlId_fk FOREIGN KEY (permLevelId) REFERENCES jivePermLevel INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jivePermLvlMsk ADD CONSTRAINT jvPrmLvlMsk_msk_fk FOREIGN KEY (mask) REFERENCES jiveEntitlementMsk INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveObjTypeMsk: All masks available for a particular object type

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjTypeMsk (

mask INTEGER NOT NULL,

objType INTEGER NOT NULL,

creationDate INTEGER DEFAULT 0 NOT NULL,

extender INTEGER DEFAULT 0 NOT NULL

);

CREATE UNIQUE INDEX jvObjTpMsk_msk_idx ON jiveObjTypeMsk (mask,objType,extender);

ALTER TABLE jiveObjTypeMsk ADD CONSTRAINT jvObjTpLvlMsk_msk_fk FOREIGN KEY (mask) REFERENCES jiveEntitlementMsk INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveObjTypeMsk ADD CONSTRAINT jvObjTpLvlMsk_ext_fk FOREIGN KEY (extender) REFERENCES jiveExtContext (contextId) INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveGrantedPermLvl: When a perm level is set on an entity it is stored in this table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveGrantedPermLvl (

permLevelId INTEGER NOT NULL,

grantedObjectType INTEGER NOT NULL,

grantedId INTEGER NOT NULL,

targetObjectType INTEGER NOT NULL,

targetObjectId INTEGER NOT NULL

);

CREATE INDEX jGrntPmLvl_tob_idx ON jiveGrantedPermLvl (targetObjectId,targetObjectType);

CREATE INDEX jGrntPmLvl_gob_idx ON jiveGrantedPermLvl (grantedId,grantedObjectType);

ALTER TABLE jiveGrantedPermLvl ADD CONSTRAINT jGrntPrmLvl_prmId_fk FOREIGN KEY (permLevelId) REFERENCES jivePermLevel INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveEntlmntInherit: The listing of containers which are inheriting entitlement settings from their parent containers

-- ---------------------------------------------------------------------------

CREATE TABLE jiveEntlmntInherit (

containerType INTEGER NOT NULL,

containerId INTEGER NOT NULL,

CONSTRAINT jvEnttlmntInhrt_pk PRIMARY KEY (containerType,containerId)

);

-- ---------------------------------------------------------------------------

-- jiveProjectStatus: Table for project status.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveProjectStatus (

statusID INTEGER NOT NULL,

projectID INTEGER NOT NULL,

statusType INTEGER NOT NULL,

statusDesc VARCHAR2(255 char) NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jivePjtStat_pk PRIMARY KEY (statusID)

);

ALTER TABLE jiveProjectStatus ADD CONSTRAINT jvPjtStat_pjtID_fk FOREIGN KEY (projectID) REFERENCES jiveProject INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveInvite: Table for invitations

-- ---------------------------------------------------------------------------

CREATE TABLE jiveInvite (

invitationId INTEGER NOT NULL,

inviterId INTEGER NULL,

userId INTEGER NULL,

email VARCHAR2(100) NULL,

objectType INTEGER NOT NULL,

objectId INTEGER NOT NULL,

sentDate INTEGER NULL,

revokerId INTEGER NULL,

revokeDate INTEGER NULL,

state INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveInvite_pk PRIMARY KEY (invitationId)

);

-- ---------------------------------------------------------------------------

-- jiveInviteProp: Properties for invitations.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveInviteProp (

invitationId INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jiveInvProp_pk PRIMARY KEY (invitationId,name)

);

ALTER TABLE jiveInviteProp ADD CONSTRAINT jvInvProp_invID_fk FOREIGN KEY (invitationId) REFERENCES jiveInvite INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePlugin: Plugin information

-- ---------------------------------------------------------------------------

CREATE TABLE jivePlugin (

pluginID INTEGER NOT NULL,

name VARCHAR2(100 char) UNIQUE NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

data BLOB NULL,

CONSTRAINT jivePlugin_pk PRIMARY KEY (pluginID)

);

-- ---------------------------------------------------------------------------

-- jiveUserContainer: Representation of user-owned content aggregation

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserContainer (

userContainerID INTEGER NOT NULL,

userID INTEGER NOT NULL,

name VARCHAR2(255 char) NULL,

displayName VARCHAR2(255 char) NULL,

description VARCHAR2(1000 char) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveUCont_pk PRIMARY KEY (userContainerID)

);

CREATE INDEX jiveUCont_uID ON jiveUserContainer (userID);

ALTER TABLE jiveUserContainer ADD CONSTRAINT jiveUCont_uID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUserContnrProp: A table that contents contains custom properties stored for user containers

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserContnrProp (

userContainerID INTEGER NOT NULL,

name VARCHAR2(100) NOT NULL,

propValue VARCHAR2(3500) NOT NULL,

CONSTRAINT jiveUCntPrp_pk PRIMARY KEY (userContainerID,name)

);

ALTER TABLE jiveUserContnrProp ADD CONSTRAINT jiveUCntPrp_uID_fk FOREIGN KEY (userContainerID) REFERENCES jiveUserContainer INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveI18nText: provides internationalized text for other tables.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveI18nText (

textID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

objectAttribute INTEGER NOT NULL,

locale VARCHAR2(100) NOT NULL,

text VARCHAR2(2000 char) NOT NULL,

custom INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveI18nText_pk PRIMARY KEY (textID)

);

CREATE UNIQUE INDEX jvI18nTxt_uq_idx ON jiveI18nText (objectType,objectID,objectAttribute,locale);

-- ---------------------------------------------------------------------------

-- jiveBookmark: provides bookmarking for Jive SBS content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBookmark (

id INTEGER NOT NULL,

markedType INTEGER NOT NULL,

markedId INTEGER NOT NULL,

creatorType INTEGER NOT NULL,

creatorId INTEGER NOT NULL,

ownerType INTEGER NOT NULL,

ownerId INTEGER NOT NULL,

subject VARCHAR2(255) NULL,

notes VARCHAR2(3500) NULL,

status INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveBookmark_pk PRIMARY KEY (id)

);

CREATE UNIQUE INDEX jiveBmark_id_idx ON jiveBookmark (markedType,markedId,creatorType,creatorId,ownerType,ownerId);

-- ---------------------------------------------------------------------------

-- jiveExternalURL: holds URL information for external favorites

-- ---------------------------------------------------------------------------

CREATE TABLE jiveExternalURL (

id INTEGER NOT NULL,

protocol VARCHAR2(10) NOT NULL,

host VARCHAR2(3500) NOT NULL,

port INTEGER NULL,

path VARCHAR2(3500) NULL,

query VARCHAR2(3500) NULL,

ref VARCHAR2(3500) NULL,

creationDate INTEGER NOT NULL,

title VARCHAR2(1000) NULL,

mimeType VARCHAR2(100) NULL,

modificationDate INTEGER NOT NULL,

status INTEGER NOT NULL,

CONSTRAINT jiveExternalURL_pk PRIMARY KEY (id)

);

-- ---------------------------------------------------------------------------

-- jiveLabel: holds labels used for personal organization of various jive objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLabel (

labelID INTEGER NOT NULL,

name VARCHAR2(200) NOT NULL,

ownerID INTEGER NOT NULL,

style VARCHAR2(200) NULL,

CONSTRAINT jvLbl_pk PRIMARY KEY (labelID)

);

ALTER TABLE jiveLabel ADD CONSTRAINT jvLbl_fk_userID FOREIGN KEY (ownerID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveLabelType: Maps labels to one or many jive object types

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLabelType (

labelID INTEGER NOT NULL,

objectType INTEGER NOT NULL

);

CREATE UNIQUE INDEX jvLblType_uk_idx ON jiveLabelType (labelID,objectType);

ALTER TABLE jiveLabelType ADD CONSTRAINT jvLblType_fk_lstID FOREIGN KEY (labelID) REFERENCES jiveLabel;

-- ---------------------------------------------------------------------------

-- jiveLabelMap: Maps labels to jive objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLabelMap (

labelID INTEGER NOT NULL,

objectID INTEGER NOT NULL,

objectType INTEGER NOT NULL

);

CREATE UNIQUE INDEX jvLblMap_uk_idx ON jiveLabelMap (labelID,objectID,objectType);

ALTER TABLE jiveLabelMap ADD CONSTRAINT jvLblMap_fk_lstID FOREIGN KEY (labelID) REFERENCES jiveLabel;

-- ---------------------------------------------------------------------------

-- jiveCredential: Store for persisting credentials

-- ---------------------------------------------------------------------------

CREATE TABLE jiveCredential (

userID INTEGER NOT NULL,

artifactID INTEGER NOT NULL,

type INTEGER NOT NULL,

token RAW(2000) NOT NULL

);

CREATE UNIQUE INDEX jvCred_uk_idx ON jiveCredential (userID,artifactID,type);

-- ---------------------------------------------------------------------------

-- jiveFeatured: Store content to be featured in a container

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFeatured (

objectID INTEGER NOT NULL,

objectType INTEGER NOT NULL,

containerID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveFtCtnt_pk PRIMARY KEY (containerID,containerType,objectType,objectID)

);

-- ---------------------------------------------------------------------------

-- jiveFollow: Containers (Projects/Spaces) a user is following

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFollow (

userID INTEGER NOT NULL,

containerID INTEGER NOT NULL,

containerType INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

CONSTRAINT jiveFollow_pk PRIMARY KEY (userID,containerID,containerType)

);

CREATE INDEX jiveFollow_uID ON jiveFollow (userID);

ALTER TABLE jiveFollow ADD CONSTRAINT jiveFollow_uID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jivePermMask: Stores permissions with it's permission mask

-- ---------------------------------------------------------------------------

CREATE TABLE jivePermMask (

name VARCHAR2(255) NOT NULL,

mask INTEGER UNIQUE NOT NULL,

CONSTRAINT jivePermMask_pk PRIMARY KEY (name)

);

-- ---------------------------------------------------------------------------

-- jiveObjectType: Stores the type ID and code of object types

-- ---------------------------------------------------------------------------

CREATE TABLE jiveObjectType (

typeID INTEGER NOT NULL,

code VARCHAR2(255) UNIQUE NOT NULL,

CONSTRAINT jiveObjectType_pk PRIMARY KEY (typeID)

);

-- ---------------------------------------------------------------------------

-- jiveUUID: Stores the type, ID, version and UUID jive objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUUID (

uuid VARCHAR2(128) NOT NULL,

objectID INTEGER NULL,

objectType INTEGER NULL,

version INTEGER NULL,

longID INTEGER UNIQUE NOT NULL,

CONSTRAINT jiveUUID_pk PRIMARY KEY (uuid)

);

CREATE UNIQUE INDEX jiveUUID_jo_uk ON jiveUUID (objectID,objectType,version);

-- ---------------------------------------------------------------------------

-- jiveUUIDExt: Stores UUID to external id mappings

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUUIDExt (

uuid VARCHAR2(128) NOT NULL,

externalIDType VARCHAR2(128) NOT NULL,

externalID VARCHAR2(255) NOT NULL,

CONSTRAINT jiveUUIDExt_pk PRIMARY KEY (uuid,externalIDType,externalID)

);

-- ---------------------------------------------------------------------------

-- jiveMetering: Stores metered resource usage stats

-- ---------------------------------------------------------------------------

CREATE TABLE jiveMetering (

plainDate INTEGER NOT NULL,

encryptedDate VARCHAR2(64) NOT NULL,

component VARCHAR2(255) NOT NULL,

resourceType VARCHAR2(255) NOT NULL,

encryptedUsageAmt VARCHAR2(255) NOT NULL,

lastUpdateTs INTEGER NULL,

isAdjustment INTEGER NOT NULL

);

-- ---------------------------------------------------------------------------

-- jivePageViewConfig: Stores customized configurationd data for page view metering

-- ---------------------------------------------------------------------------

CREATE TABLE jivePageViewConfig (

component VARCHAR2(255) NOT NULL,

lastUpdateTs INTEGER NOT NULL,

configXml CLOB NULL

);

-- ---------------------------------------------------------------------------

-- jiveContent: Table for storing generic content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContent (

contentID INTEGER NOT NULL,

contentType INTEGER NOT NULL,

subject VARCHAR2(255) NULL,

body CLOB NULL,

creationDate INTEGER NULL,

modificationDate INTEGER NULL,

status INTEGER NULL,

userID INTEGER NULL,

containerID INTEGER NULL,

containerType INTEGER NULL,

CONSTRAINT jiveContent_PK PRIMARY KEY (contentID,contentType)

);

CREATE INDEX jvCntnt_ctID_idx ON jiveContent (containerID,containerType);

CREATE INDEX jvCntnt_user_idx ON jiveContent (userID);

-- ---------------------------------------------------------------------------

-- jiveContentProp: Table for storing generic content properties

-- ---------------------------------------------------------------------------

CREATE TABLE jiveContentProp (

contentID INTEGER NOT NULL,

contentType INTEGER NOT NULL,

propName VARCHAR2(255) NOT NULL,

propValue CLOB NOT NULL,

modificationDate INTEGER NULL,

CONSTRAINT jiveContentProp_PK PRIMARY KEY (contentType,contentID,propName)

);

-- ---------------------------------------------------------------------------

-- jiveLicenseReport: Stores license reports.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLicenseReport (

reportID INTEGER NOT NULL,

reportBody CLOB NOT NULL,

reportBodyE CLOB NULL,

reportDate INTEGER NULL,

manual INTEGER NOT NULL,

reporter VARCHAR2(100 char) NULL,

uuid VARCHAR2(100) NOT NULL,

CONSTRAINT jiveLReport_pk PRIMARY KEY (reportID)

);

-- ---------------------------------------------------------------------------

-- jiveLicenseConfirm: Stores license report confirmations.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveLicenseConfirm (

confirmationID INTEGER NOT NULL,

remoteConfirmID INTEGER NULL,

reportID INTEGER NULL,

confirmationDate INTEGER NOT NULL,

confirmationBody CLOB NULL,

confirmationBodyE CLOB NULL,

accepted INTEGER NOT NULL,

uuid VARCHAR2(100) NOT NULL

);

ALTER TABLE jiveLicenseConfirm ADD CONSTRAINT jLC_reportID_fk FOREIGN KEY (reportID) REFERENCES jiveLicenseReport INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveBridgedCont: Table for bridged content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBridgedCont (

bridgedContID INTEGER NOT NULL,

contentID INTEGER NOT NULL,

contentType INTEGER NOT NULL,

origContentID INTEGER NOT NULL,

origContentType INTEGER NOT NULL,

origSubject VARCHAR2(1000) NOT NULL,

origTags VARCHAR2(3500) NOT NULL,

rootMessageID INTEGER NOT NULL,

active INTEGER NOT NULL,

url VARCHAR2(255) NOT NULL,

uuid VARCHAR2(128) NOT NULL,

CONSTRAINT jiveBridgedCont_PK PRIMARY KEY (bridgedContID)

);

CREATE INDEX jiveBridgedHas_idx ON jiveBridgedCont (contentID,contentType);

-- ---------------------------------------------------------------------------

-- jiveBridgedContMsg: Table for bridged content messages

-- ---------------------------------------------------------------------------

CREATE TABLE jiveBridgedContMsg (

bridgedContMsgID INTEGER NOT NULL,

bridgedContID INTEGER NOT NULL,

orderIndex INTEGER NOT NULL,

body CLOB NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

origContentID INTEGER NOT NULL,

origContentType INTEGER NOT NULL,

CONSTRAINT jiveBrdgdCntMsg_PK PRIMARY KEY (bridgedContMsgID)

);

ALTER TABLE jiveBridgedContMsg ADD CONSTRAINT jBridgeContMsg_bdID_fk FOREIGN KEY (bridgedContID) REFERENCES jiveBridgedCont INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveRmtBridgedCont: Table for remote bridged content

-- ---------------------------------------------------------------------------

CREATE TABLE jiveRmtBridgedCont (

rmtBridgedContID INTEGER NOT NULL,

bridgedContID INTEGER NOT NULL,

rmtContentID INTEGER NOT NULL,

rmtContentType INTEGER NOT NULL,

localContentID INTEGER NOT NULL,

localContentType INTEGER NOT NULL,

url VARCHAR2(255) NOT NULL,

uuid VARCHAR2(128) NOT NULL,

CONSTRAINT jiveRmtBrdgdCnt_PK PRIMARY KEY (rmtBridgedContID)

);

CREATE INDEX jiveRmtBrdgHas_idx ON jiveRmtBridgedCont (localContentID,localContentType);

-- ---------------------------------------------------------------------------

-- jiveTermsAndCond: Table for a community's terms and conditions

-- ---------------------------------------------------------------------------

CREATE TABLE jiveTermsAndCond (

body CLOB NULL,

version INTEGER NOT NULL

);

-- ---------------------------------------------------------------------------

-- jiveDVRevision: Table for DocVerse conversion source

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDVRevision (

revisionID INTEGER NOT NULL,

documentID INTEGER NOT NULL,

documentClassID INTEGER NOT NULL,

eviDocID VARCHAR2(50) NULL,

eviVersionID VARCHAR2(50) NULL,

eviPrevVersionID VARCHAR2(50) NULL,

eviFrozen INTEGER DEFAULT 0 NOT NULL,

fileName VARCHAR2(255) NOT NULL,

fileSize INTEGER NOT NULL,

pageCount INTEGER DEFAULT 0 NOT NULL,

revisionNumber INTEGER NOT NULL,

origin INTEGER NOT NULL,

metadata VARCHAR2(3500) NULL,

hashBytes VARCHAR2(255) NULL,

packageID INTEGER NULL,

reconciled INTEGER DEFAULT 1 NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT jiveDVRev_pk PRIMARY KEY (revisionID)

);

CREATE UNIQUE INDEX jiveDVRev_id_idx ON jiveDVRevision (revisionID,documentID);

-- ---------------------------------------------------------------------------

-- jiveDVRevStatus: Table for tracking the DocVerse conversion process

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDVRevStatus (

revisionID INTEGER NOT NULL,

step INTEGER DEFAULT 0 NOT NULL,

modificationDate INTEGER NOT NULL,

message VARCHAR2(3500) NULL

);

CREATE UNIQUE INDEX jiveDVRevSt_uk_idx ON jiveDVRevStatus (revisionID,step);

ALTER TABLE jiveDVRevStatus ADD CONSTRAINT jiveDVRevSt_fk FOREIGN KEY (revisionID) REFERENCES jiveDVRevision INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveDVRevArtifact: Table for tracking DocVerse conversion artifacts

-- ---------------------------------------------------------------------------

CREATE TABLE jiveDVRevArtifact (

instanceID VARCHAR2(255) NOT NULL,

revisionID INTEGER NOT NULL,

artifactType VARCHAR2(20 char) NOT NULL,

page INTEGER NOT NULL,

fileName VARCHAR2(255 char) NULL,

fileSize INTEGER NOT NULL,

contentType VARCHAR2(100) NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL

);

CREATE UNIQUE INDEX jiveDVRvAr_uk_idx ON jiveDVRevArtifact (revisionID,artifactType,page);

ALTER TABLE jiveDVRevArtifact ADD CONSTRAINT jiveDVRvAr_fk FOREIGN KEY (revisionID) REFERENCES jiveDVRevision INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveArchivePolicy: Table for tracking Archival policies associated with jive objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveArchivePolicy (

objectType INTEGER NOT NULL,

objectID INTEGER NOT NULL,

processingDate INTEGER NOT NULL,

visibility INTEGER NOT NULL,

orgContainerID INTEGER NOT NULL,

orgContainerType INTEGER NOT NULL,

destContainerID INTEGER NOT NULL,

destContainerType INTEGER NOT NULL,

userID INTEGER NOT NULL,

status INTEGER NOT NULL

);

-- ---------------------------------------------------------------------------

-- jiveAcclaim: Holds acclaim object information

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAcclaim (

acclaimId INTEGER NOT NULL,

objectType INTEGER NOT NULL,

objectId INTEGER NOT NULL,

acclaimType VARCHAR2(20 char) NOT NULL,

score DECIMAL DEFAULT 0 NOT NULL,

scoreDisplay DECIMAL DEFAULT 0 NOT NULL,

visibility INTEGER DEFAULT 1 NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

CONSTRAINT acclaim_pk PRIMARY KEY (acclaimId)

);

CREATE INDEX acclaim_typeid_idx ON jiveAcclaim (objectType,objectId);

CREATE UNIQUE INDEX acclaim_uk_idx ON jiveAcclaim (objectType,objectId,acclaimType);

-- ---------------------------------------------------------------------------

-- jiveAcclaimProp: Properties for voting objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAcclaimProp (

acclaimId INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT acclaimProp_pk PRIMARY KEY (acclaimId,name)

);

ALTER TABLE jiveAcclaimProp ADD CONSTRAINT aP_acclaimId_fk FOREIGN KEY (acclaimId) REFERENCES jiveAcclaim INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAccContainer: Container and acclaim object map table

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAccContainer (

containerType INTEGER NOT NULL,

containerId INTEGER NOT NULL,

acclaimId INTEGER NOT NULL,

recursive INTEGER NOT NULL,

CONSTRAINT acclaimCC_pk PRIMARY KEY (acclaimId,containerType,containerId,recursive)

);

ALTER TABLE jiveAccContainer ADD CONSTRAINT aCC_acclaimId_fk FOREIGN KEY (acclaimId) REFERENCES jiveAcclaim INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAcclaimVote: Holds user votes for acclaim objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAcclaimVote (

voteId INTEGER NOT NULL,

voterId INTEGER NOT NULL,

acclaimId INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

modificationDate INTEGER NOT NULL,

voteValue INTEGER DEFAULT 0 NOT NULL,

CONSTRAINT acclaimVote_pk PRIMARY KEY (voteId)

);

CREATE INDEX aV_voterType_idx ON jiveAcclaimVote (voterId);

CREATE INDEX aV_acclaim_idx ON jiveAcclaimVote (acclaimId);

CREATE INDEX aV_cDate_idx ON jiveAcclaimVote (creationDate);

ALTER TABLE jiveAcclaimVote ADD CONSTRAINT aV_acclaimId_fk FOREIGN KEY (acclaimId) REFERENCES jiveAcclaim INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveAccVoteProp: Properties for user votes

-- ---------------------------------------------------------------------------

CREATE TABLE jiveAccVoteProp (

voteId INTEGER NOT NULL,

name VARCHAR2(100 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT acclaimVoteProp_pk PRIMARY KEY (voteId,name)

);

ALTER TABLE jiveAccVoteProp ADD CONSTRAINT aVP_voteId_fk FOREIGN KEY (voteId) REFERENCES jiveAcclaimVote INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveUserAnnProp: Announcements dismissed by a user

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserAnnProp (

userID INTEGER NOT NULL,

annID INTEGER NOT NULL,

CONSTRAINT jvUsrAnn_id_pk PRIMARY KEY (userID,annID)

);

CREATE INDEX jvUsrAnn_usr_idx ON jiveUserAnnProp (userID);

CREATE INDEX jvUsrAnn_ann_idx ON jiveUserAnnProp (annID);

-- ---------------------------------------------------------------------------

-- jiveSystemJob: Holds System jobs that are scheduled for execution

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSystemJob (

jobID VARCHAR2(255) NOT NULL,

beanID VARCHAR2(1024) NOT NULL,

userID INTEGER NOT NULL,

creationDate INTEGER NOT NULL,

runDate INTEGER NOT NULL,

nodeID VARCHAR2(512) NULL,

startedOn INTEGER DEFAULT 0 NOT NULL,

completedOn INTEGER DEFAULT 0 NOT NULL,

status INTEGER NOT NULL,

result VARCHAR2(3500) NULL,

CONSTRAINT jiveSysJob_pk PRIMARY KEY (jobID)

);

CREATE INDEX jiveSysJob_rundt ON jiveSystemJob (runDate);

CREATE INDEX jiveSysJob_strtdt ON jiveSystemJob (startedOn);

CREATE INDEX jiveSysJob_cmpldt ON jiveSystemJob (completedOn);

-- ---------------------------------------------------------------------------

-- jiveSystemJobProp: Properties for Task objects

-- ---------------------------------------------------------------------------

CREATE TABLE jiveSystemJobProp (

jobID VARCHAR2(255) NOT NULL,

name VARCHAR2(255 char) NOT NULL,

propValue VARCHAR2(3500 char) NOT NULL,

CONSTRAINT jvSysJobPrp_pk PRIMARY KEY (jobID,name)

);

ALTER TABLE jiveSystemJobProp ADD CONSTRAINT jSJP_jSj_fk FOREIGN KEY (jobID) REFERENCES jiveSystemJob INITIALLY DEFERRED DEFERRABLE;

-- ---------------------------------------------------------------------------

-- jiveThreadLatest: Stores the latest unmoderated message for each thread.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveThreadLatest (

threadId INTEGER NOT NULL,

messageId INTEGER NULL,

messageCount INTEGER NULL,

rootMessageUserId INTEGER NOT NULL,

CONSTRAINT jvthrdLatest_pk PRIMARY KEY (threadId)

);

CREATE INDEX jvthrdLatest_uID ON jiveThreadLatest (rootMessageUserId);

-- ---------------------------------------------------------------------------

-- jiveUserMesgCount: Stores the count of unmoderated message for each user.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveUserMesgCount (

userId INTEGER NOT NULL,

messageCount INTEGER NOT NULL,

CONSTRAINT jvUsrMesgCount_pk PRIMARY KEY (userId)

);

-- ---------------------------------------------------------------------------

-- jiveFormToken: Stores the form tokens for posts that are used to help fix possible CSRF attacks on forms.

-- ---------------------------------------------------------------------------

CREATE TABLE jiveFormToken (

tokenKey VARCHAR2(255) NOT NULL,

tokenValue VARCHAR2(255) NOT NULL,

CONSTRAINT jvFormTkn_pk PRIMARY KEY (tokenKey)

);

-- ---------------------------------------------------------------------------

-- SQL sections

-- ---------------------------------------------------------------------------

-- Default Data.

-- ---------------------------------------------------------------------------

INSERT INTO jiveID (idType, id) VALUES (0, 1000);

INSERT INTO jiveID (idType, id) VALUES (1, 1000);

INSERT INTO jiveID (idType, id) VALUES (2, 1000);

INSERT INTO jiveID (idType, id) VALUES (3, 2000);

INSERT INTO jiveID (idType, id) VALUES (4, 1000);

INSERT INTO jiveID (idType, id) VALUES (13, 1000);

INSERT INTO jiveID (idType, id) VALUES (14, 2000);

INSERT INTO jiveID (idType, id) VALUES (18, 1000);

INSERT INTO jiveID (idType, id) VALUES (19, 1000);

INSERT INTO jiveID (idType, id) VALUES (20, 1000);

INSERT INTO jiveID (idType, id) VALUES (22, 1000);

INSERT INTO jiveID (idType, id) VALUES (23, 1000);

INSERT INTO jiveID (idType, id) VALUES (24, 1000);

INSERT INTO jiveID (idType, id) VALUES (25, 2000);

INSERT INTO jiveID (idType, id) VALUES (26, 1000);

INSERT INTO jiveID (idType, id) VALUES (36, 1000);

INSERT INTO jiveID (idType, id) VALUES (37, 1000);

INSERT INTO jiveID (idType, id) VALUES (38, 1000);

INSERT INTO jiveID (idType, id) VALUES (44, 1000);

INSERT INTO jiveID (idType, id) VALUES (56, 1000);

INSERT INTO jiveID (idType, id) VALUES (57, 1000);

INSERT INTO jiveID (idType, id) VALUES (71, 1000);

INSERT INTO jiveID (idType, id) VALUES (201, 1000);

INSERT INTO jiveID (idType, id) VALUES (202, 1000);

INSERT INTO jiveID (idType, id) VALUES (500, 5000);

INSERT INTO jiveID (idType, id) VALUES (501, 2000);

INSERT INTO jiveID (idType, id) VALUES (502, 6000);

INSERT INTO jiveID (idType, id) VALUES (600, 1000);

INSERT INTO jiveID (idType, id) VALUES (601, 1000);

INSERT INTO jiveID (idType, id) VALUES (602, 1000);

INSERT INTO jiveID (idType, id) VALUES (700, 1000);

INSERT INTO jiveID (idType, id) VALUES (701, 1000);

INSERT INTO jiveID (idType, id) VALUES (2001, 1000);

INSERT INTO jiveID (idType, id) VALUES (2002, 1000);

INSERT INTO jiveID (idType, id) VALUES (42, 1000);

INSERT INTO jiveID (idType, id) VALUES (2020, 1000);

INSERT INTO jiveID (idType, id) VALUES (360, 1000);

INSERT INTO jiveID (idType, id) VALUES (800, 1000);

INSERT INTO jiveID (idType, id) VALUES (801, 1000);

INSERT INTO jiveID (idType, id) VALUES (900, 1000);

INSERT INTO jiveID (idType, id) VALUES (3000, 1000);

INSERT INTO jiveID (idType, id) VALUES (2015, 2000);

INSERT INTO jiveUser (userID,name,username,passwordHash,email,emailVisible,nameVisible,userEnabled,creationDate,modificationDate,lastLoggedIn,status)

VALUES (1,'Administrator','admin','21232f297a57a5a743894a0e4a801fc3','admin@example.com',1,1,1,0,0,1000000000000,6);

INSERT INTO jiveUserContainer (userContainerID, userID, name, displayName, description, creationDate, modificationDate, status) VALUES (1, 1, 'admin', 'Administrator', 'Administrator', 0, 0, 1);

--INSERT INTO jiveUser (userID, username, passwordHash, name, nameVisible, email, emailVisible, userEnabled, creationDate, modificationDate, lastLoggedIn, lastProfileUpdate, isExternal, federated, visible, status)

-- VALUES (2, 'openfire_SHRJKZCNU53', 'INVALID', '', 0, 'no@mail.com', 0, 1, 0, 0, 0, 0, 0, 0, 0, 6);

--INSERT INTO jiveUserContainer (userContainerID, userID, name, displayName, description, creationDate, modificationDate, status) VALUES (2, 2, 'openfire', 'Openfire', 'Openfire', 0, 0, 1);

INSERT INTO jiveUser (userID, username, passwordHash, name, nameVisible, email, emailVisible, userEnabled, creationDate, modificationDate, lastLoggedIn, lastProfileUpdate, isExternal, federated, visible, status)

VALUES (3, 'docverse_SHRJKZCNU53', 'INVALID', '', 0, 'no@mail.com', 0, 1, 0, 0, 0, 0, 0, 0, 0, 6);

INSERT INTO jiveUserContainer (userContainerID, userID, name, displayName, description, creationDate, modificationDate, status) VALUES (3, 3, 'docverse', 'DocVerse', 'DocVerse', 0, 0, 1);

INSERT INTO jiveCommunity (communityID, name, displayName, description, creationDate, modificationDate, lft, rgt, localeCode, workflowID, modDfltThreadVal, modDfltMsgVal, status)

VALUES (1, 'Jive SBS', 'jivesbs', ' ', 0, 0, 1, 2, null, -1, 1, 1, 1);

INSERT INTO jiveCommunityProp (communityID, name, propvalue ) values ( 1, 'contentType.1', 'true' );

INSERT INTO jiveCommunityProp (communityID, name, propvalue ) values ( 1, 'contentType.102', 'true' );

INSERT INTO jiveCommunityProp (communityID, name, propvalue ) values ( 1, 'contentType.37', 'true' );

-- grant admin admin entitlement on the system container

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(1, -6, -2, -1, 1, -1, 511, 0, 0);

-- grant admin admin entitlement on communities within the system

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(2, -6, -2, 14, 1, -1, 511, 0, 0);

-- grant admin admin entitlement on user containers within the system

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(3, -6, -2, 2020, 1, -1, 511, 0, 0);

-- grant admin admin entitlement on groups within the system

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(4, -6, -2, 4, 1, -1, 511, 0, 0);

-- grant defined users admin on their own containers

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(5, 1, 2020, -1, 1, -1, 511, 0, 0);

INSERT INTO jiveEntitlement(entitlementID, objectID, objectType, contentType, userID, groupID, entitlementMask, creationDate, modificationDate)

VALUES(6, 2, 2020, -1, 1, -1, 511, 0, 0);

-- doc, comment and doc body

INSERT INTO jiveID (idType, id) VALUES (102, 1000);

INSERT INTO jiveID (idType, id) VALUES (105, 1000);

INSERT INTO jiveID (idType, id) VALUES (110, 1000);

-- image

INSERT INTO jiveID (idType, id) VALUES (111, 1000);

-- A few necessary properties

-- default filters

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (1, 'boolean');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (2, 'fixed');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (3, 'hidden');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (4, 'jid-multi');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (5, 'jid-single');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (6, 'list-multi');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (7, 'list-single');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (8, 'text-multi');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (9, 'text-private');

INSERT INTO jiveDataType (dataTypeID, dataType) VALUES (10, 'text-single');

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (1,'boolean','Boolean','Example: Yes/No or True/False',1,'com.jivesoftware.community.field.validator.DecimalValidator','com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (2, 'datetime', 'Date/Time', 'Example: Jan 17, 1978 or 9:17pm.', 10,'com.jivesoftware.community.field.validator.DateValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (3, 'decimal', 'Decimal', 'Example: 183.032, 0.993 or 32395.9381.', 10, 'com.jivesoftware.community.field.validator.DecimalValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (4, 'email', 'Email', 'Example: alice@foo.com or bob@bar.org', 10, 'com.jivesoftware.community.field.validator.EmailValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (5, 'number', 'Number', 'Example: 183, 35 or 32,395.', 10, 'com.jivesoftware.community.field.validator.NumberValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (6, 'largetext', 'Large Text Field', 'A paragraph or two of text, up to 3500 characters.', 8, 'com.jivesoftware.community.field.validator.NonValidatingValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (7, 'multilist', 'Multiple Select List', 'A list of options where multiple options can be selected.', 6, 'com.jivesoftware.community.field.validator.NonValidatingValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter',0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (8, 'singlelist', 'Single Select List', 'A list of options where only one option can be selected.', 7, 'com.jivesoftware.community.field.validator.NonValidatingValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter',0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (9, 'text', 'Text Field', 'One or two sentences of text.', 10, 'com.jivesoftware.community.field.validator.NonValidatingValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter',0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (10, 'url', 'URL', 'Example: cnn.com or http://www.yoursite.com/', 10, 'com.jivesoftware.community.field.validator.URLValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveFieldType (fieldTypeID,name,displayName,description,dataTypeID,validator,formatter, modifiable)

VALUES (11, 'custom', 'Custom', 'User defined field, custom display and validation logic.', 10, 'com.jivesoftware.community.field.validator.RegexValidator', 'com.jivesoftware.community.field.validator.NonFormattingFormatter', 0);

INSERT INTO jiveID (idType, id) VALUES (123, 12);

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (0, 'DOCUMENT_FIELD');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (1, 'BODY');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (2, 'SUMMARY');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (3, 'ATTACHMENT');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (4, 'TITLE');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (5, 'LANGUAGE');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (6, 'KEYWORDS');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (7, 'EXPIRATION_DATE');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (8, 'SECTION');

INSERT INTO jiveDocElementType (elementTypeID, name) VALUES (9, 'LABEL');

-- DocTypes

INSERT INTO jiveID (idType, id) VALUES (108, 2);

INSERT INTO jiveDocType (typeID, name, description, presenterID, validator, typeIndex) VALUES (1, 'Default', 'A simple document', null, null, 0);

INSERT INTO jiveDocTypeElement (elementID, documentTypeID, elementTypeID, elementIndex, fRowVersion) VALUES (1, 1, 1, 2, 0);

INSERT INTO jiveDocTypeElement (elementID, documentTypeID, elementTypeID, elementIndex, fRowVersion) VALUES (2, 1, 4, 0, 0);

INSERT INTO jiveDocTypeElement (elementID, documentTypeID, elementTypeID, elementIndex, fRowVersion) VALUES (3, 1, 3, 3, 0);

INSERT INTO jiveDocTypeElement (elementID, documentTypeID, elementTypeID, elementIndex, fRowVersion) VALUES (5, 1, 5, 1, 0);

-- widget

INSERT INTO jiveID (idType, id) VALUES (340, 2000);

INSERT INTO jiveWidget (widgetID, className) VALUES (1000, 'com.jivesoftware.community.widget.impl.CommunityActionsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1001, 'com.jivesoftware.community.widget.impl.SubCommunitiesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1002, 'com.jivesoftware.community.widget.impl.RecentContentWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1003, 'com.jivesoftware.community.widget.impl.RecentDocumentsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1004, 'com.jivesoftware.community.widget.impl.RecentThreadsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1005, 'com.jivesoftware.community.widget.impl.RecentBlogPostsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1006, 'com.jivesoftware.community.widget.impl.PopularDocumentsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1007, 'com.jivesoftware.community.widget.impl.PopularThreadsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1008, 'com.jivesoftware.community.widget.impl.PopularBlogPostsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1009, 'com.jivesoftware.community.widget.impl.TagCloudWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1010, 'com.jivesoftware.community.widget.impl.TopMembersWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1011, 'com.jivesoftware.community.widget.impl.LatestPollWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1012, 'com.jivesoftware.community.widget.impl.RssSubscriptionWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1013, 'com.jivesoftware.community.widget.impl.FormattedTextWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1014, 'com.jivesoftware.community.widget.impl.HtmlTextWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1015, 'com.jivesoftware.community.widget.impl.RecentActivityWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1016, 'com.jivesoftware.community.widget.impl.TagGroupWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1017, 'com.jivesoftware.community.widget.impl.PrivateMessagesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1018, 'com.jivesoftware.community.widget.impl.YourTasksWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1019, 'com.jivesoftware.community.widget.impl.UserWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1020, 'com.jivesoftware.community.widget.impl.TagWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1021, 'com.jivesoftware.community.widget.impl.YourProjectsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1025, 'com.jivesoftware.community.widget.impl.ProjectActionsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1026, 'com.jivesoftware.community.widget.impl.ProjectCalendarWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1027, 'com.jivesoftware.community.widget.impl.CommunityProjectsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1028, 'com.jivesoftware.community.widget.impl.ProjectCheckPointsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1029, 'com.jivesoftware.community.widget.impl.ProjectTasksWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1032, 'com.jivesoftware.community.widget.impl.ProjectStatusWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1034, 'com.jivesoftware.community.widget.impl.MembersListWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1035, 'com.jivesoftware.community.widget.impl.SocialGroupActionsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1036, 'com.jivesoftware.community.widget.impl.SocialGroupOverviewWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1038, 'com.jivesoftware.community.widget.impl.RelatedSpacesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1039, 'com.jivesoftware.community.widget.impl.YourSocialGroupsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1040, 'com.jivesoftware.community.widget.impl.NewestMembersWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1041, 'com.jivesoftware.community.widget.impl.PlacesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1046, 'com.jivesoftware.community.favorites.widget.PopularFavoritesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1047, 'com.jivesoftware.community.favorites.widget.YourFavoritesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1048, 'com.jivesoftware.community.favorites.widget.RecentFavoritesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1049, 'com.jivesoftware.community.widget.impl.QuickTipsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1050, 'com.jivesoftware.community.widget.impl.YourContentWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1051, 'com.jivesoftware.community.widget.impl.DocumentViewWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1052, 'com.jivesoftware.community.widget.impl.UnansweredQuestionsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1053, 'com.jivesoftware.community.widget.impl.FeaturedContentWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1054, 'com.jivesoftware.community.acclaim.widget.TopLikedWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1055, 'com.jivesoftware.community.rating.widget.TopRatedWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1056, 'com.jivesoftware.community.microblogging.widget.PopularWallEntriesWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1057, 'com.jivesoftware.community.microblogging.widget.TrendingTopicsWidget');

INSERT INTO jiveWidget (widgetID, className) VALUES (1058, 'com.jivesoftware.community.microblogging.widget.WallWidget');

-- widget layout

INSERT INTO jiveID (idType, id) VALUES (342, 2000);

INSERT INTO jiveWLayout (layoutID, className) VALUES (1000, 'com.jivesoftware.community.widget.impl.ThreeSmallColumnsLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1001, 'com.jivesoftware.community.widget.impl.OneSmallOneLargeOneSmallColumnLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1002, 'com.jivesoftware.community.widget.impl.OneLargeOneSmallOneSmallColumnLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1003, 'com.jivesoftware.community.widget.impl.TwoColumnLargeLeftColumnLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1004, 'com.jivesoftware.community.widget.impl.TwoColumnLargeRightColumnLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1005, 'com.jivesoftware.community.widget.impl.TwoColumnLargeRightLargeLeftColumnLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1006, 'com.jivesoftware.community.widget.impl.OneColumnLargeLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1007, 'com.jivesoftware.community.widget.impl.OneTopTwoLargeLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1008, 'com.jivesoftware.community.widget.impl.TwoLargeOneBottomLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1009, 'com.jivesoftware.community.widget.impl.OneTopThreeSmallLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1010, 'com.jivesoftware.community.widget.impl.ThreeSmallOneBottomLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1011, 'com.jivesoftware.community.widget.impl.OneTopTwoLargeOneBottomLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1012, 'com.jivesoftware.community.widget.impl.OneTopThreeSmallOneBottomLayout');

INSERT INTO jiveWLayout (layoutID, className) VALUES (1013, 'com.jivesoftware.community.widget.impl.OneTopOneLargeOneSmallLayout');

-- Create an ID for relationship graphs

INSERT INTO jiveID (idType, id) VALUES (50, 2000);

-- Create default IDs for the graphs

INSERT INTO jiveProperty (name, propValue) VALUES ('ur.graph.hier.default.ID', '1000');

INSERT INTO jiveProperty (name, propValue) VALUES ('ur.graph.mesh.default.ID', '1001');

-- no content conversion required

INSERT INTO jiveProperty (name, propValue) VALUES ('jive.bodyConversionTask.completed', 'true');

-- use first and last name columns of jiveUser by default

INSERT INTO jiveProperty (name, propValue) VALUES ('jive.user.lastname.firstname.enabled', 'true');

-- use full-name as sender in outgoing notification emails

-- INSERT INTO jiveProperty (name, propValue) VALUES ('email.fromaddress.username', 'false');

-- turn off trackbacks by default

INSERT INTO jiveProperty (name, propValue) VALUES ('blog.trackbacks.enabled', 'false');

-- widget frame

INSERT INTO jiveID (idType, id) VALUES (341, 2000);

-- default to threaded discussions on new installs

INSERT INTO jiveProperty (name, propValue) VALUES ('skin.default.threadMode', 'threaded');

INSERT INTO jiveProperty (name, propValue) VALUES ('skin.default.commentThreadMode', 'flat');

-- default values for login throttling and captcha

INSERT INTO jiveProperty (name, propValue) VALUES ('login.throttle.enabled', 'true');

INSERT INTO jiveProperty (name, propValue) VALUES ('login.throttle.delay', '10');

INSERT INTO jiveProperty (name, propValue) VALUES ('login.throttle.maxContinuousAttempts', '3');

INSERT INTO jiveProperty (name, propValue) VALUES ('login.captcha.enabled', 'true');

INSERT INTO jiveProperty (name, propValue) VALUES ('login.captcha.size', '6');

-- Admin wizard property

INSERT INTO jiveProperty (name,propValue) VALUES ('admin.wizard.completed','false');

-- status levels

INSERT INTO jiveStatusLevel (statusLevelID, name, imagePath, minPoints, maxPoints, groupID) VALUES (1000,'Newbie','/images/status/statusicon-47.gif',0,100,-1);

INSERT INTO jiveStatusLevel (statusLevelID, name, imagePath, minPoints, maxPoints, groupID) VALUES (1001,'Novice','/images/status/statusicon-48.gif',101,500,-1);

INSERT INTO jiveStatusLevel (statusLevelID, name, imagePath, minPoints, maxPoints, groupID) VALUES (1002,'Apprentice','/images/status/statusicon-49.gif',501,1500,-1);

INSERT INTO jiveStatusLevel (statusLevelID, name, imagePath, minPoints, maxPoints, groupID) VALUES (1003,'Expert','/images/status/statusicon-50.gif',1501,3000,-1);

INSERT INTO jiveStatusLevel (statusLevelID, name, imagePath, minPoints, maxPoints, groupID) VALUES (1004,'Master','/images/status/statusicon-51.gif',3001,-1,-1);

--| SBS EXT |

--------------

INSERT INTO jiveExtContext (contextId, type, name) VALUES (0, -1, 'system');

-- | ENTITLEMENTS |

-- ----------------

-- Permission Levels start at 1000

INSERT INTO jiveID (idType, id) VALUES (370, 1000);

-- Object Permission Levels start at 1000

INSERT INTO jiveID (idType, id) VALUES (371, 1000);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (-2, 'No Access', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (0, 'No Access', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (1, 'Administer', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (2, 'Moderate', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (3, 'Create', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (4, 'Contribute', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (5, 'View', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (6, 'Full Access', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (7, 'Manage System', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (8, 'Moderate Content', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (9, 'Manage Users', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (10, 'Manage Groups', 1);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (11, 'Create Private Message', 2);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (12, 'Attach Private Message', 2);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (13, 'Read Blog', 3);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (14, 'Create Blog', 3);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (15, 'Comment Blog', 3);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (16, 'Attach Blog', 3);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (17, 'Image Blog', 3);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (18, 'Read Social Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (19, 'Create Public Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (20, 'Create Private Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (21, 'Attach Social Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (22, 'Image Social Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (23, 'Create Announcement', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (24, 'Create Poll', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (25, 'Vote Poll', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (26, 'Create Video', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (27, 'Rate Video', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (28, 'Comment Video', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (29, 'iPhone Access', 6);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (30, 'iDiscuss', 0);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (31, 'Manage Social Group', 4);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (32, 'Create Status', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (33, 'Rate Status', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (34, 'Comment Status', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (35, 'Read Status', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (36, 'Insert Status Image', 5);

INSERT INTO jivePermLevel (permLevelId, name, system) VALUES (37, 'Insert Comment Image', 5);

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (0, 'Read');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (1, 'Create');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (2, 'Moderate');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (3, 'Administer');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (4, 'Insert image');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (5, 'Rate');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (6, 'Vote');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (7, 'Comment/reply');

INSERT INTO jiveEntitlementMsk (mask, name) VALUES (8, 'Attach file');

-- CONSOLE

-- -------

INSERT INTO jiveObjPermLvl (objPermLevelId, name, lvlType) VALUES (18, 'Administer', 'system');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (18, 0, 0);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 18);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (7, 18);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (18, 3);

-- Full Access

-- admin system

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, -2);

-- admin spaces

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, 14);

-- moderate spaces

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 2, 14);

-- admin user container

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, 2020);

-- admin groups

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, 4);

-- Manage System

-- It should be noted that manage system manifests itself as read on the system type. This is basically

-- to distinguish it from full access but not grant them extra privleges they wouldn't otherwise have.

-- read system

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (7, 0, -2);

-- admin groups

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (7, 3, 4);

-- admin user container

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (7, 3, 2020);

-- Moderate Content

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (8, 2, 14);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (8, 2, 20);

-- Manage Users

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (9, 3, 2020);

-- Manage Groups

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (10, 3, 4);

-- SPACE

-- -----

-- MODERATE and FULL CONTROL on a space set these object permission levels which trigger

-- logic in the listeners to provide the proper entitlements and kick off the workflow for moderate.

-- Also, it allows us to map these settings back to custom permission levels in the interface.

-- FULL CONTROL permission level grants access to administer any space to those with Full Access

-- and those who have been granted administer on a space

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (16, 'Full Control', 14,

'manage');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (16, 0, 0);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 16);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 16);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (16, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (16, 1);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (16, 3);

-- MODERATE permision level grants access to moderate a space

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (24, 'Moderate', 14,

'manage');

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 24);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (24, 2);

-- DEFAULT view permission on space - if users get granted any permission on a space they get view

-- permission by default

INSERT INTO jiveObjPermLvl (objPermLevelId, name) VALUES (19, 'View Space');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (19, 0, 0);

INSERT INTO jiveDefObjPermLvl (objPermLevelId, objType) VALUES (19, 14);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (19, 0);

-- Manage System Gets View Space

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (26, 'View Space', 14, 'admin');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (26, 0, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (26, 0);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (7, 26);

-- PROFILE IMAGE

-- --------------

-- MODERATE permision level grants access to moderate a profile image

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (25, 'Moderate', 501,

'manage');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (25, 0, 0);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (8, 25);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (25, 2);

-- EXTERNAL URL

-- -------------

-- MODERATE permision level grants access to moderate an external link

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (27, 'Moderate', 801,

'manage');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (27, 0, 0);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (8, 27);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (27, 2);

-- SOCIAL GROUPS

-- -------------

-- Full Access and Manage System get the ability to create, administer, and view social groups by

-- default

-- VIEW

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 0, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 0, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (18, 0, 700);

-- CREATE PUBLIC

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 1, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (19, 1, 700);

-- CREATE PRIVATE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 702);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 1, 702);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (20, 1, 702);

-- IMAGE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 4, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 4, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (22, 4, 700);

-- ATTACH FILE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 8, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 8, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (21, 8, 700);

-- MANAGE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, 700);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (31, 3, 700);

-- PRIVATE MESSAGES

-- -----------------

-- CREATE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 20);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (11, 1, 20);

-- ATTACH FILE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 8, 20);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (12, 8, 20);

-- BLOGS

-- -----------------

-- VIEW

-- View Blog

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 0, 37);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (13, 0, 37);

-- View Blog Post

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 0, 38);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (13, 0, 38);

-- CREATE

-- Create Blog

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 37);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (14, 1, 37);

-- Create Blog Post

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 38);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (14, 1, 38);

-- IMAGE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 4, 38);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (17, 4, 38);

-- COMMENT

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 7, 38);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (15, 7, 38);

-- ATTACH FILE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 8, 38);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (16, 8, 38);

-- ADMINISTER

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 3, 37);

-- HOME PAGE

-- -----------------

-- CREATE SYSTEM ANNOUNCEMENTS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 22);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (23, 1, 22);

-- CREATE SYSTEM POLLS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 18);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (24, 1, 18);

-- VOTE SYSTEM POLLS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 6, 18);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (25, 6, 18);

-- CREATE SYSTEM VIDEO

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 1100);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (26, 1, 1100);

-- RATE SYSTEM VIDEO

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 5, 1100);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (27, 5, 1100);

-- COMMENT SYSTEM VIDEO

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 7, 1100);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (28, 7, 1100);

-- VIEW STATUS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 0, 1464927464);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (35, 0, 1464927464);

-- CREATE STATUS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 1464927464);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (32, 1, 1464927464);

-- RATE STATUS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 5, 1464927464);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (33, 5, 1464927464);

-- COMMENT STATUS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 7, 1464927464);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (34, 7, 1464927464);

-- INSERT STATUS IMAGE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 4, 1464927464);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (36, 4, 1464927464);

-- INSERT COMMENT IMAGE

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 4, 105);

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (37, 4, 105);

-- MOBILE

-- -----------------

-- IPHONE ACCESS

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (29, 1, 1200);

-- PERMISSION LEVELS

-- -----------------

-- Admin can create and administer permission levels

INSERT INTO jiveEntitlement (entitlementId, objectType, objectId, userId, groupId, contentType,

entitlementMask, creationDate, modificationDate) VALUES (7, -2, 17, 1, -1, 370, 11, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (17, 'Administer', 370,

'system');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (17, 0, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (17, 3);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (17, 1);

-- Full Access

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 17);

-- Manage System

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (7, 17);

-- ANNOUNCEMENTS

-- -------------

-- DEFAULT read announcement on space

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (20, 'View Announcement', 22);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (20, 0, 0);

INSERT INTO jiveDefObjPermLvl (objPermLevelId, objType) VALUES (20, 14);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (20, 0);

-- create

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (23, 'Create Announcements',

22, 'bundled');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (23, 0, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (23, 1);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 23);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 23);

-- PROJECTS

-- --------

-- DEFAULT read project on space

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (21, 'View Project', 600);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (21, 0, 0);

INSERT INTO jiveDefObjPermLvl (objPermLevelId, objType) VALUES (21, 14);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (21, 0);

-- create

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 1, 600);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType, lvlType) VALUES (22, 'Create Projects', 600,

'bundled');

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (22, 0, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (22, 1);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 22);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 22);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (3, 22);

-- TASKS

-- -----

-- view - Only 'System Admin' can view all tasks globally - 'Manage System' cannot. CS-20916

INSERT INTO jivePermLvlMsk (permLevelId, mask, objType) VALUES (6, 0, 602);

-- DISCUSSIONS

-- -----------

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (1, 'Create', 1);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (1, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (2, 'Contribute', 1);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (2, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (3, 'View', 1);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (3, 0, 0);

-- Administer

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 1);

-- Moderate

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 1);

-- Create

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (3, 1);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (30, 1);

-- Contribute

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (4, 2);

-- View

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (5, 3);

-- Full Access

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 1);

-- Create

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 1);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (1, 5);

-- Contribute

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (2, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (2, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (2, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (2, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (2, 5);

-- View

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (3, 0);

-- All available masks

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 0);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 1);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 7);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 8);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 4);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (1, 5);

-- DOCUMENTS

-- -----------

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (4, 'Create', 102);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (4, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (5, 'Contribute', 102);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (5, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (6, 'View', 102);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (6, 0, 0);

-- Administer

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 4);

-- Moderate

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 4);

-- Create

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (3, 4);

-- Contribute

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (4, 5);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (30, 5);

-- View

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (5, 6);

-- Full Access

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 4);

-- Create

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 1);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (4, 5);

-- Contribute

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (5, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (5, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (5, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (5, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (5, 5);

-- View

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (6, 0);

-- All available masks

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 0);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 1);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 7);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 8);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 4);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (102, 5);

-- BLOGS

-- -----------

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (7, 'Create', 38);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (7, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (8, 'Contribute', 38);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (8, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (9, 'View', 38);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (9, 0, 0);

-- Administer

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 7);

-- Moderate

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 7);

-- Create

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (3, 7);

-- Contribute

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (4, 8);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (30, 8);

-- View

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (5, 9);

-- Full Access

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 7);

-- Create

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 1);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (7, 5);

-- Contribute

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (8, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (8, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (8, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (8, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (8, 5);

-- View

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (9, 0);

-- All available masks

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 0);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 1);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 7);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 8);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 4);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (38, 5);

-- POLLS

-- -----------

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (10, 'Create', 18);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (10, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (11, 'Contribute', 18);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (11, 0, 0);

INSERT INTO jiveObjPermLvl (objPermLevelId, name, objType) VALUES (12, 'View', 18);

INSERT INTO jiveObjPermLvlExt (objPermLevelId, creationDate, extender) VALUES (12, 0, 0);

-- Administer

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (1, 10);

-- Moderate

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (2, 10);

-- Create

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (3, 10);

-- Contribute

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (4, 11);

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (30, 11);

-- View

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (5, 12);

-- Full Access

INSERT INTO jiveObjPermLvlMap (permLevelId, objPermLevelId) VALUES (6, 10);

-- Create

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 1);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 6);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (10, 5);

-- Contribute

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 0);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 7);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 8);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 4);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 6);

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (11, 5);

-- View

INSERT INTO jiveObjPermLvlMsk (objPermLevelId, mask) VALUES (12, 0);

-- All available masks

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 0);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 1);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 7);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 8);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 4);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 6);

INSERT INTO jiveObjTypeMsk (objType, mask) VALUES (18, 5);

-- Grant Full Access perm level to system admin

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (6, 3, 1, -2, -6);

-- Grant view perm level to anonymous users for root space

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (5, 4, -3, 14, 1);

-- Grant view perm levels to anonymous users for system level blogs and social groups

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (13, 4, -3, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (18, 4, -3, -2, 17);

-- Grant create perm level to registered users for private messages

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (11, 4, -2, -2, 17);

-- HOME PAGE PERMS for Registered Users - Vote in Poll, Create/Rate/Comment Video,

-- Read/Create/Rate/Comment Status updates

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (25, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (26, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (27, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (28, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (32, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (33, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (34, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (36, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (35, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (35, 4, -3, -2, 17);

-- Insert image into comments

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (37, 4, -2, -2, 17);

-- Category/TagSet defaults

INSERT INTO jiveProperty (name, propValue) VALUES ('jive.container.category.max', '25');

INSERT INTO jiveProperty (name, propValue) VALUES ('jive.permission.upgrade.completed', 'true');

-- Disable office integration by default

INSERT INTO jiveProperty (name, propValue) VALUES ('officeintegration.enabled', 'false');

-- Upgrade task versions

INSERT INTO jiveVersion (name, version) VALUES ('clearspace', 42000112);

INSERT INTO jiveVersion (name, version) VALUES ('development', 42000016);

-- Jive SBS Data

-- ---------------------------------------------------------------------------

-- Starting Jive SBS specific section

-- Profile security levels

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1000, 'prof.sec.level.everyone.internal', 'allUsersProfileSecurityStrategy',0);

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1001, 'prof.sec.level.reguser.internal', 'regUsersProfileSecurityStrategy', 1);

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1002, 'prof.sec.level.colleagues.internal', 'colleaguesProfileSecurityStrategy', 1);

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1003, 'prof.sec.level.connections.internal', 'connectionsProfileSecurityStrategy', 1);

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1004, 'prof.sec.level.connandcoll.internal', 'connectionsAndColleaguesProfileSecurityStrategy', 1);

INSERT INTO jiveProfileSecLvl(levelID, nameKey, strategyName, enabled) VALUES (1005, 'prof.sec.level.owner.internal', 'ownerProfileSecurityStrategy', 1);

-- Profile fields

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 1, 'Title', 0, 1, 1, 1, 1, 1, 9, 0, 1, 0, 0, 0, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 2, 'Department', 0, 1, 1, 1, 1, 1, 9, 1, 1, 0, 0, 1, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 3, 'Address', 0, 0, 1, 1, 1, 1, 14, 2, 1, 0, 0, -1, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 4, 'Phone Number', 0, 0, 0, 1, 1, 1, 11, 3, 1, 0, 0, 2, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 5, 'Home Phone Number', 0, 0, 0, 1, 1, 1, 11, 4, 1, 0, 0, -1, 1003);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 6, 'Mobile Phone Number', 0, 0, 0, 1, 1, 1, 11, 5, 1, 0, 0, 3, 1003);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 7, 'Hire Date', 0, 1, 1, 1, 1, 1, 2, 6, 1, 0, 0, -1, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 8, 'Biography', 0, 0, 1, 1, 1, 1, 6, 7, 1, 0, 0, 4, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES ( 9, 'Expertise', 0, 0, 1, 1, 1, 1, 6, 8, 1, 0, 0, 5, 1001);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES (10, 'Alternate Email', 0, 0, 0, 1, 1, 1, 4, 9, 1, 0, 0, -1, 1003);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES (11, 'Home Address', 0, 0, 1, 1, 1, 1, 14, 10, 1, 0, 0, -1, 1003);

INSERT INTO jiveProfileField (fieldID, name, isRequired, isFilterable, isSearchable, isVisibleToUsers, isVisibleToGuests, isEditable, fieldType, idx, isDefault, extManaged, isList, regIdx, defaultSecLvlID) VALUES (12, 'Location', 0, 1, 1, 1, 1, 1, 9, 11, 1, 0, 0, -1, 1001);

-- Profile field security levels

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (1, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (1, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (1, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (1, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (1, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (2, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (2, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (2, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (2, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (2, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (3, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (3, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (3, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (3, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (3, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (4, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (4, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (4, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (4, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (4, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (5, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (5, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (5, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (5, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (5, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (6, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (6, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (6, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (6, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (6, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (7, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (7, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (7, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (7, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (7, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (8, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (8, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (8, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (8, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (8, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (9, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (9, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (9, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (9, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (9, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (10, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (10, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (10, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (10, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (10, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (11, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (11, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (11, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (11, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (11, 1005);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (12, 1001);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (12, 1002);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (12, 1003);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (12, 1004);

INSERT INTO jiveProfileFldSec (fieldID, levelID) VALUES (12, 1005);

-- Default Ratings

INSERT INTO jiveRatingType (score, description) VALUES (1, 'Poor');

INSERT INTO jiveRatingType (score, description) VALUES (2, 'Below Average');

INSERT INTO jiveRatingType (score, description) VALUES (3, 'Average');

INSERT INTO jiveRatingType (score, description) VALUES (4, 'Above Average');

INSERT INTO jiveRatingType (score, description) VALUES (5, 'Exceptional');

-- A few necessary properties

-- default filters

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (1, 'stslvl.scen.message_added.text', 'messageAdded', 1, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (2, 'stslvl.scen.answered_qstns.text', 'correctAnswerAdded', 4, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (3, 'stslvl.scen.helpfulRespons.text', 'helpfulAnswerAdded', 2, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (4, 'stslvl.scen.wiki_doc_added.text', 'documentAdded', 4, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (5, 'stslvl.scen.blogPostAdded.text', 'blogpostAdded', 3, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (6, 'stslvl.scen.taskCompleted.text', 'taskCompleted', 1, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (8, 'stslvl.scen.wallEntryAdded.text', 'wallEntryAdded', 1, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (9, 'stslvl.scen.wallEntryLiked.text', 'wallEntryLiked', 1, 1);

INSERT INTO jiveStatLvlScen (scenarioID, i18nKey, code, points, enabled)

VALUES (10, 'stslvl.scen.wallEntryShared.text', 'wallEntryShared', 1, 1);

-- Disallow Guest Access

INSERT INTO jiveProperty (name, propValue) VALUES ('jive.auth.disallowGuest', 'true');

-- Protect Feeds

INSERT INTO jiveProperty (name, propValue) VALUES ('feeds.protected', 'true');

-- Show full name instead of username

INSERT INTO jiveProperty (name, propValue) VALUES ('skin.default.displayFullNames', 'true');

-- Limit user settings on non-profile fields for name, email

INSERT INTO jiveProperty (name, propValue) VALUES ('__nameProfileSecurityLevelOptions', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('__emailProfileSecurityLevelOptions', '1001');

-- Set security level defaults for non-profile fields

INSERT INTO jiveProperty (name, propValue) VALUES ('user.nameProfileSecurityLevel', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('user.emailProfileSecurityLevel', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('user.imageProfileSecurityLevel', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('user.statusLevelProfileSecurityLevel', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('user.socialGroupProfileSecurityLevel', '1001');

INSERT INTO jiveProperty (name, propValue) VALUES ('user.tagProfileSecurityLevel', '1001');

-- Create a default org chart graph and soc conx graph

INSERT INTO jiveUserRelGraph (graphID, graphTypeID, name, enabled, labelManager, labelPeer, labelDirRpt, approvalsEnabled, peerChoiceAllowed, mgrChoiceAllowed, drptChoiceAllowed, reflexive) VALUES (1000,1,'profile.gr.org.title',1,'profile.gr.org.mgr','profile.gr.org.peer','profile.gr.org.drpt',0,0,0,1,1);

INSERT INTO jiveUserRelGraph (graphID, graphTypeID, name, enabled, labelPeer, approvalsEnabled, peerChoiceAllowed, mgrChoiceAllowed, drptChoiceAllowed, reflexive) VALUES (1001,2,'profile.gr.conx.title',1,'profile.gr.conx.peer',0,1,0,0,0);

-- Allow users to choose an avatar

INSERT INTO jiveUserPerm (objectType,objectID,userID,permissionType,permission) VALUES (17,-1,0,1,13);

-- Allow custom avatars

INSERT INTO jiveProperty (name,propValue) VALUES ('avatars.customEnabled','true');

INSERT INTO jiveProperty (name,propValue) VALUES ('avatars.maxUserAvatars','5');

INSERT INTO jiveProperty (name,propValue) VALUES ('avatars.modUserAvatars','false');

-- Create default spaces

UPDATE jiveID SET id='2003' WHERE idType='14';

INSERT INTO jiveCommunity (communityID,name,displayName,description,creationDate,modificationDate,lft,rgt,localeCode,workflowID,modDfltThreadVal,modDfltMsgVal,status) VALUES

(2001,'Getting Started','getting-started','New to Jive SBS? Start here to learn how to get the most out of it.',1229124377717,1229124378597,2,3,NULL,-1,1,1,1);

INSERT INTO jiveCommunity (communityID,name,displayName,description,creationDate,modificationDate,lft,rgt,localeCode,workflowID,modDfltThreadVal,modDfltMsgVal,status) VALUES

(2002,'Water Cooler','water-cooler','A place for topics that don''t seem to fit anywhere else.',1229124431735,1229124431864,4,5,NULL,-1,1,1,1);

UPDATE jiveCommunity SET rgt=6 WHERE communityID = '1';

-- Establish entitlement inheritance for default spaces

INSERT INTO jiveEntlmntInherit(containerType, containerId) VALUES (14, 2001);

INSERT INTO jiveEntlmntInherit(containerType, containerId) VALUES (14, 2002);

-- Grant blog perm levels to registered users for system level blogs and social groups

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (13, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (14, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (15, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (16, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (17, 4, -2, -2, 17);

-- Grant create perm level to registered users for root space

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (3, 4, -2, 14, 1);

-- SOCIAL GROUP PERMS for Registered Users - View, Create, etc.

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (18, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (19, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (20, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (21, 4, -2, -2, 17);

INSERT INTO jiveGrantedPermLvl(permLevelId, grantedObjectType, grantedId, targetObjectType,

targetObjectId) VALUES (22, 4, -2, -2, 17);

Code

2010-01-12T10:42:00.000-08:00

EffectiveEntitlement entitlement = new EffectiveUserEntitlement(AnonymousUser.ANONYMOUS_ID, blog,

Entitlement.MODERATE, Entitlement.READ, Entitlement.WRITE);

*****************************************************************************************************************************************

/**

* Immutable expression of user entitlement within the system for a specific

* object. Of note is that this object exposes object type and content type - the

* object type is the object for which the entitlements have been captured. The

* content type is the child content to which the entitlement applies, possibly

* none in which case the entitlement applies to all content types.

* @Since 2.1

public class EffectiveEntitlement implements ExternalizableLite {

private static final long serialVersionUID = -1938147856787179824L;

public static final int NONE = -1;

private static final int cacheSize =

CacheSizes.sizeOfInt() + //mask

CacheSizes.sizeOfInt() + //content type

CacheSizes.sizeOfLong() + //user id

CacheSizes.sizeOfLong() + //group id

CacheSizes.sizeOfLong() + //object id

CacheSizes.sizeOfInt() + //object type

CacheSizes.sizeOfInt() ; //hash code

private int entitlementMask = 0;

private long userId = 0;

private long groupId = 0;

private long objectId = 0;

private int objectType = 0;

private int contentType = 0;

private int hashCode = 0;

public EffectiveEntitlement(long userId, long groupId, long objectId, int objectType, int contentType, int entitlementMask) {

super();

this.entitlementMask = entitlementMask;

this.userId = userId;

this.groupId = groupId;

this.objectId = objectId;

this.objectType = objectType;

this.contentType = contentType;

setHash();

}

public EffectiveEntitlement(long userId, long groupId, long objectId, int objectType, int contentType, Entitlement entitlement) {

this(userId, groupId, objectId, objectType, contentType, entitlement.getID());

}

public EffectiveEntitlement(long userId, long groupId, long objectId, int objectType, int contentType, Entitlement... entitlements) {

super();

int mask = 0;

if(null != entitlements && entitlements.length != 0) {

for(Entitlement ent : entitlements) {

mask |= ent.getID();

}

this.entitlementMask = mask;

this.userId = userId;

this.groupId = groupId;

this.objectId = objectId;

this.objectType = objectType;

this.contentType = contentType;

setHash();

}

/**

* Create a new entitlement based on a deep copy of an existing entitlement representation.

* @param ee

public EffectiveEntitlement(EffectiveEntitlement ee) {

this(ee.getUserID(), ee.getGroupID(), ee.getObjectID(), ee.getObjectType(), ee.getContentType(), ee.getEntitlementMask());

}

/**

* Creates a new effective entitlement for the given user id, copying the remainder

* of the entitlement from the specified data.

* @param ee - Entitlement to use as the source of the new entitlement data.

* @param userID - UserID to apply to the new entitlement.

public EffectiveEntitlement(EffectiveEntitlement ee, long userID) {

this(userID, ee.getGroupID(), ee.getObjectID(), ee.getObjectType(), ee.getContentType(), ee.getEntitlementMask());

}

public long getUserID() {

return userId;

}

public long getGroupID() {

return groupId;

}

public long getObjectID() {

return objectId;

}

public int getObjectType() {

return objectType;

}

public int getContentType() {

return contentType;

}

public int getEntitlementMask() {

return entitlementMask;

}

public boolean isAuthorized(Entitlement toCheck) {

return (this.entitlementMask & toCheck.getID()) != 0;

}

public boolean isAuthorized(int mask) {

return (this.entitlementMask & mask) != 0;

}

public boolean isAuthorized(Entitlement... ents) {

int localMask = 0;

if(ents != null) {

for(Entitlement ent : ents) {

localMask |= ent.getID();

}

return (localMask & this.entitlementMask) != 0;

}

@Override

public String toString() {

if(userId <>

if(groupId == RegisteredUsersGroup.REGISTERED_USERS_GROUP_ID) {

if(contentType == EffectiveEntitlement.NONE) {

return String.format("[reg-users][oid:%s/%s]",

objectId, objectType);

}

else {

return String.format("[reg-users][oid:%s/%s][%s]",

objectId, objectType, contentType);

}

else if(groupId == -1) {

if(contentType == EffectiveEntitlement.NONE) {

return String.format("[anonymous][oid:%s/%s]",

objectId, objectType);

}

else {

return String.format("[anonymous][oid:%s/%s][%s]",

objectId, objectType, contentType);

}

else {

if(contentType == EffectiveEntitlement.NONE) {

return String.format("[group:%s][oid:%s/%s]",

groupId, objectId, objectType);

}

else {

return String.format("[group:%s][oid:%s/%s][%s]",

groupId, objectId, objectType, contentType);

}

else {

if(contentType == EffectiveEntitlement.NONE) {

return String.format("[user:%s][oid:%s/%s]",

userId, objectId, objectType);

}

else {

return String.format("[user:%s][oid:%s/%s][%s]",

userId, objectId, objectType, contentType);

}

@Override

public boolean equals(Object rhs0) {

if(null == rhs0) return false;

EffectiveEntitlement rhs = (EffectiveEntitlement)rhs0;

return (

this.entitlementMask == rhs.entitlementMask &&

this.userId == rhs.userId &&

this.groupId == rhs.groupId &&

this.objectId == rhs.objectId &&

this.objectType == rhs.objectType &&

this.contentType == rhs.contentType);

}

@Override

public int hashCode() {

return hashCode;

}

public void readExternal(DataInput in) throws IOException {

this.entitlementMask = in.readInt();

this.userId = in.readLong();

this.groupId = in.readLong();

this.objectId = in.readLong();

this.objectType = in.readInt();

this.contentType = in.readInt();

this.hashCode = in.readInt();

}

public void writeExternal(DataOutput out) throws IOException {

out.writeInt(this.entitlementMask);

out.writeLong(this.userId);

out.writeLong(this.groupId);

out.writeLong(this.objectId);

out.writeInt(this.objectType);

out.writeInt(this.contentType);

out.writeInt(this.hashCode);

}

public int getCacheSize() {

return cacheSize;

}

private void setHash() {

final long longValue = (this.entitlementMask * 2) + (this.userId * 3) + (this.objectId * 11) +

(this.objectType * 13) + (this.groupId * 17) + (this.contentType * 19);

hashCode = (int)(longValue^(longValue >>>32));

}

*************************************************************************************************************************************************

public enum Entitlement {

NONE(0x0),

READ(0x1),

WRITE(0x2),

MODERATE(0x4),

ADMINISTER(0x8);

private final int entitlementId;

Entitlement(int entitlementId) {

this.entitlementId = entitlementId;

}

public int getID() {

return this.entitlementId;

}

public int value() {

return this.entitlementId;

}

2009-12-28T10:48:00.000-08:00

/*
* AbstractPdfStamperView.java
* Created to make the process of merging field data on an AcroForm easy to do in Spring.
*
* @author K. Bryant Larsen
*/

package edu.utah.oit.osl.controller.web.view;

import com.lowagie.text.pdf.PdfReader;
import com.lowagie.text.pdf.PdfStamper;
import java.io.ByteArrayOutputStream;
import java.net.URL;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.view.AbstractView;

/**
* This abstract class is intended to be used for the merging of
* existing PDF forms with data and will write the PDF to the response.
* Similar to Springs AbstractPdfView except there is no document in this version.
* Created on March 26, 2008, 12:09 PM
* @author Bryant Larsen Media Solutions The University of Utah
*/
public abstract class AbstractPdfStamperView extends AbstractView {

private String url;
private static final int OUTPUT_BYTE_ARRAY_INITIAL_SIZE = 1024;

public AbstractPdfStamperView(){
super();
setContentType("application/pdf");
}

/**This is the method that will put all of the peices together and send the final output down to the response.
* @param model A Map that contains the model data for this instance
* @param request The request that will be handed to this view.
* @param response Contains the byte array outpu stream to output to the actuall browser.
*/
protected final void renderMergedOutputModel(Map model, HttpServletRequest request, HttpServletResponse response) throws Exception{
//Hold our output in a temporary output stream.
ByteArrayOutputStream tempOutputStream = new ByteArrayOutputStream(AbstractPdfStamperView.OUTPUT_BYTE_ARRAY_INITIAL_SIZE);
//Get the file from the filesystem.
PdfReader reader = new PdfReader(this.getServletContext().getResourceAsStream(this.getUrl()));
//Get a PdfStamper instance that will give us access to the AcroField
PdfStamper stamper = new PdfStamper(reader, tempOutputStream);
//Give the user the chance to merge the form fields.
mergePdfDocument(model, stamper, request, response);
//Set various settings on the stamper object.
//Example, stamper.setFormFlattening(true);
prepareStamper(stamper);
//Close the stamper
stamper.close();
// Write content type and also length(determined via byte array).
response.setContentType(getContentType());
response.setContentLength(tempOutputStream.size());

// Flush byte array to servlet output stream.
ServletOutputStream out = response.getOutputStream();
tempOutputStream.writeTo(out);
out.flush();
}

/**
* Abstract method that subclasses must implement.

* This is where you are able to set values on the AcroForm.

* An example of what can be done at this level is:

* //get the form from the document:

* AcroFields form = stamper.getAcroFields();

*

* //Set some values on the form.

* form.setField("field1", "Value1");

* form.setField("field2", "Value2");

* //Set the disposition and filename

* response.setHeader("Content-disposition", "attachment; FILENAME=someName.pdf" );
*@param model The model
*@param stamper The PdfStamper instance that will containt he AcroFields.
*@param request Provided in case you want to get parameters directly from the request
*@param response Provided for setting cookies or other things. Do not attempt to write the stamper to the outputstream. That is done in a different method.
*
*/
protected abstract void mergePdfDocument(Map model, com.lowagie.text.pdf.PdfStamper stamper, HttpServletRequest request, HttpServletResponse response) throws Exception ;

/** This method is run After the MergePdfDocument and Before the stamper is closed.

* This gives you an oportunity set your own settings on the stamper.
* This is where we suggest that you set things like setFormFlattening and many other interesting things.
* @param stamper. The stamper object that has already been merged.
*/
protected abstract void prepareStamper(PdfStamper stamper);

/** The url in this case is a string value that represents the existing PDF file that will be used to generate the stamper.
* You can use the view setup to specify the url. mergePdf.(class)=.PdfStamperClass mergePdf.url=/WEB-INF/views/resources/template.pdf
* will set the url as /WEB-INF/views/resources/template.pdf.
* @param url The file location to the PDF document.
*/
public void setUrl(String url) {
this.url = url;
}

/** JavaBean method to retreive the document url from the instance.
* @return url.
*/
public String getUrl() {
return this.url;
}

}

2008-02-26T05:50:00.000-08:00

Some good blogs on finance and equity (India markets)

http://www.rasoni.blogspot.com/
http://www.valuestockplus.net/
http://www.desimoney.blogspot.com/
http://journeytowealth.blogspot.com/
http://blog.investraction.com/

http://www.valueinvestorindia.blogspot.com/

http://www.ranjanblog.com/
http://fundooprofessor.blogspot.com/

http://scrip-tures.blogspot.com/
http://www.rupya.com/
http://timamo.blogspot.com/
http://premsagark.blogspot.com/

Write your own Generic Comparator

2007-11-15T01:09:00.000-08:00

Lets implement PropertyComparator a generic utility classs that is used to sort on properties of an object.The property can be specified as a string using the format specified in org.apache.commons.beanutils.PropertyUtils.You need to include Commons BeanUtils in your classpath.


import java.util.Comparator;
import org.apache.commons.beanutils.PropertyUtils;

public class PropertyComparator implements Comparator{
    private String propertyName;
      public PropertyComparator(String property)
    {
       this.propertyName = property;
    }
     public int compare(Object object1, Object object2)
    {
       int result = 0;
     try
       {
          Object property1 = PropertyUtils.getProperty(object1, propertyName);
          Object property2 = PropertyUtils.getProperty(object2, propertyName);
         if (property1 instanceof Comparable)
          {
             result = ((Comparable)property1).compareTo(property2);
          } 
     }catch (Exception e)
       {
          e.printStackTrace(); 
     }
    return result ;
}

Ok now moving on to usage of this class, Say we have a class called Employee
which is defined like:


public class Employee{
   String empId;
   String empName;

   public Employee(String empId,String empName)
   {
      this.empId = empId;
      this.empName = empName;
   }
   public String getEmpId()
   { 
      return empId;
   }

   public void setEmpId(String empId)
   {
      this.empId = empId;
   }

   public String getEmpName()
   {
      return empName;
   }

  public void setEmpName(String name)
  {
      this.empName = name;
  }

  public String toString()
  {
      return "EmpId : "+empId+" EmpName : "+empName;
  }

}

Ok i must tell you that if you dont have getter and setter methods for the properties , this will not work.

Now moving on


public class TestGenericComparator
    {
        public static void main(String args[])
       {
           Employee emp1 = new Employee("1","Zebra");
           Employee emp2 = new Employee("2","Apple");
           List empList  = new ArrayList();
           empList.add(emp1);
           empList.add(emp2);
           ........................
 
           Collections.sort(empList,new PropertyCoparator("empName"));
           for(Employee emp:empList)
           {
               System.out.println(emp.toString());
           }
       }
 }

Well this concept can be similarly extended for multi property comparators.

Hello World!!

2007-11-15T00:40:00.000-08:00

Yeah i wanted to do this for a long time. I mean i wanted to blog. Blog on what . i dont know