Mercurial > dive4elements > river

changeset 170:88c14d5d45be

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added missing sequences. Deleted unsupported sqlite schema. flys-backend/trunk@1460 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 14 Mar 2011 11:31:00 +0000
parents 7929f4144d2f
children 4a83e14f40f9
files flys-backend/ChangeLog flys-backend/doc/schema/postgresql-cleanup.sql flys-backend/doc/schema/postgresql.sql flys-backend/doc/schema/sqlite.sql
diffstat 4 files changed, 55 insertions(+), 142 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Fri Mar 11 16:23:42 2011 +0000
+++ b/flys-backend/ChangeLog	Mon Mar 14 11:31:00 2011 +0000
@@ -1,3 +1,10 @@
+2011-03-14	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql.sql, doc/schema/postgresql-cleanup.sql:
+	  Added missing sequences.
+
+	* doc/schema/sqlite.sql: Deleted. Not longer supported.
+
 2011-03-11	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* doc/schema/postgresql-cleanup.sql: Forgot to add.
--- a/flys-backend/doc/schema/postgresql-cleanup.sql	Fri Mar 11 16:23:42 2011 +0000
+++ b/flys-backend/doc/schema/postgresql-cleanup.sql	Mon Mar 14 11:31:00 2011 +0000
@@ -23,6 +23,22 @@
 
 DROP TABLE rivers CASCADE;
 
+DROP SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
+DROP SEQUENCE WST_Q_RANGES_ID_SEQ;
+DROP SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
+DROP SEQUENCE WST_COLUMN_ID_SEQ;
+DROP SEQUENCE WST_ID_SEQ;
+DROP SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
+DROP SEQUENCE DISCHARGE_TABLES_ID_SEQ;
+DROP SEQUENCE MAIN_VALUES_ID_SEQ;
+DROP SEQUENCE TIME_INTERVALS_ID_SEQ;
+DROP SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
+DROP SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
+DROP SEQUENCE GAUGES_ID_SEQ;
+DROP SEQUENCE ANNOTATIONS_ID_SEQ;
+DROP SEQUENCE POSITIONS_ID_SEQ;
+DROP SEQUENCE RANGES_ID_SEQ;
+DROP SEQUENCE ATTRIBUTES_ID_SEQ;
 DROP SEQUENCE RIVERS_ID_SEQ;
 
 COMMIT;
--- a/flys-backend/doc/schema/postgresql.sql	Fri Mar 11 16:23:42 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Mon Mar 14 11:31:00 2011 +0000
@@ -9,12 +9,16 @@
 );
 
 -- Bruecke, Haefen, etc.
+CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
+
 CREATE TABLE attributes (
     id    int PRIMARY KEY NOT NULL,
     value VARCHAR(256)    NOT NULL UNIQUE
 );
 
 -- segments from/to at a river
+CREATE SEQUENCE RANGES_ID_SEQ;
+
 CREATE TABLE ranges (
     id       int PRIMARY KEY NOT NULL,
     river_id int             NOT NULL REFERENCES rivers(id),
@@ -24,6 +28,8 @@
 );
 
 -- Lage 'links', 'rechts', etc.
