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