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

annotate flys-backend/doc/schema/postgresql.sql @ 5444:70e0732bd4dd

WaterlevelFacet: Avoid classcastexceptions.

author	Felix Wolfsteller <felix.wolfsteller@intevation.de>
date	Wed, 27 Mar 2013 09:49:04 +0100
parents	6dd354e7abfc
children	88cbe798cbab

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
2347 0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	3 CREATE SEQUENCE UNITS_ID_SEQ;
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	4
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	5 CREATE TABLE units (
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	6 id int PRIMARY KEY NOT NULL,
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	7 name VARCHAR(32) NOT NULL UNIQUE
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	8 );
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2346 diff changeset	9
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10 -- Gewaesser
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	11 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	12
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	13 CREATE TABLE rivers (
3946 948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3796 diff changeset	14 id int PRIMARY KEY NOT NULL,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3796 diff changeset	15 official_number int8 UNIQUE,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3796 diff changeset	16 name VARCHAR(256) NOT NULL UNIQUE,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3796 diff changeset	17 km_up BOOLEAN NOT NULL DEFAULT true,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3796 diff changeset	18 wst_unit_id int NOT NULL REFERENCES units(id)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	21 -- Bruecke, Haefen, etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	22 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	23
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	24 CREATE TABLE attributes (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	25 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	26 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 );
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 -- 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	30 CREATE SEQUENCE RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	31
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 CREATE TABLE ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	34 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	35 a NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 b NUMERIC,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37 UNIQUE (river_id, a, b)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	38 );
5297 4f3cc1aebcc0 trigger sequence of range IDs in DB as it is not used by hibernate only anymore Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	39
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41 -- Lage 'links', 'rechts', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	42 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	43
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	45 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	46 value VARCHAR(256) NOT NULL UNIQUE
164 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
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	49 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	50 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	51
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	52 CREATE TABLE edges (
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	53 id int PRIMARY KEY NOT NULL,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	54 top NUMERIC,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	55 bottom NUMERIC
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	56 );
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	57
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	58 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	59 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	60
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	61 CREATE TABLE annotation_types (
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	62 id int PRIMARY KEY NOT NULL,
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	63 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	64 );
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	65
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	66 -- 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	67 -- plus its position.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	68 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	69
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	70 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	72 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	73 attribute_id int NOT NULL REFERENCES attributes(id),
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	74 position_id int REFERENCES positions(id),
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	75 edge_id int REFERENCES edges(id),
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	76 type_id int REFERENCES annotation_types(id)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	77 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	78
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79 -- Pegel
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	80 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	81
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	82 CREATE TABLE gauges (
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	83 id int PRIMARY KEY NOT NULL,
fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	84 name VARCHAR(256) NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	85 -- remove river id here because range_id references river already
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	86 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	87 station NUMERIC NOT NULL,
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	88 aeo NUMERIC NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	89 official_number int8 UNIQUE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	90
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91 -- Pegelnullpunkt
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	92 datum NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93 -- Streckengueltigkeit
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	94 range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	95
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	96 UNIQUE (name, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	97 UNIQUE (river_id, station)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	98 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	99
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	100 -- 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	101 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	102
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	103 CREATE TABLE main_value_types (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	104 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	105 name VARCHAR(256) NOT NULL UNIQUE
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	108 -- 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	109 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	110
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	111 CREATE TABLE named_main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	112 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	113 name VARCHAR(256) NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	114 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	115 UNIQUE (name, type_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	117
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118 -- Table for time intervals
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	119 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	120
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	123 start_time TIMESTAMP NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 );
5298 eaa630838705 trigger sequence of time_interval IDs in DB as it is not used by hibernate only anymore Tom Gottfried <tom@intevation.de> parents: 5297 diff changeset	127
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	128
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	129 -- Stammdaten
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	130 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	131
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	132 CREATE TABLE main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	133 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	134 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	135 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	136 value NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138 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	139
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	140 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 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	142 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	143
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144 -- Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	145 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	146
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	147 CREATE TABLE discharge_tables (
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	148 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	149 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
493 b35c5dc0f8b7 Importer: Make import of historical discharge tables work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 476 diff changeset	150 description VARCHAR(256) NOT NULL,
4776 20b6ebf23916 !!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables. Sascha L. Teichmann <teichmann@intevation.de> parents: 4651 diff changeset	151 bfg_id VARCHAR(50),
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	152 kind int NOT NULL DEFAULT 0,
496 4f2d8980415f Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 493 diff changeset	153 time_interval_id int REFERENCES time_intervals(id)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	154
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	155 -- TODO: better checks
496 4f2d8980415f Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 493 diff changeset	156 -- UNIQUE (gauge_id, kind, time_interval_id)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	159 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	160 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	161
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	162 CREATE TABLE discharge_table_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	163 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	164 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	165 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	166 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	168 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	169 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	170
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	171 -- WST files
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	172 --lookup table for wst kinds
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	173 CREATE TABLE wst_kinds (
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	174 id int PRIMARY KEY NOT NULL,
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	175 kind VARCHAR(64) NOT NULL
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	176 );
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	177 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	178 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	179 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	180 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	181 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	182 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	183 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	184 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	185
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	186
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	187 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	188
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	189 CREATE TABLE wsts (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	190 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	191 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	192 description VARCHAR(256) NOT NULL,
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	193 kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	195 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	196 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	197
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	198 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	199 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	200
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	201 CREATE TABLE wst_columns (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	202 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	203 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	204 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	205 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	206 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	207
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	208 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	209
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	210 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	211 UNIQUE (wst_id, position)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	212 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	213
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	214 -- 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	215 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	216
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	217 CREATE TABLE wst_column_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	218 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	219 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	220 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	221 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	222
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	223 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	224 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	225 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	226
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	227 -- 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	228 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	229
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	230 CREATE TABLE wst_q_ranges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	231 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	232 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	233 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	234 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	235
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	236 -- 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	237 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	238
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	239 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	240 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	241 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	242 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	243
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	244 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	245 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	246
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	247 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	248 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	249 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	250 (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	251 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	252 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	253 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	254 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	255 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	256 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	257 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	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 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	266 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	267
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	268 -- view to select the w values of a WST
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	269 CREATE VIEW wst_w_values AS
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	270 SELECT wcv."position" AS km,
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	271 wcv.w AS w,
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	272 wc."position" AS column_pos,
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	273 w.id AS wst_id
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	274 FROM wst_column_values wcv
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	275 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	276 JOIN wsts w ON wc.wst_id = w.id
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	277 ORDER BY wcv."position", wc."position";
5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	278
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	279 -- view to select the q values of a WST
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	280 CREATE VIEW wst_q_values AS
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	281 SELECT wc.position AS column_pos,
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	282 wqr.q AS q,
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	283 r.a AS a,
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	284 r.b AS b,
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	285 wc.wst_id AS wst_id
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	286 FROM wst_column_q_ranges wcqr
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	287 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	288 JOIN ranges r ON wqr.range_id = r.id
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	289 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	290 ORDER BY wc.position, wcqr.wst_column_id, r.a;
a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	291
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	292 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	293
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	294 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	295
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	296 CREATE TABLE cross_sections (
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	297 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	298 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
1203 3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	299 time_interval_id int REFERENCES time_intervals(id),
1202 44581b40b968 Schema: Added description column to the cross section table Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1201 diff changeset	300 description VARCHAR(256)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	301 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	302
1205 5f1506fc7636 Made import of cross sections work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1203 diff changeset	303 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
1203 3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	304
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	305 CREATE TABLE cross_section_lines (
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	306 id int PRIMARY KEY NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	307 km NUMERIC NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	308 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
1203 3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	309 UNIQUE (km, cross_section_id)
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	310 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	311
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	312 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	313
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	314 CREATE TABLE cross_section_points (
1203 3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	315 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	316 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
1203 3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	317 col_pos int NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	318 x NUMERIC NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	319 y NUMERIC NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	320 UNIQUE (cross_section_line_id, col_pos)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	321 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	322
3796 61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	323 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	324 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	325 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	326 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	327 ON cross_section_points(cross_section_line_id);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	328
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	329 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	330
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	331 CREATE SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	332
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	333 CREATE TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	334 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	335 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	336 description VARCHAR(256) NOT NULL
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	337 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	338
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	339 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	340
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	341 CREATE TABLE hyk_entries (
1212 dd948530fd57 Schema: Added missing columns in HYK tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1209 diff changeset	342 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	343 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
1212 dd948530fd57 Schema: Added missing columns in HYK tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1209 diff changeset	344 km NUMERIC NOT NULL,
1217 d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1214 diff changeset	345 measure TIMESTAMP,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	346 UNIQUE (hyk_id, km)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	347 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	348
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	349 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	350
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	351 CREATE TABLE hyk_formations (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	352 id int PRIMARY KEY NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	353 formation_num int NOT NULL DEFAULT 0,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	354 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	355 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	356 bottom NUMERIC NOT NULL,
1214 32ee9babe42c Schema: Argh! Added distance_{vl\|hf\|vr} to Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1212 diff changeset	357 distance_vl NUMERIC NOT NULL,
32ee9babe42c Schema: Argh! Added distance_{vl\|hf\|vr} to Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1212 diff changeset	358 distance_hf NUMERIC NOT NULL,
32ee9babe42c Schema: Argh! Added distance_{vl\|hf\|vr} to Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1212 diff changeset	359 distance_vr NUMERIC NOT NULL,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	360 UNIQUE (hyk_entry_id, formation_num)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	361 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	362
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	363 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	364
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	365 CREATE TABLE hyk_flow_zone_types (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	366 id int PRIMARY KEY NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	367 name VARCHAR(50) NOT NULL UNIQUE,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	368 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	369 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	370
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	371 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	372
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	373 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	374 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	375 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	376 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	377 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	378 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	379 CHECK (a <= b)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	380 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	381
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	382 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	383 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	384 SELECT w.river_id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	385 w.id AS wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	386 wc.id AS wst_column_id,
3470 3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	387 wc.name AS name,
3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	388 wc.position AS wst_column_pos
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	389 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	390 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	391 ON wc.wst_id = w.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	392 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	393
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	394 CREATE VIEW q_main_values
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	395 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	396 SELECT riv.id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	397 g.id AS gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	398 g.name AS gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	399 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	400 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	401 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	402 nmv.name, E'[:space:]\\(.\\)[:space:]*', '') AS name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	403 CAST(mv.value AS NUMERIC(38, 2)) AS value
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	404 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	405 JOIN named_main_values nmv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	406 ON mv.named_value_id = nmv.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	407 JOIN main_value_types mvt
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	408 ON nmv.type_id = mvt.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	409 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	410 ON mv.gauge_id = g.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	411 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	412 ON g.range_id = r.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	413 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	414 ON g.river_id = riv.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	415 WHERE mvt.name = 'Q'
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	416 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2));
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	417
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	418 CREATE VIEW official_q_values
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	419 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	420 SELECT ol.river_id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	421 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	422 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	423 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	424 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	425 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	426 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	427 ol.name,
3470 3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	428 value,
3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	429 wst_column_pos
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	430 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	431 JOIN q_main_values qmv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	432 ON ol.river_id = qmv.river_id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	433 AND ol.name = qmv.name;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	434
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	435 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	436 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	437 SELECT wc.id AS wst_column_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	438 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	439 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	440 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	441 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	442 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	443 ON wc.id = wcv.wst_column_id
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	444 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	445 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	446
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	447 COMMIT;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql.sql @ 5444:70e0732bd4dd