3 CREATE TABLE Attachments (
4 id INTEGER PRIMARY KEY ,
5 TransactionId INTEGER ,
6 Parent integer NULL DEFAULT 0 ,
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 DEFAULT 0 ,
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 Lifecycle varchar(32) NULL ,
32 SubjectTag varchar(120) NULL ,
33 InitialPriority integer NULL DEFAULT 0 ,
34 FinalPriority integer NULL DEFAULT 0 ,
35 DefaultDueIn integer NULL DEFAULT 0 ,
36 Creator integer NULL DEFAULT 0 ,
37 Created DATETIME NULL ,
38 LastUpdatedBy integer NULL DEFAULT 0 ,
39 LastUpdated DATETIME NULL ,
40 Disabled int2 NOT NULL DEFAULT 0
43 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
50 id INTEGER PRIMARY KEY ,
51 Base varchar(240) NULL ,
52 Target varchar(240) NULL ,
53 Type varchar(20) NOT NULL ,
54 LocalTarget integer NULL DEFAULT 0 ,
55 LocalBase integer NULL DEFAULT 0 ,
56 LastUpdatedBy integer NULL DEFAULT 0 ,
57 LastUpdated DATETIME NULL ,
58 Creator integer NULL DEFAULT 0 ,
62 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
63 CREATE INDEX Links4 ON Links(Type,LocalBase);
69 CREATE TABLE Principals (
70 id INTEGER PRIMARY KEY,
71 PrincipalType VARCHAR(16) not null,
73 Disabled int2 NOT NULL DEFAULT 0
83 Name varchar(200) NULL ,
84 Description varchar(255) NULL ,
88 Creator integer NOT NULL DEFAULT 0 ,
89 Created DATETIME NULL ,
90 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
91 LastUpdated DATETIME NULL
95 CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;
99 --- {{{ ScripConditions
101 CREATE TABLE ScripConditions (
102 id INTEGER PRIMARY KEY ,
103 Name varchar(200) NULL ,
104 Description varchar(255) NULL ,
105 ExecModule varchar(60) NULL ,
106 Argument varchar(255) NULL ,
107 ApplicableTransTypes varchar(60) NULL ,
109 Creator integer NULL DEFAULT 0 ,
110 Created DATETIME NULL ,
111 LastUpdatedBy integer NULL DEFAULT 0 ,
112 LastUpdated DATETIME NULL
119 CREATE TABLE Transactions (
120 id INTEGER PRIMARY KEY ,
121 ObjectType varchar(255) NULL ,
122 ObjectId integer NULL DEFAULT 0 ,
123 TimeTaken integer NULL DEFAULT 0 ,
124 Type varchar(20) NULL ,
125 Field varchar(40) NULL ,
126 OldValue varchar(255) NULL ,
127 NewValue varchar(255) NULL ,
128 ReferenceType varchar(255) NULL ,
129 OldReference integer NULL ,
130 NewReference integer NULL ,
131 Data varchar(255) NULL ,
133 Creator integer NULL DEFAULT 0 ,
134 Created DATETIME NULL
137 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
143 CREATE TABLE Scrips (
144 id INTEGER PRIMARY KEY ,
145 Description varchar(255),
146 ScripCondition integer NULL DEFAULT 0 ,
147 ScripAction integer NULL DEFAULT 0 ,
148 ConditionRules text NULL ,
149 ActionRules text NULL ,
150 CustomIsApplicableCode text NULL ,
151 CustomPrepareCode text NULL ,
152 CustomCommitCode text NULL ,
153 Stage varchar(32) NULL ,
154 Queue integer NULL DEFAULT 0 ,
155 Template integer NULL DEFAULT 0 ,
156 Creator integer NULL DEFAULT 0 ,
157 Created DATETIME NULL ,
158 LastUpdatedBy integer NULL DEFAULT 0 ,
159 LastUpdated DATETIME NULL
167 id INTEGER PRIMARY KEY ,
168 PrincipalType varchar(25) NOT NULL,
170 PrincipalId INTEGER DEFAULT 0,
171 RightName varchar(25) NOT NULL ,
172 ObjectType varchar(25) NOT NULL ,
173 ObjectId INTEGER default 0,
174 Creator integer NOT NULL DEFAULT 0 ,
175 Created DATETIME NULL ,
176 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
177 LastUpdated DATETIME NULL
186 CREATE TABLE GroupMembers (
187 id INTEGER PRIMARY KEY ,
188 GroupId integer NULL DEFAULT 0,
189 MemberId integer NULL DEFAULT 0,
190 Creator integer NOT NULL DEFAULT 0 ,
191 Created DATETIME NULL ,
192 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
193 LastUpdated DATETIME NULL
199 --- {{{ CachedGroupMembers
201 create table CachedGroupMembers (
202 id integer primary key ,
206 ImmediateParentId int,
207 Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
208 # group or this group is disabled, this will be set to 1
209 # this allows us to not find members of disabled subgroups when listing off
210 # group members recursively.
211 # Also, this allows us to have the ACL system elide members of disabled groups
222 Name varchar(200) NOT NULL ,
223 Password varchar(256) NULL ,
224 AuthToken varchar(16) NULL ,
226 Signature blob NULL ,
227 EmailAddress varchar(120) NULL ,
228 FreeformContactInfo blob NULL ,
229 Organization varchar(200) NULL ,
230 RealName varchar(120) NULL ,
231 NickName varchar(16) NULL ,
232 Lang varchar(16) NULL ,
233 EmailEncoding varchar(16) NULL ,
234 WebEncoding varchar(16) NULL ,
235 ExternalContactInfoId varchar(100) NULL ,
236 ContactInfoSystem varchar(30) NULL ,
237 ExternalAuthId varchar(100) NULL ,
238 AuthSystem varchar(30) NULL ,
239 Gecos varchar(16) NULL ,
240 HomePhone varchar(30) NULL ,
241 WorkPhone varchar(30) NULL ,
242 MobilePhone varchar(30) NULL ,
243 PagerPhone varchar(30) NULL ,
244 Address1 varchar(200) NULL ,
245 Address2 varchar(200) NULL ,
246 City varchar(100) NULL ,
247 State varchar(100) NULL ,
248 Zip varchar(16) NULL ,
249 Country varchar(50) NULL ,
250 Timezone char(50) NULL ,
253 Creator integer NULL DEFAULT 0 ,
254 Created DATETIME NULL ,
255 LastUpdatedBy integer NULL DEFAULT 0 ,
256 LastUpdated DATETIME NULL
261 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
262 CREATE INDEX Users2 ON Users (Name);
263 CREATE INDEX Users3 ON Users (id, EmailAddress);
264 CREATE INDEX Users4 ON Users (EmailAddress);
271 CREATE TABLE Tickets (
272 id INTEGER PRIMARY KEY ,
273 EffectiveId integer NULL DEFAULT 0 ,
274 Queue integer NULL DEFAULT 0 ,
275 Type varchar(16) NULL ,
276 IssueStatement integer NULL DEFAULT 0 ,
277 Resolution integer NULL DEFAULT 0 ,
278 Owner integer NULL DEFAULT 0 ,
279 Subject varchar(200) NULL DEFAULT '[no subject]' ,
280 InitialPriority integer NULL DEFAULT 0 ,
281 FinalPriority integer NULL DEFAULt 0 ,
282 Priority integer NULL DEFAULT 0 ,
283 TimeEstimated integer NULL DEFAULT 0 ,
284 TimeWorked integer NULL DEFAULT 0 ,
285 Status varchar(64) NULL ,
286 TimeLeft integer NULL DEFAULT 0 ,
288 Starts DATETIME NULL ,
289 Started DATETIME NULL ,
291 Resolved DATETIME NULL ,
294 LastUpdatedBy integer NULL DEFAULT 0 ,
295 LastUpdated DATETIME NULL ,
296 Creator integer NULL DEFAULT 0 ,
297 Created DATETIME NULL ,
298 Disabled int2 NOT NULL DEFAULT 0
302 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
303 CREATE INDEX Tickets2 ON Tickets (Owner) ;
304 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
305 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
306 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
312 CREATE TABLE ScripActions (
313 id INTEGER PRIMARY KEY ,
314 Name varchar(200) NULL ,
315 Description varchar(255) NULL ,
316 ExecModule varchar(60) NULL ,
317 Argument varchar(255) NULL ,
318 Creator integer NULL DEFAULT 0 ,
319 Created DATETIME NULL ,
320 LastUpdatedBy integer NULL DEFAULT 0 ,
321 LastUpdated DATETIME NULL
329 CREATE TABLE Templates (
330 id INTEGER PRIMARY KEY ,
331 Queue integer NOT NULL DEFAULT 0 ,
332 Name varchar(200) NOT NULL ,
333 Description varchar(255) NULL ,
334 Type varchar(16) NULL ,
335 Language varchar(16) NULL ,
336 TranslationOf integer NULL DEFAULT 0 ,
338 LastUpdated DATETIME NULL ,
339 LastUpdatedBy integer NULL DEFAULT 0 ,
340 Creator integer NULL DEFAULT 0 ,
341 Created DATETIME NULL
350 CREATE TABLE ObjectCustomFieldValues (
351 id INTEGER NOT NULL ,
352 CustomField int NOT NULL ,
353 ObjectType varchar(255) NOT NULL, # Final target of the Object
354 ObjectId int NOT NULL , # New -- Replaces Ticket
355 SortOrder integer NOT NULL DEFAULT 0 ,
357 Content varchar(255) NULL ,
358 LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
359 ContentType varchar(80) NULL, # New -- only text/* gets searched
360 ContentEncoding varchar(80) NULL , # New -- for binary Content
362 Creator integer NOT NULL DEFAULT 0 ,
363 Created DATETIME NULL ,
364 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
365 LastUpdated DATETIME NULL ,
366 Disabled int2 NOT NULL DEFAULT 0 ,
370 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
371 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
375 CREATE TABLE CustomFields (
376 id INTEGER NOT NULL ,
377 Name varchar(200) NULL ,
378 Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
379 RenderType varchar(64) NULL ,
380 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
381 Pattern varchar(65536) NULL , # New -- Must validate against this
382 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
383 BasedOn INTEGER NULL,
384 ValuesClass varchar(64) NULL ,
385 Description varchar(255) NULL ,
386 SortOrder integer NOT NULL DEFAULT 0 ,
387 LookupType varchar(255) NOT NULL,
389 Creator integer NOT NULL DEFAULT 0 ,
390 Created DATETIME NULL ,
391 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
392 LastUpdated DATETIME NULL ,
393 Disabled int2 NOT NULL DEFAULT 0 ,
399 CREATE TABLE ObjectCustomFields (
400 id INTEGER NOT NULL ,
401 CustomField int NOT NULL ,
402 ObjectId integer NOT NULL,
403 SortOrder integer NOT NULL DEFAULT 0 ,
405 Creator integer NOT NULL DEFAULT 0 ,
406 Created DATETIME NULL ,
407 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
408 LastUpdated DATETIME NULL ,
414 CREATE TABLE CustomFieldValues (
415 id INTEGER NOT NULL ,
416 CustomField int NOT NULL ,
417 Name varchar(200) NULL ,
418 Description varchar(255) NULL ,
419 SortOrder integer NOT NULL DEFAULT 0 ,
420 Category varchar(255) NULL ,
422 Creator integer NOT NULL DEFAULT 0 ,
423 Created DATETIME NULL ,
424 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
425 LastUpdated DATETIME NULL ,
429 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
433 CREATE TABLE Attributes (
434 id INTEGER PRIMARY KEY ,
435 Name varchar(255) NOT NULL ,
436 Description varchar(255) NULL ,
437 Content LONGTEXT NULL ,
438 ContentType varchar(16),
439 ObjectType varchar(25) NOT NULL ,
441 Creator integer NULL DEFAULT 0 ,
442 Created DATETIME NULL ,
443 LastUpdatedBy integer NULL DEFAULT 0 ,
444 LastUpdated DATETIME NULL
447 CREATE INDEX Attributes1 on Attributes(Name);
448 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
452 CREATE TABLE Classes (
453 id INTEGER PRIMARY KEY,
454 Name varchar(255) NOT NULL DEFAULT '',
455 Description varchar(255) NOT NULL DEFAULT '',
456 SortOrder integer NOT NULL DEFAULT 0,
457 Disabled smallint NOT NULL DEFAULT 0,
458 Creator integer NOT NULL DEFAULT 0,
459 Created TIMESTAMP NULL,
460 LastUpdatedBy integer NOT NULL DEFAULT 0,
461 LastUpdated TIMESTAMP NULL,
462 HotList smallint NOT NULL DEFAULT 0
465 CREATE TABLE Articles (
466 id INTEGER PRIMARY KEY,
467 Name varchar(255) NOT NULL DEFAULT '',
468 Summary varchar(255) NOT NULL DEFAULT '',
469 SortOrder integer NOT NULL DEFAULT 0,
470 Class integer NOT NULL DEFAULT 0,
471 Parent integer NOT NULL DEFAULT 0,
473 Creator integer NOT NULL DEFAULT 0,
474 Created TIMESTAMP NULL,
475 LastUpdatedBy integer NOT NULL DEFAULT 0,
476 LastUpdated TIMESTAMP NULL
480 CREATE TABLE Topics (
481 id INTEGER PRIMARY KEY,
482 Parent integer NOT NULL DEFAULT 0,
483 Name varchar(255) NOT NULL DEFAULT '',
484 Description varchar(255) NOT NULL DEFAULT '',
485 ObjectType varchar(64) NOT NULL DEFAULT '',
486 ObjectId integer NOT NULL DEFAULT 0
490 CREATE TABLE ObjectTopics (
491 id INTEGER PRIMARY KEY,
492 Topic integer NOT NULL DEFAULT 0,
493 ObjectType varchar(64) NOT NULL DEFAULT '',
494 ObjectId integer NOT NULL DEFAULT 0
497 CREATE TABLE ObjectClasses (
498 id INTEGER PRIMARY KEY,
499 Class integer NOT NULL DEFAULT 0,
500 ObjectType varchar(64) NOT NULL DEFAULT '',
501 ObjectId integer NOT NULL DEFAULT 0,
502 Creator integer NOT NULL DEFAULT 0,
503 Created TIMESTAMP NULL,
504 LastUpdatedBy integer NOT NULL DEFAULT 0,
505 LastUpdated TIMESTAMP NULL