[geeklog-devel] g2 SQL Server schema

Thu Jan 23 11:43:25 EST 2003

SQL Server Schema - http://www.tsystemscorp.com/geeklog/g2sqlservercreate.sql


/*                                                          */
/*  File generated by "DeZign for databases"                */
/*  Create-date    :1/23/2003                               */
/*  Create-time    :10:35:14 AM                             */
/*  project-name   :Geeklog2                                */
/*  project-author :                                        */
/*                                                          */


CREATE TABLE Itemtypes(
ittTypeId int NOT NULL CONSTRAINT PK_Itemtypes1 PRIMARY KEY,
ittTypeName varchar(50) NOT NULL)
go


CREATE TABLE ItemStates(
isStateId tinyint NOT NULL CONSTRAINT PK_ItemStates1 PRIMARY KEY,
isStateName varchar(50) NOT NULL,
isDescription varchar(255) NOT NULL)
go


CREATE TABLE Categories(
catCategoryId int NOT NULL CONSTRAINT PK_Categories1 PRIMARY KEY,
catOwnerId int NOT NULL,
catGroupId int NOT NULL,
catPermOwner tinyint NOT NULL,
catPermAnon tinyint NOT NULL,
catName varchar(50) NOT NULL,
catPermMembers tinyint NOT NULL,
catPermGroup tinyint NOT NULL,
catParentCategoryId int NOT NULL,
catImageUrl varchar(128) DEFAULT NULL NULL,
catSortNum tinyint DEFAULT NULL NULL)
go


CREATE TABLE Users(
uUserId int NOT NULL CONSTRAINT PK_Users1 PRIMARY KEY,
uRegDate int NOT NULL,
uProfileViews int DEFAULT 0 NOT NULL,
uItemsPerPage tinyint DEFAULT 10 NOT NULL,
uLangId tinyint DEFAULT 1 NOT NULL,
uUserName varchar(25) NOT NULL,
uPassWord varchar(35) NOT NULL,
uEnabled tinyint NOT NULL,
uEmail varchar(128) NOT NULL,
uCommentModeId tinyint NULL,
uCommentOrderId tinyint NULL,
uCommentLimit int DEFAULT NULL NULL,
uCookieTimeout int NULL,
uLocale varchar(3) NULL,
uDateFormatId tinyint NULL,
uBlocksEnabled tinyint DEFAULT 1 NULL)
go


CREATE TABLE Items(
itItemId int NOT NULL CONSTRAINT PK_Items1 PRIMARY KEY,
itPermMembers tinyint NOT NULL,
itPermAnon tinyint NOT NULL,
itTypeId int NOT NULL,
itUserId int NOT NULL,
itCategoryId int NOT NULL,
itDate int NOT NULL,
itViews int DEFAULT 0 NOT NULL,
itStateId tinyint NOT NULL,
itEmails int DEFAULT 0 NOT NULL,
itNumRatings int DEFAULT 0 NOT NULL,
itRatingSum int DEFAULT 0 NOT NULL,
itOwnerId int NOT NULL,
itGroupId int NOT NULL,
itPermOwner tinyint NOT NULL,
itPermGroup tinyint NOT NULL,
itExpireDate int DEFAULT NULL NULL,
itParentItemId int DEFAULT NULL NULL)
go


CREATE TABLE Usercategorys(
ucUserId int NOT NULL,
ucCategoryId int NOT NULL,
CONSTRAINT PK_Usercategorys1 PRIMARY KEY (ucUserId,ucCategoryId))
go


CREATE TABLE UserSupps(
usUserId int NOT NULL CONSTRAINT PK_UserSupps1 PRIMARY KEY,
usSignature varchar(160) DEFAULT NULL NULL,
usBiography text DEFAULT NULL NULL,
usFileQuoteAmb FLOAT NULL,
usFirstName varchar(40) DEFAULT NULL NULL,
usLastName varchar(40) DEFAULT NULL NULL,
usHomepage varchar(128) DEFAULT NULL NULL)
go


