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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ) TYPE=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 ,
293 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
294 LastUpdated DATETIME NULL ,
295 Creator integer NOT NULL DEFAULT 0 ,
296 Created DATETIME NULL ,
297 Disabled int2 NOT NULL DEFAULT 0 ,
299 ) TYPE=InnoDB CHARACTER SET utf8;
301 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
302 CREATE INDEX Tickets2 ON Tickets (Owner) ;
303 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
309 CREATE TABLE ScripActions (
310 id INTEGER NOT NULL AUTO_INCREMENT,
311 Name varchar(200) NULL ,
312 Description varchar(255) NULL ,
313 ExecModule varchar(60) CHARACTER SET ascii NULL,
314 Argument VARBINARY(255) NULL ,
315 Creator integer NOT NULL DEFAULT 0 ,
316 Created DATETIME NULL ,
317 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
318 LastUpdated DATETIME NULL ,
320 ) TYPE=InnoDB CHARACTER SET utf8;
326 CREATE TABLE Templates (
327 id INTEGER NOT NULL AUTO_INCREMENT,
328 Queue integer NOT NULL DEFAULT 0 ,
329 Name varchar(200) NOT NULL ,
330 Description varchar(255) NULL ,
331 Type varchar(16) CHARACTER SET ascii NULL ,
332 Language varchar(16) CHARACTER SET ascii NULL ,
333 TranslationOf integer NOT NULL DEFAULT 0 ,
335 LastUpdated DATETIME NULL ,
336 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
337 Creator integer NOT NULL DEFAULT 0 ,
338 Created DATETIME NULL ,
340 ) TYPE=InnoDB CHARACTER SET utf8;
344 # {{{ ObjectCustomFieldValues
346 CREATE TABLE ObjectCustomFieldValues (
347 id INTEGER NOT NULL AUTO_INCREMENT,
348 CustomField int NOT NULL ,
349 ObjectType varchar(255) CHARACTER SET ascii NOT NULL, # Final target of the Object
350 ObjectId int NOT NULL , # New -- Replaces Ticket
351 SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values
353 Content varchar(255) NULL ,
354 LargeContent LONGBLOB NULL, # New -- to hold 255+ strings
355 ContentType varchar(80) CHARACTER SET ascii NULL, # New -- only text/* gets searched
356 ContentEncoding varchar(80) CHARACTER SET ascii NULL , # New -- for binary Content
358 Creator integer NOT NULL DEFAULT 0 ,
359 Created DATETIME NULL ,
360 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
361 LastUpdated DATETIME NULL ,
362 Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current
364 ) TYPE=InnoDB CHARACTER SET utf8;
366 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
367 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
373 CREATE TABLE CustomFields (
374 id INTEGER NOT NULL AUTO_INCREMENT,
375 Name varchar(200) NULL ,
376 Type varchar(200) CHARACTER SET ascii NULL , # Changed -- 'Single' and 'Multiple' is moved out
377 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
378 Pattern TEXT NULL , # New -- Must validate against this
379 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
380 Description varchar(255) NULL ,
381 SortOrder integer NOT NULL DEFAULT 0 ,
382 LookupType varchar(255) CHARACTER SET ascii NOT NULL,
384 Creator integer NOT NULL DEFAULT 0 ,
385 Created DATETIME NULL ,
386 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
387 LastUpdated DATETIME NULL ,
388 Disabled int2 NOT NULL DEFAULT 0 ,
389 Required int2 NOT NULL DEFAULT 0 ,
391 ) TYPE=InnoDB CHARACTER SET utf8;
395 # {{{ ObjectCustomFields
397 CREATE TABLE ObjectCustomFields (
398 id INTEGER NOT NULL AUTO_INCREMENT,
399 CustomField integer NOT NULL ,
400 ObjectId integer NOT NULL,
401 SortOrder integer NOT NULL DEFAULT 0 ,
403 Creator integer NOT NULL DEFAULT 0 ,
404 Created DATETIME NULL ,
405 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
406 LastUpdated DATETIME NULL ,
408 ) TYPE=InnoDB CHARACTER SET utf8;
412 # {{{ CustomFieldValues
414 CREATE TABLE CustomFieldValues (
415 id INTEGER NOT NULL AUTO_INCREMENT,
416 CustomField int NOT NULL ,
417 Name varchar(200) NULL ,
418 Description varchar(255) NULL ,
419 SortOrder integer NOT NULL DEFAULT 0 ,
421 Creator integer NOT NULL DEFAULT 0 ,
422 Created DATETIME NULL ,
423 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
424 LastUpdated DATETIME NULL ,
426 ) TYPE=InnoDB CHARACTER SET utf8;
428 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
435 CREATE TABLE Attributes (
436 id INTEGER NOT NULL AUTO_INCREMENT,
437 Name varchar(255) NULL ,
438 Description varchar(255) NULL ,
440 ContentType varchar(16) CHARACTER SET ascii,
441 ObjectType varchar(64) CHARACTER SET ascii,
442 ObjectId integer, # foreign key to anything
443 Creator integer NOT NULL DEFAULT 0 ,
444 Created DATETIME NULL ,
445 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
446 LastUpdated DATETIME NULL ,
448 ) TYPE=InnoDB CHARACTER SET utf8;
450 CREATE INDEX Attributes1 on Attributes(Name);
451 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
457 # sessions is used by Apache::Session to keep sessions in the database.
458 # We should have a reaper script somewhere.
460 CREATE TABLE sessions (
461 id char(32) NOT NULL,
463 LastUpdated TIMESTAMP,