-- // $RCSfile: Jive_db2_7.sql,v $
-- // $Revision: 1.1.1.1 $
-- // $Date: 2002/09/09 13:50:34 $
-- // Conversion from Oracle 8 script by James Radvan

CREATE TABLE jiveForum (
  forumID              INTEGER NOT NULL,
  name                 VARCHAR(255) NOT NULL,
  description          VARCHAR(2000),
  modDefaultThreadVal  INTEGER NOT NULL,
  modMinThreadVal      INTEGER NOT NULL,
  modDefaultMsgVal     INTEGER NOT NULL,
  modMinMsgVal         INTEGER NOT NULL,
  creationDate         VARCHAR(15) NOT NULL,
  modifiedDate         VARCHAR(15) NOT NULL,
  CONSTRAINT jiveForum_pk PRIMARY KEY (forumID)
);


CREATE TABLE jiveForumProp (
  forumID     INTEGER NOT NULL,
  name        VARCHAR(100) NOT NULL,
  propValue   VARCHAR(2000) NOT NULL,
  CONSTRAINT jiveForumProp_pk PRIMARY KEY (forumID,name)
);


CREATE TABLE jiveThread (
  threadID            INTEGER NOT NULL,
  forumID             INTEGER NOT NULL,
  rootMessageID       INTEGER NOT NULL,
  modValue            INTEGER NOT NULL,
  rewardPoints        INTEGER NOT NULL,
  creationDate        VARCHAR(15) NOT NULL,
  modifiedDate        VARCHAR(15) NOT NULL,
  CONSTRAINT jiveThread_pk PRIMARY KEY (threadID)
);
CREATE INDEX jT_forumID_idx ON jiveThread (forumID);
CREATE INDEX jT_modValue_idx ON jiveThread (modValue);
CREATE INDEX jT_cDate_idx ON jiveThread (creationDate ASC);
CREATE INDEX jT_mDate_idx ON jiveThread (modifiedDate DESC);


CREATE TABLE jiveThreadProp (
  threadID      INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     VARCHAR(2000) NOT NULL,
  CONSTRAINT jiveThreadProp_pk PRIMARY KEY (threadID, name)
);


CREATE TABLE jiveMessage (
  messageID         INTEGER NOT NULL,
  parentMessageID	INTEGER,
  threadID          INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  userID            INTEGER,
  subject           VARCHAR(255),  
  body              LONG VARCHAR,
  modValue          INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modifiedDate      VARCHAR(15) NOT NULL,
  CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)  
);
CREATE INDEX jM_threadID_idx ON jiveMessage (threadID ASC);
CREATE INDEX jM_userID_idx ON jiveMessage (userID ASC);
CREATE INDEX jM_forumId_mV_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jM_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jM_mDate_idx ON jiveMessage (modifiedDate DESC);


CREATE TABLE jiveMessageProp (
  messageID     INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     VARCHAR(2000) NOT NULL,
  CONSTRAINT jiveMessageProp_pk PRIMARY KEY (messageID, name)
);


CREATE TABLE jiveUser (
  userID        	INTEGER NOT NULL,
  username          VARCHAR(30) UNIQUE NOT NULL,
  passwordHash      VARCHAR(32) NOT NULL,
  name              VARCHAR(100),
  nameVisible       INTEGER NOT NULL,
  email             VARCHAR(100) NOT NULL,
  emailVisible      INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modifiedDate      VARCHAR(15) NOT NULL,
  CONSTRAINT jiveUser_pk PRIMARY KEY (userID)
);
CREATE INDEX jU_cDate_idx on jiveUser (creationDate ASC);


CREATE TABLE jiveUserPerm (
  forumID          INTEGER,
  userID           INTEGER,
  userType         INTEGER NOT NULL,
  permission       INTEGER NOT NULL
);
CREATE INDEX jUP_forumID_idx ON jiveUserPerm (forumID ASC);
CREATE INDEX jUP_userID_idx ON jiveUserPerm (userID ASC);


CREATE TABLE jiveUserProp (
  userID      INTEGER NOT NULL,
  name        VARCHAR(100) NOT NULL,
  propValue   VARCHAR(2000) NOT NULL,
  CONSTRAINT jiveUserProp_pk PRIMARY KEY (userID, name)
);


CREATE TABLE jiveGroup (
  groupID       INTEGER NOT NULL,
  name          VARCHAR(100) UNIQUE NOT NULL,
  description   VARCHAR(255),
  creationDate  VARCHAR(15) NOT NULL,
  modifiedDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveGroup_pk PRIMARY KEY (groupID)
);
CREATE INDEX jG_cDate_idx on jiveGroup (creationDate ASC);


