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



--
-- Sequences for table ATTACHMENTS
--

CREATE SEQUENCE attachments_id_seq;



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) ;
CREATE INDEX Attachments4 ON Attachments (Filename) ;





--
-- 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  ,
  Lifecycle varchar(32) NULL,
  SubjectTag varchar(120) 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  ,
  SLADisabled integer NOT NULL DEFAULT 1 ,
  Disabled integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)

);
CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ;







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







--
-- 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,
        Disabled integer NOT NULL DEFAULT 0 ,
        PRIMARY KEY (id)

);








--
-- 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),
  Instance integer,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);
CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Name), Instance);
CREATE INDEX Groups2 On Groups (Instance);








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

);






--
-- Sequences for table TRANSACTIONS
--

CREATE SEQUENCE transactions_id_seq;

CREATE TABLE Transactions (
  id INTEGER DEFAULT nextval('transactions_id_seq'),
  ObjectType varchar(255) NOT NULL  ,
  ObjectId 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  ,
  ReferenceType varchar(255) NULL,
  OldReference integer NULL  ,
  NewReference integer NULL  ,
  Data varchar(255) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);







--
-- 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  ,
  CustomIsApplicableCode text NULL  ,
  CustomPrepareCode text NULL  ,
  CustomCommitCode text NULL  ,
  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  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);

CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);



--
-- 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,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);

CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);








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







--
-- 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 integer NOT NULL DEFAULT 0 , 
        PRIMARY KEY (id)

);

CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
CREATE INDEX CachedGroupMembers3  on CachedGroupMembers (MemberId,ImmediateParentId);







--
-- 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(256) NULL  ,
  AuthToken varchar(16) 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  ,
  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  ,
  SMIMECertificate 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 (LOWER(Name)) ;
CREATE INDEX Users4 ON Users (EmailAddress);








--
-- 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  ,
  IsMerged smallint NULL DEFAULT NULL ,
  Queue integer NOT NULL DEFAULT 0  ,
  Type varchar(16) NULL  ,
  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(64) NULL  ,
  SLA varchar(64) 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  ,
  PRIMARY KEY (id)

);

CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;


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

);







--
-- 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  ,
  Content text NULL  ,
  LastUpdated TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);







--
-- Sequences for table TICKETCUSTOMFIELDVALUES
--

CREATE SEQUENCE objectcustomfieldvalues_id_s;

CREATE TABLE ObjectCustomFieldValues (
  id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
  CustomField int NOT NULL  ,
  ObjectType varchar(255) NULL  ,
  ObjectId int NOT NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Content varchar(255) NULL  ,
  LargeContent text NULL,
  ContentType varchar(80) NULL,
  ContentEncoding varchar(80) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)

);

CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content); 
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); 







--
-- 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  ,
  RenderType varchar(64) NULL  ,
  MaxValues integer NOT NULL DEFAULT 0  ,
  ValuesClass varchar(64) NULL  ,
  BasedOn integer NULL, 
  Pattern varchar(65536) NULL  ,
  LookupType varchar(255) NOT NULL  ,
  EntryHint varchar(255) NULL,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  UniqueValues integer NOT NULL DEFAULT 0 ,
  CanonicalizeClass varchar(64) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)

);





CREATE SEQUENCE objectcustomfields_id_s;

CREATE TABLE ObjectCustomFields (
  id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
  CustomField integer NOT NULL,
  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  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);







--
-- 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  ,
  Category 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 CustomFieldValues1 ON CustomFieldValues (CustomField);






CREATE SEQUENCE attributes_id_seq;

CREATE TABLE Attributes (
  id INTEGER DEFAULT nextval('attributes_id_seq'),
  Name varchar(255) NOT NULL  ,
  Description varchar(255) NULL  ,
  Content text,
  ContentType varchar(16),
  ObjectType varchar(64),
  ObjectId integer, 
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);

CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);





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

);


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),
Disabled smallint 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 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)
);

CREATE SEQUENCE assets_id_seq;
CREATE TABLE Assets (
    id                integer                  DEFAULT nextval('assets_id_seq'),
    Name              varchar(255)    NOT NULL DEFAULT '',
    Catalog           integer         NOT NULL DEFAULT 0,
    Status            varchar(64)     NOT NULL DEFAULT '',
    Description       varchar(255)    NOT NULL DEFAULT '',
    Creator           integer         NOT NULL DEFAULT 0,
    Created           timestamp                DEFAULT NULL,
    LastUpdatedBy     integer         NOT NULL DEFAULT 0,
    LastUpdated       timestamp                DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX AssetsName ON Assets (LOWER(Name));
CREATE INDEX AssetsStatus ON Assets (Status);
CREATE INDEX AssetsCatalog ON Assets (Catalog);

CREATE SEQUENCE catalogs_id_seq;
CREATE TABLE Catalogs (
    id                integer                  DEFAULT nextval('catalogs_id_seq'),
    Name              varchar(255)    NOT NULL DEFAULT '',
    Lifecycle         varchar(32)     NOT NULL DEFAULT 'assets',
    Description       varchar(255)    NOT NULL DEFAULT '',
    Disabled          integer         NOT NULL DEFAULT 0,
    Creator           integer         NOT NULL DEFAULT 0,
    Created           timestamp                DEFAULT NULL,
    LastUpdatedBy     integer         NOT NULL DEFAULT 0,
    LastUpdated       timestamp                DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX CatalogsName ON Catalogs (LOWER(Name));
CREATE INDEX CatalogsDisabled ON Catalogs (Disabled);

CREATE SEQUENCE customroles_id_seq;

CREATE TABLE CustomRoles (
  id INTEGER DEFAULT nextval('customroles_id_seq'),
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  MaxValues integer NOT NULL DEFAULT 0  ,
  EntryHint varchar(255) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)

);

CREATE SEQUENCE objectcustomroles_id_seq;

CREATE TABLE ObjectCustomRoles (
  id INTEGER DEFAULT nextval('objectscrips_id_seq'),
  CustomRole integer NOT NULL,
  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  ,
  LastUpdated TIMESTAMP NULL  ,
  PRIMARY KEY (id)

);

CREATE UNIQUE INDEX ObjectCustomRoles1 ON ObjectCustomRoles (ObjectId, CustomRole);