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