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