[geeklog-devel] postgresql schema

Thu Jan 23 11:05:52 EST 2003

Here is the schema for postgresql.
http://www.tsystemscorp.com/geeklog/g2postgresqlcreate.sql

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

CREATE TABLE Itemtypes(
ittTypeId int4 NOT NULL PRIMARY KEY,
ittTypeName varchar(50) NOT NULL);

CREATE TABLE ItemStates(
isStateId int2 NOT NULL PRIMARY KEY,
isStateName varchar(50) NOT NULL,
isDescription varchar(255) NOT NULL);

CREATE TABLE Categories(
catCategoryId int4 NOT NULL PRIMARY KEY,
catOwnerId int4 NOT NULL,
catGroupId int4 NOT NULL,
catPermOwner int2 NOT NULL,
catPermAnon int2 NOT NULL,
catName varchar(50) NOT NULL,
catPermMembers int2 NOT NULL,
catPermGroup int2 NOT NULL,
catParentCategoryId int4 NOT NULL,
catImageUrl varchar(128) DEFAULT NULL,
catSortNum int2 DEFAULT NULL);

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

CREATE TABLE Items(
itItemId int4 NOT NULL PRIMARY KEY,
itPermMembers int2 NOT NULL,
itPermAnon int2 NOT NULL,
itTypeId int4 NOT NULL,
itUserId int4 NOT NULL,
itCategoryId int4 NOT NULL,
itDate int4 NOT NULL,
itViews int4 DEFAULT 0 NOT NULL,
itStateId int2 NOT NULL,
itEmails int4 DEFAULT 0 NOT NULL,
itNumRatings int4 DEFAULT 0 NOT NULL,
itRatingSum int4 DEFAULT 0 NOT NULL,
itOwnerId int4 NOT NULL,
itGroupId int4 NOT NULL,
itPermOwner int2 NOT NULL,
itPermGroup int2 NOT NULL,
itExpireDate int4 DEFAULT NULL,
itParentItemId int4 DEFAULT NULL,
FOREIGN KEY (itCategoryId) REFERENCES Categories (catCategoryId),
FOREIGN KEY (itStateId) REFERENCES ItemStates (isStateId),
FOREIGN KEY (itTypeId) REFERENCES Itemtypes (ittTypeId),
FOREIGN KEY (itUserId) REFERENCES Users (uUserId));

CREATE TABLE Usercategorys(
ucUserId int4 NOT NULL,
ucCategoryId int4 NOT NULL,
FOREIGN KEY (ucCategoryId) REFERENCES Categories (catCategoryId),
FOREIGN KEY (ucUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ucUserId,ucCategoryId));

CREATE TABLE UserSupps(
usUserId int4 NOT NULL PRIMARY KEY,
usSignature varchar(160) DEFAULT NULL,
usBiography text DEFAULT NULL,
usFileQuoteAmb float8,
usFirstName varchar(40) DEFAULT NULL,
usLastName varchar(40) DEFAULT NULL,
usHomepage varchar(128) DEFAULT NULL,
FOREIGN KEY (usUserId) REFERENCES Users (uUserId));

CREATE TABLE ItemWatchs(
iwItemId int4 NOT NULL,
iwUserId int4 NOT NULL,
FOREIGN KEY (iwItemId) REFERENCES Items (itItemId),
FOREIGN KEY (iwUserId) REFERENCES Users (uUserId),
PRIMARY KEY (iwItemId,iwUserId));

CREATE TABLE UserWatchs(
uwUserId int4 NOT NULL,
uwWatchUserId int4 NOT NULL,
FOREIGN KEY (uwUserId) REFERENCES Users (uUserId),
FOREIGN KEY (uwWatchUserId) REFERENCES Users (uUserId),
PRIMARY KEY (uwUserId,uwWatchUserId));

CREATE TABLE UserBuddies(
ubUserId int4 NOT NULL,
ubBuddyUserId int4 NOT NULL,
FOREIGN KEY (ubBuddyUserId) REFERENCES Users (uUserId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubUserId,ubBuddyUserId));

