Upgrade to 4.0.8 with mod of ExternalAuth + absolute paths to ticket-menu.
[usit-rt.git] / etc / schema.Oracle
CommitLineData
84fb5b46
MKG
1
2CREATE SEQUENCE ATTACHMENTS_seq;
3CREATE TABLE Attachments (
4 id NUMBER(11,0)
5 CONSTRAINT Attachments_Key PRIMARY KEY,
6 TransactionId NUMBER(11,0) NOT NULL,
7 Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
8 MessageId VARCHAR2(160),
9 Subject VARCHAR2(255),
10 Filename VARCHAR2(255),
11 ContentType VARCHAR2(80),
12 ContentEncoding VARCHAR2(80),
13 Content CLOB,
14 Headers CLOB,
15 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
16 Created DATE
17);
18CREATE INDEX Attachments2 ON Attachments (TransactionId);
19CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
20
21
22CREATE SEQUENCE QUEUES_seq;
23CREATE TABLE Queues (
24 id NUMBER(11,0)
25 CONSTRAINT Queues_Key PRIMARY KEY,
26 Name VARCHAR2(200) CONSTRAINT Queues_Name_Unique UNIQUE NOT NULL,
27 Description VARCHAR2(255),
28 CorrespondAddress VARCHAR2(120),
29 CommentAddress VARCHAR2(120),
30 Lifecycle VARCHAR2(32),
31 SubjectTag VARCHAR2(120),
32 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
33 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
34 DefaultDueIn NUMBER(11,0) DEFAULT 0 NOT NULL,
35 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
36 Created DATE,
37 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
38 LastUpdated DATE,
39 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
40);
41 CREATE INDEX Queues1 ON Queues (LOWER(Name));
42CREATE INDEX Queues2 ON Queues (Disabled);
43
44
45CREATE SEQUENCE LINKS_seq;
46CREATE TABLE Links (
47 id NUMBER(11,0)
48 CONSTRAINT Links_Key PRIMARY KEY,
49 Base VARCHAR2(240),
50 Target VARCHAR2(240),
51 Type VARCHAR2(20) NOT NULL,
52 LocalTarget NUMBER(11,0) DEFAULT 0 NOT NULL,
53 LocalBase NUMBER(11,0) DEFAULT 0 NOT NULL,
54 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
55 LastUpdated DATE,
56 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
57 Created DATE
58);
59CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
60CREATE INDEX Links2 ON Links (Base, Type);
61CREATE INDEX Links3 ON Links (Target, Type);
62CREATE INDEX Links4 ON Links(Type,LocalBase);
63
64
65CREATE SEQUENCE PRINCIPALS_seq;
66CREATE TABLE Principals (
67 id NUMBER(11,0)
68 CONSTRAINT Principals_Key PRIMARY KEY,
69 PrincipalType VARCHAR2(16),
70 ObjectId NUMBER(11,0),
71 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
72);
73CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId);
74
75
76CREATE SEQUENCE GROUPS_seq;
77CREATE TABLE Groups (
78 id NUMBER(11,0)
79 CONSTRAINT Groups_Key PRIMARY KEY,
80 Name VARCHAR2(200),
81 Description VARCHAR2(255),
82 Domain VARCHAR2(64),
83 Type VARCHAR2(64),
84 Instance NUMBER(11,0) DEFAULT 0, -- NOT NULL
85 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
86 Created DATE,
87 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
88 LastUpdated DATE
89-- Instance VARCHAR2(64)
90);
91CREATE INDEX Groups1 ON Groups (LOWER(Domain), Instance, LOWER(Type), id);
92CREATE INDEX Groups2 ON Groups (LOWER(Type), Instance, LOWER(Domain));
93
94
95CREATE SEQUENCE SCRIPCONDITIONS_seq;
96CREATE TABLE ScripConditions (
97 id NUMBER(11, 0)
98 CONSTRAINT ScripConditions_Key PRIMARY KEY,
99 Name VARCHAR2(200),
100 Description VARCHAR2(255),
101 ExecModule VARCHAR2(60),
102 Argument VARCHAR2(255),
103 ApplicableTransTypes VARCHAR2(60),
104 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
105 Created DATE,
106 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
107 LastUpdated DATE
108);
109
110
111CREATE SEQUENCE TRANSACTIONS_seq;
112CREATE TABLE Transactions (
113 id NUMBER(11,0)
114 CONSTRAINT Transactions_Key PRIMARY KEY,
115 ObjectType VARCHAR2(255),
116 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
117 TimeTaken NUMBER(11,0) DEFAULT 0 NOT NULL,
118 Type VARCHAR2(20),
119 Field VARCHAR2(40),
120 OldValue VARCHAR2(255),
121 NewValue VARCHAR2(255),
122 ReferenceType VARCHAR2(255),
123 OldReference NUMBER(11,0),
124 NewReference NUMBER(11,0),
125 Data VARCHAR2(255),
126 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
127 Created DATE
128);
129CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
130
131
132CREATE SEQUENCE SCRIPS_seq;
133CREATE TABLE Scrips (
134 id NUMBER(11,0)
135 CONSTRAINT Scrips_Key PRIMARY KEY,
136 Description VARCHAR2(255),
137 ScripCondition NUMBER(11,0) DEFAULT 0 NOT NULL,
138 ScripAction NUMBER(11,0) DEFAULT 0 NOT NULL,
139 ConditionRules CLOB,
140 ActionRules CLOB,
141 CustomIsApplicableCode CLOB,
142 CustomPrepareCode CLOB,
143 CustomCommitCode CLOB,
144 Stage VARCHAR2(32),
145 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
146 Template NUMBER(11,0) DEFAULT 0 NOT NULL,
147 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
148 Created DATE,
149 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
150 LastUpdated DATE
151);
152
153
154CREATE SEQUENCE ACL_seq;
155CREATE TABLE ACL (
156 id NUMBER(11,0)
157 CONSTRAINT ACL_Key PRIMARY KEY,
158 PrincipalType VARCHAR2(25) NOT NULL,
159 PrincipalId NUMBER(11,0) NOT NULL,
160 RightName VARCHAR2(25) NOT NULL,
161 ObjectType VARCHAR2(25) NOT NULL,
162 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
163 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
164 Created DATE,
165 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
166 LastUpdated DATE
167);
168CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
169
170
171CREATE SEQUENCE GROUPMEMBERS_seq;
172CREATE TABLE GroupMembers (
173 id NUMBER(11,0)
174 CONSTRAINT GroupMembers_Key PRIMARY KEY,
175 GroupId NUMBER(11,0) DEFAULT 0 NOT NULL,
176 MemberId NUMBER(11,0) DEFAULT 0 NOT NULL,
177 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
178 Created DATE,
179 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
180 LastUpdated DATE
181);
182CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
183
184
185CREATE SEQUENCE CachedGroupMembers_seq;
186CREATE TABLE CachedGroupMembers (
187 id NUMBER(11,0)
188 CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
189 GroupId NUMBER(11,0),
190 MemberId NUMBER(11,0),
191 Via NUMBER(11,0),
192 ImmediateParentId NUMBER(11,0),
193 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
194);
195CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
196CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
197CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
198
199
200CREATE SEQUENCE USERS_seq;
201CREATE TABLE Users (
202 id NUMBER(11,0)
203 CONSTRAINT Users_Key PRIMARY KEY,
204 Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
205 unique NOT NULL,
206 Password VARCHAR2(256),
207 AuthToken VARCHAR2(16),
208 Comments CLOB,
209 Signature CLOB,
210 EmailAddress VARCHAR2(120),
211 FreeFormContactInfo CLOB,
212 Organization VARCHAR2(200),
213 RealName VARCHAR2(120),
214 NickName VARCHAR2(16),
215 Lang VARCHAR2(16),
216 EmailEncoding VARCHAR2(16),
217 WebEncoding VARCHAR2(16),
218 ExternalContactInfoId VARCHAR2(100),
219 ContactInfoSystem VARCHAR2(30),
220 ExternalAuthId VARCHAR2(100),
221 AuthSystem VARCHAR2(30),
222 Gecos VARCHAR2(16),
223 HomePhone VARCHAR2(30),
224 WorkPhone VARCHAR2(30),
225 MobilePhone VARCHAR2(30),
226 PagerPhone VARCHAR2(30),
227 Address1 VARCHAR2(200),
228 Address2 VARCHAR2(200),
229 City VARCHAR2(100),
230 State VARCHAR2(100),
231 Zip VARCHAR2(16),
232 Country VARCHAR2(50),
233 Timezone VARCHAR2(50),
234 PGPKey CLOB,
235 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
236 Created DATE,
237 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
238 LastUpdated DATE
239);
240-- CREATE UNIQUE INDEX Users1 ON Users (Name);
241
242CREATE INDEX Users2 ON Users( LOWER(Name));
243CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
244
245
246CREATE SEQUENCE TICKETS_seq;
247CREATE TABLE Tickets (
248 id NUMBER(11, 0)
249 CONSTRAINT Tickets_Key PRIMARY KEY,
250 EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
251 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
252 Type VARCHAR2(16),
253 IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
254 Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
255 Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
256 Subject VARCHAR2(200) DEFAULT '[no subject]',
257 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
258 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
259 Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
260 TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
261 TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
262 Status VARCHAR2(64),
263 TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
264 Told DATE,
265 Starts DATE,
266 Started DATE,
267 Due DATE,
268 Resolved DATE,
269 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
270 LastUpdated DATE,
271 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
272 Created DATE,
273 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
274);
275CREATE INDEX Tickets1 ON Tickets (Queue, Status);
276CREATE INDEX Tickets2 ON Tickets (Owner);
277CREATE INDEX Tickets4 ON Tickets (id, Status);
278CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
279CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
280
281
282CREATE SEQUENCE SCRIPACTIONS_seq;
283CREATE TABLE ScripActions (
284 id NUMBER(11,0)
285 CONSTRAINT ScripActions_Key PRIMARY KEY,
286 Name VARCHAR2(200),
287 Description VARCHAR2(255),
288 ExecModule VARCHAR2(60),
289 Argument VARCHAR2(255),
290 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
291 Created DATE,
292 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
293 LastUpdated DATE
294);
295
296
297CREATE SEQUENCE TEMPLATES_seq;
298CREATE TABLE Templates (
299 id NUMBER(11,0)
300 CONSTRAINT Templates_Key PRIMARY KEY,
301 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
302 Name VARCHAR2(200) NOT NULL,
303 Description VARCHAR2(255),
304 Type VARCHAR2(16),
305 Language VARCHAR2(16),
306 TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
307 Content CLOB,
308 LastUpdated DATE,
309 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
310 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
311 Created DATE
312);
313
314
315CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
316CREATE TABLE ObjectCustomFields (
317 id NUMBER(11,0)
318 CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
319 CustomField NUMBER(11,0) NOT NULL,
320 ObjectId NUMBER(11,0) NOT NULL,
321 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
322 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
323 Created DATE,
324 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
325 LastUpdated DATE
326);
327
328
329CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
330CREATE TABLE ObjectCustomFieldValues (
331 id NUMBER(11,0)
332 CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
333 CustomField NUMBER(11,0) NOT NULL,
334 ObjectType VARCHAR2(25) NOT NULL,
335 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
336 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
337 Content VARCHAR2(255),
338 LargeContent CLOB,
339 ContentType VARCHAR2(80),
340 ContentEncoding VARCHAR2(80),
341 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
342 Created DATE,
343 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
344 LastUpdated DATE,
345 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
346);
347
348CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
349CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
350
351CREATE SEQUENCE CUSTOMFIELDS_seq;
352CREATE TABLE CustomFields (
353 id NUMBER(11,0)
354 CONSTRAINT CustomFields_Key PRIMARY KEY,
355 Name VARCHAR2(200),
356 Type VARCHAR2(200),
357 RenderType VARCHAR2(64),
358 MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
359 Pattern CLOB,
360 Repeated NUMBER(11,0) DEFAULT 0 NOT NULL,
361 ValuesClass VARCHAR2(64),
362 BasedOn NUMBER(11,0) NULL,
363 Description VARCHAR2(255),
364 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
365 LookupType VARCHAR2(255),
366 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
367 Created DATE,
368 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
369 LastUpdated DATE,
370 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
371);
372
373
374CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
375CREATE TABLE CustomFieldValues (
376 id NUMBER(11,0)
377 CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
378 CustomField NUMBER(11,0),
379 Name VARCHAR2(200),
380 Description VARCHAR2(255),
381 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
382 Category VARCHAR2(255),
383 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
384 Created DATE,
385 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
386 LastUpdated DATE
387);
388
389CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
390
391CREATE SEQUENCE ATTRIBUTES_seq;
392CREATE TABLE Attributes (
393 id NUMBER(11,0) PRIMARY KEY,
394 Name VARCHAR2(255) NOT NULL,
395 Description VARCHAR2(255),
396 Content CLOB,
397 ContentType VARCHAR(16),
398 ObjectType VARCHAR2(25) NOT NULL,
399 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
400 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
401 Created DATE,
402 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
403 LastUpdated DATE
404);
405
406CREATE INDEX Attributes1 on Attributes(Name);
407CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
408
409
410CREATE TABLE sessions (
411 id VARCHAR2(32)
412 CONSTRAINT Sessions_Key PRIMARY KEY,
413 a_session CLOB,
414 LastUpdated DATE
415);
416
417CREATE SEQUENCE Classes_seq;
418CREATE TABLE Classes (
419id NUMBER(11,0)
420 CONSTRAINT Classes_key PRIMARY KEY,
421Name varchar2(255) DEFAULT '',
422Description varchar2(255) DEFAULT '',
423SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
424Disabled NUMBER(11,0) DEFAULT 0 NOT NULL,
425Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
426Created DATE,
427LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
428LastUpdated DATE,
429HotList NUMBER(11,0) DEFAULT 0 NOT NULL
430);
431
432CREATE SEQUENCE Articles_seq;
433CREATE TABLE Articles (
434id NUMBER(11,0)
435 CONSTRAINT Articles_key PRIMARY KEY,
436Name varchar2(255) DEFAULT '',
437Summary varchar2(255) DEFAULT '',
438SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
439Class NUMBER(11,0) DEFAULT 0 NOT NULL,
440Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
441URI varchar2(255),
442Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
443Created DATE,
444LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
445LastUpdated DATE
446);
447
448
449CREATE SEQUENCE Topics_seq;
450CREATE TABLE Topics (
451id NUMBER(11,0)
452 CONSTRAINT Topics_key PRIMARY KEY,
453Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
454Name varchar2(255) DEFAULT '',
455Description varchar2(255) DEFAULT '',
456ObjectType varchar2(64) DEFAULT '' NOT NULL,
457ObjectId NUMBER(11,0) NOT NULL
458);
459
460
461CREATE SEQUENCE ObjectTopics_seq;
462CREATE TABLE ObjectTopics (
463id NUMBER(11,0)
464 CONSTRAINT ObjectTopics_key PRIMARY KEY,
465Topic NUMBER(11,0) NOT NULL,
466ObjectType varchar2(64) DEFAULT '' NOT NULL,
467ObjectId NUMBER(11,0) NOT NULL
468);
469
470CREATE SEQUENCE ObjectClasses_seq;
471CREATE TABLE ObjectClasses (
472id NUMBER(11,0)
473 CONSTRAINT ObjectClasses_key PRIMARY KEY,
474Class NUMBER(11,0) NOT NULL,
475ObjectType varchar2(255) DEFAULT '' NOT NULL,
476ObjectId NUMBER(11,0) NOT NULL,
477Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
478Created DATE,
479LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
480LastUpdated DATE
481);