dive4elements/river: flys-backend/doc/schema/postgresql.sql annotate

annotate flys-backend/doc/schema/postgresql.sql @ 492:14de9246b356

Importer: parse historical discharge tables, too. TODO: store them in database. flys-backend/trunk@1840 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Thu, 05 May 2011 15:25:16 +0000
parents	77f337650211
children	b35c5dc0f8b7

rev	line source
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 BEGIN;
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 -- Gewaesser
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	4 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	5
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 CREATE TABLE rivers (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	8 name VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11 -- Bruecke, Haefen, etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	12 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	13
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14 CREATE TABLE attributes (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	16 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 -- segments from/to at a river
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	20 CREATE SEQUENCE RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	21
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	22 CREATE TABLE ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	23 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	24 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	25 a NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 b NUMERIC,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 UNIQUE (river_id, a, b)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 -- Lage 'links', 'rechts', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	31 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	32
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	34 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	35 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	38 -- Some object (eg. Hafen) at a segment of river
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39 -- plus its position.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	40 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	41
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	42 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	44 range_id int NOT NULL REFERENCES ranges(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	45 attribute_id int NOT NULL REFERENCES attributes(id),
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 position_id int REFERENCES positions(id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	47 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	48
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	49 -- Pegel
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	50 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	51
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	52 CREATE TABLE gauges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	53 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	54 name VARCHAR(256) NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	55 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	56 station NUMERIC NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	57 aeo NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	58
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	59 -- Pegelnullpunkt
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	60 datum NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	61 -- Streckengueltigkeit
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	62 range_id int NOT NULL REFERENCES ranges (id),
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	63
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	64 UNIQUE (name, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	65 UNIQUE (river_id, station)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	66 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	67
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	68 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	69 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	70
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 CREATE TABLE main_value_types (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	72 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	73 name VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	74 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	75
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	76 -- Named type of a Hauptwert (eg. HQ100)
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	77 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	78
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79 CREATE TABLE named_main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	80 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	81 name VARCHAR(256) NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	82 type_id int NOT NULL REFERENCES main_value_types(id),
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	83 UNIQUE (name, type_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	84 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	85
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	86 -- Table for time intervals
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	87 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	88
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	89 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	90 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	91 start_time TIMESTAMP NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	92 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	95
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	96 -- Stammdaten
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	97 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	98
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	99 CREATE TABLE main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	100 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	101 gauge_id int NOT NULL REFERENCES gauges(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	102 named_value_id int NOT NULL REFERENCES named_main_values(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	103 value NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	104
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105 time_interval_id int REFERENCES time_intervals(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	106
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	107 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	108 UNIQUE (gauge_id, named_value_id, time_interval_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	110
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	111 -- Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	112 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	113
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	114 CREATE TABLE discharge_tables (
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	115 id int PRIMARY KEY NOT NULL,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 gauge_id int NOT NULL REFERENCES gauges(id),
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	117 kind int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118 time_interval_id int REFERENCES time_intervals(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	120 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 UNIQUE (gauge_id, time_interval_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	125 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	126
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	127 CREATE TABLE discharge_table_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	128 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	129 table_id int NOT NULL REFERENCES discharge_tables(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	130 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	131 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	132
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	133 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	134 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	135
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	136 -- WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	137 CREATE SEQUENCE WSTS_ID_SEQ;
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	138
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	139 CREATE TABLE wsts (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	140 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	141 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	142 description VARCHAR(256) NOT NULL,
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	143 kind int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	145 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	146 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	147
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	148 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	149 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	150
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	151 CREATE TABLE wst_columns (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	152 id int PRIMARY KEY NOT NULL,
173 8e2f2ddc507f Fixed wrong spelled column references in foreign keys introduces with last change. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 171 diff changeset	153 wst_id int NOT NULL REFERENCES wsts(id),
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	154 name VARCHAR(256) NOT NULL,
174 249390dd24e7 Added foreign key constraint annotations to model classes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	155 description VARCHAR(256),
471 3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	156 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	158 time_interval_id int REFERENCES time_intervals(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	159
471 3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	160 UNIQUE (wst_id, name),
3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	161 UNIQUE (wst_id, position)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	162 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	163
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	164 -- w values in WST file column
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	165 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	166
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167 CREATE TABLE wst_column_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	168 id int PRIMARY KEY NOT NULL,
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	169 wst_column_id int NOT NULL REFERENCES wst_columns(id),
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	170 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	171 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	172
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	173 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	174 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	175 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	176
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	177 -- bind q values to range
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	178 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	179
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	180 CREATE TABLE wst_q_ranges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	181 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	182 range_id int NOT NULL REFERENCES ranges(id),
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	183 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	184 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	185
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	186 -- bind q ranges to wst columns
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	187 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	188
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	189 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	190 id int PRIMARY KEY NOT NULL,
173 8e2f2ddc507f Fixed wrong spelled column references in foreign keys introduces with last change. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 171 diff changeset	191 wst_column_id int NOT NULL REFERENCES wst_columns(id),
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	192 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	193
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 UNIQUE (wst_column_id, wst_q_range_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	195 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	196
476 77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	197 CREATE VIEW wst_value_table AS
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	198 SELECT wcv.position AS position,
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	199 w,
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	200 (SELECT q
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	201 FROM wst_column_q_ranges wcqr
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	202 JOIN wst_q_ranges wqr
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	203 ON wcqr.wst_q_range_id = wqr.id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	204 JOIN ranges r
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	205 ON r.id = wqr.range_id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	206 WHERE wcqr.wst_column_id = wc.id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	207 AND wcv.position BETWEEN r.a AND r.b) AS q,
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	208 wc.position AS column_pos,
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	209 w.id AS wst_id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	210 FROM wst_column_values wcv
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	211 JOIN wst_columns wc
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	212 ON wcv.wst_column_id = wc.id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	213 JOIN wsts w
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	214 ON wc.wst_id = w.id
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	215 ORDER BY wcv.position ASC,
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	216 wc.position DESC;
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 471 diff changeset	217
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	218 COMMIT;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql.sql @ 492:14de9246b356