summaryrefslogtreecommitdiff
path: root/rt/etc/schema.mysql
blob: 46f8ec562768aca0133eea5767c5f2612bc13c2f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
# {{{ Attachments

CREATE TABLE Attachments (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  TransactionId integer NOT NULL  ,
  Parent integer NOT NULL DEFAULT 0  ,
  MessageId varchar(160) NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) NULL  ,
  ContentEncoding varchar(80) NULL  ,
  Content LONGTEXT NULL  ,
  Headers LONGTEXT NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
# }}}

# {{{ Queues
CREATE TABLE Queues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  CorrespondAddress varchar(120) NULL  ,
  CommentAddress varchar(120) NULL  ,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  DefaultDueIn integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) TYPE=InnoDB;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
CREATE INDEX Queues2 ON Queues (Disabled) ;

# }}}

# {{{ Links

CREATE TABLE Links (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Base varchar(240) NULL  ,
  Target varchar(240) NULL  ,
  Type varchar(20) NOT NULL  ,
  LocalTarget integer NOT NULL DEFAULT 0  ,
  LocalBase integer NOT NULL DEFAULT 0  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
CREATE INDEX Links2 ON Links (Base,  Type) ;
CREATE INDEX Links3 ON Links (Target,  Type) ;

# }}}

# {{{ Principals

CREATE TABLE Principals (
        id INTEGER  AUTO_INCREMENT not null,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer, # foreign key to Users or Groups, depending
        Disabled int2 NOT NULL DEFAULT 0 ,
        PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX Principals2 ON Principals (ObjectId);

# }}}

# {{{ Groups

CREATE TABLE Groups (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  Domain varchar(64),
  Type varchar(64),
  Instance varchar(64),
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
CREATE INDEX Groups2 On Groups  (Type, Instance, Domain);   

# }}}

# {{{ ScripConditions

CREATE TABLE ScripConditions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  ApplicableTransTypes varchar(60) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ Transactions
CREATE TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveTicket integer NOT NULL DEFAULT 0  ,
  Ticket integer NOT NULL DEFAULT 0  ,
  TimeTaken integer NOT NULL DEFAULT 0  ,
  Type varchar(20) NULL  ,
  Field varchar(40) NULL  ,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  Data varchar(100) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);

# }}}

# {{{ Scrips 

CREATE TABLE Scrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Description varchar(255),
  ScripCondition integer NOT NULL DEFAULT 0  ,
  ScripAction integer NOT NULL DEFAULT 0  ,
  ConditionRules text NULL  ,
  ActionRules text NULL  ,
  CustomIsApplicableCode text NULL  ,
  CustomPrepareCode text NULL  ,
  CustomCommitCode text NULL  ,
  Stage varchar(32) NULL  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Template integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ ACL
CREATE TABLE ACL (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor" 

  PrincipalId integer NOT NULL  , #Foreign key to principals
  RightName varchar(25) NOT NULL  ,
  ObjectType varchar(25) NOT NULL  ,
  ObjectId integer NOT NULL default 0,
  DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
  DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);

# }}}

# {{{ GroupMembers 

CREATE TABLE GroupMembers (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  GroupId integer NOT NULL DEFAULT 0,
  MemberId integer NOT NULL DEFAULT 0,  #Foreign key to Principals
  PRIMARY KEY (id)
) TYPE=InnoDB;
CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);


# }}}

# {{{ GroupMembersCache

create table CachedGroupMembers (
        id int auto_increment,
        GroupId int, # foreign key to Principals
        MemberId int, # foreign key to Principals
        Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
        ImmediateParentId int, #foreign key to prinicpals.         
                               # this points to the group that the member is
                               # a member of, for ease of deletes.
        Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
                                           # group or this group is disabled, this will be set to 1
                                           # this allows us to not find members of disabled subgroups when listing off
                                           # group members recursively.
                                           # Also, this allows us to have the ACL system elide members of disabled groups
        PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
CREATE INDEX GrouMem  on CachedGroupMembers (GroupId,MemberId);

# }}}

# {{{ Users

CREATE TABLE Users (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Password varchar(40) NULL  ,
  Comments blob NULL  ,
  Signature blob NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo blob NULL  ,
  Organization varchar(200) NULL  ,
  RealName varchar(120) NULL  ,
  NickName varchar(16) NULL  ,
  Lang varchar(16) NULL  ,
  EmailEncoding varchar(16) NULL  ,
  WebEncoding varchar(16) NULL  ,
  ExternalContactInfoId varchar(100) NULL  ,
  ContactInfoSystem varchar(30) NULL  ,
  ExternalAuthId varchar(100) NULL  ,
  AuthSystem varchar(30) NULL  ,
  Gecos varchar(16) NULL  ,
  HomePhone varchar(30) NULL  ,
  WorkPhone varchar(30) NULL  ,
  MobilePhone varchar(30) NULL  ,
  PagerPhone varchar(30) NULL  ,
  Address1 varchar(200) NULL  ,
  Address2 varchar(200) NULL  ,
  City varchar(100) NULL  ,
  State varchar(100) NULL  ,
  Zip varchar(16) NULL  ,
  Country varchar(50) NULL  ,
  Timezone varchar(50) NULL  ,
  PGPKey text NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;


CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);


# }}}

# {{{ Tickets

CREATE TABLE Tickets (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveId integer NOT NULL DEFAULT 0  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Type varchar(16) NULL  ,
  IssueStatement integer NOT NULL DEFAULT 0  ,
  Resolution integer NOT NULL DEFAULT 0  ,
  Owner integer NOT NULL DEFAULT 0  ,
  Subject varchar(200) NULL DEFAULT '[no subject]' ,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  Priority integer NOT NULL DEFAULT 0  ,
  TimeEstimated integer NOT NULL DEFAULT 0  ,
  TimeWorked integer NOT NULL DEFAULT 0  ,
  Status varchar(10) NULL  ,
  TimeLeft integer NOT NULL DEFAULT 0  ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,


  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
CREATE INDEX Tickets4 ON Tickets (id, Status) ;
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;

# }}}

# {{{ ScripActions

CREATE TABLE ScripActions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ Templates

CREATE TABLE Templates (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  Type varchar(16) NULL  ,
  Language varchar(16) NULL  ,
  TranslationOf integer NOT NULL DEFAULT 0  ,
  Content blob NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ TicketCustomFieldValues 

CREATE TABLE TicketCustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Ticket int NOT NULL  ,
  CustomField int NOT NULL  ,
  Content varchar(255) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ CustomFields

CREATE TABLE CustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Type varchar(200) NULL  ,
  Queue integer NOT NULL DEFAULT 0 ,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);


# }}}

# {{{ CustomFieldValues 

CREATE TABLE CustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) TYPE=InnoDB;

# }}}

# {{{ Sessions

# sessions is used by Apache::Session to keep sessions in the database.
# We should have a reaper script somewhere.

CREATE TABLE sessions (
    id char(32) NOT NULL,
    a_session LONGTEXT,
    LastUpdated TIMESTAMP,
    PRIMARY KEY (id)
);

# }}}