Add database schema and testing data

2 files changed, 787 insertions, 0 deletions

diff --git a/database/schema.sql b/database/schema.sql
new file mode 100644
index 00000000..ab9fd3a0
--- /dev/null
+++ b/database/schema.sql
@@ -0,0 +1,556 @@
+/*
+*   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,
+    google_id               TEXT                    NOT NULL,
+    email                   TEXT,
+    given_name              TEXT,
+    family_name             TEXT
+);
+
+/*
+*   The authorizations table defines which users are authorized to "OWN", "EDIT", or "VIEW" a simulation. The
+*   authorization_level table defines the permission levels.
+*/
+
+-- User authorizations
+CREATE TABLE IF NOT EXISTS authorizations (
+    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) 
+);
+
+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
+);
+INSERT INTO authorization_levels (level) VALUES ("OWN"), ("EDIT"), ("VIEW");
+
+/*
+*   A Simulation has several Paths, which define the topology of the datacenter at different times. A Simulation also
+*   has several Experiments, which can be run on a combination of Paths, Schedulers and Traces. Simulations also serve
+*   as the scope to which different Users can be Authorized.
+*
+*   The datetime_created and datetime_last_edited columns are in a subset of ISO-8601 (second fractions are ommitted): 
+*   YYYY-MM-DDTHH:MM:SS, where...
+*       -   YYYY is the four-digit year,
+*       -   MM is the two-digit month (1-12)
+*       -   DD is the two-digit day of the month (1-31)
+*       -   HH is the two-digit hours part (0-23)
+*       -   MM is the two-digit minutes part (0-59)
+*       -   SS is the two-digit secodns part (0-59)
+*/
+
+-- 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
+);
+
+/*
+*   An Experiment consists of a Path, a Scheduler, and a Trace. The Path defines the topology of the datacenter at
+*   different times in the simulation. The Scheduler defines which scheduler to use to simulate this experiment. The
+*   Trace defines which tasks have to be run in the simulation.
+*/
+
+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,
+
+    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
+);
+
+-- Queued experiments are ready to be run by the simulator
+CREATE TABLE IF NOT EXISTS queued_experiments (
+    experiment_id           INTEGER                 NOT NULL,
+
+    FOREIGN KEY (experiment_id) REFERENCES experiments (id) 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__:__:__'),
+
+    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),
+
+    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
+);
+INSERT INTO schedulers (name) VALUES ("DEFAULT");
+
+/*
+*   Each simulation has a single trace. A trace contains tasks and their start times.
+*/
+
+-- 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
+);
+
+-- 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,
+
+    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
+);
+
+/*
+*   A task_state describes how much of a task has already been completed at the time of the current tick. Several
+*   machine_states show which machines worked on the task.
+*/
+
+-- 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,
+    flops_left              INTEGER                 NOT NULL,
+
+    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)
+);
+
+/*
+*   A Section references a Datacenter topology, which can be used by multiple Sections to create Paths that go back and
+*   forth between different topologies.
+*/
+
+-- 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),
+
+    FOREIGN KEY (simulation_id) REFERENCES simulations (id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
+/*
+*   A datacenter consists of several rooms. A room has a type that specifies what kind of objects can be in it.
+*/
+
+-- 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,
+
+    UNIQUE(name, datacenter_id)
+);
+
+CREATE TABLE IF NOT EXISTS room_types (
+    name                    TEXT PRIMARY KEY        NOT NULL
+);
+INSERT INTO room_types (name) VALUES ('SERVER'), ('HALLWAY'), ('OFFICE'), ('POWER'), ('COOLING');
+
+/*
+*   A room consists of tiles that have a quantized (x,y) position. The same tile can't be in multiple rooms. All tiles 
+*   in a room must touch at least one edge to another tile in that room. A tile is occupied by a single object, which
+*   has a type from the object_types table.
+*/
+
+-- 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
+);
+
+-- 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 (
+
+        -- 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 (
+
+        -- 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;
+
+/*
+*   Objects are on tiles and have a type. They form an extra abstraction layer to make it easier to find what object is
+*   on a tile, as well as to enforce that only objects of the right type are in a certain room.
+*
+*   To add a PSU, cooling item, or rack to a tile, first add an object. Then use that object's ID as the value for the
+*   object_id column of the PSU, cooling item, or rack table.
+*   
+*   The allowed_object table specifies what types of objects are allowed in what types of rooms.
+*/
+
+-- Objects
+CREATE TABLE IF NOT EXISTS objects (
+    id                      INTEGER PRIMARY KEY     NOT NULL,
+    type                    TEXT                    NOT NULL,
+
+    FOREIGN KEY (type) REFERENCES object_types (name)
+);
+
+-- Object types
+CREATE TABLE IF NOT EXISTS object_types (
+    name                    TEXT PRIMARY KEY        NOT NULL
+);
+INSERT INTO object_types (name) VALUES ('PSU'), ('COOLING_ITEM'), ('RACK');
+
+-- Allowed objects table
+CREATE TABLE IF NOT EXISTS allowed_objects (
+    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
+);
+
+-- 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');
+
+-- 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
+        )
+
+        -- 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;
+
+/*
+*   PSUs are a type of object.
+*/
+
+-- 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,
+
+    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)
+);
+
+/*
+*   Cooling items are a type of object.
+*/
+
+-- 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,
+
+    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)
+);
+
+/*
+*   Racks are a type of object.
+*/
+
+-- 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)
+);
+
+/*
+*   A rack contains a number of machines. A rack cannot have more than its capacity of machines in it. No more than one
+*   machine can occupy a position in a rack at the same time.
+*/
+
+-- 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),
+
+    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)
+);
+
+-- 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;
+
+/*
+*   A machine can have a tag for easy search and filtering.
+*/
+
+-- 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
+);
+
+/*
+*   A failure model defines the probability of a machine breaking at any given time.
+*/
+
+-- 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)
+);
+
+/*
+*   A cpu stores information about a type of cpu. The machine_cpu table keeps track of which cpus are in which machines.
+*/
+
+-- 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
+);
+
+-- 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,
+
+    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
+);
+
+/*
+*   A gpu stores information about a type of gpu. The machine_gpu table keeps track of which gpus are in which machines.
+*/
+
+-- 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
+);
+
+-- 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,
+
+    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
+);
+
+/*
+*   A memory stores information about a type of memory. The machine_memory table keeps track of which memories are in 
+*   which machines.
+*/
+
+-- 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
+);
+
+-- 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,
+
+    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
+);
+
+/*
+*   A storage stores information about a type of storage. The machine_storage table keeps track of which storages are in 
+*   which machines.
+*/
+
+-- 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
+);
+
+-- 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
+);
diff --git a/database/test.sql b/database/test.sql
new file mode 100644
index 00000000..775ff584
--- /dev/null
+++ b/database/test.sql
@@ -0,0 +1,231 @@
+-- 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');
+INSERT INTO users (google_id, email, given_name, family_name)
+    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');
+
+-- Authorizations
+INSERT INTO authorizations (user_id, simulation_id, authorization_level)
+    VALUES (1, 1, 'OWN');
+INSERT INTO authorizations (user_id, simulation_id, authorization_level)
+    VALUES (2, 1, 'OWN');
+
+-- Paths
+INSERT INTO paths (simulation_id, datetime_created)
+    VALUES (1, '2016-07-11T11:00:00');
+INSERT INTO paths (simulation_id, datetime_created)
+    VALUES (1, '2016-07-18T09:00:00');
+
+-- Datacenter
+INSERT INTO datacenters (starred, simulation_id) VALUES (0, 1);
+INSERT INTO datacenters (starred, simulation_id) VALUES (0, 1);
+INSERT INTO datacenters (starred, simulation_id) VALUES (0, 1);
+
+-- Sections
+INSERT INTO sections (path_id, datacenter_id, start_tick) VALUES (1, 1, 0);
+INSERT INTO sections (path_id, datacenter_id, start_tick) VALUES (1, 2, 50);
+INSERT INTO sections (path_id, datacenter_id, start_tick) VALUES (1, 3, 100);
+
+INSERT INTO sections (path_id, datacenter_id, start_tick) VALUES (2, 3, 0);
+
+-- Trace
+INSERT INTO traces (name) VALUES ('test trace');
+
+-- Tasks
+INSERT INTO tasks (id, start_tick, total_flop_count, trace_id) VALUES (1, 0, 40000000, 1);
+INSERT INTO tasks (start_tick, total_flop_count, trace_id) VALUES (25, 10000, 1);
+INSERT INTO tasks (start_tick, total_flop_count, trace_id) VALUES (25, 10000, 1);
+INSERT INTO tasks (start_tick, total_flop_count, trace_id) VALUES (26, 10000, 1);
+INSERT INTO tasks (start_tick, total_flop_count, trace_id, task_dependency_id) VALUES (80, 200000, 1, 1);
+
+-- Experiments
+INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name)
+    VALUES (1, 1, 1, 'DEFAULT', 'Experiment 1');
+INSERT INTO experiments (simulation_id, path_id, trace_id, scheduler_name, name)
+    VALUES (1, 2, 1, 'DEFAULT', 'Experiment 2');
+
+-- 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) VALUES ('room 2', 3, 'SERVER');
+INSERT INTO rooms (name, datacenter_id, type) VALUES ('Power Room', 1, 'POWER');
+
+-- Tiles
+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) 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) VALUES (11, 10, 4);
+INSERT INTO tiles (position_x, position_y, room_id) VALUES (12, 10, 4);
+
+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;
+
+-- Machines
+INSERT INTO machines (rack_id, position) VALUES (1, 1);
+INSERT INTO machines (rack_id, position) VALUES (1, 2);
+INSERT INTO machines (rack_id, position) VALUES (1, 6);
+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) 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) VALUES (9, 4);
+INSERT INTO machines (rack_id, position) VALUES (9, 5);
+INSERT INTO machines (rack_id, position) VALUES (9, 6);
+INSERT INTO machines (rack_id, position) VALUES (9, 7);
+
+-- Tags
+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);
+
+-- 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);
+	
+-- 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);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (1, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (2, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (2, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (3, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (3, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (3, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (4, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (4, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (4, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (5, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (6, 1);
+
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (7, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (7, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (7, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (8, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (8, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (9, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (9, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (9, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (10, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (10, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (10, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (11, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (12, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (13, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (14, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (15, 1);
+
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (16, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (16, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (16, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (17, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (17, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (18, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (18, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (18, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (19, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (19, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (19, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (20, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (21, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (22, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (23, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (24, 1);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (25, 2);
+INSERT INTO machine_cpus (machine_id, cpu_id) VALUES (26, 2);
+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);
+
+-- 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);
+
+-- 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);
+
+-- Simulation queue
+INSERT INTO queued_experiments (experiment_id) VALUES (1);
+INSERT INTO queued_experiments (experiment_id) VALUES (2);
+