3 CREATE TABLE rt3.Attachments (
4 id numeric(38,0) identity,
5 TransactionId integer NOT NULL ,
6 Parent integer NOT NULL ,
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 ,
14 Creator integer NOT NULL ,
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) ;
25 CREATE TABLE rt3.Queues (
26 id numeric(38,0) identity,
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 ,
32 FinalPriority integer NOT NULL ,
33 DefaultDueIn integer NOT NULL ,
34 Creator integer NOT NULL ,
35 Created DATETIME NULL ,
36 LastUpdatedBy integer NOT NULL ,
37 LastUpdated DATETIME NULL ,
38 Disabled numeric(1) NOT NULL ,
41 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
42 CREATE INDEX Queues2 ON Queues (Disabled) ;
48 CREATE TABLE rt3.Links (
49 id numeric(38,0) identity,
50 Base varchar(240) NULL ,
51 Target varchar(240) NULL ,
52 Type varchar(20) NOT NULL ,
53 LocalTarget integer NOT NULL ,
54 LocalBase integer NOT NULL ,
55 LastUpdatedBy integer NOT NULL ,
56 LastUpdated DATETIME NULL ,
57 Creator integer NOT NULL ,
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) ;
65 CREATE INDEX Links4 ON Links(Type,LocalBase);
71 CREATE TABLE rt3.Principals (
72 id numeric(38,0) identity,
73 PrincipalType VARCHAR(16) not null,
74 ObjectId integer, Disabled numeric(1) NOT NULL ,
78 CREATE INDEX Principals2 ON Principals (ObjectId);
84 CREATE TABLE rt3.Groups (
85 id numeric(38,0) identity,
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 rt3.ScripConditions (
102 id numeric(38,0) identity,
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 ,
110 Created DATETIME NULL ,
111 LastUpdatedBy integer NOT NULL ,
112 LastUpdated DATETIME NULL ,
119 CREATE TABLE rt3.Transactions (
120 id numeric(38,0) identity,
121 EffectiveTicket integer NOT NULL ,
122 Ticket integer NOT NULL ,
123 TimeTaken integer NOT NULL ,
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 ,
131 Created DATETIME NULL ,
134 CREATE INDEX Transactions1 ON Transactions (Ticket);
135 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
141 CREATE TABLE rt3.Scrips (
142 id numeric(38,0) identity,
143 Description varchar(255),
144 ScripCondition integer NOT NULL ,
145 ScripAction integer NOT NULL ,
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 ,
153 Template integer NOT NULL ,
154 Creator integer NOT NULL ,
155 Created DATETIME NULL ,
156 LastUpdatedBy integer NOT NULL ,
157 LastUpdated DATETIME NULL ,
164 CREATE TABLE rt3.ACL (
165 id numeric(38,0) identity,
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 ,
172 DelegatedBy integer NOT NULL , #foreign key to principals with a userid
173 DelegatedFrom integer NOT NULL , #foreign key to ACL
177 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
183 CREATE TABLE rt3.GroupMembers (
184 id numeric(38,0) identity,
185 GroupId integer NOT NULL ,
186 MemberId integer NOT NULL , #Foreign key to Principals
189 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
194 # {{{ GroupMembersCache
196 CREATE TABLE rt3.CachedGroupMembers (
197 id numeric(38,0) identity,
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 numeric(1) NOT NULL , # 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);
219 CREATE TABLE rt3.Users (
220 id numeric(38,0) identity,
221 Name varchar(200) NOT NULL ,
222 Password varchar(40) NULL ,
224 Signature text NULL ,
225 EmailAddress varchar(120) NULL ,
226 FreeformContactInfo text 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 ,
252 Created DATETIME NULL ,
253 LastUpdatedBy integer NOT NULL ,
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 rt3.Tickets (
270 id numeric(38,0) identity,
271 EffectiveId integer NOT NULL ,
272 Queue integer NOT NULL ,
273 Type varchar(16) NULL ,
274 IssueStatement integer NOT NULL ,
275 Resolution integer NOT NULL ,
276 Owner integer NOT NULL ,
277 Subject varchar(200) NULL,
278 InitialPriority integer NOT NULL ,
279 FinalPriority integer NOT NULL ,
280 Priority integer NOT NULL ,
281 TimeEstimated integer NOT NULL ,
282 TimeWorked integer NOT NULL ,
283 Status varchar(10) NULL ,
284 TimeLeft integer NOT NULL ,
286 Starts DATETIME NULL ,
287 Started DATETIME NULL ,
289 Resolved DATETIME NULL ,
292 LastUpdatedBy integer NOT NULL ,
293 LastUpdated DATETIME NULL ,
294 Creator integer NOT NULL ,
295 Created DATETIME NULL ,
296 Disabled numeric(1) NOT NULL ,
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 rt3.ScripActions (
312 id numeric(38,0) identity,
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 ,
318 Created DATETIME NULL ,
319 LastUpdatedBy integer NOT NULL ,
320 LastUpdated DATETIME NULL ,
328 CREATE TABLE rt3.Templates (
329 id numeric(38,0) identity,
330 Queue integer NOT NULL ,
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 ,
337 LastUpdated DATETIME NULL ,
338 LastUpdatedBy integer NOT NULL ,
339 Creator integer NOT NULL ,
340 Created DATETIME NULL ,
346 # {{{ TicketCustomFieldValues
348 CREATE TABLE rt3.TicketCustomFieldValues (
349 id numeric(38,0) identity,
350 Ticket int NOT NULL ,
351 CustomField int NOT NULL ,
352 Content varchar(255) NULL ,
354 Creator integer NOT NULL ,
355 Created DATETIME NULL ,
356 LastUpdatedBy integer NOT NULL ,
357 LastUpdated DATETIME NULL ,
361 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
362 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
368 CREATE TABLE rt3.CustomFields (
369 id numeric(38,0) identity,
370 Name varchar(200) NULL ,
371 Type varchar(200) NULL ,
372 Queue integer NOT NULL ,
373 Description varchar(255) NULL ,
374 SortOrder integer NOT NULL ,
376 Creator integer NOT NULL ,
377 Created DATETIME NULL ,
378 LastUpdatedBy integer NOT NULL ,
379 LastUpdated DATETIME NULL ,
380 Disabled numeric(1) NOT NULL ,
384 CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
389 # {{{ CustomFieldValues
391 CREATE TABLE rt3.CustomFieldValues (
392 id numeric(38,0) identity,
393 CustomField int NOT NULL ,
394 Name varchar(200) NULL ,
395 Description varchar(255) NULL ,
396 SortOrder integer NOT NULL ,
398 Creator integer NOT NULL ,
399 Created DATETIME NULL ,
400 LastUpdatedBy integer NOT NULL ,
401 LastUpdated DATETIME NULL ,
405 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
412 CREATE TABLE rt3.Attributes (
413 id numeric(38,0) identity,
414 Name varchar(255) NULL ,
415 Description varchar(255) NULL ,
417 ContentType varchar(16),
418 ObjectType varchar(64),
419 ObjectId integer, # foreign key to anything
420 Creator integer NOT NULL ,
421 Created DATETIME NULL ,
422 LastUpdatedBy integer NOT NULL ,
423 LastUpdated DATETIME NULL ,
427 CREATE INDEX Attributes1 on Attributes(Name);
428 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
434 # sessions is used by Apache::Session to keep sessions in the database.
435 # We should have a reaper script somewhere.
437 CREATE TABLE rt3.sessions (
438 id char(32) NOT NULL,
440 LastUpdated DATETIME,