1 -- This schema was adopted from the oracle schema by
3 -- Version 0.2 Date 2003.10.21
4 -- The work is still in progress
6 CREATE TABLE Attachments (
8 TransactionId INTEGER NOT NULL,
9 Parent INTEGER DEFAULT 0 NOT NULL,
10 MessageId VARCHAR(160),
12 Filename VARCHAR(255),
13 ContentType VARCHAR(80),
14 ContentEncoding VARCHAR(80),
17 Creator INTEGER DEFAULT 0 NOT NULL,
18 Created DATETIME YEAR TO SECOND,
21 CREATE INDEX Attachments1 ON Attachments (Parent);
22 CREATE INDEX Attachments2 ON Attachments (TransactionId);
23 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
28 Name VARCHAR(200) DEFAULT '' NOT NULL,
29 Description VARCHAR(255) DEFAULT NULL,
30 CorrespondAddress VARCHAR(120) DEFAULT NULL,
31 CommentAddress VARCHAR(120) DEFAULT NULL,
32 InitialPriority INTEGER DEFAULT 0 NOT NULL,
33 FinalPriority INTEGER DEFAULT 0 NOT NULL,
34 DefaultDueIn INTEGER DEFAULT 0 NOT NULL,
35 Creator INTEGER DEFAULT 0 NOT NULL,
36 Created DATETIME YEAR TO SECOND,
37 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
38 LastUpdated DATETIME YEAR TO SECOND,
39 Disabled INTEGER DEFAULT 0 NOT NULL,
42 CREATE UNIQUE INDEX Queues1 ON Queues (Name);
43 CREATE INDEX Queues2 ON Queues (Disabled);
48 Base VARCHAR(240) DEFAULT NULL,
49 Target VARCHAR(240) DEFAULT NULL,
50 Type VARCHAR(20) DEFAULT '' NOT NULL,
51 LocalTarget INTEGER DEFAULT 0 NOT NULL,
52 LocalBase INTEGER DEFAULT 0 NOT NULL,
53 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
54 LastUpdated DATETIME YEAR TO SECOND,
55 Creator INTEGER DEFAULT 0 NOT NULL,
56 Created DATETIME YEAR TO SECOND,
59 -- CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
60 CREATE INDEX Links2 ON Links (Base, Type);
61 CREATE INDEX Links3 ON Links (Target, Type);
62 CREATE INDEX Links4 ON Links(Type,LocalBase);
65 CREATE TABLE Principals (
67 PrincipalType VARCHAR(16) DEFAULT '' NOT NULL,
68 ObjectId INTEGER DEFAULT 0,
69 Disabled INTEGER DEFAULT 0 NOT NULL,
72 CREATE INDEX Principals2 ON Principals (ObjectId);
77 Name VARCHAR(200) DEFAULT NULL,
78 Description VARCHAR(255) DEFAULT NULL,
79 Domain VARCHAR(64) DEFAULT '',
80 Type VARCHAR(64) DEFAULT '',
81 Instance INTEGER DEFAULT 0 NOT NULL,
82 -- Instance VARCHAR(64) DEFAULT '' NOT NULL,
85 CREATE INDEX Groups1 ON Groups (Domain, Instance, Type, id);
86 CREATE INDEX Groups2 ON Groups (Type, Instance, Domain);
89 CREATE TABLE ScripConditions (
92 Description VARCHAR(255),
93 ExecModule VARCHAR(60),
94 Argument VARCHAR(255),
95 ApplicableTransTypes VARCHAR(60),
96 Creator INTEGER DEFAULT 0 NOT NULL,
97 Created DATETIME YEAR TO SECOND,
98 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
99 LastUpdated DATETIME YEAR TO SECOND,
104 CREATE TABLE Transactions (
106 EffectiveTicket INTEGER DEFAULT 0 NOT NULL,
107 Ticket INTEGER DEFAULT 0 NOT NULL,
108 TimeTaken INTEGER DEFAULT 0 NOT NULL,
111 OldValue VARCHAR(255),
112 NewValue VARCHAR(255),
114 Creator INTEGER DEFAULT 0 NOT NULL,
115 Created DATETIME YEAR TO SECOND,
118 CREATE INDEX Transactions1 ON Transactions (Ticket);
119 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
122 CREATE TABLE Scrips (
124 Description VARCHAR(255) DEFAULT '',
125 ScripCondition INTEGER DEFAULT 0 NOT NULL,
126 ScripAction INTEGER DEFAULT 0 NOT NULL,
129 CustomIsApplicableCode BYTE,
130 CustomPrepareCode BYTE,
131 CustomCommitCode BYTE,
133 Queue INTEGER DEFAULT 0 NOT NULL,
134 Template INTEGER DEFAULT 0 NOT NULL,
135 Creator INTEGER DEFAULT 0 NOT NULL,
136 Created DATETIME YEAR TO SECOND,
137 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
138 LastUpdated DATETIME YEAR TO SECOND,
145 PrincipalType VARCHAR(25) NOT NULL,
146 PrincipalId INTEGER NOT NULL,
147 RightName VARCHAR(25) NOT NULL,
148 ObjectType VARCHAR(25) NOT NULL,
149 ObjectId INTEGER DEFAULT 0 NOT NULL,
150 DelegatedBy INTEGER DEFAULT 0 NOT NULL,
151 DelegatedFrom INTEGER DEFAULT 0 NOT NULL,
154 CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
157 CREATE TABLE GroupMembers (
159 GroupId INTEGER DEFAULT 0 NOT NULL,
160 MemberId INTEGER DEFAULT 0 NOT NULL,
163 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
166 CREATE TABLE CachedGroupMembers (
168 GroupId INTEGER DEFAULT 0,
169 MemberId INTEGER DEFAULT 0,
170 Via INTEGER DEFAULT 0,
171 ImmediateParentId INTEGER DEFAULT 0,
172 Disabled INTEGER DEFAULT 0 NOT NULL,
175 CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
176 CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
181 Name VARCHAR(200) NOT NULL,
182 Password VARCHAR(40),
185 EmailAddress VARCHAR(120),
186 FreeFormContactInfo BYTE,
187 Organization VARCHAR(200),
188 RealName VARCHAR(120),
189 NickName VARCHAR(16),
191 EmailEncoding VARCHAR(16),
192 WebEncoding VARCHAR(16),
193 ExternalContactInfoId VARCHAR(100),
194 ContactInfoSystem VARCHAR(30),
195 ExternalAuthId VARCHAR(100),
196 AuthSystem VARCHAR(30),
198 HomePhone VARCHAR(30),
199 WorkPhone VARCHAR(30),
200 MobilePhone VARCHAR(30),
201 PagerPhone VARCHAR(30),
202 Address1 VARCHAR(200),
203 Address2 VARCHAR(200),
208 Timezone VARCHAR(50),
210 Creator INTEGER DEFAULT 0 NOT NULL,
211 Created DATETIME YEAR TO SECOND,
212 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
213 LastUpdated DATETIME YEAR TO SECOND,
216 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
217 CREATE INDEX Users2 ON Users (Name);
218 CREATE INDEX Users3 ON Users (id, EmailAddress);
219 CREATE INDEX Users4 ON Users (EmailAddress);
222 CREATE TABLE Tickets (
224 EffectiveId INTEGER DEFAULT 0 NOT NULL,
225 Queue INTEGER DEFAULT 0 NOT NULL,
227 IssueStatement INTEGER DEFAULT 0 NOT NULL,
228 Resolution INTEGER DEFAULT 0 NOT NULL,
229 Owner INTEGER DEFAULT 0 NOT NULL,
230 Subject VARCHAR(200) DEFAULT '[no subject]',
231 InitialPriority INTEGER DEFAULT 0 NOT NULL,
232 FinalPriority INTEGER DEFAULT 0 NOT NULL,
233 Priority INTEGER DEFAULT 0 NOT NULL,
234 TimeEstimated INTEGER DEFAULT 0 NOT NULL,
235 TimeWorked INTEGER DEFAULT 0 NOT NULL,
237 TimeLeft INTEGER DEFAULT 0 NOT NULL,
238 Told DATETIME YEAR TO SECOND,
239 Starts DATETIME YEAR TO SECOND,
240 Started DATETIME YEAR TO SECOND,
241 Due DATETIME YEAR TO SECOND,
242 Resolved DATETIME YEAR TO SECOND,
243 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
244 LastUpdated DATETIME YEAR TO SECOND,
245 Creator INTEGER DEFAULT 0 NOT NULL,
246 Created DATETIME YEAR TO SECOND,
247 Disabled INTEGER DEFAULT 0 NOT NULL,
250 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
251 CREATE INDEX Tickets2 ON Tickets (Owner);
252 CREATE INDEX Tickets3 ON Tickets (EffectiveId);
253 CREATE INDEX Tickets4 ON Tickets (id, Status);
254 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
255 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
258 CREATE TABLE ScripActions (
261 Description VARCHAR(255),
262 ExecModule VARCHAR(60),
263 Argument VARCHAR(255),
264 Creator INTEGER DEFAULT 0 NOT NULL,
265 Created DATETIME YEAR TO SECOND,
266 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
267 LastUpdated DATETIME YEAR TO SECOND,
272 CREATE TABLE Templates (
274 Queue INTEGER DEFAULT 0 NOT NULL,
275 Name VARCHAR(200) NOT NULL,
276 Description VARCHAR(255),
278 Language VARCHAR(16),
279 TranslationOf INTEGER DEFAULT 0 NOT NULL,
281 LastUpdated DATETIME YEAR TO SECOND,
282 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
283 Creator INTEGER DEFAULT 0 NOT NULL,
284 Created DATETIME YEAR TO SECOND,
289 CREATE TABLE TicketCustomFieldValues (
291 Ticket INTEGER NOT NULL,
292 CustomField INTEGER NOT NULL,
293 Content VARCHAR(255),
294 Creator INTEGER DEFAULT 0 NOT NULL,
295 Created DATETIME YEAR TO SECOND,
296 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
297 LastUpdated DATETIME YEAR TO SECOND,
301 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
302 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
304 CREATE TABLE CustomFields (
308 Queue INTEGER DEFAULT 0 NOT NULL,
309 Description VARCHAR(255),
310 SortOrder INTEGER DEFAULT 0 NOT NULL,
311 Creator INTEGER DEFAULT 0 NOT NULL,
312 Created DATETIME YEAR TO SECOND,
313 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
314 LastUpdated DATETIME YEAR TO SECOND,
315 Disabled SMALLINT DEFAULT 0 NOT NULL,
318 CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);
321 CREATE TABLE CustomFieldValues (
323 CustomField INTEGER NOT NULL,
325 Description VARCHAR(255),
326 SortOrder INTEGER DEFAULT 0 NOT NULL,
327 Creator INTEGER DEFAULT 0 NOT NULL,
328 Created DATETIME YEAR TO SECOND,
329 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
330 LastUpdated DATETIME YEAR TO SECOND,
334 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
336 CREATE TABLE Attributes (
338 Name VARCHAR(255) DEFAULT '' NOT NULL,
339 Description VARCHAR(255) DEFAULT NULL,
341 ContentType VARCHAR(16),
342 ObjectType VARCHAR(25) NOT NULL,
343 ObjectId INTEGER DEFAULT 0 NOT NULL,
344 Creator INTEGER DEFAULT 0 NOT NULL,
345 Created DATETIME YEAR TO SECOND,
346 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
347 LastUpdated DATETIME YEAR TO SECOND,
351 CREATE INDEX Attributes1 on Attributes(Name);
352 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
355 CREATE TABLE sessions (
356 id VARCHAR(32) NOT NULL,
358 LastUpdated DATETIME YEAR TO SECOND,