
-- $Header: /cvsroot/mvnforum/mvnforum/sql/mvnForum_Sybase.sql,v 1.14 2006/03/29 19:14:37 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.14 $
-- $Date: 2006/03/29 19:14:37 $
-- Database : Sybase
-- Driver   :
-- Url      :


-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : IDENTITY
-- LONGVARCHAR    : TEXT       Is Sybase support NTEXT as in Sql Server ???
-- DATE           : DATETIME
-- TIMESTAMP      : DATETIME
-- VARCHAR        : NVARCHAR
-- BLOB           : 
-- now()          : GETDATE()

-- Uncomment the following drop table command if you want to drop the tables
-- Note: drop tables will delete all the data in them.
-- Note: you should always backup your data before run the script

-- drop table mvnforumCategory;
-- drop table mvnforumForum;
-- drop table mvnforumGroupForum;
-- drop table mvnforumGroupPermission;
-- drop table mvnforumGroups;
-- drop table mvnforumMember;
-- drop table mvnforumMemberGroup;
-- drop table mvnforumMemberPermission;
-- drop table mvnforumMessageFolder;
-- drop table mvnforumPost;
-- drop table mvnforumThread;
-- drop table mvnforumWatch;
-- drop table mvnforumAttachment;
-- drop table mvnforumMemberForum;
-- drop table mvnforumFavoriteThread;
-- drop table mvnforumRank;
-- drop table mvnforumMessage;
-- drop table mvnforumMessageStatistics;
-- drop table mvnforumPmAttachment;
-- drop table mvnforumPmAttachMessage;


CREATE TABLE mvnforumCategory
(
   CategoryID                     INT                            not null IDENTITY,
   ParentCategoryID               INT                            not null,
   CategoryName                   NVARCHAR(250)                  not null,
   CategoryDesc                   TEXT                           not null,
   CategoryCreationDate           DATETIME                       not null,
   CategoryModifiedDate           DATETIME                       not null,
   CategoryOrder                  SMALLINT                       not null,
   CategoryOption                 INT                            not null,
   CategoryStatus                 INT                            not null,
   primary key (CategoryID),
   unique (CategoryName)
);

CREATE TABLE mvnforumForum
(
   ForumID                        INT                            not null IDENTITY,
   CategoryID                     INT                            not null,
   LastPostMemberName             NVARCHAR(30)                   not null,
   ForumName                      NVARCHAR(250)                  not null,
   ForumDesc                      TEXT                           not null,
   ForumCreationDate              DATETIME                       not null,
   ForumModifiedDate              DATETIME                       not null,
   ForumLastPostDate              DATETIME                       not null,
   ForumOrder                     SMALLINT                       not null,
   ForumType                      INT                            not null,
   ForumFormatOption              INT                            not null,
   ForumOption                    INT                            not null,
   ForumStatus                    INT                            not null,
   ForumModerationMode            INT                            not null,
   ForumPassword                  NVARCHAR(40)                   not null,
   ForumThreadCount               INT                            not null,
   ForumPostCount                 INT                            not null,
   primary key (ForumID),
   unique (ForumName, CategoryID)
);

CREATE INDEX Forum_CatID_idx on mvnforumForum
(
   CategoryID
);

CREATE TABLE mvnforumGroupForum
(
   GroupID                        INT                            not null,
   ForumID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (GroupID, ForumID, Permission)
);

CREATE INDEX GroupForum_1_idx on mvnforumGroupForum
(
   GroupID
);

CREATE INDEX GroupForum_2_idx on mvnforumGroupForum
(
   ForumID
);

CREATE TABLE mvnforumGroupPermission
(
   GroupID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (GroupID, Permission)
);

CREATE INDEX GroupPermission_1_idx on mvnforumGroupPermission
(
   GroupID
);

