1def migrate_up(manager):
2    manager.execute_script(ADD_COLUMN_SQL)
3    manager.execute_script(ALTER_VIEWS_UP_SQL)
4
5
6def migrate_down(manager):
7    manager.execute_script(DROP_COLUMN_SQL)
8    manager.execute_script(ALTER_VIEWS_DOWN_SQL)
9
10
11ADD_COLUMN_SQL = """\
12ALTER TABLE tests ADD COLUMN finished_time datetime NULL;
13"""
14
15DROP_COLUMN_SQL = """\
16ALTER TABLE tests DROP finished_time;
17"""
18
19ALTER_VIEWS_UP_SQL = """\
20ALTER VIEW test_view AS
21SELECT  tests.test_idx,
22        tests.job_idx,
23        tests.test,
24        tests.subdir,
25        tests.kernel_idx,
26        tests.status,
27        tests.reason,
28        tests.machine_idx,
29        tests.finished_time AS test_finished_time,
30        jobs.tag AS job_tag,
31        jobs.label AS job_label,
32        jobs.username AS job_username,
33        jobs.queued_time AS job_queued_time,
34        jobs.started_time AS job_started_time,
35        jobs.finished_time AS job_finished_time,
36        machines.hostname AS machine_hostname,
37        machines.machine_group,
38        machines.owner AS machine_owner,
39        kernels.kernel_hash,
40        kernels.base AS kernel_base,
41        kernels.printable AS kernel_printable,
42        status.word AS status_word
43FROM tests
44INNER JOIN jobs ON jobs.job_idx = tests.job_idx
45INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
46INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
47INNER JOIN status ON status.status_idx = tests.status;
48
49-- perf_view (to make life easier for people trying to mine performance data)
50ALTER VIEW perf_view AS
51SELECT  tests.test_idx,
52        tests.job_idx,
53        tests.test,
54        tests.subdir,
55        tests.kernel_idx,
56        tests.status,
57        tests.reason,
58        tests.machine_idx,
59        tests.finished_time AS test_finished_time,
60        jobs.tag AS job_tag,
61        jobs.label AS job_label,
62        jobs.username AS job_username,
63        jobs.queued_time AS job_queued_time,
64        jobs.started_time AS job_started_time,
65        jobs.finished_time AS job_finished_time,
66        machines.hostname AS machine_hostname,
67        machines.machine_group,
68        machines.owner AS machine_owner,
69        kernels.kernel_hash,
70        kernels.base AS kernel_base,
71        kernels.printable AS kernel_printable,
72        status.word AS status_word,
73        iteration_result.iteration,
74        iteration_result.attribute AS iteration_key,
75        iteration_result.value AS iteration_value
76FROM tests
77INNER JOIN jobs ON jobs.job_idx = tests.job_idx
78INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
79INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
80INNER JOIN status ON status.status_idx = tests.status
81INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx;
82"""
83
84
85ALTER_VIEWS_DOWN_SQL = """\
86ALTER VIEW test_view AS
87SELECT  tests.test_idx,
88        tests.job_idx,
89        tests.test,
90        tests.subdir,
91        tests.kernel_idx,
92        tests.status,
93        tests.reason,
94        tests.machine_idx,
95        jobs.tag AS job_tag,
96        jobs.label AS job_label,
97        jobs.username AS job_username,
98        jobs.queued_time AS job_queued_time,
99        jobs.started_time AS job_started_time,
100        jobs.finished_time AS job_finished_time,
101        machines.hostname AS machine_hostname,
102        machines.machine_group,
103        machines.owner AS machine_owner,
104        kernels.kernel_hash,
105        kernels.base AS kernel_base,
106        kernels.printable AS kernel_printable,
107        status.word AS status_word
108FROM tests
109INNER JOIN jobs ON jobs.job_idx = tests.job_idx
110INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
111INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
112INNER JOIN status ON status.status_idx = tests.status;
113
114-- perf_view (to make life easier for people trying to mine performance data)
115ALTER VIEW perf_view AS
116SELECT  tests.test_idx,
117        tests.job_idx,
118        tests.test,
119        tests.subdir,
120        tests.kernel_idx,
121        tests.status,
122        tests.reason,
123        tests.machine_idx,
124        jobs.tag AS job_tag,
125        jobs.label AS job_label,
126        jobs.username AS job_username,
127        jobs.queued_time AS job_queued_time,
128        jobs.started_time AS job_started_time,
129        jobs.finished_time AS job_finished_time,
130        machines.hostname AS machine_hostname,
131        machines.machine_group,
132        machines.owner AS machine_owner,
133        kernels.kernel_hash,
134        kernels.base AS kernel_base,
135        kernels.printable AS kernel_printable,
136        status.word AS status_word,
137        iteration_result.iteration,
138        iteration_result.attribute AS iteration_key,
139        iteration_result.value AS iteration_value
140FROM tests
141INNER JOIN jobs ON jobs.job_idx = tests.job_idx
142INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
143INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
144INNER JOIN status ON status.status_idx = tests.status
145INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx;
146"""
147