diff options
Diffstat (limited to 'opendc/opendc-experiments-sc20/schema.sql')
| -rw-r--r-- | opendc/opendc-experiments-sc20/schema.sql | 75 |
1 files changed, 47 insertions, 28 deletions
diff --git a/opendc/opendc-experiments-sc20/schema.sql b/opendc/opendc-experiments-sc20/schema.sql index 677240fa..515348c6 100644 --- a/opendc/opendc-experiments-sc20/schema.sql +++ b/opendc/opendc-experiments-sc20/schema.sql @@ -1,13 +1,26 @@ --- A portfolio represents a collection of scenarios are tested. -DROP TABLE IF EXISTS portfolios; +-- An experiment represents a collection of portfolios. +DROP TABLE IF EXISTS experiments CASCADE; +CREATE TABLE experiments +( + id BIGSERIAL PRIMARY KEY NOT NULL, + creation_time TIMESTAMP NOT NULL DEFAULT (now()) +); + +-- A portfolio represents a collection of scenarios tested. +DROP TABLE IF EXISTS portfolios CASCADE; CREATE TABLE portfolios ( - id BIGSERIAL PRIMARY KEY NOT NULL, - name TEXT NOT NULL + id BIGSERIAL PRIMARY KEY NOT NULL, + experiment_id BIGINT NOT NULL, + name TEXT NOT NULL, + + FOREIGN KEY (experiment_id) REFERENCES experiments (id) + ON DELETE CASCADE + ON UPDATE CASCADE ); -- A scenario represents a single point in the design space (a unique combination of parameters) -DROP TABLE IF EXISTS scenarios; +DROP TABLE IF EXISTS scenarios CASCADE; CREATE TABLE scenarios ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -17,27 +30,30 @@ CREATE TABLE scenarios workload_name TEXT NOT NULL, workload_fraction DOUBLE PRECISION NOT NULL, allocation_policy TEXT NOT NULL, - failures BIT NOT NULL, - interference BIT NOT NULL, + failures BOOLEAN NOT NULL, + interference BOOLEAN NOT NULL, FOREIGN KEY (portfolio_id) REFERENCES portfolios (id) ON DELETE CASCADE ON UPDATE CASCADE + ); +DROP TYPE IF EXISTS run_state CASCADE; CREATE TYPE run_state AS ENUM ('wait', 'active', 'fail', 'ok'); -- An experiment run represent a single invocation of a trial and is used to distinguish between repetitions of the -- same set of parameters. -DROP TABLE IF EXISTS runs; +DROP TABLE IF EXISTS runs CASCADE; CREATE TABLE runs ( - id INTEGER NOT NULL, - scenario_id BIGINT NOT NULL, - seed INTEGER NOT NULL, - state run_state NOT NULL DEFAULT 'wait'::run_state, - start_time TIMESTAMP NOT NULL, - end_time TIMESTAMP, + id INTEGER NOT NULL, + scenario_id BIGINT NOT NULL, + seed INTEGER NOT NULL, + state run_state NOT NULL DEFAULT 'wait'::run_state, + submit_time TIMESTAMP NOT NULL DEFAULT (now()), + start_time TIMESTAMP DEFAULT (NULL), + end_time TIMESTAMP DEFAULT (NULL), PRIMARY KEY (scenario_id, id), FOREIGN KEY (scenario_id) REFERENCES scenarios (id) @@ -46,7 +62,7 @@ CREATE TABLE runs ); -- Metrics of the hypervisors reported per slice -DROP TABLE IF EXISTS host_metrics; +DROP TABLE IF EXISTS host_metrics CASCADE; CREATE TABLE host_metrics ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -70,10 +86,11 @@ CREATE TABLE host_metrics ON UPDATE CASCADE ); +DROP INDEX IF EXISTS host_metrics_idx; CREATE INDEX host_metrics_idx ON host_metrics (scenario_id, run_id, timestamp, host_id); -- Metrics of the VMs reported per slice -DROP TABLE IF EXISTS vm_metrics; +DROP TABLE IF EXISTS vm_metrics CASCADE; CREATE TABLE vm_metrics ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -96,27 +113,29 @@ CREATE TABLE vm_metrics ON UPDATE CASCADE ); +DROP INDEX IF EXISTS vm_metrics_idx; CREATE INDEX vm_metrics_idx ON vm_metrics (scenario_id, run_id, timestamp, vm_id); -- Metrics of the provisioner reported per change -DROP TABLE IF EXISTS provisioner_metrics; +DROP TABLE IF EXISTS provisioner_metrics CASCADE; CREATE TABLE provisioner_metrics ( - id BIGSERIAL PRIMARY KEY NOT NULL, - scenario_id BIGINT NOT NULL, - run_id INTEGER NOT NULL, - timestamp TIMESTAMP NOT NULL, - host_total_count INTEGER NOT NULL, - host_available_count INTEGER NOT NULL, - vm_total_count INTEGER NOT NULL, - vm_active_count INTEGER NOT NULL, - vm_inactive_count INTEGER NOT NULL, - vm_waiting_count INTEGER NOT NULL, - vm_failed_count INTEGER NOT NULL, + id BIGSERIAL PRIMARY KEY NOT NULL, + scenario_id BIGINT NOT NULL, + run_id INTEGER NOT NULL, + timestamp TIMESTAMP NOT NULL, + host_total_count INTEGER NOT NULL, + host_available_count INTEGER NOT NULL, + vm_total_count INTEGER NOT NULL, + vm_active_count INTEGER NOT NULL, + vm_inactive_count INTEGER NOT NULL, + vm_waiting_count INTEGER NOT NULL, + vm_failed_count INTEGER NOT NULL, FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) ON DELETE CASCADE ON UPDATE CASCADE ); +DROP INDEX IF EXISTS provisioner_metrics_idx; CREATE INDEX provisioner_metrics_idx ON provisioner_metrics (scenario_id, run_id, timestamp); |