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 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
31 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
32 DefaultDueIn NUMBER(11,0) DEFAULT 0 NOT NULL,
33 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
35 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
37 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
39 CREATE INDEX Queues1 ON Queues (LOWER(Name));
40 CREATE INDEX Queues2 ON Queues (Disabled);
43 CREATE SEQUENCE LINKS_seq;
46 CONSTRAINT Links_Key PRIMARY KEY,
49 Type VARCHAR2(20) NOT NULL,
50 LocalTarget NUMBER(11,0) DEFAULT 0 NOT NULL,
51 LocalBase NUMBER(11,0) DEFAULT 0 NOT NULL,
52 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
54 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
57 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
58 CREATE INDEX Links2 ON Links (Base, Type);
59 CREATE INDEX Links3 ON Links (Target, Type);
60 CREATE INDEX Links4 ON Links(Type,LocalBase);
63 CREATE SEQUENCE PRINCIPALS_seq;
64 CREATE TABLE Principals (
66 CONSTRAINT Principals_Key PRIMARY KEY,
67 PrincipalType VARCHAR2(16),
68 ObjectId NUMBER(11,0),
69 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
71 CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId);
74 CREATE SEQUENCE GROUPS_seq;
77 CONSTRAINT Groups_Key PRIMARY KEY,
79 Description VARCHAR2(255),
82 Instance NUMBER(11,0) DEFAULT 0 -- NOT NULL
83 -- Instance VARCHAR2(64)
85 CREATE INDEX Groups1 ON Groups (LOWER(Domain), Instance, LOWER(Type), id);
86 CREATE INDEX Groups2 ON Groups (LOWER(Type), Instance, LOWER(Domain));
89 CREATE SEQUENCE SCRIPCONDITIONS_seq;
90 CREATE TABLE ScripConditions (
92 CONSTRAINT ScripConditions_Key PRIMARY KEY,
94 Description VARCHAR2(255),
95 ExecModule VARCHAR2(60),
96 Argument VARCHAR2(255),
97 ApplicableTransTypes VARCHAR2(60),
98 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
100 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
105 CREATE SEQUENCE TRANSACTIONS_seq;
106 CREATE TABLE Transactions (
108 CONSTRAINT Transactions_Key PRIMARY KEY,
109 ObjectType VARCHAR2(255),
110 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
111 TimeTaken NUMBER(11,0) DEFAULT 0 NOT NULL,
114 OldValue VARCHAR2(255),
115 NewValue VARCHAR2(255),
116 ReferenceType VARCHAR2(255),
117 OldReference NUMBER(11,0),
118 NewReference NUMBER(11,0),
120 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
123 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
126 CREATE SEQUENCE SCRIPS_seq;
127 CREATE TABLE Scrips (
129 CONSTRAINT Scrips_Key PRIMARY KEY,
130 Description VARCHAR2(255),
131 ScripCondition NUMBER(11,0) DEFAULT 0 NOT NULL,
132 ScripAction NUMBER(11,0) DEFAULT 0 NOT NULL,
135 CustomIsApplicableCode CLOB,
136 CustomPrepareCode CLOB,
137 CustomCommitCode CLOB,
139 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
140 Template NUMBER(11,0) DEFAULT 0 NOT NULL,
141 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
143 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
148 CREATE SEQUENCE ACL_seq;
151 CONSTRAINT ACL_Key PRIMARY KEY,
152 PrincipalType VARCHAR2(25) NOT NULL,
153 PrincipalId NUMBER(11,0) NOT NULL,
154 RightName VARCHAR2(25) NOT NULL,
155 ObjectType VARCHAR2(25) NOT NULL,
156 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
157 DelegatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
158 DelegatedFrom NUMBER(11,0) DEFAULT 0 NOT NULL
160 CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
163 CREATE SEQUENCE GROUPMEMBERS_seq;
164 CREATE TABLE GroupMembers (
166 CONSTRAINT GroupMembers_Key PRIMARY KEY,
167 GroupId NUMBER(11,0) DEFAULT 0 NOT NULL,
168 MemberId NUMBER(11,0) DEFAULT 0 NOT NULL
170 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
173 CREATE SEQUENCE CachedGroupMembers_seq;
174 CREATE TABLE CachedGroupMembers (
176 CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
177 GroupId NUMBER(11,0),
178 MemberId NUMBER(11,0),
180 ImmediateParentId NUMBER(11,0),
181 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
183 CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
184 CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
185 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
188 CREATE SEQUENCE USERS_seq;
191 CONSTRAINT Users_Key PRIMARY KEY,
192 Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
194 Password VARCHAR2(40),
197 EmailAddress VARCHAR2(120),
198 FreeFormContactInfo CLOB,
199 Organization VARCHAR2(200),
200 RealName VARCHAR2(120),
201 NickName VARCHAR2(16),
203 EmailEncoding VARCHAR2(16),
204 WebEncoding VARCHAR2(16),
205 ExternalContactInfoId VARCHAR2(100),
206 ContactInfoSystem VARCHAR2(30),
207 ExternalAuthId VARCHAR2(100),
208 AuthSystem VARCHAR2(30),
210 HomePhone VARCHAR2(30),
211 WorkPhone VARCHAR2(30),
212 MobilePhone VARCHAR2(30),
213 PagerPhone VARCHAR2(30),
214 Address1 VARCHAR2(200),
215 Address2 VARCHAR2(200),
219 Country VARCHAR2(50),
220 Timezone VARCHAR2(50),
222 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
224 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
227 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
229 CREATE INDEX Users2 ON Users( LOWER(Name));
230 CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
233 CREATE SEQUENCE TICKETS_seq;
234 CREATE TABLE Tickets (
236 CONSTRAINT Tickets_Key PRIMARY KEY,
237 EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
238 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
240 IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
241 Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
242 Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
243 Subject VARCHAR2(200) DEFAULT '[no subject]',
244 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
245 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
246 Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
247 TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
248 TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
250 TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
256 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
258 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
260 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
262 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
263 CREATE INDEX Tickets2 ON Tickets (Owner);
264 CREATE INDEX Tickets4 ON Tickets (id, Status);
265 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
266 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
269 CREATE SEQUENCE SCRIPACTIONS_seq;
270 CREATE TABLE ScripActions (
272 CONSTRAINT ScripActions_Key PRIMARY KEY,
274 Description VARCHAR2(255),
275 ExecModule VARCHAR2(60),
276 Argument VARCHAR2(255),
277 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
279 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
284 CREATE SEQUENCE TEMPLATES_seq;
285 CREATE TABLE Templates (
287 CONSTRAINT Templates_Key PRIMARY KEY,
288 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
289 Name VARCHAR2(200) NOT NULL,
290 Description VARCHAR2(255),
292 Language VARCHAR2(16),
293 TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
296 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
297 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
302 CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
303 CREATE TABLE ObjectCustomFields (
305 CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
306 CustomField NUMBER(11,0) NOT NULL,
307 ObjectId NUMBER(11,0) NOT NULL,
308 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
309 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
311 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
316 CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
317 CREATE TABLE ObjectCustomFieldValues (
319 CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
320 CustomField NUMBER(11,0) NOT NULL,
321 ObjectType VARCHAR2(25) NOT NULL,
322 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
323 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
324 Content VARCHAR2(255),
326 ContentType VARCHAR2(80),
327 ContentEncoding VARCHAR2(80),
328 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
330 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
332 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
335 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
336 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
338 CREATE SEQUENCE CUSTOMFIELDS_seq;
339 CREATE TABLE CustomFields (
341 CONSTRAINT CustomFields_Key PRIMARY KEY,
344 MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
346 Repeated NUMBER(11,0) DEFAULT 0 NOT NULL,
347 Description VARCHAR2(255),
348 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
349 LookupType VARCHAR2(255),
350 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
352 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
354 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
358 CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
359 CREATE TABLE CustomFieldValues (
361 CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
362 CustomField NUMBER(11,0),
364 Description VARCHAR2(255),
365 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
366 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
368 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
372 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
374 CREATE SEQUENCE ATTRIBUTES_seq;
375 CREATE TABLE Attributes (
376 id NUMBER(11,0) PRIMARY KEY,
377 Name VARCHAR2(255) NOT NULL,
378 Description VARCHAR2(255),
380 ContentType VARCHAR(16),
381 ObjectType VARCHAR2(25) NOT NULL,
382 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
383 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
385 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
389 CREATE INDEX Attributes1 on Attributes(Name);
390 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
393 CREATE TABLE sessions (
395 CONSTRAINT Sessions_Key PRIMARY KEY,