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
|
CREATE SEQUENCE KEYWORDSELECTS_seq;
CREATE TABLE KeywordSelects (
id NUMBER(11, 0) PRIMARY KEY,
Name VARCHAR2(255),
Keyword NUMBER(11, 0),
Single NUMBER(11, 0),
Depth NUMBER(11, 0) DEFAULT 0,
ObjectType VARCHAR2(32) NOT NULL,
ObjectField VARCHAR2(32),
ObjectValue VARCHAR2(255),
Disabled NUMBER(11, 0) DEFAULT 0
);
CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
CREATE INDEX KeywordSelects2 ON
KeywordSelects(ObjectType, ObjectField, ObjectValue);
CREATE SEQUENCE ATTACHMENTS_seq;
CREATE TABLE Attachments (
id NUMBER(11,0) PRIMARY KEY,
TransactionId NUMBER(11,0) NOT NULL,
Parent NUMBER(11,0),
MessageId VARCHAR2(160),
Subject VARCHAR2(255),
Filename VARCHAR2(255),
ContentType VARCHAR2(80),
ContentEncoding VARCHAR2(80),
Content CLOB,
Headers CLOB,
Creator NUMBER(11,0),
Created DATE,
Disabled NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE QUEUES_seq;
CREATE TABLE Queues (
id NUMBER(11, 0) PRIMARY KEY,
Name VARCHAR2(40) NOT NULL UNIQUE,
Description VARCHAR2(120),
CorrespondAddress VARCHAR2(40),
CommentAddress VARCHAR2(40),
InitialPriority NUMBER(11, 0),
FinalPriority NUMBER(11, 0),
DefaultDueIn NUMBER(11, 0),
Creator NUMBER(11, 0),
Created DATE,
LastUpdatedBy NUMBER(11, 0),
LastUpdated DATE,
Disabled NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE LINKS_seq;
CREATE TABLE Links (
id NUMBER(11,0) PRIMARY KEY,
Base VARCHAR2(255),
Target VARCHAR2(255),
Type VARCHAR2(20) NOT NULL,
LocalTarget NUMBER(11,0),
LocalBase NUMBER(11,0),
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Creator NUMBER(11,0),
Created DATE
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE SEQUENCE GROUPS_seq;
CREATE TABLE Groups (
id NUMBER(11,0) PRIMARY KEY,
Name VARCHAR2(16) UNIQUE,
Description VARCHAR(64),
Pseudo NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE WATCHERS_seq;
CREATE TABLE Watchers (
id NUMBER(11,0) PRIMARY KEY,
Type VARCHAR2(16),
Scope VARCHAR2(16),
Value NUMBER(11,0),
Email VARCHAR2(255),
Quiet NUMBER(11,0),
Owner NUMBER(11,0),
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE SEQUENCE SCRIPCONDITIONS_seq;
CREATE TABLE ScripConditions (
id NUMBER(11, 0) PRIMARY KEY,
Name VARCHAR2(255),
Description VARCHAR2(255),
ExecModule VARCHAR2(60),
Argument VARCHAR2(255),
ApplicableTransTypes VARCHAR2(60),
Creator NUMBER(11, 0),
Created DATE,
LastUpdatedBy NUMBER(11, 0),
LastUpdated DATE
);
CREATE SEQUENCE TRANSACTIONS_seq;
CREATE TABLE Transactions (
id NUMBER(11,0) PRIMARY KEY,
EffectiveTicket NUMBER(11,0),
Ticket NUMBER(11,0),
TimeTaken NUMBER(11,0),
Type VARCHAR2(20),
Field VARCHAR2(40),
OldValue VARCHAR2(255),
NewValue VARCHAR2(255),
Data VARCHAR2(100),
Creator NUMBER(11,0),
Created DATE,
Disabled NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE SCRIPS_seq;
CREATE TABLE Scrips (
id NUMBER(11,0) PRIMARY KEY,
ScripCondition NUMBER(11,0),
ScripAction NUMBER(11,0),
Stage VARCHAR2(32),
Queue NUMBER(11,0),
Template NUMBER(11,0),
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE SEQUENCE ACL_seq;
CREATE TABLE ACL (
id NUMBER(11,0) PRIMARY KEY,
PrincipalId NUMBER(11,0),
PrincipalType VARCHAR2(25),
RightName VARCHAR2(25),
RightScope VARCHAR2(25),
RightAppliesTo NUMBER(11,0)
);
CREATE SEQUENCE GROUPMEMBERS_seq;
CREATE TABLE GroupMembers (
id NUMBER(11,0) PRIMARY KEY,
GroupId NUMBER(11,0),
UserId NUMBER(11,0)
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
CREATE SEQUENCE OBJECTKEYWORDS_seq;
CREATE TABLE ObjectKeywords (
id NUMBER(11,0) PRIMARY KEY,
Keyword NUMBER(11,0) NOT NULL,
KeywordSelect NUMBER(11,0) NOT NULL,
ObjectType VARCHAR2(32) NOT NULL,
ObjectId NUMBER(11,0) NOT NULL
);
CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords
(ObjectId, ObjectType, KeywordSelect, Keyword);
CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
CREATE SEQUENCE KEYWORDS_seq;
CREATE TABLE Keywords (
id NUMBER(11, 0) PRIMARY KEY,
Name VARCHAR2(255) NOT NULL,
Description VARCHAR2(255),
Parent NUMBER(11, 0),
Disabled NUMBER(11, 0) DEFAULT 0
);
CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
CREATE INDEX Keywords3 ON Keywords (Parent);
CREATE SEQUENCE USERS_seq;
CREATE TABLE Users (
id NUMBER(11,0) PRIMARY KEY,
Name VARCHAR2(120) NOT NULL UNIQUE,
Password VARCHAR2(40),
Comments CLOB,
Signature CLOB,
EmailAddress VARCHAR2(120),
FreeFormContactInfo CLOB,
Organization VARCHAR2(200),
Privileged NUMBER(11,0),
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),
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Disabled NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE TICKETS_seq;
CREATE TABLE Tickets (
id NUMBER(11, 0) PRIMARY KEY,
EffectiveId NUMBER(11, 0),
Queue NUMBER(11,0),
Type VARCHAR2(16),
IssueStatement NUMBER(11,0),
Resolution NUMBER(11,0),
Owner NUMBER(11,0),
Subject VARCHAR2(200) DEFAULT '',
InitialPriority NUMBER(11,0) DEFAULT 0,
FinalPriority NUMBER(11,0) DEFAULT 0,
Priority NUMBER(11,0) DEFAULT 0,
Status VARCHAR2(10),
TimeWorked NUMBER(11,0) DEFAULT 0,
TimeLeft NUMBER(11,0) DEFAULT 0,
Told DATE,
Starts DATE,
Started DATE,
Due DATE,
Resolved DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Creator NUMBER(11,0),
Created DATE,
Disabled NUMBER(11,0) DEFAULT 0
);
CREATE SEQUENCE SCRIPACTIONS_seq;
CREATE TABLE ScripActions (
id NUMBER(11,0) PRIMARY KEY,
Name VARCHAR2(255),
Description VARCHAR2(255),
ExecModule VARCHAR2(60),
Argument VARCHAR2(255),
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE SEQUENCE TEMPLATES_seq;
CREATE TABLE Templates (
id NUMBER(11,0) PRIMARY KEY,
Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
Name VARCHAR2(40) NOT NULL UNIQUE,
Description VARCHAR2(120),
Type VARCHAR2(16),
Language VARCHAR2(16),
TranslationOf NUMBER(11,0),
Content CLOB,
LastUpdated DATE,
LastUpdatedBy NUMBER(11,0),
Creator NUMBER(11,0),
Created DATE
);
|
