X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fetc%2Fschema.Pg;h=25455be6af40635ff81dd9fd007faa49b37f7242;hp=21d981b4a0ef4885d71c5dbcb6fd9dad6d7ce6a1;hb=681a340f6be4184b1472a8e1fa9cd5d074f6f325;hpb=0ebeec96313dd7edfca340f01f8fbbbac1f4aa1d diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg index 21d981b4a..25455be6a 100755 --- a/rt/etc/schema.Pg +++ b/rt/etc/schema.Pg @@ -1,267 +1,723 @@ -CREATE TABLE KeywordSelects ( - id serial NOT NULL , - Name varchar(255) , - Keyword integer , - Single integer , - Depth integer NOT NULL DEFAULT 0 , - ObjectType varchar(32) NOT NULL , - ObjectField varchar(32) , - ObjectValue varchar(255) , - Disabled int2 NOT NULL DEFAULT 0 , - PRIMARY KEY (id) -); -CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword); -CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue); +------------------------------------------------------------------ +-- My2Pg 1.23 translated dump +-- +------------------------------------------------------------------ + + + +-- +-- Sequences for table ATTACHMENTS +-- + +CREATE SEQUENCE attachments_id_seq; + + + CREATE TABLE Attachments ( - id serial NOT NULL , + id INTEGER DEFAULT nextval('attachments_id_seq'), TransactionId integer NOT NULL , - Parent integer , - MessageId varchar(160) , - Subject varchar(255) , - Filename varchar(255) , - ContentType varchar(80) , - ContentEncoding varchar(80) , - Content TEXT , - Headers TEXT , - Creator integer , - Created timestamp , + Parent integer NOT NULL DEFAULT 0 , + MessageId varchar(160) NULL , + Subject varchar(255) NULL , + Filename varchar(255) NULL , + ContentType varchar(80) NULL , + ContentEncoding varchar(80) NULL , + Content text NULL , + Headers text NULL , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE INDEX Attachments1 ON Attachments (Parent); -CREATE INDEX Attachments2 ON Attachments (TransactionId); -CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); + +CREATE INDEX Attachments1 ON Attachments (Parent) ; +CREATE INDEX Attachments2 ON Attachments (TransactionId) ; +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; + + + + + +-- +-- Sequences for table QUEUES +-- + +CREATE SEQUENCE queues_id_seq; + CREATE TABLE Queues ( - id serial NOT NULL , - Name varchar(120) NOT NULL , - Description varchar(120) , - CorrespondAddress varchar(120) , - CommentAddress varchar(120) , - InitialPriority integer , - FinalPriority integer , - DefaultDueIn integer , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , - Disabled int2 NOT NULL DEFAULT 0 , + id INTEGER DEFAULT nextval('queues_id_seq'), + Name varchar(200) NOT NULL , + Description varchar(255) NULL , + CorrespondAddress varchar(120) NULL , + CommentAddress varchar(120) NULL , + Lifecycle varchar(32) NULL, + SubjectTag varchar(120) NULL , + InitialPriority integer NOT NULL DEFAULT 0 , + FinalPriority integer NOT NULL DEFAULT 0 , + DefaultDueIn integer NOT NULL DEFAULT 0 , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + Disabled integer NOT NULL DEFAULT 0 , PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX Queues1 ON Queues (Name); +CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ; + + + + + + + +-- +-- Sequences for table LINKS +-- + +CREATE SEQUENCE links_id_seq; + CREATE TABLE Links ( - id serial NOT NULL , - Base varchar(240) , - Target varchar(240) , + id INTEGER DEFAULT nextval('links_id_seq'), + Base varchar(240) NULL , + Target varchar(240) NULL , Type varchar(20) NOT NULL , - LocalTarget integer , - LocalBase integer , - LastUpdatedBy integer , - LastUpdated timestamp , - Creator integer , - Created timestamp , + LocalTarget integer NOT NULL DEFAULT 0 , + LocalBase integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); -CREATE TABLE Groups ( - id serial NOT NULL , - Name varchar(16) , - Description varchar(64) , - Pseudo integer NOT NULL DEFAULT 0 , - PRIMARY KEY (id) +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; +CREATE INDEX Links4 ON Links(Type,LocalBase); + + + + + + + +-- +-- Sequences for table PRINCIPALS +-- + +CREATE SEQUENCE principals_id_seq; + +CREATE TABLE Principals ( + id INTEGER DEFAULT nextval('principals_id_seq') not null, + PrincipalType VARCHAR(16) not null, + ObjectId integer, + Disabled integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX Groups1 ON Groups (Name); -CREATE TABLE Watchers ( - id serial NOT NULL , - Type varchar(16) , - Scope varchar(16) , - Value integer , - Email varchar(255) , - Quiet integer , - Owner integer , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , + +CREATE INDEX Principals2 ON Principals (ObjectId); + + + + + + + + +-- +-- Sequences for table GROUPS +-- + +CREATE SEQUENCE groups_id_seq; + +CREATE TABLE Groups ( + id INTEGER DEFAULT nextval('groups_id_seq'), + Name varchar(200) NULL , + Description varchar(255) NULL , + Domain varchar(64), + Type varchar(64), + Instance integer, + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); +CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance); +CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance); +CREATE INDEX Groups3 On Groups (Instance); + + + + + + + + +-- +-- Sequences for table SCRIPCONDITIONS +-- + +CREATE SEQUENCE scripconditions_id_seq; + CREATE TABLE ScripConditions ( - id serial NOT NULL , - Name varchar(255) , - Description varchar(255) , - ExecModule varchar(60) , - Argument varchar(255) , - ApplicableTransTypes varchar(60) , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , + id INTEGER DEFAULT nextval('scripconditions_id_seq'), + Name varchar(200) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + ApplicableTransTypes varchar(60) NULL , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); + + + + + + +-- +-- Sequences for table TRANSACTIONS +-- + +CREATE SEQUENCE transactions_id_seq; + CREATE TABLE Transactions ( - id serial NOT NULL , - EffectiveTicket integer , - Ticket integer , - TimeTaken integer , - Type varchar(20) , - Field varchar(40) , - OldValue varchar(255) , - NewValue varchar(255) , - Data varchar(100) , - Creator integer , - Created timestamp , + id INTEGER DEFAULT nextval('transactions_id_seq'), + ObjectType varchar(255) NOT NULL , + ObjectId integer NOT NULL DEFAULT 0 , + TimeTaken integer NOT NULL DEFAULT 0 , + Type varchar(20) NULL , + Field varchar(40) NULL , + OldValue varchar(255) NULL , + NewValue varchar(255) NULL , + ReferenceType varchar(255) NULL, + OldReference integer NULL , + NewReference integer NULL , + Data varchar(255) NULL , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE INDEX Transactions1 ON Transactions (Ticket); -CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); + + + + + + + +-- +-- Sequences for table SCRIPS +-- + +CREATE SEQUENCE scrips_id_seq; + CREATE TABLE Scrips ( - id serial NOT NULL , - ScripCondition integer , - ScripAction integer , - Stage varchar(32) , - Queue integer , - Template integer , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , + id INTEGER DEFAULT nextval('scrips_id_seq'), + Description varchar(255), + ScripCondition integer NOT NULL DEFAULT 0 , + ScripAction integer NOT NULL DEFAULT 0 , + CustomIsApplicableCode text NULL , + CustomPrepareCode text NULL , + CustomCommitCode text NULL , + Disabled integer NOT NULL DEFAULT 0 , + Template varchar(200) NOT NULL, + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE TABLE ACL ( - id serial NOT NULL , - PrincipalId integer , - PrincipalType varchar(25) , - RightName varchar(25) , - RightScope varchar(25) , - RightAppliesTo integer , + + +CREATE SEQUENCE objectscrips_id_seq; + +CREATE TABLE ObjectScrips ( + id INTEGER DEFAULT nextval('objectscrips_id_seq'), + Scrip integer NOT NULL, + Stage varchar(32) NOT NULL DEFAULT 'TransactionCreate' , + ObjectId integer NOT NULL, + SortOrder integer NOT NULL DEFAULT 0 , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); -CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); -CREATE TABLE GroupMembers ( - id serial NOT NULL , - GroupId integer , - UserId integer , + +CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip); + + + +-- +-- Sequences for table ACL +-- + +CREATE SEQUENCE acl_id_seq; + +CREATE TABLE ACL ( + id INTEGER DEFAULT nextval('acl_id_seq'), + PrincipalType varchar(25) NOT NULL, + + PrincipalId integer NOT NULL , + RightName varchar(25) NOT NULL , + ObjectType varchar(25) NOT NULL , + ObjectId integer NOT NULL DEFAULT 0, + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId); -CREATE TABLE ObjectKeywords ( - id serial NOT NULL , - Keyword integer NOT NULL , - KeywordSelect integer NOT NULL , - ObjectType varchar(32) NOT NULL , - ObjectId integer NOT NULL , + +CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); + + + + + + + + +-- +-- Sequences for table GROUPMEMBERS +-- + +CREATE SEQUENCE groupmembers_id_seq; + +CREATE TABLE GroupMembers ( + id INTEGER DEFAULT nextval('groupmembers_id_seq'), + GroupId integer NOT NULL DEFAULT 0, + MemberId integer NOT NULL DEFAULT 0, + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword); -CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType); -CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword); -CREATE TABLE Keywords ( - id serial NOT NULL , - Name varchar(255) NOT NULL , - Description varchar(255) , - Parent integer , - Disabled int2 NOT NULL DEFAULT 0 , - PRIMARY KEY (id) + +CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId); + + + + + + + +-- +-- Sequences for table CACHEDGROUPMEMBERS +-- + +CREATE SEQUENCE cachedgroupmembers_id_seq; + +CREATE TABLE CachedGroupMembers ( + id int DEFAULT nextval('cachedgroupmembers_id_seq'), + GroupId int, + MemberId int, + Via int, + ImmediateParentId int, + Disabled integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); -CREATE INDEX Keywords2 ON Keywords (Name); -CREATE INDEX Keywords3 ON Keywords (Parent); + +CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled); +CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); +CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId); + + + + + + + +-- +-- Sequences for table USERS +-- + +CREATE SEQUENCE users_id_seq; + CREATE TABLE Users ( - id serial NOT NULL , - Name varchar(120) NOT NULL , - Password varchar(40) , - Comments TEXT , - Signature TEXT , - EmailAddress varchar(120) , - FreeformContactInfo TEXT , - Organization varchar(200) , - Privileged integer , - RealName varchar(120) , - Nickname varchar(16) , - Lang varchar(16) , - EmailEncoding varchar(16) , - WebEncoding varchar(16) , - ExternalContactInfoId varchar(100) , - ContactInfoSystem varchar(30) , - ExternalAuthId varchar(100) , - AuthSystem varchar(30) , - Gecos varchar(16) , - HomePhone varchar(30) , - WorkPhone varchar(30) , - MobilePhone varchar(30) , - PagerPhone varchar(30) , - Address1 varchar(200) , - Address2 varchar(200) , - City varchar(100) , - State varchar(100) , - Zip varchar(16) , - Country varchar(50) , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , - Disabled int2 NOT NULL DEFAULT 0 , + id INTEGER DEFAULT nextval('users_id_seq'), + Name varchar(200) NOT NULL , + Password varchar(256) NULL , + AuthToken varchar(16) NULL , + Comments text NULL , + Signature text NULL , + EmailAddress varchar(120) NULL , + FreeformContactInfo text NULL , + Organization varchar(200) NULL , + RealName varchar(120) NULL , + NickName varchar(16) NULL , + Lang varchar(16) NULL , + EmailEncoding varchar(16) NULL , + WebEncoding varchar(16) NULL , + ExternalContactInfoId varchar(100) NULL , + ContactInfoSystem varchar(30) NULL , + ExternalAuthId varchar(100) NULL , + AuthSystem varchar(30) NULL , + Gecos varchar(16) NULL , + HomePhone varchar(30) NULL , + WorkPhone varchar(30) NULL , + MobilePhone varchar(30) NULL , + PagerPhone varchar(30) NULL , + Address1 varchar(200) NULL , + Address2 varchar(200) NULL , + City varchar(100) NULL , + State varchar(100) NULL , + Zip varchar(16) NULL , + Country varchar(50) NULL , + Timezone varchar(50) NULL , + PGPKey text NULL, + SMIMECertificate text NULL, + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); -CREATE UNIQUE INDEX Users1 ON Users (Name); -CREATE INDEX Users3 ON Users (id, EmailAddress); + + +CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name)) ; CREATE INDEX Users4 ON Users (EmailAddress); + + + + + + + + +-- +-- Sequences for table TICKETS +-- + +CREATE SEQUENCE tickets_id_seq; + CREATE TABLE Tickets ( - id serial NOT NULL , - EffectiveId integer , - Queue integer , - Type varchar(16) , - IssueStatement integer , - Resolution integer , - Owner integer , - Subject varchar(200) DEFAULT '[no subject]' , - InitialPriority integer , - FinalPriority integer , - Priority integer , - Status varchar(10) , - TimeWorked integer , - TimeLeft integer , - Told timestamp , - Starts timestamp , - Started timestamp , - Due timestamp , - Resolved timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , - Creator integer , - Created timestamp , - Disabled int2 NOT NULL DEFAULT 0 , + id INTEGER DEFAULT nextval('tickets_id_seq'), + EffectiveId integer NOT NULL DEFAULT 0 , + IsMerged smallint NULL DEFAULT NULL , + Queue integer NOT NULL DEFAULT 0 , + Type varchar(16) NULL , + IssueStatement integer NOT NULL DEFAULT 0 , + Resolution integer NOT NULL DEFAULT 0 , + Owner integer NOT NULL DEFAULT 0 , + Subject varchar(200) NULL DEFAULT '[no subject]' , + InitialPriority integer NOT NULL DEFAULT 0 , + FinalPriority integer NOT NULL DEFAULT 0 , + Priority integer NOT NULL DEFAULT 0 , + TimeEstimated integer NOT NULL DEFAULT 0 , + TimeWorked integer NOT NULL DEFAULT 0 , + Status varchar(64) NULL , + TimeLeft integer NOT NULL DEFAULT 0 , + Told TIMESTAMP NULL , + Starts TIMESTAMP NULL , + Started TIMESTAMP NULL , + Due TIMESTAMP NULL , + Resolved TIMESTAMP NULL , + + WillResolve TIMESTAMP NULL , + + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + Disabled integer NOT NULL DEFAULT 0 , PRIMARY KEY (id) + ); -CREATE INDEX Tickets1 ON Tickets (Queue, Status); -CREATE INDEX Tickets2 ON Tickets (Owner); -CREATE INDEX Tickets3 ON Tickets (EffectiveId); -CREATE INDEX Tickets4 ON Tickets (id, Status); -CREATE INDEX Tickets5 ON Tickets (id, EffectiveId); + +CREATE INDEX Tickets1 ON Tickets (Queue, Status) ; +CREATE INDEX Tickets2 ON Tickets (Owner) ; +CREATE INDEX Tickets3 ON Tickets (EffectiveId) ; + + +-- +-- Sequences for table SCRIPACTIONS +-- + +CREATE SEQUENCE scripactions_id_seq; + CREATE TABLE ScripActions ( - id serial NOT NULL , - Name varchar(255) , - Description varchar(255) , - ExecModule varchar(60) , - Argument varchar(255) , - Creator integer , - Created timestamp , - LastUpdatedBy integer , - LastUpdated timestamp , + id INTEGER DEFAULT nextval('scripactions_id_seq'), + Name varchar(200) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + ); + + + + + + + +-- +-- Sequences for table TEMPLATES +-- + +CREATE SEQUENCE templates_id_seq; + CREATE TABLE Templates ( - id serial NOT NULL , + id INTEGER DEFAULT nextval('templates_id_seq'), Queue integer NOT NULL DEFAULT 0 , - Name varchar(40) NOT NULL , - Description varchar(120) , - Type varchar(16) , - Language varchar(16) , - TranslationOf integer , - Content TEXT , - LastUpdated timestamp , - LastUpdatedBy integer , - Creator integer , - Created timestamp , + Name varchar(200) NOT NULL , + Description varchar(255) NULL , + Type varchar(16) NULL , + Content text NULL , + LastUpdated TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + PRIMARY KEY (id) + +); + + + + + + + +-- +-- Sequences for table TICKETCUSTOMFIELDVALUES +-- + +CREATE SEQUENCE objectcustomfieldvalues_id_s; + +CREATE TABLE ObjectCustomFieldValues ( + id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'), + CustomField int NOT NULL , + ObjectType varchar(255) NULL , + ObjectId int NOT NULL , + SortOrder integer NOT NULL DEFAULT 0 , + + Content varchar(255) NULL , + LargeContent text NULL, + ContentType varchar(80) NULL, + ContentEncoding varchar(80) NULL , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + Disabled integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) + +); + +CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content); +CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); + + + + + + + +-- +-- Sequences for table CUSTOMFIELDS +-- + +CREATE SEQUENCE customfields_id_seq; + +CREATE TABLE CustomFields ( + id INTEGER DEFAULT nextval('customfields_id_seq'), + Name varchar(200) NULL , + Type varchar(200) NULL , + RenderType varchar(64) NULL , + MaxValues integer NOT NULL DEFAULT 0 , + ValuesClass varchar(64) NULL , + BasedOn integer NULL, + Pattern varchar(65536) NULL , + LookupType varchar(255) NOT NULL , + Description varchar(255) NULL , + SortOrder integer NOT NULL DEFAULT 0 , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + Disabled integer NOT NULL DEFAULT 0 , + Required integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) + +); + + + + + +CREATE SEQUENCE objectcustomfields_id_s; + +CREATE TABLE ObjectCustomFields ( + id INTEGER DEFAULT nextval('objectcustomfields_id_s'), + CustomField integer NOT NULL, + ObjectId integer NOT NULL, + SortOrder integer NOT NULL DEFAULT 0 , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + PRIMARY KEY (id) + +); + + + + + + + +-- +-- Sequences for table CUSTOMFIELDVALUES +-- + +CREATE SEQUENCE customfieldvalues_id_seq; + +CREATE TABLE CustomFieldValues ( + id INTEGER DEFAULT nextval('customfieldvalues_id_seq'), + CustomField int NOT NULL , + Name varchar(200) NULL , + Description varchar(255) NULL , + SortOrder integer NOT NULL DEFAULT 0 , + Category varchar(255) NULL , + + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , + PRIMARY KEY (id) + +); + +CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); + + + + + + +CREATE SEQUENCE attributes_id_seq; + +CREATE TABLE Attributes ( + id INTEGER DEFAULT nextval('attributes_id_seq'), + Name varchar(255) NOT NULL , + Description varchar(255) NULL , + Content text, + ContentType varchar(16), + ObjectType varchar(64), + ObjectId integer, + Creator integer NOT NULL DEFAULT 0 , + Created TIMESTAMP NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated TIMESTAMP NULL , PRIMARY KEY (id) + +); + +CREATE INDEX Attributes1 on Attributes(Name); +CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId); + + + + + +-- sessions is used by Apache::Session to keep sessions in the database. +-- We should have a reaper script somewhere. + +CREATE TABLE sessions ( + id char(32) NOT NULL, + a_session bytea, + LastUpdated TIMESTAMP not null default current_timestamp, + PRIMARY KEY (id) + +); + + +CREATE TABLE Classes ( +id SERIAL, +Name varchar(255) NOT NULL DEFAULT '', +Description varchar(255) NOT NULL DEFAULT '', +SortOrder integer NOT NULL DEFAULT 0, +Disabled smallint NOT NULL DEFAULT 0, +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL, +HotList smallint NOT NULL DEFAULT 0, +PRIMARY KEY (id) +); + +CREATE TABLE Articles ( +id SERIAL, +Name varchar(255) NOT NULL DEFAULT '', +Summary varchar(255) NOT NULL DEFAULT '', +SortOrder integer NOT NULL DEFAULT 0, +Class integer NOT NULL DEFAULT 0, +Parent integer NOT NULL DEFAULT 0, +URI varchar(255), +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL, +PRIMARY KEY (id) +); + + +CREATE TABLE Topics ( +id SERIAL, +Parent integer NOT NULL DEFAULT 0, +Name varchar(255) NOT NULL DEFAULT '', +Description varchar(255) NOT NULL DEFAULT '', +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL, +PRIMARY KEY (id) +); + + +CREATE TABLE ObjectTopics ( +id SERIAL, +Topic integer NOT NULL, +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL, +PRIMARY KEY (id) +); + + +CREATE TABLE ObjectClasses ( +id SERIAL, +Class integer NOT NULL, +ObjectType varchar(255) NOT NULL DEFAULT '', +ObjectId integer NOT NULL, +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL, +PRIMARY KEY (id) ); +