[thesite] [Fwd: RE: Your email to evolt.org 16385 (fwd)]

Daniel J. Cody djc at starkmedia.com
Tue Oct 9 21:28:25 CDT 2001


rudy/others, heres what Young sent me, give it a once through, i'm going 
to run it on our mssql box at work tomorrow..



Content-Disposition: ATTACHMENT; FILENAME="evolt_MS.sql"


CREATE TABLE ATTRIBUTE (
        ATTRID               numeric(8) NOT NULL
                                    CHECK ("ATTRID" IS NOT NULL),
        ATTRNAME             varchar(50) NOT NULL
                                    CHECK ("ATTRNAME" IS NOT NULL)
)
go


ALTER TABLE ATTRIBUTE
        ADD PRIMARY KEY NONCLUSTERED (ATTRID)
go


CREATE TABLE CATEGORYS (
        CATEGORYID           numeric(8) NOT NULL
                                    CHECK ("CATEGORYID" IS NOT NULL),
        CATTYPEID            numeric(8) NOT NULL
                                    CHECK ("CATTYPEID" IS NOT NULL),
        CATEGORY             varchar(50) NOT NULL
                                    CHECK ("CATEGORY" IS NOT NULL),
        CREATED              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("CREATED" IS NOT NULL),
        CREATEDBY            numeric(8) NOT NULL
                                    CHECK ("CREATEDBY" IS NOT NULL),
        ACTIVE               numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("ACTIVE" IS NOT NULL),
        ACTIVEBY             numeric(8) NULL,
        DATEMOD              datetime NULL
)
go


CREATE TABLE CATTYPE (
        CATTYPEID            numeric(8) NOT NULL
                                    CHECK ("CATTYPEID" IS NOT NULL),
        CATTYPENAME          varchar(50) NOT NULL
                                    CHECK ("CATTYPENAME" IS NOT NULL),
        CREATED              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("CREATED" IS NOT NULL),
        CREATEDBY            numeric(8) NOT NULL
                                    CHECK ("CREATEDBY" IS NOT NULL),
        ACTIVE               numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("ACTIVE" IS NOT NULL),
        ACTIVEBY             numeric(8) NULL,
        DATEMOD              datetime NULL
)
go


CREATE TABLE CONTACT (
        CONTACTID            numeric(10) NULL,
        FIRSTNAME            varchar(50) NULL,
        LASTNAME             varchar(50) NULL,
        EMAIL                varchar(50) NULL,
        MESSAGE              text NULL,
        MAILDATE             datetime NULL,
        USERID               numeric(10) NULL,
        ANSWER               numeric(10) NULL,
        ACTIVE               numeric NULL
)
go


CREATE TABLE CONTENT (
        CONTENTID            numeric(10) NOT NULL
                                    CHECK ("CONTENTID" IS NOT NULL),
        CONTENTNAME          varchar(50) NULL,
        BODY                 text NULL,
        USERID               numeric(10) NULL,
        DATEMOD              datetime NULL,
        SIGNOFF              numeric(10) NULL,
        ADMINID              numeric(10) NULL,
        CATEGORYID           numeric(10) NULL,
        SIGNDATE             datetime NULL,
        SYNOPSIS             varchar(500) NULL,
        RATING               numeric(3,2) NOT NULL DEFAULT 0
                                    CHECK ("RATING" IS NOT NULL),
        RATINGS              numeric(8) NOT NULL DEFAULT 0
                                    CHECK ("RATINGS" IS NOT NULL),
        REPLIES              numeric(8) NOT NULL DEFAULT 0
                                    CHECK ("REPLIES" IS NOT NULL),
        KEYPHRASE            varchar(100) NULL
)
go


ALTER TABLE CONTENT
        ADD PRIMARY KEY NONCLUSTERED (CONTENTID)
go


