path: root/rt/etc/schema.Pg

------------------------------------------------------------------
-- My2Pg 1.23 translated dump
--
------------------------------------------------------------------

BEGIN;




--
-- 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) ;

-- }}}

-- {{{ 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 varchar(64),
  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(100) 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)

);

-- }}}

-- {{{ 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)

);

-- }}}

-- {{{ 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;