CREATE TABLE mvnforumGroups
(
   GroupID                        INT                            not null IDENTITY,
   GroupOwnerID                   INT                            not null,
   GroupOwnerName                 NVARCHAR(30)                   not null,
   GroupName                      NVARCHAR(250)                  not null,
   GroupDesc                      TEXT                           not null,
   GroupOption                    INT                            not null,
   GroupCreationDate              DATETIME                       not null,
   GroupModifiedDate              DATETIME                       not null,
   primary key (GroupID),
   unique (GroupName)
);

CREATE TABLE mvnforumMember
(
   MemberID                       INT                            not null IDENTITY,
   MemberName                     NVARCHAR(30)                   not null,
   MemberPassword                 NVARCHAR(200)                  not null,
   MemberFirstEmail               NVARCHAR(60)                   not null,
   MemberEmail                    NVARCHAR(60)                   not null,
   MemberEmailVisible             SMALLINT                       not null,
   MemberNameVisible              SMALLINT                       not null,
   MemberFirstIP                  NVARCHAR(20)                   not null,
   MemberLastIP                   NVARCHAR(20)                   not null,
   MemberViewCount                INT                            not null,
   MemberPostCount                INT                            not null,
   MemberCreationDate             DATETIME                       not null,
   MemberModifiedDate             DATETIME                       not null,
   MemberExpireDate               DATETIME                       not null,
   MemberLastLogon                DATETIME                       not null,
   MemberOption                   INT                            not null,
   MemberStatus                   INT                            not null,
   MemberActivateCode             NVARCHAR(40)                   not null,
   MemberTempPassword             NVARCHAR(40)                   not null,
   MemberMessageCount             INT                            not null,
   MemberMessageOption            INT                            not null,
   MemberPostsPerPage             SMALLINT                       not null,
   MemberWarnCount                SMALLINT                       not null,
   MemberVoteCount                INT                            not null,
   MemberVoteTotalStars           INT                            not null,
   MemberRewardPoints             INT                            not null,
   MemberTitle                    NVARCHAR(250)                  not null,
   MemberTimeZone                 FLOAT                          not null,
   MemberSignature                NVARCHAR(250)                  not null,
   MemberAvatar                   NVARCHAR(200)                  not null,
   MemberSkin                     NVARCHAR(70)                   not null,
   MemberLanguage                 NVARCHAR(70)                   not null,
   MemberFirstname                NVARCHAR(70)                   not null,
   MemberLastname                 NVARCHAR(70)                   not null,
   MemberGender                   SMALLINT                       not null,
   MemberBirthday                 DATETIME                       not null,
   MemberAddress                  NVARCHAR(150)                  not null,
   MemberCity                     NVARCHAR(70)                   not null,
   MemberState                    NVARCHAR(70)                   not null,
   MemberCountry                  NVARCHAR(70)                   not null,
   MemberPhone                    NVARCHAR(40)                   not null,
   MemberMobile                   NVARCHAR(40)                   not null,
   MemberFax                      NVARCHAR(40)                   not null,
   MemberCareer                   NVARCHAR(50)                   not null,
   MemberHomepage                 NVARCHAR(200)                  not null,
   MemberYahoo                    NVARCHAR(70)                   not null,
   MemberAol                      NVARCHAR(70)                   not null,
   MemberIcq                      NVARCHAR(70)                   not null,
   MemberMsn                      NVARCHAR(70)                   not null,
   MemberCoolLink1                NVARCHAR(200)                  not null,
   MemberCoolLink2                NVARCHAR(200)                  not null,
   primary key (MemberID),
   unique (MemberEmail),
   unique (MemberName)
);

CREATE TABLE mvnforumMemberGroup
(
   GroupID                        INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     NVARCHAR(30)                   not null,
   Privilege                      INT                            not null,
   CreationDate                   DATETIME                       not null,
   ModifiedDate                   DATETIME                       not null,
   primary key (GroupID, MemberID)
);

CREATE INDEX MemberGroup_1_idx on mvnforumMemberGroup
(
   MemberID
);

CREATE INDEX MemberGroup_2_idx on mvnforumMemberGroup
(
   GroupID
);

CREATE TABLE mvnforumMemberPermission
(
   MemberID                       INT                            not null,
   Permission                     INT                            not null,
   primary key (MemberID, Permission)
);

