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) NULL ,
8 Subject varchar(255) NULL ,
9 Filename varchar(255) NULL ,
10 ContentType varchar(80) NULL ,
11 ContentEncoding varchar(80) NULL ,
12 Content LONGTEXT NULL ,
13 Headers LONGTEXT NULL ,
14 Creator integer NOT NULL DEFAULT 0 ,
15 Created DATETIME NULL ,
19 CREATE INDEX Attachments1 ON Attachments (Parent) ;
20 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
21 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
26 id INTEGER NOT NULL AUTO_INCREMENT,
27 Name varchar(200) NOT NULL ,
28 Description varchar(255) NULL ,
29 CorrespondAddress varchar(120) NULL ,
30 CommentAddress varchar(120) NULL ,
31 InitialPriority integer NOT NULL DEFAULT 0 ,
32 FinalPriority integer NOT NULL DEFAULT 0 ,
33 DefaultDueIn integer NOT NULL DEFAULT 0 ,
34 Creator integer NOT NULL DEFAULT 0 ,
35 Created DATETIME NULL ,
36 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
37 LastUpdated DATETIME NULL ,
38 Disabled int2 NOT NULL DEFAULT 0 ,
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 ,
62 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
63 CREATE INDEX Links2 ON Links (Base, Type) ;
64 CREATE INDEX Links3 ON Links (Target, Type) ;
65 CREATE INDEX Links4 ON Links(Type,LocalBase);
71 CREATE TABLE Principals (
72 id INTEGER AUTO_INCREMENT not null,
73 PrincipalType VARCHAR(16) not null,
74 ObjectId integer, # foreign key to Users or Groups, depending
75 Disabled int2 NOT NULL DEFAULT 0 ,
79 CREATE INDEX Principals2 ON Principals (ObjectId);
86 id INTEGER NOT NULL AUTO_INCREMENT,
87 Name varchar(200) NULL ,
88 Description varchar(255) NULL ,
95 CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
96 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
100 # {{{ ScripConditions
102 CREATE TABLE ScripConditions (
103 id INTEGER NOT NULL AUTO_INCREMENT,
104 Name varchar(200) NULL ,
105 Description varchar(255) NULL ,
106 ExecModule varchar(60) NULL ,
107 Argument varchar(255) NULL ,
108 ApplicableTransTypes varchar(60) NULL ,
110 Creator integer NOT NULL DEFAULT 0 ,
111 Created DATETIME NULL ,
112 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
113 LastUpdated DATETIME NULL ,
120 CREATE TABLE Transactions (
121 id INTEGER NOT NULL AUTO_INCREMENT,
122 EffectiveTicket integer NOT NULL DEFAULT 0 ,
123 Ticket integer NOT NULL DEFAULT 0 ,
124 TimeTaken integer NOT NULL DEFAULT 0 ,
125 Type varchar(20) NULL ,
126 Field varchar(40) NULL ,
127 OldValue varchar(255) NULL ,
128 NewValue varchar(255) NULL ,
129 Data varchar(255) NULL ,
131 Creator integer NOT NULL DEFAULT 0 ,
132 Created DATETIME NULL ,
135 CREATE INDEX Transactions1 ON Transactions (Ticket);
136 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
142 CREATE TABLE Scrips (
143 id INTEGER NOT NULL AUTO_INCREMENT,
144 Description varchar(255),
145 ScripCondition integer NOT NULL DEFAULT 0 ,
146 ScripAction integer NOT NULL DEFAULT 0 ,
147 ConditionRules text NULL ,
148 ActionRules text NULL ,
149 CustomIsApplicableCode text NULL ,
150 CustomPrepareCode text NULL ,
151 CustomCommitCode text NULL ,
152 Stage varchar(32) NULL ,
153 Queue integer NOT NULL DEFAULT 0 ,
154 Template integer NOT NULL DEFAULT 0 ,
155 Creator integer NOT NULL DEFAULT 0 ,
156 Created DATETIME NULL ,
157 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
158 LastUpdated DATETIME NULL ,
166 id INTEGER NOT NULL AUTO_INCREMENT,
167 PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor"
169 PrincipalId integer NOT NULL , #Foreign key to principals
170 RightName varchar(25) NOT NULL ,
171 ObjectType varchar(25) NOT NULL ,
172 ObjectId integer NOT NULL default 0,
173 DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
174 DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
178 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
184 CREATE TABLE GroupMembers (
185 id INTEGER NOT NULL AUTO_INCREMENT,
186 GroupId integer NOT NULL DEFAULT 0,
187 MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals
190 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
195 # {{{ GroupMembersCache
197 create table CachedGroupMembers (
198 id int auto_increment,
199 GroupId int, # foreign key to Principals
200 MemberId int, # foreign key to Principals
201 Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
202 ImmediateParentId int, #foreign key to prinicpals.
203 # this points to the group that the member is
204 # a member of, for ease of deletes.
205 Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
206 # group or this group is disabled, this will be set to 1
207 # this allows us to not find members of disabled subgroups when listing off
208 # group members recursively.
209 # Also, this allows us to have the ACL system elide members of disabled groups
213 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
214 CREATE INDEX GrouMem on CachedGroupMembers (GroupId,MemberId);
221 id INTEGER NOT NULL AUTO_INCREMENT,
222 Name varchar(200) NOT NULL ,
223 Password varchar(40) NULL ,
225 Signature blob NULL ,
226 EmailAddress varchar(120) NULL ,
227 FreeformContactInfo blob NULL ,
228 Organization varchar(200) NULL ,
229 RealName varchar(120) NULL ,
230 NickName varchar(16) NULL ,
231 Lang varchar(16) NULL ,
232 EmailEncoding varchar(16) NULL ,
233 WebEncoding varchar(16) NULL ,
234 ExternalContactInfoId varchar(100) NULL ,
235 ContactInfoSystem varchar(30) NULL ,
236 ExternalAuthId varchar(100) NULL ,
237 AuthSystem varchar(30) NULL ,
238 Gecos varchar(16) NULL ,
239 HomePhone varchar(30) NULL ,
240 WorkPhone varchar(30) NULL ,
241 MobilePhone varchar(30) NULL ,
242 PagerPhone varchar(30) NULL ,
243 Address1 varchar(200) NULL ,
244 Address2 varchar(200) NULL ,
245 City varchar(100) NULL ,
246 State varchar(100) NULL ,
247 Zip varchar(16) NULL ,
248 Country varchar(50) NULL ,
249 Timezone varchar(50) NULL ,
252 Creator integer NOT NULL DEFAULT 0 ,
253 Created DATETIME NULL ,
254 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
255 LastUpdated DATETIME NULL ,
260 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
261 CREATE INDEX Users2 ON Users (Name);
262 CREATE INDEX Users3 ON Users (id, EmailAddress);
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) 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 ,
301 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
302 CREATE INDEX Tickets2 ON Tickets (Owner) ;
303 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
304 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
305 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
306 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
312 CREATE TABLE ScripActions (
313 id INTEGER NOT NULL AUTO_INCREMENT,
314 Name varchar(200) NULL ,
315 Description varchar(255) NULL ,
316 ExecModule varchar(60) NULL ,
317 Argument varchar(255) NULL ,
318 Creator integer NOT NULL DEFAULT 0 ,
319 Created DATETIME NULL ,
320 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
321 LastUpdated DATETIME NULL ,
329 CREATE TABLE Templates (
330 id INTEGER NOT NULL AUTO_INCREMENT,
331 Queue integer NOT NULL DEFAULT 0 ,
332 Name varchar(200) NOT NULL ,
333 Description varchar(255) NULL ,
334 Type varchar(16) NULL ,
335 Language varchar(16) NULL ,
336 TranslationOf integer NOT NULL DEFAULT 0 ,
338 LastUpdated DATETIME NULL ,
339 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
340 Creator integer NOT NULL DEFAULT 0 ,
341 Created DATETIME NULL ,
347 # {{{ TicketCustomFieldValues
349 CREATE TABLE TicketCustomFieldValues (
350 id INTEGER NOT NULL AUTO_INCREMENT,
351 Ticket int NOT NULL ,
352 CustomField int NOT NULL ,
353 Content varchar(255) NULL ,
355 Creator integer NOT NULL DEFAULT 0 ,
356 Created DATETIME NULL ,
357 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
358 LastUpdated DATETIME NULL ,
362 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
363 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
369 CREATE TABLE CustomFields (
370 id INTEGER NOT NULL AUTO_INCREMENT,
371 Name varchar(200) NULL ,
372 Type varchar(200) NULL ,
373 Queue integer NOT NULL DEFAULT 0 ,
374 Description varchar(255) NULL ,
375 SortOrder integer NOT NULL DEFAULT 0 ,
377 Creator integer NOT NULL DEFAULT 0 ,
378 Created DATETIME NULL ,
379 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
380 LastUpdated DATETIME NULL ,
381 Disabled int2 NOT NULL DEFAULT 0 ,
385 CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
390 # {{{ CustomFieldValues
392 CREATE 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 ,
399 Creator integer NOT NULL DEFAULT 0 ,
400 Created DATETIME NULL ,
401 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
402 LastUpdated DATETIME NULL ,
406 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
412 # sessions is used by Apache::Session to keep sessions in the database.
413 # We should have a reaper script somewhere.
415 CREATE TABLE sessions (
416 id char(32) NOT NULL,
418 LastUpdated TIMESTAMP,