X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Fetc%2Fschema.Pg;h=25455be6af40635ff81dd9fd007faa49b37f7242;hb=7903b3fb9ae73fc5fb46f98575141fbe9713f880;hp=48525c8d7bd3b4f5175b7ffcdb204c4112c3da37;hpb=5c96d46d56f2066bb40d9a34c4db56f53f43c6f2;p=freeside.git diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg index 48525c8d7..25455be6a 100755 --- a/rt/etc/schema.Pg +++ b/rt/etc/schema.Pg @@ -11,7 +11,7 @@ CREATE SEQUENCE attachments_id_seq; --- {{{ Attachments + CREATE TABLE Attachments ( id INTEGER DEFAULT nextval('attachments_id_seq'), @@ -33,9 +33,9 @@ CREATE TABLE Attachments ( CREATE INDEX Attachments1 ON Attachments (Parent) ; CREATE INDEX Attachments2 ON Attachments (TransactionId) ; CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; --- }}} --- {{{ Queues + + -- @@ -50,6 +50,8 @@ CREATE TABLE Queues ( 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 , @@ -61,11 +63,11 @@ CREATE TABLE Queues ( PRIMARY KEY (id) ); -CREATE UNIQUE INDEX Queues1 ON Queues (Name) ; +CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ; + + --- }}} --- {{{ Links @@ -92,9 +94,9 @@ CREATE TABLE Links ( CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; CREATE INDEX Links4 ON Links(Type,LocalBase); --- }}} --- {{{ Principals + + @@ -116,9 +118,9 @@ CREATE TABLE Principals ( CREATE INDEX Principals2 ON Principals (ObjectId); --- }}} --- {{{ Groups + + @@ -135,16 +137,21 @@ CREATE TABLE Groups ( 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 UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name); -CREATE INDEX Groups2 On Groups (Type, Instance, Domain); +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); + + --- }}} --- {{{ ScripConditions @@ -170,9 +177,9 @@ CREATE TABLE ScripConditions ( ); --- }}} --- {{{ Transactions + + -- @@ -202,9 +209,9 @@ CREATE TABLE Transactions ( ); CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); --- }}} --- {{{ Scrips + + @@ -219,14 +226,29 @@ CREATE TABLE Scrips ( Description varchar(255), ScripCondition integer NOT NULL DEFAULT 0 , ScripAction integer NOT NULL DEFAULT 0 , - ConditionRules text NULL , - ActionRules text NULL , CustomIsApplicableCode text NULL , CustomPrepareCode text NULL , CustomCommitCode text NULL , - Stage varchar(32) NULL , - Queue integer NOT NULL DEFAULT 0 , - Template integer NOT NULL DEFAULT 0 , + 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 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 , @@ -235,9 +257,8 @@ CREATE TABLE Scrips ( ); --- }}} +CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip); --- {{{ ACL -- @@ -254,8 +275,10 @@ CREATE TABLE ACL ( RightName varchar(25) NOT NULL , ObjectType varchar(25) NOT NULL , ObjectId integer NOT NULL DEFAULT 0, - DelegatedBy integer NOT NULL DEFAULT 0, - DelegatedFrom 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) ); @@ -263,9 +286,9 @@ CREATE TABLE ACL ( CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); --- }}} --- {{{ GroupMembers + + @@ -279,15 +302,19 @@ 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 GroupMembers1 ON GroupMembers(GroupId, MemberId); --- }}} --- {{{ GroupMembersCache + + @@ -308,13 +335,13 @@ CREATE TABLE CachedGroupMembers ( ); -CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId); -CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId); -CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); +CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled); +CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); +CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId); + + --- }}} --- {{{ Users @@ -327,7 +354,8 @@ CREATE SEQUENCE users_id_seq; CREATE TABLE Users ( id INTEGER DEFAULT nextval('users_id_seq'), Name varchar(200) NOT NULL , - Password varchar(40) NULL , + Password varchar(256) NULL , + AuthToken varchar(16) NULL , Comments text NULL , Signature text NULL , EmailAddress varchar(120) NULL , @@ -355,6 +383,7 @@ CREATE TABLE Users ( Country varchar(50) NULL , Timezone varchar(50) NULL , PGPKey text NULL, + SMIMECertificate text NULL, Creator integer NOT NULL DEFAULT 0 , Created TIMESTAMP NULL , @@ -365,14 +394,13 @@ CREATE TABLE Users ( ); -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); --- }}} --- {{{ Tickets + + @@ -385,6 +413,7 @@ CREATE SEQUENCE tickets_id_seq; CREATE TABLE Tickets ( 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 , @@ -396,7 +425,7 @@ CREATE TABLE Tickets ( Priority integer NOT NULL DEFAULT 0 , TimeEstimated integer NOT NULL DEFAULT 0 , TimeWorked integer NOT NULL DEFAULT 0 , - Status varchar(10) NULL , + Status varchar(64) NULL , TimeLeft integer NOT NULL DEFAULT 0 , Told TIMESTAMP NULL , Starts TIMESTAMP NULL , @@ -404,6 +433,7 @@ CREATE TABLE Tickets ( Due TIMESTAMP NULL , Resolved TIMESTAMP NULL , + WillResolve TIMESTAMP NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated TIMESTAMP NULL , @@ -417,13 +447,6 @@ CREATE TABLE Tickets ( 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) ; - --- }}} - --- {{{ ScripActions - -- @@ -446,9 +469,9 @@ CREATE TABLE ScripActions ( ); --- }}} --- {{{ Templates + + @@ -464,8 +487,6 @@ CREATE TABLE Templates ( Name varchar(200) NOT NULL , Description varchar(255) NULL , Type varchar(16) NULL , - Language varchar(16) NULL , - TranslationOf integer NOT NULL DEFAULT 0 , Content text NULL , LastUpdated TIMESTAMP NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , @@ -475,9 +496,9 @@ CREATE TABLE Templates ( ); --- }}} --- {{{ ObjectCustomFieldValues + + @@ -511,9 +532,9 @@ CREATE TABLE ObjectCustomFieldValues ( CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content); CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); --- }}} --- {{{ CustomFields + + @@ -527,8 +548,10 @@ 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 , - Repeated 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 , @@ -539,13 +562,14 @@ CREATE TABLE CustomFields ( LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated TIMESTAMP NULL , Disabled integer NOT NULL DEFAULT 0 , + Required integer NOT NULL DEFAULT 0 , PRIMARY KEY (id) ); --- }}} --- {{{ ObjectCustomFields + + CREATE SEQUENCE objectcustomfields_id_s; @@ -563,9 +587,9 @@ CREATE TABLE ObjectCustomFields ( ); --- }}} --- {{{ CustomFieldValues + + @@ -581,6 +605,7 @@ CREATE TABLE CustomFieldValues ( 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 , @@ -592,10 +617,10 @@ CREATE TABLE CustomFieldValues ( CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); --- }}} --- {{{ Attributes + + CREATE SEQUENCE attributes_id_seq; @@ -618,9 +643,9 @@ CREATE TABLE Attributes ( CREATE INDEX Attributes1 on Attributes(Name); CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId); --- }}} --- {{{ Sessions + + -- sessions is used by Apache::Session to keep sessions in the database. -- We should have a reaper script somewhere. @@ -633,5 +658,66 @@ CREATE TABLE sessions ( ); --- }}} + +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) +);