--- a/flys-backend/doc/schema/postgresql.sql	Thu Mar 10 13:44:51 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Fri Mar 11 14:41:18 2011 +0000
@@ -1,22 +1,24 @@
 BEGIN;
 
 -- Gewaesser
+CREATE SEQUENCE RIVERS_ID_SEQ;
+
 CREATE TABLE rivers (
     id   int PRIMARY KEY NOT NULL,
-    name VARCHAR(256)        NOT NULL UNIQUE
+    name VARCHAR(256)    NOT NULL UNIQUE
 );
 
 -- Bruecke, Haefen, etc.
 CREATE TABLE attributes (
     id    int PRIMARY KEY NOT NULL,
-    value VARCHAR(256)        NOT NULL UNIQUE
+    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,
+    river_id int             NOT NULL REFERENCES rivers(id),
+    a        NUMERIC         NOT NULL,
     b        NUMERIC,
     UNIQUE (river_id, a, b)
 );
@@ -24,30 +26,30 @@
 -- Lage 'links', 'rechts', etc.
 CREATE TABLE positions (
     id    int PRIMARY KEY NOT NULL,
-    value VARCHAR(256)        NOT NULL UNIQUE
+    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),
+    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,
+    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,
+    datum    NUMERIC NOT NULL,
     -- Streckengueltigkeit
-    range_id     int NOT NULL REFERENCES ranges (id),
+    range_id int NOT NULL REFERENCES ranges (id),
 
     UNIQUE (name, river_id),
     UNIQUE (river_id, datum)
@@ -55,32 +57,32 @@
 
 -- 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
+    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),
+    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,
+    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,
+    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),
 
@@ -101,29 +103,29 @@
 
 -- Values of the Abflusstafeln
 CREATE TABLE discharge_table_values (
-    id             int PRIMARY KEY NOT NULL,
-    table_id       int NOT NULL REFERENCES discharge_tables(id),
-    q              NUMERIC NOT NULL,
-    w              NUMERIC NOT NULL,
+    id       int PRIMARY KEY NOT NULL,
+    table_id int 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             int PRIMARY KEY NOT NULL,
-    river_id       int NOT NULL REFERENCES rivers(id),
-    description    VARCHAR(256) NOT NULL,
+    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,
+    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),
 
@@ -132,10 +134,10 @@
 
 -- 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,
+    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)
@@ -143,9 +145,9 @@
 
 -- 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
+    id       int PRIMARY KEY NOT NULL,
+    range_id int NOT NULL REFERENCES ranges(id),
+    q        NUMERIC NOT NULL
 );
 
 -- bind q ranges to wst columns