CREATE TABLE CONTENT2 (
        CONTENTID            numeric(8) NOT NULL
                                    CHECK ("CONTENTID" IS NOT NULL),
        LANGUAGEID           numeric(8) NOT NULL DEFAULT 3846
                                    CHECK ("LANGUAGEID" IS NOT NULL),
        CATEGORYID           numeric(8) NOT NULL
                                    CHECK ("CATEGORYID" IS NOT NULL),
        USERID               numeric(8) NOT NULL
                                    CHECK ("USERID" IS NOT NULL),
        DATEMOD              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("DATEMOD" IS NOT NULL),
        SIGNOFF              numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("SIGNOFF" IS NOT NULL),
        SIGNDATE             datetime NULL,
        ADMINID              numeric(8) NULL,
        PRIV                 numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("PRIV" IS NOT NULL),
        CONTENTNAME          varchar(50) NOT NULL
                                    CHECK ("CONTENTNAME" IS NOT NULL),
        SYNOPSIS             varchar(255) NULL,
        BODY                 image NULL,
        RATING               numeric(3,2) NOT NULL DEFAULT 0
                                    CHECK ("RATING" IS NOT NULL),
        RATINGS              numeric(8) NOT NULL DEFAULT 0
                                    CHECK ("RATINGS" IS NOT NULL),
        REPLIES              numeric(8) NOT NULL DEFAULT 0
                                    CHECK ("REPLIES" IS NOT NULL)
)
go


CREATE TABLE ELEMENT (
        ELEMENTID            numeric(8) NOT NULL
                                    CHECK ("ELEMENTID" IS NOT NULL),
        ELEMENTNAME          varchar(100) NOT NULL
                                    CHECK ("ELEMENTNAME" IS NOT NULL)
)
go


ALTER TABLE ELEMENT
        ADD PRIMARY KEY NONCLUSTERED (ELEMENTID)
go


CREATE TABLE LANGUAGE (
        LANGUAGEID           numeric(8) NOT NULL
                                    CHECK ("LANGUAGEID" IS NOT NULL),
        LANGUAGENAME         varchar(50) NOT NULL
                                    CHECK ("LANGUAGENAME" IS NOT NULL)
)
go


CREATE TABLE MEMBATTR (
        MEMBATTRID           numeric(8) NOT NULL
                                    CHECK ("MEMBATTRID" IS NOT NULL),
        MEMBERID             numeric(8) NOT NULL
                                    CHECK ("MEMBERID" IS NOT NULL),
        ATTRID               numeric(8) NOT NULL
                                    CHECK ("ATTRID" IS NOT NULL),
        SEQ                  numeric(2) NOT NULL
                                    CHECK ("SEQ" IS NOT NULL),
        PRIV                 numeric(1) NOT NULL
                                    CHECK ("PRIV" IS NOT NULL),
        ATTRVALUE            varchar(255) NULL,
        REMARKS              varchar(255) NULL
)
go


ALTER TABLE MEMBATTR
        ADD PRIMARY KEY NONCLUSTERED (MEMBATTRID)
go


CREATE TABLE MEMBER (
        MEMBERID             numeric(8) NOT NULL
                                    CHECK ("MEMBERID" IS NOT NULL),
        CREATEDATE           datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("CREATEDATE" IS NOT NULL),
        DATEMOD              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("DATEMOD" IS NOT NULL),
        FIRSTNAME            varchar(50) NOT NULL
                                    CHECK ("FIRSTNAME" IS NOT NULL),
        LASTNAME             varchar(50) NOT NULL
                                    CHECK ("LASTNAME" IS NOT NULL),
        PRIV                 numeric(1) NOT NULL DEFAULT 2
                                    CHECK ("PRIV" IS NOT NULL),
        ACTIVE               numeric(1) NOT NULL DEFAULT 1
                                    CHECK ("ACTIVE" IS NOT NULL)
)
go


ALTER TABLE MEMBER
        ADD PRIMARY KEY NONCLUSTERED (MEMBERID)
