blob: 183e9219210f3f83ea23fa78bc9a6e14eb0ea0f4 [file] [log] [blame]
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;
"""