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