3 CREATE TABLE Attachments (
4 id INTEGER PRIMARY KEY ,
5 TransactionId INTEGER ,
6 Parent integer NULL DEFAULT 0 ,
7 MessageId varchar(160) collate NOCASE NULL ,
8 Subject varchar(255) collate NOCASE NULL ,
9 Filename varchar(255) collate NOCASE NULL ,
10 ContentType varchar(80) collate NOCASE NULL ,
11 ContentEncoding varchar(80) collate NOCASE NULL ,
12 Content LONGTEXT collate NOCASE NULL ,
13 Headers LONGTEXT collate NOCASE 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) collate NOCASE NOT NULL ,
28 Description varchar(255) collate NOCASE NULL ,
29 CorrespondAddress varchar(120) collate NOCASE NULL ,
30 CommentAddress varchar(120) collate NOCASE NULL ,
31 Lifecycle varchar(32) collate NOCASE NULL ,
32 SubjectTag varchar(120) collate NOCASE 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) collate NOCASE NULL ,
52 Target varchar(240) collate NOCASE NULL ,
53 Type varchar(20) collate NOCASE 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) collate NOCASE not null,
73 Disabled int2 NOT NULL DEFAULT 0
83 Name varchar(200) collate NOCASE NULL ,
84 Description varchar(255) collate NOCASE NULL ,
85 Domain varchar(64) collate NOCASE,
86 Type varchar(64) collate NOCASE,
88 Creator integer NOT NULL DEFAULT 0 ,
89 Created DATETIME NULL ,
90 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
91 LastUpdated DATETIME NULL
95 CREATE INDEX Groups1 ON Groups (Domain,Type,Instance);
96 CREATE INDEX Groups2 ON Groups (Domain,Name,Instance);
97 CREATE INDEX Groups3 ON Groups (Instance);
101 --- {{{ ScripConditions
103 CREATE TABLE ScripConditions (
104 id INTEGER PRIMARY KEY ,
105 Name varchar(200) collate NOCASE NULL ,
106 Description varchar(255) collate NOCASE NULL ,
107 ExecModule varchar(60) collate NOCASE NULL ,
108 Argument varchar(255) collate NOCASE NULL ,
109 ApplicableTransTypes varchar(60) collate NOCASE NULL ,
111 Creator integer NULL DEFAULT 0 ,
112 Created DATETIME NULL ,
113 LastUpdatedBy integer NULL DEFAULT 0 ,
114 LastUpdated DATETIME NULL
121 CREATE TABLE Transactions (
122 id INTEGER PRIMARY KEY ,
123 ObjectType varchar(255) collate NOCASE NULL ,
124 ObjectId integer NULL DEFAULT 0 ,
125 TimeTaken integer NULL DEFAULT 0 ,
126 Type varchar(20) collate NOCASE NULL ,
127 Field varchar(40) collate NOCASE NULL ,
128 OldValue varchar(255) collate NOCASE NULL ,
129 NewValue varchar(255) collate NOCASE NULL ,
130 ReferenceType varchar(255) collate NOCASE NULL ,
131 OldReference integer NULL ,
132 NewReference integer NULL ,
133 Data varchar(255) collate NOCASE NULL ,
135 Creator integer NULL DEFAULT 0 ,
136 Created DATETIME NULL
139 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
145 CREATE TABLE Scrips (
146 id INTEGER PRIMARY KEY ,
147 Description varchar(255) collate NOCASE,
148 ScripCondition integer NULL DEFAULT 0 ,
149 ScripAction integer NULL DEFAULT 0 ,
150 CustomIsApplicableCode text collate NOCASE NULL ,
151 CustomPrepareCode text collate NOCASE NULL ,
152 CustomCommitCode text collate NOCASE NULL ,
153 Disabled int2 NOT NULL DEFAULT 0 ,
154 Template varchar(200) collate NOCASE NOT NULL ,
155 Creator integer NULL DEFAULT 0 ,
156 Created DATETIME NULL ,
157 LastUpdatedBy integer NULL DEFAULT 0 ,
158 LastUpdated DATETIME NULL
164 CREATE TABLE ObjectScrips (
165 id INTEGER NOT NULL ,
167 Stage varchar(32) collate NOCASE NOT NULL DEFAULT 'TransactionCreate' ,
168 ObjectId integer NOT NULL,
169 SortOrder integer NOT NULL DEFAULT 0 ,
171 Creator integer NOT NULL DEFAULT 0 ,
172 Created DATETIME NULL ,
173 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
174 LastUpdated DATETIME NULL ,
177 CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
181 id INTEGER PRIMARY KEY ,
182 PrincipalType varchar(25) collate NOCASE NOT NULL,
184 PrincipalId INTEGER DEFAULT 0,
185 RightName varchar(25) collate NOCASE NOT NULL ,
186 ObjectType varchar(25) collate NOCASE NOT NULL ,
187 ObjectId INTEGER default 0,
188 Creator integer NOT NULL DEFAULT 0 ,
189 Created DATETIME NULL ,
190 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
191 LastUpdated DATETIME NULL
200 CREATE TABLE GroupMembers (
201 id INTEGER PRIMARY KEY ,
202 GroupId integer NULL DEFAULT 0,
203 MemberId integer NULL DEFAULT 0,
204 Creator integer NOT NULL DEFAULT 0 ,
205 Created DATETIME NULL ,
206 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
207 LastUpdated DATETIME NULL
211 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
215 --- {{{ CachedGroupMembers
217 create table CachedGroupMembers (
218 id integer primary key ,
222 ImmediateParentId int,
223 Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
224 # group or this group is disabled, this will be set to 1
225 # this allows us to not find members of disabled subgroups when listing off
226 # group members recursively.
227 # Also, this allows us to have the ACL system elide members of disabled groups
232 CREATE INDEX CachedGroupMembers1 ON CachedGroupMembers (GroupId, MemberId, Disabled);
233 CREATE INDEX CachedGroupMembers2 ON CachedGroupMembers (MemberId, GroupId, Disabled);
234 CREATE INDEX CachedGroupMembers3 ON CachedGroupMembers (MemberId, ImmediateParentId);
242 Name varchar(200) collate NOCASE NOT NULL ,
243 Password varchar(256) collate NOCASE NULL ,
244 AuthToken varchar(16) collate NOCASE NULL ,
246 Signature blob NULL ,
247 EmailAddress varchar(120) collate NOCASE NULL ,
248 FreeformContactInfo blob NULL ,
249 Organization varchar(200) collate NOCASE NULL ,
250 RealName varchar(120) collate NOCASE NULL ,
251 NickName varchar(16) collate NOCASE NULL ,
252 Lang varchar(16) collate NOCASE NULL ,
253 EmailEncoding varchar(16) collate NOCASE NULL ,
254 WebEncoding varchar(16) collate NOCASE NULL ,
255 ExternalContactInfoId varchar(100) collate NOCASE NULL ,
256 ContactInfoSystem varchar(30) collate NOCASE NULL ,
257 ExternalAuthId varchar(100) collate NOCASE NULL ,
258 AuthSystem varchar(30) collate NOCASE NULL ,
259 Gecos varchar(16) collate NOCASE NULL ,
260 HomePhone varchar(30) collate NOCASE NULL ,
261 WorkPhone varchar(30) collate NOCASE NULL ,
262 MobilePhone varchar(30) collate NOCASE NULL ,
263 PagerPhone varchar(30) collate NOCASE NULL ,
264 Address1 varchar(200) collate NOCASE NULL ,
265 Address2 varchar(200) collate NOCASE NULL ,
266 City varchar(100) collate NOCASE NULL ,
267 State varchar(100) collate NOCASE NULL ,
268 Zip varchar(16) collate NOCASE NULL ,
269 Country varchar(50) collate NOCASE NULL ,
270 Timezone char(50) NULL ,
271 PGPKey text collate NOCASE NULL,
272 SMIMECertificate text collate NOCASE NULL,
274 Creator integer NULL DEFAULT 0 ,
275 Created DATETIME NULL ,
276 LastUpdatedBy integer NULL DEFAULT 0 ,
277 LastUpdated DATETIME NULL
282 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
283 CREATE INDEX Users4 ON Users (EmailAddress);
290 CREATE TABLE Tickets (
291 id INTEGER PRIMARY KEY ,
292 EffectiveId integer NULL DEFAULT 0 ,
293 IsMerged int2 NULL DEFAULT NULL,
294 Queue integer NULL DEFAULT 0 ,
295 Type varchar(16) collate NOCASE NULL ,
296 IssueStatement integer NULL DEFAULT 0 ,
297 Resolution integer NULL DEFAULT 0 ,
298 Owner integer NULL DEFAULT 0 ,
299 Subject varchar(200) collate NOCASE NULL DEFAULT '[no subject]' ,
300 InitialPriority integer NULL DEFAULT 0 ,
301 FinalPriority integer NULL DEFAULt 0 ,
302 Priority integer NULL DEFAULT 0 ,
303 TimeEstimated integer NULL DEFAULT 0 ,
304 TimeWorked integer NULL DEFAULT 0 ,
305 Status varchar(64) collate NOCASE NULL ,
306 TimeLeft integer NULL DEFAULT 0 ,
308 Starts DATETIME NULL ,
309 Started DATETIME NULL ,
311 Resolved DATETIME NULL ,
314 LastUpdatedBy integer NULL DEFAULT 0 ,
315 LastUpdated DATETIME NULL ,
316 Creator integer NULL DEFAULT 0 ,
317 Created DATETIME NULL ,
318 Disabled int2 NOT NULL DEFAULT 0
322 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
323 CREATE INDEX Tickets2 ON Tickets (Owner) ;
324 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
330 CREATE TABLE ScripActions (
331 id INTEGER PRIMARY KEY ,
332 Name varchar(200) collate NOCASE NULL ,
333 Description varchar(255) collate NOCASE NULL ,
334 ExecModule varchar(60) collate NOCASE NULL ,
335 Argument varchar(255) collate NOCASE NULL ,
336 Creator integer NULL DEFAULT 0 ,
337 Created DATETIME NULL ,
338 LastUpdatedBy integer NULL DEFAULT 0 ,
339 LastUpdated DATETIME NULL
347 CREATE TABLE Templates (
348 id INTEGER PRIMARY KEY ,
349 Queue integer NOT NULL DEFAULT 0 ,
350 Name varchar(200) collate NOCASE NOT NULL ,
351 Description varchar(255) collate NOCASE NULL ,
352 Type varchar(16) collate NOCASE NULL ,
354 LastUpdated DATETIME NULL ,
355 LastUpdatedBy integer NULL DEFAULT 0 ,
356 Creator integer NULL DEFAULT 0 ,
357 Created DATETIME NULL
366 CREATE TABLE ObjectCustomFieldValues (
367 id INTEGER NOT NULL ,
368 CustomField int NOT NULL ,
369 ObjectType varchar(255) collate NOCASE NOT NULL, # Final target of the Object
370 ObjectId int NOT NULL , # New -- Replaces Ticket
371 SortOrder integer NOT NULL DEFAULT 0 ,
373 Content varchar(255) collate NOCASE NULL ,
374 LargeContent LONGTEXT collate NOCASE NULL, # New -- to hold 255+ strings
375 ContentType varchar(80) collate NOCASE NULL, # New -- only text/* gets searched
376 ContentEncoding varchar(80) collate NOCASE NULL , # New -- for binary Content
378 Creator integer NOT NULL DEFAULT 0 ,
379 Created DATETIME NULL ,
380 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
381 LastUpdated DATETIME NULL ,
382 Disabled int2 NOT NULL DEFAULT 0 ,
386 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
387 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
391 CREATE TABLE CustomFields (
392 id INTEGER NOT NULL ,
393 Name varchar(200) collate NOCASE NULL ,
394 Type varchar(200) collate NOCASE NULL , # Changed -- 'Single' and 'Multiple' is moved out
395 RenderType varchar(64) collate NOCASE NULL ,
396 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
397 Pattern varchar(65536) collate NOCASE NULL , # New -- Must validate against this
398 BasedOn INTEGER NULL,
399 ValuesClass varchar(64) collate NOCASE NULL ,
400 Description varchar(255) collate NOCASE NULL ,
401 SortOrder integer NOT NULL DEFAULT 0 ,
402 LookupType varchar(255) collate NOCASE NOT NULL,
404 Creator integer NOT NULL DEFAULT 0 ,
405 Created DATETIME NULL ,
406 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
407 LastUpdated DATETIME NULL ,
408 Disabled int2 NOT NULL DEFAULT 0 ,
414 CREATE TABLE ObjectCustomFields (
415 id INTEGER NOT NULL ,
416 CustomField int NOT NULL ,
417 ObjectId integer NOT NULL,
418 SortOrder integer NOT NULL DEFAULT 0 ,
420 Creator integer NOT NULL DEFAULT 0 ,
421 Created DATETIME NULL ,
422 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
423 LastUpdated DATETIME NULL ,
429 CREATE TABLE CustomFieldValues (
430 id INTEGER NOT NULL ,
431 CustomField int NOT NULL ,
432 Name varchar(200) collate NOCASE NULL ,
433 Description varchar(255) collate NOCASE NULL ,
434 SortOrder integer NOT NULL DEFAULT 0 ,
435 Category varchar(255) collate NOCASE NULL ,
437 Creator integer NOT NULL DEFAULT 0 ,
438 Created DATETIME NULL ,
439 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
440 LastUpdated DATETIME NULL ,
444 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
448 CREATE TABLE Attributes (
449 id INTEGER PRIMARY KEY ,
450 Name varchar(255) collate NOCASE NOT NULL ,
451 Description varchar(255) collate NOCASE NULL ,
452 Content LONGTEXT collate NOCASE NULL ,
453 ContentType varchar(16) collate NOCASE,
454 ObjectType varchar(25) collate NOCASE NOT NULL ,
456 Creator integer NULL DEFAULT 0 ,
457 Created DATETIME NULL ,
458 LastUpdatedBy integer NULL DEFAULT 0 ,
459 LastUpdated DATETIME NULL
462 CREATE INDEX Attributes1 on Attributes(Name);
463 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
467 CREATE TABLE Classes (
468 id INTEGER PRIMARY KEY,
469 Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
470 Description varchar(255) collate NOCASE NOT NULL DEFAULT '',
471 SortOrder integer NOT NULL DEFAULT 0,
472 Disabled smallint 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,
477 HotList smallint NOT NULL DEFAULT 0
480 CREATE TABLE Articles (
481 id INTEGER PRIMARY KEY,
482 Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
483 Summary varchar(255) collate NOCASE NOT NULL DEFAULT '',
484 SortOrder integer NOT NULL DEFAULT 0,
485 Class integer NOT NULL DEFAULT 0,
486 Parent integer NOT NULL DEFAULT 0,
487 URI varchar(255) collate NOCASE,
488 Creator integer NOT NULL DEFAULT 0,
489 Created TIMESTAMP NULL,
490 LastUpdatedBy integer NOT NULL DEFAULT 0,
491 LastUpdated TIMESTAMP NULL
495 CREATE TABLE Topics (
496 id INTEGER PRIMARY KEY,
497 Parent integer NOT NULL DEFAULT 0,
498 Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
499 Description varchar(255) collate NOCASE NOT NULL DEFAULT '',
500 ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
501 ObjectId integer NOT NULL DEFAULT 0
505 CREATE TABLE ObjectTopics (
506 id INTEGER PRIMARY KEY,
507 Topic integer NOT NULL DEFAULT 0,
508 ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
509 ObjectId integer NOT NULL DEFAULT 0
512 CREATE TABLE ObjectClasses (
513 id INTEGER PRIMARY KEY,
514 Class integer NOT NULL DEFAULT 0,
515 ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
516 ObjectId integer NOT NULL DEFAULT 0,
517 Creator integer NOT NULL DEFAULT 0,
518 Created TIMESTAMP NULL,
519 LastUpdatedBy integer NOT NULL DEFAULT 0,
520 LastUpdated TIMESTAMP NULL