Master to 4.2.8
[usit-rt.git] / etc / schema.mysql
1
2 CREATE 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
18 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
19 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
20
21 CREATE TABLE Queues (
22   id INTEGER NOT NULL  AUTO_INCREMENT,
23   Name varchar(200) NOT NULL  ,
24   Description varchar(255) NULL  ,
25   CorrespondAddress varchar(120) NULL,
26   CommentAddress varchar(120) NULL,
27   Lifecycle varchar(32) 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
40 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
41 CREATE INDEX Queues2 ON Queues (Disabled) ;
42
43
44
45 CREATE TABLE Links (
46   id INTEGER NOT NULL  AUTO_INCREMENT,
47   Base varchar(240) NULL,
48   Target varchar(240) NULL,
49   Type varchar(20) CHARACTER SET ascii 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 utf8;
58
59 CREATE INDEX Links2 ON Links (Base,  Type) ;
60 CREATE INDEX Links3 ON Links (Target,  Type) ;
61 CREATE INDEX Links4 ON Links (Type,LocalBase);
62
63
64
65 CREATE 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
73 CREATE INDEX Principals2 ON Principals (ObjectId);
74
75
76
77 CREATE 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
91 CREATE INDEX Groups1 ON Groups (Domain, Type, Instance);
92 CREATE INDEX Groups2 ON Groups (Domain, Name, Instance);
93 CREATE INDEX Groups3 On Groups (Instance);
94
95 CREATE 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
111 CREATE 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
130 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
131
132
133
134 CREATE 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  ,
139   CustomIsApplicableCode text NULL  ,
140   CustomPrepareCode text NULL  ,
141   CustomCommitCode text NULL  ,
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
151 CREATE 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
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
165 CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
166
167 CREATE 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
182 CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
183
184
185
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
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;
196 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
197
198
199
200
201 create 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
217 CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
218 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
219 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
220
221
222
223 CREATE 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,
255   SMIMECertificate text NULL,
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
265 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
266 CREATE INDEX Users4 ON Users (EmailAddress);
267
268
269
270
271 CREATE TABLE Tickets (
272   id INTEGER NOT NULL  AUTO_INCREMENT,
273   EffectiveId integer NOT NULL DEFAULT 0  ,
274   IsMerged int2 NULL DEFAULT NULL,
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
303 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
304 CREATE INDEX Tickets2 ON Tickets (Owner) ;
305 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
306
307
308
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  ,
319   PRIMARY KEY (id)
320 ) ENGINE=InnoDB CHARACTER SET utf8;
321
322
323
324 CREATE 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  ,
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
340 CREATE TABLE ObjectCustomFieldValues (
341   id INTEGER NOT NULL  AUTO_INCREMENT,
342   CustomField int NOT NULL  ,
343   ObjectType varchar(255) CHARACTER SET ascii NOT NULL,     # Final target of the Object
344   ObjectId int NOT NULL  ,                  # New -- Replaces Ticket
345   SortOrder integer NOT NULL DEFAULT 0  ,   # New -- ordering for multiple values
346
347   Content varchar(255) NULL  ,
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
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
360 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
361 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
362
363
364
365 CREATE TABLE CustomFields (
366   id INTEGER NOT NULL  AUTO_INCREMENT,
367   Name varchar(200) NULL  ,
368   Type varchar(200) CHARACTER SET ascii NULL  , # Changed -- 'Single' and 'Multiple' is moved out
369   RenderType varchar(64) CHARACTER SET ascii NULL  ,
370   MaxValues integer,            # New -- was 'Single'(1) and 'Multiple'(0)
371   Pattern TEXT NULL  ,  # New -- Must validate against this
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
388 CREATE 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
403 CREATE 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
418 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
419
420
421
422
423 CREATE TABLE Attributes (
424   id INTEGER NOT NULL  AUTO_INCREMENT,
425   Name varchar(255) NULL  ,
426   Description varchar(255) NULL  ,
427   Content LONGBLOB,
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
438 CREATE INDEX Attributes1 on Attributes(Name);
439 CREATE 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
446 CREATE 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
453 CREATE 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
467 CREATE 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
482 CREATE 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
492 CREATE 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
500 CREATE 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;