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