]>
Commit | Line | Data |
---|---|---|
84fb5b46 MKG |
1 | --- {{{ Attachments |
2 | ||
3 | CREATE TABLE Attachments ( | |
4 | id INTEGER PRIMARY KEY , | |
5 | TransactionId INTEGER , | |
6 | Parent integer 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 , | |
12 | Content LONGTEXT NULL , | |
13 | Headers LONGTEXT NULL , | |
14 | Creator integer NULL , | |
15 | Created DATETIME NULL | |
16 | ||
17 | ) ; | |
18 | ||
19 | CREATE INDEX Attachments1 ON Attachments (Parent) ; | |
20 | CREATE INDEX Attachments2 ON Attachments (TransactionId) ; | |
21 | CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; | |
22 | --- }}} | |
23 | ||
24 | --- {{{ Queues | |
25 | CREATE TABLE Queues ( | |
26 | id INTEGER PRIMARY KEY , | |
27 | Name varchar(200) NOT NULL , | |
28 | Description varchar(255) NULL , | |
29 | CorrespondAddress varchar(120) NULL , | |
30 | CommentAddress varchar(120) NULL , | |
31 | Lifecycle varchar(32) NULL , | |
32 | SubjectTag varchar(120) NULL , | |
33 | InitialPriority integer NULL , | |
34 | FinalPriority integer NULL , | |
35 | DefaultDueIn integer NULL , | |
36 | Creator integer NULL , | |
37 | Created DATETIME NULL , | |
38 | LastUpdatedBy integer NULL , | |
39 | LastUpdated DATETIME NULL , | |
40 | Disabled int2 NOT NULL DEFAULT 0 | |
41 | ||
42 | ) ; | |
43 | CREATE UNIQUE INDEX Queues1 ON Queues (Name) ; | |
44 | ||
45 | --- }}} | |
46 | ||
47 | --- {{{ Links | |
48 | ||
49 | CREATE TABLE Links ( | |
50 | id INTEGER PRIMARY KEY , | |
51 | Base varchar(240) NULL , | |
52 | Target varchar(240) NULL , | |
53 | Type varchar(20) NOT NULL , | |
54 | LocalTarget integer NULL , | |
55 | LocalBase integer NULL , | |
56 | LastUpdatedBy integer NULL , | |
57 | LastUpdated DATETIME NULL , | |
58 | Creator integer NULL , | |
59 | Created DATETIME NULL | |
60 | ||
61 | ) ; | |
62 | CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; | |
63 | CREATE INDEX Links4 ON Links(Type,LocalBase); | |
64 | ||
65 | --- }}} | |
66 | ||
67 | --- {{{ Principals | |
68 | ||
69 | CREATE TABLE Principals ( | |
70 | id INTEGER PRIMARY KEY, | |
71 | PrincipalType VARCHAR(16) not null, | |
72 | ObjectId integer, | |
73 | Disabled int2 NOT NULL DEFAULT 0 | |
74 | ||
75 | ) ; | |
76 | ||
77 | --- }}} | |
78 | ||
79 | --- {{{ Groups | |
80 | ||
81 | CREATE TABLE Groups ( | |
82 | id INTEGER , | |
83 | Name varchar(200) NULL , | |
84 | Description varchar(255) NULL , | |
85 | Domain varchar(64), | |
86 | Type varchar(64), | |
87 | Instance integer, | |
88 | Creator integer NOT NULL DEFAULT 0 , | |
89 | Created DATETIME NULL , | |
90 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
91 | LastUpdated DATETIME NULL | |
92 | ||
93 | ) ; | |
94 | ||
95 | CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ; | |
96 | ||
97 | --- }}} | |
98 | ||
99 | --- {{{ ScripConditions | |
100 | ||
101 | CREATE TABLE ScripConditions ( | |
102 | id INTEGER PRIMARY KEY , | |
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 , | |
108 | ||
109 | Creator integer NULL , | |
110 | Created DATETIME NULL , | |
111 | LastUpdatedBy integer NULL , | |
112 | LastUpdated DATETIME NULL | |
113 | ||
114 | ) ; | |
115 | ||
116 | --- }}} | |
117 | ||
118 | --- {{{ Transactions | |
119 | CREATE TABLE Transactions ( | |
120 | id INTEGER PRIMARY KEY , | |
121 | ObjectType varchar(255) NULL , | |
122 | ObjectId integer NULL , | |
123 | TimeTaken integer NULL , | |
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 , | |
132 | ||
133 | Creator integer NULL , | |
134 | Created DATETIME NULL | |
135 | ||
136 | ) ; | |
137 | CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); | |
138 | ||
139 | --- }}} | |
140 | ||
141 | --- {{{ Scrips | |
142 | ||
143 | CREATE TABLE Scrips ( | |
144 | id INTEGER PRIMARY KEY , | |
145 | Description varchar(255), | |
146 | ScripCondition integer NULL , | |
147 | ScripAction integer NULL , | |
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 NULL , | |
155 | Template integer NULL , | |
156 | Creator integer NULL , | |
157 | Created DATETIME NULL , | |
158 | LastUpdatedBy integer NULL , | |
159 | LastUpdated DATETIME NULL | |
160 | ||
161 | ) ; | |
162 | ||
163 | --- }}} | |
164 | ||
165 | --- {{{ ACL | |
166 | CREATE TABLE ACL ( | |
167 | id INTEGER PRIMARY KEY , | |
168 | PrincipalType varchar(25) NOT NULL, | |
169 | ||
170 | PrincipalId INTEGER, | |
171 | RightName varchar(25) NOT NULL , | |
172 | ObjectType varchar(25) NOT NULL , | |
173 | ObjectId INTEGER default 0, | |
174 | Creator integer NOT NULL DEFAULT 0 , | |
175 | Created DATETIME NULL , | |
176 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
177 | LastUpdated DATETIME NULL | |
178 | ||
179 | ) ; | |
180 | ||
181 | ||
182 | --- }}} | |
183 | ||
184 | --- {{{ GroupMembers | |
185 | ||
186 | CREATE TABLE GroupMembers ( | |
187 | id INTEGER PRIMARY KEY , | |
188 | GroupId integer NULL, | |
189 | MemberId integer NULL, | |
190 | Creator integer NOT NULL DEFAULT 0 , | |
191 | Created DATETIME NULL , | |
192 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
193 | LastUpdated DATETIME NULL | |
194 | ||
195 | ) ; | |
196 | ||
197 | --- }}} | |
198 | ||
199 | --- {{{ CachedGroupMembers | |
200 | ||
201 | create table CachedGroupMembers ( | |
202 | id integer primary key , | |
203 | GroupId int, | |
204 | MemberId int, | |
205 | Via int, | |
206 | ImmediateParentId int, | |
207 | Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled | |
208 | # group or this group is disabled, this will be set to 1 | |
209 | # this allows us to not find members of disabled subgroups when listing off | |
210 | # group members recursively. | |
211 | # Also, this allows us to have the ACL system elide members of disabled groups | |
212 | ||
213 | ||
214 | ) ; | |
215 | ||
216 | --- }}} | |
217 | ||
218 | --- {{{ Users | |
219 | ||
220 | CREATE TABLE Users ( | |
221 | id INTEGER , | |
222 | Name varchar(200) NOT NULL , | |
223 | Password varchar(256) NULL , | |
224 | AuthToken varchar(16) NULL , | |
225 | Comments blob NULL , | |
226 | Signature blob NULL , | |
227 | EmailAddress varchar(120) NULL , | |
228 | FreeformContactInfo blob NULL , | |
229 | Organization varchar(200) NULL , | |
230 | RealName varchar(120) NULL , | |
231 | NickName varchar(16) NULL , | |
232 | Lang varchar(16) NULL , | |
233 | EmailEncoding varchar(16) NULL , | |
234 | WebEncoding varchar(16) NULL , | |
235 | ExternalContactInfoId varchar(100) NULL , | |
236 | ContactInfoSystem varchar(30) NULL , | |
237 | ExternalAuthId varchar(100) NULL , | |
238 | AuthSystem varchar(30) NULL , | |
239 | Gecos varchar(16) NULL , | |
240 | HomePhone varchar(30) NULL , | |
241 | WorkPhone varchar(30) NULL , | |
242 | MobilePhone varchar(30) NULL , | |
243 | PagerPhone varchar(30) NULL , | |
244 | Address1 varchar(200) NULL , | |
245 | Address2 varchar(200) NULL , | |
246 | City varchar(100) NULL , | |
247 | State varchar(100) NULL , | |
248 | Zip varchar(16) NULL , | |
249 | Country varchar(50) NULL , | |
250 | Timezone char(50) NULL , | |
251 | PGPKey text NULL, | |
252 | ||
253 | Creator integer NULL , | |
254 | Created DATETIME NULL , | |
255 | LastUpdatedBy integer NULL , | |
256 | LastUpdated DATETIME NULL | |
257 | ||
258 | ) ; | |
259 | ||
260 | ||
261 | CREATE UNIQUE INDEX Users1 ON Users (Name) ; | |
262 | CREATE INDEX Users2 ON Users (Name); | |
263 | CREATE INDEX Users3 ON Users (id, EmailAddress); | |
264 | CREATE INDEX Users4 ON Users (EmailAddress); | |
265 | ||
266 | ||
267 | --- }}} | |
268 | ||
269 | --- {{{ Tickets | |
270 | ||
271 | CREATE TABLE Tickets ( | |
272 | id INTEGER PRIMARY KEY , | |
273 | EffectiveId integer NULL , | |
274 | Queue integer NULL , | |
275 | Type varchar(16) NULL , | |
276 | IssueStatement integer NULL , | |
277 | Resolution integer NULL , | |
278 | Owner integer NULL , | |
279 | Subject varchar(200) NULL DEFAULT '[no subject]' , | |
280 | InitialPriority integer NULL , | |
281 | FinalPriority integer NULL , | |
282 | Priority integer NULL , | |
283 | TimeEstimated integer NULL , | |
284 | TimeWorked integer NULL , | |
285 | Status varchar(64) NULL , | |
286 | TimeLeft integer NULL , | |
287 | Told DATETIME NULL , | |
288 | Starts DATETIME NULL , | |
289 | Started DATETIME NULL , | |
290 | Due DATETIME NULL , | |
291 | Resolved DATETIME NULL , | |
292 | ||
293 | ||
294 | LastUpdatedBy integer NULL , | |
295 | LastUpdated DATETIME NULL , | |
296 | Creator integer NULL , | |
297 | Created DATETIME NULL , | |
298 | Disabled int2 NOT NULL DEFAULT 0 | |
299 | ||
300 | ) ; | |
301 | ||
302 | CREATE INDEX Tickets1 ON Tickets (Queue, Status) ; | |
303 | CREATE INDEX Tickets2 ON Tickets (Owner) ; | |
304 | CREATE INDEX Tickets3 ON Tickets (EffectiveId) ; | |
305 | CREATE INDEX Tickets4 ON Tickets (id, Status) ; | |
306 | CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ; | |
307 | ||
308 | --- }}} | |
309 | ||
310 | --- {{{ ScripActions | |
311 | ||
312 | CREATE TABLE ScripActions ( | |
313 | id INTEGER PRIMARY KEY , | |
314 | Name varchar(200) NULL , | |
315 | Description varchar(255) NULL , | |
316 | ExecModule varchar(60) NULL , | |
317 | Argument varchar(255) NULL , | |
318 | Creator integer NULL , | |
319 | Created DATETIME NULL , | |
320 | LastUpdatedBy integer NULL , | |
321 | LastUpdated DATETIME NULL | |
322 | ||
323 | ) ; | |
324 | ||
325 | --- }}} | |
326 | ||
327 | --- {{{ Templates | |
328 | ||
329 | CREATE TABLE Templates ( | |
330 | id INTEGER PRIMARY KEY , | |
331 | Queue integer NOT NULL DEFAULT 0 , | |
332 | Name varchar(200) NOT NULL , | |
333 | Description varchar(255) NULL , | |
334 | Type varchar(16) NULL , | |
335 | Language varchar(16) NULL , | |
336 | TranslationOf integer NULL , | |
337 | Content blob NULL , | |
338 | LastUpdated DATETIME NULL , | |
339 | LastUpdatedBy integer NULL , | |
340 | Creator integer NULL , | |
341 | Created DATETIME NULL | |
342 | ||
343 | ) ; | |
344 | ||
345 | --- }}} | |
346 | ||
347 | ||
348 | ||
349 | ||
350 | CREATE TABLE ObjectCustomFieldValues ( | |
351 | id INTEGER NOT NULL , | |
352 | CustomField int NOT NULL , | |
353 | ObjectType varchar(255) NOT NULL, # Final target of the Object | |
354 | ObjectId int NOT NULL , # New -- Replaces Ticket | |
355 | SortOrder integer NOT NULL DEFAULT 0 , | |
356 | ||
357 | Content varchar(255) NULL , | |
358 | LargeContent LONGTEXT NULL, # New -- to hold 255+ strings | |
359 | ContentType varchar(80) NULL, # New -- only text/* gets searched | |
360 | ContentEncoding varchar(80) NULL , # New -- for binary Content | |
361 | ||
362 | Creator integer NOT NULL DEFAULT 0 , | |
363 | Created DATETIME NULL , | |
364 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
365 | LastUpdated DATETIME NULL , | |
366 | Disabled int2 NOT NULL DEFAULT 0 , | |
367 | PRIMARY KEY (id) | |
368 | ) ; | |
369 | ||
370 | CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); | |
371 | CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); | |
372 | ||
373 | ||
374 | ||
375 | CREATE TABLE CustomFields ( | |
376 | id INTEGER NOT NULL , | |
377 | Name varchar(200) NULL , | |
378 | Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out | |
379 | RenderType varchar(64) NULL , | |
380 | MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0) | |
381 | Pattern varchar(65536) NULL , # New -- Must validate against this | |
382 | Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry | |
383 | BasedOn INTEGER NULL, | |
384 | ValuesClass varchar(64) NULL , | |
385 | Description varchar(255) NULL , | |
386 | SortOrder integer NOT NULL DEFAULT 0 , | |
387 | LookupType varchar(255) NOT NULL, | |
388 | ||
389 | Creator integer NOT NULL DEFAULT 0 , | |
390 | Created DATETIME NULL , | |
391 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
392 | LastUpdated DATETIME NULL , | |
393 | Disabled int2 NOT NULL DEFAULT 0 , | |
394 | PRIMARY KEY (id) | |
395 | ) ; | |
396 | ||
397 | ||
398 | ||
399 | CREATE TABLE ObjectCustomFields ( | |
400 | id INTEGER NOT NULL , | |
401 | CustomField int NOT NULL , | |
402 | ObjectId integer NOT NULL, | |
403 | SortOrder integer NOT NULL DEFAULT 0 , | |
404 | ||
405 | Creator integer NOT NULL DEFAULT 0 , | |
406 | Created DATETIME NULL , | |
407 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
408 | LastUpdated DATETIME NULL , | |
409 | PRIMARY KEY (id) | |
410 | ) ; | |
411 | ||
412 | ||
413 | ||
414 | CREATE TABLE CustomFieldValues ( | |
415 | id INTEGER NOT NULL , | |
416 | CustomField int NOT NULL , | |
417 | Name varchar(200) NULL , | |
418 | Description varchar(255) NULL , | |
419 | SortOrder integer NOT NULL DEFAULT 0 , | |
420 | Category varchar(255) NULL , | |
421 | ||
422 | Creator integer NOT NULL DEFAULT 0 , | |
423 | Created DATETIME NULL , | |
424 | LastUpdatedBy integer NOT NULL DEFAULT 0 , | |
425 | LastUpdated DATETIME NULL , | |
426 | PRIMARY KEY (id) | |
427 | ) ; | |
428 | ||
429 | CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); | |
430 | ||
431 | ||
432 | --- {{{ Attributes | |
433 | CREATE TABLE Attributes ( | |
434 | id INTEGER PRIMARY KEY , | |
435 | Name varchar(255) NOT NULL , | |
436 | Description varchar(255) NULL , | |
437 | Content LONGTEXT NULL , | |
438 | ContentType varchar(16), | |
439 | ObjectType varchar(25) NOT NULL , | |
440 | ObjectId INTEGER default 0, | |
441 | Creator integer NULL , | |
442 | Created DATETIME NULL , | |
443 | LastUpdatedBy integer NULL , | |
444 | LastUpdated DATETIME NULL | |
445 | ||
446 | ) ; | |
447 | CREATE INDEX Attributes1 on Attributes(Name); | |
448 | CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId); | |
449 | ||
450 | --- }}} | |
451 | ||
452 | CREATE TABLE Classes ( | |
453 | id INTEGER PRIMARY KEY, | |
454 | Name varchar(255) NOT NULL DEFAULT '', | |
455 | Description varchar(255) NOT NULL DEFAULT '', | |
456 | SortOrder integer NOT NULL DEFAULT 0, | |
457 | Disabled smallint NOT NULL DEFAULT 0, | |
458 | Creator integer NOT NULL DEFAULT 0, | |
459 | Created TIMESTAMP NULL, | |
460 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
461 | LastUpdated TIMESTAMP NULL, | |
462 | HotList smallint NOT NULL DEFAULT 0 | |
463 | ); | |
464 | ||
465 | CREATE TABLE Articles ( | |
466 | id INTEGER PRIMARY KEY, | |
467 | Name varchar(255) NOT NULL DEFAULT '', | |
468 | Summary varchar(255) NOT NULL DEFAULT '', | |
469 | SortOrder integer NOT NULL DEFAULT 0, | |
470 | Class integer NOT NULL DEFAULT 0, | |
471 | Parent integer NOT NULL DEFAULT 0, | |
472 | URI varchar(255), | |
473 | Creator integer NOT NULL DEFAULT 0, | |
474 | Created TIMESTAMP NULL, | |
475 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
476 | LastUpdated TIMESTAMP NULL | |
477 | ); | |
478 | ||
479 | ||
480 | CREATE TABLE Topics ( | |
481 | id INTEGER PRIMARY KEY, | |
482 | Parent integer NOT NULL DEFAULT 0, | |
483 | Name varchar(255) NOT NULL DEFAULT '', | |
484 | Description varchar(255) NOT NULL DEFAULT '', | |
485 | ObjectType varchar(64) NOT NULL DEFAULT '', | |
486 | ObjectId integer NOT NULL | |
487 | ); | |
488 | ||
489 | ||
490 | CREATE TABLE ObjectTopics ( | |
491 | id INTEGER PRIMARY KEY, | |
492 | Topic integer NOT NULL, | |
493 | ObjectType varchar(64) NOT NULL DEFAULT '', | |
494 | ObjectId integer NOT NULL | |
495 | ); | |
496 | ||
497 | CREATE TABLE ObjectClasses ( | |
498 | id INTEGER PRIMARY KEY, | |
499 | Class integer NOT NULL, | |
500 | ObjectType varchar(64) NOT NULL DEFAULT '', | |
501 | ObjectId integer NOT NULL, | |
502 | Creator integer NOT NULL DEFAULT 0, | |
503 | Created TIMESTAMP NULL, | |
504 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
505 | LastUpdated TIMESTAMP NULL | |
506 | ); |