Add ability to associate drone sets with jobs. This restricts a job to
running on a specified set of drones.
Signed-off-by: James Ren <jamesren@google.com>
git-svn-id: http://test.kernel.org/svn/autotest/trunk@4439 592f7852-d20e-0410-864c-8624ca9c26a4
diff --git a/frontend/migrations/058_drone_management.py b/frontend/migrations/058_drone_management.py
new file mode 100644
index 0000000..183e921
--- /dev/null
+++ b/frontend/migrations/058_drone_management.py
@@ -0,0 +1,85 @@
+UP_SQL = """
+CREATE TABLE afe_drones (
+ id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
+ hostname VARCHAR(255) NOT NULL
+) ENGINE=InnoDB;
+
+ALTER TABLE afe_drones
+ADD CONSTRAINT afe_drones_unique
+UNIQUE KEY (hostname);
+
+
+CREATE TABLE afe_drone_sets (
+ id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
+ name VARCHAR(255) NOT NULL
+) ENGINE=InnoDB;
+
+ALTER TABLE afe_drone_sets
+ADD CONSTRAINT afe_drone_sets_unique
+UNIQUE KEY (name);
+
+
+CREATE TABLE afe_drone_sets_drones (
+ id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
+ droneset_id INT NOT NULL,
+ drone_id INT NOT NULL
+) ENGINE=InnoDB;
+
+ALTER TABLE afe_drone_sets_drones
+ADD CONSTRAINT afe_drone_sets_drones_droneset_ibfk
+FOREIGN KEY (droneset_id) REFERENCES afe_drone_sets (id);
+
+ALTER TABLE afe_drone_sets_drones
+ADD CONSTRAINT afe_drone_sets_drones_drone_ibfk
+FOREIGN KEY (drone_id) REFERENCES afe_drones (id);
+
+ALTER TABLE afe_drone_sets_drones
+ADD CONSTRAINT afe_drone_sets_drones_unique
+UNIQUE KEY (droneset_id, drone_id);
+
+
+ALTER TABLE afe_jobs
+ADD COLUMN drone_set_id INT;
+
+ALTER TABLE afe_jobs
+ADD CONSTRAINT afe_jobs_drone_set_ibfk
+FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id);
+
+
+ALTER TABLE afe_users
+ADD COLUMN drone_set_id INT;
+
+ALTER TABLE afe_users
+ADD CONSTRAINT afe_users_drone_set_ibfk
+FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id);
+
+
+UPDATE afe_special_tasks SET requested_by_id = (
+ SELECT id FROM afe_users WHERE login = 'autotest_system')
+WHERE requested_by_id IS NULL;
+
+ALTER TABLE afe_special_tasks
+MODIFY COLUMN requested_by_id INT NOT NULL;
+"""
+
+
+DOWN_SQL = """
+ALTER TABLE afe_special_tasks
+MODIFY COLUMN requested_by_id INT DEFAULT NULL;
+
+ALTER TABLE afe_users
+DROP FOREIGN KEY afe_users_drone_set_ibfk;
+
+ALTER TABLE afe_users
+DROP COLUMN drone_set_id;
+
+ALTER TABLE afe_jobs
+DROP FOREIGN KEY afe_jobs_drone_set_ibfk;
+
+ALTER TABLE afe_jobs
+DROP COLUMN drone_set_id;
+
+DROP TABLE IF EXISTS afe_drone_sets_drones;
+DROP TABLE IF EXISTS afe_drone_sets;
+DROP TABLE IF EXISTS afe_drones;
+"""