CREATE TABLE jiveGroupPerm (
  forumID       INTEGER NOT NULL,
  groupID       INTEGER NOT NULL,
  permission    INTEGER NOT NULL,
  CONSTRAINT jiveGroupPerm_pk PRIMARY KEY (forumID, groupID, permission)
);
CREATE INDEX jGP_groupID_idx ON jiveGroupPerm (groupID ASC);


CREATE TABLE jiveGroupProp (
  groupID     INTEGER NOT NULL,
  name        VARCHAR(100) NOT NULL,
  propValue   VARCHAR(2000) NOT NULL,
  CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupID, name)
);


CREATE TABLE jiveGroupUser (
  groupID          INTEGER NOT NULL,
  userID           INTEGER NOT NULL,
  administrator    INTEGER NOT NULL,
  CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupID, userID)
);
CREATE INDEX jGU_userID_idx ON jiveGroupUser (userID ASC);


CREATE TABLE jiveID (
  idType INTEGER NOT NULL,
  id     INTEGER NOT NULL,
  CONSTRAINT jiveID_pk PRIMARY KEY (idType)
);


CREATE TABLE jiveModeration (
  objectID    INTEGER NOT NULL,
  objectType  INTEGER NOT NULL,
  userID      INTEGER,
  modDate     VARCHAR(15) NOT NULL,
  modValue    INTEGER NOT NULL
);
CREATE INDEX jMo_objectID_idx ON jiveModeration (objectID ASC);
CREATE INDEX jMo_objectType_idx on jiveModeration (objectType);
CREATE INDEX jMo_userID_idx ON jiveModeration (userID ASC);


CREATE TABLE jiveWatch (
  userID            INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  threadID          INTEGER NOT NULL,
  watchType         INTEGER NOT NULL,
  expirable         INTEGER NOT NULL,
  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, threadID, watchType)  
);
CREATE INDEX jW_userID_idx ON jiveWatch (userID ASC);
CREATE INDEX jW_forumID_idx ON jiveWatch (forumID ASC);
CREATE INDEX jW_threadID_idx ON jiveWatch (threadID ASC);
CREATE INDEX jW_type_idx ON jiveWatch (watchType);


CREATE TABLE jiveReward (
  userID          INTEGER NOT NULL,
  creationDate    VARCHAR(15) NOT NULL,
  rewardPoints    INTEGER NOT NULL,
  messageID       INTEGER,
  threadID        INTEGER
);
CREATE INDEX jR_userID_idx ON jiveReward (userID ASC);
CREATE INDEX jR_creatDate_idx ON jiveReward (creationDate);
CREATE INDEX jR_messageID_idx ON jiveReward (messageID ASC);
CREATE INDEX jR_threadID_idx ON jiveReward (threadID ASC);


-- // Foreign key constraints. These are not deferrable in DB2.

ALTER TABLE jiveForumProp ADD CONSTRAINT jFP_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum ;

ALTER TABLE jiveThread ADD CONSTRAINT jT_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum ;

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

ALTER TABLE jiveMessage ADD CONSTRAINT jM_parentMsgID_fk FOREIGN KEY (parentMessageID) REFERENCES jiveMessage(messageID) ;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread ;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum ;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;

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

ALTER TABLE jiveUserPerm ADD CONSTRAINT jUP_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum ;
ALTER TABLE jiveUserPerm ADD CONSTRAINT jUP_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;

ALTER TABLE jiveUserProp ADD CONSTRAINT jUP_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jGP_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum ;
ALTER TABLE jiveGroupPerm ADD CONSTRAINT jGP_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup ;

ALTER TABLE jiveGroupProp ADD CONSTRAINT jGP_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup ;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jGU_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup ;
ALTER TABLE jiveGroupUser ADD CONSTRAINT jGU_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;

ALTER TABLE jiveWatch ADD CONSTRAINT jW_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;
ALTER TABLE jiveWatch ADD CONSTRAINT jW_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread ;

ALTER TABLE jiveReward ADD CONSTRAINT jR_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser ;

-- // Finally, insert default table values.

-- // Unique ID entry for forum, thread, messages, user, group.
-- // The User ID entry starts at 2 (after admin user entry). 
insert into jiveID values (0, 1);
insert into jiveID values (1, 1);
insert into jiveID values (2, 1);
insert into jiveID values (3, 2);
insert into jiveID values (4, 1);

-- // Entry for admin user -- password is 'admin'
insert into jiveUser (userID,name,username,passwordHash,email,emailVisible,nameVisible,creationDate,modifiedDate,rewardPoints) values (1,'Administrator','admin','21232f297a57a5a743894a0e4a801fc3','admin@yoursite.com',1,1,'0','0',0);
insert into jiveUserPerm(forumID,userID,userType,permission) values (NULL,1,3,1);