Putting 4.2.0 on top of 4.0.17
[usit-rt.git] / etc / schema.mysql
CommitLineData
84fb5b46
MKG
1
2CREATE TABLE Attachments (
3 id INTEGER NOT NULL AUTO_INCREMENT,
4 TransactionId integer NOT NULL ,
5 Parent integer NOT NULL DEFAULT 0 ,
6 MessageId varchar(160) CHARACTER SET ascii NULL ,
7 Subject varchar(255) NULL ,
8 Filename varchar(255) NULL ,
9 ContentType varchar(80) CHARACTER SET ascii NULL ,
10 ContentEncoding varchar(80) CHARACTER SET ascii NULL ,
11 Content LONGBLOB NULL ,
12 Headers LONGTEXT NULL ,
13 Creator integer NOT NULL DEFAULT 0 ,
14 Created DATETIME NULL ,
15 PRIMARY KEY (id)
16) ENGINE=InnoDB CHARACTER SET utf8;
17
18CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
19CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
20
21CREATE TABLE Queues (
22 id INTEGER NOT NULL AUTO_INCREMENT,
23 Name varchar(200) NOT NULL ,
24 Description varchar(255) NULL ,
25 CorrespondAddress varchar(120) CHARACTER SET ascii NULL,
26 CommentAddress varchar(120) CHARACTER SET ascii NULL,
27 Lifecycle varchar(32) CHARACTER SET ascii NULL,
28 SubjectTag varchar(120) NULL,
29 InitialPriority integer NOT NULL DEFAULT 0 ,
30 FinalPriority integer NOT NULL DEFAULT 0 ,
31 DefaultDueIn integer NOT NULL DEFAULT 0 ,
32 Creator integer NOT NULL DEFAULT 0 ,
33 Created DATETIME NULL ,
34 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
35 LastUpdated DATETIME NULL ,
36 Disabled int2 NOT NULL DEFAULT 0 ,
37 PRIMARY KEY (id)
38) ENGINE=InnoDB CHARACTER SET utf8;
39
40CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
41CREATE INDEX Queues2 ON Queues (Disabled) ;
42
43
44
45CREATE TABLE Links (
46 id INTEGER NOT NULL AUTO_INCREMENT,
47 Base varchar(240) NULL,
48 Target varchar(240) NULL,
49 Type varchar(20) NOT NULL,
50 LocalTarget integer NOT NULL DEFAULT 0 ,
51 LocalBase integer NOT NULL DEFAULT 0 ,
52 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
53 LastUpdated DATETIME NULL ,
54 Creator integer NOT NULL DEFAULT 0 ,
55 Created DATETIME NULL ,
56 PRIMARY KEY (id)
57) ENGINE=InnoDB CHARACTER SET ascii;
58
59CREATE INDEX Links2 ON Links (Base, Type) ;
60CREATE INDEX Links3 ON Links (Target, Type) ;
61CREATE INDEX Links4 ON Links (Type,LocalBase);
62
63
64
65CREATE TABLE Principals (
66 id INTEGER AUTO_INCREMENT not null,
67 PrincipalType VARCHAR(16) not null,
68 ObjectId integer, # foreign key to Users or Groups, depending
69 Disabled int2 NOT NULL DEFAULT 0 ,
70 PRIMARY KEY (id)
71) ENGINE=InnoDB CHARACTER SET ascii;
72
73CREATE INDEX Principals2 ON Principals (ObjectId);
74
75
76
77CREATE TABLE Groups (
78 id INTEGER NOT NULL AUTO_INCREMENT,
79 Name varchar(200) NULL ,
80 Description varchar(255) NULL ,
81 Domain varchar(64) CHARACTER SET ascii NULL,
82 Type varchar(64) CHARACTER SET ascii NULL,
83 Instance integer,
84 Creator integer NOT NULL DEFAULT 0 ,
85 Created DATETIME NULL ,
86 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
87 LastUpdated DATETIME NULL ,
88 PRIMARY KEY (id)
89) ENGINE=InnoDB CHARACTER SET utf8;
90
af59614d
MKG
91CREATE INDEX Groups1 ON Groups (Domain, Type, Instance);
92CREATE INDEX Groups2 ON Groups (Domain, Name, Instance);
93CREATE INDEX Groups3 On Groups (Instance);
84fb5b46
MKG
94
95CREATE TABLE ScripConditions (
96 id INTEGER NOT NULL AUTO_INCREMENT,
97 Name varchar(200) NULL ,
98 Description varchar(255) NULL ,
99 ExecModule varchar(60) CHARACTER SET ascii NULL,
100 Argument VARBINARY(255) NULL ,
101 ApplicableTransTypes varchar(60) CHARACTER SET ascii NULL ,
102
103 Creator integer NOT NULL DEFAULT 0 ,
104 Created DATETIME NULL ,
105 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
106 LastUpdated DATETIME NULL ,
107 PRIMARY KEY (id)
108) ENGINE=InnoDB CHARACTER SET utf8;
109
110
111CREATE TABLE Transactions (
112 id INTEGER NOT NULL AUTO_INCREMENT,
113 ObjectType varchar(64) CHARACTER SET ascii NOT NULL,
114 ObjectId integer NOT NULL DEFAULT 0 ,
115 TimeTaken integer NOT NULL DEFAULT 0 ,
116 Type varchar(20) CHARACTER SET ascii NULL,
117 Field varchar(40) CHARACTER SET ascii NULL,
118 OldValue varchar(255) NULL ,
119 NewValue varchar(255) NULL ,
120 ReferenceType varchar(255) CHARACTER SET ascii NULL,
121 OldReference integer NULL ,
122 NewReference integer NULL ,
123 Data varchar(255) NULL ,
124
125 Creator integer NOT NULL DEFAULT 0 ,
126 Created DATETIME NULL ,
127 PRIMARY KEY (id)
128) ENGINE=InnoDB CHARACTER SET utf8;
129
130CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
131
132
133
134CREATE TABLE Scrips (
135 id INTEGER NOT NULL AUTO_INCREMENT,
136 Description varchar(255),
137 ScripCondition integer NOT NULL DEFAULT 0 ,
138 ScripAction integer NOT NULL DEFAULT 0 ,
84fb5b46
MKG
139 CustomIsApplicableCode text NULL ,
140 CustomPrepareCode text NULL ,
141 CustomCommitCode text NULL ,
af59614d
MKG
142 Disabled int2 NOT NULL DEFAULT 0 ,
143 Template varchar(200) NOT NULL ,
144 Creator integer NOT NULL DEFAULT 0 ,
145 Created DATETIME NULL ,
146 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
147 LastUpdated DATETIME NULL ,
148 PRIMARY KEY (id)
149) ENGINE=InnoDB CHARACTER SET utf8;
150
151CREATE TABLE ObjectScrips (
152 id INTEGER NOT NULL AUTO_INCREMENT,
153 Scrip integer NOT NULL ,
154 Stage varchar(32) CHARACTER SET ascii NOT NULL DEFAULT 'TransactionCreate',
155 ObjectId integer NOT NULL,
156 SortOrder integer NOT NULL DEFAULT 0 ,
157
84fb5b46
MKG
158 Creator integer NOT NULL DEFAULT 0 ,
159 Created DATETIME NULL ,
160 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
161 LastUpdated DATETIME NULL ,
162 PRIMARY KEY (id)
163) ENGINE=InnoDB CHARACTER SET utf8;
164
af59614d 165CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
84fb5b46
MKG
166
167CREATE TABLE ACL (
168 id INTEGER NOT NULL AUTO_INCREMENT,
169 PrincipalType varchar(25) CHARACTER SET ascii NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor"
170
171 PrincipalId integer NOT NULL DEFAULT 0 , #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 Creator integer NOT NULL DEFAULT 0 ,
176 Created DATETIME NULL ,
177 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
178 LastUpdated DATETIME NULL ,
179 PRIMARY KEY (id)
180) ENGINE=InnoDB CHARACTER SET utf8;
181
182CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
183
184
185
186CREATE 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
190 Creator integer NOT NULL DEFAULT 0 ,
191 Created DATETIME NULL ,
192 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
193 LastUpdated DATETIME NULL ,
194 PRIMARY KEY (id)
195) ENGINE=InnoDB CHARACTER SET utf8;
196CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
197
198
199
200
201create table CachedGroupMembers (
202 id int auto_increment,
203 GroupId int, # foreign key to Principals
204 MemberId int, # foreign key to Principals
205 Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
206 ImmediateParentId int, #foreign key to prinicpals.
207 # this points to the group that the member is
208 # a member of, for ease of deletes.
209 Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
210 # group or this group is disabled, this will be set to 1
211 # this allows us to not find members of disabled subgroups when listing off
212 # group members recursively.
213 # Also, this allows us to have the ACL system elide members of disabled groups
214 PRIMARY KEY (id)
215) ENGINE=InnoDB CHARACTER SET utf8;
216
217CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
af59614d 218CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
84fb5b46
MKG
219CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
220
221
222
223CREATE TABLE Users (
224 id INTEGER NOT NULL AUTO_INCREMENT,
225 Name varchar(200) NOT NULL ,
226 Password VARCHAR(256) NULL ,
227 AuthToken VARCHAR(16) CHARACTER SET ascii NULL ,
228 Comments TEXT NULL ,
229 Signature TEXT NULL ,
230 EmailAddress varchar(120) NULL ,
231 FreeformContactInfo TEXT NULL ,
232 Organization varchar(200) NULL ,
233 RealName varchar(120) NULL ,
234 NickName varchar(16) NULL ,
235 Lang varchar(16) NULL ,
236 EmailEncoding varchar(16) NULL ,
237 WebEncoding varchar(16) NULL ,
238 ExternalContactInfoId varchar(100) NULL ,
239 ContactInfoSystem varchar(30) NULL ,
240 ExternalAuthId varchar(100) NULL ,
241 AuthSystem varchar(30) NULL ,
242 Gecos varchar(16) NULL ,
243 HomePhone varchar(30) NULL ,
244 WorkPhone varchar(30) NULL ,
245 MobilePhone varchar(30) NULL ,
246 PagerPhone varchar(30) NULL ,
247 Address1 varchar(200) NULL ,
248 Address2 varchar(200) NULL ,
249 City varchar(100) NULL ,
250 State varchar(100) NULL ,
251 Zip varchar(16) NULL ,
252 Country varchar(50) NULL ,
253 Timezone varchar(50) NULL ,
254 PGPKey text NULL,
af59614d 255 SMIMECertificate text NULL,
84fb5b46
MKG
256
257 Creator integer NOT NULL DEFAULT 0 ,
258 Created DATETIME NULL ,
259 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
260 LastUpdated DATETIME NULL ,
261 PRIMARY KEY (id)
262) ENGINE=InnoDB CHARACTER SET utf8;
263
264
265CREATE UNIQUE INDEX Users1 ON Users (Name) ;
266CREATE INDEX Users4 ON Users (EmailAddress);
267
268
269
270
271CREATE TABLE Tickets (
272 id INTEGER NOT NULL AUTO_INCREMENT,
273 EffectiveId integer NOT NULL DEFAULT 0 ,
af59614d 274 IsMerged int2 NULL DEFAULT NULL,
84fb5b46
MKG
275 Queue integer NOT NULL DEFAULT 0 ,
276 Type varchar(16) CHARACTER SET ascii NULL ,
277 IssueStatement integer NOT NULL DEFAULT 0 ,
278 Resolution integer NOT NULL DEFAULT 0 ,
279 Owner integer NOT NULL DEFAULT 0 ,
280 Subject varchar(200) NULL DEFAULT '[no subject]' ,
281 InitialPriority integer NOT NULL DEFAULT 0 ,
282 FinalPriority integer NOT NULL DEFAULT 0 ,
283 Priority integer NOT NULL DEFAULT 0 ,
284 TimeEstimated integer NOT NULL DEFAULT 0 ,
285 TimeWorked integer NOT NULL DEFAULT 0 ,
286 Status varchar(64) NULL ,
287 TimeLeft integer NOT NULL DEFAULT 0 ,
288 Told DATETIME NULL ,
289 Starts DATETIME NULL ,
290 Started DATETIME NULL ,
291 Due DATETIME NULL ,
292 Resolved DATETIME NULL ,
293
294
295 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
296 LastUpdated DATETIME NULL ,
297 Creator integer NOT NULL DEFAULT 0 ,
298 Created DATETIME NULL ,
299 Disabled int2 NOT NULL DEFAULT 0 ,
300 PRIMARY KEY (id)
301) ENGINE=InnoDB CHARACTER SET utf8;
302
303CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
304CREATE INDEX Tickets2 ON Tickets (Owner) ;
305CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
306
307
308
309CREATE 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 ,
319 PRIMARY KEY (id)
320) ENGINE=InnoDB CHARACTER SET utf8;
321
322
323
324CREATE TABLE Templates (
325 id INTEGER NOT NULL AUTO_INCREMENT,
326 Queue integer NOT NULL DEFAULT 0 ,
327 Name varchar(200) NOT NULL ,
328 Description varchar(255) NULL ,
329 Type varchar(16) CHARACTER SET ascii NULL ,
84fb5b46
MKG
330 Content TEXT NULL ,
331 LastUpdated DATETIME NULL ,
332 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
333 Creator integer NOT NULL DEFAULT 0 ,
334 Created DATETIME NULL ,
335 PRIMARY KEY (id)
336) ENGINE=InnoDB CHARACTER SET utf8;
337
338
339
340CREATE TABLE ObjectCustomFieldValues (
341 id INTEGER NOT NULL AUTO_INCREMENT,
342 CustomField int NOT NULL ,
af59614d
MKG
343 ObjectType varchar(255) CHARACTER SET ascii NOT NULL, # Final target of the Object
344 ObjectId int NOT NULL , # New -- Replaces Ticket
84fb5b46
MKG
345 SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values
346
347 Content varchar(255) NULL ,
af59614d
MKG
348 LargeContent LONGBLOB NULL, # New -- to hold 255+ strings
349 ContentType varchar(80) CHARACTER SET ascii NULL, # New -- only text/* gets searched
350 ContentEncoding varchar(80) CHARACTER SET ascii NULL , # New -- for binary Content
84fb5b46
MKG
351
352 Creator integer NOT NULL DEFAULT 0 ,
353 Created DATETIME NULL ,
354 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
355 LastUpdated DATETIME NULL ,
356 Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current
357 PRIMARY KEY (id)
358) ENGINE=InnoDB CHARACTER SET utf8;
359
360CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
361CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
362
363
364
365CREATE TABLE CustomFields (
366 id INTEGER NOT NULL AUTO_INCREMENT,
367 Name varchar(200) NULL ,
af59614d 368 Type varchar(200) CHARACTER SET ascii NULL , # Changed -- 'Single' and 'Multiple' is moved out
84fb5b46 369 RenderType varchar(64) CHARACTER SET ascii NULL ,
af59614d
MKG
370 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
371 Pattern TEXT NULL , # New -- Must validate against this
84fb5b46
MKG
372 BasedOn INTEGER NULL,
373 ValuesClass varchar(64) CHARACTER SET ascii NULL ,
374 Description varchar(255) NULL ,
375 SortOrder integer NOT NULL DEFAULT 0 ,
376 LookupType varchar(255) CHARACTER SET ascii NOT NULL,
377
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 ,
383 PRIMARY KEY (id)
384) ENGINE=InnoDB CHARACTER SET utf8;
385
386
387
388CREATE TABLE ObjectCustomFields (
389 id INTEGER NOT NULL AUTO_INCREMENT,
390 CustomField integer NOT NULL ,
391 ObjectId integer NOT NULL,
392 SortOrder integer NOT NULL DEFAULT 0 ,
393
394 Creator integer NOT NULL DEFAULT 0 ,
395 Created DATETIME NULL ,
396 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
397 LastUpdated DATETIME NULL ,
398 PRIMARY KEY (id)
399) ENGINE=InnoDB CHARACTER SET utf8;
400
401
402
403CREATE TABLE CustomFieldValues (
404 id INTEGER NOT NULL AUTO_INCREMENT,
405 CustomField int NOT NULL ,
406 Name varchar(200) NULL ,
407 Description varchar(255) NULL ,
408 SortOrder integer NOT NULL DEFAULT 0 ,
409 Category VARCHAR(255) NULL,
410
411 Creator integer NOT NULL DEFAULT 0 ,
412 Created DATETIME NULL ,
413 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
414 LastUpdated DATETIME NULL ,
415 PRIMARY KEY (id)
416) ENGINE=InnoDB CHARACTER SET utf8;
417
418CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
419
420
421
422
423CREATE TABLE Attributes (
424 id INTEGER NOT NULL AUTO_INCREMENT,
425 Name varchar(255) NULL ,
426 Description varchar(255) NULL ,
b5747ff2 427 Content LONGBLOB,
84fb5b46
MKG
428 ContentType varchar(16) CHARACTER SET ascii,
429 ObjectType varchar(64) CHARACTER SET ascii,
430 ObjectId integer, # foreign key to anything
431 Creator integer NOT NULL DEFAULT 0 ,
432 Created DATETIME NULL ,
433 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
434 LastUpdated DATETIME NULL ,
435 PRIMARY KEY (id)
436) ENGINE=InnoDB CHARACTER SET utf8;
437
438CREATE INDEX Attributes1 on Attributes(Name);
439CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
440
441
442
443# sessions is used by Apache::Session to keep sessions in the database.
444# We should have a reaper script somewhere.
445
446CREATE TABLE sessions (
447 id char(32) NOT NULL,
448 a_session LONGBLOB,
449 LastUpdated TIMESTAMP,
450 PRIMARY KEY (id)
451) ENGINE=InnoDB CHARACTER SET ascii;
452
453CREATE TABLE Classes (
454 id int(11) NOT NULL auto_increment,
455 Name varchar(255) NOT NULL default '',
456 Description varchar(255) NOT NULL default '',
457 SortOrder int(11) NOT NULL default '0',
458 Disabled int(2) NOT NULL default '0',
459 Creator int(11) NOT NULL default '0',
460 Created datetime default NULL,
461 LastUpdatedBy int(11) NOT NULL default '0',
462 LastUpdated datetime default NULL,
463 HotList int(2) NOT NULL default '0',
464 PRIMARY KEY (id)
465) ENGINE=InnoDB DEFAULT CHARSET=utf8;
466
467CREATE TABLE Articles (
468 id int(11) NOT NULL auto_increment,
469 Name varchar(255) NOT NULL default '',
470 Summary varchar(255) NOT NULL default '',
471 SortOrder int(11) NOT NULL default '0',
472 Class int(11) NOT NULL default '0',
473 Parent int(11) NOT NULL default '0',
474 URI varchar(255) character set ascii default NULL,
475 Creator int(11) NOT NULL default '0',
476 Created datetime default NULL,
477 LastUpdatedBy int(11) NOT NULL default '0',
478 LastUpdated datetime default NULL,
479 PRIMARY KEY (id)
480) ENGINE=InnoDB DEFAULT CHARSET=utf8;
481
482CREATE TABLE Topics (
483 id int(11) NOT NULL auto_increment,
484 Parent int(11) NOT NULL default '0',
485 Name varchar(255) NOT NULL default '',
486 Description varchar(255) NOT NULL default '',
487 ObjectType varchar(64) character set ascii NOT NULL default '',
488 ObjectId int(11) NOT NULL default '0',
489 PRIMARY KEY (id)
490) ENGINE=InnoDB DEFAULT CHARSET=utf8;
491
492CREATE TABLE ObjectTopics (
493 id int(11) NOT NULL auto_increment,
494 Topic int(11) NOT NULL default '0',
495 ObjectType varchar(64) character set ascii NOT NULL default '',
496 ObjectId int(11) NOT NULL default '0',
497 PRIMARY KEY (id)
498) ENGINE=InnoDB DEFAULT CHARSET=utf8;
499
500CREATE TABLE ObjectClasses (
501 id int(11) NOT NULL auto_increment,
502 Class int(11) NOT NULL default '0',
503 ObjectType varchar(255) character set ascii NOT NULL default '',
504 ObjectId int(11) NOT NULL default '0',
505 Creator int(11) NOT NULL default '0',
506 Created datetime default NULL,
507 LastUpdatedBy int(11) NOT NULL default '0',
508 LastUpdated datetime default NULL,
509 PRIMARY KEY (id)
510) ENGINE=InnoDB DEFAULT CHARSET=utf8;