Home | History | Annotate | Download | only in migrations 
      1 def migrate_up(manager):
      2     manager.execute_script(ADD_COLUMN_SQL)
      3     manager.execute_script(ALTER_VIEWS_UP_SQL)
      4 
      5 
      6 def migrate_down(manager):
      7     manager.execute_script(DROP_COLUMN_SQL)
      8     manager.execute_script(ALTER_VIEWS_DOWN_SQL)
      9 
     10 
     11 ADD_COLUMN_SQL = """\
     12 ALTER TABLE tests ADD COLUMN finished_time datetime NULL;
     13 """
     14 
     15 DROP_COLUMN_SQL = """\
     16 ALTER TABLE tests DROP finished_time;
     17 """
     18 
     19 ALTER_VIEWS_UP_SQL = """\
     20 ALTER VIEW test_view AS
     21 SELECT  tests.test_idx,
     22         tests.job_idx,
     23         tests.test,
     24         tests.subdir,
     25         tests.kernel_idx,
     26         tests.status,
     27         tests.reason,
     28         tests.machine_idx,
     29         tests.finished_time AS test_finished_time,
     30         jobs.tag AS job_tag,
     31         jobs.label AS job_label,
     32         jobs.username AS job_username,
     33         jobs.queued_time AS job_queued_time,
     34         jobs.started_time AS job_started_time,
     35         jobs.finished_time AS job_finished_time,
     36         machines.hostname AS machine_hostname,
     37         machines.machine_group,
     38         machines.owner AS machine_owner,
     39         kernels.kernel_hash,
     40         kernels.base AS kernel_base,
     41         kernels.printable AS kernel_printable,
     42         status.word AS status_word
     43 FROM tests
     44 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
     45 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
     46 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     47 INNER JOIN status ON status.status_idx = tests.status;
     48 
     49 -- perf_view (to make life easier for people trying to mine performance data)
     50 ALTER VIEW perf_view AS
     51 SELECT  tests.test_idx,
     52         tests.job_idx,
     53         tests.test,
     54         tests.subdir,
     55         tests.kernel_idx,
     56         tests.status,
     57         tests.reason,
     58         tests.machine_idx,
     59         tests.finished_time AS test_finished_time,
     60         jobs.tag AS job_tag,
     61         jobs.label AS job_label,
     62         jobs.username AS job_username,
     63         jobs.queued_time AS job_queued_time,
     64         jobs.started_time AS job_started_time,
     65         jobs.finished_time AS job_finished_time,
     66         machines.hostname AS machine_hostname,
     67         machines.machine_group,
     68         machines.owner AS machine_owner,
     69         kernels.kernel_hash,
     70         kernels.base AS kernel_base,
     71         kernels.printable AS kernel_printable,
     72         status.word AS status_word,
     73         iteration_result.iteration,
     74         iteration_result.attribute AS iteration_key,
     75         iteration_result.value AS iteration_value
     76 FROM tests
     77 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
     78 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
     79 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     80 INNER JOIN status ON status.status_idx = tests.status
     81 INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx;
     82 """
     83 
     84 
     85 ALTER_VIEWS_DOWN_SQL = """\
     86 ALTER VIEW test_view AS
     87 SELECT  tests.test_idx,
     88         tests.job_idx,
     89         tests.test,
     90         tests.subdir,
     91         tests.kernel_idx,
     92         tests.status,
     93         tests.reason,
     94         tests.machine_idx,
     95         jobs.tag AS job_tag,
     96         jobs.label AS job_label,
     97         jobs.username AS job_username,
     98         jobs.queued_time AS job_queued_time,
     99         jobs.started_time AS job_started_time,
    100         jobs.finished_time AS job_finished_time,
    101         machines.hostname AS machine_hostname,
    102         machines.machine_group,
    103         machines.owner AS machine_owner,
    104         kernels.kernel_hash,
    105         kernels.base AS kernel_base,
    106         kernels.printable AS kernel_printable,
    107         status.word AS status_word
    108 FROM tests
    109 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
    110 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
    111 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
    112 INNER JOIN status ON status.status_idx = tests.status;
    113 
    114 -- perf_view (to make life easier for people trying to mine performance data)
    115 ALTER VIEW perf_view AS
    116 SELECT  tests.test_idx,
    117         tests.job_idx,
    118         tests.test,
    119         tests.subdir,
    120         tests.kernel_idx,
    121         tests.status,
    122         tests.reason,
    123         tests.machine_idx,
    124         jobs.tag AS job_tag,
    125         jobs.label AS job_label,
    126         jobs.username AS job_username,
    127         jobs.queued_time AS job_queued_time,
    128         jobs.started_time AS job_started_time,
    129         jobs.finished_time AS job_finished_time,
    130         machines.hostname AS machine_hostname,
    131         machines.machine_group,
    132         machines.owner AS machine_owner,
    133         kernels.kernel_hash,
    134         kernels.base AS kernel_base,
    135         kernels.printable AS kernel_printable,
    136         status.word AS status_word,
    137         iteration_result.iteration,
    138         iteration_result.attribute AS iteration_key,
    139         iteration_result.value AS iteration_value
    140 FROM tests
    141 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
    142 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
    143 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
    144 INNER JOIN status ON status.status_idx = tests.status
    145 INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx;
    146 """
    147