CREATE TABLE ItemWatchs(
iwItemId int NOT NULL,
iwUserId int NOT NULL,
CONSTRAINT PK_ItemWatchs1 PRIMARY KEY (iwItemId,iwUserId))
go


CREATE TABLE UserWatchs(
uwUserId int NOT NULL,
uwWatchUserId int NOT NULL,
CONSTRAINT PK_UserWatchs1 PRIMARY KEY (uwUserId,uwWatchUserId))
go


CREATE TABLE UserBuddies(
ubUserId int NOT NULL,
ubBuddyUserId int NOT NULL,
CONSTRAINT PK_UserBuddies1 PRIMARY KEY (ubUserId,ubBuddyUserId))
go


CREATE TABLE Languages(
lngLangId tinyint DEFAULT 1 NOT NULL CONSTRAINT PK_Languages1 PRIMARY KEY,
lngName varchar(50) NULL)
go


CREATE TABLE BanReasons(
brReasonId int NOT NULL CONSTRAINT PK_BanReasons1 PRIMARY KEY,
brName varchar(64) NOT NULL,
brDescription varchar(255) NULL)
go


CREATE TABLE BanLists(
banId int NOT NULL CONSTRAINT PK_BanLists1 PRIMARY KEY,
banUserId int NOT NULL,
banReasonId int NOT NULL,
banIp varchar(15) DEFAULT NULL NULL,
banEnd int DEFAULT NULL NULL,
banStart int DEFAULT NULL NULL)
go


CREATE TABLE Articlearchives(
aaItemId int NOT NULL CONSTRAINT PK_Articlearchives1 PRIMARY KEY,
aaTitle varchar(128) NOT NULL,
aaText text NOT NULL)
go


CREATE TABLE Articles(
artArticleId int NOT NULL CONSTRAINT PK_Articles1 PRIMARY KEY,
artPages tinyint DEFAULT 1 NOT NULL,
artTitle varchar(128) NOT NULL,
artIntro text NULL)
go


CREATE TABLE ArticlePages(
apPageNumber tinyint NOT NULL,
apArticleId int NOT NULL,
apText text NOT NULL,
CONSTRAINT PK_ArticlePages1 PRIMARY KEY (apPageNumber,apArticleId))
go


CREATE TABLE Ratings(
rtValue tinyint NOT NULL CONSTRAINT PK_Ratings1 PRIMARY KEY,
rtLabel varchar(25) NOT NULL)
go


CREATE TABLE Comments(
cmtItemId int NOT NULL CONSTRAINT PK_Comments1 PRIMARY KEY,
cmtText text NOT NULL,
cmtParentId decimal NULL,
cmtTitle varchar(128) NULL)
go


CREATE TABLE FileTypes(
ftFileTypeId tinyint NOT NULL CONSTRAINT PK_FileTypes1 PRIMARY KEY,
ftDescription varchar(255) NOT NULL,
ftEextensions varchar(255) NULL)
go


CREATE TABLE Files(
fItemId int NOT NULL CONSTRAINT PK_Files1 PRIMARY KEY,
ftFileTypeId tinyint NOT NULL,
fFileTypeId tinyint NOT NULL,
fSize varchar(10) NOT NULL,
fDescription varchar(255) NULL)
go


CREATE TABLE Blocks(
blkBlockItemId int NOT NULL CONSTRAINT PK_Blocks1 PRIMARY KEY,
blkLocationId tinyint NOT NULL,
blkUserConfigurable tinyint DEFAULT 0 NOT NULL,
blkCollapsable tinyint DEFAULT 0 NOT NULL,
blkNewWindows tinyint DEFAULT 1 NOT NULL,
blkName varchar(50) NOT NULL,
blkBlockType tinyint NOT NULL,
blkTitle varchar(50) NOT NULL,
blkCategoryId int NOT NULL,
blkRdfUrl varchar(128) NULL,
blkRdfUpdated int NULL,
blkBlockFn varchar(64) NULL,
blkMaxItems tinyint DEFAULT NULL NULL,
blkSortNum tinyint NULL,
blkContent text NULL)
go


