Edgewall Software

Ticket #8396: db_default.py

File db_default.py, 13.1 KB (added by guillaume.brocard@…, 3 years ago)

Default db schema definition, modified under "Ticket system" for the description column type.

Line 
1# -*- coding: utf-8 -*-
2#
3# Copyright (C) 2003-2009 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 http://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 http://trac.edgewall.org/log/.
14#
15# Author: Daniel Lundin <daniel@edgewall.com>
16
17from trac.db import Table, Column, Index
18
19# Database version identifier. Used for automatic upgrades.
20db_version = 21
21
22def __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
36schema = [
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
61    # Attachments
62    Table('attachment', key=('type', 'id', 'filename'))[
63        Column('type'),
64        Column('id'),
65        Column('filename'),
66        Column('size', type='int'),
67        Column('time', type='int'),
68        Column('description'),
69        Column('author'),
70        Column('ipnr')],
71
72    # Wiki system
73    Table('wiki', key=('name', 'version'))[
74        Column('name'),
75        Column('version', type='int'),
76        Column('time', type='int'),
77        Column('author'),
78        Column('ipnr'),
79        Column('text'),
80        Column('comment'),
81        Column('readonly', type='int'),
82        Index(['time'])],
83
84    # Version control cache
85    Table('revision', key='rev')[
86        Column('rev'),
87        Column('time', type='int'),
88        Column('author'),
89        Column('message'),
90        Index(['time'])],
91    Table('node_change', key=('rev', 'path', 'change_type'))[
92        Column('rev'),
93        Column('path'),
94        Column('node_type', size=1),
95        Column('change_type', size=1),
96        Column('base_path'),
97        Column('base_rev'),
98        Index(['rev'])],
99
100    # Ticket system
101    Table('ticket', key='id')[
102        Column('id', auto_increment=True),
103        Column('type'),
104        Column('time', type='int'),
105        Column('changetime', type='int'),
106        Column('component'),
107        Column('severity'),
108        Column('priority'),
109        Column('owner'),
110        Column('reporter'),
111        Column('cc'),
112        Column('version'),
113        Column('milestone'),
114        Column('status'),
115        Column('resolution'),
116        Column('summary'),
117        Column('description', type='mediumtext'),
118        Column('keywords'),
119        Index(['time']),
120        Index(['status'])],   
121    Table('ticket_change', key=('ticket', 'time', 'field'))[
122        Column('ticket', type='int'),
123        Column('time', type='int'),
124        Column('author'),
125        Column('field'),
126        Column('oldvalue'),
127        Column('newvalue'),
128        Index(['ticket']),
129        Index(['time'])],
130    Table('ticket_custom', key=('ticket', 'name'))[
131        Column('ticket', type='int'),
132        Column('name'),
133        Column('value')],
134    Table('enum', key=('type', 'name'))[
135        Column('type'),
136        Column('name'),
137        Column('value')],
138    Table('component', key='name')[
139        Column('name'),
140        Column('owner'),
141        Column('description')],
142    Table('milestone', key='name')[
143        Column('name'),
144        Column('due', type='int'),
145        Column('completed', type='int'),
146        Column('description')],
147    Table('version', key='name')[
148        Column('name'),
149        Column('time', type='int'),
150        Column('description')],
151
152    # Report system
153    Table('report', key='id')[
154        Column('id', auto_increment=True),
155        Column('author'),
156        Column('title'),
157        Column('query'),
158        Column('description')],
159]
160
161
162##
163## Default Reports
164##
165
166def get_reports(db):
167    return (
168('Active Tickets',
169"""
170 * List all active tickets by priority.
171 * Color each row based on priority.
172""",
173"""
174SELECT p.value AS __color__,
175   id AS ticket, summary, component, version, milestone, t.type AS type,
176   owner, status,
177   time AS created,
178   changetime AS _changetime, description AS _description,
179   reporter AS _reporter
180  FROM ticket t
181  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
182  WHERE status <> 'closed'
183  ORDER BY """ + db.cast('p.value', 'int') + """, milestone, t.type, time
184"""),
185#----------------------------------------------------------------------------
186 ('Active Tickets by Version',
187"""
188This report shows how to color results by priority,
189while grouping results by version.
190
191Last modification time, description and reporter are included as hidden fields
192for useful RSS export.
193""",
194"""
195SELECT p.value AS __color__,
196   version AS __group__,
197   id AS ticket, summary, component, version, t.type AS type,
198   owner, status,
199   time AS created,
200   changetime AS _changetime, description AS _description,
201   reporter AS _reporter
202  FROM ticket t
203  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
204  WHERE status <> 'closed'
205  ORDER BY (version IS NULL),version, """ + db.cast('p.value', 'int') +
206  """, t.type, time
207"""),
208#----------------------------------------------------------------------------
209('Active Tickets by Milestone',
210"""
211This report shows how to color results by priority,
212while grouping results by milestone.
213
214Last modification time, description and reporter are included as hidden fields
215for useful RSS export.
216""",
217"""
218SELECT p.value AS __color__,
219   %s AS __group__,
220   id AS ticket, summary, component, version, t.type AS type,
221   owner, status,
222   time AS created,
223   changetime AS _changetime, description AS _description,
224   reporter AS _reporter
225  FROM ticket t
226  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
227  WHERE status <> 'closed'
228  ORDER BY (milestone IS NULL),milestone, %s, t.type, time
229""" % (db.concat("'Milestone '", 'milestone'), db.cast('p.value', 'int'))),
230#----------------------------------------------------------------------------
231('Accepted, Active Tickets by Owner',
232"""
233List accepted tickets, group by ticket owner, sorted by priority.
234""",
235"""
236
237SELECT p.value AS __color__,
238   owner AS __group__,
239   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
240   changetime AS _changetime, description AS _description,
241   reporter AS _reporter
242  FROM ticket t
243  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
244  WHERE status = 'accepted'
245  ORDER BY owner, """ + db.cast('p.value', 'int') + """, t.type, time
246"""),
247#----------------------------------------------------------------------------
248('Accepted, Active Tickets by Owner (Full Description)',
249"""
250List tickets accepted, group by ticket owner.
251This report demonstrates the use of full-row display.
252""",
253"""
254SELECT p.value AS __color__,
255   owner AS __group__,
256   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
257   description AS _description_,
258   changetime AS _changetime, reporter AS _reporter
259  FROM ticket t
260  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
261  WHERE status = 'accepted'
262  ORDER BY owner, """ + db.cast('p.value', 'int') + """, t.type, time
263"""),
264#----------------------------------------------------------------------------
265('All Tickets By Milestone  (Including closed)',
266"""
267A more complex example to show how to make advanced reports.
268""",
269"""
270SELECT p.value AS __color__,
271   t.milestone AS __group__,
272   (CASE status
273      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
274      ELSE
275        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
276    END) AS __style__,
277   id AS ticket, summary, component, status,
278   resolution,version, t.type AS type, priority, owner,
279   changetime AS modified,
280   time AS _time,reporter AS _reporter
281  FROM ticket t
282  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
283  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
284        (CASE status WHEN 'closed' THEN changetime ELSE (-1) * %s END) DESC
285""" % db.cast('p.value', 'int')),
286#----------------------------------------------------------------------------
287('My Tickets',
288"""
289This report demonstrates the use of the automatically set
290USER dynamic variable, replaced with the username of the
291logged in user when executed.
292""",
293"""
294SELECT p.value AS __color__,
295   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
296   id AS ticket, summary, component, version, milestone,
297   t.type AS type, priority, time AS created,
298   changetime AS _changetime, description AS _description,
299   reporter AS _reporter
300  FROM ticket t
301  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
302  WHERE t.status <> 'closed' AND owner = $USER
303  ORDER BY (status = 'accepted') DESC, """ + db.cast('p.value', 'int') + 
304  """, milestone, t.type, time
305"""),
306#----------------------------------------------------------------------------
307('Active Tickets, Mine first',
308"""
309 * List all active tickets by priority.
310 * Show all tickets owned by the logged in user in a group first.
311""",
312"""
313SELECT p.value AS __color__,
314   (CASE owner
315     WHEN $USER THEN 'My Tickets'
316     ELSE 'Active Tickets'
317    END) AS __group__,
318   id AS ticket, summary, component, version, milestone, t.type AS type,
319   owner, status,
320   time AS created,
321   changetime AS _changetime, description AS _description,
322   reporter AS _reporter
323  FROM ticket t
324  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
325  WHERE status <> 'closed'
326  ORDER BY (COALESCE(owner, '') = $USER) DESC, """
327  + db.cast('p.value', 'int') + """, milestone, t.type, time
328"""))
329
330
331##
332## Default database values
333##
334
335# (table, (column1, column2), ((row1col1, row1col2), (row2col1, row2col2)))
336def get_data(db):
337   return (('component',
338             ('name', 'owner'),
339               (('component1', 'somebody'),
340                ('component2', 'somebody'))),
341           ('milestone',
342             ('name', 'due', 'completed'),
343               (('milestone1', 0, 0),
344                ('milestone2', 0, 0),
345                ('milestone3', 0, 0),
346                ('milestone4', 0, 0))),
347           ('version',
348             ('name', 'time'),
349               (('1.0', 0),
350                ('2.0', 0))),
351           ('enum',
352             ('type', 'name', 'value'),
353               (('resolution', 'fixed', 1),
354                ('resolution', 'invalid', 2),
355                ('resolution', 'wontfix', 3),
356                ('resolution', 'duplicate', 4),
357                ('resolution', 'worksforme', 5),
358                ('priority', 'blocker', 1),
359                ('priority', 'critical', 2),
360                ('priority', 'major', 3),
361                ('priority', 'minor', 4),
362                ('priority', 'trivial', 5),
363                ('ticket_type', 'defect', 1),
364                ('ticket_type', 'enhancement', 2),
365                ('ticket_type', 'task', 3))),
366           ('permission',
367             ('username', 'action'),
368               (('anonymous', 'LOG_VIEW'),
369                ('anonymous', 'FILE_VIEW'),
370                ('anonymous', 'WIKI_VIEW'),
371                ('authenticated', 'WIKI_CREATE'),
372                ('authenticated', 'WIKI_MODIFY'),
373                ('anonymous', 'SEARCH_VIEW'),
374                ('anonymous', 'REPORT_VIEW'),
375                ('anonymous', 'REPORT_SQL_VIEW'),
376                ('anonymous', 'TICKET_VIEW'),
377                ('authenticated', 'TICKET_CREATE'),
378                ('authenticated', 'TICKET_MODIFY'),
379                ('anonymous', 'BROWSER_VIEW'),
380                ('anonymous', 'TIMELINE_VIEW'),
381                ('anonymous', 'CHANGESET_VIEW'),
382                ('anonymous', 'ROADMAP_VIEW'),
383                ('anonymous', 'MILESTONE_VIEW'))),
384           ('system',
385             ('name', 'value'),
386               (('database_version', str(db_version)),
387                ('initial_database_version', str(db_version)),
388                ('youngest_rev', ''))),
389           ('report',
390             ('author', 'title', 'query', 'description'),
391               __mkreports(get_reports(db))))