diff options
Diffstat (limited to 'database')
| -rw-r--r-- | database/schema.sql | 306 | ||||
| -rw-r--r-- | database/test.sql | 186 |
2 files changed, 263 insertions, 229 deletions
diff --git a/database/schema.sql b/database/schema.sql index 6338a457..c5e94ebe 100644 --- a/database/schema.sql +++ b/database/schema.sql @@ -1,10 +1,13 @@ +-- Tables referred to in foreign key constraints are defined after the constraints are defined +SET FOREIGN_KEY_CHECKS = 0; + /* * A user is identified by their google_id, which the server gets by authenticating with Google. */ -- Users CREATE TABLE IF NOT EXISTS users ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, google_id TEXT NOT NULL, email TEXT, given_name TEXT, @@ -18,9 +21,9 @@ CREATE TABLE IF NOT EXISTS users ( -- User authorizations CREATE TABLE IF NOT EXISTS authorizations ( - user_id INTEGER NOT NULL, - simulation_id INTEGER NOT NULL, - authorization_level TEXT NOT NULL, + user_id INTEGER NOT NULL, + simulation_id INTEGER NOT NULL, + authorization_level VARCHAR(50) NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE @@ -39,7 +42,7 @@ CREATE UNIQUE INDEX authorizations_index -- Authorization levels CREATE TABLE IF NOT EXISTS authorization_levels ( - level TEXT PRIMARY KEY NOT NULL + level VARCHAR(50) PRIMARY KEY NOT NULL ); INSERT INTO authorization_levels (level) VALUES ("OWN"); INSERT INTO authorization_levels (level) VALUES ("EDIT"); @@ -62,10 +65,10 @@ INSERT INTO authorization_levels (level) VALUES ("VIEW"); -- Simulation CREATE TABLE IF NOT EXISTS simulations ( - id INTEGER PRIMARY KEY NOT NULL, - datetime_created TEXT NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'), - datetime_last_edited TEXT NOT NULL CHECK (datetime_last_edited LIKE '____-__-__T__:__:__'), - name TEXT NOT NULL + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, + datetime_created VARCHAR(50) NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'), + datetime_last_edited VARCHAR(50) NOT NULL CHECK (datetime_last_edited LIKE '____-__-__T__:__:__'), + name VARCHAR(50) NOT NULL ); /* @@ -75,11 +78,11 @@ CREATE TABLE IF NOT EXISTS simulations ( */ CREATE TABLE IF NOT EXISTS experiments ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, simulation_id INTEGER NOT NULL, path_id INTEGER NOT NULL, trace_id INTEGER NOT NULL, - scheduler_name TEXT NOT NULL, + scheduler_name VARCHAR(50) NOT NULL, name TEXT NOT NULL, state TEXT NOT NULL, last_simulated_tick INTEGER NOT NULL DEFAULT 0 CHECK (last_simulated_tick >= 0), @@ -106,10 +109,10 @@ CREATE TABLE IF NOT EXISTS experiments ( -- Path CREATE TABLE IF NOT EXISTS paths ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, simulation_id INTEGER NOT NULL, name TEXT, - datetime_created TEXT NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'), + datetime_created VARCHAR(50) NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'), FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE @@ -118,7 +121,7 @@ CREATE TABLE IF NOT EXISTS paths ( -- Sections CREATE TABLE IF NOT EXISTS sections ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, path_id INTEGER NOT NULL, datacenter_id INTEGER NOT NULL, start_tick INTEGER NOT NULL CHECK (start_tick >= 0), @@ -145,13 +148,13 @@ INSERT INTO schedulers (name) VALUES ("FIFO"); -- A trace describes when tasks arrives in a datacenter CREATE TABLE IF NOT EXISTS traces ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, name TEXT NOT NULL ); -- A job CREATE TABLE IF NOT EXISTS jobs ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, name TEXT NOT NULL, trace_id INTEGER NOT NULL, @@ -162,12 +165,12 @@ CREATE TABLE IF NOT EXISTS jobs ( -- A task that's defined in terms of how many flops (floating point operations) it takes to complete CREATE TABLE IF NOT EXISTS tasks ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, start_tick INTEGER NOT NULL CHECK (start_tick >= 0), total_flop_count INTEGER NOT NULL, job_id INTEGER NOT NULL, task_dependency_id INTEGER NULL, - parallelizability TEXT NOT NULL, + parallelizability VARCHAR(50) NOT NULL, FOREIGN KEY (job_id) REFERENCES jobs (id) ON DELETE CASCADE @@ -184,7 +187,7 @@ CREATE TABLE IF NOT EXISTS tasks ( -- A state for a task_flop CREATE TABLE IF NOT EXISTS task_states ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, task_id INTEGER NOT NULL, experiment_id INTEGER NOT NULL, tick INTEGER NOT NULL CHECK (tick >= 0), @@ -197,7 +200,7 @@ CREATE TABLE IF NOT EXISTS task_states ( -- A machine state CREATE TABLE IF NOT EXISTS machine_states ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, task_id INTEGER, machine_id INTEGER NOT NULL, experiment_id INTEGER NOT NULL, @@ -220,7 +223,7 @@ CREATE TABLE IF NOT EXISTS machine_states ( -- Datacenters CREATE TABLE IF NOT EXISTS datacenters ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, simulation_id INTEGER NOT NULL, starred INTEGER CHECK (starred = 0 OR starred = 1), @@ -235,10 +238,10 @@ CREATE TABLE IF NOT EXISTS datacenters ( -- Rooms in a datacenter CREATE TABLE IF NOT EXISTS rooms ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, name TEXT NOT NULL, datacenter_id INTEGER NOT NULL, - type TEXT NOT NULL, + type VARCHAR(50) NOT NULL, FOREIGN KEY (datacenter_id) REFERENCES datacenters (id) ON DELETE CASCADE @@ -251,7 +254,7 @@ CREATE TABLE IF NOT EXISTS rooms ( ); CREATE TABLE IF NOT EXISTS room_types ( - name TEXT PRIMARY KEY NOT NULL + name VARCHAR(50) PRIMARY KEY NOT NULL ); INSERT INTO room_types (name) VALUES ('SERVER'); INSERT INTO room_types (name) VALUES ('HALLWAY'); @@ -267,7 +270,7 @@ INSERT INTO room_types (name) VALUES ('COOLING'); -- Tiles in a room CREATE TABLE IF NOT EXISTS tiles ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, position_x INTEGER NOT NULL, position_y INTEGER NOT NULL, room_id INTEGER NOT NULL, @@ -282,63 +285,61 @@ CREATE TABLE IF NOT EXISTS tiles ( UNIQUE (object_id) -- an object can only be on one tile ); --- Make sure this datacenter doesn't already have a tile in this location. -CREATE TRIGGER IF NOT EXISTS before_insert_tiles_check_existence - BEFORE - INSERT - ON tiles -BEGIN - - -- raise an error if... - SELECT RAISE(ABORT, 'OccupiedTilePosition') - WHERE ( +DELIMITER // +-- Make sure this datacenter doesn't already have a tile in this location +-- and tiles in a room are connected. +DROP TRIGGER IF EXISTS before_insert_tiles_check_existence; +CREATE TRIGGER before_insert_tiles_check_existence +BEFORE INSERT ON tiles +FOR EACH ROW + BEGIN + -- checking tile overlap -- a tile already exists such that.. - EXISTS(SELECT datacenter_id - FROM tiles - JOIN rooms ON tiles.room_id = rooms.id - WHERE ( - - -- it's in the same datacenter as the new tile... - datacenter_id = (SELECT datacenter_id - FROM rooms - WHERE rooms.id = NEW.room_id) - - -- and in the the same position as the new tile. - AND NEW.position_x = tiles.position_x AND NEW.position_y = tiles.position_y - )) - ); -END; - --- Make sure tiles in a room are all connected. -CREATE TRIGGER IF NOT EXISTS before_insert_tiles_check_adjacency - BEFORE - INSERT - ON tiles -BEGIN - - -- raise an error if... - SELECT RAISE(ABORT, 'InvalidTilePosition') - WHERE ( - + IF EXISTS(SELECT datacenter_id + FROM tiles + JOIN rooms ON tiles.room_id = rooms.id + WHERE ( + + -- it's in the same datacenter as the new tile... + datacenter_id = (SELECT datacenter_id + FROM rooms + WHERE rooms.id = NEW.room_id) + + -- and in the the same position as the new tile. + AND NEW.position_x = tiles.position_x AND NEW.position_y = tiles.position_y + )) + THEN + -- raise an error + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = "OccupiedTilePosition"; + END IF; + + -- checking tile adjacency -- this isn't the first tile, ... - EXISTS(SELECT * - FROM tiles - WHERE (NEW.room_id = tiles.room_id)) - - -- and the new tile isn't directly to right, to the left, above, or below an exisiting tile. - AND NOT EXISTS(SELECT * - FROM tiles - WHERE ( - NEW.room_id = tiles.room_id AND ( - (NEW.position_x + 1 = tiles.position_x AND NEW.position_y = tiles.position_y) -- right - OR (NEW.position_x - 1 = tiles.position_x AND NEW.position_y = tiles.position_y) -- left - OR (NEW.position_x = tiles.position_x AND NEW.position_y + 1 = tiles.position_y) -- above - OR (NEW.position_x = tiles.position_x AND NEW.position_y - 1 = tiles.position_y) -- below - ) - )) - ); -END; + IF (EXISTS(SELECT * + FROM tiles + WHERE (NEW.room_id = tiles.room_id)) + + -- and the new tile isn't directly to right, to the left, above, or below an exisiting tile. + AND NOT EXISTS(SELECT * + FROM tiles + WHERE ( + NEW.room_id = tiles.room_id AND ( + (NEW.position_x + 1 = tiles.position_x AND NEW.position_y = tiles.position_y) -- right + OR (NEW.position_x - 1 = tiles.position_x AND NEW.position_y = tiles.position_y) -- left + OR (NEW.position_x = tiles.position_x AND NEW.position_y + 1 = tiles.position_y) -- above + OR (NEW.position_x = tiles.position_x AND NEW.position_y - 1 = tiles.position_y) -- below + ) + ))) + THEN + -- raise an error + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'InvalidTilePosition'; + END IF; + END// + +DELIMITER ; /* * Objects are on tiles and have a type. They form an extra abstraction layer to make it easier to find what object is @@ -352,15 +353,15 @@ END; -- Objects CREATE TABLE IF NOT EXISTS objects ( - id INTEGER PRIMARY KEY NOT NULL, - type TEXT NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, + type VARCHAR(50) NOT NULL, FOREIGN KEY (type) REFERENCES object_types (name) ); -- Object types CREATE TABLE IF NOT EXISTS object_types ( - name TEXT PRIMARY KEY NOT NULL + name VARCHAR(50) PRIMARY KEY NOT NULL ); INSERT INTO object_types (name) VALUES ('PSU'); INSERT INTO object_types (name) VALUES ('COOLING_ITEM'); @@ -368,46 +369,53 @@ INSERT INTO object_types (name) VALUES ('RACK'); -- Allowed objects table CREATE TABLE IF NOT EXISTS allowed_objects ( - room_type TEXT NOT NULL, - object_type TEXT NOT NULL, + room_type VARCHAR(50) NOT NULL, + object_type VARCHAR(50) NOT NULL, FOREIGN KEY (room_type) REFERENCES room_types (name), - FOREIGN KEY (object_type) REFERENCES object_types + FOREIGN KEY (object_type) REFERENCES object_types (name) ); -- Allowed objects per room INSERT INTO allowed_objects (room_type, object_type) VALUES ('SERVER', 'RACK'); ---INSERT INTO allowed_objects (room_type, object_type) VALUES ('POWER', 'PSU'); ---INSERT INTO allowed_objects (room_type, object_type) VALUES ('COOLING', 'COOLING_ITEM'); +-- INSERT INTO allowed_objects (room_type, object_type) VALUES ('POWER', 'PSU'); +-- INSERT INTO allowed_objects (room_type, object_type) VALUES ('COOLING', 'COOLING_ITEM'); --- Make sure objects are added to tiles in rooms they're allowed to be in. -CREATE TRIGGER IF NOT EXISTS before_update_tiles - BEFORE - UPDATE - ON tiles -BEGIN - - -- raise an error if... - SELECT RAISE(ABORT, 'ForbiddenObjectType') - WHERE (NEW.object_id IS NOT NULL) AND ( - - -- the type of the object being added to the tile... - ( - SELECT objects.type - FROM objects - JOIN tiles ON tiles.object_id = objects.id - WHERE tiles.id = NEW.id - ) +DELIMITER // - -- is not in the set of allowed object types for the room the tile is in. - NOT IN ( - SELECT object_type - FROM allowed_objects - JOIN rooms ON rooms.type = allowed_objects.room_type - WHERE rooms.id = NEW.room_id - ) - ); -END; +-- Make sure objects are added to tiles in rooms they're allowed to be in. +DROP TRIGGER IF EXISTS before_update_tiles; +CREATE TRIGGER before_update_tiles +BEFORE UPDATE ON tiles +FOR EACH ROW + BEGIN + + IF ((NEW.object_id IS NOT NULL) AND ( + + -- the type of the object being added to the tile... + ( + SELECT objects.type + FROM objects + JOIN tiles ON tiles.object_id = objects.id + WHERE tiles.id = NEW.id + ) + + -- is not in the set of allowed object types for the room the tile is in. + NOT IN ( + SELECT object_type + FROM allowed_objects + JOIN rooms ON rooms.type = allowed_objects.room_type + WHERE rooms.id = NEW.room_id + ) + )) + THEN + -- raise an error + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'ForbiddenObjectType'; + END IF; + END// + +DELIMITER ; /* * PSUs are a type of object. @@ -415,10 +423,10 @@ END; -- PSUs on tiles CREATE TABLE IF NOT EXISTS psus ( - id INTEGER NOT NULL, - energy_kwh INTEGER NOT NULL CHECK (energy_kwh > 0), - type TEXT NOT NULL, - failure_model_id INTEGER NOT NULL, + id INTEGER NOT NULL AUTO_INCREMENT, + energy_kwh INTEGER NOT NULL CHECK (energy_kwh > 0), + type VARCHAR(50) NOT NULL, + failure_model_id INTEGER NOT NULL, FOREIGN KEY (id) REFERENCES objects (id) ON DELETE CASCADE @@ -436,10 +444,10 @@ CREATE TABLE IF NOT EXISTS psus ( -- Cooling items on tiles CREATE TABLE IF NOT EXISTS cooling_items ( - id INTEGER NOT NULL, - energy_consumption_w INTEGER NOT NULL CHECK (energy_consumption_w > 0), - type TEXT NOT NULL, - failure_model_id INTEGER NOT NULL, + id INTEGER NOT NULL AUTO_INCREMENT, + energy_consumption_w INTEGER NOT NULL CHECK (energy_consumption_w > 0), + type VARCHAR(50) NOT NULL, + failure_model_id INTEGER NOT NULL, FOREIGN KEY (id) REFERENCES objects (id) ON DELETE CASCADE @@ -457,7 +465,7 @@ CREATE TABLE IF NOT EXISTS cooling_items ( -- Racks on tiles CREATE TABLE IF NOT EXISTS racks ( - id INTEGER NOT NULL, + id INTEGER NOT NULL AUTO_INCREMENT, name TEXT, capacity INTEGER NOT NULL CHECK (capacity > 0), power_capacity_w INTEGER NOT NULL CHECK (power_capacity_w > 0), @@ -476,7 +484,7 @@ CREATE TABLE IF NOT EXISTS racks ( -- Machines in racks CREATE TABLE IF NOT EXISTS machines ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, rack_id INTEGER NOT NULL, position INTEGER NOT NULL CHECK (position > 0), @@ -488,19 +496,27 @@ CREATE TABLE IF NOT EXISTS machines ( UNIQUE (rack_id, position) ); +DELIMITER // + -- Make sure a machine is not inserted at a position that does not exist for its rack. -CREATE TRIGGER IF NOT EXISTS before_insert_machine - BEFORE - INSERT - ON machines -BEGIN - SELECT RAISE(ABORT, 'InvalidMachinePosition') - WHERE ( - NEW.position > (SELECT capacity - FROM racks - WHERE racks.id = NEW.rack_id) - ); -END; +DROP TRIGGER IF EXISTS before_inser_machine; +CREATE TRIGGER before_insert_machine +BEFORE INSERT ON machines +FOR EACH ROW + BEGIN + IF ( + NEW.position > (SELECT capacity + FROM racks + WHERE racks.id = NEW.rack_id) + ) + THEN + -- raise an error + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'InvalidMachinePosition'; + END IF; + END// + +DELIMITER ; /* * A machine can have a tag for easy search and filtering. @@ -522,7 +538,7 @@ CREATE TABLE IF NOT EXISTS machine_tags ( -- Failure models CREATE TABLE IF NOT EXISTS failure_models ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, name TEXT NOT NULL, rate REAL NOT NULL CHECK (rate >= 0 AND rate <= 1) ); @@ -533,7 +549,7 @@ CREATE TABLE IF NOT EXISTS failure_models ( -- CPU specs CREATE TABLE IF NOT EXISTS cpus ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, manufacturer TEXT NOT NULL, family TEXT NOT NULL, generation TEXT NOT NULL, @@ -550,7 +566,7 @@ CREATE TABLE IF NOT EXISTS cpus ( -- CPUs in machines CREATE TABLE IF NOT EXISTS machine_cpus ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, machine_id INTEGER NOT NULL, cpu_id INTEGER NOT NULL, @@ -568,7 +584,7 @@ CREATE TABLE IF NOT EXISTS machine_cpus ( -- GPU specs CREATE TABLE IF NOT EXISTS gpus ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, manufacturer TEXT NOT NULL, family TEXT NOT NULL, generation TEXT NOT NULL, @@ -585,7 +601,7 @@ CREATE TABLE IF NOT EXISTS gpus ( -- GPUs in machines CREATE TABLE IF NOT EXISTS machine_gpus ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, machine_id INTEGER NOT NULL, gpu_id INTEGER NOT NULL, @@ -604,7 +620,7 @@ CREATE TABLE IF NOT EXISTS machine_gpus ( -- Memory specs CREATE TABLE IF NOT EXISTS memories ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, manufacturer TEXT NOT NULL, family TEXT NOT NULL, generation TEXT NOT NULL, @@ -621,7 +637,7 @@ CREATE TABLE IF NOT EXISTS memories ( -- Memory in machines CREATE TABLE IF NOT EXISTS machine_memories ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, machine_id INTEGER NOT NULL, memory_id INTEGER NOT NULL, @@ -640,7 +656,7 @@ CREATE TABLE IF NOT EXISTS machine_memories ( -- Storage specs CREATE TABLE IF NOT EXISTS storages ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, manufacturer TEXT NOT NULL, family TEXT NOT NULL, generation TEXT NOT NULL, @@ -657,7 +673,7 @@ CREATE TABLE IF NOT EXISTS storages ( -- Storage in machines CREATE TABLE IF NOT EXISTS machine_storages ( - id INTEGER PRIMARY KEY NOT NULL, + id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, machine_id INTEGER NOT NULL, storage_id INTEGER NOT NULL, diff --git a/database/test.sql b/database/test.sql index 7d2f002a..747f5257 100644 --- a/database/test.sql +++ b/database/test.sql @@ -1,27 +1,24 @@ --- Turn on foreign keys -PRAGMA foreign_keys = ON; - -- Users INSERT INTO users (google_id, email, given_name, family_name) - VALUES ('106671218963420759042', 'l.overweel@gmail.com', 'Leon', 'Overweel'); +VALUES ('106671218963420759042', 'l.overweel@gmail.com', 'Leon', 'Overweel'); INSERT INTO users (google_id, email, given_name, family_name) - VALUES ('118147174005839766927', 'jorgos.andreadis@gmail.com', 'Jorgos', 'Andreadis'); +VALUES ('118147174005839766927', 'jorgos.andreadis@gmail.com', 'Jorgos', 'Andreadis'); -- Simulations -INSERT INTO simulations (name, datetime_created, datetime_last_edited) - VALUES ('Test Simulation 1', '2016-07-11T11:00:00', '2016-07-11T11:00:00'); +INSERT INTO simulations (name, datetime_created, datetime_last_edited) +VALUES ('Test Simulation 1', '2016-07-11T11:00:00', '2016-07-11T11:00:00'); -- Authorizations INSERT INTO authorizations (user_id, simulation_id, authorization_level) - VALUES (1, 1, 'OWN'); +VALUES (1, 1, 'OWN'); INSERT INTO authorizations (user_id, simulation_id, authorization_level) - VALUES (2, 1, 'OWN'); +VALUES (2, 1, 'OWN'); -- Paths INSERT INTO paths (simulation_id, datetime_created) - VALUES (1, '2016-07-11T11:00:00'); +VALUES (1, '2016-07-11T11:00:00'); INSERT INTO paths (simulation_id, datetime_created) - VALUES (1, '2016-07-18T09:00:00'); +VALUES (1, '2016-07-18T09:00:00'); -- Datacenter INSERT INTO datacenters (starred, simulation_id) VALUES (0, 1); @@ -42,7 +39,7 @@ INSERT INTO traces (name) VALUES ('Default'); INSERT INTO jobs (name, trace_id) VALUES ('Default', 1); -- Tasks -INSERT INTO tasks (id, start_tick, total_flop_count, job_id, parallelizability) VALUES (1, 0, 400000, 1, 'SEQUENTIAL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 400000, 1, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (25, 10000, 1, 'PARALLEL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (25, 10000, 1, 'PARALLEL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (26, 10000, 1, 'PARALLEL'); @@ -123,39 +120,39 @@ INSERT INTO traces (name) VALUES ('Path planning'); -- Jobs INSERT INTO jobs (name, trace_id) VALUES ('Path planning', 3); -INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 1000000, 2, 'PARALLEL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 1000000, 3, 'PARALLEL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (11, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (11, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (12, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (12, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (13, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (13, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (14, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (14, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (11, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (11, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (12, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (12, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (13, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (13, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (14, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (14, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (11, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (11, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (12, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (12, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (13, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (13, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (14, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (14, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (11, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (11, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (12, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (12, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (13, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (13, 200000, 3, 66, 'SEQUENTIAL'); INSERT INTO tasks (start_tick, total_flop_count, job_id, task_dependency_id, parallelizability) -VALUES (14, 200000, 1, 66, 'SEQUENTIAL'); +VALUES (14, 200000, 3, 66, 'SEQUENTIAL'); -- Parallelizable Trace INSERT INTO traces (name) VALUES ('Parallel heavy trace'); @@ -163,8 +160,8 @@ INSERT INTO traces (name) VALUES ('Parallel heavy trace'); -- Jobs INSERT INTO jobs (name, trace_id) VALUES ('Parallel heavy trace', 4); -INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 100000, 3, 'SEQUENTIAL'); -INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 900000, 3, 'PARALLEL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 100000, 4, 'SEQUENTIAL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 900000, 4, 'PARALLEL'); -- Sequential Trace INSERT INTO traces (name) VALUES ('Sequential heavy trace'); @@ -172,22 +169,22 @@ INSERT INTO traces (name) VALUES ('Sequential heavy trace'); -- Jobs INSERT INTO jobs (name, trace_id) VALUES ('Sequential heavy trace', 5); -INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 100000, 4, 'PARALLEL'); -INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 900000, 4, 'SEQUENTIAL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 100000, 5, 'PARALLEL'); +INSERT INTO tasks (start_tick, total_flop_count, job_id, parallelizability) VALUES (0, 900000, 5, 'SEQUENTIAL'); -- Experiments INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 1, 'SRTF', 'Default trace, SRTF', 'QUEUED', 0); +VALUES (1, 1, 1, 'SRTF', 'Default trace, SRTF', 'QUEUED', 110); INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 2, 'SRTF', 'Image processing trace, SRTF', 'QUEUED', 0); +VALUES (1, 1, 2, 'SRTF', 'Image processing trace, SRTF', 'QUEUED', 0); INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 3, 'SRTF', 'Path planning trace, FIFI', 'QUEUED', 0); +VALUES (1, 1, 3, 'SRTF', 'Path planning trace, FIFI', 'QUEUED', 0); INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 1, 'FIFO', 'Default trace, SRTF', 'QUEUED', 0); +VALUES (1, 1, 1, 'FIFO', 'Default trace, SRTF', 'QUEUED', 0); INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 2, 'FIFO', 'Image processing trace, SRTF', 'QUEUED', 0); +VALUES (1, 1, 2, 'FIFO', 'Image processing trace, SRTF', 'QUEUED', 0); INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name, state, last_simulated_tick) - VALUES (1, 1, 3, 'FIFO', 'Path planning trace, FIFO', 'QUEUED', 0); +VALUES (1, 1, 3, 'FIFO', 'Path planning trace, FIFO', 'QUEUED', 0); -- Rooms INSERT INTO rooms (name, datacenter_id, type) VALUES ('room 1', 1, 'SERVER'); @@ -217,35 +214,56 @@ INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 12, 5); INSERT INTO tiles (position_x, position_y, room_id) VALUES (10, 13, 5); -- Racks -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 1', 5000); -UPDATE tiles SET object_id = 1 WHERE id = 1; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 2', 5000); -UPDATE tiles SET object_id = 2 WHERE id = 2; - -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 1', 5000); -UPDATE tiles SET object_id = 3 WHERE id = 4; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 2', 5000); -UPDATE tiles SET object_id = 4 WHERE id = 5; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 3', 5000); -UPDATE tiles SET object_id = 5 WHERE id = 7; - -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 1', 5000); -UPDATE tiles SET object_id = 6 WHERE id = 8; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 2', 5000); -UPDATE tiles SET object_id = 7 WHERE id = 9; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 3', 5000); -UPDATE tiles SET object_id = 8 WHERE id = 11; -INSERT INTO objects (type) VALUES ( 'RACK' ); -INSERT INTO racks (capacity, name, power_capacity_w) values (42, 'rack 4', 5000); -UPDATE tiles SET object_id = 9 WHERE id = 12; +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 1', 5000); +UPDATE tiles +SET object_id = 1 +WHERE id = 1; +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 2', 5000); +UPDATE tiles +SET object_id = 2 +WHERE id = 2; + +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 1', 5000); +UPDATE tiles +SET object_id = 3 +WHERE id = 4; +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 2', 5000); +UPDATE tiles +SET object_id = 4 +WHERE id = 5; +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 3', 5000); +UPDATE tiles +SET object_id = 5 +WHERE id = 7; + +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 1', 5000); +UPDATE tiles +SET object_id = 6 +WHERE id = 8; + +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 2', 5000); +UPDATE tiles +SET object_id = 7 +WHERE id = 9; + +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 3', 5000); +UPDATE tiles +SET object_id = 8 +WHERE id = 11; + +INSERT INTO objects (type) VALUES ('RACK'); +INSERT INTO racks (capacity, name, power_capacity_w) VALUES (42, 'rack 4', 5000); +UPDATE tiles +SET object_id = 9 +WHERE id = 12; -- Machines INSERT INTO machines (rack_id, position) VALUES (1, 1); @@ -284,18 +302,18 @@ INSERT INTO machine_tags (name, machine_id) VALUES ('my fave machine', 1); INSERT INTO machine_tags (name, machine_id) VALUES ('my best machine', 2); -- Failure models -INSERT INTO failure_models (name, rate) values('test_model', 0); +INSERT INTO failure_models (name, rate) VALUES ('test_model', 0); -- CPUs -INSERT INTO cpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, - failure_model_id) VALUES ('intel','i7','v6','6700k',4100,4,70,1); -INSERT INTO cpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, - failure_model_id) VALUES ('intel','i5','v6','6700k',3500,2,50,1); - ---- GPUs -INSERT INTO gpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, - failure_model_id) VALUES ("NVIDIA", "GTX", "4", "1080", 1200, 200, 250, 1); - +INSERT INTO cpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, + failure_model_id) VALUES ('intel', 'i7', 'v6', '6700k', 4100, 4, 70, 1); +INSERT INTO cpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, + failure_model_id) VALUES ('intel', 'i5', 'v6', '6700k', 3500, 2, 50, 1); + +-- GPUs +INSERT INTO gpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, + failure_model_id) VALUES ("NVIDIA", "GTX", "4", "1080", 1200, 200, 250, 1); + -- CPUs in machines INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (1, 1); INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (1, 1); @@ -350,15 +368,15 @@ INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (27, 2); INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (28, 2); -- GPUs -INSERT INTO gpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, - failure_model_id) VALUES ('nvidia','GeForce GTX Series','10','80', 1607, 2560, 70, 1); +INSERT INTO gpus (manufacturer, family, generation, model, clock_rate_mhz, number_of_cores, energy_consumption_w, + failure_model_id) VALUES ('nvidia', 'GeForce GTX Series', '10', '80', 1607, 2560, 70, 1); -- Memories -INSERT INTO memories (manufacturer, family, generation, model, speed_mb_per_s, size_mb, energy_consumption_w, - failure_model_id) VALUES ('samsung', 'PC DRAM', 'K4A4G045WD', 'DDR4', 16000, 4000, 10, 1); +INSERT INTO memories (manufacturer, family, generation, model, speed_mb_per_s, size_mb, energy_consumption_w, + failure_model_id) VALUES ('samsung', 'PC DRAM', 'K4A4G045WD', 'DDR4', 16000, 4000, 10, 1); -- Storages -INSERT INTO storages (manufacturer, family, generation, model, speed_mb_per_s, size_mb, energy_consumption_w, - failure_model_id) VALUES ('samsung', 'EVO', '2016', 'SATA III', 6000, 250000, 10, 1); +INSERT INTO storages (manufacturer, family, generation, model, speed_mb_per_s, size_mb, energy_consumption_w, + failure_model_id) VALUES ('samsung', 'EVO', '2016', 'SATA III', 6000, 250000, 10, 1); |