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);
187 CREATE SEQUENCE USERS_seq;
190 CONSTRAINT Users_Key PRIMARY KEY,
191 Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
193 Password VARCHAR2(40),
196 EmailAddress VARCHAR2(120),
197 FreeFormContactInfo CLOB,
198 Organization VARCHAR2(200),
199 RealName VARCHAR2(120),
200 NickName VARCHAR2(16),
202 EmailEncoding VARCHAR2(16),
203 WebEncoding VARCHAR2(16),
204 ExternalContactInfoId VARCHAR2(100),
205 ContactInfoSystem VARCHAR2(30),
206 ExternalAuthId VARCHAR2(100),
207 AuthSystem VARCHAR2(30),
209 HomePhone VARCHAR2(30),
210 WorkPhone VARCHAR2(30),
211 MobilePhone VARCHAR2(30),
212 PagerPhone VARCHAR2(30),
213 Address1 VARCHAR2(200),
214 Address2 VARCHAR2(200),
218 Country VARCHAR2(50),
219 Timezone VARCHAR2(50),
221 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
223 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
226 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
228 CREATE INDEX Users2 ON Users( LOWER(name));
229 CREATE INDEX Users4 ON Users (lower(EmailAddress));
232 CREATE SEQUENCE TICKETS_seq;
233 CREATE TABLE Tickets (
235 CONSTRAINT Tickets_Key PRIMARY KEY,
236 EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
237 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
239 IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
240 Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
241 Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
242 Subject VARCHAR2(200) DEFAULT '[no subject]',
243 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
244 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
245 Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
246 TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
247 TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
249 TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
255 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
257 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
259 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
261 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
262 CREATE INDEX Tickets2 ON Tickets (Owner);
263 CREATE INDEX Tickets4 ON Tickets (id, Status);
264 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
265 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
268 CREATE SEQUENCE SCRIPACTIONS_seq;
269 CREATE TABLE ScripActions (
271 CONSTRAINT ScripActions_Key PRIMARY KEY,
273 Description VARCHAR2(255),
274 ExecModule VARCHAR2(60),
275 Argument VARCHAR2(255),
276 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
278 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
283 CREATE SEQUENCE TEMPLATES_seq;
284 CREATE TABLE Templates (
286 CONSTRAINT Templates_Key PRIMARY KEY,
287 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
288 Name VARCHAR2(200) NOT NULL,
289 Description VARCHAR2(255),
291 Language VARCHAR2(16),
292 TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
295 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
296 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
301 CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
302 CREATE TABLE ObjectCustomFields (
304 CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
305 CustomField NUMBER(11,0) NOT NULL,
306 ObjectId NUMBER(11,0) NOT NULL,
307 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
308 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
310 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
315 CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
316 CREATE TABLE ObjectCustomFieldValues (
318 CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
319 CustomField NUMBER(11,0) NOT NULL,
320 ObjectType VARCHAR2(25) NOT NULL,
321 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
322 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
323 Content VARCHAR2(255),
325 ContentType VARCHAR2(80),
326 ContentEncoding VARCHAR2(80),
327 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
329 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
331 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
334 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
335 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
337 CREATE SEQUENCE CUSTOMFIELDS_seq;
338 CREATE TABLE CustomFields (
340 CONSTRAINT CustomFields_Key PRIMARY KEY,
343 MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
344 Pattern VARCHAR2(255),
345 Repeated NUMBER(11,0) DEFAULT 0 NOT NULL,
346 Description VARCHAR2(255),
347 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
348 LookupType VARCHAR2(255),
349 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
351 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
353 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
357 CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
358 CREATE TABLE CustomFieldValues (
360 CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
361 CustomField NUMBER(11,0),
363 Description VARCHAR2(255),
364 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
365 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
367 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
371 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
373 CREATE SEQUENCE ATTRIBUTES_seq;
374 CREATE TABLE Attributes (
375 id NUMBER(11,0) PRIMARY KEY,
376 Name VARCHAR2(255) NOT NULL,
377 Description VARCHAR2(255),
379 ContentType VARCHAR(16),
380 ObjectType VARCHAR2(25) NOT NULL,
381 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
382 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
384 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
388 CREATE INDEX Attributes1 on Attributes(Name);
389 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
392 CREATE TABLE sessions (
394 CONSTRAINT Sessions_Key PRIMARY KEY,