jive的資料庫sybase7

CREATE TABLE jiveForum (
forumID INT NOT NULL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
description TEXT NULL,
modDefaultThreadVal INT NOT NULL,
modMinThreadVal INT NOT NULL,
modDefaultMsgVal INT NOT NULL,
modMinMsgVal INT NOT NULL,
modifiedDate VARCHAR(15) NOT NULL,
creationDate VARCHAR(15) NOT NULL,
);

CREATE TABLE jiveForumProp (
forumID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (forumID, name),
);

CREATE TABLE jiveThread (
threadID INT NOT NULL PRIMARY KEY,
forumID INT NOT NULL,
rootMessageID INT NOT NULL,
modValue INT NOT NULL,
rewardPoints INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modifiedDate CHAR(15) NOT NULL,
);
CREATE INDEX jiveThread_forumID_idx ON jiveThread (forumID);
CREATE INDEX jiveThread_modValue_idx ON jiveThread (modValue);
CREATE INDEX jiveThread_cDate_idx ON jiveThread (creationDate);
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modifiedDate);

CREATE TABLE jiveThreadProp (
threadID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (threadID, name)
);

CREATE TABLE jiveMessage (
messageID INT NOT NULL PRIMARY KEY,
parentMessageID INT NULL,
threadID INT NOT NULL,
forumID INT NOT NULL,
userID INT NULL,
subject VARCHAR(255) NULL,
body TEXT NULL,
modValue INT NOT NULL,
rewardPoints INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modifiedDate CHAR(15) NOT NULL,
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID);
CREATE INDEX jiveMessage_forumID_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_userID_idx ON jiveMessage (userID);
CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modifiedDate);

CREATE TABLE jiveMessageProp (
messageID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (messageID, name)
);

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

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

CREATE TABLE jiveUserProp (
userID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (userID, name)
);

CREATE TABLE jiveGroup (
groupID INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NULL,
modifiedDate CHAR(15) NOT NULL,
creationDate CHAR(15) NOT NULL,
);
CREATE INDEX jiveGroup_cDate_idx ON jiveGroup (creationDate);

CREATE TABLE jiveGroupPerm (
forumID INT NOT NULL,
groupID INT NOT NULL,
permission INT NOT NULL,
PRIMARY KEY (forumID, groupID,permission)
);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);

CREATE TABLE jiveGroupProp (
groupID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (groupID, name)
);

CREATE TABLE jiveGroupUser (
groupID INT NOT NULL ,
userID INT NOT NULL ,
administrator INT NOT NULL,
PRIMARY KEY (groupID, userID)
);
CREATE INDEX jiveGroupUser_userID_idx ON jiveGroupUser (userID);

CREATE TABLE jiveID (
idType INT NOT NULL PRIMARY KEY,
id INT NOT NULL,
);

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

CREATE TABLE jiveWatch (
userID INT NOT NULL ,
forumID INT NOT NULL ,
threadID INT NOT NULL ,
watchType INT NOT NULL ,
expirable INT NOT NULL,
PRIMARY KEY (userID, forumID,watchType)
);
CREATE INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE INDEX jiveWatch_forumID_idx ON jiveWatch (forumID);
CREATE INDEX jiveWatch_threadID_idx ON jiveWatch (threadID);
CREATE INDEX jiveWatch_type_idx ON jiveWatch (watchType);

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

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

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

/*
Note: need to figure out how to make constraints deferrable
under SQL Server. Until then, disable certain fk's.

*/

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

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_parentMsgID_fk
FOREIGN KEY (parentMessageID) REFERENCES jiveMessage(messageID);

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_threadID_fk
FOREIGN KEY (threadID) REFERENCES jiveThread;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

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

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

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

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_groupID_fk
FOREIGN KEY (groupID) REFERENCES jiveGroup;

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

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_groupID_fk
FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

/* 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,username,passwordHash,name,nameVisible,email,emailVisible,rewardPoints,creationDate,modifiedDate)
VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3','Administrator',1,'admin@yoursite.com',1,0,'0','0');
INSERT INTO jiveUserPerm(forumID,userID,userType,permission)
VALUES (NULL,1,3,1);

jive的資料庫sybase7

相關文章