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 INDEX Links2 ON Links (Base, Type) ;
62 CREATE INDEX Links3 ON Links (Target, Type) ;
63 CREATE INDEX Links4 ON Links (Type,LocalBase);
69 CREATE TABLE Principals (
70 id INTEGER AUTO_INCREMENT not null,
71 PrincipalType VARCHAR(16) not null,
72 ObjectId integer, # foreign key to Users or Groups, depending
73 Disabled int2 NOT NULL DEFAULT 0 ,
77 CREATE INDEX Principals2 ON Principals (ObjectId);
84 id INTEGER NOT NULL AUTO_INCREMENT,
85 Name varchar(200) NULL ,
86 Description varchar(255) NULL ,
93 CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
94 CREATE INDEX Groups2 On Groups (Type, Instance);
100 CREATE TABLE ScripConditions (
101 id INTEGER NOT NULL AUTO_INCREMENT,
102 Name varchar(200) NULL ,
103 Description varchar(255) NULL ,
104 ExecModule varchar(60) NULL ,
105 Argument varchar(255) NULL ,
106 ApplicableTransTypes varchar(60) NULL ,
108 Creator integer NOT NULL DEFAULT 0 ,
109 Created DATETIME NULL ,
110 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
111 LastUpdated DATETIME NULL ,
118 CREATE TABLE Transactions (
119 id INTEGER NOT NULL AUTO_INCREMENT,
120 ObjectType varchar(64) NOT NULL,
121 ObjectId integer NOT NULL DEFAULT 0 ,
122 TimeTaken integer NOT NULL DEFAULT 0 ,
123 Type varchar(20) NULL ,
124 Field varchar(40) NULL ,
125 OldValue varchar(255) NULL ,
126 NewValue varchar(255) NULL ,
127 ReferenceType varchar(255) NULL,
128 OldReference integer NULL ,
129 NewReference integer NULL ,
130 Data varchar(255) NULL ,
132 Creator integer NOT NULL DEFAULT 0 ,
133 Created DATETIME NULL ,
136 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
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 CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
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 TEXT 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,