CREATE TABLE Polls(
pPollId int NOT NULL CONSTRAINT PK_Polls1 PRIMARY KEY,
pVotes int NOT NULL,
pQuestion varchar(255) NOT NULL)
go


CREATE TABLE PollAnswers(
paAnswerId int NOT NULL CONSTRAINT PK_PollAnswers1 PRIMARY KEY,
paItemId int NOT NULL,
paAnswer varchar(255) NOT NULL,
paVotes int NOT NULL)
go


CREATE TABLE PollVotes(
pvVoteId int NOT NULL CONSTRAINT PK_PollVotes1 PRIMARY KEY,
pvUserId int NOT NULL,
pvPollId int NOT NULL,
pvIpAddress varchar(15) NOT NULL)
go


CREATE TABLE Vars(
varName varchar(20) NOT NULL CONSTRAINT PK_Vars1 PRIMARY KEY,
varValue varchar(128) NOT NULL,
varDescription varchar(255) NULL)
go


CREATE TABLE UserBlocks(
ubUserId int NOT NULL,
ubBlockItemId int NOT NULL,
CONSTRAINT PK_UserBlocks1 PRIMARY KEY (ubUserId,ubBlockItemId))
go


CREATE TABLE PrivateMessageStates(
pmsStateId tinyint NOT NULL CONSTRAINT PK_PrivateMessageStates1 PRIMARY KEY,
pmsName varchar(128) NOT NULL)
go


CREATE TABLE PrivateMessages(
pmMessageId int NOT NULL CONSTRAINT PK_PrivateMessages1 PRIMARY KEY,
pmDate int NOT NULL,
pmStateId tinyint NOT NULL,
pmText text NOT NULL,
pmToUserId int NOT NULL,
pmSubject varchar(128) NULL,
pmFromUserId int NULL)
go


CREATE TABLE Modules(
modModuleId int NOT NULL CONSTRAINT PK_Modules1 PRIMARY KEY,
modName varchar(30) NOT NULL,
modEnabled tinyint DEFAULT 1 NOT NULL,
modAdminSortNum tinyint NULL,
modUserSortNum tinyint NULL)
go


CREATE TABLE aaApplications(
appId varchar(30) NOT NULL CONSTRAINT PK_aaApplications1 PRIMARY KEY,
appName varchar(50) NOT NULL,
appContactEmail varchar(50) DEFAULT NULL NULL)
go


CREATE TABLE aaAppPrivileges(
apAppId varchar(30) NOT NULL,
apPrivilegeCd varchar(20) NOT NULL,
apPrivilegeDesc varchar(128) NOT NULL,
CONSTRAINT PK_aaAppPrivileges1 PRIMARY KEY (apAppId,apPrivilegeCd))
go


CREATE TABLE aaGroups(
gGroupId int identity(1,1) NOT NULL CONSTRAINT PK_aaGroups1 PRIMARY KEY,
gAppId varchar(30) NOT NULL,
gGroupLogicaName varchar(50) NOT NULL,
gGroupDisplayName varchar(50) NOT NULL,
gGroupDescr varchar(255) NOT NULL,
gCreationDate int DEFAULT 0 NOT NULL,
gChangeDate int DEFAULT NULL NULL,
gChangeUserId int DEFAULT NULL NULL)
go


CREATE TABLE aaPrivilegeAccesses(
paPrivilegeCd varchar(20) NOT NULL,
paAppId varchar(30) NOT NULL,
paUserId int DEFAULT NULL NULL,
paGroupId int DEFAULT NULL NULL,
CONSTRAINT PK_aaPrivilegeAccesses1 PRIMARY KEY (paPrivilegeCd,paAppId))
go


