Upgrade to 4.0.8 with mod of ExternalAuth + absolute paths to ticket-menu.
[usit-rt.git] / etc / schema.SQLite
CommitLineData
84fb5b46
MKG
1--- {{{ Attachments
2
3CREATE TABLE Attachments (
4 id INTEGER PRIMARY KEY ,
5 TransactionId INTEGER ,
dab09ea8 6 Parent integer NULL DEFAULT 0 ,
84fb5b46
MKG
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 ,
dab09ea8 14 Creator integer NULL DEFAULT 0 ,
84fb5b46
MKG
15 Created DATETIME NULL
16
17) ;
18
19CREATE INDEX Attachments1 ON Attachments (Parent) ;
20CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
21CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
22--- }}}
23
24--- {{{ Queues
25CREATE 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 ,
dab09ea8
MKG
33 InitialPriority integer NULL DEFAULT 0 ,
34 FinalPriority integer NULL DEFAULT 0 ,
35 DefaultDueIn integer NULL DEFAULT 0 ,
36 Creator integer NULL DEFAULT 0 ,
84fb5b46 37 Created DATETIME NULL ,
dab09ea8 38 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
39 LastUpdated DATETIME NULL ,
40 Disabled int2 NOT NULL DEFAULT 0
41
42) ;
43CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
44
45--- }}}
46
47--- {{{ Links
48
49CREATE TABLE Links (
50 id INTEGER PRIMARY KEY ,
51 Base varchar(240) NULL ,
52 Target varchar(240) NULL ,
53 Type varchar(20) NOT NULL ,
dab09ea8
MKG
54 LocalTarget integer NULL DEFAULT 0 ,
55 LocalBase integer NULL DEFAULT 0 ,
56 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 57 LastUpdated DATETIME NULL ,
dab09ea8 58 Creator integer NULL DEFAULT 0 ,
84fb5b46
MKG
59 Created DATETIME NULL
60
61) ;
62CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
63CREATE INDEX Links4 ON Links(Type,LocalBase);
64
65--- }}}
66
67--- {{{ Principals
68
69CREATE 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
81CREATE 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
95CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;
96
97--- }}}
98
99--- {{{ ScripConditions
100
101CREATE 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
dab09ea8 109 Creator integer NULL DEFAULT 0 ,
84fb5b46 110 Created DATETIME NULL ,
dab09ea8 111 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
112 LastUpdated DATETIME NULL
113
114) ;
115
116--- }}}
117
118--- {{{ Transactions
119CREATE TABLE Transactions (
120 id INTEGER PRIMARY KEY ,
121 ObjectType varchar(255) NULL ,
dab09ea8
MKG
122 ObjectId integer NULL DEFAULT 0 ,
123 TimeTaken integer NULL DEFAULT 0 ,
84fb5b46
MKG
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
dab09ea8 133 Creator integer NULL DEFAULT 0 ,
84fb5b46
MKG
134 Created DATETIME NULL
135
136) ;
137CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
138
139--- }}}
140
141--- {{{ Scrips
142
143CREATE TABLE Scrips (
144 id INTEGER PRIMARY KEY ,
145 Description varchar(255),
dab09ea8
MKG
146 ScripCondition integer NULL DEFAULT 0 ,
147 ScripAction integer NULL DEFAULT 0 ,
84fb5b46
MKG
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 ,
dab09ea8
MKG
154 Queue integer NULL DEFAULT 0 ,
155 Template integer NULL DEFAULT 0 ,
156 Creator integer NULL DEFAULT 0 ,
84fb5b46 157 Created DATETIME NULL ,
dab09ea8 158 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
159 LastUpdated DATETIME NULL
160
161) ;
162
163--- }}}
164
165--- {{{ ACL
166CREATE TABLE ACL (
167 id INTEGER PRIMARY KEY ,
168 PrincipalType varchar(25) NOT NULL,
169
dab09ea8 170 PrincipalId INTEGER DEFAULT 0,
84fb5b46
MKG
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
186CREATE TABLE GroupMembers (
187 id INTEGER PRIMARY KEY ,
dab09ea8
MKG
188 GroupId integer NULL DEFAULT 0,
189 MemberId integer NULL DEFAULT 0,
84fb5b46
MKG
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
201create 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
220CREATE 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
dab09ea8 253 Creator integer NULL DEFAULT 0 ,
84fb5b46 254 Created DATETIME NULL ,
dab09ea8 255 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
256 LastUpdated DATETIME NULL
257
258) ;
259
260
261CREATE UNIQUE INDEX Users1 ON Users (Name) ;
262CREATE INDEX Users2 ON Users (Name);
263CREATE INDEX Users3 ON Users (id, EmailAddress);
264CREATE INDEX Users4 ON Users (EmailAddress);
265
266
267--- }}}
268
269--- {{{ Tickets
270
271CREATE TABLE Tickets (
272 id INTEGER PRIMARY KEY ,
dab09ea8
MKG
273 EffectiveId integer NULL DEFAULT 0 ,
274 Queue integer NULL DEFAULT 0 ,
84fb5b46 275 Type varchar(16) NULL ,
dab09ea8
MKG
276 IssueStatement integer NULL DEFAULT 0 ,
277 Resolution integer NULL DEFAULT 0 ,
278 Owner integer NULL DEFAULT 0 ,
84fb5b46 279 Subject varchar(200) NULL DEFAULT '[no subject]' ,
dab09ea8
MKG
280 InitialPriority integer NULL DEFAULT 0 ,
281 FinalPriority integer NULL DEFAULt 0 ,
282 Priority integer NULL DEFAULT 0 ,
283 TimeEstimated integer NULL DEFAULT 0 ,
284 TimeWorked integer NULL DEFAULT 0 ,
84fb5b46 285 Status varchar(64) NULL ,
dab09ea8 286 TimeLeft integer NULL DEFAULT 0 ,
84fb5b46
MKG
287 Told DATETIME NULL ,
288 Starts DATETIME NULL ,
289 Started DATETIME NULL ,
290 Due DATETIME NULL ,
291 Resolved DATETIME NULL ,
292
293
dab09ea8 294 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 295 LastUpdated DATETIME NULL ,
dab09ea8 296 Creator integer NULL DEFAULT 0 ,
84fb5b46
MKG
297 Created DATETIME NULL ,
298 Disabled int2 NOT NULL DEFAULT 0
299
300) ;
301
302CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
303CREATE INDEX Tickets2 ON Tickets (Owner) ;
304CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
305CREATE INDEX Tickets4 ON Tickets (id, Status) ;
306CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
307
308--- }}}
309
310--- {{{ ScripActions
311
312CREATE 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 ,
dab09ea8 318 Creator integer NULL DEFAULT 0 ,
84fb5b46 319 Created DATETIME NULL ,
dab09ea8 320 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
321 LastUpdated DATETIME NULL
322
323) ;
324
325--- }}}
326
327--- {{{ Templates
328
329CREATE 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 ,
dab09ea8 336 TranslationOf integer NULL DEFAULT 0 ,
84fb5b46
MKG
337 Content blob NULL ,
338 LastUpdated DATETIME NULL ,
dab09ea8
MKG
339 LastUpdatedBy integer NULL DEFAULT 0 ,
340 Creator integer NULL DEFAULT 0 ,
84fb5b46
MKG
341 Created DATETIME NULL
342
343) ;
344
345--- }}}
346
347
348
349
350CREATE 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
370CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
371CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
372
373
374
375CREATE 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
399CREATE 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
414CREATE 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
429CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
430
431
432--- {{{ Attributes
433CREATE 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 ,
dab09ea8
MKG
440 ObjectId INTEGER ,
441 Creator integer NULL DEFAULT 0 ,
84fb5b46 442 Created DATETIME NULL ,
dab09ea8 443 LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46
MKG
444 LastUpdated DATETIME NULL
445
446) ;
447CREATE INDEX Attributes1 on Attributes(Name);
448CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
449
450--- }}}
451
452CREATE TABLE Classes (
453id INTEGER PRIMARY KEY,
454Name varchar(255) NOT NULL DEFAULT '',
455Description varchar(255) NOT NULL DEFAULT '',
456SortOrder integer NOT NULL DEFAULT 0,
457Disabled smallint NOT NULL DEFAULT 0,
458Creator integer NOT NULL DEFAULT 0,
459Created TIMESTAMP NULL,
460LastUpdatedBy integer NOT NULL DEFAULT 0,
461LastUpdated TIMESTAMP NULL,
462HotList smallint NOT NULL DEFAULT 0
463);
464
465CREATE TABLE Articles (
466id INTEGER PRIMARY KEY,
467Name varchar(255) NOT NULL DEFAULT '',
468Summary varchar(255) NOT NULL DEFAULT '',
469SortOrder integer NOT NULL DEFAULT 0,
470Class integer NOT NULL DEFAULT 0,
471Parent integer NOT NULL DEFAULT 0,
472URI varchar(255),
473Creator integer NOT NULL DEFAULT 0,
474Created TIMESTAMP NULL,
475LastUpdatedBy integer NOT NULL DEFAULT 0,
476LastUpdated TIMESTAMP NULL
477);
478
479
480CREATE TABLE Topics (
481id INTEGER PRIMARY KEY,
482Parent integer NOT NULL DEFAULT 0,
483Name varchar(255) NOT NULL DEFAULT '',
484Description varchar(255) NOT NULL DEFAULT '',
485ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8 486ObjectId integer NOT NULL DEFAULT 0
84fb5b46
MKG
487);
488
489
490CREATE TABLE ObjectTopics (
491id INTEGER PRIMARY KEY,
dab09ea8 492Topic integer NOT NULL DEFAULT 0,
84fb5b46 493ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8 494ObjectId integer NOT NULL DEFAULT 0
84fb5b46
MKG
495);
496
497CREATE TABLE ObjectClasses (
498id INTEGER PRIMARY KEY,
dab09ea8 499Class integer NOT NULL DEFAULT 0,
84fb5b46 500ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8 501ObjectId integer NOT NULL DEFAULT 0,
84fb5b46
MKG
502Creator integer NOT NULL DEFAULT 0,
503Created TIMESTAMP NULL,
504LastUpdatedBy integer NOT NULL DEFAULT 0,
505LastUpdated TIMESTAMP NULL
506);