1 import common 2 from autotest_lib.database import db_utils 3 4 5 RECREATE_VIEWS_UP = """ 6 CREATE VIEW tko_test_view AS 7 SELECT 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 30 FROM tko_tests 31 INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx 32 INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx 33 INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx 34 INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status; 35 36 37 CREATE VIEW tko_perf_view AS 38 SELECT 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 64 FROM tko_tests 65 INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx 66 INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx 67 INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx 68 INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status 69 INNER JOIN tko_iteration_result ON 70 tko_iteration_result.test_idx = tko_tests.test_idx; 71 72 73 CREATE VIEW tko_test_view_2 AS 74 SELECT 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 98 FROM tko_tests 99 INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx 100 INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx 101 INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx 102 INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status; 103 104 105 CREATE VIEW tko_test_view_outer_joins AS 106 SELECT 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 129 FROM tko_tests 130 LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx 131 LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx 132 LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx 133 LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status; 134 135 136 CREATE VIEW tko_perf_view_2 AS 137 SELECT 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 163 FROM tko_tests 164 LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx 165 LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx 166 LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx 167 LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status 168 LEFT OUTER JOIN tko_iteration_result ON 169 tko_iteration_result.test_idx = tko_tests.test_idx; 170 """ 171 172 173 RECREATE_VIEWS_DOWN = """ 174 CREATE VIEW test_view AS 175 SELECT 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 198 FROM tests 199 INNER JOIN jobs ON jobs.job_idx = tests.job_idx 200 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx 201 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 202 INNER JOIN status ON status.status_idx = tests.status; 203 204 205 CREATE VIEW perf_view AS 206 SELECT 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 232 FROM tests 233 INNER JOIN jobs ON jobs.job_idx = tests.job_idx 234 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx 235 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 236 INNER JOIN status ON status.status_idx = tests.status 237 INNER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx; 238 239 240 CREATE VIEW test_view_outer_joins AS 241 SELECT 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 264 FROM tests 265 LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx 266 LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx 267 LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 268 LEFT OUTER JOIN status ON status.status_idx = tests.status; 269 270 271 CREATE VIEW test_view_2 AS 272 SELECT 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 296 FROM tests 297 INNER JOIN jobs ON jobs.job_idx = tests.job_idx 298 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx 299 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 300 INNER JOIN status ON status.status_idx = tests.status; 301 302 303 CREATE VIEW perf_view_2 AS 304 SELECT 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 330 FROM tests 331 LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx 332 LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx 333 LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 334 LEFT OUTER JOIN status ON status.status_idx = tests.status 335 LEFT OUTER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx; 336 """ 337 338 339 ORIG_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 356 RENAMES_UP = dict((name, 'tko_' + name) for name in ORIG_NAMES) 357 VIEWS_TO_DROP_UP = ( 358 'test_view', 359 'test_view_2', 360 'test_view_outer_joins', 361 'perf_view', 362 'perf_view_2', 363 ) 364 365 RENAMES_DOWN = dict((value, key) for key, value in RENAMES_UP.iteritems()) 366 VIEWS_TO_DROP_DOWN = ['tko_' + view for view in VIEWS_TO_DROP_UP] 367 368 369 def 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 375 def 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