CREATE TABLE aaUsers(
uUserId int identity(1,1) NOT NULL CONSTRAINT PK_aaUsers1 PRIMARY KEY,
uChangeDate int DEFAULT 0 NOT NULL,
uUserName varchar(75) NOT NULL,
uPassword varchar(20) NOT NULL,
uEmployeId varchar(20) NOT NULL,
uAccountLocked tinyint DEFAULT 0 NOT NULL,
uFailedAttempts tinyint DEFAULT 0 NOT NULL,
uPwdLastSet int DEFAULT 0 NOT NULL,
uCreationDate int DEFAULT 0 NOT NULL,
uChangeUserId int DEFAULT NULL NULL)
go


CREATE TABLE aaUserPasswords(
upUserId int NOT NULL,
upPassword varchar(20) NOT NULL,
upInsertDate varchar(20) NOT NULL,
CONSTRAINT PK_aaUserPasswords1 PRIMARY KEY (upUserId,upPassword,upInsertDate))
go


CREATE TABLE Links(
lnkItemId int NOT NULL CONSTRAINT PK_Links1 PRIMARY KEY,
lnkTitle varchar(128) NOT NULL,
lnkUrl varchar(128) NOT NULL,
lnkDescription text NULL)
go


CREATE TABLE aaGroupAssignments(
gaMainGroupId int NOT NULL,
gaUserId int NOT NULL,
gaAssignedGroupId int DEFAULT NULL NULL,
gaPrivilegeCache varchar(255) DEFAULT NULL NULL,
CONSTRAINT PK_aaGroupAssignments1 PRIMARY KEY (gaMainGroupId,gaUserId))
go


ALTER TABLE Items
ADD CONSTRAINT FK_Items_1
FOREIGN KEY (itCategoryId) REFERENCES Categories (catCategoryId)
go

ALTER TABLE Items
ADD CONSTRAINT FK_Items_2
FOREIGN KEY (itStateId) REFERENCES ItemStates (isStateId)
go

ALTER TABLE Items
ADD CONSTRAINT FK_Items_3
FOREIGN KEY (itTypeId) REFERENCES Itemtypes (ittTypeId)
go

ALTER TABLE Items
ADD CONSTRAINT FK_Items_4
FOREIGN KEY (itUserId) REFERENCES Users (uUserId)
go


ALTER TABLE Usercategorys
ADD CONSTRAINT FK_Usercategorys_1
FOREIGN KEY (ucCategoryId) REFERENCES Categories (catCategoryId)
go

ALTER TABLE Usercategorys
ADD CONSTRAINT FK_Usercategorys_2
FOREIGN KEY (ucUserId) REFERENCES Users (uUserId)
go


ALTER TABLE UserSupps
ADD CONSTRAINT FK_UserSupps_1
FOREIGN KEY (usUserId) REFERENCES Users (uUserId)
go


ALTER TABLE ItemWatchs
ADD CONSTRAINT FK_ItemWatchs_1
FOREIGN KEY (iwItemId) REFERENCES Items (itItemId)
go

ALTER TABLE ItemWatchs
ADD CONSTRAINT FK_ItemWatchs_2
FOREIGN KEY (iwUserId) REFERENCES Users (uUserId)
go


ALTER TABLE UserWatchs
ADD CONSTRAINT FK_UserWatchs_1
FOREIGN KEY (uwUserId) REFERENCES Users (uUserId)
go

ALTER TABLE UserWatchs
ADD CONSTRAINT FK_UserWatchs_2
FOREIGN KEY (uwWatchUserId) REFERENCES Users (uUserId)
go


ALTER TABLE UserBuddies
ADD CONSTRAINT FK_UserBuddies_1
FOREIGN KEY (ubBuddyUserId) REFERENCES Users (uUserId)
go

ALTER TABLE UserBuddies
ADD CONSTRAINT FK_UserBuddies_2
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId)
go


ALTER TABLE BanLists
ADD CONSTRAINT FK_BanLists_1
FOREIGN KEY (banReasonId) REFERENCES BanReasons (brReasonId)
go

