Homec4science
Diffusion blackdynamite (previous) Browse

R3127/src/build_tables.sql
3b2523ad7fa1previous

Last Change
Raw File

build_tables.sql
View Options

-- 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