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

annotate backend/doc/schema/postgresql.sql @ 7076:7f600001c807 generator-refactoring

Add LTR inversion code to diagram generator. This code is used in serveral diagrams and as it modifies a whole diagram it should be central. (This should also make maintenance easier). This function can be called by processors to make sure that their data is plotted with an LTR waterflow.

author	Andre Heinecke <aheinecke@intevation.de>
date	Fri, 20 Sep 2013 16:33:22 +0200
parents	0711ce5ca701
children	3b93f439e954

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,
5893 c1abd48a03e2 Schema change: remove UNIQUE-constraint from rivers.official number as we have different representations of the same river (e.g. for sommer and winter) Tom Gottfried <tom@intevation.de> parents: 5883 diff changeset	15 official_number int8,
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	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,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	72 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 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,
5894 62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs Tom Gottfried <tom@intevation.de> parents: 5893 diff changeset	89 official_number int8,
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),
5894 62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs Tom Gottfried <tom@intevation.de> parents: 5893 diff changeset	97 UNIQUE (official_number, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	98 UNIQUE (river_id, station)
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	101 -- 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	102 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	103
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	104 CREATE TABLE main_value_types (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	105 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	106 name VARCHAR(256) NOT NULL UNIQUE
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 -- 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	110 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	111
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	112 CREATE TABLE named_main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	113 id int PRIMARY KEY NOT NULL,
5915 4fafe8d147b2 Schema change: named_main_values.name is not unique, as we can have the same name for different types Tom Gottfried <tom@intevation.de> parents: 5894 diff changeset	114 name VARCHAR(256) NOT NULL,
5684 88cbe798cbab Schema change: fixed constraints Tom Gottfried <tom@intevation.de> parents: 5321 diff changeset	115 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	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,
5883 4f35b34f4efa Schema change: add constraint to discharge tables Tom Gottfried <tom.gottfried@intevation.de> parents: 5838 diff changeset	153 time_interval_id int REFERENCES time_intervals(id),
5923 131f5f58ff7d Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge Tom Gottfried <tom@intevation.de> parents: 5915 diff changeset	154 UNIQUE(gauge_id, bfg_id, kind)
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	158 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	159
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	160 CREATE TABLE discharge_table_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	161 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	162 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	163 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	164 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	165
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	166 UNIQUE (table_id, q, w)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	169 -- WST files
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	170 --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	171 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	172 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	173 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	174 );
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	175 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	176 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	177 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	178 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	179 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	180 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	181 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	182 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	183
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	184
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	185 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	186
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	187 CREATE TABLE wsts (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	188 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	189 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	190 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	191 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	192 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	193 UNIQUE (river_id, description)
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 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	197 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	198
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	199 CREATE TABLE wst_columns (
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_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	202 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	203 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	204 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	205
6011 d9a1cf0438a5 backout 6009:6ea171b6d8d2 because this was only really valid for fixations Tom Gottfried <tom@intevation.de> parents: 6009 diff changeset	206 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	207
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	208 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	209 UNIQUE (wst_id, position)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	210 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	211
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	212 -- 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	213 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	214
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	215 CREATE TABLE wst_column_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	216 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	217 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	218 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	219 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	220
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	221 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	222 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	223 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	224
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	225 -- 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	226 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	227
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	228 CREATE TABLE wst_q_ranges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	229 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	230 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	231 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	232 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	233
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	234 -- 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	235 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	236
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	237 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	238 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	239 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	240 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	241
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	242 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	243 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	244
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	245 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	246
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	247 CREATE TABLE official_lines (
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	248 id int PRIMARY KEY NOT NULL,
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	249 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	250 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	251
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	252 UNIQUE (wst_column_id, named_main_value_id)
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	253 );
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	254
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	255 CREATE VIEW wst_value_table AS
6078 176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	256 SELECT
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	257 wcv.position AS position,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	258 w,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	259 q,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	260 wc.position AS column_pos,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	261 w.id AS wst_id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	262 FROM wsts w
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	263 JOIN wst_columns wc
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	264 ON wc.wst_id=w.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	265 JOIN wst_column_q_ranges wcqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	266 ON wcqr.wst_column_id=wc.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	267 JOIN wst_q_ranges wqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	268 ON wcqr.wst_q_range_id=wqr.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	269 JOIN ranges r
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	270 ON wqr.range_id=r.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	271 JOIN wst_column_values wcv
6177 573112007ec7 Remove stray semicolon from sql schema. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 6143 diff changeset	272 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b
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	273 ORDER BY wcv.position ASC,
6078 176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	274 wc.position DESC;
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	275
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	276 -- 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	277 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	278 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	279 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	280 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	281 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	282 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	283 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	284 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	285 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	286
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	287 -- 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	288 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	289 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	300 -- data for the cross-sections
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 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
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 CREATE TABLE cross_sections (
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	305 id int PRIMARY KEY NOT NULL,
6030 d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	306 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	307 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	308 description VARCHAR(256)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	309 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	310
1205 5f1506fc7636 Made import of cross sections work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1203 diff changeset	311 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	312
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	313 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	314 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	315 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	316 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	317 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	318 );
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
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	320 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
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 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	323 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	324 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	325 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	326 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	327 y NUMERIC NOT NULL,
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	328 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	329
3796 61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	330 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	331 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	332 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	333 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	334 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	335
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	336 -- Hydraulische Kenngroessen
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 CREATE SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	339
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	340 CREATE TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	341 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	342 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	343 description VARCHAR(256) NOT NULL
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
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	346 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	347
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	348 CREATE TABLE hyk_entries (
1212 dd948530fd57 Schema: Added missing columns in HYK tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1209 diff changeset	349 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	350 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	351 km NUMERIC NOT NULL,
1217 d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1214 diff changeset	352 measure TIMESTAMP,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	353 UNIQUE (hyk_id, km)
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
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	356 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	357
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	358 CREATE TABLE hyk_formations (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	359 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	360 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	361 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	362 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	363 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	364 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	365 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	366 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	367 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	368 );
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 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	371
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	372 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	373 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	374 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	375 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	376 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	377
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	378 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	379
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	380 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	381 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	382 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	383 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	384 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	385 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	386 CHECK (a <= b)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	387 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	388
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	389 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	390 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	391 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	392 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	393 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	394 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	395 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	396 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	397 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	398 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	399 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	400
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	401 COMMIT;

Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 7076:7f600001c807 generator-refactoring