CREATE INDEX MemberPermission_1_idx on mvnforumMemberPermission
(
   MemberID
);

CREATE TABLE mvnforumMessageFolder
(
   FolderName                     NVARCHAR(30)                   not null,
   MemberID                       INT                            not null,
   FolderOrder                    SMALLINT                       not null,
   FolderStatus                   INT                            not null,
   FolderOption                   INT                            not null,
   FolderType                     INT                            not null,
   FolderCreationDate             DATETIME                       not null,
   FolderModifiedDate             DATETIME                       not null,
   primary key (FolderName, MemberID)
);

CREATE INDEX MessageFolder_1_idx on mvnforumMessageFolder
(
   MemberID
);

CREATE TABLE mvnforumPost
(
   PostID                         INT                            not null IDENTITY,
   ParentPostID                   INT                            not null,
   ForumID                        INT                            not null,
   ThreadID                       INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     NVARCHAR(30)                   not null,
   LastEditMemberName             NVARCHAR(30)                   not null,
   PostTopic                      NVARCHAR(250)                  not null,
   PostBody                       TEXT                           not null,
   PostCreationDate               DATETIME                       not null,
   PostLastEditDate               DATETIME                       not null,
   PostCreationIP                 NVARCHAR(20)                   not null,
   PostLastEditIP                 NVARCHAR(20)                   not null,
   PostEditCount                  SMALLINT                       not null,
   PostFormatOption               INT                            not null,
   PostOption                     INT                            not null,
   PostStatus                     INT                            not null,
   PostIcon                       NVARCHAR(10)                   not null,
   PostAttachCount                SMALLINT                       not null,
   primary key (PostID)
);

CREATE INDEX Post_1_idx on mvnforumPost
(
   ForumID
);

CREATE INDEX Post_2_idx on mvnforumPost
(
   ThreadID
);

CREATE INDEX Post_3_idx on mvnforumPost
(
   MemberID
);

CREATE INDEX Post_4_idx on mvnforumPost
(
   ParentPostID
);

CREATE TABLE mvnforumThread
(
   ThreadID                       INT                            not null IDENTITY,
   ForumID                        INT                            not null,
   MemberName                     NVARCHAR(30)                   not null,
   LastPostMemberName             NVARCHAR(30)                   not null,
   ThreadTopic                    NVARCHAR(250)                  not null,
   ThreadBody                     TEXT                           not null,
   ThreadVoteCount                INT                            not null,
   ThreadVoteTotalStars           INT                            not null,
   ThreadCreationDate             DATETIME                       not null,
   ThreadLastPostDate             DATETIME                       not null,
   ThreadType                     INT                            not null,
   ThreadOption                   INT                            not null,
   ThreadStatus                   INT                            not null,
   ThreadHasPoll                  INT                            not null,
   ThreadViewCount                INT                            not null,
   ThreadReplyCount               INT                            not null,
   ThreadIcon                     NVARCHAR(10)                   not null,
   ThreadDuration                 INT                            not null,
   ThreadAttachCount              INT                            not null,
   primary key (ThreadID)
);

CREATE INDEX Thread_1_idx on mvnforumThread
(
   ForumID
);

CREATE TABLE mvnforumWatch
(
   WatchID                        INT                            not null IDENTITY,
   MemberID                       INT                            not null,
   CategoryID                     INT                            not null,
   ForumID                        INT                            not null,
   ThreadID                       INT                            not null,
   WatchType                      INT                            not null,
   WatchOption                    INT                            not null,
   WatchStatus                    INT                            not null,
   WatchCreationDate              DATETIME                       not null,
   WatchLastSentDate              DATETIME                       not null,
   WatchEndDate                   DATETIME                       not null,
   primary key (WatchID),
   unique (MemberID, CategoryID, ForumID, ThreadID)
);

CREATE INDEX Watch_MemberID_idx on mvnforumWatch
(
   MemberID
);

CREATE INDEX Watch_CategoryID_idx on mvnforumWatch
(
   CategoryID
);