ALTER TABLE BanLists
ADD CONSTRAINT FK_BanLists_2
FOREIGN KEY (banUserId) REFERENCES Users (uUserId)
go


ALTER TABLE Articles
ADD CONSTRAINT FK_Articles_1
FOREIGN KEY (artArticleId) REFERENCES Items (itItemId)
go


ALTER TABLE ArticlePages
ADD CONSTRAINT FK_ArticlePages_1
FOREIGN KEY (apArticleId) REFERENCES Articles (artArticleId)
go


ALTER TABLE Comments
ADD CONSTRAINT FK_Comments_1
FOREIGN KEY (cmtItemId) REFERENCES Items (itItemId)
go


ALTER TABLE Files
ADD CONSTRAINT FK_Files_1
FOREIGN KEY (ftFileTypeId) REFERENCES FileTypes (ftFileTypeId)
go

ALTER TABLE Files
ADD CONSTRAINT FK_Files_2
FOREIGN KEY (fItemId) REFERENCES Items (itItemId)
go


ALTER TABLE Polls
ADD CONSTRAINT FK_Polls_1
FOREIGN KEY (pPollId) REFERENCES Items (itItemId)
go


ALTER TABLE PollAnswers
ADD CONSTRAINT FK_PollAnswers_1
FOREIGN KEY (paAnswerId) REFERENCES Polls (pPollId)
go


ALTER TABLE PollVotes
ADD CONSTRAINT FK_PollVotes_1
FOREIGN KEY (pvPollId) REFERENCES Polls (pPollId)
go

ALTER TABLE PollVotes
ADD CONSTRAINT FK_PollVotes_2
FOREIGN KEY (pvUserId) REFERENCES Users (uUserId)
go


ALTER TABLE UserBlocks
ADD CONSTRAINT FK_UserBlocks_1
FOREIGN KEY (ubBlockItemId) REFERENCES Blocks (blkBlockItemId)
go

ALTER TABLE UserBlocks
ADD CONSTRAINT FK_UserBlocks_2
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId)
go


ALTER TABLE PrivateMessages
ADD CONSTRAINT FK_PrivateMessages_1
FOREIGN KEY (pmStateId) REFERENCES PrivateMessageStates (pmsStateId)
go

ALTER TABLE PrivateMessages
ADD CONSTRAINT FK_PrivateMessages_2
FOREIGN KEY (pmFromUserId) REFERENCES Users (uUserId)
go


ALTER TABLE aaAppPrivileges
ADD CONSTRAINT FK_aaAppPrivileges_1
FOREIGN KEY (apAppId) REFERENCES aaApplications (appId)
go


ALTER TABLE aaGroups
ADD CONSTRAINT FK_aaGroups_1
FOREIGN KEY (gAppId) REFERENCES aaApplications (appId)
go


/* ALTER TABLE aaPrivilegeAccesses
ADD CONSTRAINT FK_aaPrivilegeAccesses_1
FOREIGN KEY (paPrivilegeCd,paAppId) REFERENCES aaAppPrivileges 
(apPrivilegeCd,apAppId)
go */


ALTER TABLE aaUserPasswords
ADD CONSTRAINT FK_aaUserPasswords_1
FOREIGN KEY (upUserId) REFERENCES aaUsers (uUserId)
go


ALTER TABLE Links
ADD CONSTRAINT FK_Links_1
FOREIGN KEY (lnkItemId) REFERENCES Items (itItemId)
go


ALTER TABLE aaGroupAssignments
ADD CONSTRAINT FK_aaGroupAssignments_1
FOREIGN KEY (gaMainGroupId) REFERENCES aaGroups (gGroupId)
go

ALTER TABLE aaGroupAssignments
ADD CONSTRAINT FK_aaGroupAssignments_2
FOREIGN KEY (gaUserId) REFERENCES aaUsers (uUserId)
go


Dwight
dwight at trumbower.com