-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);
+------------------------------------------------------------------
+-- My2Pg 1.23 translated dump
+--
+------------------------------------------------------------------
+
+
+
+--
+-- Sequences for table ATTACHMENTS
+--
+
+CREATE SEQUENCE attachments_id_seq;
+
+
+
CREATE TABLE Attachments (
- id serial NOT NULL ,
+ id INTEGER DEFAULT nextval('attachments_id_seq'),
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 ,
+ 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 Attachments1 ON Attachments (Parent) ;
+CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
+CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
+
+
+
+
+
+--
+-- Sequences for table QUEUES
+--
+
+CREATE SEQUENCE queues_id_seq;
+
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 ,
+ 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 ,
+ 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 integer NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
+
);
-CREATE UNIQUE INDEX Queues1 ON Queues (Name);
+CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ;
+
+
+
+
+
+
+
+--
+-- Sequences for table LINKS
+--
+
+CREATE SEQUENCE links_id_seq;
+
CREATE TABLE Links (
- id serial NOT NULL ,
- Base varchar(240) ,
- Target varchar(240) ,
+ id INTEGER DEFAULT nextval('links_id_seq'),
+ Base varchar(240) NULL ,
+ Target varchar(240) NULL ,
Type varchar(20) NOT NULL ,
- LocalTarget integer ,
- LocalBase integer ,
- LastUpdatedBy integer ,
- LastUpdated timestamp ,
- Creator integer ,
- Created timestamp ,
+ 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 TABLE Groups (
- id serial NOT NULL ,
- Name varchar(16) ,
- Description varchar(64) ,
- Pseudo integer NOT NULL DEFAULT 0 ,
- 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,
+ ObjectId integer,
+ Disabled 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 ,
+
+CREATE INDEX Principals2 ON Principals (ObjectId);
+
+
+
+
+
+
+
+
+--
+-- 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,
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created TIMESTAMP NULL ,
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated TIMESTAMP NULL ,
PRIMARY KEY (id)
+
);
-CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
+CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance);
+CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance);
+CREATE INDEX Groups3 On Groups (Instance);
+
+
+
+
+
+
+
+
+--
+-- Sequences for table SCRIPCONDITIONS
+--
+
+CREATE SEQUENCE scripconditions_id_seq;
+
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 ,
+ 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 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 ,
+ 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 (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
+
+
+
+
+
+
+
+--
+-- Sequences for table SCRIPS
+--
+
+CREATE SEQUENCE scrips_id_seq;
+
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 ,
+ 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 TABLE ACL (
- id serial NOT NULL ,
- PrincipalId integer ,
- PrincipalType varchar(25) ,
- RightName varchar(25) ,
- RightScope varchar(25) ,
- RightAppliesTo integer ,
+
+
+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 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 ,
+
+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 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 ,
+
+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 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 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 UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
-CREATE INDEX Keywords2 ON Keywords (Name);
-CREATE INDEX Keywords3 ON Keywords (Parent);
+
+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 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 ,
+ 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 ,
+ 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,
+ 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 (Name);
-CREATE INDEX Users3 ON Users (id, EmailAddress);
+
+
+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 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 ,
+ 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 ,
+ 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(64) NULL ,
+ TimeLeft integer NOT NULL DEFAULT 0 ,
+ Told TIMESTAMP NULL ,
+ Starts TIMESTAMP NULL ,
+ Started TIMESTAMP NULL ,
+ Due TIMESTAMP NULL ,
+ Resolved TIMESTAMP NULL ,
+
+ WillResolve TIMESTAMP NULL ,
+
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated TIMESTAMP NULL ,
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created TIMESTAMP NULL ,
+ Disabled integer 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 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 serial NOT NULL ,
- Name varchar(255) ,
- Description varchar(255) ,
- ExecModule varchar(60) ,
- Argument varchar(255) ,
- Creator integer ,
- Created timestamp ,
- LastUpdatedBy integer ,
- LastUpdated timestamp ,
+ 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 serial NOT NULL ,
+ id INTEGER DEFAULT nextval('templates_id_seq'),
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 ,
+ 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 ,
+ 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 integer NOT NULL DEFAULT 0 ,
+ Required 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),
+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)
);
+