1 CREATE SEQUENCE ATTACHMENTS_seq;
2 CREATE TABLE Attachments (
4 CONSTRAINT Attachments_Key PRIMARY KEY,
5 TransactionId NUMBER(11,0) NOT NULL,
6 Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
7 MessageId VARCHAR2(160),
9 Filename VARCHAR2(255),
10 ContentType VARCHAR2(80),
11 ContentEncoding VARCHAR2(80),
14 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
17 CREATE INDEX Attachments2 ON Attachments (TransactionId);
18 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
21 CREATE SEQUENCE QUEUES_seq;
24 CONSTRAINT Queues_Key PRIMARY KEY,
25 Name VARCHAR2(200) CONSTRAINT Queues_Name_Unique UNIQUE NOT NULL,
26 Description VARCHAR2(255),
27 CorrespondAddress VARCHAR2(120),
28 CommentAddress VARCHAR2(120),
29 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
30 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
31 DefaultDueIn NUMBER(11,0) DEFAULT 0 NOT NULL,
32 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
34 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
36 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
38 CREATE INDEX Queues1 ON Queues (lower(Name));
39 CREATE INDEX Queues2 ON Queues (Disabled);
42 CREATE SEQUENCE LINKS_seq;
45 CONSTRAINT Links_Key PRIMARY KEY,
48 Type VARCHAR2(20) NOT NULL,
49 LocalTarget NUMBER(11,0) DEFAULT 0 NOT NULL,
50 LocalBase NUMBER(11,0) DEFAULT 0 NOT NULL,
51 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
53 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
56 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
57 CREATE INDEX Links2 ON Links (Base, Type);
58 CREATE INDEX Links3 ON Links (Target, Type);
59 CREATE INDEX Links4 ON Links(Type,LocalBase);
62 CREATE SEQUENCE PRINCIPALS_seq;
63 CREATE TABLE Principals (
65 CONSTRAINT Principals_Key PRIMARY KEY,
66 PrincipalType VARCHAR2(16),
67 ObjectId NUMBER(11,0),
68 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
70 CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId);
73 CREATE SEQUENCE GROUPS_seq;
76 CONSTRAINT Groups_Key PRIMARY KEY,
78 Description VARCHAR2(255),
81 Instance NUMBER(11,0) DEFAULT 0 -- NOT NULL
82 -- Instance VARCHAR2(64)
84 CREATE INDEX Groups1 ON Groups (lower( Domain), Instance, lower(Type), id);
85 CREATE INDEX Groups2 ON Groups (lower(Type), Instance, lower(Domain));
88 CREATE SEQUENCE SCRIPCONDITIONS_seq;
89 CREATE TABLE ScripConditions (
91 CONSTRAINT ScripConditions_Key PRIMARY KEY,
93 Description VARCHAR2(255),
94 ExecModule VARCHAR2(60),
95 Argument VARCHAR2(255),
96 ApplicableTransTypes VARCHAR2(60),
97 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
99 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
104 CREATE SEQUENCE TRANSACTIONS_seq;
105 CREATE TABLE Transactions (
107 CONSTRAINT Transactions_Key PRIMARY KEY,
108 EffectiveTicket NUMBER(11,0) DEFAULT 0 NOT NULL,
109 Ticket NUMBER(11,0) DEFAULT 0 NOT NULL,
110 TimeTaken NUMBER(11,0) DEFAULT 0 NOT NULL,
113 OldValue VARCHAR2(255),
114 NewValue VARCHAR2(255),
116 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
119 CREATE INDEX Transactions1 ON Transactions (Ticket);
120 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
123 CREATE SEQUENCE SCRIPS_seq;
124 CREATE TABLE Scrips (
126 CONSTRAINT Scrips_Key PRIMARY KEY,
127 Description VARCHAR2(255),
128 ScripCondition NUMBER(11,0) DEFAULT 0 NOT NULL,
129 ScripAction NUMBER(11,0) DEFAULT 0 NOT NULL,
132 CustomIsApplicableCode CLOB,
133 CustomPrepareCode CLOB,
134 CustomCommitCode CLOB,
136 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
137 Template NUMBER(11,0) DEFAULT 0 NOT NULL,
138 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
140 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
145 CREATE SEQUENCE ACL_seq;
148 CONSTRAINT ACL_Key PRIMARY KEY,
149 PrincipalType VARCHAR2(25) NOT NULL,
150 PrincipalId NUMBER(11,0) NOT NULL,
151 RightName VARCHAR2(25) NOT NULL,
152 ObjectType VARCHAR2(25) NOT NULL,
153 ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
154 DelegatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
155 DelegatedFrom NUMBER(11,0) DEFAULT 0 NOT NULL
157 CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
160 CREATE SEQUENCE GROUPMEMBERS_seq;
161 CREATE TABLE GroupMembers (
163 CONSTRAINT GroupMembers_Key PRIMARY KEY,
164 GroupId NUMBER(11,0) DEFAULT 0 NOT NULL,
165 MemberId NUMBER(11,0) DEFAULT 0 NOT NULL
167 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
170 CREATE SEQUENCE CachedGroupMembers_seq;
171 CREATE TABLE CachedGroupMembers (
173 CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
174 GroupId NUMBER(11,0),
175 MemberId NUMBER(11,0),
177 ImmediateParentId NUMBER(11,0),
178 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
180 CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
181 CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
184 CREATE SEQUENCE USERS_seq;
187 CONSTRAINT Users_Key PRIMARY KEY,
188 Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
190 Password VARCHAR2(40),
193 EmailAddress VARCHAR2(120),
194 FreeFormContactInfo CLOB,
195 Organization VARCHAR2(200),
196 RealName VARCHAR2(120),
197 NickName VARCHAR2(16),
199 EmailEncoding VARCHAR2(16),
200 WebEncoding VARCHAR2(16),
201 ExternalContactInfoId VARCHAR2(100),
202 ContactInfoSystem VARCHAR2(30),
203 ExternalAuthId VARCHAR2(100),
204 AuthSystem VARCHAR2(30),
206 HomePhone VARCHAR2(30),
207 WorkPhone VARCHAR2(30),
208 MobilePhone VARCHAR2(30),
209 PagerPhone VARCHAR2(30),
210 Address1 VARCHAR2(200),
211 Address2 VARCHAR2(200),
215 Country VARCHAR2(50),
216 Timezone VARCHAR2(50),
218 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
220 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
223 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
225 CREATE INDEX Users2 ON Users( LOWER(name));
226 CREATE INDEX Users4 ON Users (lower(EmailAddress));
229 CREATE SEQUENCE TICKETS_seq;
230 CREATE TABLE Tickets (
232 CONSTRAINT Tickets_Key PRIMARY KEY,
233 EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
234 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
236 IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
237 Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
238 Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
239 Subject VARCHAR2(200) DEFAULT '[no subject]',
240 InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
241 FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
242 Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
243 TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
244 TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
246 TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
252 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
254 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
256 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
258 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
259 CREATE INDEX Tickets2 ON Tickets (Owner);
260 CREATE INDEX Tickets4 ON Tickets (id, Status);
261 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
262 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
265 CREATE SEQUENCE SCRIPACTIONS_seq;
266 CREATE TABLE ScripActions (
268 CONSTRAINT ScripActions_Key PRIMARY KEY,
270 Description VARCHAR2(255),
271 ExecModule VARCHAR2(60),
272 Argument VARCHAR2(255),
273 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
275 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
280 CREATE SEQUENCE TEMPLATES_seq;
281 CREATE TABLE Templates (
283 CONSTRAINT Templates_Key PRIMARY KEY,
284 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
285 Name VARCHAR2(200) NOT NULL,
286 Description VARCHAR2(255),
288 Language VARCHAR2(16),
289 TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
292 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
293 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
298 CREATE SEQUENCE TICKETCUSTOMFIELDVALUES_seq;
299 CREATE TABLE TicketCustomFieldValues (
301 CONSTRAINT TicketCustomFieldValues_Key PRIMARY KEY,
303 CustomField NUMBER(11,0) NOT NULL,
304 Content VARCHAR2(255),
305 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
307 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
311 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
312 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
314 CREATE SEQUENCE CUSTOMFIELDS_seq;
315 CREATE TABLE CustomFields (
317 CONSTRAINT CustomFields_Key PRIMARY KEY,
320 Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
321 Description VARCHAR2(255),
322 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
323 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
325 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
327 Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
329 CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);
332 CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
333 CREATE TABLE CustomFieldValues (
335 CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
336 CustomField NUMBER(11,0),
338 Description VARCHAR2(255),
339 SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
340 Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
342 LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
346 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
348 CREATE TABLE sessions (
350 CONSTRAINT Sessions_Key PRIMARY KEY,