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