Jive SQL

# $ Rcsfile: jive_mysql.sql, v $ # $ revision: 1.1.1.1 $ # $ date: 2002/09/09 13:50:34 $

CREATE TABLE jiveForum (forumID BIGINT NOT NULL, name VARCHAR (255) UNIQUE NOT NULL, description TEXT, modDefaultThreadVal BIGINT NOT NULL, modMinThreadVal BIGINT NOT NULL, modDefaultMsgVal BIGINT NOT NULL, modMinMsgVal BIGINT NOT NULL, modifiedDate VARCHAR (15) NOT NULL, creationDate VARCHAR (15) NOT NULL, Primary Key (ForumID), INDEX JIVEFORUM_NAME_IDX (Name (10)));

Create Table JiveForumprop (ForumID Bigint Not Null, Name VARCHAR (100) Not Null, PropValue Text Not Null, Primary Key (ForumID, Name);

CREATE TABLE jiveThread (threadID BIGINT NOT NULL, forumID BIGINT NOT NULL, rootMessageID BIGINT NOT NULL, modValue BIGINT NOT NULL, rewardPoints INT NOT NULL, creationDate VARCHAR (15) NOT NULL, modifiedDate VARCHAR (15) NOT NULL, PRIMARY KEY (threadID) , Index jivethread_forumid_idx (forumid), index jivethread_modvalue_idx (model), index jiveth_cdate_idx (createDate), index jivethread_mdate_idx (model);

Create Table JiveThreadProp (Threadid Bigint Not Null, Name VARCHAR (100) Not Null, PropValue Text Not Null, Primary Key (ThreadID, Name);

CREATE TABLE jiveMessage (messageID BIGINT NOT NULL, parentMessageID BIGINT NULL, threadID BIGINT NOT NULL, forumID BIGINT NOT NULL, userID BIGINT NULL, subject VARCHAR (255), body TEXT, modValue BIGINT NOT NULL, rewardPoints INT NOT NULL, creationDate VARCHAR (15 ) NOT NULL, modifiedDate VARCHAR (15) NOT NULL, PRIMARY KEY (messageID), INDEX jiveMessage_threadID_idx (threadID), INDEX jiveMessage_forumID_idx (forumID), INDEX jiveMessage_userID_idx (userID), INDEX jiveMessage_modValue_idx (modValue), INDEX jiveMessage_cDate_idx (creationDate), INDEX jiveMessage_mDate_idx (MODIFIEDDATE)); CREATE TABLE JIVEMESSAGEPROP (MessageId Bigint Not Null, Name VARCHAR (100) Not Null, PropValue Text Not Null, Primary Key (MessageId, Name);

CREATE TABLE jiveUser (userID BIGINT NOT NULL, username VARCHAR (30) UNIQUE NOT NULL, passwordHash VARCHAR (32) NOT NULL, name VARCHAR (100), nameVisible INT NOT NULL, email VARCHAR (100) NOT NULL, emailVisible INT NOT NULL, rewardPoints INT NOT NULL, creationDate VARCHAR (15) NOT NULL, modifiedDate VARCHAR (15) NOT NULL, PRIMARY KEY (userID), INDEX jiveUser_username_idx (username (10)), INDEX jiveUser_cDate_idx (creationDate));

CREATE TABLE jiveUserPerm (forumID BIGINT NULL, userID BIGINT NULL, userType INT NOT NULL, permission INT NOT NULL, INDEX jiveUserPerm_forumID_idx (forumID), INDEX jiveUserPerm_userID_idx (userID));

CREATE TABLE jiveUserProp (userID BIGINT NOT NULL, name VARCHAR (100) NOT NULL, propValue TEXT NOT NULL, PRIMARY KEY (userID, name)); CREATE TABLE jiveGroup (groupID BIGINT NOT NULL, name VARCHAR (50) NOT NULL, description VARCHAR (255), CreationDate Varchar (15) Not null, ModifiedDate Varchar (15) Not Null, Primary Key (GroupID), INDEX JIVEGROUP_NAME_IDX (Name (10)), INDEX JIVEGROUP_CDATE_IDX (CREATIONDATE);

Create Table JiveGroupperm (ForumID Bigint Not Null, GroupId Bigint Not Null, Permission Int Not Null, Index JiveGroupperm_forumId_IDX (ForumID), INDEX JIVEGROUPPERM_GROUPID_IDX (GroupID));

Create Table JiveGroupProp (GroupID Bigint Not Null, Name VARCHAR (100) Not Null, PropValue Text Not Null, Primary Key (GroupID, Name);

Create Table JiveGroupuser (GroupID Bigint Not Null, Userid Bigint Not Null, Administrator Int Not Null, Primary Key (GroupID, UserId);

Create Table JiveID (IDTYPE INT NOT NULL, ID BIGINT NOT NULL, PRIMARY Key (IDTYPE));

CREATE TABLE jiveModeration (objectID BIGINT NOT NULL, objectType BIGINT NOT NULL, userID BIGINT NULL, modDate VARCHAR (15) NOT NULL, modValue BIGINT NOT NULL, INDEX jiveModeration_objectID_idx (objectID), INDEX jiveModeration_objectType_idx (objectType), INDEX jiveModeration_userID_idx (userID));

CREATE TABLE jiveWatch (userID BIGINT NOT NULL, forumID BIGINT NOT NULL, threadID BIGINT NOT NULL, watchType BIGINT NOT NULL, expirable BIGINT NOT NULL, PRIMARY KEY (userID, threadID, watchType), INDEX jiveWatch_userID_idx (userID), INDEX jiveWatch_forumID_idx (forumID) , INDEX jiveWatch_threadID_idx (threadID), INDEX jiveWatch_type_idx (watchType)); CREATE TABLE jiveReward (userID BIGINT NOT NULL, creationDate VARCHAR (15) NOT NULL, rewardPoints BIGINT NOT NULL, messageID BIGINT NULL, threadID BIGINT NULL, INDEX jiveReward_userID_idx (userID), Index jivereward_creationdate_idx (createDate), index jivereward_messageid_idx (messageid), index jivereward_threadid_idx (threadid);

# Finally, INSERT DEFAULT TABLE VALUES.

# UNIQUE ID Entry for For Forum, Thread, Messages, User, Group. # The user ID Entry StartS at 2 (Insto 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);