2 CREATE SEQUENCE ATTACHMENTS_seq;
3 CREATE TABLE Attachments (
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),
10 Filename VARCHAR2(255),
11 ContentType VARCHAR2(80),
12 ContentEncoding VARCHAR2(80),
15 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
18 CREATE INDEX Attachments2 ON Attachments (TransactionId);
19 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
22 CREATE SEQUENCE QUEUES_seq;
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,
37 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
39 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
41 CREATE INDEX Queues1 ON Queues (LOWER(Name));
42 CREATE INDEX Queues2 ON Queues (Disabled);
45 CREATE SEQUENCE LINKS_seq;
48 CONSTRAINT Links_Key PRIMARY KEY,
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,
56 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
59 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
60 CREATE INDEX Links2 ON Links (Base, Type);
61 CREATE INDEX Links3 ON Links (Target, Type);
62 CREATE INDEX Links4 ON Links(Type,LocalBase);
65 CREATE SEQUENCE PRINCIPALS_seq;
66 CREATE TABLE Principals (
68 CONSTRAINT Principals_Key PRIMARY KEY,
69 PrincipalType VARCHAR2(16),
70 ObjectId NUMBER(11,0),
71 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
73 CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId);
76 CREATE SEQUENCE GROUPS_seq;
79 CONSTRAINT Groups_Key PRIMARY KEY,
81 Description VARCHAR2(255),
84 Instance NUMBER(11,0) DEFAULT 0, -- NOT NULL
85 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
87 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
89 -- Instance VARCHAR2(64)
91 CREATE INDEX Groups1 ON Groups (LOWER(Domain), Instance, LOWER(Type), id);
92 CREATE INDEX Groups2 ON Groups (LOWER(Type), Instance, LOWER(Domain));
95 CREATE SEQUENCE SCRIPCONDITIONS_seq;
96 CREATE TABLE ScripConditions (
98 CONSTRAINT ScripConditions_Key PRIMARY KEY,
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,
106 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
111 CREATE SEQUENCE TRANSACTIONS_seq;
112 CREATE TABLE Transactions (
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,
120 OldValue VARCHAR2(255),
121 NewValue VARCHAR2(255),
122 ReferenceType VARCHAR2(255),
123 OldReference NUMBER(11,0),
124 NewReference NUMBER(11,0),
126 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
129 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
132 CREATE SEQUENCE SCRIPS_seq;
133 CREATE TABLE Scrips (
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,
141 CustomIsApplicableCode CLOB,
142 CustomPrepareCode CLOB,
143 CustomCommitCode CLOB,
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,
149 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
154 CREATE SEQUENCE ACL_seq;
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,
165 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
168 CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
171 CREATE SEQUENCE GROUPMEMBERS_seq;
172 CREATE TABLE GroupMembers (
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,
179 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
182 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
185 CREATE SEQUENCE CachedGroupMembers_seq;
186 CREATE TABLE CachedGroupMembers (
188 CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
189 GroupId NUMBER(11,0),
190 MemberId NUMBER(11,0),
192 ImmediateParentId NUMBER(11,0),
193 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
195 CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
196 CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
197 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
200 CREATE SEQUENCE USERS_seq;
203 CONSTRAINT Users_Key PRIMARY KEY,
204 Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
206 Password VARCHAR2(256),
207 AuthToken VARCHAR2(16),
210 EmailAddress VARCHAR2(120),
211 FreeFormContactInfo CLOB,
212 Organization VARCHAR2(200),
213 RealName VARCHAR2(120),
214 NickName 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),
223 HomePhone VARCHAR2(30),
224 WorkPhone VARCHAR2(30),
225 MobilePhone VARCHAR2(30),
226 PagerPhone VARCHAR2(30),
227 Address1 VARCHAR2(200),
228 Address2 VARCHAR2(200),
232 Country VARCHAR2(50),
233 Timezone VARCHAR2(50),
235 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
237 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
240 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
242 CREATE INDEX Users2 ON Users( LOWER(Name));
243 CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
246 CREATE SEQUENCE TICKETS_seq;
247 CREATE TABLE Tickets (
249 CONSTRAINT Tickets_Key PRIMARY KEY,
250 EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
251 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
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,
263 TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
269 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
271 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
273 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
275 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
276 CREATE INDEX Tickets2 ON Tickets (Owner);
277 CREATE INDEX Tickets4 ON Tickets (id, Status);
278 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
279 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
282 CREATE SEQUENCE SCRIPACTIONS_seq;
283 CREATE TABLE ScripActions (
285 CONSTRAINT ScripActions_Key PRIMARY KEY,
287 Description VARCHAR2(255),
288 ExecModule VARCHAR2(60),
289 Argument VARCHAR2(255),
290 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
292 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
297 CREATE SEQUENCE TEMPLATES_seq;
298 CREATE TABLE Templates (
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),
305 Language VARCHAR2(16),
306 TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
309 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
310 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
315 CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
316 CREATE TABLE ObjectCustomFields (
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,
324 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
329 CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
330 CREATE TABLE ObjectCustomFieldValues (
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),
339 ContentType VARCHAR2(80),
340 ContentEncoding VARCHAR2(80),
341 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
343 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
345 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
348 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
349 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
351 CREATE SEQUENCE CUSTOMFIELDS_seq;
352 CREATE TABLE CustomFields (
354 CONSTRAINT CustomFields_Key PRIMARY KEY,
357 RenderType VARCHAR2(64),
358 MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
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,
368 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
370 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
374 CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
375 CREATE TABLE CustomFieldValues (
377 CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
378 CustomField NUMBER(11,0),
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,
385 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
389 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
391 CREATE SEQUENCE ATTRIBUTES_seq;
392 CREATE TABLE Attributes (
393 id NUMBER(11,0) PRIMARY KEY,
394 Name VARCHAR2(255) NOT NULL,
395 Description VARCHAR2(255),
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,
402 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
406 CREATE INDEX Attributes1 on Attributes(Name);
407 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
410 CREATE TABLE sessions (
412 CONSTRAINT Sessions_Key PRIMARY KEY,
417 CREATE SEQUENCE Classes_seq;
418 CREATE TABLE Classes (
420 CONSTRAINT Classes_key PRIMARY KEY,
421 Name varchar2(255) DEFAULT '',
422 Description varchar2(255) DEFAULT '',
423 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
424 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL,
425 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
427 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
429 HotList NUMBER(11,0) DEFAULT 0 NOT NULL
432 CREATE SEQUENCE Articles_seq;
433 CREATE TABLE Articles (
435 CONSTRAINT Articles_key PRIMARY KEY,
436 Name varchar2(255) DEFAULT '',
437 Summary varchar2(255) DEFAULT '',
438 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
439 Class NUMBER(11,0) DEFAULT 0 NOT NULL,
440 Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
442 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
444 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
449 CREATE SEQUENCE Topics_seq;
450 CREATE TABLE Topics (
452 CONSTRAINT Topics_key PRIMARY KEY,
453 Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
454 Name varchar2(255) DEFAULT '',
455 Description varchar2(255) DEFAULT '',
456 ObjectType varchar2(64) DEFAULT '' NOT NULL,
457 ObjectId NUMBER(11,0) NOT NULL
461 CREATE SEQUENCE ObjectTopics_seq;
462 CREATE TABLE ObjectTopics (
464 CONSTRAINT ObjectTopics_key PRIMARY KEY,
465 Topic NUMBER(11,0) NOT NULL,
466 ObjectType varchar2(64) DEFAULT '' NOT NULL,
467 ObjectId NUMBER(11,0) NOT NULL
470 CREATE SEQUENCE ObjectClasses_seq;
471 CREATE TABLE ObjectClasses (
473 CONSTRAINT ObjectClasses_key PRIMARY KEY,
474 Class NUMBER(11,0) NOT NULL,
475 ObjectType varchar2(255) DEFAULT '' NOT NULL,
476 ObjectId NUMBER(11,0) NOT NULL,
477 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
479 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,