sql = [
#-- Add ticket_type to 'ticket', remove the unused 'url' column
"""CREATE TEMPORARY TABLE ticket_old AS SELECT * FROM ticket;""",
"""DROP TABLE ticket;""",
"""CREATE TABLE ticket (
id integer PRIMARY KEY,
type text, -- the nature of the ticket
time integer, -- the time it was created
changetime integer,
component text,
severity text,
priority text,
owner text, -- who is this ticket assigned to
reporter text,
cc text, -- email addresses to notify
version text, --
milestone text, --
status text,
resolution text,
summary text, -- one-line summary
description text, -- problem description (long)
keywords text
);""",
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
owner, reporter, cc, version, milestone, status, resolution,
summary, description, keywords)
SELECT id, 'defect', time, changetime, component, severity, priority, owner,
reporter, cc, version, milestone, status, resolution, summary,
description, keywords FROM ticket_old
WHERE COALESCE(severity,'') <> 'enhancement';""",
"""INSERT INTO ticket(id, type, time, changetime, component, severity, priority,
owner, reporter, cc, version, milestone, status, resolution,
summary, description, keywords)
SELECT id, 'enhancement', time, changetime, component, 'normal', priority,
owner, reporter, cc, version, milestone, status, resolution, summary,
description, keywords FROM ticket_old
WHERE severity = 'enhancement';""",
"""INSERT INTO enum (type, name, value) VALUES ('ticket_type', 'defect', '1');""",
"""INSERT INTO enum (type, name, value) VALUES ('ticket_type', 'enhancement', '2');""",
"""INSERT INTO enum (type, name, value) VALUES ('ticket_type', 'task', '3');""",
"""DELETE FROM enum WHERE type = 'severity' AND name = 'enhancement';""",
"""DROP TABLE ticket_old;""",
]
def do_upgrade(env, ver, cursor):
for s in sql:
cursor.execute(s)
# -- upgrade reports (involve a rename)
cursor.execute("SELECT id,sql FROM report")
reports = {}
for id, rsql in cursor:
reports[id] = rsql
for id, rsql in reports.items():
parts = rsql.split('ORDER BY', 1)
ending = len(parts)>1 and 'ORDER BY'+parts[1] or ''
cursor.execute("UPDATE report SET sql=%s WHERE id=%s",
(parts[0].replace('severity,',
't.type AS type, severity,') + ending,
id))
|