Home | History | Annotate | Download | only in migrations 
      1 def migrate_up(manager):
      2     manager.execute(CREATE_QUERIES_TABLE)
      3     manager.execute(CREATE_TEST_VIEW_OUTER_JOINS)
      4     manager.execute(CREATE_PERF_VIEW_2)
      5 
      6 def migrate_down(manager):
      7     manager.execute(DROP_QUERIES_TABLE)
      8     manager.execute(DROP_TEST_VIEW_OUTER_JOINS)
      9     manager.execute(DROP_PERF_VIEW_2)
     10 
     11 
     12 CREATE_QUERIES_TABLE = """\
     13 CREATE TABLE embedded_graphing_queries (
     14     id INT NOT NULL AUTO_INCREMENT,
     15     url_token TEXT NOT NULL,
     16     graph_type VARCHAR(16) NOT NULL,
     17     params TEXT NOT NULL,
     18     last_accessed DATETIME NOT NULL,
     19     PRIMARY KEY(id),
     20     INDEX (url_token(128)))
     21 """
     22 
     23 DROP_QUERIES_TABLE = """\
     24 DROP TABLE IF EXISTS embedded_graphing_queries
     25 """
     26 
     27 CREATE_TEST_VIEW_OUTER_JOINS = """\
     28 CREATE VIEW test_view_outer_joins AS
     29 SELECT  tests.test_idx,
     30         tests.job_idx,
     31         tests.test AS test_name,
     32         tests.subdir,
     33         tests.kernel_idx,
     34         tests.status AS status_idx,
     35         tests.reason,
     36         tests.machine_idx,
     37         tests.started_time AS test_started_time,
     38         tests.finished_time AS test_finished_time,
     39         jobs.tag AS job_tag,
     40         jobs.label AS job_name,
     41         jobs.username AS job_owner,
     42         jobs.queued_time AS job_queued_time,
     43         jobs.started_time AS job_started_time,
     44         jobs.finished_time AS job_finished_time,
     45         machines.hostname AS hostname,
     46         machines.machine_group AS platform,
     47         machines.owner AS machine_owner,
     48         kernels.kernel_hash,
     49         kernels.base AS kernel_base,
     50         kernels.printable AS kernel,
     51         status.word AS status
     52 FROM tests
     53 LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx
     54 LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx
     55 LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     56 LEFT OUTER JOIN status ON status.status_idx = tests.status;
     57 """
     58 
     59 DROP_TEST_VIEW_OUTER_JOINS = """\
     60 DROP VIEW IF EXISTS test_view_outer_joins
     61 """
     62 
     63 CREATE_PERF_VIEW_2 = """\
     64 CREATE VIEW perf_view_2 AS
     65 SELECT  tests.test_idx,
     66         tests.job_idx,
     67         tests.test AS test_name,
     68         tests.subdir,
     69         tests.kernel_idx,
     70         tests.status AS status_idx,
     71         tests.reason,
     72         tests.machine_idx,
     73         tests.started_time AS test_started_time,
     74         tests.finished_time AS test_finished_time,
     75         jobs.tag AS job_tag,
     76         jobs.label AS job_name,
     77         jobs.username AS job_owner,
     78         jobs.queued_time AS job_queued_time,
     79         jobs.started_time AS job_started_time,
     80         jobs.finished_time AS job_finished_time,
     81         machines.hostname AS hostname,
     82         machines.machine_group AS platform,
     83         machines.owner AS machine_owner,
     84         kernels.kernel_hash,
     85         kernels.base AS kernel_base,
     86         kernels.printable AS kernel,
     87         status.word AS status,
     88         iteration_result.iteration,
     89         iteration_result.attribute AS iteration_key,
     90         iteration_result.value AS iteration_value
     91 FROM tests
     92 INNER JOIN jobs ON jobs.job_idx = tests.job_idx
     93 INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
     94 INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
     95 INNER JOIN status ON status.status_idx = tests.status
     96 INNER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx;
     97 """
     98 
     99 DROP_PERF_VIEW_2 = """\
    100 DROP VIEW IF EXISTS perf_view_2
    101 """
    102