go


CREATE TABLE POSTS (
        POSTDATE             datetime NULL,
        POPDATE              varchar(350) NULL,
        AUTHOR               varchar(350) NULL,
        SUBJECT              varchar(350) NULL,
        SORTSUBJECT          varchar(350) NULL,
        HEADER               varchar(2000) NULL,
        ID                   numeric(8) NULL,
        BODY                 text NULL,
        TESTDATE             varchar(50) NULL
)
go

CREATE INDEX POSTS_INDEX ON POSTS
(
        ID,
        SUBJECT
)
go


CREATE TABLE RATING (
        RATINGID             numeric(8) NOT NULL
                                    CHECK ("RATINGID" IS NOT NULL),
        USERID               numeric(8) NOT NULL
                                    CHECK ("USERID" IS NOT NULL),
        CONTENTID            numeric(8) NOT NULL
                                    CHECK ("CONTENTID" IS NOT NULL),
        DATECREATED          datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("DATECREATED" IS NOT NULL),
        LASTMOD              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("LASTMOD" IS NOT NULL),
        RATINGVALUE          numeric(1) NOT NULL
                                    CHECK ("RATINGVALUE" IS NOT NULL),
        CHECK (ratingvalue between 1 and 5)
)
go

CREATE UNIQUE INDEX ONERATINGPERUSERPERARTICLE ON RATING
(
        USERID,
        CONTENTID
)
go


ALTER TABLE RATING
        ADD PRIMARY KEY NONCLUSTERED (RATINGID)
go


CREATE TABLE REPLIES (
        COMMENTID            numeric(10) NULL,
        CONTENTID            numeric(10) NULL,
        USERID               varchar(50) NULL,
        MESSAGE              text NULL,
        DATEMOD              datetime NULL,
        GRRRS                numeric(5) NULL,
        MESSAGENAME          varchar(50) NULL
)
go


CREATE TABLE RESPONSE (
        CONRESID             numeric(11) NULL,
        CONTACTID            numeric(11) NULL,
        RESPONDER            numeric(11) NULL,
        RESPONSE             image NULL,
        MAILDATE             datetime NULL,
        ACTIVE               numeric(11) NULL,
        RESPNDR              numeric(10) NULL,
        RESPNS               numeric(10) NULL,
        REPLY                image NULL
)
go


CREATE TABLE SCHEME (
        SCHEMEID             numeric(8) NOT NULL
                                    CHECK ("SCHEMEID" IS NOT NULL),
        SCHEMEUSERID         numeric(8) NOT NULL
                                    CHECK ("SCHEMEUSERID" IS NOT NULL),
        SCHEMENAME           varchar(50) NOT NULL
                                    CHECK ("SCHEMENAME" IS NOT NULL),
        PRIV                 numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("PRIV" IS NOT NULL)
)
go


ALTER TABLE SCHEME
        ADD PRIMARY KEY NONCLUSTERED (SCHEMEID)
go


CREATE TABLE SCHEMEELEMENT (
        SCHEMEELEMID         numeric(8) NOT NULL
                                    CHECK ("SCHEMEELEMID" IS NOT NULL),
        SCHEMEID             numeric(8) NOT NULL
                                    CHECK ("SCHEMEID" IS NOT NULL),
        ELEMENTID            numeric(8) NOT NULL
                                    CHECK ("ELEMENTID" IS NOT NULL),
        SCHEMEELEMVALUE      varchar(100) NOT NULL
                                    CHECK ("SCHEMEELEMVALUE" IS NOT NULL)
)
go


ALTER TABLE SCHEMEELEMENT
        ADD PRIMARY KEY NONCLUSTERED (SCHEMEELEMID)
go


CREATE TABLE SEARCHINDEX (
        SINDEX               numeric(10) NULL,
        USERID               numeric(10) NULL,
        DATEMOD              datetime NULL
)
go


