--- {{{ 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 ,
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
) ;
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,
DelegatedBy integer NOT NULL default 0,
DelegatedFrom integer NOT NULL default 0
) ;
--- }}}
--- {{{ GroupMembers
CREATE TABLE GroupMembers (
id INTEGER PRIMARY KEY ,
GroupId integer NULL,
MemberId integer 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(40) 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(10) 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
) ;
--- }}}
# {{{ ObjectCustomFieldValues
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);
# }}}
# {{{ CustomFields
CREATE TABLE CustomFields (
id INTEGER NOT NULL ,
Name varchar(200) NULL ,
Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
Pattern varchar(255) NULL , # New -- Must validate against this
Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
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)
) ;
# }}}
# {{{ ObjectCustomFields
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)
) ;
# }}}
# {{{ CustomFieldValues
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 ,
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);
--- }}}
