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