# HG changeset patch # User Sascha L. Teichmann # Date 1300102260 0 # Node ID 88c14d5d45be6e4a4ce8f297260e5302aa4c3fc8 # Parent 7929f4144d2fa9f96caa2befbc40e8abaaf7428c Added missing sequences. Deleted unsupported sqlite schema. flys-backend/trunk@1460 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 7929f4144d2f -r 88c14d5d45be flys-backend/ChangeLog --- 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 + + * 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 * doc/schema/postgresql-cleanup.sql: Forgot to add. diff -r 7929f4144d2f -r 88c14d5d45be flys-backend/doc/schema/postgresql-cleanup.sql --- 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; diff -r 7929f4144d2f -r 88c14d5d45be flys-backend/doc/schema/postgresql.sql --- 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), diff -r 7929f4144d2f -r 88c14d5d45be flys-backend/doc/schema/sqlite.sql --- 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;