Add job queued, started and finished timestamps to the tko db and

modify the parsing code to find these timestamps in the job keyval
file and insert them into the db.

Signed-off-by: John Admanski <jadmanski@google.com>



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

1 files changed, 74 insertions, 0 deletions

diff --git a/tko/migrations/002_add_job_timestamps.py b/tko/migrations/002_add_job_timestamps.py
new file mode 100644
index 00000000..73a1cccc
--- /dev/null
+++ b/tko/migrations/002_add_job_timestamps.py
@@ -0,0 +1,74 @@
+def migrate_up(manager):
+	manager.execute_script(ADD_COLUMNS_SQL)
+	manager.execute_script(ALTER_VIEWS_SQL)
+
+
+ADD_COLUMNS_SQL = """\
+ALTER TABLE jobs ADD COLUMN queued_time datetime NULL;
+ALTER TABLE jobs ADD COLUMN started_time datetime NULL;
+ALTER TABLE jobs ADD COLUMN finished_time datetime NULL;
+"""
+
+
+ALTER_VIEWS_SQL = """\
+ALTER VIEW test_view AS
+SELECT	tests.test_idx,
+	tests.job_idx,
+	tests.test,
+	tests.subdir,
+	tests.kernel_idx,
+	tests.status,
+	tests.reason,
+	tests.machine_idx,
+	jobs.tag AS job_tag,
+	jobs.label AS job_label,
+	jobs.username AS job_username,
+	jobs.queued_time AS job_queued_time,
+	jobs.started_time AS job_started_time,
+	jobs.finished_time AS job_finished_time,
+	machines.hostname AS machine_hostname,
+	machines.machine_group,
+	machines.owner AS machine_owner,
+	kernels.kernel_hash,
+	kernels.base AS kernel_base, 
+	kernels.printable AS kernel_printable,
+	status.word AS status_word
+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;
+
+-- perf_view (to make life easier for people trying to mine performance data)
+ALTER VIEW perf_view AS
+SELECT	tests.test_idx,
+	tests.job_idx,
+	tests.test,
+	tests.subdir,
+	tests.kernel_idx,
+	tests.status,
+	tests.reason,
+	tests.machine_idx,
+	jobs.tag AS job_tag,
+	jobs.label AS job_label,
+	jobs.username AS job_username,
+	jobs.queued_time AS job_queued_time,
+	jobs.started_time AS job_started_time,
+	jobs.finished_time AS job_finished_time,
+	machines.hostname AS machine_hostname,
+	machines.machine_group,
+	machines.owner AS machine_owner,
+	kernels.kernel_hash,
+	kernels.base AS kernel_base, 
+	kernels.printable AS kernel_printable,
+	status.word AS status_word,
+	iteration_result.iteration,
+	iteration_result.attribute AS iteration_key,
+	iteration_result.value AS iteration_value
+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
+INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx;
+"""