diff options
Diffstat (limited to 'database')
| -rw-r--r-- | database/schema.sql | 36 | ||||
| -rw-r--r-- | database/test.sql | 67 |
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); |