Add jobs to the SQL schema and test script

1 files changed, 369 insertions, 267 deletions

diff --git a/database/schema.sql b/database/schema.sql
index bb066328..858f5d47 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -3,12 +3,12 @@
 */
 
 -- Users
-CREATE TABLE IF NOT EXISTS users(
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    google_id               TEXT                    NOT NULL,
-    email                   TEXT,
-    given_name              TEXT,
-    family_name             TEXT
+CREATE TABLE IF NOT EXISTS users (
+  id          INTEGER PRIMARY KEY     NOT NULL,
+  google_id   TEXT                    NOT NULL,
+  email       TEXT,
+  given_name  TEXT,
+  family_name TEXT
 );
 
 /*
@@ -18,23 +18,28 @@ 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 TEXT    NOT NULL,
 
-    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (authorization_level) REFERENCES authorization_levels (level) 
+  FOREIGN KEY (user_id) REFERENCES users (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (simulation_id) REFERENCES simulations (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (authorization_level) REFERENCES authorization_levels (level)
 );
 
-CREATE UNIQUE INDEX authorizations_index ON authorizations (
+CREATE UNIQUE INDEX authorizations_index
+  ON authorizations (
     user_id,
     simulation_id
-);
+  );
 
 -- Authorization levels
 CREATE TABLE IF NOT EXISTS authorization_levels (
-    level                   TEXT PRIMARY KEY        NOT NULL
+  level TEXT PRIMARY KEY        NOT NULL
 );
 INSERT INTO authorization_levels (level) VALUES ("OWN");
 INSERT INTO authorization_levels (level) VALUES ("EDIT");
@@ -57,10 +62,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,
+  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
 );
 
 /*
@@ -70,51 +75,65 @@ CREATE TABLE IF NOT EXISTS simulations (
 */
 
 CREATE TABLE IF NOT EXISTS experiments (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    simulation_id           INTEGER                 NOT NULL,
-    path_id                 INTEGER                 NOT NULL,
-    trace_id                INTEGER                 NOT NULL,
-    scheduler_name          TEXT                    NOT NULL,
-    name                    TEXT                    NOT NULL,
-	state					TEXT					NOT NULL,
-	last_simulated_tick		INTEGER					NOT NULL DEFAULT 0 CHECK (last_simulated_tick >= 0),
-
-    FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE ON UPDATE CASCADE
-    FOREIGN KEY (path_id) REFERENCES paths (id) ON DELETE CASCADE ON UPDATE CASCADE
-    FOREIGN KEY (trace_id) REFERENCES traces (id) ON DELETE CASCADE ON UPDATE CASCADE
-    FOREIGN KEY (scheduler_name) REFERENCES schedulers (name) ON DELETE CASCADE ON UPDATE CASCADE
+  id                  INTEGER PRIMARY KEY     NOT NULL,
+  simulation_id       INTEGER                 NOT NULL,
+  path_id             INTEGER                 NOT NULL,
+  trace_id            INTEGER                 NOT NULL,
+  scheduler_name      TEXT                    NOT NULL,
+  name                TEXT                    NOT NULL,
+  state               TEXT                    NOT NULL,
+  last_simulated_tick INTEGER                 NOT NULL DEFAULT 0 CHECK (last_simulated_tick >= 0),
+
+  FOREIGN KEY (simulation_id) REFERENCES simulations (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (path_id) REFERENCES paths (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (trace_id) REFERENCES traces (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (scheduler_name) REFERENCES schedulers (name)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
 *   A Simulation has several Paths, which each contain Sections. A Section details which Datacenter topology to use
 *   starting at which point in time (known internally as a "tick"). So, combining the several Sections in a Path
 *   tells us which Datacenter topology to use at each tick.
-*/ 
+*/
 
 -- Path
 CREATE TABLE IF NOT EXISTS paths (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    simulation_id           INTEGER                 NOT NULL,
-    name                    TEXT,
-    datetime_created        TEXT                    NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'),
+  id               INTEGER PRIMARY KEY     NOT NULL,
+  simulation_id    INTEGER                 NOT NULL,
+  name             TEXT,
+  datetime_created TEXT                    NOT NULL CHECK (datetime_created LIKE '____-__-__T__:__:__'),
 
-    FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (simulation_id) REFERENCES simulations (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- Sections
 CREATE TABLE IF NOT EXISTS sections (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    path_id                 INTEGER                 NOT NULL,
-    datacenter_id           INTEGER                 NOT NULL,
-    start_tick              INTEGER                 NOT NULL CHECK (start_tick >= 0),
+  id            INTEGER PRIMARY KEY     NOT NULL,
+  path_id       INTEGER                 NOT NULL,
+  datacenter_id INTEGER                 NOT NULL,
+  start_tick    INTEGER                 NOT NULL CHECK (start_tick >= 0),
 
-    FOREIGN KEY (path_id) REFERENCES paths (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (datacenter_id) REFERENCES datacenters (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (path_id) REFERENCES paths (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (datacenter_id) REFERENCES datacenters (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- Scheduler names
 CREATE TABLE IF NOT EXISTS schedulers (
-    name                    TEXT PRIMARY KEY        NOT NULL
+  name TEXT PRIMARY KEY        NOT NULL
 );
 INSERT INTO schedulers (name) VALUES ("DEFAULT");
 INSERT INTO schedulers (name) VALUES ("SRTF");
@@ -126,21 +145,31 @@ 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,
-    name                    TEXT                    NOT NULL
+  id   INTEGER PRIMARY KEY     NOT NULL,
+  name TEXT                    NOT NULL
+);
+
+-- A job
+CREATE TABLE IF NOT EXISTS jobs (
+  id   INTEGER PRIMARY KEY     NOT NULL,
+  name TEXT                    NOT NULL
 );
 
 -- 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,
-    start_tick              INTEGER                 NOT NULL CHECK (start_tick >= 0),
-    total_flop_count        INTEGER                 NOT NULL,
-    trace_id                INTEGER                 NOT NULL,
-    task_dependency_id      INTEGER                 NULL,
-	parallelizability		TEXT					NOT NULL,
+  id                 INTEGER PRIMARY KEY     NOT NULL,
+  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,
 
-    FOREIGN KEY (trace_id) REFERENCES traces (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (task_dependency_id) REFERENCES tasks (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (job_id) REFERENCES jobs (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (task_dependency_id) REFERENCES tasks (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -150,31 +179,33 @@ 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,
-    task_id                 INTEGER                 NOT NULL,
-    experiment_id           INTEGER                 NOT NULL,
-    tick                    INTEGER                 NOT NULL CHECK (tick >= 0),
-    flops_left              INTEGER                 NOT NULL CHECK (flops_left >= 0),
-	cores_used				INTEGER					NOT NULL CHECK (cores_used >= 0),
+  id            INTEGER PRIMARY KEY     NOT NULL,
+  task_id       INTEGER                 NOT NULL,
+  experiment_id INTEGER                 NOT NULL,
+  tick          INTEGER                 NOT NULL CHECK (tick >= 0),
+  flops_left    INTEGER                 NOT NULL CHECK (flops_left >= 0),
+  cores_used    INTEGER                 NOT NULL CHECK (cores_used >= 0),
 
-    FOREIGN KEY (task_id) REFERENCES tasks (id),
-    FOREIGN KEY (experiment_id) REFERENCES experiments (id)
+  FOREIGN KEY (task_id) REFERENCES tasks (id),
+  FOREIGN KEY (experiment_id) REFERENCES experiments (id)
 );
 
 -- A machine state 
 CREATE TABLE IF NOT EXISTS machine_states (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-	task_id                 INTEGER,
-    machine_id              INTEGER                 NOT NULL,
-    experiment_id           INTEGER                 NOT NULL,
-    tick                    INTEGER                 NOT NULL,
-    temperature_c           REAL,
-    in_use_memory_mb        INTEGER,
-    load_fraction           REAL                    CHECK (load_fraction >= 0 AND load_fraction <= 1),
-
-    FOREIGN KEY (task_id) REFERENCES tasks (id),
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE
-    FOREIGN KEY (experiment_id) REFERENCES experiments (id)
+  id               INTEGER PRIMARY KEY     NOT NULL,
+  task_id          INTEGER,
+  machine_id       INTEGER                 NOT NULL,
+  experiment_id    INTEGER                 NOT NULL,
+  tick             INTEGER                 NOT NULL,
+  temperature_c    REAL,
+  in_use_memory_mb INTEGER,
+  load_fraction    REAL CHECK (load_fraction >= 0 AND load_fraction <= 1),
+
+  FOREIGN KEY (task_id) REFERENCES tasks (id),
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (experiment_id) REFERENCES experiments (id)
 );
 
 /*
@@ -184,11 +215,13 @@ CREATE TABLE IF NOT EXISTS machine_states (
 
 -- Datacenters 
 CREATE TABLE IF NOT EXISTS datacenters (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    simulation_id           INTEGER                 NOT NULL,
-    starred                 INTEGER                 CHECK (starred = 0 OR starred = 1),
+  id            INTEGER PRIMARY KEY     NOT NULL,
+  simulation_id INTEGER                 NOT NULL,
+  starred       INTEGER CHECK (starred = 0 OR starred = 1),
 
-    FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (simulation_id) REFERENCES simulations (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -197,19 +230,23 @@ CREATE TABLE IF NOT EXISTS datacenters (
 
 -- Rooms in a datacenter
 CREATE TABLE IF NOT EXISTS rooms (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    name                    TEXT                    NOT NULL,
-    datacenter_id           INTEGER                 NOT NULL,
-    type                    TEXT                    NOT NULL,
-    
-    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,
+  id            INTEGER PRIMARY KEY     NOT NULL,
+  name          TEXT                    NOT NULL,
+  datacenter_id INTEGER                 NOT NULL,
+  type          TEXT                    NOT NULL,
+
+  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,
 
-    UNIQUE(name, datacenter_id)
+  UNIQUE (name, datacenter_id)
 );
 
 CREATE TABLE IF NOT EXISTS room_types (
-    name                    TEXT PRIMARY KEY        NOT NULL
+  name TEXT PRIMARY KEY        NOT NULL
 );
 INSERT INTO room_types (name) VALUES ('SERVER');
 INSERT INTO room_types (name) VALUES ('HALLWAY');
@@ -225,60 +262,77 @@ INSERT INTO room_types (name) VALUES ('COOLING');
 
 -- Tiles in a room
 CREATE TABLE IF NOT EXISTS tiles (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    position_x              INTEGER                 NOT NULL,
-    position_y              INTEGER                 NOT NULL,
-    room_id                 INTEGER                 NOT NULL,
-    object_id               INTEGER,
-    
-    FOREIGN KEY (room_id) REFERENCES rooms (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (object_id) REFERENCES objects (id),
-    
-    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
+  id         INTEGER PRIMARY KEY     NOT NULL,
+  position_x INTEGER                 NOT NULL,
+  position_y INTEGER                 NOT NULL,
+  room_id    INTEGER                 NOT NULL,
+  object_id  INTEGER,
+
+  FOREIGN KEY (room_id) REFERENCES rooms (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (object_id) REFERENCES objects (id),
+
+  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
 );
 
 -- 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
+CREATE TRIGGER IF NOT EXISTS before_insert_tiles_check_existence
+  BEFORE
+  INSERT
+  ON tiles
 BEGIN
-    
-    -- raise an error if...
-    SELECT RAISE (ABORT, 'OccupiedTilePosition')
-    WHERE (
-
-        -- 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
-        ))
-    );
+
+  -- raise an error if...
+  SELECT RAISE(ABORT, 'OccupiedTilePosition')
+  WHERE (
+
+    -- 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
+CREATE TRIGGER IF NOT EXISTS before_insert_tiles_check_adjacency
+  BEFORE
+  INSERT
+  ON tiles
 BEGIN
-    
-    -- raise an error if...
-    SELECT RAISE (ABORT, 'InvalidTilePosition')
-    WHERE (
-
-        -- 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
-            )
-        ))
-    );
+
+  -- raise an error if...
+  SELECT RAISE(ABORT, 'InvalidTilePosition')
+  WHERE (
+
+    -- 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;
 
 /*
@@ -293,15 +347,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,
+  type TEXT                    NOT NULL,
 
-    FOREIGN KEY (type) REFERENCES object_types (name)
+  FOREIGN KEY (type) REFERENCES object_types (name)
 );
 
 -- Object types
 CREATE TABLE IF NOT EXISTS object_types (
-    name                    TEXT PRIMARY KEY        NOT NULL
+  name TEXT PRIMARY KEY        NOT NULL
 );
 INSERT INTO object_types (name) VALUES ('PSU');
 INSERT INTO object_types (name) VALUES ('COOLING_ITEM');
@@ -309,11 +363,11 @@ 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   TEXT NOT NULL,
+  object_type TEXT NOT NULL,
 
-    FOREIGN KEY (room_type) REFERENCES room_types (name),
-    FOREIGN KEY (object_type) REFERENCES object_types
+  FOREIGN KEY (room_type) REFERENCES room_types (name),
+  FOREIGN KEY (object_type) REFERENCES object_types
 );
 
 -- Allowed objects per room
@@ -322,27 +376,32 @@ INSERT INTO allowed_objects (room_type, object_type) VALUES ('SERVER', 'RACK');
 --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
+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
-        )
-
-        -- 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
-        )
-    );
+
+  -- 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
+    )
+
+    -- 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;
 
 /*
@@ -351,15 +410,19 @@ 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,
+  energy_kwh       INTEGER NOT NULL CHECK (energy_kwh > 0),
+  type             TEXT    NOT NULL,
+  failure_model_id INTEGER NOT NULL,
 
-    FOREIGN KEY (id) REFERENCES objects (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (id) REFERENCES objects (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
 
-    PRIMARY KEY (id)
+  PRIMARY KEY (id)
 );
 
 /*
@@ -368,15 +431,19 @@ 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,
+  energy_consumption_w INTEGER NOT NULL CHECK (energy_consumption_w > 0),
+  type                 TEXT    NOT NULL,
+  failure_model_id     INTEGER NOT NULL,
 
-    FOREIGN KEY (id) REFERENCES objects (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (id) REFERENCES objects (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
 
-    PRIMARY KEY (id)
+  PRIMARY KEY (id)
 );
 
 /*
@@ -385,14 +452,16 @@ CREATE TABLE IF NOT EXISTS cooling_items (
 
 -- Racks on tiles
 CREATE TABLE IF NOT EXISTS racks (
-    id                      INTEGER	            NOT NULL,
-    name                    TEXT,
-    capacity                INTEGER                 NOT NULL CHECK (capacity > 0),
-    power_capacity_w        INTEGER                 NOT NULL CHECK (power_capacity_w > 0),
-    
-    FOREIGN KEY (id) REFERENCES objects (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    
-    PRIMARY KEY (id)
+  id               INTEGER NOT NULL,
+  name             TEXT,
+  capacity         INTEGER NOT NULL CHECK (capacity > 0),
+  power_capacity_w INTEGER NOT NULL CHECK (power_capacity_w > 0),
+
+  FOREIGN KEY (id) REFERENCES objects (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+
+  PRIMARY KEY (id)
 );
 
 /*
@@ -402,23 +471,30 @@ CREATE TABLE IF NOT EXISTS racks (
 
 -- Machines in racks
 CREATE TABLE IF NOT EXISTS machines (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    rack_id                 INTEGER                 NOT NULL,
-    position                INTEGER                 NOT NULL CHECK(position > 0),
+  id       INTEGER PRIMARY KEY     NOT NULL,
+  rack_id  INTEGER                 NOT NULL,
+  position INTEGER                 NOT NULL CHECK (position > 0),
 
-    FOREIGN KEY (rack_id) REFERENCES racks (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (rack_id) REFERENCES racks (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
 
-    -- Prevent machines from occupying the same position in a rack.
-    UNIQUE(rack_id, position)
+  -- Prevent machines from occupying the same position in a rack.
+  UNIQUE (rack_id, position)
 );
 
 -- 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
+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)
-    );
+  SELECT RAISE(ABORT, 'InvalidMachinePosition')
+  WHERE (
+    NEW.position > (SELECT capacity
+                    FROM racks
+                    WHERE racks.id = NEW.rack_id)
+  );
 END;
 
 /*
@@ -427,10 +503,12 @@ END;
 
 -- Tags for machines
 CREATE TABLE IF NOT EXISTS machine_tags (
-    name                    TEXT                    NOT NULL,
-    machine_id              INTEGER                 NOT NULL,
-    
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE
+  name       TEXT    NOT NULL,
+  machine_id INTEGER NOT NULL,
+
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -439,9 +517,9 @@ CREATE TABLE IF NOT EXISTS machine_tags (
 
 -- Failure models
 CREATE TABLE IF NOT EXISTS failure_models (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    name                    TEXT                    NOT NULL,
-    rate                    REAL                    NOT NULL CHECK (rate >= 0 AND rate <= 1)
+  id   INTEGER PRIMARY KEY     NOT NULL,
+  name TEXT                    NOT NULL,
+  rate REAL                    NOT NULL CHECK (rate >= 0 AND rate <= 1)
 );
 
 /*
@@ -450,27 +528,33 @@ CREATE TABLE IF NOT EXISTS failure_models (
 
 -- CPU specs
 CREATE TABLE IF NOT EXISTS cpus (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    manufacturer            TEXT                    NOT NULL,
-    family                  TEXT                    NOT NULL,
-    generation              TEXT                    NOT NULL,
-    model                   TEXT                    NOT NULL,
-    clock_rate_mhz          INTEGER                 NOT NULL CHECK (clock_rate_mhz > 0),
-    number_of_cores         INTEGER                 NOT NULL CHECK (number_of_cores > 0),
-    energy_consumption_w    REAL                    NOT NULL CHECK (energy_consumption_w > 0),
-    failure_model_id        INTEGER                 NOT NULL,
-    
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE
+  id                   INTEGER PRIMARY KEY     NOT NULL,
+  manufacturer         TEXT                    NOT NULL,
+  family               TEXT                    NOT NULL,
+  generation           TEXT                    NOT NULL,
+  model                TEXT                    NOT NULL,
+  clock_rate_mhz       INTEGER                 NOT NULL CHECK (clock_rate_mhz > 0),
+  number_of_cores      INTEGER                 NOT NULL CHECK (number_of_cores > 0),
+  energy_consumption_w REAL                    NOT NULL CHECK (energy_consumption_w > 0),
+  failure_model_id     INTEGER                 NOT NULL,
+
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- CPUs in machines
 CREATE TABLE IF NOT EXISTS machine_cpus (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    machine_id              INTEGER                 NOT NULL,
-    cpu_id                  INTEGER                 NOT NULL,
+  id         INTEGER PRIMARY KEY     NOT NULL,
+  machine_id INTEGER                 NOT NULL,
+  cpu_id     INTEGER                 NOT NULL,
 
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (cpu_id) REFERENCES cpus (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (cpu_id) REFERENCES cpus (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -479,27 +563,33 @@ CREATE TABLE IF NOT EXISTS machine_cpus (
 
 -- GPU specs
 CREATE TABLE IF NOT EXISTS gpus (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    manufacturer            TEXT                    NOT NULL,
-    family                  TEXT                    NOT NULL,
-    generation              TEXT                    NOT NULL,
-    model                   TEXT                    NOT NULL,
-    clock_rate_mhz          INTEGER                 NOT NULL CHECK (clock_rate_mhz > 0),
-    number_of_cores         INTEGER                 NOT NULL CHECK (number_of_cores > 0),
-    energy_consumption_w    REAL                    NOT NULL CHECK (energy_consumption_w > 0),
-    failure_model_id        INTEGER                 NOT NULL,
-    
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE
+  id                   INTEGER PRIMARY KEY     NOT NULL,
+  manufacturer         TEXT                    NOT NULL,
+  family               TEXT                    NOT NULL,
+  generation           TEXT                    NOT NULL,
+  model                TEXT                    NOT NULL,
+  clock_rate_mhz       INTEGER                 NOT NULL CHECK (clock_rate_mhz > 0),
+  number_of_cores      INTEGER                 NOT NULL CHECK (number_of_cores > 0),
+  energy_consumption_w REAL                    NOT NULL CHECK (energy_consumption_w > 0),
+  failure_model_id     INTEGER                 NOT NULL,
+
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- GPUs in machines
 CREATE TABLE IF NOT EXISTS machine_gpus (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    machine_id              INTEGER                 NOT NULL,
-    gpu_id                  INTEGER                 NOT NULL,
+  id         INTEGER PRIMARY KEY     NOT NULL,
+  machine_id INTEGER                 NOT NULL,
+  gpu_id     INTEGER                 NOT NULL,
 
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (gpu_id) REFERENCES gpus (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (gpu_id) REFERENCES gpus (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -509,27 +599,33 @@ CREATE TABLE IF NOT EXISTS machine_gpus (
 
 -- Memory specs
 CREATE TABLE IF NOT EXISTS memories (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    manufacturer            TEXT                    NOT NULL,
-    family                  TEXT                    NOT NULL,
-    generation              TEXT                    NOT NULL,
-    model                   TEXT                    NOT NULL,
-    speed_mb_per_s          INTEGER                 NOT NULL CHECK (speed_mb_per_s > 0),
-    size_mb                 INTEGER                 NOT NULL CHECK (size_mb > 0),
-    energy_consumption_w    REAL                    NOT NULL CHECK (energy_consumption_w > 0),
-    failure_model_id        INTEGER                 NOT NULL,
-    
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE
+  id                   INTEGER PRIMARY KEY     NOT NULL,
+  manufacturer         TEXT                    NOT NULL,
+  family               TEXT                    NOT NULL,
+  generation           TEXT                    NOT NULL,
+  model                TEXT                    NOT NULL,
+  speed_mb_per_s       INTEGER                 NOT NULL CHECK (speed_mb_per_s > 0),
+  size_mb              INTEGER                 NOT NULL CHECK (size_mb > 0),
+  energy_consumption_w REAL                    NOT NULL CHECK (energy_consumption_w > 0),
+  failure_model_id     INTEGER                 NOT NULL,
+
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- Memory in machines
 CREATE TABLE IF NOT EXISTS machine_memories (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    machine_id              INTEGER                 NOT NULL,
-    memory_id               INTEGER                 NOT NULL,
+  id         INTEGER PRIMARY KEY     NOT NULL,
+  machine_id INTEGER                 NOT NULL,
+  memory_id  INTEGER                 NOT NULL,
 
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (memory_id) REFERENCES memories (id) ON DELETE CASCADE ON UPDATE CASCADE
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (memory_id) REFERENCES memories (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 /*
@@ -539,25 +635,31 @@ CREATE TABLE IF NOT EXISTS machine_memories (
 
 -- Storage specs
 CREATE TABLE IF NOT EXISTS storages (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    manufacturer            TEXT                    NOT NULL,
-    family                  TEXT                    NOT NULL,
-    generation              TEXT                    NOT NULL,
-    model                   TEXT                    NOT NULL,
-    speed_mb_per_s          INTEGER                 NOT NULL CHECK (speed_mb_per_s > 0),
-    size_mb                 INTEGER                 NOT NULL CHECK (size_mb > 0),
-    energy_consumption_w    REAL                    NOT NULL CHECK (energy_consumption_w > 0),
-    failure_model_id        INTEGER                 NOT NULL,
-    
-    FOREIGN KEY (failure_model_id) REFERENCES failure_models (id) ON DELETE CASCADE ON UPDATE CASCADE
+  id                   INTEGER PRIMARY KEY     NOT NULL,
+  manufacturer         TEXT                    NOT NULL,
+  family               TEXT                    NOT NULL,
+  generation           TEXT                    NOT NULL,
+  model                TEXT                    NOT NULL,
+  speed_mb_per_s       INTEGER                 NOT NULL CHECK (speed_mb_per_s > 0),
+  size_mb              INTEGER                 NOT NULL CHECK (size_mb > 0),
+  energy_consumption_w REAL                    NOT NULL CHECK (energy_consumption_w > 0),
+  failure_model_id     INTEGER                 NOT NULL,
+
+  FOREIGN KEY (failure_model_id) REFERENCES failure_models (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );
 
 -- Storage in machines
 CREATE TABLE IF NOT EXISTS machine_storages (
-    id                      INTEGER PRIMARY KEY     NOT NULL,
-    machine_id              INTEGER                 NOT NULL,
-    storage_id              INTEGER                 NOT NULL,
-
-    FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY (storage_id) REFERENCES storages (id) ON DELETE CASCADE ON UPDATE CASCADE
+  id         INTEGER PRIMARY KEY     NOT NULL,
+  machine_id INTEGER                 NOT NULL,
+  storage_id INTEGER                 NOT NULL,
+
+  FOREIGN KEY (machine_id) REFERENCES machines (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (storage_id) REFERENCES storages (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
 );