1UP_SQL = """ 2CREATE TABLE afe_drones ( 3 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 4 hostname VARCHAR(255) NOT NULL 5) ENGINE=InnoDB; 6 7ALTER TABLE afe_drones 8ADD CONSTRAINT afe_drones_unique 9UNIQUE KEY (hostname); 10 11 12CREATE TABLE afe_drone_sets ( 13 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 14 name VARCHAR(255) NOT NULL 15) ENGINE=InnoDB; 16 17ALTER TABLE afe_drone_sets 18ADD CONSTRAINT afe_drone_sets_unique 19UNIQUE KEY (name); 20 21 22CREATE TABLE afe_drone_sets_drones ( 23 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 24 droneset_id INT NOT NULL, 25 drone_id INT NOT NULL 26) ENGINE=InnoDB; 27 28ALTER TABLE afe_drone_sets_drones 29ADD CONSTRAINT afe_drone_sets_drones_droneset_ibfk 30FOREIGN KEY (droneset_id) REFERENCES afe_drone_sets (id); 31 32ALTER TABLE afe_drone_sets_drones 33ADD CONSTRAINT afe_drone_sets_drones_drone_ibfk 34FOREIGN KEY (drone_id) REFERENCES afe_drones (id); 35 36ALTER TABLE afe_drone_sets_drones 37ADD CONSTRAINT afe_drone_sets_drones_unique 38UNIQUE KEY (droneset_id, drone_id); 39 40 41ALTER TABLE afe_jobs 42ADD COLUMN drone_set_id INT; 43 44ALTER TABLE afe_jobs 45ADD CONSTRAINT afe_jobs_drone_set_ibfk 46FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id); 47 48 49ALTER TABLE afe_users 50ADD COLUMN drone_set_id INT; 51 52ALTER TABLE afe_users 53ADD CONSTRAINT afe_users_drone_set_ibfk 54FOREIGN KEY (drone_set_id) REFERENCES afe_drone_sets (id); 55 56 57UPDATE afe_special_tasks SET requested_by_id = ( 58 SELECT id FROM afe_users WHERE login = 'autotest_system') 59WHERE requested_by_id IS NULL; 60 61SET foreign_key_checks = 0; 62 63ALTER TABLE afe_special_tasks 64MODIFY COLUMN requested_by_id INT NOT NULL; 65 66SET foreign_key_checks = 1; 67""" 68 69 70DOWN_SQL = """ 71ALTER TABLE afe_special_tasks 72MODIFY COLUMN requested_by_id INT DEFAULT NULL; 73 74ALTER TABLE afe_users 75DROP FOREIGN KEY afe_users_drone_set_ibfk; 76 77ALTER TABLE afe_users 78DROP COLUMN drone_set_id; 79 80ALTER TABLE afe_jobs 81DROP FOREIGN KEY afe_jobs_drone_set_ibfk; 82 83ALTER TABLE afe_jobs 84DROP COLUMN drone_set_id; 85 86DROP TABLE IF EXISTS afe_drone_sets_drones; 87DROP TABLE IF EXISTS afe_drone_sets; 88DROP TABLE IF EXISTS afe_drones; 89""" 90