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 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(255) 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);
219 id INTEGER NOT NULL AUTO_INCREMENT,
220 Name varchar(200) NOT NULL ,
221 Password varchar(40) NULL ,
223 Signature blob NULL ,
224 EmailAddress varchar(120) NULL ,
225 FreeformContactInfo blob NULL ,
226 Organization varchar(200) NULL ,
227 RealName varchar(120) NULL ,
228 NickName varchar(16) NULL ,
229 Lang varchar(16) NULL ,
230 EmailEncoding varchar(16) NULL ,
231 WebEncoding varchar(16) NULL ,
232 ExternalContactInfoId varchar(100) NULL ,
233 ContactInfoSystem varchar(30) NULL ,
234 ExternalAuthId varchar(100) NULL ,
235 AuthSystem varchar(30) NULL ,
236 Gecos varchar(16) NULL ,
237 HomePhone varchar(30) NULL ,
238 WorkPhone varchar(30) NULL ,
239 MobilePhone varchar(30) NULL ,
240 PagerPhone varchar(30) NULL ,
241 Address1 varchar(200) NULL ,
242 Address2 varchar(200) NULL ,
243 City varchar(100) NULL ,
244 State varchar(100) NULL ,
245 Zip varchar(16) NULL ,
246 Country varchar(50) NULL ,
247 Timezone varchar(50) NULL ,
250 Creator integer NOT NULL DEFAULT 0 ,
251 Created DATETIME NULL ,
252 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
253 LastUpdated DATETIME NULL ,
258 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
259 CREATE INDEX Users4 ON Users (EmailAddress);
266 CREATE TABLE Tickets (
267 id INTEGER NOT NULL AUTO_INCREMENT,
268 EffectiveId integer NOT NULL DEFAULT 0 ,
269 Queue integer NOT NULL DEFAULT 0 ,
270 Type varchar(16) NULL ,
271 IssueStatement integer NOT NULL DEFAULT 0 ,
272 Resolution integer NOT NULL DEFAULT 0 ,
273 Owner integer NOT NULL DEFAULT 0 ,
274 Subject varchar(200) NULL DEFAULT '[no subject]' ,
275 InitialPriority integer NOT NULL DEFAULT 0 ,
276 FinalPriority integer NOT NULL DEFAULT 0 ,
277 Priority integer NOT NULL DEFAULT 0 ,
278 TimeEstimated integer NOT NULL DEFAULT 0 ,
279 TimeWorked integer NOT NULL DEFAULT 0 ,
280 Status varchar(10) NULL ,
281 TimeLeft integer NOT NULL DEFAULT 0 ,
283 Starts DATETIME NULL ,
284 Started DATETIME NULL ,
286 Resolved DATETIME NULL ,
289 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
290 LastUpdated DATETIME NULL ,
291 Creator integer NOT NULL DEFAULT 0 ,
292 Created DATETIME NULL ,
293 Disabled int2 NOT NULL DEFAULT 0 ,
297 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
298 CREATE INDEX Tickets2 ON Tickets (Owner) ;
299 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
305 CREATE TABLE ScripActions (
306 id INTEGER NOT NULL AUTO_INCREMENT,
307 Name varchar(200) NULL ,
308 Description varchar(255) NULL ,
309 ExecModule varchar(60) NULL ,
310 Argument varchar(255) NULL ,
311 Creator integer NOT NULL DEFAULT 0 ,
312 Created DATETIME NULL ,
313 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
314 LastUpdated DATETIME NULL ,
322 CREATE TABLE Templates (
323 id INTEGER NOT NULL AUTO_INCREMENT,
324 Queue integer NOT NULL DEFAULT 0 ,
325 Name varchar(200) NOT NULL ,
326 Description varchar(255) NULL ,
327 Type varchar(16) NULL ,
328 Language varchar(16) NULL ,
329 TranslationOf integer NOT NULL DEFAULT 0 ,
331 LastUpdated DATETIME NULL ,
332 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
333 Creator integer NOT NULL DEFAULT 0 ,
334 Created DATETIME NULL ,
340 # {{{ TicketCustomFieldValues
342 CREATE TABLE TicketCustomFieldValues (
343 id INTEGER NOT NULL AUTO_INCREMENT,
344 Ticket int NOT NULL ,
345 CustomField int NOT NULL ,
346 Content varchar(255) NULL ,
348 Creator integer NOT NULL DEFAULT 0 ,
349 Created DATETIME NULL ,
350 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
351 LastUpdated DATETIME NULL ,
355 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
361 CREATE TABLE CustomFields (
362 id INTEGER NOT NULL AUTO_INCREMENT,
363 Name varchar(200) NULL ,
364 Type varchar(200) NULL ,
365 Queue integer NOT NULL DEFAULT 0 ,
366 Description varchar(255) NULL ,
367 SortOrder integer NOT NULL DEFAULT 0 ,
369 Creator integer NOT NULL DEFAULT 0 ,
370 Created DATETIME NULL ,
371 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
372 LastUpdated DATETIME NULL ,
373 Disabled int2 NOT NULL DEFAULT 0 ,
377 CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
382 # {{{ CustomFieldValues
384 CREATE TABLE CustomFieldValues (
385 id INTEGER NOT NULL AUTO_INCREMENT,
386 CustomField int NOT NULL ,
387 Name varchar(200) NULL ,
388 Description varchar(255) NULL ,
389 SortOrder integer NOT NULL DEFAULT 0 ,
391 Creator integer NOT NULL DEFAULT 0 ,
392 Created DATETIME NULL ,
393 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
394 LastUpdated DATETIME NULL ,
398 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
405 CREATE TABLE Attributes (
406 id INTEGER NOT NULL AUTO_INCREMENT,
407 Name varchar(255) NULL ,
408 Description varchar(255) NULL ,
410 ContentType varchar(16),
411 ObjectType varchar(64),
412 ObjectId integer, # foreign key to anything
413 Creator integer NOT NULL DEFAULT 0 ,
414 Created DATETIME NULL ,
415 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
416 LastUpdated DATETIME NULL ,
420 CREATE INDEX Attributes1 on Attributes(Name);
421 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
427 # sessions is used by Apache::Session to keep sessions in the database.
428 # We should have a reaper script somewhere.
430 CREATE TABLE sessions (
431 id char(32) NOT NULL,
433 LastUpdated TIMESTAMP,