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

annotate flys-backend/doc/schema/postgresql.sql @ 5200:42bb6ff78d1b 2.9.11

Directly set the connectionInitSqls on the datasource Somehow the factory fails to set the connectionInitSqls if we add it to the dbcpProperties. So we now set it directly

author	Andre Heinecke <aheinecke@intevation.de>
date	Fri, 08 Mar 2013 11:48:33 +0100
parents	b79eb203032d
children	646c154477fe

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 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40 -- Lage 'links', 'rechts', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	41 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	42
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	45 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	47
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	48 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	49 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	50
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	51 CREATE TABLE edges (
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	52 id int PRIMARY KEY NOT NULL,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	53 top NUMERIC,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	54 bottom NUMERIC
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	55 );
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	56
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	57 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	58 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	59
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	60 CREATE TABLE annotation_types (
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	61 id int PRIMARY KEY NOT NULL,
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	62 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	63 );
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	64
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	65 -- 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	66 -- plus its position.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	68
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	69 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	70 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	71 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	72 attribute_id int NOT NULL REFERENCES attributes(id),
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	73 position_id int REFERENCES positions(id),
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	74 edge_id int REFERENCES edges(id),
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	75 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	76 );
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 -- Pegel
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	79 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	80
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	81 CREATE TABLE gauges (
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	82 id int PRIMARY KEY NOT NULL,
fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	83 name VARCHAR(256) 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	84 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	85 station NUMERIC NOT NULL UNIQUE,
fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	86 aeo NUMERIC NOT NULL,
fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	87 official_number int8 UNIQUE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	88
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	89 -- Pegelnullpunkt
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	90 datum NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91 -- Streckengueltigkeit
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	92 range_id int REFERENCES ranges (id) ON DELETE CASCADE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	94 UNIQUE (name, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	95 UNIQUE (river_id, station)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	96 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	97
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	98 -- 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	99 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	100
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	101 CREATE TABLE main_value_types (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	102 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	103 name VARCHAR(256) NOT NULL UNIQUE
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	106 -- 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	107 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	108
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 CREATE TABLE named_main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	110 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	111 name VARCHAR(256) NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	112 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	113 UNIQUE (name, type_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	114 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	115
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 -- Table for time intervals
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	117 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	118
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	120 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	121 start_time TIMESTAMP NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 -- Stammdaten
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	127 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	128
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	129 CREATE TABLE main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	130 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	131 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	132 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	133 value NUMERIC NOT NULL,
164 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 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	136
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138 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	139 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	140
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 -- Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	142 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	143
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	144 CREATE TABLE discharge_tables (
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	145 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	146 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	147 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	148 bfg_id VARCHAR(50),
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	149 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	150 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	151
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	152 -- 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	153 -- 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	154 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	155
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	156 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	157 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	158
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	159 CREATE TABLE discharge_table_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	160 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	161 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	162 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	163 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	164
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	165 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	166 );
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 -- WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	169 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	170
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	171 CREATE TABLE wsts (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	172 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	173 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	174 description VARCHAR(256) NOT NULL,
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	175 kind int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	176 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	177 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	178 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	179
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	180 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	181 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	182
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	183 CREATE TABLE wst_columns (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	184 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	185 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	186 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	187 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	188 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	189
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	190 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	191
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	192 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	193 UNIQUE (wst_id, position)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 );
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 -- 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	197 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	198
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	199 CREATE TABLE wst_column_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	200 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	201 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	202 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	203 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	204
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	205 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	206 UNIQUE (position, wst_column_id, w)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	209 -- 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	210 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	211
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	212 CREATE TABLE wst_q_ranges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	213 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	214 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	215 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	216 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	217
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	218 -- 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	219 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	220
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	221 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	222 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	223 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	224 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	225
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	226 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	227 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	228
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	229 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	230 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	231 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	232 (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	233 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	234 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	235 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	236 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	237 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	238 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	239 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	240 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	241 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	242 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	243 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	244 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	245 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	246 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	247 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	248 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	249
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	250 -- 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	251 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	252 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	253 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	254 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	255 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	256 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	257 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	258 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	259 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	260
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	261 -- 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	262 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	263 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	264 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	265 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	266 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	267 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	268 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	269 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	270 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	271 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	272 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	273
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	274 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	275
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	276 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	277
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	278 CREATE TABLE cross_sections (
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	279 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	280 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	281 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	282 description VARCHAR(256)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	283 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	284
1205 5f1506fc7636 Made import of cross sections work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1203 diff changeset	285 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	286
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	287 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	288 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	289 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	290 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	291 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	292 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	293
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	294 CREATE SEQUENCE CROSS_SECTION_POINTS_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_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	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 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	299 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	300 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	301 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	302 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	303 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	304
3796 61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	305 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	306 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	307 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	308 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	309 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	310
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	311 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	312
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	313 CREATE SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	314
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	315 CREATE TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	316 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	317 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	318 description VARCHAR(256) NOT NULL
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	319 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	320
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	321 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	322
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	323 CREATE TABLE hyk_entries (
1212 dd948530fd57 Schema: Added missing columns in HYK tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1209 diff changeset	324 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	325 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	326 km NUMERIC NOT NULL,
1217 d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1214 diff changeset	327 measure TIMESTAMP,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	328 UNIQUE (hyk_id, km)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	329 );
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 HYK_FORMATIONS_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 hyk_formations (
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,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	335 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	336 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	337 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	338 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	339 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	340 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	341 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	342 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	343 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	344
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	345 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	346
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	347 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	348 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	349 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	350 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	351 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	352
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	353 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	354
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	355 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	356 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	357 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	358 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	359 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	360 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	361 CHECK (a <= b)
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
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	364 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	365 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	366 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	367 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	368 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	369 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	370 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	371 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	372 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	373 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	374 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	375
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	376 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	377 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	378 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	379 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	380 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	381 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	382 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	383 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	384 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	385 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	386 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	387 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	388 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	389 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	390 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	391 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	392 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	393 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	394 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	395 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	396 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	397 WHERE mvt.name = 'Q'
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	398 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	399
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	400 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	401 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	402 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	403 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	404 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	405 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	406 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	407 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	408 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	409 ol.name,
3470 3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	410 value,
3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	411 wst_column_pos
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	412 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2373 diff changeset	413 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	414 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	415 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	416
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	417 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	418 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	419 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	420 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	421 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	422 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	423 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	424 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	425 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	426 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	427 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	428
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	429 COMMIT;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql.sql @ 5200:42bb6ff78d1b 2.9.11