Homec4science
Diffusion blackdynamite (previous) Browse

R3127/python/BlackDynamite/build_tables.sql
a99c8a15c9b3previous

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
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));
-- 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 ON DELETE CASCADE,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities ON DELETE CASCADE);
CREATE INDEX scalar_integer_run_id on SCHEMAS_IDENTIFIER.scalar_integer (run_id,quantity_id,step);
-- 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 ON DELETE CASCADE,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities ON DELETE CASCADE);
CREATE INDEX scalar_real_run_id on SCHEMAS_IDENTIFIER.scalar_real (run_id,quantity_id,step);
-- 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 ON DELETE CASCADE,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities ON DELETE CASCADE);
CREATE INDEX vector_real_run_id on SCHEMAS_IDENTIFIER.vector_real (run_id,quantity_id,step);
-- 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 ON DELETE CASCADE,
FOREIGN KEY (quantity_id) REFERENCES SCHEMAS_IDENTIFIER.quantities ON DELETE CASCADE);
CREATE INDEX vector_integer_run_id on SCHEMAS_IDENTIFIER.vector_integer (run_id,quantity_id,step);
-- configuration files
CREATE TABLE SCHEMAS_IDENTIFIER.configfiles (
id SERIAL PRIMARY KEY, --id, standard
filename TEXT NOT NULL, -- name of the config file
file TEXT NOT NULL -- content of the config file
);
CREATE UNIQUE INDEX configfiles_md5file_key ON SCHEMAS_IDENTIFIER.configfiles ((md5(file)));
--ALTER TABLE SCHEMAS_IDENTIFIER.configfiles ADD UNIQUE (SCHEMAS_IDENTIFIER.configfiles_md5file_key);
-- configuration files
CREATE TABLE SCHEMAS_IDENTIFIER.runconfig (
id SERIAL PRIMARY KEY, --id, standard
run_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.runs
configfile_id INTEGER NOT NULL, --referencing SCHEMAS_IDENTIFIER.configfiles
FOREIGN KEY (run_id) REFERENCES SCHEMAS_IDENTIFIER.runs ON DELETE CASCADE,
FOREIGN KEY (configfile_id) REFERENCES SCHEMAS_IDENTIFIER.configfiles);
-- create functions for the different triggers
--create run_inserter
CREATE FUNCTION SCHEMAS_IDENTIFIER.run_inserter()
RETURNS TRIGGER AS $run_inserter$
BEGIN
NEW.state := 'CREATED';
-- NEW.run_name := 'run' || NEW.id || ':' || NEW.run_name;
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 set start time trigger
CREATE FUNCTION SCHEMAS_IDENTIFIER.set_start_time()
RETURNS TRIGGER AS $set_start_time$
BEGIN
IF NEW.state = 'START' THEN NEW.start_time := CURRENT_TIMESTAMP; END IF;
RETURN NEW;
END;
$set_start_time$ LANGUAGE PLPGSQL VOLATILE;
--automagic clean of config files
CREATE FUNCTION SCHEMAS_IDENTIFIER.delete_file()
RETURNS TRIGGER AS $delete_file$
BEGIN
DELETE FROM SCHEMAS_IDENTIFIER.configfiles;
RETURN NEW;
EXCEPTION WHEN FOREIGN_KEY_VIOLATION THEN NULL;
RETURN NEW;
END;
$delete_file$ LANGUAGE PLPGSQL;
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();
CREATE TRIGGER update_start_time BEFORE UPDATE ON SCHEMAS_IDENTIFIER.runs FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.set_start_time();
CREATE TRIGGER delete_conffile AFTER DELETE ON SCHEMAS_IDENTIFIER.runs FOR EACH ROW EXECUTE PROCEDURE SCHEMAS_IDENTIFIER.delete_file();
c4science · Help