From dfddb6c25c96598295ad8b50092c9f4dd946e560 Mon Sep 17 00:00:00 2001
From: Fabian Mastenbroek <mail.fabianm@gmail.com>
Date: Fri, 20 Jul 2018 00:19:03 +0200
Subject: feat: Implement Scheduler Reference Architecture

This change implements the parts of the Datacenter Scheduling Reference
Architecture as published in SC18.

This commit consists of changes to the database schema such as:

- **Removal of `parallelizability` column in `Task`**
  This field was not used by the simulator and we opted to use a field
  describing the amount of cores the task can run on instead.
- **Removal of `task_id` column in `MachineState`**
  This field did not make sense anymore now that a machine can run
  multiple tasks. Fortunately, this field is not used in the frontend.
---
 database/schema.sql | 47 +++++++++++++++++++++++++++++++++++------------
 1 file changed, 35 insertions(+), 12 deletions(-)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 7f0d5879..15dbf043 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -63,7 +63,7 @@ INSERT INTO authorization_levels (level) VALUES ('VIEW');
 *       -   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)
+*       -   SS is the two-digit seconds part (0-59)
 */
 
 -- Simulation
@@ -146,9 +146,18 @@ DROP TABLE IF EXISTS schedulers;
 CREATE TABLE schedulers (
   name VARCHAR(50) PRIMARY KEY        NOT NULL
 );
