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) ;
70 CREATE TABLE Principals (
71 id INTEGER AUTO_INCREMENT not null,
72 PrincipalType VARCHAR(16) not null,
73 ObjectId integer, # foreign key to Users or Groups, depending
74 Disabled int2 NOT NULL DEFAULT 0 ,
78 CREATE INDEX Principals2 ON Principals (ObjectId);
85 id INTEGER NOT NULL AUTO_INCREMENT,
86 Name varchar(200) NULL ,
87 Description varchar(255) NULL ,
94 CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
95 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
101 CREATE TABLE ScripConditions (
102 id INTEGER NOT NULL AUTO_INCREMENT,
103 Name varchar(200) NULL ,
104 Description varchar(255) NULL ,
105 ExecModule varchar(60) NULL ,
106 Argument varchar(255) NULL ,
107 ApplicableTransTypes varchar(60) NULL ,
109 Creator integer NOT NULL DEFAULT 0 ,
110 Created DATETIME NULL ,
111 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
112 LastUpdated DATETIME NULL ,
119 CREATE TABLE Transactions (
120 id INTEGER NOT NULL AUTO_INCREMENT,
121 EffectiveTicket integer NOT NULL DEFAULT 0 ,
122 Ticket integer NOT NULL DEFAULT 0 ,
123 TimeTaken integer NOT NULL DEFAULT 0 ,
124 Type varchar(20) NULL ,
125 Field varchar(40) NULL ,
126 OldValue varchar(255) NULL ,
127 NewValue varchar(255) NULL ,
128 Data varchar(100) NULL ,
130 Creator integer NOT NULL DEFAULT 0 ,
131 Created DATETIME NULL ,
134 CREATE INDEX Transactions1 ON Transactions (Ticket);
135 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
141 CREATE TABLE Scrips (
142 id INTEGER NOT NULL AUTO_INCREMENT,
143 Description varchar(255),
144 ScripCondition integer NOT NULL DEFAULT 0 ,
145 ScripAction integer NOT NULL DEFAULT 0 ,
146 ConditionRules text NULL ,
147 ActionRules text NULL ,
148 CustomIsApplicableCode text NULL ,
149 CustomPrepareCode text NULL ,
150 CustomCommitCode text NULL ,
151 Stage varchar(32) NULL ,
152 Queue integer NOT NULL DEFAULT 0 ,
153 Template integer NOT NULL DEFAULT 0 ,
154 Creator integer NOT NULL DEFAULT 0 ,
155 Created DATETIME NULL ,
156 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
157 LastUpdated DATETIME NULL ,
165 id INTEGER NOT NULL AUTO_INCREMENT,
166 PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor"
168 PrincipalId integer NOT NULL , #Foreign key to principals
169 RightName varchar(25) NOT NULL ,
170 ObjectType varchar(25) NOT NULL ,
171 ObjectId integer NOT NULL default 0,
172 DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
173 DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
177 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
183 CREATE TABLE GroupMembers (
184 id INTEGER NOT NULL AUTO_INCREMENT,
185 GroupId integer NOT NULL DEFAULT 0,
186 MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals
189 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
194 # {{{ GroupMembersCache
196 create table CachedGroupMembers (
197 id int auto_increment,
198 GroupId int, # foreign key to Principals
199 MemberId int, # foreign key to Principals
200 Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
201 ImmediateParentId int, #foreign key to prinicpals.
202 # this points to the group that the member is
203 # a member of, for ease of deletes.
204 Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
205 # group or this group is disabled, this will be set to 1
206 # this allows us to not find members of disabled subgroups when listing off
207 # group members recursively.
208 # Also, this allows us to have the ACL system elide members of disabled groups
212 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
213 CREATE INDEX GrouMem on CachedGroupMembers (GroupId,MemberId);
220 id INTEGER NOT NULL AUTO_INCREMENT,
221 Name varchar(200) NOT NULL ,
222 Password varchar(40) NULL ,
224 Signature blob NULL ,
225 EmailAddress varchar(120) NULL ,
226 FreeformContactInfo blob NULL ,
227 Organization varchar(200) NULL ,
228 RealName varchar(120) NULL ,
229 NickName varchar(16) NULL ,
230 Lang varchar(16) NULL ,
231 EmailEncoding varchar(16) NULL ,
232 WebEncoding varchar(16) NULL ,
233 ExternalContactInfoId varchar(100) NULL ,
234 ContactInfoSystem varchar(30) NULL ,
235 ExternalAuthId varchar(100) NULL ,
236 AuthSystem varchar(30) NULL ,
237 Gecos varchar(16) NULL ,
238 HomePhone varchar(30) NULL ,
239 WorkPhone varchar(30) NULL ,
240 MobilePhone varchar(30) NULL ,
241 PagerPhone varchar(30) NULL ,
242 Address1 varchar(200) NULL ,
243 Address2 varchar(200) NULL ,
244 City varchar(100) NULL ,
245 State varchar(100) NULL ,
246 Zip varchar(16) NULL ,
247 Country varchar(50) NULL ,
248 Timezone varchar(50) NULL ,
251 Creator integer NOT NULL DEFAULT 0 ,
252 Created DATETIME NULL ,
253 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
254 LastUpdated DATETIME NULL ,
259 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
260 CREATE INDEX Users2 ON Users (Name);
261 CREATE INDEX Users3 ON Users (id, EmailAddress);
262 CREATE INDEX Users4 ON Users (EmailAddress);
269 CREATE TABLE Tickets (
270 id INTEGER NOT NULL AUTO_INCREMENT,
271 EffectiveId integer NOT NULL DEFAULT 0 ,
272 Queue integer NOT NULL DEFAULT 0 ,
273 Type varchar(16) NULL ,
274 IssueStatement integer NOT NULL DEFAULT 0 ,
275 Resolution integer NOT NULL DEFAULT 0 ,
276 Owner integer NOT NULL DEFAULT 0 ,
277 Subject varchar(200) NULL DEFAULT '[no subject]' ,
278 InitialPriority integer NOT NULL DEFAULT 0 ,
279 FinalPriority integer NOT NULL DEFAULT 0 ,
280 Priority integer NOT NULL DEFAULT 0 ,
281 TimeEstimated integer NOT NULL DEFAULT 0 ,
282 TimeWorked integer NOT NULL DEFAULT 0 ,
283 Status varchar(10) NULL ,
284 TimeLeft integer NOT NULL DEFAULT 0 ,
286 Starts DATETIME NULL ,
287 Started DATETIME NULL ,
289 Resolved DATETIME NULL ,
292 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
293 LastUpdated DATETIME NULL ,
294 Creator integer NOT NULL DEFAULT 0 ,
295 Created DATETIME NULL ,
296 Disabled int2 NOT NULL DEFAULT 0 ,
300 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
301 CREATE INDEX Tickets2 ON Tickets (Owner) ;
302 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
303 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
304 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
305 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
311 CREATE TABLE ScripActions (
312 id INTEGER NOT NULL AUTO_INCREMENT,
313 Name varchar(200) NULL ,
314 Description varchar(255) NULL ,
315 ExecModule varchar(60) NULL ,
316 Argument varchar(255) NULL ,
317 Creator integer NOT NULL DEFAULT 0 ,
318 Created DATETIME NULL ,
319 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
320 LastUpdated DATETIME NULL ,
328 CREATE TABLE Templates (
329 id INTEGER NOT NULL AUTO_INCREMENT,
330 Queue integer NOT NULL DEFAULT 0 ,
331 Name varchar(200) NOT NULL ,
332 Description varchar(255) NULL ,
333 Type varchar(16) NULL ,
334 Language varchar(16) NULL ,
335 TranslationOf integer NOT NULL DEFAULT 0 ,
337 LastUpdated DATETIME NULL ,
338 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
339 Creator integer NOT NULL DEFAULT 0 ,
340 Created DATETIME NULL ,
346 # {{{ TicketCustomFieldValues
348 CREATE TABLE TicketCustomFieldValues (
349 id INTEGER NOT NULL AUTO_INCREMENT,
350 Ticket int NOT NULL ,
351 CustomField int NOT NULL ,
352 Content varchar(255) NULL ,
354 Creator integer NOT NULL DEFAULT 0 ,
355 Created DATETIME NULL ,
356 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
357 LastUpdated DATETIME NULL ,
365 CREATE TABLE CustomFields (
366 id INTEGER NOT NULL AUTO_INCREMENT,
367 Name varchar(200) NULL ,
368 Type varchar(200) NULL ,
369 Queue integer NOT NULL DEFAULT 0 ,
370 Description varchar(255) NULL ,
371 SortOrder integer NOT NULL DEFAULT 0 ,
373 Creator integer NOT NULL DEFAULT 0 ,
374 Created DATETIME NULL ,
375 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
376 LastUpdated DATETIME NULL ,
377 Disabled int2 NOT NULL DEFAULT 0 ,
381 CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
386 # {{{ CustomFieldValues
388 CREATE TABLE CustomFieldValues (
389 id INTEGER NOT NULL AUTO_INCREMENT,
390 CustomField int NOT NULL ,
391 Name varchar(200) NULL ,
392 Description varchar(255) NULL ,
393 SortOrder integer NOT NULL DEFAULT 0 ,
395 Creator integer NOT NULL DEFAULT 0 ,
396 Created DATETIME NULL ,
397 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
398 LastUpdated DATETIME NULL ,
406 # sessions is used by Apache::Session to keep sessions in the database.
407 # We should have a reaper script somewhere.
409 CREATE TABLE sessions (
410 id char(32) NOT NULL,
412 LastUpdated TIMESTAMP,