Homec4science
Diffusion blackdynamite (previous) Browse

R3127/src/build_tables.sql
c98a351f6047previous

Last Change
Raw File

build_tables.sql
View Options

-- runs
CREATE TABLE SCHEMAS_IDENTIFIER.runs (
id SERIAL PRIMARY KEY, --id, standard
machine_name TEXT, --name of the machine on which a job runs. initialised to NULL and filled out at job start
job_id INTEGER NOT NULL, --references the job on which this job is based
has_started BOOLEAN NOT NULL, --initialised to FALSE, set to true at the beginning of a simulation run
has_finished BOOLEAN NOT NULL, --initialised to FALSE, set to true at the end of a simulation run
run_name TEXT NOT NULL, -- automacally generated run_name which will be shown in qstat
wait_id INTEGER, -- may be null, in which case the run can be executed immediately. if non-null, this job will be held back using the run_name of the job having id=wait_id
user_field TEXT , -- can be used as seen fit by the utser
start_time TIMESTAMP, --automatically set at run start. used to figure out most recent values
FOREIGN KEY (job_id) REFERENCES SCHEMAS_IDENTIFIER.jobs);
-- quantities
CREATE TABLE SCHEMAS_IDENTIFIER.quantities (
id SERIAL PRIMARY KEY, --id, standard
name TEXT NOT NULL, -- human readable name idendifying the quantity
mean_window INTEGER, -- size of the averaging window. NULL corresponds to no averaging (i.e. mean_window=NULL corresponds to mean_window=1)
is_integer BOOLEAN NOT NULL, --whether the quantity is an integer quantity
is_vector BOOLEAN NOT NULL, --whether the quantity is vectorial or scalar
description TEXT , --general description the quantity (like units)
UNIQUE (name, mean_window));
-- integer scalar values
CREATE TABLE SCHEMAS_IDENTIFIER.scalar_integer (
id SERIAL PRIMARY KEY, --id, standard
run_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.runs
quantity_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.quantities
measurement INTEGER NOT NULL, -- measured value
step INTEGER NOT NULL, --step at which this measurement has been taken
computed_at TIMESTAMP NOT NULL, --time when the measurement has been added to the database. this column might be dropped later on
FOREIGN KEY (run_id) REFERENCES SCHEMAS_IDENTIFIER.runs,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities);
-- real scalar values
CREATE TABLE SCHEMAS_IDENTIFIER.scalar_real (
id SERIAL PRIMARY KEY, --id, standard
run_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.runs
quantity_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.quantities
measurement DOUBLE PRECISION NOT NULL, -- measured value
step INTEGER NOT NULL, --step at which this measurement has been taken
computed_at TIMESTAMP NOT NULL, --time when the measurement has been added to the database. this column might be dropped later on
FOREIGN KEY (run_id) REFERENCES SCHEMAS_IDENTIFIER.runs,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities);
-- real vector values
CREATE TABLE SCHEMAS_IDENTIFIER.vector_real (
id SERIAL PRIMARY KEY, --id, standard
run_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.runs
quantity_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.quantities
measurement DOUBLE PRECISION[] NOT NULL, -- measured value
step INTEGER NOT NULL, --step at which this measurement has been taken
computed_at TIMESTAMP NOT NULL, --time when the measurement has been added to the database. this column might be dropped later on
FOREIGN KEY (run_id) REFERENCES SCHEMAS_IDENTIFIER.runs,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities);
-- int vector values
CREATE TABLE SCHEMAS_IDENTIFIER.vector_integer (
id SERIAL PRIMARY KEY, --id, standard
run_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.runs
quantity_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.quantities
measurement INTEGER [] NOT NULL, -- measured value
step INTEGER NOT NULL, --step at which this measurement has been taken
computed_at TIMESTAMP NOT NULL, --time when the measurement has been added to the database. this column might be dropped later on
FOREIGN KEY (run_id) REFERENCES SCHEMAS_IDENTIFIER.runs,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities);
-- create functions for the different triggers
--create run_inserter
CREATE FUNCTION SCHEMAS_IDENTIFIER.run_inserter()
RETURNS TRIGGER AS $run_inserter$
BEGIN
NEW.has_started := FALSE;
NEW.has_finished := FALSE;
NEW.run_name := 'run' || NEW.id;
RETURN NEW;
END;
$run_inserter$ LANGUAGE 'PLPGSQL' VOLATILE;
CREATE TRIGGER run_inserter BEFORE INSERT ON SCHEMAS_IDENTIFIER.runs FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.run_inserter();
--create measurement inserter
CREATE FUNCTION SCHEMAS_IDENTIFIER.measurement_inserter()
RETURNS TRIGGER AS $measurement_inserter$
BEGIN
NEW.computed_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$measurement_inserter$ LANGUAGE 'PLPGSQL' VOLATILE;
CREATE TRIGGER int_scal_insert BEFORE INSERT ON SCHEMAS_IDENTIFIER.scalar_integer FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.measurement_inserter();
CREATE TRIGGER int_vect_insert BEFORE INSERT ON SCHEMAS_IDENTIFIER.vector_integer FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.measurement_inserter();
CREATE TRIGGER real_scal_insert BEFORE INSERT ON SCHEMAS_IDENTIFIER.scalar_real FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.measurement_inserter();
CREATE TRIGGER real_vect_insert BEFORE INSERT ON SCHEMAS_IDENTIFIER.vector_real FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.measurement_inserter();
c4science · Help