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 serial NOT NULL ,
Name varchar(255) ,
Keyword integer ,
Single integer ,
Depth integer NOT NULL DEFAULT 0 ,
ObjectType varchar(32) NOT NULL ,
ObjectField varchar(32) ,
ObjectValue varchar(255) ,
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 serial NOT NULL ,
TransactionId integer NOT NULL ,
Parent integer ,
MessageId varchar(160) ,
Subject varchar(255) ,
Filename varchar(255) ,
ContentType varchar(80) ,
ContentEncoding varchar(80) ,
Content TEXT ,
Headers TEXT ,
Creator integer ,
Created timestamp ,
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 serial NOT NULL ,
Name varchar(120) NOT NULL ,
Description varchar(120) ,
CorrespondAddress varchar(120) ,
CommentAddress varchar(120) ,
InitialPriority integer ,
FinalPriority integer ,
DefaultDueIn integer ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
Disabled int2 NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Queues1 ON Queues (Name);
CREATE TABLE Links (
id serial NOT NULL ,
Base varchar(240) ,
Target varchar(240) ,
Type varchar(20) NOT NULL ,
LocalTarget integer ,
LocalBase integer ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
Creator integer ,
Created timestamp ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE TABLE Groups (
id serial NOT NULL ,
Name varchar(16) ,
Description varchar(64) ,
Pseudo integer NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Groups1 ON Groups (Name);
CREATE TABLE Watchers (
id serial NOT NULL ,
Type varchar(16) ,
Scope varchar(16) ,
Value integer ,
Email varchar(255) ,
Quiet integer ,
Owner integer ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
PRIMARY KEY (id)
);
CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
CREATE TABLE ScripConditions (
id serial NOT NULL ,
Name varchar(255) ,
Description varchar(255) ,
ExecModule varchar(60) ,
Argument varchar(255) ,
ApplicableTransTypes varchar(60) ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
PRIMARY KEY (id)
);
CREATE TABLE Transactions (
id serial NOT NULL ,
EffectiveTicket integer ,
Ticket integer ,
TimeTaken integer ,
Type varchar(20) ,
Field varchar(40) ,
OldValue varchar(255) ,
NewValue varchar(255) ,
Data varchar(100) ,
Creator integer ,
Created timestamp ,
PRIMARY KEY (id)
);
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
CREATE TABLE Scrips (
id serial NOT NULL ,
ScripCondition integer ,
ScripAction integer ,
Stage varchar(32) ,
Queue integer ,
Template integer ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
PRIMARY KEY (id)
);
CREATE TABLE ACL (
id serial NOT NULL ,
PrincipalId integer ,
PrincipalType varchar(25) ,
RightName varchar(25) ,
RightScope varchar(25) ,
RightAppliesTo integer ,
PRIMARY KEY (id)
);
CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
CREATE TABLE GroupMembers (
id serial NOT NULL ,
GroupId integer ,
UserId integer ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
CREATE TABLE ObjectKeywords (
id serial NOT NULL ,
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 serial NOT NULL ,
Name varchar(255) NOT NULL ,
Description varchar(255) ,
Parent integer ,
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 serial NOT NULL ,
Name varchar(120) NOT NULL ,
Password varchar(40) ,
Comments TEXT ,
Signature TEXT ,
EmailAddress varchar(120) ,
FreeformContactInfo TEXT ,
Organization varchar(200) ,
Privileged integer ,
RealName varchar(120) ,
Nickname varchar(16) ,
Lang varchar(16) ,
EmailEncoding varchar(16) ,
WebEncoding varchar(16) ,
ExternalContactInfoId varchar(100) ,
ContactInfoSystem varchar(30) ,
ExternalAuthId varchar(100) ,
AuthSystem varchar(30) ,
Gecos varchar(16) ,
HomePhone varchar(30) ,
WorkPhone varchar(30) ,
MobilePhone varchar(30) ,
PagerPhone varchar(30) ,
Address1 varchar(200) ,
Address2 varchar(200) ,
City varchar(100) ,
State varchar(100) ,
Zip varchar(16) ,
Country varchar(50) ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
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 serial NOT NULL ,
EffectiveId integer ,
Queue integer ,
Type varchar(16) ,
IssueStatement integer ,
Resolution integer ,
Owner integer ,
Subject varchar(200) DEFAULT '[no subject]' ,
InitialPriority integer ,
FinalPriority integer ,
Priority integer ,
Status varchar(10) ,
TimeWorked integer ,
TimeLeft integer ,
Told timestamp ,
Starts timestamp ,
Started timestamp ,
Due timestamp ,
Resolved timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
Creator integer ,
Created timestamp ,
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 serial NOT NULL ,
Name varchar(255) ,
Description varchar(255) ,
ExecModule varchar(60) ,
Argument varchar(255) ,
Creator integer ,
Created timestamp ,
LastUpdatedBy integer ,
LastUpdated timestamp ,
PRIMARY KEY (id)
);
CREATE TABLE Templates (
id serial NOT NULL ,
Queue integer NOT NULL DEFAULT 0 ,
Name varchar(40) NOT NULL ,
Description varchar(120) ,
Type varchar(16) ,
Language varchar(16) ,
TranslationOf integer ,
Content TEXT ,
LastUpdated timestamp ,
LastUpdatedBy integer ,
Creator integer ,
Created timestamp ,
PRIMARY KEY (id)
);
|
