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 Attachments2 ON Attachments (TransactionId) ;
20 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
25 id INTEGER NOT NULL AUTO_INCREMENT,
26 Name varchar(200) NOT NULL ,
27 Description varchar(255) NULL ,
28 CorrespondAddress varchar(120) NULL ,
29 CommentAddress varchar(120) NULL ,
30 InitialPriority integer NOT NULL DEFAULT 0 ,
31 FinalPriority integer NOT NULL DEFAULT 0 ,
32 DefaultDueIn integer NOT NULL DEFAULT 0 ,
33 Creator integer NOT NULL DEFAULT 0 ,
34 Created DATETIME NULL ,
35 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
36 LastUpdated DATETIME NULL ,
37 Disabled int2 NOT NULL DEFAULT 0 ,
40 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
41 CREATE INDEX Queues2 ON Queues (Disabled) ;
48 id INTEGER NOT NULL AUTO_INCREMENT,
49 Base varchar(240) NULL ,
50 Target varchar(240) NULL ,
51 Type varchar(20) NOT NULL ,
52 LocalTarget integer NOT NULL DEFAULT 0 ,
53 LocalBase integer NOT NULL DEFAULT 0 ,
54 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
55 LastUpdated DATETIME NULL ,
56 Creator integer NOT NULL DEFAULT 0 ,
57 Created DATETIME NULL ,
61 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
62 CREATE INDEX Links2 ON Links (Base, Type) ;
63 CREATE INDEX Links3 ON Links (Target, Type) ;
64 CREATE INDEX Links4 ON Links (Type,LocalBase);
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);
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 ObjectType varchar(64) NOT NULL,
122 ObjectId 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 ReferenceType varchar(255) NULL,
129 OldReference integer NULL ,
130 NewReference integer NULL ,
131 Data varchar(255) NULL ,
133 Creator integer NOT NULL DEFAULT 0 ,
134 Created DATETIME NULL ,
137 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
143 CREATE TABLE Scrips (
144 id INTEGER NOT NULL AUTO_INCREMENT,
145 Description varchar(255),
146 ScripCondition integer NOT NULL DEFAULT 0 ,
147 ScripAction integer NOT NULL DEFAULT 0 ,
148 ConditionRules text NULL ,
149 ActionRules text NULL ,
150 CustomIsApplicableCode text NULL ,
151 CustomPrepareCode text NULL ,
152 CustomCommitCode text NULL ,
153 Stage varchar(32) NULL ,
154 Queue integer NOT NULL DEFAULT 0 ,
155 Template integer NOT NULL DEFAULT 0 ,
156 Creator integer NOT NULL DEFAULT 0 ,
157 Created DATETIME NULL ,
158 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
159 LastUpdated DATETIME NULL ,
167 id INTEGER NOT NULL AUTO_INCREMENT,
168 PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor"
170 PrincipalId integer NOT NULL , #Foreign key to principals
171 RightName varchar(25) NOT NULL ,
172 ObjectType varchar(25) NOT NULL ,
173 ObjectId integer NOT NULL default 0,
174 DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
175 DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
179 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
185 CREATE TABLE GroupMembers (
186 id INTEGER NOT NULL AUTO_INCREMENT,
187 GroupId integer NOT NULL DEFAULT 0,
188 MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals
191 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
196 # {{{ GroupMembersCache
198 create table CachedGroupMembers (
199 id int auto_increment,
200 GroupId int, # foreign key to Principals
201 MemberId int, # foreign key to Principals
202 Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
203 ImmediateParentId int, #foreign key to prinicpals.
204 # this points to the group that the member is
205 # a member of, for ease of deletes.
206 Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
207 # group or this group is disabled, this will be set to 1
208 # this allows us to not find members of disabled subgroups when listing off
209 # group members recursively.
210 # Also, this allows us to have the ACL system elide members of disabled groups
214 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
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 Users4 ON Users (EmailAddress);
268 CREATE TABLE Tickets (
269 id INTEGER NOT NULL AUTO_INCREMENT,
270 EffectiveId integer NOT NULL DEFAULT 0 ,
271 Queue integer NOT NULL DEFAULT 0 ,
272 Type varchar(16) NULL ,
273 IssueStatement integer NOT NULL DEFAULT 0 ,
274 Resolution integer NOT NULL DEFAULT 0 ,
275 Owner integer NOT NULL DEFAULT 0 ,
276 Subject varchar(200) NULL DEFAULT '[no subject]' ,
277 InitialPriority integer NOT NULL DEFAULT 0 ,
278 FinalPriority integer NOT NULL DEFAULT 0 ,
279 Priority integer NOT NULL DEFAULT 0 ,
280 TimeEstimated integer NOT NULL DEFAULT 0 ,
281 TimeWorked integer NOT NULL DEFAULT 0 ,
282 Status varchar(10) NULL ,
283 TimeLeft integer NOT NULL DEFAULT 0 ,
285 Starts DATETIME NULL ,
286 Started DATETIME NULL ,
288 Resolved DATETIME NULL ,
291 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
292 LastUpdated DATETIME NULL ,
293 Creator integer NOT NULL DEFAULT 0 ,
294 Created DATETIME NULL ,
295 Disabled int2 NOT NULL DEFAULT 0 ,
299 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
300 CREATE INDEX Tickets2 ON Tickets (Owner) ;
301 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
307 CREATE TABLE ScripActions (
308 id INTEGER NOT NULL AUTO_INCREMENT,
309 Name varchar(200) NULL ,
310 Description varchar(255) NULL ,
311 ExecModule varchar(60) NULL ,
312 Argument varchar(255) NULL ,
313 Creator integer NOT NULL DEFAULT 0 ,
314 Created DATETIME NULL ,
315 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
316 LastUpdated DATETIME NULL ,
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) NULL ,
330 Language varchar(16) NULL ,
331 TranslationOf integer NOT NULL DEFAULT 0 ,
333 LastUpdated DATETIME NULL ,
334 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
335 Creator integer NOT NULL DEFAULT 0 ,
336 Created DATETIME NULL ,
342 # {{{ ObjectCustomFieldValues
344 CREATE TABLE ObjectCustomFieldValues (
345 id INTEGER NOT NULL AUTO_INCREMENT,
346 CustomField int NOT NULL ,
347 ObjectType varchar(255) NOT NULL, # Final target of the Object
348 ObjectId int NOT NULL , # New -- Replaces Ticket
349 SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values
351 Content varchar(255) NULL ,
352 LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
353 ContentType varchar(80) NULL, # New -- only text/* gets searched
354 ContentEncoding varchar(80) NULL , # New -- for binary Content
356 Creator integer NOT NULL DEFAULT 0 ,
357 Created DATETIME NULL ,
358 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
359 LastUpdated DATETIME NULL ,
360 Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current
364 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
365 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
371 CREATE TABLE CustomFields (
372 id INTEGER NOT NULL AUTO_INCREMENT,
373 Name varchar(200) NULL ,
374 Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
375 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
376 Pattern varchar(255) NULL , # New -- Must validate against this
377 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
378 Description varchar(255) NULL ,
379 SortOrder integer NOT NULL DEFAULT 0 ,
380 LookupType varchar(255) NOT NULL,
382 Creator integer NOT NULL DEFAULT 0 ,
383 Created DATETIME NULL ,
384 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
385 LastUpdated DATETIME NULL ,
386 Disabled int2 NOT NULL DEFAULT 0 ,
392 # {{{ ObjectCustomFields
394 CREATE TABLE ObjectCustomFields (
395 id INTEGER NOT NULL AUTO_INCREMENT,
396 CustomField int NOT NULL ,
397 ObjectId integer NOT NULL,
398 SortOrder integer NOT NULL DEFAULT 0 ,
400 Creator integer NOT NULL DEFAULT 0 ,
401 Created DATETIME NULL ,
402 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
403 LastUpdated DATETIME NULL ,
409 # {{{ CustomFieldValues
411 CREATE TABLE CustomFieldValues (
412 id INTEGER NOT NULL AUTO_INCREMENT,
413 CustomField int NOT NULL ,
414 Name varchar(200) NULL ,
415 Description varchar(255) NULL ,
416 SortOrder integer NOT NULL DEFAULT 0 ,
418 Creator integer NOT NULL DEFAULT 0 ,
419 Created DATETIME NULL ,
420 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
421 LastUpdated DATETIME NULL ,
425 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
432 CREATE TABLE Attributes (
433 id INTEGER NOT NULL AUTO_INCREMENT,
434 Name varchar(255) NULL ,
435 Description varchar(255) NULL ,
437 ContentType varchar(16),
438 ObjectType varchar(64),
439 ObjectId integer, # foreign key to anything
440 Creator integer NOT NULL DEFAULT 0 ,
441 Created DATETIME NULL ,
442 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
443 LastUpdated DATETIME NULL ,
447 CREATE INDEX Attributes1 on Attributes(Name);
448 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
454 # sessions is used by Apache::Session to keep sessions in the database.
455 # We should have a reaper script somewhere.
457 CREATE TABLE sessions (
458 id char(32) NOT NULL,
460 LastUpdated TIMESTAMP,