-INSERT INTO schedulers (name) VALUES ('DEFAULT');
-INSERT INTO schedulers (name) VALUES ('SRTF');
-INSERT INTO schedulers (name) VALUES ('FIFO');
+INSERT INTO schedulers (name) VALUES ('FIFO-FIRSTFIT');
+INSERT INTO schedulers (name) VALUES ('FIFO-BESTFIT');
+INSERT INTO schedulers (name) VALUES ('FIFO-WORSTFIT');
+INSERT INTO schedulers (name) VALUES ('FIFO-RANDOM');
+INSERT INTO schedulers (name) VALUES ('SRTF-FIRSTFIT');
+INSERT INTO schedulers (name) VALUES ('SRTF-BESTFIT');
+INSERT INTO schedulers (name) VALUES ('SRTF-WORSTFIT');
+INSERT INTO schedulers (name) VALUES ('SRTF-RANDOM');
+INSERT INTO schedulers (name) VALUES ('RANDOM-FIRSTFIT');
+INSERT INTO schedulers (name) VALUES ('RANDOM-BESTFIT');
+INSERT INTO schedulers (name) VALUES ('RANDOM-WORSTFIT');
+INSERT INTO schedulers (name) VALUES ('RANDOM-RANDOM');
 
 /*
 *   Each simulation has a single trace. A trace contains tasks and their start times.
@@ -180,13 +189,24 @@ CREATE TABLE tasks (
   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  VARCHAR(50)             NOT NULL,
 
   FOREIGN KEY (job_id) REFERENCES jobs (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
+);
+
+-- A dependency between two tasks.
+DROP TABLE IF EXISTS task_dependencies;
+CREATE TABLE task_dependencies (
+  first_task_id  INTEGER NOT NULL,
+  second_task_id INTEGER NOT NULL,
+
+  PRIMARY KEY (first_task_id, second_task_id),
+  FOREIGN KEY (first_task_id) REFERENCES tasks (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
-  FOREIGN KEY (task_dependency_id) REFERENCES tasks (id)
+  FOREIGN KEY (second_task_id) REFERENCES tasks (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE
 );
@@ -318,8 +338,9 @@ DELIMITER //
 -- 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
+  BEFORE INSERT
+  ON tiles
+  FOR EACH ROW
   BEGIN
     -- checking tile overlap
     -- a tile already exists such that..
@@ -416,8 +437,9 @@ DELIMITER //
 -- 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
+  BEFORE UPDATE
+  ON tiles
+  FOR EACH ROW
   BEGIN
 
     IF ((NEW.object_id IS NOT NULL) AND (
@@ -543,8 +565,9 @@ DELIMITER //
 -- Make sure a machine is not inserted at a position that does not exist for its rack.
 DROP TRIGGER IF EXISTS before_insert_machine;
 CREATE TRIGGER before_insert_machine
-BEFORE INSERT ON machines
-FOR EACH ROW
+  BEFORE INSERT
+  ON machines
+  FOR EACH ROW
   BEGIN
     IF (
       NEW.position > (SELECT capacity
-- 
cgit v1.2.3


From 1e7e32cf7c65a5c9138c54495f1cc3f277529dd1 Mon Sep 17 00:00:00 2001
From: Georgios Andreadis <g.andreadis@student.tudelft.nl>
Date: Tue, 6 Feb 2018 11:59:54 +0100
Subject: feat: Add GWF conversion script

This change adds a conversion script that allows users to import traces
from the Grid Workload Archive (see http://gwa.ewi.tudelft.nl/) into the
OpenDC database.
---
 database/schema.sql | 12 +++++-------
 1 file changed, 5 insertions(+), 7 deletions(-)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 15dbf043..aa0ad1e5 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -185,11 +185,11 @@ CREATE TABLE jobs (
 -- A task that's defined in terms of how many flops (floating point operations) it takes to complete
 DROP TABLE IF EXISTS tasks;
 CREATE TABLE tasks (
-  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,
-  parallelizability  VARCHAR(50)             NOT NULL,
+  id               INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  start_tick       INTEGER                 NOT NULL CHECK (start_tick >= 0),
+  total_flop_count BIGINT                  NOT NULL CHECK (total_flop_count >= 0),
+  core_count       INTEGER                 NOT NULL CHECK (core_count >= 0),
+  job_id           INTEGER                 NOT NULL,
 
   FOREIGN KEY (job_id) REFERENCES jobs (id)
     ON DELETE CASCADE
@@ -236,7 +236,6 @@ CREATE TABLE task_states (
 DROP TABLE IF EXISTS machine_states;
 CREATE TABLE machine_states (
   id               INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
-  task_id          INTEGER,
   machine_id       INTEGER                 NOT NULL,
   experiment_id    INTEGER                 NOT NULL,
   tick             INTEGER                 NOT NULL,
@@ -244,7 +243,6 @@ CREATE TABLE machine_states (
   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,
-- 
cgit v1.2.3


From d8f5e2a7270a6aed04dac17048c6ef7229d3caab Mon Sep 17 00:00:00 2001
From: Fabian Mastenbroek <mail.fabianm@gmail.com>
Date: Sun, 11 Mar 2018 20:35:28 +0100
Subject: feat: Add stage measurements to schema

---
 database/schema.sql | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index aa0ad1e5..9e356b4a 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -232,6 +232,20 @@ CREATE TABLE task_states (
     ON UPDATE CASCADE
 );
 
+-- The measurements of a single stage
+CREATE TABLE stage_measurements (
+  id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  experiment_id INTEGER                 NOT NULL,
+  tick          INTEGER                 NOT NULL CHECK (tick >= 0),
+  stage         INTEGER                 NOT NULL CHECK (stage >= 0),
+  duration      INTEGER                 NOT NULL CHECK (duration >= 0),
+  size          INTEGER                 NOT NULL CHECK (size >= 0),
+
+  FOREIGN KEY (experiment_id) REFERENCES experiments (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
+);
+
 -- A machine state
 DROP TABLE IF EXISTS machine_states;
 CREATE TABLE machine_states (
-- 
cgit v1.2.3


From 1ccc68defdb1f67f39dc836ee6659a89135c038c Mon Sep 17 00:00:00 2001
From: Georgios Andreadis <g.andreadis@student.tudelft.nl>
Date: Mon, 12 Mar 2018 07:33:59 +0100
Subject: feat: Add task metrics to DB schema

---
 database/schema.sql | 19 +++++++++++++++++++
 1 file changed, 19 insertions(+)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 9e356b4a..3b751c30 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -233,6 +233,7 @@ CREATE TABLE task_states (
 );
 
 -- The measurements of a single stage
+DROP TABLE IF EXISTS stage_measurements;
 CREATE TABLE stage_measurements (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   experiment_id INTEGER                 NOT NULL,
@@ -246,6 +247,24 @@ CREATE TABLE stage_measurements (
     ON UPDATE CASCADE
 );
 
+-- Metrics of a single task
+DROP TABLE IF EXISTS task_metrics;
+CREATE TABLE task_metrics (
+  id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  experiment_id INTEGER                 NOT NULL,
+  task_id       INTEGER                 NOT NULL,
+  waiting       INTEGER                 NOT NULL CHECK (waiting >= 0),
+  execution     INTEGER                 NOT NULL CHECK (execution >= 0),
+  turnaround    INTEGER                 NOT NULL CHECK (turnaround >= 0),
+
+  FOREIGN KEY (experiment_id) REFERENCES experiments (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (task_id) REFERENCES tasks (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
+);
+
 -- A machine state
 DROP TABLE IF EXISTS machine_states;
 CREATE TABLE machine_states (
-- 
cgit v1.2.3


From aa97e067caae9cbe25fc9140fc3e625933615fb9 Mon Sep 17 00:00:00 2001
From: Georgios Andreadis <g.andreadis@student.tudelft.nl>
Date: Sat, 24 Mar 2018 11:55:45 +0100
Subject: feat: Add job id to DB schema

---
 database/schema.sql | 4 ++++
 1 file changed, 4 insertions(+)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 3b751c30..b9460701 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -253,6 +253,7 @@ CREATE TABLE task_metrics (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   experiment_id INTEGER                 NOT NULL,
   task_id       INTEGER                 NOT NULL,
+  job_id        INTEGER                 NOT NULL,
   waiting       INTEGER                 NOT NULL CHECK (waiting >= 0),
   execution     INTEGER                 NOT NULL CHECK (execution >= 0),
   turnaround    INTEGER                 NOT NULL CHECK (turnaround >= 0),
@@ -261,6 +262,9 @@ CREATE TABLE task_metrics (
     ON DELETE CASCADE
     ON UPDATE CASCADE,
   FOREIGN KEY (task_id) REFERENCES tasks (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (job_id) REFERENCES jobs (id)
     ON DELETE CASCADE
     ON UPDATE CASCADE
 );
-- 
cgit v1.2.3


From fc7c58da98f0eddbd0109e2a09bf394dde555176 Mon Sep 17 00:00:00 2001
From: Fabian Mastenbroek <mail.fabianm@gmail.com>
Date: Sat, 24 Mar 2018 12:22:02 +0100
Subject: feat: Add support for both wall and cpu time

---
 database/schema.sql | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index b9460701..36db1e17 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -239,8 +239,10 @@ CREATE TABLE stage_measurements (
   experiment_id INTEGER                 NOT NULL,
   tick          INTEGER                 NOT NULL CHECK (tick >= 0),
   stage         INTEGER                 NOT NULL CHECK (stage >= 0),
-  duration      INTEGER                 NOT NULL CHECK (duration >= 0),
+  cpu           INTEGER                 NOT NULL CHECK (cpu >= 0),
+  wall          INTEGER                 NOT NULL CHECK (wall >= 0),
   size          INTEGER                 NOT NULL CHECK (size >= 0),
+  iterations    INTEGER                 NOT NULL CHECK (iterations >= 0),
 
   FOREIGN KEY (experiment_id) REFERENCES experiments (id)
     ON DELETE CASCADE
-- 
cgit v1.2.3


From 47994b66619872336519079846e845b313e948b0 Mon Sep 17 00:00:00 2001
From: Fabian Mastenbroek <mail.fabianm@gmail.com>
Date: Mon, 26 Mar 2018 00:47:14 +0200
Subject: feat: Add job metrics

---
 database/schema.sql | 24 ++++++++++++++++++++++--
 1 file changed, 22 insertions(+), 2 deletions(-)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 36db1e17..f6286260 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -239,8 +239,8 @@ CREATE TABLE stage_measurements (
   experiment_id INTEGER                 NOT NULL,
   tick          INTEGER                 NOT NULL CHECK (tick >= 0),
   stage         INTEGER                 NOT NULL CHECK (stage >= 0),
-  cpu           INTEGER                 NOT NULL CHECK (cpu >= 0),
-  wall          INTEGER                 NOT NULL CHECK (wall >= 0),
+  cpu           BIGINT                  NOT NULL CHECK (cpu >= 0),
+  wall          BIGINT                  NOT NULL CHECK (wall >= 0),
   size          INTEGER                 NOT NULL CHECK (size >= 0),
   iterations    INTEGER                 NOT NULL CHECK (iterations >= 0),
 
@@ -249,6 +249,26 @@ CREATE TABLE stage_measurements (
     ON UPDATE CASCADE
 );
 
+-- Metrics of a job task
+DROP TABLE IF EXISTS job_metrics;
+CREATE TABLE job_metrics (
+  id                   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
+  experiment_id        INTEGER                 NOT NULL,
+  job_id               INTEGER                 NOT NULL,
+  critical_path        INTEGER                 NOT NULL CHECK (critical_path >= 0),
+  critical_path_length INTEGER                 NOT NULL CHECK (critical_path_length >= 0),
+  waiting_time         INTEGER                 NOT NULL CHECK (waiting_time >= 0),
+  makespan             INTEGER                 NOT NULL CHECK (makespan >= 0),
+  nsl                  INTEGER                 NOT NULL CHECK (nsl >= 0),
+
+  FOREIGN KEY (experiment_id) REFERENCES experiments (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE,
+  FOREIGN KEY (job_id) REFERENCES jobs (id)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE
+);
+
 -- Metrics of a single task
 DROP TABLE IF EXISTS task_metrics;
 CREATE TABLE task_metrics (
-- 
cgit v1.2.3