CREATE TABLE Languages(
lngLangId int2 DEFAULT 1 NOT NULL PRIMARY KEY,
lngName varchar(50));

CREATE TABLE BanReasons(
brReasonId int4 NOT NULL PRIMARY KEY,
brName varchar(64) NOT NULL,
brDescription varchar(255));

CREATE TABLE BanLists(
banId int4 NOT NULL PRIMARY KEY,
banUserId int4 NOT NULL,
banReasonId int4 NOT NULL,
banIp varchar(15) DEFAULT NULL,
banEnd int4 DEFAULT NULL,
banStart int4 DEFAULT NULL,
FOREIGN KEY (banReasonId) REFERENCES BanReasons (brReasonId),
FOREIGN KEY (banUserId) REFERENCES Users (uUserId));

CREATE TABLE Articlearchives(
aaItemId int4 NOT NULL PRIMARY KEY,
aaTitle varchar(128) NOT NULL,
aaText text NOT NULL);

CREATE TABLE Articles(
artArticleId int4 NOT NULL PRIMARY KEY,
artPages int2 DEFAULT 1 NOT NULL,
artTitle varchar(128) NOT NULL,
artIntro text,
FOREIGN KEY (artArticleId) REFERENCES Items (itItemId));

CREATE TABLE ArticlePages(
apPageNumber int2 NOT NULL,
apArticleId int4 NOT NULL,
apText text NOT NULL,
FOREIGN KEY (apArticleId) REFERENCES Articles (artArticleId),
PRIMARY KEY (apPageNumber,apArticleId));

CREATE TABLE Ratings(
rtValue int2 NOT NULL PRIMARY KEY,
rtLabel varchar(25) NOT NULL);

CREATE TABLE Comments(
cmtItemId int4 NOT NULL PRIMARY KEY,
cmtText text NOT NULL,
cmtParentId decimal,
cmtTitle varchar(128),
FOREIGN KEY (cmtItemId) REFERENCES Items (itItemId));

CREATE TABLE FileTypes(
ftFileTypeId int2 NOT NULL PRIMARY KEY,
ftDescription varchar(255) NOT NULL,
ftEextensions varchar(255));

CREATE TABLE Files(
fItemId int4 NOT NULL PRIMARY KEY,
ftFileTypeId int2 NOT NULL,
fFileTypeId int2 NOT NULL,
fSize varchar(10) NOT NULL,
fDescription varchar(255),
FOREIGN KEY (ftFileTypeId) REFERENCES FileTypes (ftFileTypeId),
FOREIGN KEY (fItemId) REFERENCES Items (itItemId));

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

CREATE TABLE Polls(
pPollId int4 NOT NULL PRIMARY KEY,
pVotes int4 NOT NULL,
pQuestion varchar(255) NOT NULL,
FOREIGN KEY (pPollId) REFERENCES Items (itItemId));

CREATE TABLE PollAnswers(
paAnswerId int4 NOT NULL PRIMARY KEY,
paItemId int4 NOT NULL,
paAnswer varchar(255) NOT NULL,
paVotes int4 NOT NULL,
FOREIGN KEY (paAnswerId) REFERENCES Polls (pPollId));

CREATE TABLE PollVotes(
pvVoteId int4 NOT NULL PRIMARY KEY,
pvUserId int4 NOT NULL,
pvPollId int4 NOT NULL,
pvIpAddress varchar(15) NOT NULL,
FOREIGN KEY (pvPollId) REFERENCES Polls (pPollId),
FOREIGN KEY (pvUserId) REFERENCES Users (uUserId));

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

CREATE TABLE UserBlocks(
ubUserId int4 NOT NULL,
ubBlockItemId int4 NOT NULL,
FOREIGN KEY (ubBlockItemId) REFERENCES Blocks (blkBlockItemId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubUserId,ubBlockItemId));

CREATE TABLE PrivateMessageStates(
pmsStateId int2 NOT NULL PRIMARY KEY,
pmsName varchar(128) NOT NULL);

