diff options
Diffstat (limited to 'rt/etc/schema.Pg')
| -rwxr-xr-x | rt/etc/schema.Pg | 747 |
1 files changed, 218 insertions, 529 deletions
diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg index ba0d6fc6c..21d981b4a 100755 --- a/rt/etc/schema.Pg +++ b/rt/etc/schema.Pg @@ -1,578 +1,267 @@ ------------------------------------------------------------------- --- My2Pg 1.23 translated dump --- ------------------------------------------------------------------- - -BEGIN; - - - - --- --- Sequences for table ATTACHMENTS --- - -CREATE SEQUENCE attachments_id_seq; - --- {{{ Attachments - +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 INTEGER DEFAULT nextval('attachments_id_seq'), + id serial NOT NULL , TransactionId integer NOT NULL , - 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 , + 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) ; --- }}} - --- {{{ Queues - - --- --- Sequences for table QUEUES --- - -CREATE SEQUENCE queues_id_seq; - +CREATE INDEX Attachments1 ON Attachments (Parent); +CREATE INDEX Attachments2 ON Attachments (TransactionId); +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); CREATE TABLE Queues ( - id INTEGER DEFAULT nextval('queues_id_seq'), - Name varchar(200) NOT NULL , - Description varchar(255) NULL , - CorrespondAddress varchar(120) NULL , - CommentAddress 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 , + 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) ; - --- }}} - --- {{{ Links - - - --- --- Sequences for table LINKS --- - -CREATE SEQUENCE links_id_seq; - +CREATE UNIQUE INDEX Queues1 ON Queues (Name); CREATE TABLE Links ( - id INTEGER DEFAULT nextval('links_id_seq'), - Base varchar(240) NULL , - Target varchar(240) NULL , + id serial NOT NULL , + Base varchar(240) , + Target varchar(240) , Type varchar(20) NOT NULL , - 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 , + LocalTarget integer , + LocalBase integer , + LastUpdatedBy integer , + LastUpdated timestamp , + Creator integer , + Created timestamp , PRIMARY KEY (id) - ); -CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; - --- }}} - --- {{{ Principals - - - --- --- 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 int2 NOT NULL DEFAULT 0 , - PRIMARY KEY (id) - -); - -CREATE INDEX Principals2 ON Principals (ObjectId); - - --- }}} - --- {{{ Groups - - - --- --- Sequences for table GROUPS --- - -CREATE SEQUENCE groups_id_seq; - +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); 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 varchar(64), + 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 UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name); -CREATE INDEX Groups2 On Groups (Type, Instance, Domain); - - --- }}} - --- {{{ ScripConditions - - - --- --- Sequences for table SCRIPCONDITIONS --- - -CREATE SEQUENCE scripconditions_id_seq; - +CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); CREATE TABLE ScripConditions ( - 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 , + 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) - ); - --- }}} - --- {{{ Transactions - - --- --- Sequences for table TRANSACTIONS --- - -CREATE SEQUENCE transactions_id_seq; - CREATE TABLE Transactions ( - id INTEGER DEFAULT nextval('transactions_id_seq'), - EffectiveTicket integer NOT NULL DEFAULT 0 , - Ticket 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 , - Data varchar(100) NULL , - - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , + 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); - --- }}} - --- {{{ Scrips - - - --- --- Sequences for table SCRIPS --- - -CREATE SEQUENCE scrips_id_seq; - CREATE TABLE Scrips ( - id INTEGER DEFAULT nextval('scrips_id_seq'), - 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 , - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated TIMESTAMP NULL , + 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) - ); - --- }}} - --- {{{ ACL - - --- --- 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, - DelegatedBy integer NOT NULL DEFAULT 0, - DelegatedFrom integer NOT NULL DEFAULT 0, + 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(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); - - --- }}} - --- {{{ GroupMembers - - - --- --- Sequences for table GROUPMEMBERS --- - -CREATE SEQUENCE groupmembers_id_seq; - +CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); +CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); CREATE TABLE GroupMembers ( - id INTEGER DEFAULT nextval('groupmembers_id_seq'), - GroupId integer NOT NULL DEFAULT 0, - MemberId integer NOT NULL DEFAULT 0, + 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) - ); - --- }}} - --- {{{ GroupMembersCache - - - --- --- 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 int2 NOT NULL DEFAULT 0 , - 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 INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId); -CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId); -CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); - --- }}} - --- {{{ Users - - - --- --- Sequences for table USERS --- - -CREATE SEQUENCE users_id_seq; - +CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords2 ON Keywords (Name); +CREATE INDEX Keywords3 ON Keywords (Parent); CREATE TABLE Users ( - id INTEGER DEFAULT nextval('users_id_seq'), - Name varchar(200) NOT NULL , - Password varchar(40) 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, - - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated TIMESTAMP NULL , + 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 Users2 ON Users (Name); +CREATE UNIQUE INDEX Users1 ON Users (Name); CREATE INDEX Users3 ON Users (id, EmailAddress); CREATE INDEX Users4 ON Users (EmailAddress); - - --- }}} - --- {{{ Tickets - - - --- --- Sequences for table TICKETS --- - -CREATE SEQUENCE tickets_id_seq; - CREATE TABLE Tickets ( - id INTEGER DEFAULT nextval('tickets_id_seq'), - EffectiveId integer NOT NULL DEFAULT 0 , - 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(10) NULL , - TimeLeft integer NOT NULL DEFAULT 0 , - Told TIMESTAMP NULL , - Starts TIMESTAMP NULL , - Started TIMESTAMP NULL , - Due TIMESTAMP NULL , - Resolved TIMESTAMP NULL , - - - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated TIMESTAMP NULL , - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , + 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) ; - --- }}} - --- {{{ ScripActions - - - --- --- Sequences for table SCRIPACTIONS --- - -CREATE SEQUENCE scripactions_id_seq; - +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 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 , + 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) - ); - --- }}} - --- {{{ Templates - - - --- --- Sequences for table TEMPLATES --- - -CREATE SEQUENCE templates_id_seq; - CREATE TABLE Templates ( - id INTEGER DEFAULT nextval('templates_id_seq'), - Queue integer NOT NULL DEFAULT 0 , - 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 , - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , - PRIMARY KEY (id) - -); - --- }}} - --- {{{ TicketCustomFieldValues - - - --- --- Sequences for table TICKETCUSTOMFIELDVALUES --- - -CREATE SEQUENCE ticketcustomfieldvalues_id_s; - -CREATE TABLE TicketCustomFieldValues ( - id INTEGER DEFAULT nextval('ticketcustomfieldvalues_id_s'), - Ticket int NOT NULL , - CustomField int NOT NULL , - Content varchar(255) NULL , - - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated TIMESTAMP NULL , - PRIMARY KEY (id) - -); - --- }}} - --- {{{ CustomFields - - - --- --- 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 , + id serial NOT NULL , Queue integer NOT NULL DEFAULT 0 , - 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 int2 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) - -); - --- }}} - --- {{{ CustomFieldValues - - - --- --- 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 , - - Creator integer NOT NULL DEFAULT 0 , - Created TIMESTAMP NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated TIMESTAMP NULL , - PRIMARY KEY (id) - -); - --- }}} - --- {{{ Sessions - --- 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) - ); - --- }}} - - - -COMMIT; |