1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 from trac.db.schema import Table, Column, Index
18
19
20 db_version = 45
21
23 """Utility function used to create report data in same syntax as the
24 default data. This extra step is done to simplify editing the default
25 reports."""
26 result = []
27 for report in reports:
28 result.append((None, report[0], report[2], report[1]))
29 return result
30
31
32
33
34
35
36 schema = [
37
38 Table('system', key='name')[
39 Column('name'),
40 Column('value')],
41 Table('permission', key=('username', 'action'))[
42 Column('username'),
43 Column('action')],
44 Table('auth_cookie', key=('cookie', 'ipnr', 'name'))[
45 Column('cookie'),
46 Column('name'),
47 Column('ipnr'),
48 Column('time', type='int')],
49 Table('session', key=('sid', 'authenticated'))[
50 Column('sid'),
51 Column('authenticated', type='int'),
52 Column('last_visit', type='int'),
53 Index(['last_visit']),
54 Index(['authenticated'])],
55 Table('session_attribute', key=('sid', 'authenticated', 'name'))[
56 Column('sid'),
57 Column('authenticated', type='int'),
58 Column('name'),
59 Column('value')],
60 Table('cache', key='id')[
61 Column('id', type='int'),
62 Column('generation', type='int'),
63 Column('key')],
64
65
66 Table('attachment', key=('type', 'id', 'filename'))[
67 Column('type'),
68 Column('id'),
69 Column('filename'),
70 Column('size', type='int'),
71 Column('time', type='int64'),
72 Column('description'),
73 Column('author')],
74
75
76 Table('wiki', key=('name', 'version'))[
77 Column('name'),
78 Column('version', type='int'),
79 Column('time', type='int64'),
80 Column('author'),
81 Column('text'),
82 Column('comment'),
83 Column('readonly', type='int'),
84 Index(['time'])],
85
86
87 Table('repository', key=('id', 'name'))[
88 Column('id', type='int'),
89 Column('name'),
90 Column('value')],
91 Table('revision', key=('repos', 'rev'))[
92 Column('repos', type='int'),
93 Column('rev', key_size=40),
94 Column('time', type='int64'),
95 Column('author'),
96 Column('message'),
97 Index(['repos', 'time'])],
98 Table('node_change', key='id')[
99 Column('id', auto_increment=True),
100 Column('repos', type='int'),
101 Column('rev', key_size=40),
102 Column('path', key_size=255),
103 Column('node_type', size=1),
104 Column('change_type', size=1),
105 Column('base_path'),
106 Column('base_rev'),
107 Index(['repos', 'rev', 'path']),
108 Index(['repos', 'path', 'rev'])],
109
110
111 Table('ticket', key='id')[
112 Column('id', auto_increment=True),
113 Column('type'),
114 Column('time', type='int64'),
115 Column('changetime', type='int64'),
116 Column('component'),
117 Column('severity'),
118 Column('priority'),
119 Column('owner'),
120 Column('reporter'),
121 Column('cc'),
122 Column('version'),
123 Column('milestone'),
124 Column('status'),
125 Column('resolution'),
126 Column('summary'),
127 Column('description'),
128 Column('keywords'),
129 Index(['time']),
130 Index(['status'])],
131 Table('ticket_change', key=('ticket', 'time', 'field'))[
132 Column('ticket', type='int'),
133 Column('time', type='int64'),
134 Column('author'),
135 Column('field'),
136 Column('oldvalue'),
137 Column('newvalue'),
138 Index(['ticket']),
139 Index(['time'])],
140 Table('ticket_custom', key=('ticket', 'name'))[
141 Column('ticket', type='int'),
142 Column('name'),
143 Column('value')],
144 Table('enum', key=('type', 'name'))[
145 Column('type'),
146 Column('name'),
147 Column('value'),
148 Column('description')],
149 Table('component', key='name')[
150 Column('name'),
151 Column('owner'),
152 Column('description')],
153 Table('milestone', key='name')[
154 Column('name'),
155 Column('due', type='int64'),
156 Column('completed', type='int64'),
157 Column('description')],
158 Table('version', key='name')[
159 Column('name'),
160 Column('time', type='int64'),
161 Column('description')],
162
163
164 Table('report', key='id')[
165 Column('id', auto_increment=True),
166 Column('author'),
167 Column('title'),
168 Column('query'),
169 Column('description')],
170
171
172 Table('notify_subscription', key='id')[
173 Column('id', auto_increment=True),
174 Column('time', type='int64'),
175 Column('changetime', type='int64'),
176 Column('class'),
177 Column('sid'),
178 Column('authenticated', type='int'),
179 Column('distributor'),
180 Column('format'),
181 Column('priority', type='int'),
182 Column('adverb'),
183 Index(['sid', 'authenticated']),
184 Index(['class'])],
185 Table('notify_watch', key='id')[
186 Column('id', auto_increment=True),
187 Column('sid'),
188 Column('authenticated', type='int'),
189 Column('class'),
190 Column('realm'),
191 Column('target'),
192 Index(['sid', 'authenticated', 'class']),
193 Index(['class', 'realm', 'target'])],
194 ]
195
196
197
198
199
200
202 return (
203 ('Active Tickets',
204 """\
205 * List all active tickets by priority.
206 * Color each row based on priority.
207 """,
208 """\
209 SELECT p.value AS __color__,
210 t.id AS ticket, t.summary, t.component, t.version, t.milestone,
211 t.type AS type, t.owner, t.status, t.time AS created,
212 t.changetime AS _changetime, t.description AS _description,
213 t.reporter AS _reporter
214 FROM ticket t
215 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
216 WHERE t.status <> 'closed'
217 ORDER BY """ + db.cast('p.value', 'int') + """, t.milestone, t.type, t.time
218 """),
219
220 ('Active Tickets by Version',
221 """\
222 This report shows how to color results by priority,
223 while grouping results by version.
224
225 Last modification time, description and reporter are included as hidden fields
226 for useful RSS export.
227 """,
228 """\
229 SELECT p.value AS __color__,
230 t.version AS __group__,
231 t.id AS ticket, t.summary, t.component, t.version, t.type AS type,
232 t.owner, t.status, t.time AS created,
233 t.changetime AS _changetime, t.description AS _description,
234 t.reporter AS _reporter
235 FROM ticket t
236 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
237 WHERE t.status <> 'closed'
238 ORDER BY (t.version IS NULL), t.version, """ + db.cast('p.value', 'int') +
239 """, t.type, t.time
240 """),
241
242 ('Active Tickets by Milestone',
243 """\
244 This report shows how to color results by priority,
245 while grouping results by milestone.
246
247 Last modification time, description and reporter are included as hidden fields
248 for useful RSS export.
249 """,
250 """\
251 SELECT p.value AS __color__,
252 %s AS __group__,
253 t.id AS ticket, t.summary, t.component, t.version, t.type AS type,
254 t.owner, t.status, t.time AS created, t.changetime AS _changetime,
255 t.description AS _description, t.reporter AS _reporter
256 FROM ticket t
257 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
258 WHERE t.status <> 'closed'
259 ORDER BY (t.milestone IS NULL), t.milestone, %s, t.type, t.time
260 """ % (db.concat("'Milestone '", 't.milestone'), db.cast('p.value', 'int'))),
261
262 ('Accepted, Active Tickets by Owner',
263 """\
264 List accepted tickets, group by ticket owner, sorted by priority.
265 """,
266 """\
267 SELECT p.value AS __color__,
268 t.owner AS __group__,
269 t.id AS ticket, t.summary, t.component, t.milestone, t.type AS type,
270 t.time AS created, t.changetime AS _changetime,
271 t.description AS _description, t.reporter AS _reporter
272 FROM ticket t
273 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
274 WHERE t.status = 'accepted'
275 ORDER BY t.owner, """ + db.cast('p.value', 'int') + """, t.type, t.time
276 """),
277
278 ('Accepted, Active Tickets by Owner (Full Description)',
279 """\
280 List tickets accepted, group by ticket owner.
281 This report demonstrates the use of full-row display.
282 """,
283 """\
284 SELECT p.value AS __color__,
285 t.owner AS __group__,
286 t.id AS ticket, t.summary, t.component, t.milestone, t.type AS type,
287 t.time AS created, t.description AS _description_,
288 t.changetime AS _changetime, t.reporter AS _reporter
289 FROM ticket t
290 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
291 WHERE t.status = 'accepted'
292 ORDER BY t.owner, """ + db.cast('p.value', 'int') + """, t.type, t.time
293 """),
294
295 ('All Tickets By Milestone (Including closed)',
296 """\
297 A more complex example to show how to make advanced reports.
298 """,
299 """\
300 SELECT p.value AS __color__,
301 t.milestone AS __group__,
302 (CASE t.status
303 WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
304 ELSE
305 (CASE t.owner WHEN $USER THEN 'font-weight: bold' END)
306 END) AS __style__,
307 t.id AS ticket, t.summary, t.component, t.status, t.resolution, t.version,
308 t.type AS type, t.priority, t.owner, t.changetime AS modified,
309 t.time AS _time, t.reporter AS _reporter
310 FROM ticket t
311 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
312 ORDER BY (t.milestone IS NULL), t.milestone DESC, (t.status = 'closed'),
313 (CASE t.status WHEN 'closed' THEN t.changetime ELSE (-1) * %s END) DESC
314 """ % db.cast('p.value', 'int')),
315
316 ('My Tickets',
317 """\
318 This report demonstrates the use of the automatically set
319 USER dynamic variable, replaced with the username of the
320 logged in user when executed.
321 """,
322 """\
323 SELECT p.value AS __color__,
324 (CASE
325 WHEN t.owner = $USER AND t.status = 'accepted' THEN 'Accepted'
326 WHEN t.owner = $USER THEN 'Owned'
327 WHEN t.reporter = $USER THEN 'Reported'
328 ELSE 'Commented' END) AS __group__,
329 t.id AS ticket, t.summary, t.component, t.version, t.milestone,
330 t.type AS type, t.priority, t.time AS created,
331 t.changetime AS _changetime, t.description AS _description,
332 t.reporter AS _reporter
333 FROM ticket t
334 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
335 WHERE t.status <> 'closed' AND
336 (t.owner = $USER OR t.reporter = $USER OR
337 EXISTS (SELECT * FROM ticket_change tc
338 WHERE tc.ticket = t.id AND tc.author = $USER AND
339 tc.field = 'comment'))
340 ORDER BY (COALESCE(t.owner, '') = $USER AND t.status = 'accepted') DESC,
341 COALESCE(t.owner, '') = $USER DESC,
342 COALESCE(t.reporter, '') = $USER DESC,
343 """ + db.cast('p.value', 'int') + """, t.milestone, t.type, t.time
344 """),
345
346 ('Active Tickets, Mine first',
347 """\
348 * List all active tickets by priority.
349 * Show all tickets owned by the logged in user in a group first.
350 """,
351 """\
352 SELECT p.value AS __color__,
353 (CASE t.owner
354 WHEN $USER THEN 'My Tickets'
355 ELSE 'Active Tickets'
356 END) AS __group__,
357 t.id AS ticket, t.summary, t.component, t.version, t.milestone,
358 t.type AS type, t.owner, t.status, t.time AS created,
359 t.changetime AS _changetime, t.description AS _description,
360 t.reporter AS _reporter
361 FROM ticket t
362 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
363 WHERE t.status <> 'closed'
364 ORDER BY (COALESCE(t.owner, '') = $USER) DESC, """
365 + db.cast('p.value', 'int') + """, t.milestone, t.type, t.time
366 """))
367
368
369
370
371
372
373
375 return (('component',
376 ('name', 'owner'),
377 (('component1', 'somebody'),
378 ('component2', 'somebody'))),
379 ('milestone',
380 ('name', 'due', 'completed'),
381 (('milestone1', 0, 0),
382 ('milestone2', 0, 0),
383 ('milestone3', 0, 0),
384 ('milestone4', 0, 0))),
385 ('version',
386 ('name', 'time'),
387 (('1.0', 0),
388 ('2.0', 0))),
389 ('enum',
390 ('type', 'name', 'value'),
391 (('resolution', 'fixed', 1),
392 ('resolution', 'invalid', 2),
393 ('resolution', 'wontfix', 3),
394 ('resolution', 'duplicate', 4),
395 ('resolution', 'worksforme', 5),
396 ('priority', 'blocker', 1),
397 ('priority', 'critical', 2),
398 ('priority', 'major', 3),
399 ('priority', 'minor', 4),
400 ('priority', 'trivial', 5),
401 ('ticket_type', 'defect', 1),
402 ('ticket_type', 'enhancement', 2),
403 ('ticket_type', 'task', 3))),
404 ('permission',
405 ('username', 'action'),
406 (('anonymous', 'LOG_VIEW'),
407 ('anonymous', 'FILE_VIEW'),
408 ('anonymous', 'WIKI_VIEW'),
409 ('authenticated', 'WIKI_CREATE'),
410 ('authenticated', 'WIKI_MODIFY'),
411 ('anonymous', 'SEARCH_VIEW'),
412 ('anonymous', 'REPORT_VIEW'),
413 ('anonymous', 'REPORT_SQL_VIEW'),
414 ('anonymous', 'TICKET_VIEW'),
415 ('authenticated', 'TICKET_CREATE'),
416 ('authenticated', 'TICKET_MODIFY'),
417 ('anonymous', 'BROWSER_VIEW'),
418 ('anonymous', 'TIMELINE_VIEW'),
419 ('anonymous', 'CHANGESET_VIEW'),
420 ('anonymous', 'ROADMAP_VIEW'),
421 ('anonymous', 'MILESTONE_VIEW'))),
422 ('system',
423 ('name', 'value'),
424 (('database_version', str(db_version)),
425 ('initial_database_version', str(db_version)))),
426 ('report',
427 ('author', 'title', 'query', 'description'),
428 __mkreports(get_reports(db))))
429