Added afe_job_id column to the jobs table in TKO. Changed link in AFE

View Job tab to use afe_job_id instead of job_tag.

Signed-off-by: James Ren <jamesren@google.com>


git-svn-id: svn://test.kernel.org/autotest/trunk@3665 592f7852-d20e-0410-864c-8624ca9c26a4

2 files changed, 85 insertions, 1 deletions

diff --git a/tko/db.py b/tko/db.py
index ec833553..4b3f82ad 100644
--- a/tko/db.py
+++ b/tko/db.py
@@ -318,13 +318,20 @@ class db_sql(object):
         else:
             self.update_machine_information(job, commit=commit)
 
+        afe_job_id = None
+        pattern = re.compile('^([0-9]+)-.+/.+$')
+        match = pattern.match(tag)
+        if match:
+            afe_job_id = match.group(1)
+
         self.insert('jobs', {'tag':tag,
                              'label': job.label,
                              'username': job.user,
                              'machine_idx': job.machine_idx,
                              'queued_time': job.queued_time,
                              'started_time': job.started_time,
-                             'finished_time': job.finished_time},
+                             'finished_time': job.finished_time,
+                             'afe_job_id': afe_job_id},
                              commit=commit)
         job.index = self.get_last_autonumber_value()
         for test in job.tests:
diff --git a/tko/migrations/030_add_afe_job_id_to_jobs.py b/tko/migrations/030_add_afe_job_id_to_jobs.py
new file mode 100644
index 00000000..24427af0
--- /dev/null
+++ b/tko/migrations/030_add_afe_job_id_to_jobs.py
@@ -0,0 +1,77 @@
+UP_SQL = """
+ALTER TABLE jobs
+ADD COLUMN afe_job_id INT DEFAULT NULL;
+
+UPDATE jobs
+SET afe_job_id = SUBSTRING_INDEX(tag, '-', 1)
+WHERE tag REGEXP '^[0-9]+-.+/.+$';
+
+CREATE INDEX afe_job_id
+ON jobs(afe_job_id);
+
+ALTER VIEW test_view_2 AS
+SELECT  tests.test_idx,
+        tests.job_idx,
+        tests.test AS test_name,
+        tests.subdir,
+        tests.kernel_idx,
+        tests.status AS status_idx,
+        tests.reason,
+        tests.machine_idx,
+        tests.started_time AS test_started_time,
+        tests.finished_time AS test_finished_time,
+        jobs.tag AS job_tag,
+        jobs.label AS job_name,
+        jobs.username AS job_owner,
+        jobs.queued_time AS job_queued_time,
+        jobs.started_time AS job_started_time,
+        jobs.finished_time AS job_finished_time,
+        jobs.afe_job_id AS afe_job_id,
+        machines.hostname AS hostname,
+        machines.machine_group AS platform,
+        machines.owner AS machine_owner,
+        kernels.kernel_hash,
+        kernels.base AS kernel_base,
+        kernels.printable AS kernel,
+        status.word AS status
+FROM tests
+INNER JOIN jobs ON jobs.job_idx = tests.job_idx
+INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
+INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
+INNER JOIN status ON status.status_idx = tests.status;
+"""
+
+DOWN_SQL = """
+ALTER VIEW test_view_2 AS
+SELECT  tests.test_idx,
+        tests.job_idx,
+        tests.test AS test_name,
+        tests.subdir,
+        tests.kernel_idx,
+        tests.status AS status_idx,
+        tests.reason,
+        tests.machine_idx,
+        tests.started_time AS test_started_time,
+        tests.finished_time AS test_finished_time,
+        jobs.tag AS job_tag,
+        jobs.label AS job_name,
+        jobs.username AS job_owner,
+        jobs.queued_time AS job_queued_time,
+        jobs.started_time AS job_started_time,
+        jobs.finished_time AS job_finished_time,
+        machines.hostname AS hostname,
+        machines.machine_group AS platform,
+        machines.owner AS machine_owner,
+        kernels.kernel_hash,
+        kernels.base AS kernel_base,
+        kernels.printable AS kernel,
+        status.word AS status
+FROM tests
+INNER JOIN jobs ON jobs.job_idx = tests.job_idx
+INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
+INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
+INNER JOIN status ON status.status_idx = tests.status;
+
+ALTER TABLE jobs
+DROP COLUMN afe_job_id;
+"""