CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE MVNFORUMCATEGORY(CATEGORYID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PARENTCATEGORYID INTEGER NOT NULL,CATEGORYNAME VARCHAR(250) NOT NULL,CATEGORYDESC LONGVARCHAR NOT NULL,CATEGORYCREATIONDATE TIMESTAMP NOT NULL,CATEGORYMODIFIEDDATE TIMESTAMP NOT NULL,CATEGORYORDER INTEGER NOT NULL,CATEGORYOPTION INTEGER NOT NULL,CATEGORYSTATUS INTEGER NOT NULL,CONSTRAINT SYS_CT_1 UNIQUE(CATEGORYNAME))
CREATE MEMORY TABLE MVNFORUMFORUM(FORUMID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CATEGORYID INTEGER NOT NULL,LASTPOSTMEMBERNAME VARCHAR(30) NOT NULL,FORUMNAME VARCHAR(250) NOT NULL,FORUMDESC LONGVARCHAR NOT NULL,FORUMCREATIONDATE TIMESTAMP NOT NULL,FORUMMODIFIEDDATE TIMESTAMP NOT NULL,FORUMLASTPOSTDATE TIMESTAMP NOT NULL,FORUMORDER INTEGER NOT NULL,FORUMTYPE INTEGER NOT NULL,FORUMFORMATOPTION INTEGER NOT NULL,FORUMOPTION INTEGER NOT NULL,FORUMSTATUS INTEGER NOT NULL,FORUMMODERATIONMODE INTEGER NOT NULL,FORUMPASSWORD VARCHAR(40) NOT NULL,FORUMTHREADCOUNT INTEGER NOT NULL,FORUMPOSTCOUNT INTEGER NOT NULL,CONSTRAINT SYS_CT_3 UNIQUE(FORUMNAME,CATEGORYID))
CREATE INDEX FORUM_CATID_IDX ON MVNFORUMFORUM(CATEGORYID)
CREATE MEMORY TABLE MVNFORUMGROUPFORUM(GROUPID INTEGER NOT NULL,FORUMID INTEGER NOT NULL,PERMISSION INTEGER NOT NULL,PRIMARY KEY(GROUPID,FORUMID,PERMISSION))
CREATE INDEX GROUPFORUM_1_IDX ON MVNFORUMGROUPFORUM(GROUPID)
CREATE INDEX GROUPFORUM_2_IDX ON MVNFORUMGROUPFORUM(FORUMID)
CREATE MEMORY TABLE MVNFORUMGROUPPERMISSION(GROUPID INTEGER NOT NULL,PERMISSION INTEGER NOT NULL,PRIMARY KEY(GROUPID,PERMISSION))
CREATE INDEX GROUPPERMISSION_1_IDX ON MVNFORUMGROUPPERMISSION(GROUPID)
CREATE MEMORY TABLE MVNFORUMGROUPS(GROUPID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,GROUPOWNERID INTEGER NOT NULL,GROUPOWNERNAME VARCHAR(30) NOT NULL,GROUPNAME VARCHAR(250) NOT NULL,GROUPDESC LONGVARCHAR NOT NULL,GROUPOPTION INTEGER NOT NULL,GROUPCREATIONDATE TIMESTAMP NOT NULL,GROUPMODIFIEDDATE TIMESTAMP NOT NULL,CONSTRAINT SYS_CT_5 UNIQUE(GROUPNAME))
CREATE MEMORY TABLE MVNFORUMMEMBER(MEMBERID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,MEMBERNAME VARCHAR_IGNORECASE(30) NOT NULL,MEMBERPASSWORD VARCHAR(200) NOT NULL,MEMBERFIRSTEMAIL VARCHAR(60) NOT NULL,MEMBEREMAIL VARCHAR(60) NOT NULL,MEMBEREMAILVISIBLE INTEGER NOT NULL,MEMBERNAMEVISIBLE INTEGER NOT NULL,MEMBERFIRSTIP VARCHAR(20) NOT NULL,MEMBERLASTIP VARCHAR(20) NOT NULL,MEMBERVIEWCOUNT INTEGER NOT NULL,MEMBERPOSTCOUNT INTEGER NOT NULL,MEMBERCREATIONDATE TIMESTAMP NOT NULL,MEMBERMODIFIEDDATE TIMESTAMP NOT NULL,MEMBEREXPIREDATE TIMESTAMP NOT NULL,MEMBERLASTLOGON TIMESTAMP NOT NULL,MEMBEROPTION INTEGER NOT NULL,MEMBERSTATUS INTEGER NOT NULL,MEMBERACTIVATECODE VARCHAR(40) NOT NULL,MEMBERTEMPPASSWORD VARCHAR(40) NOT NULL,MEMBERMESSAGECOUNT INTEGER NOT NULL,MEMBERMESSAGEOPTION INTEGER NOT NULL,MEMBERPOSTSPERPAGE INTEGER NOT NULL,MEMBERWARNCOUNT INTEGER NOT NULL,MEMBERVOTECOUNT INTEGER NOT NULL,MEMBERVOTETOTALSTARS INTEGER NOT NULL,MEMBERREWARDPOINTS INTEGER NOT NULL,MEMBERTITLE VARCHAR(250) NOT NULL,MEMBERTIMEZONE FLOAT NOT NULL,MEMBERSIGNATURE VARCHAR(250) NOT NULL,MEMBERAVATAR VARCHAR(200) NOT NULL,MEMBERSKIN VARCHAR(70) NOT NULL,MEMBERLANGUAGE VARCHAR(70) NOT NULL,MEMBERFIRSTNAME VARCHAR(70) NOT NULL,MEMBERLASTNAME VARCHAR(70) NOT NULL,MEMBERGENDER INTEGER NOT NULL,MEMBERBIRTHDAY DATE NOT NULL,MEMBERADDRESS VARCHAR(150) NOT NULL,MEMBERCITY VARCHAR(70) NOT NULL,MEMBERSTATE VARCHAR(70) NOT NULL,MEMBERCOUNTRY VARCHAR(70) NOT NULL,MEMBERPHONE VARCHAR(40) NOT NULL,MEMBERMOBILE VARCHAR(40) NOT NULL,MEMBERFAX VARCHAR(40) NOT NULL,MEMBERCAREER VARCHAR(50) NOT NULL,MEMBERHOMEPAGE VARCHAR(200) NOT NULL,MEMBERYAHOO VARCHAR(70) NOT NULL,MEMBERAOL VARCHAR(70) NOT NULL,MEMBERICQ VARCHAR(70) NOT NULL,MEMBERMSN VARCHAR(70) NOT NULL,MEMBERCOOLLINK1 VARCHAR(200) NOT NULL,MEMBERCOOLLINK2 VARCHAR(200) NOT NULL,CONSTRAINT SYS_CT_7 UNIQUE(MEMBEREMAIL),CONSTRAINT SYS_CT_8 UNIQUE(MEMBERNAME))
CREATE MEMORY TABLE MVNFORUMMEMBERGROUP(GROUPID INTEGER NOT NULL,MEMBERID INTEGER NOT NULL,MEMBERNAME VARCHAR(30) NOT NULL,PRIVILEGE INTEGER NOT NULL,CREATIONDATE TIMESTAMP NOT NULL,MODIFIEDDATE TIMESTAMP NOT NULL,PRIMARY KEY(GROUPID,MEMBERID))
CREATE INDEX MEMBERGROUP_1_IDX ON MVNFORUMMEMBERGROUP(MEMBERID)
CREATE INDEX MEMBERGROUP_2_IDX ON MVNFORUMMEMBERGROUP(GROUPID)
CREATE MEMORY TABLE MVNFORUMMEMBERPERMISSION(MEMBERID INTEGER NOT NULL,PERMISSION INTEGER NOT NULL,PRIMARY KEY(MEMBERID,PERMISSION))
CREATE INDEX MEMBERPERMISSION_1_IDX ON MVNFORUMMEMBERPERMISSION(MEMBERID)
CREATE MEMORY TABLE MVNFORUMMESSAGEFOLDER(FOLDERNAME VARCHAR(30) NOT NULL,MEMBERID INTEGER NOT NULL,FOLDERORDER INTEGER NOT NULL,FOLDERSTATUS INTEGER NOT NULL,FOLDEROPTION INTEGER NOT NULL,FOLDERTYPE INTEGER NOT NULL,FOLDERCREATIONDATE TIMESTAMP NOT NULL,FOLDERMODIFIEDDATE TIMESTAMP NOT NULL,PRIMARY KEY(FOLDERNAME,MEMBERID))
CREATE INDEX MESSAGEFOLDER_1_IDX ON MVNFORUMMESSAGEFOLDER(MEMBERID)
CREATE MEMORY TABLE MVNFORUMPOST(POSTID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PARENTPOSTID INTEGER NOT NULL,FORUMID INTEGER NOT NULL,THREADID INTEGER NOT NULL,MEMBERID INTEGER NOT NULL,MEMBERNAME VARCHAR(30) NOT NULL,LASTEDITMEMBERNAME VARCHAR(30) NOT NULL,POSTTOPIC VARCHAR(250) NOT NULL,POSTBODY LONGVARCHAR NOT NULL,POSTCREATIONDATE TIMESTAMP NOT NULL,POSTLASTEDITDATE TIMESTAMP NOT NULL,POSTCREATIONIP VARCHAR(20) NOT NULL,POSTLASTEDITIP VARCHAR(20) NOT NULL,POSTEDITCOUNT INTEGER NOT NULL,POSTFORMATOPTION INTEGER NOT NULL,POSTOPTION INTEGER NOT NULL,POSTSTATUS INTEGER NOT NULL,POSTICON VARCHAR(10) NOT NULL,POSTATTACHCOUNT INTEGER NOT NULL)
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 MEMORY TABLE MVNFORUMTHREAD(THREADID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,FORUMID INTEGER NOT NULL,MEMBERNAME VARCHAR(30) NOT NULL,LASTPOSTMEMBERNAME VARCHAR(30) NOT NULL,THREADTOPIC VARCHAR(250) NOT NULL,THREADBODY LONGVARCHAR NOT NULL,THREADVOTECOUNT INTEGER NOT NULL,THREADVOTETOTALSTARS INTEGER NOT NULL,THREADCREATIONDATE TIMESTAMP NOT NULL,THREADLASTPOSTDATE TIMESTAMP NOT NULL,THREADTYPE INTEGER NOT NULL,THREADOPTION INTEGER NOT NULL,THREADSTATUS INTEGER NOT NULL,THREADHASPOLL INTEGER NOT NULL,THREADVIEWCOUNT INTEGER NOT NULL,THREADREPLYCOUNT INTEGER NOT NULL,THREADICON VARCHAR(10) NOT NULL,THREADDURATION INTEGER NOT NULL,THREADATTACHCOUNT INTEGER NOT NULL)
CREATE INDEX THREAD_1_IDX ON MVNFORUMTHREAD(FORUMID)
CREATE MEMORY TABLE MVNFORUMWATCH(WATCHID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,MEMBERID INTEGER NOT NULL,CATEGORYID INTEGER NOT NULL,FORUMID INTEGER NOT NULL,THREADID INTEGER NOT NULL,WATCHTYPE INTEGER NOT NULL,WATCHOPTION INTEGER NOT NULL,WATCHSTATUS INTEGER NOT NULL,WATCHCREATIONDATE TIMESTAMP NOT NULL,WATCHLASTSENTDATE TIMESTAMP NOT NULL,WATCHENDDATE TIMESTAMP NOT NULL,CONSTRAINT SYS_CT_11 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 MEMORY TABLE MVNFORUMATTACHMENT(ATTACHID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,POSTID INTEGER NOT NULL,MEMBERID INTEGER NOT NULL,ATTACHFILENAME VARCHAR(250) NOT NULL,ATTACHFILESIZE INTEGER NOT NULL,ATTACHMIMETYPE VARCHAR(70) NOT NULL,ATTACHDESC LONGVARCHAR NOT NULL,ATTACHCREATIONIP VARCHAR(20) NOT NULL,ATTACHCREATIONDATE TIMESTAMP NOT NULL,ATTACHMODIFIEDDATE TIMESTAMP NOT NULL,ATTACHDOWNLOADCOUNT INTEGER NOT NULL,ATTACHOPTION INTEGER NOT NULL,ATTACHSTATUS INTEGER NOT NULL)
CREATE INDEX ATTACHMENT_POSTID_IDX ON MVNFORUMATTACHMENT(POSTID)
CREATE INDEX ATTACHMENT_MEMBERID_IDX ON MVNFORUMATTACHMENT(MEMBERID)
CREATE MEMORY TABLE MVNFORUMMEMBERFORUM(MEMBERID INTEGER NOT NULL,FORUMID INTEGER NOT NULL,PERMISSION INTEGER NOT NULL,PRIMARY KEY(MEMBERID,FORUMID,PERMISSION))
CREATE INDEX MEMBERFORUM_1_IDX ON MVNFORUMMEMBERFORUM(MEMBERID)
CREATE INDEX MEMBERFORUM_2_IDX ON MVNFORUMMEMBERFORUM(FORUMID)
CREATE MEMORY TABLE MVNFORUMFAVORITETHREAD(MEMBERID INTEGER NOT NULL,THREADID INTEGER NOT NULL,FORUMID INTEGER NOT NULL,FAVORITECREATIONDATE TIMESTAMP NOT NULL,FAVORITETYPE INTEGER NOT NULL,FAVORITEOPTION INTEGER NOT NULL,FAVORITESTATUS INTEGER NOT NULL,PRIMARY KEY(MEMBERID,THREADID))
CREATE INDEX FAVORTHREAD_1_IDX ON MVNFORUMFAVORITETHREAD(MEMBERID)
CREATE INDEX FAVORTHREAD_2_IDX ON MVNFORUMFAVORITETHREAD(THREADID)
CREATE MEMORY TABLE MVNFORUMRANK(RANKID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,RANKMINPOSTS INTEGER NOT NULL,RANKLEVEL INTEGER NOT NULL,RANKTITLE VARCHAR(250) NOT NULL,RANKIMAGE VARCHAR(250) NOT NULL,RANKTYPE INTEGER NOT NULL,RANKOPTION INTEGER NOT NULL,CONSTRAINT SYS_CT_13 UNIQUE(RANKMINPOSTS),CONSTRAINT SYS_CT_14 UNIQUE(RANKTITLE))
CREATE MEMORY TABLE MVNFORUMMESSAGE(MESSAGEID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,FOLDERNAME VARCHAR(30) NOT NULL,MEMBERID INTEGER NOT NULL,MESSAGESENDERID INTEGER NOT NULL,MESSAGESENDERNAME VARCHAR(30) NOT NULL,MESSAGETOLIST VARCHAR(250) NOT NULL,MESSAGECCLIST VARCHAR(250),MESSAGEBCCLIST VARCHAR(250),MESSAGETOPIC VARCHAR(250) NOT NULL,MESSAGEBODY LONGVARCHAR NOT NULL,MESSAGETYPE INTEGER NOT NULL,MESSAGEOPTION INTEGER NOT NULL,MESSAGESTATUS INTEGER NOT NULL,MESSAGEREADSTATUS INTEGER NOT NULL,MESSAGENOTIFY INTEGER NOT NULL,MESSAGEICON VARCHAR(10) NOT NULL,MESSAGEATTACHCOUNT INTEGER NOT NULL,MESSAGEIP VARCHAR(20) NOT NULL,MESSAGECREATIONDATE TIMESTAMP NOT NULL)
CREATE INDEX MESSAGE_1_IDX ON MVNFORUMMESSAGE(FOLDERNAME,MEMBERID)
CREATE INDEX MESSAGE_2_IDX ON MVNFORUMMESSAGE(MESSAGESENDERID)
CREATE MEMORY TABLE MVNFORUMMESSAGESTATISTICS(FROMID INTEGER NOT NULL,TOID INTEGER NOT NULL,MESSAGECREATIONDATE TIMESTAMP NOT NULL,MESSAGEATTACHCOUNT INTEGER NOT NULL,MESSAGETYPE INTEGER NOT NULL,MESSAGEOPTION INTEGER NOT NULL,MESSAGESTATUS INTEGER NOT NULL)
CREATE INDEX MESSAGESTATISTICS_1_IDX ON MVNFORUMMESSAGESTATISTICS(FROMID)
CREATE INDEX MESSAGESTATISTICS_2_IDX ON MVNFORUMMESSAGESTATISTICS(TOID)
CREATE MEMORY TABLE MVNFORUMPMATTACHMENT(PMATTACHID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,MEMBERID INTEGER NOT NULL,PMATTACHFILENAME VARCHAR(250) NOT NULL,PMATTACHFILESIZE INTEGER NOT NULL,PMATTACHMIMETYPE VARCHAR(70) NOT NULL,PMATTACHDESC LONGVARCHAR NOT NULL,PMATTACHCREATIONIP VARCHAR(20) NOT NULL,PMATTACHCREATIONDATE TIMESTAMP NOT NULL,PMATTACHMODIFIEDDATE TIMESTAMP NOT NULL,PMATTACHDOWNLOADCOUNT INTEGER NOT NULL,PMATTACHOPTION INTEGER NOT NULL,PMATTACHSTATUS INTEGER NOT NULL)
CREATE INDEX PMATTACHMENT_1_IDX ON MVNFORUMPMATTACHMENT(MEMBERID)
CREATE MEMORY TABLE MVNFORUMPMATTACHMESSAGE(MESSAGEID INTEGER NOT NULL,PMATTACHID INTEGER NOT NULL,RELATIONTYPE INTEGER NOT NULL,RELATIONOPTION INTEGER NOT NULL,RELATIONSTATUS INTEGER NOT NULL,PRIMARY KEY(MESSAGEID,PMATTACHID))
ALTER TABLE MVNFORUMCATEGORY ALTER COLUMN CATEGORYID RESTART WITH 1
ALTER TABLE MVNFORUMFORUM ALTER COLUMN FORUMID RESTART WITH 1
ALTER TABLE MVNFORUMGROUPS ALTER COLUMN GROUPID RESTART WITH 6
ALTER TABLE MVNFORUMMEMBER ALTER COLUMN MEMBERID RESTART WITH 2
ALTER TABLE MVNFORUMPOST ALTER COLUMN POSTID RESTART WITH 1
ALTER TABLE MVNFORUMTHREAD ALTER COLUMN THREADID RESTART WITH 1
ALTER TABLE MVNFORUMWATCH ALTER COLUMN WATCHID RESTART WITH 1
ALTER TABLE MVNFORUMATTACHMENT ALTER COLUMN ATTACHID RESTART WITH 1
ALTER TABLE MVNFORUMRANK ALTER COLUMN RANKID RESTART WITH 5
ALTER TABLE MVNFORUMMESSAGE ALTER COLUMN MESSAGEID RESTART WITH 1
ALTER TABLE MVNFORUMPMATTACHMENT ALTER COLUMN PMATTACHID RESTART WITH 1
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
INSERT INTO MVNFORUMGROUPPERMISSION VALUES(1,109)
INSERT INTO MVNFORUMGROUPPERMISSION VALUES(2,110)
INSERT INTO MVNFORUMGROUPPERMISSION VALUES(3,100)
INSERT INTO MVNFORUMGROUPPERMISSION VALUES(4,105)
INSERT INTO MVNFORUMGROUPPERMISSION VALUES(5,106)
INSERT INTO MVNFORUMGROUPS VALUES(1,0,'','Guest','All anonymous users belong to this group.',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMGROUPS VALUES(2,0,'','Member','All registered users belong to this group.',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMGROUPS VALUES(3,1,'admin','Admin','This group have SystemAdmin permission by default.',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMGROUPS VALUES(4,1,'admin','Forum Admin','This group have ForumAdmin permission by default.',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMGROUPS VALUES(5,1,'admin','Forum Moderator','This group have ForumModerator permission by default.',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMMEMBER VALUES(1,'admin','ISMvKXpXpadDiUoOSoAfww==','admin@yourdomain.com','admin@yourdomain.com',0,1,'127.0.0.1','127.0.0.1',0,0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000',0,0,'','',0,0,10,0,0,0,0,'',0.0E0,'','','','','','',1,'2005-01-29','','','','','','','','','','','','','','','')
INSERT INTO MVNFORUMMEMBERGROUP VALUES(3,1,'admin',0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMMEMBERPERMISSION VALUES(1,100)
INSERT INTO MVNFORUMMESSAGEFOLDER VALUES('Inbox',1,0,0,0,0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMMESSAGEFOLDER VALUES('Sent',1,2,0,0,0,'2005-01-29 11:30:34.704000000','2005-01-29 11:30:34.704000000')
INSERT INTO MVNFORUMRANK VALUES(1,0,0,'Stranger','',0,0)
INSERT INTO MVNFORUMRANK VALUES(2,20,0,'Newbie','',0,0)
INSERT INTO MVNFORUMRANK VALUES(3,50,0,'Member','',0,0)
INSERT INTO MVNFORUMRANK VALUES(4,100,0,'Advanced Member','',0,0)
