--- {{{ Attachments CREATE TABLE Attachments ( id INTEGER PRIMARY KEY , TransactionId INTEGER , Parent integer NULL , MessageId varchar(160) NULL , Subject varchar(255) NULL , Filename varchar(255) NULL , ContentType varchar(80) NULL , ContentEncoding varchar(80) NULL , Content LONGTEXT NULL , Headers LONGTEXT NULL , Creator integer NULL , Created DATETIME NULL ) ; CREATE INDEX Attachments1 ON Attachments (Parent) ; CREATE INDEX Attachments2 ON Attachments (TransactionId) ; CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; --- }}} --- {{{ Queues CREATE TABLE Queues ( id INTEGER PRIMARY KEY , 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 NULL , FinalPriority integer NULL , DefaultDueIn integer NULL , Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 ) ; CREATE UNIQUE INDEX Queues1 ON Queues (Name) ; --- }}} --- {{{ Links CREATE TABLE Links ( id INTEGER PRIMARY KEY , Base varchar(240) NULL , Target varchar(240) NULL , Type varchar(20) NOT NULL , LocalTarget integer NULL , LocalBase integer NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , Creator integer NULL , Created DATETIME NULL ) ; CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; CREATE INDEX Links4 ON Links(Type,LocalBase); --- }}} --- {{{ Principals CREATE TABLE Principals ( id INTEGER PRIMARY KEY, PrincipalType VARCHAR(16) not null, ObjectId integer, Disabled int2 NOT NULL DEFAULT 0 ) ; --- }}} --- {{{ Groups CREATE TABLE Groups ( id INTEGER , Name varchar(200) NULL , Description varchar(255) NULL , Domain varchar(64), Type varchar(64), Instance integer, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ; --- }}} --- {{{ ScripConditions CREATE TABLE ScripConditions ( id INTEGER PRIMARY KEY , Name varchar(200) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , ApplicableTransTypes varchar(60) NULL , Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL ) ; --- }}} --- {{{ Transactions CREATE TABLE Transactions ( id INTEGER PRIMARY KEY , ObjectType varchar(255) NULL , ObjectId integer NULL , TimeTaken integer NULL , 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 NULL , Created DATETIME NULL ) ; CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); --- }}} --- {{{ Scrips CREATE TABLE Scrips ( id INTEGER PRIMARY KEY , Description varchar(255), ScripCondition integer NULL , ScripAction integer NULL , ConditionRules text NULL , ActionRules text NULL , CustomIsApplicableCode text NULL , CustomPrepareCode text NULL , CustomCommitCode text NULL , Stage varchar(32) NULL , Queue integer NULL , Template integer NULL , Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL ) ; --- }}} --- {{{ ACL CREATE TABLE ACL ( id INTEGER PRIMARY KEY , PrincipalType varchar(25) NOT NULL, PrincipalId INTEGER, RightName varchar(25) NOT NULL , ObjectType varchar(25) NOT NULL , ObjectId INTEGER default 0, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; --- }}} --- {{{ GroupMembers CREATE TABLE GroupMembers ( id INTEGER PRIMARY KEY , GroupId integer NULL, MemberId integer NULL, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; --- }}} --- {{{ CachedGroupMembers create table CachedGroupMembers ( id integer primary key , GroupId int, MemberId int, Via int, ImmediateParentId int, Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled # group or this group is disabled, this will be set to 1 # this allows us to not find members of disabled subgroups when listing off # group members recursively. # Also, this allows us to have the ACL system elide members of disabled groups ) ; --- }}} --- {{{ Users CREATE TABLE Users ( id INTEGER , Name varchar(200) NOT NULL , Password varchar(256) NULL , AuthToken varchar(16) NULL , Comments blob NULL , Signature blob NULL , EmailAddress varchar(120) NULL , FreeformContactInfo blob 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 char(50) NULL , PGPKey text NULL, Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL ) ; 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 CREATE TABLE Tickets ( id INTEGER PRIMARY KEY , EffectiveId integer NULL , Queue integer NULL , Type varchar(16) NULL , IssueStatement integer NULL , Resolution integer NULL , Owner integer NULL , Subject varchar(200) NULL DEFAULT '[no subject]' , InitialPriority integer NULL , FinalPriority integer NULL , Priority integer NULL , TimeEstimated integer NULL , TimeWorked integer NULL , Status varchar(64) NULL , TimeLeft integer NULL , Told DATETIME NULL , Starts DATETIME NULL , Started DATETIME NULL , Due DATETIME NULL , Resolved DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , Creator integer NULL , Created DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 ) ; 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 CREATE TABLE ScripActions ( id INTEGER PRIMARY KEY , Name varchar(200) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL ) ; --- }}} --- {{{ Templates CREATE TABLE Templates ( id INTEGER PRIMARY KEY , 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 NULL , Content blob NULL , LastUpdated DATETIME NULL , LastUpdatedBy integer NULL , Creator integer NULL , Created DATETIME NULL ) ; --- }}} CREATE TABLE ObjectCustomFieldValues ( id INTEGER NOT NULL , CustomField int NOT NULL , ObjectType varchar(255) NOT NULL, # Final target of the Object ObjectId int NOT NULL , # New -- Replaces Ticket SortOrder integer NOT NULL DEFAULT 0 , Content varchar(255) NULL , LargeContent LONGTEXT NULL, # New -- to hold 255+ strings ContentType varchar(80) NULL, # New -- only text/* gets searched ContentEncoding varchar(80) NULL , # New -- for binary Content Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) ) ; CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); CREATE TABLE CustomFields ( id INTEGER NOT NULL , Name varchar(200) NULL , Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out RenderType varchar(64) NULL , MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0) Pattern varchar(65536) NULL , # New -- Must validate against this Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry BasedOn INTEGER NULL, ValuesClass varchar(64) NULL , Description varchar(255) NULL , SortOrder integer NOT NULL DEFAULT 0 , LookupType varchar(255) NOT NULL, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) ) ; CREATE TABLE ObjectCustomFields ( id INTEGER NOT NULL , CustomField int NOT NULL , ObjectId integer NOT NULL, SortOrder integer NOT NULL DEFAULT 0 , Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , PRIMARY KEY (id) ) ; CREATE TABLE CustomFieldValues ( id INTEGER NOT NULL , 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 DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , PRIMARY KEY (id) ) ; CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); --- {{{ Attributes CREATE TABLE Attributes ( id INTEGER PRIMARY KEY , Name varchar(255) NOT NULL , Description varchar(255) NULL , Content LONGTEXT NULL , ContentType varchar(16), ObjectType varchar(25) NOT NULL , ObjectId INTEGER default 0, Creator integer NULL , Created DATETIME NULL , LastUpdatedBy integer NULL , LastUpdated DATETIME NULL ) ; CREATE INDEX Attributes1 on Attributes(Name); CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId); --- }}} CREATE TABLE Classes ( id INTEGER PRIMARY KEY, 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 ); CREATE TABLE Articles ( id INTEGER PRIMARY KEY, 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 ); CREATE TABLE Topics ( id INTEGER PRIMARY KEY, 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 ); CREATE TABLE ObjectTopics ( id INTEGER PRIMARY KEY, Topic integer NOT NULL, ObjectType varchar(64) NOT NULL DEFAULT '', ObjectId integer NOT NULL ); CREATE TABLE ObjectClasses ( id INTEGER PRIMARY KEY, Class integer NOT NULL, ObjectType varchar(64) 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 );