3 CREATE TABLE Attachments (
4 id INTEGER NOT NULL AUTO_INCREMENT,
5 TransactionId integer NOT NULL ,
6 Parent integer NOT NULL DEFAULT 0 ,
7 MessageId varchar(160) CHARACTER SET ascii NULL ,
8 Subject varchar(255) NULL ,
9 Filename varchar(255) NULL ,
10 ContentType varchar(80) CHARACTER SET ascii NULL ,
11 ContentEncoding varchar(80) CHARACTER SET ascii NULL ,
12 Content LONGBLOB NULL ,
13 Headers LONGTEXT NULL ,
14 Creator integer NOT NULL DEFAULT 0 ,
15 Created DATETIME NULL ,
17 ) ENGINE=InnoDB CHARACTER SET utf8;
19 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
20 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
25 id INTEGER NOT NULL AUTO_INCREMENT,
26 Name varchar(200) NOT NULL ,
27 Description varchar(255) NULL ,
28 CorrespondAddress varchar(120) CHARACTER SET ascii NULL,
29 CommentAddress varchar(120) CHARACTER SET ascii NULL,
30 InitialPriority integer NOT NULL DEFAULT 0 ,
31 FinalPriority integer NOT NULL DEFAULT 0 ,
32 DefaultDueIn integer NOT NULL DEFAULT 0 ,
33 Creator integer NOT NULL DEFAULT 0 ,
34 Created DATETIME NULL ,
35 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
36 LastUpdated DATETIME NULL ,
37 Disabled int2 NOT NULL DEFAULT 0 ,
39 ) ENGINE=InnoDB CHARACTER SET utf8;
41 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
42 CREATE INDEX Queues2 ON Queues (Disabled) ;
49 id INTEGER NOT NULL AUTO_INCREMENT,
50 Base varchar(240) NULL,
51 Target varchar(240) NULL,
52 Type varchar(20) NOT NULL,
53 LocalTarget integer NOT NULL DEFAULT 0 ,
54 LocalBase integer NOT NULL DEFAULT 0 ,
55 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
56 LastUpdated DATETIME NULL ,
57 Creator integer NOT NULL DEFAULT 0 ,
58 Created DATETIME NULL ,
60 ) ENGINE=InnoDB CHARACTER SET ascii;
62 CREATE INDEX Links2 ON Links (Base, Type) ;
63 CREATE INDEX Links3 ON Links (Target, Type) ;
64 CREATE INDEX Links4 ON Links (Type,LocalBase);
70 CREATE TABLE Principals (
71 id INTEGER AUTO_INCREMENT not null,
72 PrincipalType VARCHAR(16) not null,
73 ObjectId integer, # foreign key to Users or Groups, depending
74 Disabled int2 NOT NULL DEFAULT 0 ,
76 ) ENGINE=InnoDB CHARACTER SET ascii;
78 CREATE INDEX Principals2 ON Principals (ObjectId);
85 id INTEGER NOT NULL AUTO_INCREMENT,
86 Name varchar(200) NULL ,
87 Description varchar(255) NULL ,
88 Domain varchar(64) CHARACTER SET ascii NULL,
89 Type varchar(64) CHARACTER SET ascii NULL,
92 ) ENGINE=InnoDB CHARACTER SET utf8;
94 CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
95 CREATE INDEX Groups2 On Groups (Type, Instance);
101 CREATE TABLE ScripConditions (
102 id INTEGER NOT NULL AUTO_INCREMENT,
103 Name varchar(200) NULL ,
104 Description varchar(255) NULL ,
105 ExecModule varchar(60) CHARACTER SET ascii NULL,
106 Argument VARBINARY(255) NULL ,
107 ApplicableTransTypes varchar(60) CHARACTER SET ascii NULL ,
109 Creator integer NOT NULL DEFAULT 0 ,
110 Created DATETIME NULL ,
111 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
112 LastUpdated DATETIME NULL ,
114 ) ENGINE=InnoDB CHARACTER SET utf8;
119 CREATE TABLE Transactions (
120 id INTEGER NOT NULL AUTO_INCREMENT,
121 ObjectType varchar(64) CHARACTER SET ascii NOT NULL,
122 ObjectId integer NOT NULL DEFAULT 0 ,
123 TimeTaken integer NOT NULL DEFAULT 0 ,
124 Type varchar(20) CHARACTER SET ascii NULL,
125 Field varchar(40) CHARACTER SET ascii NULL,
126 OldValue varchar(255) NULL ,
127 NewValue varchar(255) NULL ,
128 ReferenceType varchar(255) CHARACTER SET ascii NULL,
129 OldReference integer NULL ,
130 NewReference integer NULL ,
131 Data varchar(255) NULL ,
133 Creator integer NOT NULL DEFAULT 0 ,
134 Created DATETIME NULL ,
136 ) ENGINE=InnoDB CHARACTER SET utf8;
138 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
144 CREATE TABLE Scrips (
145 id INTEGER NOT NULL AUTO_INCREMENT,
146 Description varchar(255),
147 ScripCondition integer NOT NULL DEFAULT 0 ,
148 ScripAction integer NOT NULL DEFAULT 0 ,
149 ConditionRules text NULL ,
150 ActionRules text NULL ,
151 CustomIsApplicableCode text NULL ,
152 CustomPrepareCode text NULL ,
153 CustomCommitCode text NULL ,
154 Stage varchar(32) CHARACTER SET ascii NULL ,
155 Queue integer NOT NULL DEFAULT 0 ,
156 Template integer NOT NULL DEFAULT 0 ,
157 Creator integer NOT NULL DEFAULT 0 ,
158 Created DATETIME NULL ,
159 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
160 LastUpdated DATETIME NULL ,
162 ) ENGINE=InnoDB CHARACTER SET utf8;
168 id INTEGER NOT NULL AUTO_INCREMENT,
169 PrincipalType varchar(25) CHARACTER SET ascii NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor"
171 PrincipalId integer NOT NULL , #Foreign key to principals
172 RightName varchar(25) CHARACTER SET ascii NOT NULL,
173 ObjectType varchar(25) CHARACTER SET ascii NOT NULL,
174 ObjectId integer NOT NULL default 0,
175 DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
176 DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
178 ) ENGINE=InnoDB CHARACTER SET utf8;
180 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
186 CREATE TABLE GroupMembers (
187 id INTEGER NOT NULL AUTO_INCREMENT,
188 GroupId integer NOT NULL DEFAULT 0,
189 MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals
191 ) ENGINE=InnoDB CHARACTER SET utf8;
192 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
197 # {{{ GroupMembersCache
199 create table CachedGroupMembers (
200 id int auto_increment,
201 GroupId int, # foreign key to Principals
202 MemberId int, # foreign key to Principals
203 Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
204 ImmediateParentId int, #foreign key to prinicpals.
205 # this points to the group that the member is
206 # a member of, for ease of deletes.
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
213 ) ENGINE=InnoDB CHARACTER SET utf8;
215 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
216 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
223 id INTEGER NOT NULL AUTO_INCREMENT,
224 Name varchar(200) NOT NULL ,
225 Password VARBINARY(40) NULL ,
227 Signature TEXT NULL ,
228 EmailAddress varchar(120) NULL ,
229 FreeformContactInfo TEXT NULL ,
230 Organization varchar(200) NULL ,
231 RealName varchar(120) NULL ,
232 NickName varchar(16) NULL ,
233 Lang varchar(16) NULL ,
234 EmailEncoding varchar(16) NULL ,
235 WebEncoding varchar(16) NULL ,
236 ExternalContactInfoId varchar(100) NULL ,
237 ContactInfoSystem varchar(30) NULL ,
238 ExternalAuthId varchar(100) NULL ,
239 AuthSystem varchar(30) NULL ,
240 Gecos varchar(16) NULL ,
241 HomePhone varchar(30) NULL ,
242 WorkPhone varchar(30) NULL ,
243 MobilePhone varchar(30) NULL ,
244 PagerPhone varchar(30) NULL ,
245 Address1 varchar(200) NULL ,
246 Address2 varchar(200) NULL ,
247 City varchar(100) NULL ,
248 State varchar(100) NULL ,
249 Zip varchar(16) NULL ,
250 Country varchar(50) NULL ,
251 Timezone varchar(50) NULL ,
254 Creator integer NOT NULL DEFAULT 0 ,
255 Created DATETIME NULL ,
256 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
257 LastUpdated DATETIME NULL ,
259 ) ENGINE=InnoDB CHARACTER SET utf8;
262 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
263 CREATE INDEX Users4 ON Users (EmailAddress);
270 CREATE TABLE Tickets (
271 id INTEGER NOT NULL AUTO_INCREMENT,
272 EffectiveId integer NOT NULL DEFAULT 0 ,
273 Queue integer NOT NULL DEFAULT 0 ,
274 Type varchar(16) CHARACTER SET ascii NULL ,
275 IssueStatement integer NOT NULL DEFAULT 0 ,
276 Resolution integer NOT NULL DEFAULT 0 ,
277 Owner integer NOT NULL DEFAULT 0 ,
278 Subject varchar(200) NULL DEFAULT '[no subject]' ,
279 InitialPriority integer NOT NULL DEFAULT 0 ,
280 FinalPriority integer NOT NULL DEFAULT 0 ,
281 Priority integer NOT NULL DEFAULT 0 ,
282 TimeEstimated integer NOT NULL DEFAULT 0 ,
283 TimeWorked integer NOT NULL DEFAULT 0 ,
284 Status varchar(10) NULL ,
285 TimeLeft integer NOT NULL DEFAULT 0 ,
287 Starts DATETIME NULL ,
288 Started DATETIME NULL ,
290 Resolved DATETIME NULL ,
292 WillResolve DATETIME NULL ,
294 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
295 LastUpdated DATETIME NULL ,
296 Creator integer NOT NULL DEFAULT 0 ,
297 Created DATETIME NULL ,
298 Disabled int2 NOT NULL DEFAULT 0 ,
300 ) ENGINE=InnoDB CHARACTER SET utf8;
302 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
303 CREATE INDEX Tickets2 ON Tickets (Owner) ;
304 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
310 CREATE TABLE ScripActions (
311 id INTEGER NOT NULL AUTO_INCREMENT,
312 Name varchar(200) NULL ,
313 Description varchar(255) NULL ,
314 ExecModule varchar(60) CHARACTER SET ascii NULL,
315 Argument VARBINARY(255) NULL ,
316 Creator integer NOT NULL DEFAULT 0 ,
317 Created DATETIME NULL ,
318 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
319 LastUpdated DATETIME NULL ,
321 ) ENGINE=InnoDB CHARACTER SET utf8;
327 CREATE TABLE Templates (
328 id INTEGER NOT NULL AUTO_INCREMENT,
329 Queue integer NOT NULL DEFAULT 0 ,
330 Name varchar(200) NOT NULL ,
331 Description varchar(255) NULL ,
332 Type varchar(16) CHARACTER SET ascii NULL ,
333 Language varchar(16) CHARACTER SET ascii NULL ,
334 TranslationOf integer NOT NULL DEFAULT 0 ,
336 LastUpdated DATETIME NULL ,
337 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
338 Creator integer NOT NULL DEFAULT 0 ,
339 Created DATETIME NULL ,
341 ) ENGINE=InnoDB CHARACTER SET utf8;
345 # {{{ ObjectCustomFieldValues
347 CREATE TABLE ObjectCustomFieldValues (
348 id INTEGER NOT NULL AUTO_INCREMENT,
349 CustomField int NOT NULL ,
350 ObjectType varchar(255) CHARACTER SET ascii NOT NULL, # Final target of the Object
351 ObjectId int NOT NULL , # New -- Replaces Ticket
352 SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values
354 Content varchar(255) NULL ,
355 LargeContent LONGBLOB NULL, # New -- to hold 255+ strings
356 ContentType varchar(80) CHARACTER SET ascii NULL, # New -- only text/* gets searched
357 ContentEncoding varchar(80) CHARACTER SET ascii NULL , # New -- for binary Content
359 Creator integer NOT NULL DEFAULT 0 ,
360 Created DATETIME NULL ,
361 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
362 LastUpdated DATETIME NULL ,
363 Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current
365 ) ENGINE=InnoDB CHARACTER SET utf8;
367 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
368 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
374 CREATE TABLE CustomFields (
375 id INTEGER NOT NULL AUTO_INCREMENT,
376 Name varchar(200) NULL ,
377 Type varchar(200) CHARACTER SET ascii NULL , # Changed -- 'Single' and 'Multiple' is moved out
378 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
379 Pattern TEXT NULL , # New -- Must validate against this
380 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
381 Description varchar(255) NULL ,
382 SortOrder integer NOT NULL DEFAULT 0 ,
383 LookupType varchar(255) CHARACTER SET ascii NOT NULL,
385 Creator integer NOT NULL DEFAULT 0 ,
386 Created DATETIME NULL ,
387 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
388 LastUpdated DATETIME NULL ,
389 Disabled int2 NOT NULL DEFAULT 0 ,
390 Required int2 NOT NULL DEFAULT 0 ,
392 ) ENGINE=InnoDB CHARACTER SET utf8;
396 # {{{ ObjectCustomFields
398 CREATE TABLE ObjectCustomFields (
399 id INTEGER NOT NULL AUTO_INCREMENT,
400 CustomField integer NOT NULL ,
401 ObjectId integer NOT NULL,
402 SortOrder integer NOT NULL DEFAULT 0 ,
404 Creator integer NOT NULL DEFAULT 0 ,
405 Created DATETIME NULL ,
406 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
407 LastUpdated DATETIME NULL ,
409 ) ENGINE=InnoDB CHARACTER SET utf8;
413 # {{{ CustomFieldValues
415 CREATE TABLE CustomFieldValues (
416 id INTEGER NOT NULL AUTO_INCREMENT,
417 CustomField int NOT NULL ,
418 Name varchar(200) NULL ,
419 Description varchar(255) NULL ,
420 SortOrder integer NOT NULL DEFAULT 0 ,
422 Creator integer NOT NULL DEFAULT 0 ,
423 Created DATETIME NULL ,
424 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
425 LastUpdated DATETIME NULL ,
427 ) ENGINE=InnoDB CHARACTER SET utf8;
429 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
436 CREATE TABLE Attributes (
437 id INTEGER NOT NULL AUTO_INCREMENT,
438 Name varchar(255) NULL ,
439 Description varchar(255) NULL ,
441 ContentType varchar(16) CHARACTER SET ascii,
442 ObjectType varchar(64) CHARACTER SET ascii,
443 ObjectId integer, # foreign key to anything
444 Creator integer NOT NULL DEFAULT 0 ,
445 Created DATETIME NULL ,
446 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
447 LastUpdated DATETIME NULL ,
449 ) ENGINE=InnoDB CHARACTER SET utf8;
451 CREATE INDEX Attributes1 on Attributes(Name);
452 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
458 # sessions is used by Apache::Session to keep sessions in the database.
459 # We should have a reaper script somewhere.
461 CREATE TABLE sessions (
462 id char(32) NOT NULL,
464 LastUpdated TIMESTAMP,