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