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 ObjectType VARCHAR(255),
107 ObjectId INTEGER DEFAULT 0 NOT NULL,
108 TimeTaken INTEGER DEFAULT 0 NOT NULL,
111 OldValue VARCHAR(255),
112 NewValue VARCHAR(255),
113 ReferenceType VARCHAR(255),
114 OldReference INTEGER DEFAULT 0,
115 NewReference INTEGER DEFAULT 0,
117 Creator INTEGER DEFAULT 0 NOT NULL,
118 Created DATETIME YEAR TO SECOND,
121 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
124 CREATE TABLE Scrips (
126 Description VARCHAR(255) DEFAULT '',
127 ScripCondition INTEGER DEFAULT 0 NOT NULL,
128 ScripAction INTEGER DEFAULT 0 NOT NULL,
131 CustomIsApplicableCode BYTE,
132 CustomPrepareCode BYTE,
133 CustomCommitCode BYTE,
135 Queue INTEGER DEFAULT 0 NOT NULL,
136 Template INTEGER DEFAULT 0 NOT NULL,
137 Creator INTEGER DEFAULT 0 NOT NULL,
138 Created DATETIME YEAR TO SECOND,
139 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
140 LastUpdated DATETIME YEAR TO SECOND,
147 PrincipalType VARCHAR(25) NOT NULL,
148 PrincipalId INTEGER NOT NULL,
149 RightName VARCHAR(25) NOT NULL,
150 ObjectType VARCHAR(25) NOT NULL,
151 ObjectId INTEGER DEFAULT 0 NOT NULL,
152 DelegatedBy INTEGER DEFAULT 0 NOT NULL,
153 DelegatedFrom INTEGER DEFAULT 0 NOT NULL,
156 CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
159 CREATE TABLE GroupMembers (
161 GroupId INTEGER DEFAULT 0 NOT NULL,
162 MemberId INTEGER DEFAULT 0 NOT NULL,
165 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
168 CREATE TABLE CachedGroupMembers (
170 GroupId INTEGER DEFAULT 0,
171 MemberId INTEGER DEFAULT 0,
172 Via INTEGER DEFAULT 0,
173 ImmediateParentId INTEGER DEFAULT 0,
174 Disabled INTEGER DEFAULT 0 NOT NULL,
177 CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
178 CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
183 Name VARCHAR(200) NOT NULL,
184 Password VARCHAR(40),
187 EmailAddress VARCHAR(120),
188 FreeFormContactInfo BYTE,
189 Organization VARCHAR(200),
190 RealName VARCHAR(120),
191 NickName VARCHAR(16),
193 EmailEncoding VARCHAR(16),
194 WebEncoding VARCHAR(16),
195 ExternalContactInfoId VARCHAR(100),
196 ContactInfoSystem VARCHAR(30),
197 ExternalAuthId VARCHAR(100),
198 AuthSystem VARCHAR(30),
200 HomePhone VARCHAR(30),
201 WorkPhone VARCHAR(30),
202 MobilePhone VARCHAR(30),
203 PagerPhone VARCHAR(30),
204 Address1 VARCHAR(200),
205 Address2 VARCHAR(200),
210 Timezone VARCHAR(50),
212 Creator INTEGER DEFAULT 0 NOT NULL,
213 Created DATETIME YEAR TO SECOND,
214 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
215 LastUpdated DATETIME YEAR TO SECOND,
218 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
219 CREATE INDEX Users2 ON Users (Name);
220 CREATE INDEX Users3 ON Users (id, EmailAddress);
221 CREATE INDEX Users4 ON Users (EmailAddress);
224 CREATE TABLE Tickets (
226 EffectiveId INTEGER DEFAULT 0 NOT NULL,
227 Queue INTEGER DEFAULT 0 NOT NULL,
229 IssueStatement INTEGER DEFAULT 0 NOT NULL,
230 Resolution INTEGER DEFAULT 0 NOT NULL,
231 Owner INTEGER DEFAULT 0 NOT NULL,
232 Subject VARCHAR(200) DEFAULT '[no subject]',
233 InitialPriority INTEGER DEFAULT 0 NOT NULL,
234 FinalPriority INTEGER DEFAULT 0 NOT NULL,
235 Priority INTEGER DEFAULT 0 NOT NULL,
236 TimeEstimated INTEGER DEFAULT 0 NOT NULL,
237 TimeWorked INTEGER DEFAULT 0 NOT NULL,
239 TimeLeft INTEGER DEFAULT 0 NOT NULL,
240 Told DATETIME YEAR TO SECOND,
241 Starts DATETIME YEAR TO SECOND,
242 Started DATETIME YEAR TO SECOND,
243 Due DATETIME YEAR TO SECOND,
244 Resolved DATETIME YEAR TO SECOND,
245 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
246 LastUpdated DATETIME YEAR TO SECOND,
247 Creator INTEGER DEFAULT 0 NOT NULL,
248 Created DATETIME YEAR TO SECOND,
249 Disabled INTEGER DEFAULT 0 NOT NULL,
252 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
253 CREATE INDEX Tickets2 ON Tickets (Owner);
254 CREATE INDEX Tickets3 ON Tickets (EffectiveId);
255 CREATE INDEX Tickets4 ON Tickets (id, Status);
256 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
257 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
260 CREATE TABLE ScripActions (
263 Description VARCHAR(255),
264 ExecModule VARCHAR(60),
265 Argument VARCHAR(255),
266 Creator INTEGER DEFAULT 0 NOT NULL,
267 Created DATETIME YEAR TO SECOND,
268 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
269 LastUpdated DATETIME YEAR TO SECOND,
274 CREATE TABLE Templates (
276 Queue INTEGER DEFAULT 0 NOT NULL,
277 Name VARCHAR(200) NOT NULL,
278 Description VARCHAR(255),
280 Language VARCHAR(16),
281 TranslationOf INTEGER DEFAULT 0 NOT NULL,
283 LastUpdated DATETIME YEAR TO SECOND,
284 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
285 Creator INTEGER DEFAULT 0 NOT NULL,
286 Created DATETIME YEAR TO SECOND,
291 CREATE TABLE TicketCustomFieldValues (
293 Ticket INTEGER NOT NULL,
294 CustomField INTEGER NOT NULL,
295 Content VARCHAR(255),
296 Creator INTEGER DEFAULT 0 NOT NULL,
297 Created DATETIME YEAR TO SECOND,
298 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
299 LastUpdated DATETIME YEAR TO SECOND,
303 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
304 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
306 CREATE TABLE CustomFields (
310 MaxValues INTEGER DEFAULT 0 NOT NULL,
311 Pattern VARCHAR(255),
312 LookupType VARCHAR(255),
313 Description VARCHAR(255),
314 SortOrder INTEGER DEFAULT 0 NOT NULL,
315 Creator INTEGER DEFAULT 0 NOT NULL,
316 Created DATETIME YEAR TO SECOND,
317 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
318 LastUpdated DATETIME YEAR TO SECOND,
319 Disabled SMALLINT DEFAULT 0 NOT NULL,
324 CREATE TABLE CustomFieldValues (
326 CustomField INTEGER NOT NULL,
328 Description VARCHAR(255),
329 SortOrder INTEGER DEFAULT 0 NOT NULL,
330 Creator INTEGER DEFAULT 0 NOT NULL,
331 Created DATETIME YEAR TO SECOND,
332 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
333 LastUpdated DATETIME YEAR TO SECOND,
337 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
339 CREATE TABLE Attributes (
341 Name VARCHAR(255) DEFAULT '' NOT NULL,
342 Description VARCHAR(255) DEFAULT NULL,
344 ContentType VARCHAR(16),
345 ObjectType VARCHAR(25) NOT NULL,
346 ObjectId INTEGER DEFAULT 0 NOT NULL,
347 Creator INTEGER DEFAULT 0 NOT NULL,
348 Created DATETIME YEAR TO SECOND,
349 LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
350 LastUpdated DATETIME YEAR TO SECOND,
354 CREATE INDEX Attributes1 on Attributes(Name);
355 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
358 CREATE TABLE sessions (
359 id VARCHAR(32) NOT NULL,
361 LastUpdated DATETIME YEAR TO SECOND,