
sascha@161: BEGIN TRANSACTION;
sascha@161: 
sascha@161: -- Gewaesser
sascha@161: CREATE TABLE rivers (
sascha@161:     id   INTEGER PRIMARY KEY NOT NULL,
sascha@161:     name VARCHAR(256)        NOT NULL UNIQUE
sascha@161: );
sascha@161: 
sascha@161: -- Bruecke, Haefen, etc.
sascha@161: CREATE TABLE attributes (
sascha@161:     id    INTEGER PRIMARY KEY NOT NULL,
sascha@161:     value VARCHAR(256)        NOT NULL UNIQUE
sascha@161: );
sascha@161: 
sascha@161: -- segments from/to at a river
sascha@161: CREATE TABLE ranges (
sascha@161:     id       INTEGER PRIMARY KEY NOT NULL,
sascha@164:     river_id INTEGER NOT NULL REFERENCES rivers(id),
sascha@161:     a        NUMERIC NOT NULL,
sascha@161:     b        NUMERIC,
sascha@161:     UNIQUE (river_id, a, b)
sascha@161: );
sascha@161: 
sascha@161: -- Lage 'links', 'rechts', etc.
sascha@161: CREATE TABLE positions (
sascha@161:     id    INTEGER PRIMARY KEY NOT NULL,
sascha@161:     value VARCHAR(256)        NOT NULL UNIQUE
sascha@161: );
sascha@161: 
sascha@161: -- Some object (eg. Hafen) at a segment of river
sascha@161: -- plus its position.
sascha@161: CREATE TABLE annotations (
sascha@161:     id           INTEGER PRIMARY KEY NOT NULL,
sascha@161:     range_id     INTEGER NOT NULL REFERENCES ranges(id),
sascha@161:     attribute_id INTEGER NOT NULL REFERENCES attributes(id),
sascha@161:     position_id  INTEGER REFERENCES positions(id)
sascha@161: );
sascha@161: 
sascha@161: -- Pegel
sascha@161: CREATE TABLE gauges (
sascha@161:     id           INTEGER PRIMARY KEY NOT NULL,
sascha@161:     name         VARCHAR(256)        NOT NULL,
sascha@164:     river_id     INTEGER             NOT NULL REFERENCES rivers(id),
sascha@161:     station      NUMERIC             NOT NULL UNIQUE,
sascha@161:     aeo          NUMERIC             NOT NULL,
sascha@161: 
sascha@161:     -- Pegelnullpunkt
sascha@161:     datum        NUMERIC NOT NULL,
sascha@161:     -- Streckengueltigkeit
sascha@161:     range_id     INTEGER NOT NULL REFERENCES ranges (id),
sascha@161: 
sascha@167:     UNIQUE (name, river_id),
sascha@167:     UNIQUE (river_id, datum)
sascha@161: );
sascha@161: 
sascha@161: -- Type of a Hauptwert 'W', 'Q', 'D', etc.
sascha@161: CREATE TABLE main_value_types (
sascha@161:     id           INTEGER PRIMARY KEY NOT NULL,
sascha@161:     name         VARCHAR(256)        NOT NULL UNIQUE
sascha@161: );
sascha@161: 
sascha@161: --  Named type of a Hauptwert (eg. HQ100)
sascha@161: CREATE TABLE named_main_values (
sascha@161:     id           INTEGER PRIMARY KEY NOT NULL,
sascha@161:     name         VARCHAR(256)        NOT NULL UNIQUE,
sascha@161:     type_id      INTEGER NOT NULL REFERENCES main_value_types(id),
sascha@161:     UNIQUE (name, type_id)
sascha@161: );
sascha@161: 
sascha@163: -- Table for time intervals
sascha@163: CREATE TABLE time_intervals (
sascha@163:     id         INTEGER PRIMARY KEY NOT NULL,
sascha@163:     start_time TIMESTAMP NOT NULL,
sascha@163:     stop_time  TIMESTAMP,
sascha@163:     CHECK (start_time <= stop_time)
sascha@163: );
sascha@163: 
sascha@161: -- Stammdaten
sascha@161: CREATE TABLE main_values (
sascha@163:     id               INTEGER PRIMARY KEY NOT NULL,
sascha@163:     gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
sascha@163:     named_value_id   INTEGER NOT NULL REFERENCES named_main_values(id),
sascha@163:     value            NUMERIC NOT NULL,
sascha@161: 
sascha@163:     time_interval_id INTEGER REFERENCES time_intervals(id),
sascha@161: 
sascha@161:     -- TODO: better checks
sascha@163:     UNIQUE (gauge_id, named_value_id, time_interval_id)
sascha@161: );
sascha@161: 
sascha@161: -- Abflusstafeln
sascha@167: CREATE TABLE discharge_tables (
sascha@163:     id               INTEGER PRIMARY KEY NOT NULL,
sascha@163:     gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
sascha@161: 
sascha@163:     time_interval_id INTEGER REFERENCES time_intervals(id),
sascha@161: 
sascha@161:     -- TODO: better checks
sascha@163:     UNIQUE (gauge_id, time_interval_id)
sascha@161: );
sascha@161: 
sascha@161: -- Values of the Abflusstafeln
sascha@161: CREATE TABLE discharge_table_values (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@167:     table_id       INTEGER NOT NULL REFERENCES discharge_tables(id),
sascha@161:     q              NUMERIC NOT NULL,
sascha@161:     w              NUMERIC NOT NULL,
sascha@161: 
sascha@161:     UNIQUE (table_id, q, w)
sascha@161: );
sascha@161: 
sascha@161: -- WST files
sascha@161: CREATE TABLE wst (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@164:     river_id       INTEGER NOT NULL REFERENCES rivers(id),
sascha@161:     description    VARCHAR(256) NOT NULL,
sascha@161:     -- TODO: more meta infos
sascha@161:     UNIQUE (river_id, description)
sascha@161: );
sascha@161: 
sascha@161: -- columns of WST files
sascha@161: CREATE TABLE wst_column (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@161:     wst_id         INTEGER NOT NULL REFERENCES wst(id),
sascha@161:     name           VARCHAR(256) NOT NULL,
sascha@161:     description    VARCHAR,
sascha@161: 
sascha@163:     time_interval_id INTEGER REFERENCES time_intervals(id),
sascha@161: 
sascha@161:     UNIQUE (wst_id, name)
sascha@161: );
sascha@161: 
sascha@161: -- w values in  WST file column
sascha@161: CREATE TABLE wst_column_values (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@161:     wst_column_id  INTEGER NOT NULL REFERENCES wst_column(id),
sascha@161:     position       NUMERIC NOT NULL,
sascha@161:     w              NUMERIC NOT NULL,
sascha@161: 
sascha@161:     UNIQUE (position, wst_column_id),
sascha@161:     UNIQUE (position, wst_column_id, w)
sascha@161: );
sascha@161: 
sascha@161: -- bind q values to range
sascha@161: CREATE TABLE wst_q_ranges (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@161:     range_id       INTEGER NOT NULL REFERENCES ranges(id),
sascha@161:     q              NUMERIC NOT NULL
sascha@161: );
sascha@161: 
sascha@161: -- bind q ranges to wst columns
sascha@161: CREATE TABLE wst_column_q_ranges (
sascha@161:     id             INTEGER PRIMARY KEY NOT NULL,
sascha@161:     wst_column_id  INTEGER NOT NULL REFERENCES wst_column(id),
sascha@161:     wst_q_range_id INTEGER NOT NULL REFERENCES wst_q_ranges(id),
sascha@161: 
sascha@161:     UNIQUE (wst_column_id, wst_q_range_id)
sascha@161: );
sascha@161: 
sascha@161: END TRANSACTION;




