diff options
Diffstat (limited to 'rt/etc/schema.Pg')
| -rwxr-xr-x | rt/etc/schema.Pg | 578 |
1 files changed, 578 insertions, 0 deletions
diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg new file mode 100755 index 0000000..085c615 --- /dev/null +++ b/rt/etc/schema.Pg @@ -0,0 +1,578 @@ +------------------------------------------------------------------ +-- My2Pg 1.23 translated dump +-- +------------------------------------------------------------------ + + + +-- +-- Sequences for table ATTACHMENTS +-- + +CREATE SEQUENCE attachments_id_seq; + +-- {{{ Attachments + +CREATE TABLE Attachments ( + id INTEGER DEFAULT nextval('attachments_id_seq'), + 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 , + 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 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 , + 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 TABLE Links ( + id INTEGER DEFAULT nextval('links_id_seq'), + Base varchar(240) NULL , + Target varchar(240) NULL , + 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 , + PRIMARY KEY (id) + +); +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; +CREATE INDEX Links4 ON Links(Type,LocalBase); + +-- }}} + +-- {{{ 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 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, + 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 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 , + 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(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); + +-- }}} + +-- {{{ 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 , + 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, + PRIMARY KEY (id) + +); + +CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); + + +-- }}} + +-- {{{ GroupMembers + + + +-- +-- 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, + 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 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 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 , + PRIMARY KEY (id) + +); + + +CREATE UNIQUE INDEX Users1 ON Users (Name) ; +CREATE INDEX Users2 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 , + 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 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 , + 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) + +); + +CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); +CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); + +-- }}} + +-- {{{ 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 , + 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 , + 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) + +); + +CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); + +-- }}} + +-- {{{ 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) + +); + +-- }}} + |