CREATE INDEX Watch_ForumID_idx on mvnforumWatch
(
   ForumID
);

CREATE INDEX Watch_ThreadID_idx on mvnforumWatch
(
   ThreadID
);

CREATE TABLE mvnforumAttachment
(
   AttachID                       INT                            not null IDENTITY,
   PostID                         INT                            not null,
   MemberID                       INT                            not null,
   AttachFilename                 NVARCHAR(250)                  not null,
   AttachFileSize                 INT                            not null,
   AttachMimeType                 NVARCHAR(70)                   not null,
   AttachDesc                     TEXT                           not null,
   AttachCreationIP               NVARCHAR(20)                   not null,
   AttachCreationDate             DATETIME                       not null,
   AttachModifiedDate             DATETIME                       not null,
   AttachDownloadCount            INT                            not null,
   AttachOption                   INT                            not null,
   AttachStatus                   INT                            not null,
   primary key (AttachID)
);

CREATE INDEX Attachment_PostID_idx on mvnforumAttachment
(
   PostID
);

CREATE INDEX Attachment_MemberID_idx on mvnforumAttachment
(
   MemberID
);

CREATE TABLE mvnforumMemberForum
(
   MemberID                       INT                            not null,
   ForumID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (MemberID, ForumID, Permission)
);

CREATE INDEX MemberForum_1_idx on mvnforumMemberForum
(
   MemberID
);

CREATE INDEX MemberForum_2_idx on mvnforumMemberForum
(
   ForumID
);

CREATE TABLE mvnforumFavoriteThread
(
   MemberID                       INT                            not null,
   ThreadID                       INT                            not null,
   ForumID                        INT                            not null,
   FavoriteCreationDate           DATETIME                       not null,
   FavoriteType                   INT                            not null,
   FavoriteOption                 INT                            not null,
   FavoriteStatus                 INT                            not null,
   primary key (MemberID, ThreadID)
);

CREATE INDEX FavorThread_1_idx on mvnforumFavoriteThread
(
   MemberID
);

CREATE INDEX FavorThread_2_idx on mvnforumFavoriteThread
(
   ThreadID
);

CREATE TABLE mvnforumRank
(
   RankID                         INT                            not null IDENTITY,
   RankMinPosts                   INT                            not null,
   RankLevel                      INT                            not null,
   RankTitle                      NVARCHAR(250)                  not null,
   RankImage                      NVARCHAR(250)                  not null,
   RankType                       INT                            not null,
   RankOption                     INT                            not null,
   primary key (RankID),
   unique (RankMinPosts),
   unique (RankTitle)
);

CREATE TABLE mvnforumMessage
(
   MessageID                      INT                            not null IDENTITY,
   FolderName                     NVARCHAR(30)                   not null,
   MemberID                       INT                            not null,
   MessageSenderID                INT                            not null,
   MessageSenderName              NVARCHAR(30)                   not null,
   MessageToList                  NVARCHAR(250)                  not null,
   MessageCcList                  NVARCHAR(250),
   MessageBccList                 NVARCHAR(250),
   MessageTopic                   NVARCHAR(250)                  not null,
   MessageBody                    TEXT                           not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null,
   MessageReadStatus              INT                            not null,
   MessageNotify                  INT                            not null,
   MessageIcon                    NVARCHAR(10)                   not null,
   MessageAttachCount             INT                            not null,
   MessageIP                      NVARCHAR(20)                   not null,
   MessageCreationDate            DATETIME                       not null,
   primary key (MessageID)
);

CREATE INDEX Message_1_idx on mvnforumMessage
(
   FolderName,
   MemberID
);

CREATE INDEX Message_2_idx on mvnforumMessage
(
   MessageSenderID
);

CREATE TABLE mvnforumMessageStatistics
(
   FromID                         INT                            not null,
   ToID                           INT                            not null,
   MessageCreationDate            DATETIME                       not null,
   MessageAttachCount             INT                            not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null
);

CREATE INDEX MessageStatistics_1_idx on mvnforumMessageStatistics
(
   FromID
);

