1 CREATE TABLE KeywordSelects (
2 id INTEGER NOT NULL AUTO_INCREMENT,
3 Name varchar(255) NULL ,
6 Depth integer NOT NULL DEFAULT 0 ,
7 ObjectType varchar(32) NOT NULL ,
8 ObjectField varchar(32) NULL ,
9 ObjectValue varchar(255) NULL ,
10 Disabled int2 NOT NULL DEFAULT 0 ,
13 CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
14 CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue);
15 CREATE TABLE Attachments (
16 id INTEGER NOT NULL AUTO_INCREMENT,
17 TransactionId integer NOT NULL ,
19 MessageId varchar(160) NULL ,
20 Subject varchar(255) NULL ,
21 Filename varchar(255) NULL ,
22 ContentType varchar(80) NULL ,
23 ContentEncoding varchar(80) NULL ,
24 Content LONGTEXT NULL ,
25 Headers LONGTEXT NULL ,
26 Creator integer NULL ,
27 Created DATETIME NULL ,
30 CREATE INDEX Attachments1 ON Attachments (Parent);
31 CREATE INDEX Attachments2 ON Attachments (TransactionId);
32 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
34 id INTEGER NOT NULL AUTO_INCREMENT,
35 Name varchar(120) NOT NULL ,
36 Description varchar(120) NULL ,
37 CorrespondAddress varchar(120) NULL ,
38 CommentAddress varchar(120) NULL ,
39 InitialPriority integer NULL ,
40 FinalPriority integer NULL ,
41 DefaultDueIn integer NULL ,
42 Creator integer NULL ,
43 Created DATETIME NULL ,
44 LastUpdatedBy integer NULL ,
45 LastUpdated DATETIME NULL ,
46 Disabled int2 NOT NULL DEFAULT 0 ,
49 CREATE UNIQUE INDEX Queues1 ON Queues (Name);
51 id INTEGER NOT NULL AUTO_INCREMENT,
52 Base varchar(240) NULL ,
53 Target varchar(240) NULL ,
54 Type varchar(20) NOT NULL ,
55 LocalTarget integer NULL ,
56 LocalBase integer NULL ,
57 LastUpdatedBy integer NULL ,
58 LastUpdated DATETIME NULL ,
59 Creator integer NULL ,
60 Created DATETIME NULL ,
63 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
65 id INTEGER NOT NULL AUTO_INCREMENT,
66 Name varchar(16) NULL ,
67 Description varchar(64) NULL ,
68 Pseudo integer NOT NULL DEFAULT 0 ,
71 CREATE UNIQUE INDEX Groups1 ON Groups (Name);
72 CREATE TABLE Watchers (
73 id INTEGER NOT NULL AUTO_INCREMENT,
74 Type varchar(16) NULL ,
75 Scope varchar(16) NULL ,
77 Email varchar(255) NULL ,
80 Creator integer NULL ,
81 Created DATETIME NULL ,
82 LastUpdatedBy integer NULL ,
83 LastUpdated DATETIME NULL ,
86 CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
87 CREATE TABLE ScripConditions (
88 id INTEGER NOT NULL AUTO_INCREMENT,
89 Name varchar(255) NULL ,
90 Description varchar(255) NULL ,
91 ExecModule varchar(60) NULL ,
92 Argument varchar(255) NULL ,
93 ApplicableTransTypes varchar(60) NULL ,
94 Creator integer NULL ,
95 Created DATETIME NULL ,
96 LastUpdatedBy integer NULL ,
97 LastUpdated DATETIME NULL ,
100 CREATE TABLE Transactions (
101 id INTEGER NOT NULL AUTO_INCREMENT,
102 EffectiveTicket integer NULL ,
103 Ticket integer NULL ,
104 TimeTaken integer NULL ,
105 Type varchar(20) NULL ,
106 Field varchar(40) NULL ,
107 OldValue varchar(255) NULL ,
108 NewValue varchar(255) NULL ,
109 Data varchar(100) NULL ,
110 Creator integer NULL ,
111 Created DATETIME NULL ,
114 CREATE INDEX Transactions1 ON Transactions (Ticket);
115 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
116 CREATE TABLE Scrips (
117 id INTEGER NOT NULL AUTO_INCREMENT,
118 ScripCondition integer NULL ,
119 ScripAction integer NULL ,
120 Stage varchar(32) NULL ,
122 Template integer NULL ,
123 Creator integer NULL ,
124 Created DATETIME NULL ,
125 LastUpdatedBy integer NULL ,
126 LastUpdated DATETIME NULL ,
130 id INTEGER NOT NULL AUTO_INCREMENT,
131 PrincipalId integer NULL ,
132 PrincipalType varchar(25) NULL ,
133 RightName varchar(25) NULL ,
134 RightScope varchar(25) NULL ,
135 RightAppliesTo integer NULL ,
138 CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
139 CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
140 CREATE TABLE GroupMembers (
141 id INTEGER NOT NULL AUTO_INCREMENT,
142 GroupId integer NULL ,
143 UserId integer NULL ,
146 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
147 CREATE TABLE ObjectKeywords (
148 id INTEGER NOT NULL AUTO_INCREMENT,
149 Keyword integer NOT NULL ,
150 KeywordSelect integer NOT NULL ,
151 ObjectType varchar(32) NOT NULL ,
152 ObjectId integer NOT NULL ,
155 CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword);
156 CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType);
157 CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
158 CREATE TABLE Keywords (
159 id INTEGER NOT NULL AUTO_INCREMENT,
160 Name varchar(255) NOT NULL ,
161 Description varchar(255) NULL ,
162 Parent integer NULL ,
163 Disabled int2 NOT NULL DEFAULT 0 ,
166 CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
167 CREATE INDEX Keywords2 ON Keywords (Name);
168 CREATE INDEX Keywords3 ON Keywords (Parent);
170 id INTEGER NOT NULL AUTO_INCREMENT,
171 Name varchar(120) NOT NULL ,
172 Password varchar(40) NULL ,
174 Signature blob NULL ,
175 EmailAddress varchar(120) NULL ,
176 FreeformContactInfo blob NULL ,
177 Organization varchar(200) NULL ,
178 Privileged integer NULL ,
179 RealName varchar(120) NULL ,
180 Nickname varchar(16) NULL ,
181 Lang varchar(16) NULL ,
182 EmailEncoding varchar(16) NULL ,
183 WebEncoding varchar(16) NULL ,
184 ExternalContactInfoId varchar(100) NULL ,
185 ContactInfoSystem varchar(30) NULL ,
186 ExternalAuthId varchar(100) NULL ,
187 AuthSystem varchar(30) NULL ,
188 Gecos varchar(16) NULL ,
189 HomePhone varchar(30) NULL ,
190 WorkPhone varchar(30) NULL ,
191 MobilePhone varchar(30) NULL ,
192 PagerPhone varchar(30) NULL ,
193 Address1 varchar(200) NULL ,
194 Address2 varchar(200) NULL ,
195 City varchar(100) NULL ,
196 State varchar(100) NULL ,
197 Zip varchar(16) NULL ,
198 Country varchar(50) NULL ,
199 Creator integer NULL ,
200 Created DATETIME NULL ,
201 LastUpdatedBy integer NULL ,
202 LastUpdated DATETIME NULL ,
203 Disabled int2 NOT NULL DEFAULT 0 ,
206 CREATE UNIQUE INDEX Users1 ON Users (Name);
207 CREATE INDEX Users3 ON Users (id, EmailAddress);
208 CREATE INDEX Users4 ON Users (EmailAddress);
209 CREATE TABLE Tickets (
210 id INTEGER NOT NULL AUTO_INCREMENT,
211 EffectiveId integer NULL ,
213 Type varchar(16) NULL ,
214 IssueStatement integer NULL ,
215 Resolution integer NULL ,
217 Subject varchar(200) NULL DEFAULT '[no subject]' ,
218 InitialPriority integer NULL ,
219 FinalPriority integer NULL ,
220 Priority integer NULL ,
221 Status varchar(10) NULL ,
222 TimeWorked integer NULL ,
223 TimeLeft integer NULL ,
225 Starts DATETIME NULL ,
226 Started DATETIME NULL ,
228 Resolved DATETIME NULL ,
229 LastUpdatedBy integer NULL ,
230 LastUpdated DATETIME NULL ,
231 Creator integer NULL ,
232 Created DATETIME NULL ,
233 Disabled int2 NOT NULL DEFAULT 0 ,
236 CREATE INDEX Tickets1 ON Tickets (Queue, Status);
237 CREATE INDEX Tickets2 ON Tickets (Owner);
238 CREATE INDEX Tickets3 ON Tickets (EffectiveId);
239 CREATE INDEX Tickets4 ON Tickets (id, Status);
240 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
241 CREATE TABLE ScripActions (
242 id INTEGER NOT NULL AUTO_INCREMENT,
243 Name varchar(255) NULL ,
244 Description varchar(255) NULL ,
245 ExecModule varchar(60) NULL ,
246 Argument varchar(255) NULL ,
247 Creator integer NULL ,
248 Created DATETIME NULL ,
249 LastUpdatedBy integer NULL ,
250 LastUpdated DATETIME NULL ,
253 CREATE TABLE Templates (
254 id INTEGER NOT NULL AUTO_INCREMENT,
255 Queue integer NOT NULL DEFAULT 0 ,
256 Name varchar(40) NOT NULL ,
257 Description varchar(120) NULL ,
258 Type varchar(16) NULL ,
259 Language varchar(16) NULL ,
260 TranslationOf integer NULL ,
262 LastUpdated DATETIME NULL ,
263 LastUpdatedBy integer NULL ,
264 Creator integer NULL ,
265 Created DATETIME NULL ,