1import common
2from autotest_lib.database import db_utils
3
4
5RECREATE_VIEWS_UP = """
6CREATE VIEW tko_test_view AS
7SELECT  tko_tests.test_idx,
8        tko_tests.job_idx,
9        tko_tests.test,
10        tko_tests.subdir,
11        tko_tests.kernel_idx,
12        tko_tests.status,
13        tko_tests.reason,
14        tko_tests.machine_idx,
15        tko_tests.started_time AS test_started_time,
16        tko_tests.finished_time AS test_finished_time,
17        tko_jobs.tag AS job_tag,
18        tko_jobs.label AS job_label,
19        tko_jobs.username AS job_username,
20        tko_jobs.queued_time AS job_queued_time,
21        tko_jobs.started_time AS job_started_time,
22        tko_jobs.finished_time AS job_finished_time,
23        tko_machines.hostname AS machine_hostname,
24        tko_machines.machine_group,
25        tko_machines.owner AS machine_owner,
26        tko_kernels.kernel_hash,
27        tko_kernels.base AS kernel_base,
28        tko_kernels.printable AS kernel_printable,
29        tko_status.word AS status_word
30FROM tko_tests
31INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx
32INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx
33INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx
34INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status;
35
36
37CREATE VIEW tko_perf_view AS
38SELECT  tko_tests.test_idx,
39        tko_tests.job_idx,
40        tko_tests.test,
41        tko_tests.subdir,
42        tko_tests.kernel_idx,
43        tko_tests.status,
44        tko_tests.reason,
45        tko_tests.machine_idx,
46        tko_tests.started_time AS test_started_time,
47        tko_tests.finished_time AS test_finished_time,
48        tko_jobs.tag AS job_tag,
49        tko_jobs.label AS job_label,
50        tko_jobs.username AS job_username,
51        tko_jobs.queued_time AS job_queued_time,
52        tko_jobs.started_time AS job_started_time,
53        tko_jobs.finished_time AS job_finished_time,
54        tko_machines.hostname AS machine_hostname,
55        tko_machines.machine_group,
56        tko_machines.owner AS machine_owner,
57        tko_kernels.kernel_hash,
58        tko_kernels.base AS kernel_base,
59        tko_kernels.printable AS kernel_printable,
60        tko_status.word AS status_word,
61        tko_iteration_result.iteration,
62        tko_iteration_result.attribute AS iteration_key,
63        tko_iteration_result.value AS iteration_value
64FROM tko_tests
65INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx
66INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx
67INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx
68INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status
69INNER JOIN tko_iteration_result ON
70        tko_iteration_result.test_idx = tko_tests.test_idx;
71
72
73CREATE VIEW tko_test_view_2 AS
74SELECT  tko_tests.test_idx,
75        tko_tests.job_idx,
76        tko_tests.test AS test_name,
77        tko_tests.subdir,
78        tko_tests.kernel_idx,
79        tko_tests.status AS status_idx,
80        tko_tests.reason,
81        tko_tests.machine_idx,
82        tko_tests.started_time AS test_started_time,
83        tko_tests.finished_time AS test_finished_time,
84        tko_jobs.tag AS job_tag,
85        tko_jobs.label AS job_name,
86        tko_jobs.username AS job_owner,
87        tko_jobs.queued_time AS job_queued_time,
88        tko_jobs.started_time AS job_started_time,
89        tko_jobs.finished_time AS job_finished_time,
90        tko_jobs.afe_job_id AS afe_job_id,
91        tko_machines.hostname AS hostname,
92        tko_machines.machine_group AS platform,
93        tko_machines.owner AS machine_owner,
94        tko_kernels.kernel_hash,
95        tko_kernels.base AS kernel_base,
96        tko_kernels.printable AS kernel,
97        tko_status.word AS status
98FROM tko_tests
99INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx
100INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx
101INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx
102INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status;
103
104
105CREATE VIEW tko_test_view_outer_joins AS
106SELECT  tko_tests.test_idx,
107        tko_tests.job_idx,
108        tko_tests.test AS test_name,
109        tko_tests.subdir,
110        tko_tests.kernel_idx,
111        tko_tests.status AS status_idx,
112        tko_tests.reason,
113        tko_tests.machine_idx,
114        tko_tests.started_time AS test_started_time,
115        tko_tests.finished_time AS test_finished_time,
116        tko_jobs.tag AS job_tag,
117        tko_jobs.label AS job_name,
118        tko_jobs.username AS job_owner,
119        tko_jobs.queued_time AS job_queued_time,
120        tko_jobs.started_time AS job_started_time,
121        tko_jobs.finished_time AS job_finished_time,
122        tko_machines.hostname AS hostname,
123        tko_machines.machine_group AS platform,
124        tko_machines.owner AS machine_owner,
125        tko_kernels.kernel_hash,
126        tko_kernels.base AS kernel_base,
127        tko_kernels.printable AS kernel,
128        tko_status.word AS status
129FROM tko_tests
130LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx
131LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx
132LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx
133LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status;
134
135
136CREATE VIEW tko_perf_view_2 AS
137SELECT  tko_tests.test_idx,
138        tko_tests.job_idx,
139        tko_tests.test AS test_name,
140        tko_tests.subdir,
141        tko_tests.kernel_idx,
142        tko_tests.status AS status_idx,
143        tko_tests.reason,
144        tko_tests.machine_idx,
145        tko_tests.started_time AS test_started_time,
146        tko_tests.finished_time AS test_finished_time,
147        tko_jobs.tag AS job_tag,
148        tko_jobs.label AS job_name,
149        tko_jobs.username AS job_owner,
150        tko_jobs.queued_time AS job_queued_time,
151        tko_jobs.started_time AS job_started_time,
152        tko_jobs.finished_time AS job_finished_time,
153        tko_machines.hostname AS hostname,
154        tko_machines.machine_group AS platform,
155        tko_machines.owner AS machine_owner,
156        tko_kernels.kernel_hash,
157        tko_kernels.base AS kernel_base,
158        tko_kernels.printable AS kernel,
159        tko_status.word AS status,
160        tko_iteration_result.iteration,
161        tko_iteration_result.attribute AS iteration_key,
162        tko_iteration_result.value AS iteration_value
163FROM tko_tests
164LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx
165LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx
166LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx
167LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status
168LEFT OUTER JOIN tko_iteration_result ON
169        tko_iteration_result.test_idx = tko_tests.test_idx;
170"""
171
172
173RECREATE_VIEWS_DOWN = """
174CREATE VIEW test_view AS
175SELECT  tests.test_idx,
176        tests.job_idx,
177        tests.test,
178        tests.subdir,
179        tests.kernel_idx,
180        tests.status,
181        tests.reason,
182        tests.machine_idx,
183        tests.started_time AS test_started_time,
184        tests.finished_time AS test_finished_time,
185        jobs.tag AS job_tag,
186        jobs.label AS job_label,
187        jobs.username AS job_username,
188        jobs.queued_time AS job_queued_time,
189        jobs.started_time AS job_started_time,
190        jobs.finished_time AS job_finished_time,
191        machines.hostname AS machine_hostname,
192        machines.machine_group,
193        machines.owner AS machine_owner,
194        kernels.kernel_hash,
195        kernels.base AS kernel_base,
196        kernels.printable AS kernel_printable,
197        status.word AS status_word
198FROM tests
199INNER JOIN jobs ON jobs.job_idx = tests.job_idx
200INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
201INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
202INNER JOIN status ON status.status_idx = tests.status;
203
204
205CREATE VIEW perf_view AS
206SELECT  tests.test_idx,
207        tests.job_idx,
208        tests.test,
209        tests.subdir,
210        tests.kernel_idx,
211        tests.status,
212        tests.reason,
213        tests.machine_idx,
214        tests.started_time AS test_started_time,
215        tests.finished_time AS test_finished_time,
216        jobs.tag AS job_tag,
217        jobs.label AS job_label,
218        jobs.username AS job_username,
219        jobs.queued_time AS job_queued_time,
220        jobs.started_time AS job_started_time,
221        jobs.finished_time AS job_finished_time,
222        machines.hostname AS machine_hostname,
223        machines.machine_group,
224        machines.owner AS machine_owner,
225        kernels.kernel_hash,
226        kernels.base AS kernel_base,
227        kernels.printable AS kernel_printable,
228        status.word AS status_word,
229        iteration_result.iteration,
230        iteration_result.attribute AS iteration_key,
231        iteration_result.value AS iteration_value
232FROM tests
233INNER JOIN jobs ON jobs.job_idx = tests.job_idx
234INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
235INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
236INNER JOIN status ON status.status_idx = tests.status
237INNER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx;
238
239
240CREATE VIEW test_view_outer_joins AS
241SELECT  tests.test_idx,
242        tests.job_idx,
243        tests.test AS test_name,
244        tests.subdir,
245        tests.kernel_idx,
246        tests.status AS status_idx,
247        tests.reason,
248        tests.machine_idx,
249        tests.started_time AS test_started_time,
250        tests.finished_time AS test_finished_time,
251        jobs.tag AS job_tag,
252        jobs.label AS job_name,
253        jobs.username AS job_owner,
254        jobs.queued_time AS job_queued_time,
255        jobs.started_time AS job_started_time,
256        jobs.finished_time AS job_finished_time,
257        machines.hostname AS hostname,
258        machines.machine_group AS platform,
259        machines.owner AS machine_owner,
260        kernels.kernel_hash,
261        kernels.base AS kernel_base,
262        kernels.printable AS kernel,
263        status.word AS status
264FROM tests
265LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx
266LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx
267LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
268LEFT OUTER JOIN status ON status.status_idx = tests.status;
269
270
271CREATE VIEW test_view_2 AS
272SELECT  tests.test_idx,
273        tests.job_idx,
274        tests.test AS test_name,
275        tests.subdir,
276        tests.kernel_idx,
277        tests.status AS status_idx,
278        tests.reason,
279        tests.machine_idx,
280        tests.started_time AS test_started_time,
281        tests.finished_time AS test_finished_time,
282        jobs.tag AS job_tag,
283        jobs.label AS job_name,
284        jobs.username AS job_owner,
285        jobs.queued_time AS job_queued_time,
286        jobs.started_time AS job_started_time,
287        jobs.finished_time AS job_finished_time,
288        jobs.afe_job_id AS afe_job_id,
289        machines.hostname AS hostname,
290        machines.machine_group AS platform,
291        machines.owner AS machine_owner,
292        kernels.kernel_hash,
293        kernels.base AS kernel_base,
294        kernels.printable AS kernel,
295        status.word AS status
296FROM tests
297INNER JOIN jobs ON jobs.job_idx = tests.job_idx
298INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
299INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
300INNER JOIN status ON status.status_idx = tests.status;
301
302
303CREATE VIEW perf_view_2 AS
304SELECT  tests.test_idx,
305        tests.job_idx,
306        tests.test AS test_name,
307        tests.subdir,
308        tests.kernel_idx,
309        tests.status AS status_idx,
310        tests.reason,
311        tests.machine_idx,
312        tests.started_time AS test_started_time,
313        tests.finished_time AS test_finished_time,
314        jobs.tag AS job_tag,
315        jobs.label AS job_name,
316        jobs.username AS job_owner,
317        jobs.queued_time AS job_queued_time,
318        jobs.started_time AS job_started_time,
319        jobs.finished_time AS job_finished_time,
320        machines.hostname AS hostname,
321        machines.machine_group AS platform,
322        machines.owner AS machine_owner,
323        kernels.kernel_hash,
324        kernels.base AS kernel_base,
325        kernels.printable AS kernel,
326        status.word AS status,
327        iteration_result.iteration,
328        iteration_result.attribute AS iteration_key,
329        iteration_result.value AS iteration_value
330FROM tests
331LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx
332LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx
333LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
334LEFT OUTER JOIN status ON status.status_idx = tests.status
335LEFT OUTER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx;
336"""
337
338
339ORIG_NAMES = (
340        'embedded_graphing_queries',
341        'iteration_attributes',
342        'iteration_result',
343        'jobs',
344        'kernels',
345        'machines',
346        'patches',
347        'query_history',
348        'saved_queries',
349        'status',
350        'test_attributes',
351        'test_labels',
352        'test_labels_tests',
353        'tests',
354        )
355
356RENAMES_UP = dict((name, 'tko_' + name) for name in ORIG_NAMES)
357VIEWS_TO_DROP_UP = (
358        'test_view',
359        'test_view_2',
360        'test_view_outer_joins',
361        'perf_view',
362        'perf_view_2',
363        )
364
365RENAMES_DOWN = dict((value, key) for key, value in RENAMES_UP.iteritems())
366VIEWS_TO_DROP_DOWN = ['tko_' + view for view in VIEWS_TO_DROP_UP]
367
368
369def migrate_up(manager):
370    db_utils.drop_views(manager, VIEWS_TO_DROP_UP)
371    db_utils.rename(manager, RENAMES_UP)
372    manager.execute_script(RECREATE_VIEWS_UP)
373
374
375def migrate_down(manager):
376    db_utils.drop_views(manager, VIEWS_TO_DROP_DOWN)
377    db_utils.rename(manager, RENAMES_DOWN)
378    manager.execute_script(RECREATE_VIEWS_DOWN)
379