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