1UP_SQL = """ 2ALTER TABLE jobs 3ADD COLUMN afe_job_id INT DEFAULT NULL; 4 5UPDATE jobs 6SET afe_job_id = SUBSTRING_INDEX(tag, '-', 1) 7WHERE tag REGEXP '^[0-9]+-.+/.+$'; 8 9CREATE INDEX afe_job_id 10ON jobs(afe_job_id); 11 12ALTER VIEW test_view_2 AS 13SELECT tests.test_idx, 14 tests.job_idx, 15 tests.test AS test_name, 16 tests.subdir, 17 tests.kernel_idx, 18 tests.status AS status_idx, 19 tests.reason, 20 tests.machine_idx, 21 tests.started_time AS test_started_time, 22 tests.finished_time AS test_finished_time, 23 jobs.tag AS job_tag, 24 jobs.label AS job_name, 25 jobs.username AS job_owner, 26 jobs.queued_time AS job_queued_time, 27 jobs.started_time AS job_started_time, 28 jobs.finished_time AS job_finished_time, 29 jobs.afe_job_id AS afe_job_id, 30 machines.hostname AS hostname, 31 machines.machine_group AS platform, 32 machines.owner AS machine_owner, 33 kernels.kernel_hash, 34 kernels.base AS kernel_base, 35 kernels.printable AS kernel, 36 status.word AS status 37FROM tests 38INNER JOIN jobs ON jobs.job_idx = tests.job_idx 39INNER JOIN machines ON machines.machine_idx = jobs.machine_idx 40INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 41INNER JOIN status ON status.status_idx = tests.status; 42""" 43 44DOWN_SQL = """ 45ALTER VIEW test_view_2 AS 46SELECT tests.test_idx, 47 tests.job_idx, 48 tests.test AS test_name, 49 tests.subdir, 50 tests.kernel_idx, 51 tests.status AS status_idx, 52 tests.reason, 53 tests.machine_idx, 54 tests.started_time AS test_started_time, 55 tests.finished_time AS test_finished_time, 56 jobs.tag AS job_tag, 57 jobs.label AS job_name, 58 jobs.username AS job_owner, 59 jobs.queued_time AS job_queued_time, 60 jobs.started_time AS job_started_time, 61 jobs.finished_time AS job_finished_time, 62 machines.hostname AS hostname, 63 machines.machine_group AS platform, 64 machines.owner AS machine_owner, 65 kernels.kernel_hash, 66 kernels.base AS kernel_base, 67 kernels.printable AS kernel, 68 status.word AS status 69FROM tests 70INNER JOIN jobs ON jobs.job_idx = tests.job_idx 71INNER JOIN machines ON machines.machine_idx = jobs.machine_idx 72INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx 73INNER JOIN status ON status.status_idx = tests.status; 74 75ALTER TABLE jobs 76DROP COLUMN afe_job_id; 77""" 78