From 805367fb4c5af4ba40e8c507b788da0078b4f60d Mon Sep 17 00:00:00 2001
From: Georgios Andreadis <g.andreadis@student.tudelft.nl>
Date: Mon, 25 Sep 2017 08:32:07 +0200
Subject: Update schema.sql to drop tables if existing

---
 database/schema.sql | 122 +++++++++++++++++++++++++++++++++-------------------
 1 file changed, 78 insertions(+), 44 deletions(-)

(limited to 'database/schema.sql')

diff --git a/database/schema.sql b/database/schema.sql
index 6414cf24..3fbb7a10 100644
--- a/database/schema.sql
+++ b/database/schema.sql
@@ -6,7 +6,8 @@ SET FOREIGN_KEY_CHECKS = 0;
 */
 
 -- Users
-CREATE TABLE IF NOT EXISTS users (
+DROP TABLE IF EXISTS users;
+CREATE TABLE users (
   id          INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   google_id   TEXT                    NOT NULL,
   email       TEXT,
@@ -20,7 +21,8 @@ CREATE TABLE IF NOT EXISTS users (
 */
 
 -- User authorizations
-CREATE TABLE IF NOT EXISTS authorizations (
+DROP TABLE IF EXISTS authorizations;
+CREATE TABLE authorizations (
   user_id             INTEGER     NOT NULL,
   simulation_id       INTEGER     NOT NULL,
   authorization_level VARCHAR(50) NOT NULL,
@@ -41,12 +43,13 @@ CREATE UNIQUE INDEX authorizations_index
   );
 
 -- Authorization levels
-CREATE TABLE IF NOT EXISTS authorization_levels (
+DROP TABLE IF EXISTS authorization_levels;
+CREATE TABLE authorization_levels (
   level VARCHAR(50) PRIMARY KEY        NOT NULL
 );
-INSERT INTO authorization_levels (level) VALUES ("OWN");
-INSERT INTO authorization_levels (level) VALUES ("EDIT");
-INSERT INTO authorization_levels (level) VALUES ("VIEW");
+INSERT INTO authorization_levels (level) VALUES ('OWN');
+INSERT INTO authorization_levels (level) VALUES ('EDIT');
+INSERT INTO authorization_levels (level) VALUES ('VIEW');
 
 /*
 *   A Simulation has several Paths, which define the topology of the datacenter at different times. A Simulation also
@@ -64,7 +67,8 @@ INSERT INTO authorization_levels (level) VALUES ("VIEW");
 */
 
 -- Simulation
-CREATE TABLE IF NOT EXISTS simulations (
+DROP TABLE IF EXISTS simulations;
+CREATE TABLE simulations (
   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__:__:__'),
@@ -77,7 +81,8 @@ CREATE TABLE IF NOT EXISTS simulations (
 *   Trace defines which tasks have to be run in the simulation.
 */
 
-CREATE TABLE IF NOT EXISTS experiments (
+DROP TABLE IF EXISTS experiments;
+CREATE TABLE experiments (
   id                  INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   simulation_id       INTEGER                 NOT NULL,
   path_id             INTEGER                 NOT NULL,
@@ -108,7 +113,8 @@ CREATE TABLE IF NOT EXISTS experiments (
 */
 
 -- Path
-CREATE TABLE IF NOT EXISTS paths (
+DROP TABLE IF EXISTS paths;
+CREATE TABLE paths (
   id               INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   simulation_id    INTEGER                 NOT NULL,
   name             TEXT,
@@ -120,7 +126,8 @@ CREATE TABLE IF NOT EXISTS paths (
 );
 
 -- Sections
-CREATE TABLE IF NOT EXISTS sections (
+DROP TABLE IF EXISTS sections;
+CREATE TABLE sections (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   path_id       INTEGER                 NOT NULL,
   datacenter_id INTEGER                 NOT NULL,
@@ -135,25 +142,28 @@ CREATE TABLE IF NOT EXISTS sections (
 );
 
 -- Scheduler names
-CREATE TABLE IF NOT EXISTS schedulers (
+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 ('DEFAULT');
+INSERT INTO schedulers (name) VALUES ('SRTF');
+INSERT INTO schedulers (name) VALUES ('FIFO');
 
 /*
 *   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 (
+-- A trace describes when tasks arrives in a datacenter
+DROP TABLE IF EXISTS traces;
+CREATE TABLE traces (
   id   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   name TEXT                    NOT NULL
 );
 
 -- A job
-CREATE TABLE IF NOT EXISTS jobs (
+DROP TABLE IF EXISTS jobs;
+CREATE TABLE jobs (
   id       INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   name     TEXT                    NOT NULL,
   trace_id INTEGER                 NOT NULL,
@@ -164,7 +174,8 @@ 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 (
+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,
@@ -186,7 +197,8 @@ CREATE TABLE IF NOT EXISTS tasks (
 */
 
 -- A state for a task_flop
-CREATE TABLE IF NOT EXISTS task_states (
+DROP TABLE IF EXISTS task_states;
+CREATE TABLE task_states (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   task_id       INTEGER                 NOT NULL,
   experiment_id INTEGER                 NOT NULL,
@@ -198,8 +210,9 @@ CREATE TABLE IF NOT EXISTS task_states (
   FOREIGN KEY (experiment_id) REFERENCES experiments (id)
 );
 
--- A machine state 
-CREATE TABLE IF NOT EXISTS machine_states (
+-- A machine state
+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,
@@ -221,8 +234,9 @@ CREATE TABLE IF NOT EXISTS machine_states (
 *   forth between different topologies.
 */
 
--- Datacenters 
-CREATE TABLE IF NOT EXISTS datacenters (
+-- Datacenters
+DROP TABLE IF EXISTS datacenters;
+CREATE TABLE datacenters (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   simulation_id INTEGER                 NOT NULL,
   starred       INTEGER CHECK (starred = 0 OR starred = 1),
@@ -237,7 +251,8 @@ CREATE TABLE IF NOT EXISTS datacenters (
 */
 
 -- Rooms in a datacenter
-CREATE TABLE IF NOT EXISTS rooms (
+DROP TABLE IF EXISTS rooms;
+CREATE TABLE rooms (
   id            INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   name          TEXT                    NOT NULL,
   datacenter_id INTEGER                 NOT NULL,
@@ -253,7 +268,8 @@ CREATE TABLE IF NOT EXISTS rooms (
   UNIQUE (name, datacenter_id)
 );
 
-CREATE TABLE IF NOT EXISTS room_types (
+DROP TABLE IF EXISTS room_types;
+CREATE TABLE room_types (
   name VARCHAR(50) PRIMARY KEY        NOT NULL
 );
 INSERT INTO room_types (name) VALUES ('SERVER');
@@ -269,7 +285,8 @@ INSERT INTO room_types (name) VALUES ('COOLING');
 */
 
 -- Tiles in a room
-CREATE TABLE IF NOT EXISTS tiles (
+DROP TABLE IF EXISTS tiles;
+CREATE TABLE tiles (
   id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   position_x INTEGER                 NOT NULL,
   position_y INTEGER                 NOT NULL,
@@ -312,7 +329,7 @@ FOR EACH ROW
     THEN
       -- raise an error
       SIGNAL SQLSTATE '45000'
-      SET MESSAGE_TEXT = "OccupiedTilePosition";
+      SET MESSAGE_TEXT = 'OccupiedTilePosition';
     END IF;
 
     -- checking tile adjacency
@@ -352,7 +369,8 @@ DELIMITER ;
 */
 
 -- Objects
-CREATE TABLE IF NOT EXISTS objects (
+DROP TABLE IF EXISTS objects;
+CREATE TABLE objects (
   id   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   type VARCHAR(50)             NOT NULL,
 
@@ -360,7 +378,8 @@ CREATE TABLE IF NOT EXISTS objects (
 );
 
 -- Object types
-CREATE TABLE IF NOT EXISTS object_types (
+DROP TABLE IF EXISTS object_types;
+CREATE TABLE object_types (
   name VARCHAR(50) PRIMARY KEY        NOT NULL
 );
 INSERT INTO object_types (name) VALUES ('PSU');
@@ -368,7 +387,8 @@ INSERT INTO object_types (name) VALUES ('COOLING_ITEM');
 INSERT INTO object_types (name) VALUES ('RACK');
 
 -- Allowed objects table
-CREATE TABLE IF NOT EXISTS allowed_objects (
+DROP TABLE IF EXISTS allowed_objects;
+CREATE TABLE allowed_objects (
   room_type   VARCHAR(50) NOT NULL,
   object_type VARCHAR(50) NOT NULL,
 
@@ -422,7 +442,8 @@ DELIMITER ;
 */
 
 -- PSUs on tiles
-CREATE TABLE IF NOT EXISTS psus (
+DROP TABLE IF EXISTS psus;
+CREATE TABLE psus (
   id               INTEGER     NOT NULL AUTO_INCREMENT,
   energy_kwh       INTEGER     NOT NULL CHECK (energy_kwh > 0),
   type             VARCHAR(50) NOT NULL,
@@ -443,7 +464,8 @@ CREATE TABLE IF NOT EXISTS psus (
 */
 
 -- Cooling items on tiles
-CREATE TABLE IF NOT EXISTS cooling_items (
+DROP TABLE IF EXISTS cooling_items;
+CREATE TABLE cooling_items (
   id                   INTEGER     NOT NULL AUTO_INCREMENT,
   energy_consumption_w INTEGER     NOT NULL CHECK (energy_consumption_w > 0),
   type                 VARCHAR(50) NOT NULL,
@@ -464,7 +486,8 @@ CREATE TABLE IF NOT EXISTS cooling_items (
 */
 
 -- Racks on tiles
-CREATE TABLE IF NOT EXISTS racks (
+DROP TABLE IF EXISTS racks;
+CREATE TABLE racks (
   id               INTEGER NOT NULL AUTO_INCREMENT,
   name             TEXT,
   capacity         INTEGER NOT NULL CHECK (capacity > 0),
@@ -483,7 +506,8 @@ CREATE TABLE IF NOT EXISTS racks (
 */
 
 -- Machines in racks
-CREATE TABLE IF NOT EXISTS machines (
+DROP TABLE IF EXISTS machines;
+CREATE TABLE machines (
   id       INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   rack_id  INTEGER                 NOT NULL,
   position INTEGER                 NOT NULL CHECK (position > 0),
@@ -523,7 +547,8 @@ DELIMITER ;
 */
 
 -- Tags for machines
-CREATE TABLE IF NOT EXISTS machine_tags (
+DROP TABLE IF EXISTS machine_tags;
+CREATE TABLE machine_tags (
   name       TEXT    NOT NULL,
   machine_id INTEGER NOT NULL,
 
@@ -537,7 +562,8 @@ CREATE TABLE IF NOT EXISTS machine_tags (
 */
 
 -- Failure models
-CREATE TABLE IF NOT EXISTS failure_models (
+DROP TABLE IF EXISTS failure_models;
+CREATE TABLE failure_models (
   id   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   name TEXT                    NOT NULL,
   rate REAL                    NOT NULL CHECK (rate >= 0 AND rate <= 1)
@@ -548,7 +574,8 @@ CREATE TABLE IF NOT EXISTS failure_models (
 */
 
 -- CPU specs
-CREATE TABLE IF NOT EXISTS cpus (
+DROP TABLE IF EXISTS cpus;
+CREATE TABLE cpus (
   id                   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   manufacturer         TEXT                    NOT NULL,
   family               TEXT                    NOT NULL,
@@ -565,7 +592,8 @@ CREATE TABLE IF NOT EXISTS cpus (
 );
 
 -- CPUs in machines
-CREATE TABLE IF NOT EXISTS machine_cpus (
+DROP TABLE IF EXISTS machine_cpus;
+CREATE TABLE machine_cpus (
   id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   machine_id INTEGER                 NOT NULL,
   cpu_id     INTEGER                 NOT NULL,
@@ -583,7 +611,8 @@ CREATE TABLE IF NOT EXISTS machine_cpus (
 */
 
 -- GPU specs
-CREATE TABLE IF NOT EXISTS gpus (
+DROP TABLE IF EXISTS gpus;
+CREATE TABLE gpus (
   id                   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   manufacturer         TEXT                    NOT NULL,
   family               TEXT                    NOT NULL,
@@ -600,7 +629,8 @@ CREATE TABLE IF NOT EXISTS gpus (
 );
 
 -- GPUs in machines
-CREATE TABLE IF NOT EXISTS machine_gpus (
+DROP TABLE IF EXISTS machine_gpus;
+CREATE TABLE machine_gpus (
   id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   machine_id INTEGER                 NOT NULL,
   gpu_id     INTEGER                 NOT NULL,
@@ -619,7 +649,8 @@ CREATE TABLE IF NOT EXISTS machine_gpus (
 */
 
 -- Memory specs
-CREATE TABLE IF NOT EXISTS memories (
+DROP TABLE IF EXISTS memories;
+CREATE TABLE memories (
   id                   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   manufacturer         TEXT                    NOT NULL,
   family               TEXT                    NOT NULL,
@@ -636,7 +667,8 @@ CREATE TABLE IF NOT EXISTS memories (
 );
 
 -- Memory in machines
-CREATE TABLE IF NOT EXISTS machine_memories (
+DROP TABLE IF EXISTS machine_memories;
+CREATE TABLE machine_memories (
   id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   machine_id INTEGER                 NOT NULL,
   memory_id  INTEGER                 NOT NULL,
@@ -655,7 +687,8 @@ CREATE TABLE IF NOT EXISTS machine_memories (
 */
 
 -- Storage specs
-CREATE TABLE IF NOT EXISTS storages (
+DROP TABLE IF EXISTS storages;
+CREATE TABLE storages (
   id                   INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   manufacturer         TEXT                    NOT NULL,
   family               TEXT                    NOT NULL,
@@ -672,7 +705,8 @@ CREATE TABLE IF NOT EXISTS storages (
 );
 
 -- Storage in machines
-CREATE TABLE IF NOT EXISTS machine_storages (
+DROP TABLE IF EXISTS machine_storages;
+CREATE TABLE machine_storages (
   id         INTEGER PRIMARY KEY     NOT NULL AUTO_INCREMENT,
   machine_id INTEGER                 NOT NULL,
   storage_id INTEGER                 NOT NULL,
-- 
cgit v1.2.3

