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