+CREATE SEQUENCE POSITIONS_ID_SEQ;
+
 CREATE TABLE positions (
     id    int PRIMARY KEY NOT NULL,
     value VARCHAR(256)    NOT NULL UNIQUE
@@ -31,6 +37,8 @@
 
 -- Some object (eg. Hafen) at a segment of river
 -- plus its position.
+CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
+
 CREATE TABLE annotations (
     id           int PRIMARY KEY NOT NULL,
     range_id     int             NOT NULL REFERENCES ranges(id),
@@ -39,6 +47,8 @@
 );
 
 -- Pegel
+CREATE SEQUENCE GAUGES_ID_SEQ;
+
 CREATE TABLE gauges (
     id       int PRIMARY KEY NOT NULL,
     name     VARCHAR(256)    NOT NULL,
@@ -56,12 +66,16 @@
 );
 
 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
+CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
+
 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 SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
+
 CREATE TABLE named_main_values (
     id      int PRIMARY KEY NOT NULL,
     name    VARCHAR(256)    NOT NULL UNIQUE,
@@ -70,6 +84,8 @@
 );
 
 -- Table for time intervals
+CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
+
 CREATE TABLE time_intervals (
     id         int PRIMARY KEY NOT NULL,
     start_time TIMESTAMP       NOT NULL,
@@ -78,6 +94,8 @@
 );
 
 -- Stammdaten
+CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
+
 CREATE TABLE main_values (
     id             int PRIMARY KEY NOT NULL,
     gauge_id       int NOT NULL REFERENCES gauges(id),
@@ -91,6 +109,8 @@
 );
 
 -- Abflusstafeln
+CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
+
 CREATE TABLE discharge_tables (
     id               int PRIMARY KEY NOT NULL,
     gauge_id         int NOT NULL REFERENCES gauges(id),
@@ -102,6 +122,8 @@
 );
 
 -- Values of the Abflusstafeln
+CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
+
 CREATE TABLE discharge_table_values (
     id       int PRIMARY KEY NOT NULL,
     table_id int NOT NULL REFERENCES discharge_tables(id),
@@ -112,6 +134,8 @@
 );
 
 -- WST files
+CREATE SEQUENCE WST_ID_SEQ;
+
 CREATE TABLE wst (
     id          int PRIMARY KEY NOT NULL,
     river_id    int NOT NULL REFERENCES rivers(id),
@@ -121,6 +145,8 @@
 );
 
 -- columns of WST files
+CREATE SEQUENCE WST_COLUMN_ID_SEQ;
+
 CREATE TABLE wst_column (
     id          int PRIMARY KEY NOT NULL,
     wst_id      int NOT NULL REFERENCES wst(id),
@@ -133,6 +159,8 @@
 );
 
 -- w values in  WST file column
+CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
+
 CREATE TABLE wst_column_values (
     id            int PRIMARY KEY NOT NULL,
     wst_column_id int NOT NULL REFERENCES wst_column(id),
@@ -144,6 +172,8 @@
 );
 
 -- bind q values to range
+CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
+
 CREATE TABLE wst_q_ranges (
     id       int PRIMARY KEY NOT NULL,
     range_id int NOT NULL REFERENCES ranges(id),
@@ -151,6 +181,8 @@
 );
 
 -- bind q ranges to wst columns
+CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
+
 CREATE TABLE wst_column_q_ranges (
     id             int PRIMARY KEY NOT NULL,
     wst_column_id  int NOT NULL REFERENCES wst_column(id),
--- a/flys-backend/doc/schema/sqlite.sql	Fri Mar 11 16:23:42 2011 +0000
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,160 +0,0 @@
-BEGIN TRANSACTION;
-
--- Gewaesser
-CREATE TABLE rivers (
-    id   INTEGER PRIMARY KEY NOT NULL,
-    name VARCHAR(256)        NOT NULL UNIQUE
-);
-
--- Bruecke, Haefen, etc.
-CREATE TABLE attributes (
-    id    INTEGER PRIMARY KEY NOT NULL,
-    value VARCHAR(256)        NOT NULL UNIQUE
-);
-
--- segments from/to at a river
-CREATE TABLE ranges (
-    id       INTEGER PRIMARY KEY NOT NULL,
-    river_id INTEGER NOT NULL REFERENCES rivers(id),
-    a        NUMERIC NOT NULL,
-    b        NUMERIC,
-    UNIQUE (river_id, a, b)
-);
-
--- Lage 'links', 'rechts', etc.
-CREATE TABLE positions (
-    id    INTEGER 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           INTEGER PRIMARY KEY NOT NULL,
-    range_id     INTEGER NOT NULL REFERENCES ranges(id),
-    attribute_id INTEGER NOT NULL REFERENCES attributes(id),
-    position_id  INTEGER REFERENCES positions(id)
-);
-
--- Pegel
-CREATE TABLE gauges (
-    id           INTEGER PRIMARY KEY NOT NULL,
-    name         VARCHAR(256)        NOT NULL,
-    river_id     INTEGER             NOT NULL REFERENCES rivers(id),
-    station      NUMERIC             NOT NULL UNIQUE,
-    aeo          NUMERIC             NOT NULL,
-
-    -- Pegelnullpunkt
-    datum        NUMERIC NOT NULL,
-    -- Streckengueltigkeit
-    range_id     INTEGER NOT NULL REFERENCES ranges (id),
-
-    UNIQUE (name, river_id),
-    UNIQUE (river_id, datum)
-);
-
--- Type of a Hauptwert 'W', 'Q', 'D', etc.
-CREATE TABLE main_value_types (
-    id           INTEGER PRIMARY KEY NOT NULL,
-    name         VARCHAR(256)        NOT NULL UNIQUE
-);
-
---  Named type of a Hauptwert (eg. HQ100)
-CREATE TABLE named_main_values (
-    id           INTEGER PRIMARY KEY NOT NULL,
-    name         VARCHAR(256)        NOT NULL UNIQUE,
-    type_id      INTEGER NOT NULL REFERENCES main_value_types(id),
-    UNIQUE (name, type_id)
-);
-
--- Table for time intervals
-CREATE TABLE time_intervals (
-    id         INTEGER PRIMARY KEY NOT NULL,
-    start_time TIMESTAMP NOT NULL,
-    stop_time  TIMESTAMP,
-    CHECK (start_time <= stop_time)
-);
-
--- Stammdaten
-CREATE TABLE main_values (
-    id               INTEGER PRIMARY KEY NOT NULL,
-    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
-    named_value_id   INTEGER NOT NULL REFERENCES named_main_values(id),
-    value            NUMERIC NOT NULL,
-
-    time_interval_id INTEGER REFERENCES time_intervals(id),
-
-    -- TODO: better checks
-    UNIQUE (gauge_id, named_value_id, time_interval_id)
-);
-
--- Abflusstafeln
-CREATE TABLE discharge_tables (
-    id               INTEGER PRIMARY KEY NOT NULL,
-    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
-
-    time_interval_id INTEGER REFERENCES time_intervals(id),
-
-    -- TODO: better checks
-    UNIQUE (gauge_id, time_interval_id)
-);
-
--- Values of the Abflusstafeln
-CREATE TABLE discharge_table_values (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    table_id       INTEGER NOT NULL REFERENCES discharge_tables(id),
-    q              NUMERIC NOT NULL,
-    w              NUMERIC NOT NULL,
-
-    UNIQUE (table_id, q, w)
-);
-
--- WST files
-CREATE TABLE wst (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    river_id       INTEGER 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             INTEGER PRIMARY KEY NOT NULL,
-    wst_id         INTEGER NOT NULL REFERENCES wst(id),
-    name           VARCHAR(256) NOT NULL,
-    description    VARCHAR,
-
-    time_interval_id INTEGER REFERENCES time_intervals(id),
-
-    UNIQUE (wst_id, name)
-);
-
--- w values in  WST file column
-CREATE TABLE wst_column_values (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    wst_column_id  INTEGER 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             INTEGER PRIMARY KEY NOT NULL,
-    range_id       INTEGER NOT NULL REFERENCES ranges(id),
-    q              NUMERIC NOT NULL
-);
-
--- bind q ranges to wst columns
-CREATE TABLE wst_column_q_ranges (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    wst_column_id  INTEGER NOT NULL REFERENCES wst_column(id),
-    wst_q_range_id INTEGER NOT NULL REFERENCES wst_q_ranges(id),
-
-    UNIQUE (wst_column_id, wst_q_range_id)
-);
-
-END TRANSACTION;
http://dive4elements.wald.intevation.org