Add topology_id concept to database

2 files changed, 60 insertions, 43 deletions

diff --git a/database/schema.sql b/database/schema.sql
index 988f527a..644fbd53 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -257,12 +257,16 @@ CREATE TABLE rooms (
   name          TEXT                    NOT NULL,
   datacenter_id INTEGER                 NOT NULL,
   type          VARCHAR(50)             NOT NULL,
+  topology_id   INTEGER,
 
   FOREIGN KEY (datacenter_id) REFERENCES datacenters (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
   FOREIGN KEY (type) REFERENCES room_types (name)
     ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (topology_id) REFERENCES rooms (id)
+    ON DELETE NO ACTION
     ON UPDATE CASCADE
 );
 
@@ -285,19 +289,23 @@ INSERT INTO room_types (name) VALUES ('COOLING');
 -- Tiles in a room
 DROP TABLE IF EXISTS tiles;
 CREATE TABLE tiles (
-  id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
-  position_x INTEGER                 NOT NULL,
-  position_y INTEGER                 NOT NULL,
-  room_id    INTEGER                 NOT NULL,
-  object_id  INTEGER,
+  id          INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  position_x  INTEGER                 NOT NULL,
+  position_y  INTEGER                 NOT NULL,
+  room_id     INTEGER                 NOT NULL,
+  object_id   INTEGER,
+  topology_id INTEGER,
 
   FOREIGN KEY (room_id) REFERENCES rooms (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
   FOREIGN KEY (object_id) REFERENCES objects (id),
+  FOREIGN KEY (topology_id) REFERENCES tiles (id)
+    ON DELETE NO ACTION
+    ON UPDATE CASCADE,
 
   UNIQUE (position_x, position_y, room_id), -- only one tile can be in the same position in a room
-  UNIQUE (object_id)                              -- an object can only be on one tile
+  UNIQUE (object_id)                        -- an object can only be on one tile
 );
 
 DELIMITER //
@@ -490,10 +498,14 @@ CREATE TABLE racks (
   name             TEXT,
   capacity         INTEGER NOT NULL CHECK (capacity > 0),
   power_capacity_w INTEGER NOT NULL CHECK (power_capacity_w > 0),
+  topology_id      INTEGER,
 
   FOREIGN KEY (id) REFERENCES objects (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
+  FOREIGN KEY (topology_id) REFERENCES racks (id)
+    ON DELETE NO ACTION
+    ON UPDATE CASCADE,
 
   PRIMARY KEY (id)
 );
@@ -506,13 +518,17 @@ CREATE TABLE racks (
 -- Machines in racks
 DROP TABLE IF EXISTS machines;
 CREATE TABLE machines (
-  id       INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
-  rack_id  INTEGER                 NOT NULL,
-  position INTEGER                 NOT NULL CHECK (position > 0),
+  id          INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  rack_id     INTEGER                 NOT NULL,
+  position    INTEGER                 NOT NULL CHECK (position > 0),
+  topology_id INTEGER,
 
   FOREIGN KEY (rack_id) REFERENCES racks (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
+  FOREIGN KEY (topology_id) REFERENCES machines (id)
+    ON DELETE NO ACTION
+    ON UPDATE CASCADE,
 
   -- Prevent machines from occupying the same position in a rack.
   UNIQUE (rack_id, position)
@@ -521,7 +537,7 @@ CREATE TABLE machines (
 DELIMITER //
 
 -- Make sure a machine is not inserted at a position that does not exist for its rack.
-DROP TRIGGER IF EXISTS before_inser_machine;
+DROP TRIGGER IF EXISTS before_insert_machine;
 CREATE TRIGGER before_insert_machine
 BEFORE INSERT ON machines
 FOR EACH ROW
diff --git a/database/test.sql b/database/test.sql
index d666b6a1..fa7fb8aa 100644
--- a/database/test.sql
+++ b/database/test.sql
@@ -188,8 +188,8 @@ VALUES (1, 1, 3, 'FIFO', 'Path planning trace, FIFO', 'QUEUED', 0);
 
 -- Rooms
 INSERT INTO rooms (name, datacenter_id, type) VALUES ('room 1', 1, 'SERVER');
-INSERT INTO rooms (name, datacenter_id, type) VALUES ('room 1', 2, 'SERVER');
-INSERT INTO rooms (name, datacenter_id, type) VALUES ('room 1', 3, 'SERVER');
+INSERT INTO rooms (name, datacenter_id, type, topology_id) VALUES ('room 1', 2, 'SERVER', 1);
+INSERT INTO rooms (name, datacenter_id, type, topology_id) VALUES ('room 1', 3, 'SERVER', 1);
 INSERT INTO rooms (name, datacenter_id, type) VALUES ('room 2', 3, 'SERVER');
 INSERT INTO rooms (name, datacenter_id, type) VALUES ('Power Room', 1, 'POWER');
 
@@ -198,15 +198,16 @@ INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 10, 1);
 INSERT INTO tiles (position_x, position_y, room_id) VALUES (9, 10, 1);
 INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 11, 1);
 
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 10, 2);
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (9, 10, 2);
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 11, 2);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (10, 10, 2, 1);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (9, 10, 2, 2);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (10, 11, 2, 3);
 INSERT INTO tiles (position_x, position_y, room_id) VALUES (11, 11, 2);
 
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 10, 3);
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (9, 10, 3);
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 11, 3);
-INSERT INTO tiles (position_x, position_y, room_id) VALUES (11, 11, 3);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (10, 10, 3, 1);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (9, 10, 3, 2);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (10, 11, 3, 3);
+INSERT INTO tiles (position_x, position_y, room_id, topology_id) VALUES (11, 11, 3, 7);
+
 INSERT INTO tiles (position_x, position_y, room_id) VALUES (11, 10, 4);
 INSERT INTO tiles (position_x, position_y, room_id) VALUES (12, 10, 4);
 
@@ -215,52 +216,52 @@ INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 13, 5);
 
 -- Racks
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (1, 42, 'rack 1', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (1, 42, 'Rack 1', 5000);
 UPDATE tiles
 SET object_id = 1
 WHERE id = 1;
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (2, 42, 'rack 2', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (2, 42, 'Rack 2', 5000);
 UPDATE tiles
 SET object_id = 2
 WHERE id = 2;
 
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (3, 42, 'rack 1', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w, topology_id) VALUES (3, 42, 'Rack 1', 5000, 1);
 UPDATE tiles
 SET object_id = 3
 WHERE id = 4;
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (4, 42, 'rack 2', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w, topology_id) VALUES (4, 42, 'Rack 2', 5000, 2);
 UPDATE tiles
 SET object_id = 4
 WHERE id = 5;
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (5, 42, 'rack 3', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (5, 42, 'Rack 3', 5000);
 UPDATE tiles
 SET object_id = 5
 WHERE id = 7;
 
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (6, 42, 'rack 1', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w, topology_id) VALUES (6, 42, 'Rack 1', 5000, 1);
 UPDATE tiles
 SET object_id = 6
 WHERE id = 8;
 
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (7, 42, 'rack 2', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w, topology_id) VALUES (7, 42, 'Rack 2', 5000, 2);
 UPDATE tiles
 SET object_id = 7
 WHERE id = 9;
 
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (8, 42, 'rack 3', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w, topology_id) VALUES (8, 42, 'Rack 3', 5000, 5);
 UPDATE tiles
 SET object_id = 8
 WHERE id = 11;
 
 INSERT INTO objects (type) VALUES ('RACK');
-INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (9, 42, 'rack 4', 5000);
+INSERT INTO racks (id, capacity, name, power_capacity_w) VALUES (9, 42, 'Rack 4', 5000);
 UPDATE tiles
 SET object_id = 9
 WHERE id = 12;
@@ -273,25 +274,25 @@ INSERT INTO machines (rack_id, position) VALUES (1, 10);
 INSERT INTO machines (rack_id, position) VALUES (2, 1);
 INSERT INTO machines (rack_id, position) VALUES (2, 2);
 
-INSERT INTO machines (rack_id, position) VALUES (3, 1);
-INSERT INTO machines (rack_id, position) VALUES (3, 2);
-INSERT INTO machines (rack_id, position) VALUES (3, 6);
-INSERT INTO machines (rack_id, position) VALUES (3, 10);
-INSERT INTO machines (rack_id, position) VALUES (4, 1);
-INSERT INTO machines (rack_id, position) VALUES (4, 2);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (3, 1, 1);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (3, 2, 2);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (3, 6, 3);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (3, 10, 4);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (4, 1, 5);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (4, 2, 6);
 INSERT INTO machines (rack_id, position) VALUES (5, 1);
 INSERT INTO machines (rack_id, position) VALUES (5, 2);
 INSERT INTO machines (rack_id, position) VALUES (5, 3);
 
-INSERT INTO machines (rack_id, position) VALUES (6, 1);
-INSERT INTO machines (rack_id, position) VALUES (6, 2);
-INSERT INTO machines (rack_id, position) VALUES (6, 6);
-INSERT INTO machines (rack_id, position) VALUES (6, 10);
-INSERT INTO machines (rack_id, position) VALUES (7, 1);
-INSERT INTO machines (rack_id, position) VALUES (7, 2);
-INSERT INTO machines (rack_id, position) VALUES (8, 1);
-INSERT INTO machines (rack_id, position) VALUES (8, 2);
-INSERT INTO machines (rack_id, position) VALUES (8, 3);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (6, 1, 1);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (6, 2, 2);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (6, 6, 3);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (6, 10, 4);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (7, 1, 5);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (7, 2, 6);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (8, 1, 13);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (8, 2, 14);
+INSERT INTO machines (rack_id, position, topology_id) VALUES (8, 3, 15);
 INSERT INTO machines (rack_id, position) VALUES (9, 4);
 INSERT INTO machines (rack_id, position) VALUES (9, 5);
 INSERT INTO machines (rack_id, position) VALUES (9, 6);