Add a foreign key from tests->jobs. Requires modifying tests.job_idx

to be of the same data type as jobs.job_idx.

Risk: Low
Visibility: Adds another foreign key constraint.

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



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

1 files changed, 19 insertions, 0 deletions

diff --git a/tko/migrations/028_add_tests_jobs_foreign_key.py b/tko/migrations/028_add_tests_jobs_foreign_key.py
new file mode 100644
index 00000000..d474f08f
--- /dev/null
+++ b/tko/migrations/028_add_tests_jobs_foreign_key.py
@@ -0,0 +1,19 @@
+ADD_FOREIGN_KEYS = """
+ALTER TABLE tests MODIFY COLUMN job_idx int(10) unsigned NOT NULL;
+
+DELETE FROM tests WHERE job_idx NOT IN (SELECT job_idx FROM jobs);
+
+ALTER TABLE tests ADD CONSTRAINT tests_to_jobs_ibfk
+    FOREIGN KEY (job_idx) REFERENCES jobs (job_idx);
+"""
+
+DROP_FOREIGN_KEYS = """
+ALTER TABLE tests DROP FOREIGN KEY tests_to_jobs_ibfk;
+ALTER TABLE tests MODIFY COLUMN job_idx int(11) NOT NULL;
+"""
+
+def migrate_up(mgr):
+    mgr.execute_script(ADD_FOREIGN_KEYS)
+
+def migrate_down(mgr):
+    mgr.execute_script(DROP_FOREIGN_KEYS)