3 CREATE TABLE Attachments (
4 id INTEGER PRIMARY KEY ,
5 TransactionId INTEGER ,
7 MessageId varchar(160) NULL ,
8 Subject varchar(255) NULL ,
9 Filename varchar(255) NULL ,
10 ContentType varchar(80) NULL ,
11 ContentEncoding varchar(80) NULL ,
12 Content LONGTEXT NULL ,
13 Headers LONGTEXT NULL ,
14 Creator integer NULL ,
19 CREATE INDEX Attachments1 ON Attachments (Parent) ;
20 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
21 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
26 id INTEGER PRIMARY KEY ,
27 Name varchar(200) NOT NULL ,
28 Description varchar(255) NULL ,
29 CorrespondAddress varchar(120) NULL ,
30 CommentAddress varchar(120) NULL ,
31 InitialPriority integer NULL ,
32 FinalPriority integer NULL ,
33 DefaultDueIn integer NULL ,
34 Creator integer NULL ,
35 Created DATETIME NULL ,
36 LastUpdatedBy integer NULL ,
37 LastUpdated DATETIME NULL ,
38 Disabled int2 NOT NULL DEFAULT 0
41 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
48 id INTEGER PRIMARY KEY ,
49 Base varchar(240) NULL ,
50 Target varchar(240) NULL ,
51 Type varchar(20) NOT NULL ,
52 LocalTarget integer NULL ,
53 LocalBase integer NULL ,
54 LastUpdatedBy integer NULL ,
55 LastUpdated DATETIME NULL ,
56 Creator integer NULL ,
60 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
66 CREATE TABLE Principals (
67 id INTEGER PRIMARY KEY,
68 PrincipalType VARCHAR(16) not null,
70 Disabled int2 NOT NULL DEFAULT 0
80 Name varchar(200) NULL ,
81 Description varchar(255) NULL ,
88 CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;
92 --- {{{ ScripConditions
94 CREATE TABLE ScripConditions (
95 id INTEGER PRIMARY KEY ,
96 Name varchar(200) NULL ,
97 Description varchar(255) NULL ,
98 ExecModule varchar(60) NULL ,
99 Argument varchar(255) NULL ,
100 ApplicableTransTypes varchar(60) NULL ,
102 Creator integer NULL ,
103 Created DATETIME NULL ,
104 LastUpdatedBy integer NULL ,
105 LastUpdated DATETIME NULL
112 CREATE TABLE Transactions (
113 id INTEGER PRIMARY KEY ,
114 EffectiveTicket integer NULL ,
115 Ticket integer NULL ,
116 TimeTaken integer NULL ,
117 Type varchar(20) NULL ,
118 Field varchar(40) NULL ,
119 OldValue varchar(255) NULL ,
120 NewValue varchar(255) NULL ,
121 Data varchar(100) NULL ,
123 Creator integer NULL ,
124 Created DATETIME NULL
127 CREATE INDEX Transactions1 ON Transactions (Ticket);
128 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
134 CREATE TABLE Scrips (
135 id INTEGER PRIMARY KEY ,
136 Description varchar(255),
137 ScripCondition integer NULL ,
138 ScripAction integer NULL ,
139 ConditionRules text NULL ,
140 ActionRules text NULL ,
141 CustomIsApplicableCode text NULL ,
142 CustomPrepareCode text NULL ,
143 CustomCommitCode text NULL ,
144 Stage varchar(32) NULL ,
146 Template integer NULL ,
147 Creator integer NULL ,
148 Created DATETIME NULL ,
149 LastUpdatedBy integer NULL ,
150 LastUpdated DATETIME NULL
158 id INTEGER PRIMARY KEY ,
159 PrincipalType varchar(25) NOT NULL,
162 RightName varchar(25) NOT NULL ,
163 ObjectType varchar(25) NOT NULL ,
164 ObjectId INTEGER default 0,
165 DelegatedBy integer NOT NULL default 0,
166 DelegatedFrom integer NOT NULL default 0
175 CREATE TABLE GroupMembers (
176 id INTEGER PRIMARY KEY ,
177 GroupId integer NULL,
178 MemberId integer NULL
184 --- {{{ CachedGroupMembers
186 create table CachedGroupMembers (
187 id integer primary key ,
191 ImmediateParentId int,
192 Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
193 # group or this group is disabled, this will be set to 1
194 # this allows us to not find members of disabled subgroups when listing off
195 # group members recursively.
196 # Also, this allows us to have the ACL system elide members of disabled groups
207 Name varchar(200) NOT NULL ,
208 Password varchar(40) NULL ,
210 Signature blob NULL ,
211 EmailAddress varchar(120) NULL ,
212 FreeformContactInfo blob NULL ,
213 Organization varchar(200) NULL ,
214 RealName varchar(120) NULL ,
215 NickName varchar(16) NULL ,
216 Lang varchar(16) NULL ,
217 EmailEncoding varchar(16) NULL ,
218 WebEncoding varchar(16) NULL ,
219 ExternalContactInfoId varchar(100) NULL ,
220 ContactInfoSystem varchar(30) NULL ,
221 ExternalAuthId varchar(100) NULL ,
222 AuthSystem varchar(30) NULL ,
223 Gecos varchar(16) NULL ,
224 HomePhone varchar(30) NULL ,
225 WorkPhone varchar(30) NULL ,
226 MobilePhone varchar(30) NULL ,
227 PagerPhone varchar(30) NULL ,
228 Address1 varchar(200) NULL ,
229 Address2 varchar(200) NULL ,
230 City varchar(100) NULL ,
231 State varchar(100) NULL ,
232 Zip varchar(16) NULL ,
233 Country varchar(50) NULL ,
234 Timezone char(50) NULL ,
237 Creator integer NULL ,
238 Created DATETIME NULL ,
239 LastUpdatedBy integer NULL ,
240 LastUpdated DATETIME NULL
245 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
246 CREATE INDEX Users2 ON Users (Name);
247 CREATE INDEX Users3 ON Users (id, EmailAddress);
248 CREATE INDEX Users4 ON Users (EmailAddress);
255 CREATE TABLE Tickets (
256 id INTEGER PRIMARY KEY ,
257 EffectiveId integer NULL ,
259 Type varchar(16) NULL ,
260 IssueStatement integer NULL ,
261 Resolution integer NULL ,
263 Subject varchar(200) NULL DEFAULT '[no subject]' ,
264 InitialPriority integer NULL ,
265 FinalPriority integer NULL ,
266 Priority integer NULL ,
267 TimeEstimated integer NULL ,
268 TimeWorked integer NULL ,
269 Status varchar(10) NULL ,
270 TimeLeft integer NULL ,
272 Starts DATETIME NULL ,
273 Started DATETIME NULL ,
275 Resolved DATETIME NULL ,
278 LastUpdatedBy integer NULL ,
279 LastUpdated DATETIME NULL ,
280 Creator integer NULL ,
281 Created DATETIME NULL ,
282 Disabled int2 NOT NULL DEFAULT 0
286 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
287 CREATE INDEX Tickets2 ON Tickets (Owner) ;
288 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
289 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
290 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
296 CREATE TABLE ScripActions (
297 id INTEGER PRIMARY KEY ,
298 Name varchar(200) NULL ,
299 Description varchar(255) NULL ,
300 ExecModule varchar(60) NULL ,
301 Argument varchar(255) NULL ,
302 Creator integer NULL ,
303 Created DATETIME NULL ,
304 LastUpdatedBy integer NULL ,
305 LastUpdated DATETIME NULL
313 CREATE TABLE Templates (
314 id INTEGER PRIMARY KEY ,
315 Queue integer NOT NULL DEFAULT 0 ,
316 Name varchar(200) NOT NULL ,
317 Description varchar(255) NULL ,
318 Type varchar(16) NULL ,
319 Language varchar(16) NULL ,
320 TranslationOf integer NULL ,
322 LastUpdated DATETIME NULL ,
323 LastUpdatedBy integer NULL ,
324 Creator integer NULL ,
325 Created DATETIME NULL
331 --- {{{ TicketCustomFieldValues
333 CREATE TABLE TicketCustomFieldValues (
334 id INTEGER PRIMARY KEY ,
335 Ticket int NOT NULL ,
336 CustomField int NOT NULL ,
337 Content varchar(255) NULL ,
339 Creator integer NULL ,
340 Created DATETIME NULL ,
341 LastUpdatedBy integer NULL ,
342 LastUpdated DATETIME NULL
350 CREATE TABLE CustomFields (
351 id INTEGER PRIMARY KEY ,
352 Name varchar(200) NULL ,
353 Type varchar(200) NULL ,
355 Description varchar(255) NULL ,
356 SortOrder integer NULL ,
358 Creator integer NULL ,
359 Created DATETIME NULL ,
360 LastUpdatedBy integer NULL ,
361 LastUpdated DATETIME NULL ,
362 Disabled int2 NOT NULL DEFAULT 0
368 --- {{{ CustomFieldValues
370 CREATE TABLE CustomFieldValues (
371 id INTEGER PRIMARY KEY ,
372 CustomField int NOT NULL ,
373 Name varchar(200) NULL ,
374 Description varchar(255) NULL ,
375 SortOrder integer NULL ,
377 Creator integer NULL ,
378 Created DATETIME NULL ,
379 LastUpdatedBy integer NULL ,
380 LastUpdated DATETIME NULL