CREATE TABLE PrivateMessages(
pmMessageId int4 NOT NULL PRIMARY KEY,
pmDate int4 NOT NULL,
pmStateId int2 NOT NULL,
pmText text NOT NULL,
pmToUserId int4 NOT NULL,
pmSubject varchar(128),
pmFromUserId int4,
FOREIGN KEY (pmStateId) REFERENCES PrivateMessageStates (pmsStateId),
FOREIGN KEY (pmFromUserId) REFERENCES Users (uUserId));

CREATE TABLE Modules(
modModuleId int4 NOT NULL PRIMARY KEY,
modName varchar(30) NOT NULL,
modEnabled int2 DEFAULT 1 NOT NULL,
modAdminSortNum int2,
modUserSortNum int2);

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

CREATE TABLE aaAppPrivileges(
apAppId varchar(30) NOT NULL,
apPrivilegeCd varchar(20) NOT NULL,
apPrivilegeDesc varchar(128) NOT NULL,
FOREIGN KEY (apAppId) REFERENCES aaApplications (appId),
PRIMARY KEY (apAppId,apPrivilegeCd));

CREATE TABLE aaGroups(
gGroupId serial NOT NULL PRIMARY KEY,
gAppId varchar(30) NOT NULL,
gGroupLogicaName varchar(50) NOT NULL,
gGroupDisplayName varchar(50) NOT NULL,
gGroupDescr varchar(255) NOT NULL,
gCreationDate int4 DEFAULT 0 NOT NULL,
gChangeDate int4 DEFAULT NULL,
gChangeUserId int4 DEFAULT NULL,
FOREIGN KEY (gAppId) REFERENCES aaApplications (appId));

CREATE TABLE aaPrivilegeAccesses(
paPrivilegeCd varchar(20) NOT NULL,
paAppId varchar(30) NOT NULL,
paUserId int4 DEFAULT NULL,
paGroupId int4 DEFAULT NULL,
FOREIGN KEY (paPrivilegeCd,paAppId) REFERENCES aaAppPrivileges 
(apPrivilegeCd,apAppId),
PRIMARY KEY (paPrivilegeCd,paAppId));

CREATE TABLE aaUsers(
uUserId serial NOT NULL PRIMARY KEY,
uChangeDate int4 DEFAULT 0 NOT NULL,
uUserName varchar(75) NOT NULL,
uPassword varchar(20) NOT NULL,
uEmployeId varchar(20) NOT NULL,
uAccountLocked int2 DEFAULT 0 NOT NULL,
uFailedAttempts int2 DEFAULT 0 NOT NULL,
uPwdLastSet int4 DEFAULT 0 NOT NULL,
uCreationDate int4 DEFAULT 0 NOT NULL,
uChangeUserId int4 DEFAULT NULL);

CREATE TABLE aaUserPasswords(
upUserId int4 NOT NULL,
upPassword varchar(20) NOT NULL,
upInsertDate varchar(20) NOT NULL,
FOREIGN KEY (upUserId) REFERENCES aaUsers (uUserId),
PRIMARY KEY (upUserId,upPassword,upInsertDate));

CREATE TABLE Links(
lnkItemId int4 NOT NULL PRIMARY KEY,
lnkTitle varchar(128) NOT NULL,
lnkUrl varchar(128) NOT NULL,
lnkDescription text,
FOREIGN KEY (lnkItemId) REFERENCES Items (itItemId));

CREATE TABLE aaGroupAssignments(
gaMainGroupId int4 NOT NULL,
gaUserId int4 NOT NULL,
gaAssignedGroupId int4 DEFAULT NULL,
gaPrivilegeCache varchar(255) DEFAULT NULL,
FOREIGN KEY (gaMainGroupId) REFERENCES aaGroups (gGroupId),
FOREIGN KEY (gaUserId) REFERENCES aaUsers (uUserId));

Dwight Trumbower

T Systems Corp
Custom Database Solutions
dwight at tsystemscorp.com
920-667-4438