3 CREATE TABLE Attachments (
4 id INTEGER PRIMARY KEY ,
5 TransactionId INTEGER ,
7 MessageId varchar(160) NULL ,
8 Subject varchar(255) NULL ,
9 Filename varchar(255) NULL ,
10 ContentType varchar(80) NULL ,
11 ContentEncoding varchar(80) NULL ,
12 Content LONGTEXT NULL ,
13 Headers LONGTEXT NULL ,
14 Creator integer NULL ,
19 CREATE INDEX Attachments1 ON Attachments (Parent) ;
20 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
21 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
26 id INTEGER PRIMARY KEY ,
27 Name varchar(200) NOT NULL ,
28 Description varchar(255) NULL ,
29 CorrespondAddress varchar(120) NULL ,
30 CommentAddress varchar(120) NULL ,
31 InitialPriority integer NULL ,
32 FinalPriority integer NULL ,
33 DefaultDueIn integer NULL ,
34 Creator integer NULL ,
35 Created DATETIME NULL ,
36 LastUpdatedBy integer NULL ,
37 LastUpdated DATETIME NULL ,
38 Disabled int2 NOT NULL DEFAULT 0
41 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
48 id INTEGER PRIMARY KEY ,
49 Base varchar(240) NULL ,
50 Target varchar(240) NULL ,
51 Type varchar(20) NOT NULL ,
52 LocalTarget integer NULL ,
53 LocalBase integer NULL ,
54 LastUpdatedBy integer NULL ,
55 LastUpdated DATETIME NULL ,
56 Creator integer NULL ,
60 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
61 CREATE INDEX Links4 ON Links(Type,LocalBase);
67 CREATE TABLE Principals (
68 id INTEGER PRIMARY KEY,
69 PrincipalType VARCHAR(16) not null,
71 Disabled int2 NOT NULL DEFAULT 0
81 Name varchar(200) NULL ,
82 Description varchar(255) NULL ,
89 CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;
93 --- {{{ ScripConditions
95 CREATE TABLE ScripConditions (
96 id INTEGER PRIMARY KEY ,
97 Name varchar(200) NULL ,
98 Description varchar(255) NULL ,
99 ExecModule varchar(60) NULL ,
100 Argument varchar(255) NULL ,
101 ApplicableTransTypes varchar(60) NULL ,
103 Creator integer NULL ,
104 Created DATETIME NULL ,
105 LastUpdatedBy integer NULL ,
106 LastUpdated DATETIME NULL
113 CREATE TABLE Transactions (
114 id INTEGER PRIMARY KEY ,
115 EffectiveTicket integer NULL ,
116 Ticket integer NULL ,
117 TimeTaken integer NULL ,
118 Type varchar(20) NULL ,
119 Field varchar(40) NULL ,
120 OldValue varchar(255) NULL ,
121 NewValue varchar(255) NULL ,
122 Data varchar(255) NULL ,
124 Creator integer NULL ,
125 Created DATETIME NULL
128 CREATE INDEX Transactions1 ON Transactions (Ticket);
129 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
135 CREATE TABLE Scrips (
136 id INTEGER PRIMARY KEY ,
137 Description varchar(255),
138 ScripCondition integer NULL ,
139 ScripAction integer NULL ,
140 ConditionRules text NULL ,
141 ActionRules text NULL ,
142 CustomIsApplicableCode text NULL ,
143 CustomPrepareCode text NULL ,
144 CustomCommitCode text NULL ,
145 Stage varchar(32) NULL ,
147 Template integer NULL ,
148 Creator integer NULL ,
149 Created DATETIME NULL ,
150 LastUpdatedBy integer NULL ,
151 LastUpdated DATETIME NULL
159 id INTEGER PRIMARY KEY ,
160 PrincipalType varchar(25) NOT NULL,
163 RightName varchar(25) NOT NULL ,
164 ObjectType varchar(25) NOT NULL ,
165 ObjectId INTEGER default 0,
166 DelegatedBy integer NOT NULL default 0,
167 DelegatedFrom integer NOT NULL default 0
176 CREATE TABLE GroupMembers (
177 id INTEGER PRIMARY KEY ,
178 GroupId integer NULL,
179 MemberId integer NULL
185 --- {{{ CachedGroupMembers
187 create table CachedGroupMembers (
188 id integer primary key ,
192 ImmediateParentId int,
193 Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
194 # group or this group is disabled, this will be set to 1
195 # this allows us to not find members of disabled subgroups when listing off
196 # group members recursively.
197 # Also, this allows us to have the ACL system elide members of disabled groups
208 Name varchar(200) NOT NULL ,
209 Password varchar(40) NULL ,
211 Signature blob NULL ,
212 EmailAddress varchar(120) NULL ,
213 FreeformContactInfo blob NULL ,
214 Organization varchar(200) NULL ,
215 RealName varchar(120) NULL ,
216 NickName varchar(16) NULL ,
217 Lang varchar(16) NULL ,
218 EmailEncoding varchar(16) NULL ,
219 WebEncoding varchar(16) NULL ,
220 ExternalContactInfoId varchar(100) NULL ,
221 ContactInfoSystem varchar(30) NULL ,
222 ExternalAuthId varchar(100) NULL ,
223 AuthSystem varchar(30) NULL ,
224 Gecos varchar(16) NULL ,
225 HomePhone varchar(30) NULL ,
226 WorkPhone varchar(30) NULL ,
227 MobilePhone varchar(30) NULL ,
228 PagerPhone varchar(30) NULL ,
229 Address1 varchar(200) NULL ,
230 Address2 varchar(200) NULL ,
231 City varchar(100) NULL ,
232 State varchar(100) NULL ,
233 Zip varchar(16) NULL ,
234 Country varchar(50) NULL ,
235 Timezone char(50) NULL ,
238 Creator integer NULL ,
239 Created DATETIME NULL ,
240 LastUpdatedBy integer NULL ,
241 LastUpdated DATETIME NULL
246 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
247 CREATE INDEX Users2 ON Users (Name);
248 CREATE INDEX Users3 ON Users (id, EmailAddress);
249 CREATE INDEX Users4 ON Users (EmailAddress);
256 CREATE TABLE Tickets (
257 id INTEGER PRIMARY KEY ,
258 EffectiveId integer NULL ,
260 Type varchar(16) NULL ,
261 IssueStatement integer NULL ,
262 Resolution integer NULL ,
264 Subject varchar(200) NULL DEFAULT '[no subject]' ,
265 InitialPriority integer NULL ,
266 FinalPriority integer NULL ,
267 Priority integer NULL ,
268 TimeEstimated integer NULL ,
269 TimeWorked integer NULL ,
270 Status varchar(10) NULL ,
271 TimeLeft integer NULL ,
273 Starts DATETIME NULL ,
274 Started DATETIME NULL ,
276 Resolved DATETIME NULL ,
279 LastUpdatedBy integer NULL ,
280 LastUpdated DATETIME NULL ,
281 Creator integer NULL ,
282 Created DATETIME NULL ,
283 Disabled int2 NOT NULL DEFAULT 0
287 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
288 CREATE INDEX Tickets2 ON Tickets (Owner) ;
289 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
290 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
291 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
297 CREATE TABLE ScripActions (
298 id INTEGER PRIMARY KEY ,
299 Name varchar(200) NULL ,
300 Description varchar(255) NULL ,
301 ExecModule varchar(60) NULL ,
302 Argument varchar(255) NULL ,
303 Creator integer NULL ,
304 Created DATETIME NULL ,
305 LastUpdatedBy integer NULL ,
306 LastUpdated DATETIME NULL
314 CREATE TABLE Templates (
315 id INTEGER PRIMARY KEY ,
316 Queue integer NOT NULL DEFAULT 0 ,
317 Name varchar(200) NOT NULL ,
318 Description varchar(255) NULL ,
319 Type varchar(16) NULL ,
320 Language varchar(16) NULL ,
321 TranslationOf integer NULL ,
323 LastUpdated DATETIME NULL ,
324 LastUpdatedBy integer NULL ,
325 Creator integer NULL ,
326 Created DATETIME NULL
332 --- {{{ TicketCustomFieldValues
334 CREATE TABLE TicketCustomFieldValues (
335 id INTEGER PRIMARY KEY ,
336 Ticket int NOT NULL ,
337 CustomField int NOT NULL ,
338 Content varchar(255) NULL ,
340 Creator integer NULL ,
341 Created DATETIME NULL ,
342 LastUpdatedBy integer NULL ,
343 LastUpdated DATETIME NULL
347 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
348 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
354 CREATE TABLE CustomFields (
355 id INTEGER PRIMARY KEY ,
356 Name varchar(200) NULL ,
357 Type varchar(200) NULL ,
359 Description varchar(255) NULL ,
360 SortOrder integer NULL ,
362 Creator integer NULL ,
363 Created DATETIME NULL ,
364 LastUpdatedBy integer NULL ,
365 LastUpdated DATETIME NULL ,
366 Disabled int2 NOT NULL DEFAULT 0
372 --- {{{ CustomFieldValues
374 CREATE TABLE CustomFieldValues (
375 id INTEGER PRIMARY KEY ,
376 CustomField int NOT NULL ,
377 Name varchar(200) NULL ,
378 Description varchar(255) NULL ,
379 SortOrder integer NULL ,
381 Creator integer NULL ,
382 Created DATETIME NULL ,
383 LastUpdatedBy integer NULL ,
384 LastUpdated DATETIME NULL
388 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
393 CREATE TABLE Attributes (
394 id INTEGER PRIMARY KEY ,
395 Name varchar(255) NOT NULL ,
396 Description varchar(255) NULL ,
397 Content LONGTEXT NULL ,
398 ContentType varchar(16),
399 ObjectType varchar(25) NOT NULL ,
400 ObjectId INTEGER default 0,
401 Creator integer NULL ,
402 Created DATETIME NULL ,
403 LastUpdatedBy integer NULL ,
404 LastUpdated DATETIME NULL
407 CREATE INDEX Attributes1 on Attributes(Name);
408 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);