REM  //////////////////////////////
REM  // $RCSfile: Jive_oracle_8.sql,v $
REM  // $Revision: 1.1.1.1 $
REM  // $Date: 2002/09/09 13:50:34 $
REM  //////////////////////////////

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


CREATE TABLE jiveForumProp (
  forumID     INTEGER NOT NULL,
  name        VARCHAR2(100) NOT NULL,
  propValue   VARCHAR2(4000) 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        VARCHAR2(15) NOT NULL,
  modifiedDate        VARCHAR2(15) NOT NULL,
  CONSTRAINT jiveThread_pk PRIMARY KEY (threadID)
);
CREATE BITMAP INDEX jiveThread_forumID_idx ON jiveThread (forumID);
CREATE BITMAP INDEX jiveThread_modValue_idx ON jiveThread (modValue);
CREATE INDEX jiveThread_cDate_idx ON jiveThread (creationDate ASC);
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modifiedDate DESC);


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


CREATE TABLE jiveMessage (
  messageID         INTEGER NOT NULL,
  parentMessageID	INTEGER NULL,
  threadID          INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  userID            INTEGER NULL,
  subject           VARCHAR2(255) NULL,  
  body              LONG VARCHAR NULL,
  modValue          INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR2(15) NOT NULL,
  modifiedDate      VARCHAR2(15) NOT NULL,
  CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)  
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID ASC);
CREATE INDEX jiveMessage_userID_idx ON jiveMessage (userID ASC);
CREATE INDEX jiveMessage_forumId_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modifiedDate DESC);


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


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


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


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


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


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


CREATE TABLE jiveGroupProp (
  groupID     INTEGER NOT NULL,
  name        VARCHAR2(100) NOT NULL,
  propValue   VARCHAR2(4000) 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 jiveGroupUser_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 NULL,
  modDate     VARCHAR2(15) NOT NULL,
  modValue    INTEGER NOT NULL
);
CREATE INDEX jiveModeration_objectID_idx ON jiveModeration (objectID ASC);
CREATE BITMAP INDEX jiveModeration_objectType_idx on jiveModeration (objectType);
CREATE INDEX jiveModeration_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 jiveWatch_userID_idx ON jiveWatch (userID ASC);
CREATE INDEX jiveWatch_forumID_idx ON jiveWatch (forumID ASC);
CREATE INDEX jiveWatch_threadID_idx ON jiveWatch (threadID ASC);
CREATE BITMAP INDEX jiveWatch_type_idx ON jiveWatch (watchType);


CREATE TABLE jiveReward (
  userID          INTEGER NOT NULL,
  creationDate    VARCHAR(15) NOT NULL,
  rewardPoints    INTEGER NOT NULL,
  messageID       INTEGER NULL,
  threadID        INTEGER NULL
);
CREATE INDEX jiveReward_userID_idx ON jiveReward (userID ASC);
CREATE INDEX jiveReward_creationDate_idx ON jiveReward (creationDate);
CREATE INDEX jiveReward_messageID_idx ON jiveReward (messageID ASC);
CREATE INDEX jiveReward_threadID_idx ON jiveReward (threadID ASC);


REM # Foreign key constraints. Note that all are deferrable until the commit of a transaction.

ALTER TABLE jiveForumProp ADD CONSTRAINT jiveForumProp_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_rootMsgID_fk FOREIGN KEY (rootMessageID) REFERENCES jiveMessage(messageID) INITIALLY DEFERRED DEFERRABLE;

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

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_parentMsgID_fk FOREIGN KEY (parentMessageID) REFERENCES jiveMessage(messageID) INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

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

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveUserProp ADD CONSTRAINT jiveUserProp_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveGroupProp ADD CONSTRAINT jiveGroupProp_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveWatch ADD CONSTRAINT jiveWatch_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveWatch ADD CONSTRAINT jiveWatch_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveReward ADD CONSTRAINT jiveReward_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser INITIALLY DEFERRED DEFERRABLE;

REM # Finally, insert default table values.

REM # Unique ID entry for forum, thread, messages, user, group.
REM # 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);

REM # 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);