CREATE INDEX MessageStatistics_2_idx on mvnforumMessageStatistics
(
   ToID
);

CREATE TABLE mvnforumPmAttachment
(
   PmAttachID                     INT                            not null IDENTITY,
   MemberID                       INT                            not null,
   PmAttachFilename               NVARCHAR(250)                  not null,
   PmAttachFileSize               INT                            not null,
   PmAttachMimeType               NVARCHAR(70)                   not null,
   PmAttachDesc                   TEXT                           not null,
   PmAttachCreationIP             NVARCHAR(20)                   not null,
   PmAttachCreationDate           DATETIME                       not null,
   PmAttachModifiedDate           DATETIME                       not null,
   PmAttachDownloadCount          INT                            not null,
   PmAttachOption                 INT                            not null,
   PmAttachStatus                 INT                            not null,
   primary key (PmAttachID)
);

CREATE INDEX PmAttachment_1_idx on mvnforumPmAttachment
(
   MemberID
);

CREATE TABLE mvnforumPmAttachMessage
(
   MessageID                      INT                            not null,
   PmAttachID                     INT                            not null,
   RelationType                   INT                            not null,
   RelationOption                 INT                            not null,
   RelationStatus                 INT                            not null,
   primary key (MessageID, PmAttachID)
);


--
-- data for table mvnforumMember
--
SET IDENTITY_INSERT mvnforumMember ON
INSERT INTO mvnforumMember (MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)
                    VALUES ('1', 'admin', 'ISMvKXpXpadDiUoOSoAfww==', 'admin@yourdomain.com', 'admin@yourdomain.com', '0', '1',         '127.0.0.1',   '127.0.0.1',  '0',             '0',             GETDATE(),          GETDATE(),          GETDATE(),        GETDATE(),       '0',          '0',          '',                 '',                 '0',                '0',                 '10',               '0',             '0',             '0',                  '0',                '',          '0',            '',              '',           '',         '',             '',              '',             '1',          GETDATE(),      '',            '',         '',          '',            '',          '',           '',        '',           '',             '',          '',        '',        '',        '',              '');
SET IDENTITY_INSERT mvnforumMember OFF

--
-- data for table mvnforumMessageFolder
--
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
                           VALUES ('Inbox',    '1',      '0',         0,            0,            0,          GETDATE(),          GETDATE());
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
                           VALUES ('Sent',     '1',      '2',         0,            0,            0,          GETDATE(),          GETDATE());


--
-- data for table mvnforumGroups
--
SET IDENTITY_INSERT mvnforumGroups ON

INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('1',     '0',          '',             'Guest',   'All anonymous users belong to this group.',              '0',         GETDATE(),             GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('2',     '0',          '',             'Member',  'All registered users belong to this group.',             '0',         GETDATE(),             GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('3',     '1',          'admin',        'Admin',   'This group have SystemAdmin permission by default.',     '0',         GETDATE(),             GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('4',     '1',          'admin',        'Forum Admin', 'This group have ForumAdmin permission by default.',  '0',         GETDATE(),             GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('5',     '1',          'admin',        'Forum Moderator', 'This group have ForumModerator permission by default.','0',   GETDATE(),             GETDATE());
SET IDENTITY_INSERT mvnforumGroups OFF

--
-- data for table mvnforumMemberGroup
--
INSERT INTO mvnforumMemberGroup (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)
                         VALUES ('3',     '1',      'admin',    '0',       GETDATE(),    GETDATE());



--
-- data for table mvnforumMemberPermission
--
INSERT INTO mvnforumMemberPermission (MemberID, Permission) 
                              VALUES ('1',      '100');


--
-- data for table mvnforumGroupPermission
--
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('1',     '109');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('2',     '110');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('3',     '100');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('4',     '105');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('5',     '106');

--
-- data for table mvnforumRank
--
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (0, 0, 'Stranger',                   '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (20, 0, 'Newbie',                    '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (50, 0, 'Member',                    '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (100, 0, 'Advanced Member',          '',        0,        0);

