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 ObjectType varchar(255) NULL ,
116 ObjectId 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 ReferenceType varchar(255) NULL ,
123 OldReference integer NULL ,
124 NewReference integer NULL ,
125 Data varchar(255) NULL ,
127 Creator integer NULL ,
128 Created DATETIME NULL
131 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
137 CREATE TABLE Scrips (
138 id INTEGER PRIMARY KEY ,
139 Description varchar(255),
140 ScripCondition integer NULL ,
141 ScripAction integer NULL ,
142 ConditionRules text NULL ,
143 ActionRules text NULL ,
144 CustomIsApplicableCode text NULL ,
145 CustomPrepareCode text NULL ,
146 CustomCommitCode text NULL ,
147 Stage varchar(32) NULL ,
149 Template integer NULL ,
150 Creator integer NULL ,
151 Created DATETIME NULL ,
152 LastUpdatedBy integer NULL ,
153 LastUpdated DATETIME NULL
161 id INTEGER PRIMARY KEY ,
162 PrincipalType varchar(25) NOT NULL,
165 RightName varchar(25) NOT NULL ,
166 ObjectType varchar(25) NOT NULL ,
167 ObjectId INTEGER default 0,
168 DelegatedBy integer NOT NULL default 0,
169 DelegatedFrom integer NOT NULL default 0
178 CREATE TABLE GroupMembers (
179 id INTEGER PRIMARY KEY ,
180 GroupId integer NULL,
181 MemberId integer NULL
187 --- {{{ CachedGroupMembers
189 create table CachedGroupMembers (
190 id integer primary key ,
194 ImmediateParentId int,
195 Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
196 # group or this group is disabled, this will be set to 1
197 # this allows us to not find members of disabled subgroups when listing off
198 # group members recursively.
199 # Also, this allows us to have the ACL system elide members of disabled groups
210 Name varchar(200) NOT NULL ,
211 Password varchar(40) NULL ,
213 Signature blob NULL ,
214 EmailAddress varchar(120) NULL ,
215 FreeformContactInfo blob NULL ,
216 Organization varchar(200) NULL ,
217 RealName varchar(120) NULL ,
218 NickName varchar(16) NULL ,
219 Lang varchar(16) NULL ,
220 EmailEncoding varchar(16) NULL ,
221 WebEncoding varchar(16) NULL ,
222 ExternalContactInfoId varchar(100) NULL ,
223 ContactInfoSystem varchar(30) NULL ,
224 ExternalAuthId varchar(100) NULL ,
225 AuthSystem varchar(30) NULL ,
226 Gecos varchar(16) NULL ,
227 HomePhone varchar(30) NULL ,
228 WorkPhone varchar(30) NULL ,
229 MobilePhone varchar(30) NULL ,
230 PagerPhone varchar(30) NULL ,
231 Address1 varchar(200) NULL ,
232 Address2 varchar(200) NULL ,
233 City varchar(100) NULL ,
234 State varchar(100) NULL ,
235 Zip varchar(16) NULL ,
236 Country varchar(50) NULL ,
237 Timezone char(50) NULL ,
240 Creator integer NULL ,
241 Created DATETIME NULL ,
242 LastUpdatedBy integer NULL ,
243 LastUpdated DATETIME NULL
248 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
249 CREATE INDEX Users2 ON Users (Name);
250 CREATE INDEX Users3 ON Users (id, EmailAddress);
251 CREATE INDEX Users4 ON Users (EmailAddress);
258 CREATE TABLE Tickets (
259 id INTEGER PRIMARY KEY ,
260 EffectiveId integer NULL ,
262 Type varchar(16) NULL ,
263 IssueStatement integer NULL ,
264 Resolution integer NULL ,
266 Subject varchar(200) NULL DEFAULT '[no subject]' ,
267 InitialPriority integer NULL ,
268 FinalPriority integer NULL ,
269 Priority integer NULL ,
270 TimeEstimated integer NULL ,
271 TimeWorked integer NULL ,
272 Status varchar(10) NULL ,
273 TimeLeft integer NULL ,
275 Starts DATETIME NULL ,
276 Started DATETIME NULL ,
278 Resolved DATETIME NULL ,
281 LastUpdatedBy integer NULL ,
282 LastUpdated DATETIME NULL ,
283 Creator integer NULL ,
284 Created DATETIME NULL ,
285 Disabled int2 NOT NULL DEFAULT 0
289 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
290 CREATE INDEX Tickets2 ON Tickets (Owner) ;
291 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
292 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
293 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
299 CREATE TABLE ScripActions (
300 id INTEGER PRIMARY KEY ,
301 Name varchar(200) NULL ,
302 Description varchar(255) NULL ,
303 ExecModule varchar(60) NULL ,
304 Argument varchar(255) NULL ,
305 Creator integer NULL ,
306 Created DATETIME NULL ,
307 LastUpdatedBy integer NULL ,
308 LastUpdated DATETIME NULL
316 CREATE TABLE Templates (
317 id INTEGER PRIMARY KEY ,
318 Queue integer NOT NULL DEFAULT 0 ,
319 Name varchar(200) NOT NULL ,
320 Description varchar(255) NULL ,
321 Type varchar(16) NULL ,
322 Language varchar(16) NULL ,
323 TranslationOf integer NULL ,
325 LastUpdated DATETIME NULL ,
326 LastUpdatedBy integer NULL ,
327 Creator integer NULL ,
328 Created DATETIME NULL
336 # {{{ ObjectCustomFieldValues
338 CREATE TABLE ObjectCustomFieldValues (
339 id INTEGER NOT NULL ,
340 CustomField int NOT NULL ,
341 ObjectType varchar(255) NOT NULL, # Final target of the Object
342 ObjectId int NOT NULL , # New -- Replaces Ticket
343 SortOrder integer NOT NULL DEFAULT 0 ,
345 Content varchar(255) NULL ,
346 LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
347 ContentType varchar(80) NULL, # New -- only text/* gets searched
348 ContentEncoding varchar(80) NULL , # New -- for binary Content
350 Creator integer NOT NULL DEFAULT 0 ,
351 Created DATETIME NULL ,
352 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
353 LastUpdated DATETIME NULL ,
354 Disabled int2 NOT NULL DEFAULT 0 ,
358 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
359 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
365 CREATE TABLE CustomFields (
366 id INTEGER NOT NULL ,
367 Name varchar(200) NULL ,
368 Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
369 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
370 Pattern varchar(255) NULL , # New -- Must validate against this
371 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
372 Description varchar(255) NULL ,
373 SortOrder integer NOT NULL DEFAULT 0 ,
374 LookupType varchar(255) NOT NULL,
376 Creator integer NOT NULL DEFAULT 0 ,
377 Created DATETIME NULL ,
378 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
379 LastUpdated DATETIME NULL ,
380 Disabled int2 NOT NULL DEFAULT 0 ,
386 # {{{ ObjectCustomFields
388 CREATE TABLE ObjectCustomFields (
389 id INTEGER NOT NULL ,
390 CustomField int NOT NULL ,
391 ObjectId integer NOT NULL,
392 SortOrder integer NOT NULL DEFAULT 0 ,
394 Creator integer NOT NULL DEFAULT 0 ,
395 Created DATETIME NULL ,
396 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
397 LastUpdated DATETIME NULL ,
403 # {{{ CustomFieldValues
405 CREATE TABLE CustomFieldValues (
406 id INTEGER NOT NULL ,
407 CustomField int NOT NULL ,
408 Name varchar(200) NULL ,
409 Description varchar(255) NULL ,
410 SortOrder integer NOT NULL DEFAULT 0 ,
412 Creator integer NOT NULL DEFAULT 0 ,
413 Created DATETIME NULL ,
414 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
415 LastUpdated DATETIME NULL ,
419 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
424 CREATE TABLE Attributes (
425 id INTEGER PRIMARY KEY ,
426 Name varchar(255) NOT NULL ,
427 Description varchar(255) NULL ,
428 Content LONGTEXT NULL ,
429 ContentType varchar(16),
430 ObjectType varchar(25) NOT NULL ,
431 ObjectId INTEGER default 0,
432 Creator integer NULL ,
433 Created DATETIME NULL ,
434 LastUpdatedBy integer NULL ,
435 LastUpdated DATETIME NULL
438 CREATE INDEX Attributes1 on Attributes(Name);
439 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);