diff options
Diffstat (limited to 'rt/etc/schema.Pg')
| -rwxr-xr-x | rt/etc/schema.Pg | 267 |
1 files changed, 267 insertions, 0 deletions
diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg new file mode 100755 index 000000000..21d981b4a --- /dev/null +++ b/rt/etc/schema.Pg @@ -0,0 +1,267 @@ +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); +CREATE TABLE Attachments ( + id serial NOT NULL , + 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 , + PRIMARY KEY (id) +); +CREATE INDEX Attachments1 ON Attachments (Parent); +CREATE INDEX Attachments2 ON Attachments (TransactionId); +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); +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 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Queues1 ON Queues (Name); +CREATE TABLE Links ( + id serial NOT NULL , + Base varchar(240) , + Target varchar(240) , + Type varchar(20) NOT NULL , + LocalTarget integer , + LocalBase integer , + LastUpdatedBy integer , + LastUpdated timestamp , + Creator integer , + Created timestamp , + 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 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 , + PRIMARY KEY (id) +); +CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); +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 , + PRIMARY KEY (id) +); +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 , + PRIMARY KEY (id) +); +CREATE INDEX Transactions1 ON Transactions (Ticket); +CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +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 , + PRIMARY KEY (id) +); +CREATE TABLE ACL ( + id serial NOT NULL , + PrincipalId integer , + PrincipalType varchar(25) , + RightName varchar(25) , + RightScope varchar(25) , + RightAppliesTo integer , + 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 , + 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 , + 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 Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords2 ON Keywords (Name); +CREATE INDEX Keywords3 ON Keywords (Parent); +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 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Users1 ON Users (Name); +CREATE INDEX Users3 ON Users (id, EmailAddress); +CREATE INDEX Users4 ON Users (EmailAddress); +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 , + 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 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 , + PRIMARY KEY (id) +); +CREATE TABLE Templates ( + id serial NOT NULL , + 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 , + PRIMARY KEY (id) +); |