Home | History | Annotate | Download | only in migrations 
      1 UP_SQL = """
      2 ALTER TABLE jobs
      3 ADD COLUMN afe_job_id INT DEFAULT NULL;
      4 
      5 UPDATE jobs
      6 SET afe_job_id = SUBSTRING_INDEX(tag, '-', 1)
      7 WHERE tag REGEXP '^[0-9]+-.+/.+$';
      8 
      9 CREATE INDEX afe_job_id
     10 ON jobs(afe_job_id);
     11 
     12 ALTER VIEW test_view_2 AS
     13 SELECT  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
     37 FROM tests
     38 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
     39 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
     40 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     41 INNER JOIN status ON status.status_idx = tests.status;
     42 """
     43 
     44 DOWN_SQL = """
     45 ALTER VIEW test_view_2 AS
     46 SELECT  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
     69 FROM tests
     70 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
     71 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
     72 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     73 INNER JOIN status ON status.status_idx = tests.status;
     74 
     75 ALTER TABLE jobs
     76 DROP COLUMN afe_job_id;
     77 """
     78