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