1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
|
--- {{{ Attachments
CREATE TABLE Attachments (
id INTEGER PRIMARY KEY ,
TransactionId INTEGER ,
Parent integer NULL DEFAULT 0 ,
MessageId varchar(160) collate NOCASE NULL ,
Subject varchar(255) collate NOCASE NULL ,
Filename varchar(255) collate NOCASE NULL ,
ContentType varchar(80) collate NOCASE NULL ,
ContentEncoding varchar(80) collate NOCASE NULL ,
Content LONGTEXT collate NOCASE NULL ,
Headers LONGTEXT collate NOCASE NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL
) ;
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
CREATE INDEX Attachments4 ON Attachments (Filename) ;
--- }}}
--- {{{ Queues
CREATE TABLE Queues (
id INTEGER PRIMARY KEY ,
Name varchar(200) collate NOCASE NOT NULL ,
Description varchar(255) collate NOCASE NULL ,
CorrespondAddress varchar(120) collate NOCASE NULL ,
CommentAddress varchar(120) collate NOCASE NULL ,
Lifecycle varchar(32) collate NOCASE NULL ,
SubjectTag varchar(120) collate NOCASE NULL ,
SortOrder integer NOT NULL DEFAULT 0 ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL ,
SLADisabled int2 NOT NULL DEFAULT 1,
Disabled int2 NOT NULL DEFAULT 0
) ;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
--- }}}
--- {{{ Links
CREATE TABLE Links (
id INTEGER PRIMARY KEY ,
Base varchar(240) collate NOCASE NULL ,
Target varchar(240) collate NOCASE NULL ,
Type varchar(20) collate NOCASE NOT NULL ,
LocalTarget integer NULL DEFAULT 0 ,
LocalBase integer NULL DEFAULT 0 ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL ,
Creator integer NULL DEFAULT 0 ,
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) collate NOCASE not null,
Disabled int2 NOT NULL DEFAULT 0
) ;
--- }}}
--- {{{ Groups
CREATE TABLE Groups (
id INTEGER ,
Name varchar(200) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
Domain varchar(64) collate NOCASE,
Instance integer,
Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
CREATE INDEX Groups1 ON Groups (Domain,Name,Instance);
CREATE INDEX Groups2 ON Groups (Instance);
--- }}}
--- {{{ ScripConditions
CREATE TABLE ScripConditions (
id INTEGER PRIMARY KEY ,
Name varchar(200) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
ExecModule varchar(60) collate NOCASE NULL ,
Argument varchar(255) collate NOCASE NULL ,
ApplicableTransTypes varchar(60) collate NOCASE NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
--- }}}
--- {{{ Transactions
CREATE TABLE Transactions (
id INTEGER PRIMARY KEY ,
ObjectType varchar(255) collate NOCASE NULL ,
ObjectId integer NULL DEFAULT 0 ,
TimeTaken integer NULL DEFAULT 0 ,
Type varchar(20) collate NOCASE NULL ,
Field varchar(40) collate NOCASE NULL ,
OldValue varchar(255) collate NOCASE NULL ,
NewValue varchar(255) collate NOCASE NULL ,
ReferenceType varchar(255) collate NOCASE NULL ,
OldReference integer NULL ,
NewReference integer NULL ,
Data varchar(255) collate NOCASE NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL
) ;
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
--- }}}
--- {{{ Scrips
CREATE TABLE Scrips (
id INTEGER PRIMARY KEY ,
Description varchar(255) collate NOCASE,
ScripCondition integer NULL DEFAULT 0 ,
ScripAction integer NULL DEFAULT 0 ,
CustomIsApplicableCode text collate NOCASE NULL ,
CustomPrepareCode text collate NOCASE NULL ,
CustomCommitCode text collate NOCASE NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
Template varchar(200) collate NOCASE NOT NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
--- }}}
CREATE TABLE ObjectScrips (
id INTEGER NOT NULL ,
Scrip int NOT NULL ,
Stage varchar(32) collate NOCASE NOT NULL DEFAULT 'TransactionCreate' ,
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 UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
--- {{{ ACL
CREATE TABLE ACL (
id INTEGER PRIMARY KEY ,
PrincipalType varchar(25) collate NOCASE NOT NULL,
PrincipalId INTEGER DEFAULT 0,
RightName varchar(25) collate NOCASE NOT NULL ,
ObjectType varchar(25) collate NOCASE 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 DEFAULT 0,
MemberId integer NULL DEFAULT 0,
Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
--- }}}
--- {{{ 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
) ;
CREATE INDEX CachedGroupMembers1 ON CachedGroupMembers (GroupId, MemberId, Disabled);
CREATE INDEX CachedGroupMembers2 ON CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX CachedGroupMembers3 ON CachedGroupMembers (MemberId, ImmediateParentId);
--- }}}
--- {{{ Users
CREATE TABLE Users (
id INTEGER ,
Name varchar(200) collate NOCASE NOT NULL ,
Password varchar(256) collate NOCASE NULL ,
AuthToken varchar(16) collate NOCASE NULL ,
Comments blob NULL ,
Signature blob NULL ,
EmailAddress varchar(120) collate NOCASE NULL ,
FreeformContactInfo blob NULL ,
Organization varchar(200) collate NOCASE NULL ,
RealName varchar(120) collate NOCASE NULL ,
NickName varchar(16) collate NOCASE NULL ,
Lang varchar(16) collate NOCASE NULL ,
Gecos varchar(16) collate NOCASE NULL ,
HomePhone varchar(30) collate NOCASE NULL ,
WorkPhone varchar(30) collate NOCASE NULL ,
MobilePhone varchar(30) collate NOCASE NULL ,
PagerPhone varchar(30) collate NOCASE NULL ,
Address1 varchar(200) collate NOCASE NULL ,
Address2 varchar(200) collate NOCASE NULL ,
City varchar(100) collate NOCASE NULL ,
State varchar(100) collate NOCASE NULL ,
Zip varchar(16) collate NOCASE NULL ,
Country varchar(50) collate NOCASE NULL ,
Timezone char(50) NULL ,
SMIMECertificate text collate NOCASE NULL,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users4 ON Users (EmailAddress);
--- }}}
--- {{{ Tickets
CREATE TABLE Tickets (
id INTEGER PRIMARY KEY ,
EffectiveId integer NULL DEFAULT 0 ,
IsMerged int2 NULL DEFAULT NULL,
Queue integer NULL DEFAULT 0 ,
Type varchar(16) collate NOCASE NULL ,
Owner integer NULL DEFAULT 0 ,
Subject varchar(200) collate NOCASE NULL DEFAULT '[no subject]' ,
InitialPriority integer NULL DEFAULT 0 ,
FinalPriority integer NULL DEFAULt 0 ,
Priority integer NULL DEFAULT 0 ,
TimeEstimated integer NULL DEFAULT 0 ,
TimeWorked integer NULL DEFAULT 0 ,
Status varchar(64) collate NOCASE NULL ,
SLA varchar(64) collate NOCASE NULL ,
TimeLeft integer NULL DEFAULT 0 ,
Told DATETIME NULL ,
Starts DATETIME NULL ,
Started DATETIME NULL ,
Due DATETIME NULL ,
Resolved DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL
) ;
CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
--- }}}
--- {{{ ScripActions
CREATE TABLE ScripActions (
id INTEGER PRIMARY KEY ,
Name varchar(200) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
ExecModule varchar(60) collate NOCASE NULL ,
Argument varchar(255) collate NOCASE NULL ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
LastUpdated DATETIME NULL
) ;
--- }}}
--- {{{ Templates
CREATE TABLE Templates (
id INTEGER PRIMARY KEY ,
Queue integer NOT NULL DEFAULT 0 ,
Name varchar(200) collate NOCASE NOT NULL ,
Description varchar(255) collate NOCASE NULL ,
Type varchar(16) collate NOCASE NULL ,
Content blob NULL ,
LastUpdated DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL
) ;
--- }}}
CREATE TABLE ObjectCustomFieldValues (
id INTEGER NOT NULL ,
CustomField int NOT NULL ,
ObjectType varchar(255) collate NOCASE NOT NULL, # Final target of the Object
ObjectId int NOT NULL , # New -- Replaces Ticket
SortOrder integer NOT NULL DEFAULT 0 ,
Content varchar(255) collate NOCASE NULL ,
LargeContent LONGTEXT collate NOCASE NULL, # New -- to hold 255+ strings
ContentType varchar(80) collate NOCASE NULL, # New -- only text/* gets searched
ContentEncoding varchar(80) collate NOCASE 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) collate NOCASE NULL ,
Type varchar(200) collate NOCASE NULL , # Changed -- 'Single' and 'Multiple' is moved out
RenderType varchar(64) collate NOCASE NULL ,
MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
Pattern varchar(65536) collate NOCASE NULL , # New -- Must validate against this
BasedOn INTEGER NULL,
ValuesClass varchar(64) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
SortOrder integer NOT NULL DEFAULT 0 ,
LookupType varchar(255) collate NOCASE NOT NULL,
EntryHint varchar(255) NULL,
UniqueValues int2 NOT NULL DEFAULT 0,
CanonicalizeClass varchar(64) collate NOCASE 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) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
SortOrder integer NOT NULL DEFAULT 0 ,
Category varchar(255) collate NOCASE 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) collate NOCASE NOT NULL ,
Description varchar(255) collate NOCASE NULL ,
Content LONGTEXT collate NOCASE NULL ,
ContentType varchar(16) collate NOCASE,
ObjectType varchar(25) collate NOCASE NOT NULL ,
ObjectId INTEGER ,
Creator integer NULL DEFAULT 0 ,
Created DATETIME NULL ,
LastUpdatedBy integer NULL DEFAULT 0 ,
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) collate NOCASE NOT NULL DEFAULT '',
Description varchar(255) collate NOCASE 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) collate NOCASE NOT NULL DEFAULT '',
Summary varchar(255) collate NOCASE NOT NULL DEFAULT '',
SortOrder integer NOT NULL DEFAULT 0,
Class integer NOT NULL DEFAULT 0,
Parent integer NOT NULL DEFAULT 0,
URI varchar(255) collate NOCASE,
Disabled smallint NOT NULL DEFAULT 0,
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) collate NOCASE NOT NULL DEFAULT '',
Description varchar(255) collate NOCASE NOT NULL DEFAULT '',
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0
);
CREATE TABLE ObjectTopics (
id INTEGER PRIMARY KEY,
Topic integer NOT NULL DEFAULT 0,
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0
);
CREATE TABLE ObjectClasses (
id INTEGER PRIMARY KEY,
Class integer NOT NULL DEFAULT 0,
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0,
Creator integer NOT NULL DEFAULT 0,
Created TIMESTAMP NULL,
LastUpdatedBy integer NOT NULL DEFAULT 0,
LastUpdated TIMESTAMP NULL
);
CREATE TABLE Assets (
id INTEGER PRIMARY KEY,
Name varchar(255) NOT NULL DEFAULT '',
Catalog int(11) NOT NULL DEFAULT 0,
Status varchar(64) NOT NULL DEFAULT '',
Description varchar(255) NOT NULL DEFAULT '',
Creator int(11) NOT NULL DEFAULT 0,
Created timestamp DEFAULT NULL,
LastUpdatedBy int(11) NOT NULL DEFAULT 0,
LastUpdated timestamp DEFAULT NULL
);
CREATE INDEX AssetsName on Assets (Name);
CREATE INDEX AssetsStatus ON Assets (Status);
CREATE INDEX AssetsCatalog ON Assets (Catalog);
CREATE TABLE Catalogs (
id INTEGER PRIMARY KEY,
Name varchar(255) NOT NULL DEFAULT '',
Lifecycle varchar(32) NOT NULL DEFAULT 'assets',
Description varchar(255) NOT NULL DEFAULT '',
Disabled int2 NOT NULL DEFAULT 0,
Creator int(11) NOT NULL DEFAULT 0,
Created timestamp DEFAULT NULL,
LastUpdatedBy int(11) NOT NULL DEFAULT 0,
LastUpdated timestamp DEFAULT NULL
);
CREATE INDEX CatalogsName on Catalogs (Name);
CREATE INDEX CatalogsDisabled ON Catalogs (Disabled);
CREATE TABLE CustomRoles (
id INTEGER NOT NULL ,
Name varchar(200) collate NOCASE NULL ,
Description varchar(255) collate NOCASE NULL ,
MaxValues integer,
EntryHint varchar(255) collate NOCASE 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 ObjectCustomRoles (
id INTEGER NOT NULL ,
CustomRole 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 UNIQUE INDEX ObjectCustomRoles1 ON ObjectCustomRoles (ObjectId, CustomRole);
|