CREATE TABLE THELIST (
        POSTDATE             datetime NULL,
        POPDATE              varchar(350) NULL,
        AUTHOR               varchar(350) NULL,
        SUBJECT              varchar(350) NULL,
        SORTSUBJECT          varchar(350) NULL,
        HEADER               varchar(2000) NULL,
        ID                   numeric(8) NULL,
        BODY                 text NULL,
        CONTENT              image NULL
)
go


CREATE TABLE THETIPS (
        TIP_ID               numeric(8) NOT NULL
                                    CHECK ("TIP_ID" IS NOT NULL),
        TIP_DATE             datetime NOT NULL
                                    CHECK ("TIP_DATE" IS NOT NULL),
        AUTHOR_ID            numeric(8) NULL,
        TIP_TYPE             varchar(200) NULL,
        AUTHOR               varchar(50) NULL,
        BODY                 text NULL
)
go


ALTER TABLE THETIPS
        ADD PRIMARY KEY NONCLUSTERED (TIP_ID)
go


CREATE TABLE USERS (
        USERID               numeric(8) NOT NULL
                                    CHECK ("USERID" IS NOT NULL),
        WHO                  varchar(50) NOT NULL
                                    CHECK ("WHO" IS NOT NULL),
        PASS                 varchar(50) NOT NULL
                                    CHECK ("PASS" IS NOT NULL),
        CREATEDATE           datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("CREATEDATE" IS NOT NULL),
        DATEMOD              datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("DATEMOD" IS NOT NULL),
        LASTLOGIN            datetime NOT NULL DEFAULT GETDATE()
                                    CHECK ("LASTLOGIN" IS NOT NULL),
        LEAVEOUT             numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("LEAVEOUT" IS NOT NULL),
        NOTIFY               numeric(1) NOT NULL DEFAULT 1
                                    CHECK ("NOTIFY" IS NOT NULL),
        ADMIN                numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("ADMIN" IS NOT NULL),
        ACTIVE               numeric(1) NOT NULL DEFAULT 1
                                    CHECK ("ACTIVE" IS NOT NULL),
        GOD                  numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("GOD" IS NOT NULL),
        THELIST              numeric(1) NOT NULL DEFAULT 0
                                    CHECK ("THELIST" IS NOT NULL),
        USERPRIV             numeric(1) NOT NULL DEFAULT 1
                                    CHECK ("USERPRIV" IS NOT NULL),
        PRIV                 numeric(1) NOT NULL DEFAULT 3
                                    CHECK ("PRIV" IS NOT NULL),
        FIRSTNAME            varchar(50) NULL,
        LASTNAME             varchar(50) NULL,
        EMAIL                varchar(50) NULL,
        MEMBERID             numeric(8) NULL,
        COOKIE               varchar(50) NULL
)
go


ALTER TABLE USERS
        ADD PRIMARY KEY NONCLUSTERED (USERID)
go


ALTER TABLE MEMBATTR
        ADD FOREIGN KEY (MEMBERID)
                              REFERENCES MEMBER
go


ALTER TABLE MEMBATTR
        ADD FOREIGN KEY (ATTRID)
                              REFERENCES ATTRIBUTE
go


ALTER TABLE RATING
        ADD FOREIGN KEY (USERID)
                              REFERENCES USERS
go


ALTER TABLE RATING
        ADD FOREIGN KEY (CONTENTID)
                              REFERENCES CONTENT
go


ALTER TABLE SCHEME
        ADD FOREIGN KEY (SCHEMEUSERID)
                              REFERENCES USERS
go


ALTER TABLE SCHEMEELEMENT
        ADD FOREIGN KEY (SCHEMEID)
                              REFERENCES SCHEME
go


ALTER TABLE SCHEMEELEMENT
        ADD FOREIGN KEY (ELEMENTID)
                              REFERENCES ELEMENT
go




--=====================_41189917==_--





More information about the thesite mailing list