1 | # -*- coding: utf-8 -*-
|
---|
2 | #
|
---|
3 | # Copyright (C) 2003-2023 Edgewall Software
|
---|
4 | # Copyright (C) 2003-2005 Daniel Lundin <daniel@edgewall.com>
|
---|
5 | # All rights reserved.
|
---|
6 | #
|
---|
7 | # This software is licensed as described in the file COPYING, which
|
---|
8 | # you should have received as part of this distribution. The terms
|
---|
9 | # are also available at https://trac.edgewall.org/wiki/TracLicense.
|
---|
10 | #
|
---|
11 | # This software consists of voluntary contributions made by many
|
---|
12 | # individuals. For the exact contribution history, see the revision
|
---|
13 | # history and logs, available at https://trac.edgewall.org/log/.
|
---|
14 | #
|
---|
15 | # Author: Daniel Lundin <daniel@edgewall.com>
|
---|
16 |
|
---|
17 | from trac.db.schema import Table, Column, Index
|
---|
18 |
|
---|
19 | # Database version identifier. Used for automatic upgrades.
|
---|
20 | db_version = 45
|
---|
21 |
|
---|
22 | def __mkreports(reports):
|
---|
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 | ## Database schema
|
---|
34 | ##
|
---|
35 |
|
---|
36 | schema = [
|
---|
37 | # Common
|
---|
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 | # Attachments
|
---|
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 | # Wiki system
|
---|
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 | # Version control cache
|
---|
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 | # Ticket system
|
---|
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 | # Report system
|
---|
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 | # Notification system
|
---|
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 | ## Default Reports
|
---|
199 | ##
|
---|
200 |
|
---|
201 | def get_reports(db):
|
---|
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 | ## Default database values
|
---|
371 | ##
|
---|
372 |
|
---|
373 | # (table, (column1, column2), ((row1col1, row1col2), (row2col1, row2col2)))
|
---|
374 | def get_data(db):
|
---|
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 | ('report',
|
---|
423 | ('author', 'title', 'query', 'description'),
|
---|
424 | __mkreports(get_reports(db))))
|
---|