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);
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 Users4 ON Users (EmailAddress);
267 CREATE TABLE Tickets (
268 id INTEGER NOT NULL AUTO_INCREMENT,
269 EffectiveId integer NOT NULL DEFAULT 0 ,
270 Queue integer NOT NULL DEFAULT 0 ,
271 Type varchar(16) NULL ,
272 IssueStatement integer NOT NULL DEFAULT 0 ,
273 Resolution integer NOT NULL DEFAULT 0 ,
274 Owner integer NOT NULL DEFAULT 0 ,
275 Subject varchar(200) NULL DEFAULT '[no subject]' ,
276 InitialPriority integer NOT NULL DEFAULT 0 ,
277 FinalPriority integer NOT NULL DEFAULT 0 ,
278 Priority integer NOT NULL DEFAULT 0 ,
279 TimeEstimated integer NOT NULL DEFAULT 0 ,
280 TimeWorked integer NOT NULL DEFAULT 0 ,
281 Status varchar(10) NULL ,
282 TimeLeft integer NOT NULL DEFAULT 0 ,
284 Starts DATETIME NULL ,
285 Started DATETIME NULL ,
287 Resolved DATETIME NULL ,
290 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
291 LastUpdated DATETIME NULL ,
292 Creator integer NOT NULL DEFAULT 0 ,
293 Created DATETIME NULL ,
294 Disabled int2 NOT NULL DEFAULT 0 ,
298 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
299 CREATE INDEX Tickets2 ON Tickets (Owner) ;
300 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
306 CREATE TABLE ScripActions (
307 id INTEGER NOT NULL AUTO_INCREMENT,
308 Name varchar(200) NULL ,
309 Description varchar(255) NULL ,
310 ExecModule varchar(60) NULL ,
311 Argument varchar(255) NULL ,
312 Creator integer NOT NULL DEFAULT 0 ,
313 Created DATETIME NULL ,
314 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
315 LastUpdated DATETIME NULL ,
323 CREATE TABLE Templates (
324 id INTEGER NOT NULL AUTO_INCREMENT,
325 Queue integer NOT NULL DEFAULT 0 ,
326 Name varchar(200) NOT NULL ,
327 Description varchar(255) NULL ,
328 Type varchar(16) NULL ,
329 Language varchar(16) NULL ,
330 TranslationOf integer NOT NULL DEFAULT 0 ,
332 LastUpdated DATETIME NULL ,
333 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
334 Creator integer NOT NULL DEFAULT 0 ,
335 Created DATETIME NULL ,
341 # {{{ ObjectCustomFieldValues
343 CREATE TABLE ObjectCustomFieldValues (
344 id INTEGER NOT NULL AUTO_INCREMENT,
345 CustomField int NOT NULL ,
346 ObjectType varchar(255) NOT NULL, # Final target of the Object
347 ObjectId int NOT NULL , # New -- Replaces Ticket
348 SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values
350 Content varchar(255) NULL ,
351 LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
352 ContentType varchar(80) NULL, # New -- only text/* gets searched
353 ContentEncoding varchar(80) NULL , # New -- for binary Content
355 Creator integer NOT NULL DEFAULT 0 ,
356 Created DATETIME NULL ,
357 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
358 LastUpdated DATETIME NULL ,
359 Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current
363 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
364 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
370 CREATE TABLE CustomFields (
371 id INTEGER NOT NULL AUTO_INCREMENT,
372 Name varchar(200) NULL ,
373 Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
374 MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
375 Pattern varchar(255) NULL , # New -- Must validate against this
376 Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
377 Description varchar(255) NULL ,
378 SortOrder integer NOT NULL DEFAULT 0 ,
379 LookupType varchar(255) NOT NULL,
381 Creator integer NOT NULL DEFAULT 0 ,
382 Created DATETIME NULL ,
383 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
384 LastUpdated DATETIME NULL ,
385 Disabled int2 NOT NULL DEFAULT 0 ,
391 # {{{ ObjectCustomFields
393 CREATE TABLE ObjectCustomFields (
394 id INTEGER NOT NULL AUTO_INCREMENT,
395 CustomField int NOT NULL ,
396 ObjectId integer NOT 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 ,
408 # {{{ CustomFieldValues
410 CREATE TABLE CustomFieldValues (
411 id INTEGER NOT NULL AUTO_INCREMENT,
412 CustomField int NOT NULL ,
413 Name varchar(200) NULL ,
414 Description varchar(255) NULL ,
415 SortOrder integer NOT NULL DEFAULT 0 ,
417 Creator integer NOT NULL DEFAULT 0 ,
418 Created DATETIME NULL ,
419 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
420 LastUpdated DATETIME NULL ,
424 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
431 CREATE TABLE Attributes (
432 id INTEGER NOT NULL AUTO_INCREMENT,
433 Name varchar(255) NULL ,
434 Description varchar(255) NULL ,
436 ContentType varchar(16),
437 ObjectType varchar(64),
438 ObjectId integer, # foreign key to anything
439 Creator integer NOT NULL DEFAULT 0 ,
440 Created DATETIME NULL ,
441 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
442 LastUpdated DATETIME NULL ,
446 CREATE INDEX Attributes1 on Attributes(Name);
447 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
453 # sessions is used by Apache::Session to keep sessions in the database.
454 # We should have a reaper script somewhere.
456 CREATE TABLE sessions (
457 id char(32) NOT NULL,
459 LastUpdated TIMESTAMP,