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 61ALTER TABLE afe_special_tasks 62MODIFY COLUMN requested_by_id INT NOT NULL; 63""" 64 65 66DOWN_SQL = """ 67ALTER TABLE afe_special_tasks 68MODIFY COLUMN requested_by_id INT DEFAULT NULL; 69 70ALTER TABLE afe_users 71DROP FOREIGN KEY afe_users_drone_set_ibfk; 72 73ALTER TABLE afe_users 74DROP COLUMN drone_set_id; 75 76ALTER TABLE afe_jobs 77DROP FOREIGN KEY afe_jobs_drone_set_ibfk; 78 79ALTER TABLE afe_jobs 80DROP COLUMN drone_set_id; 81 82DROP TABLE IF EXISTS afe_drone_sets_drones; 83DROP TABLE IF EXISTS afe_drone_sets; 84DROP TABLE IF EXISTS afe_drones; 85""" 86