# HG changeset patch # User Sascha L. Teichmann # Date 1299679635 0 # Node ID 324e12ab7a05ad7321506a11be6c7cd4f902ac27 # Parent 4a3b2912a0cd0d1e3ba1af556d542f71700bb798 Converted schema to be PostgreSQL compatible. flys-backend/trunk@1441 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 4a3b2912a0cd -r 324e12ab7a05 flys-backend/ChangeLog --- a/flys-backend/ChangeLog Wed Mar 09 09:41:01 2011 +0000 +++ b/flys-backend/ChangeLog Wed Mar 09 14:07:15 2011 +0000 @@ -1,3 +1,11 @@ +2011-03-09 Sascha L. Teichmann + + * README: New. Some setup instructions. + + * doc/schema/postgresql.sql: New. Schema converted to PostgreSQL + + * doc/schema/sqlite.sql: Fixed defect foreign key constraints. + 2011-03-09 Sascha L. Teichmann * doc/schema/sqlite.sql: Factorized time intervals out into diff -r 4a3b2912a0cd -r 324e12ab7a05 flys-backend/README --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-backend/README Wed Mar 09 14:07:15 2011 +0000 @@ -0,0 +1,10 @@ +For the artifact database +# su - postgres +$ createuser --no-createrole --no-superuser --pwprompt --no-createdb artifacts +$ createdb --encoding=UTF-8 --owner artifacts artifactsdb + +For the flys database + +# su - postgres +$ createuser --no-createrole --no-superuser --pwprompt --no-createdb flys +$ createdb --encoding=UTF-8 --owner flys flystest1 diff -r 4a3b2912a0cd -r 324e12ab7a05 flys-backend/doc/schema/postgresql.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-backend/doc/schema/postgresql.sql Wed Mar 09 14:07:15 2011 +0000 @@ -0,0 +1,159 @@ +BEGIN; + +-- Gewaesser +CREATE TABLE rivers ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL UNIQUE +); + +-- Bruecke, Haefen, etc. +CREATE TABLE attributes ( + id int PRIMARY KEY NOT NULL, + value VARCHAR(256) NOT NULL UNIQUE +); + +-- segments from/to at a river +CREATE TABLE ranges ( + id int PRIMARY KEY NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + a NUMERIC NOT NULL, + b NUMERIC, + UNIQUE (river_id, a, b) +); + +-- Lage 'links', 'rechts', etc. +CREATE TABLE positions ( + id int PRIMARY KEY NOT NULL, + value VARCHAR(256) NOT NULL UNIQUE +); + +-- Some object (eg. Hafen) at a segment of river +-- plus its position. +CREATE TABLE annotations ( + id int PRIMARY KEY NOT NULL, + range_id int NOT NULL REFERENCES ranges(id), + attribute_id int NOT NULL REFERENCES attributes(id), + position_id int REFERENCES positions(id) +); + +-- Pegel +CREATE TABLE gauges ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + station NUMERIC NOT NULL UNIQUE, + aeo NUMERIC NOT NULL, + + -- Pegelnullpunkt + datum NUMERIC NOT NULL, + -- Streckengueltigkeit + range_id int NOT NULL REFERENCES ranges (id), + + UNIQUE (name, river_id) +); + +-- Type of a Hauptwert 'W', 'Q', 'D', etc. +CREATE TABLE main_value_types ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL UNIQUE +); + +-- Named type of a Hauptwert (eg. HQ100) +CREATE TABLE named_main_values ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL UNIQUE, + type_id int NOT NULL REFERENCES main_value_types(id), + UNIQUE (name, type_id) +); + +-- Table for time intervals +CREATE TABLE time_intervals ( + id int PRIMARY KEY NOT NULL, + start_time TIMESTAMP NOT NULL, + stop_time TIMESTAMP, + CHECK (start_time <= stop_time) +); + +-- Stammdaten +CREATE TABLE main_values ( + id int PRIMARY KEY NOT NULL, + gauge_id int NOT NULL REFERENCES gauges(id), + named_value_id int NOT NULL REFERENCES named_main_values(id), + value NUMERIC NOT NULL, + + time_interval_id int REFERENCES time_intervals(id), + + -- TODO: better checks + UNIQUE (gauge_id, named_value_id, time_interval_id) +); + +-- Abflusstafeln +CREATE TABLE discharge_table ( + id int PRIMARY KEY NOT NULL, + gauge_id int NOT NULL REFERENCES gauges(id), + + time_interval_id int REFERENCES time_intervals(id), + + -- TODO: better checks + UNIQUE (gauge_id, time_interval_id) +); + +-- Values of the Abflusstafeln +CREATE TABLE discharge_table_values ( + id int PRIMARY KEY NOT NULL, + table_id int NOT NULL REFERENCES discharge_table(id), + q NUMERIC NOT NULL, + w NUMERIC NOT NULL, + + UNIQUE (table_id, q, w) +); + +-- WST files +CREATE TABLE wst ( + id int PRIMARY KEY NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + description VARCHAR(256) NOT NULL, + -- TODO: more meta infos + UNIQUE (river_id, description) +); + +-- columns of WST files +CREATE TABLE wst_column ( + id int PRIMARY KEY NOT NULL, + wst_id int NOT NULL REFERENCES wst(id), + name VARCHAR(256) NOT NULL, + description VARCHAR, + + time_interval_id int REFERENCES time_intervals(id), + + UNIQUE (wst_id, name) +); + +-- w values in WST file column +CREATE TABLE wst_column_values ( + id int PRIMARY KEY NOT NULL, + wst_column_id int NOT NULL REFERENCES wst_column(id), + position NUMERIC NOT NULL, + w NUMERIC NOT NULL, + + UNIQUE (position, wst_column_id), + UNIQUE (position, wst_column_id, w) +); + +-- bind q values to range +CREATE TABLE wst_q_ranges ( + id int PRIMARY KEY NOT NULL, + range_id int NOT NULL REFERENCES ranges(id), + q NUMERIC NOT NULL +); + +-- bind q ranges to wst columns +CREATE TABLE wst_column_q_ranges ( + id int PRIMARY KEY NOT NULL, + wst_column_id int NOT NULL REFERENCES wst_column(id), + wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), + + UNIQUE (wst_column_id, wst_q_range_id) +); + +COMMIT; diff -r 4a3b2912a0cd -r 324e12ab7a05 flys-backend/doc/schema/sqlite.sql --- a/flys-backend/doc/schema/sqlite.sql Wed Mar 09 09:41:01 2011 +0000 +++ b/flys-backend/doc/schema/sqlite.sql Wed Mar 09 14:07:15 2011 +0000 @@ -15,7 +15,7 @@ -- segments from/to at a river CREATE TABLE ranges ( id INTEGER PRIMARY KEY NOT NULL, - river_id INTEGER NOT NULL REFERENCES river(id), + river_id INTEGER NOT NULL REFERENCES rivers(id), a NUMERIC NOT NULL, b NUMERIC, UNIQUE (river_id, a, b) @@ -40,7 +40,7 @@ CREATE TABLE gauges ( id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, - river_id INTEGER NOT NULL REFERENCES river(id), + river_id INTEGER NOT NULL REFERENCES rivers(id), station NUMERIC NOT NULL UNIQUE, aeo NUMERIC NOT NULL, @@ -111,7 +111,7 @@ -- WST files CREATE TABLE wst ( id INTEGER PRIMARY KEY NOT NULL, - river_id INTEGER NOT NULL REFERENCES river(id), + river_id INTEGER NOT NULL REFERENCES rivers(id), description VARCHAR(256) NOT NULL, -- TODO: more meta infos UNIQUE (river_id, description)