changeset 164:324e12ab7a05

Converted schema to be PostgreSQL compatible. flys-backend/trunk@1441 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Mar 2011 14:07:15 +0000
parents 4a3b2912a0cd
children a69b8494fcac
files flys-backend/ChangeLog flys-backend/README flys-backend/doc/schema/postgresql.sql flys-backend/doc/schema/sqlite.sql
diffstat 4 files changed, 180 insertions(+), 3 deletions(-) [+]
line wrap: on
line diff
--- 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	<sascha.teichmann@intevation.de>
+
+	* 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	<sascha.teichmann@intevation.de>
 
 	* doc/schema/sqlite.sql: Factorized time intervals out into
--- /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
--- /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;
--- 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)

http://dive4elements.wald.intevation.org