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

annotate backend/doc/schema/postgresql.sql @ 9650:a2a42a6bac6b

Importer (s/u-info) extensions: outer try/catch for parse and log of line no, catching parsing exception if not enough value fields, parsing error and warning log messages with line number, detecting and rejecting duplicate data series, better differentiation between error and warning log messages

author	mschaefer
date	Mon, 23 Mar 2020 14:57:03 +0100
parents	f89fb9e9abad
children

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 (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	6 id int PRIMARY KEY NOT NULL,
2347 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
8683 cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table. Andre Heinecke <andre.heinecke@intevation.de> parents: 8658 diff changeset	10 -- SEDDB_NAME
8687 3912da70736b (issue1796) We don't necessarily need the river Rhine. SedDB river names are purely optional. Tom Gottfried <tom@intevation.de> parents: 8683 diff changeset	11 -- Lookup table for optional matching with differing river names in SedDB
3912da70736b (issue1796) We don't necessarily need the river Rhine. SedDB river names are purely optional. Tom Gottfried <tom@intevation.de> parents: 8683 diff changeset	12 -- Add name here and set rivers.seddb_name_id to id
8683 cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table. Andre Heinecke <andre.heinecke@intevation.de> parents: 8658 diff changeset	13 CREATE TABLE seddb_name (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	14 id int PRIMARY KEY NOT NULL,
8683 cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table. Andre Heinecke <andre.heinecke@intevation.de> parents: 8658 diff changeset	15 name VARCHAR(256) NOT NULL
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table. Andre Heinecke <andre.heinecke@intevation.de> parents: 8658 diff changeset	16 );
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table. Andre Heinecke <andre.heinecke@intevation.de> parents: 8658 diff changeset	17
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 -- Gewaesser
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	19 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	20
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	21 CREATE TABLE rivers (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	22 id int PRIMARY KEY NOT NULL,
7743 a67bd7744827 Schema correction following yesterday's brain failure. Tom Gottfried <tom@intevation.de> parents: 7741 diff changeset	23 model_uuid CHAR(36) UNIQUE,
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	24 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	25 name VARCHAR(256) NOT NULL UNIQUE,
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	26 km_up int DEFAULT 0 NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	27 wst_unit_id int NOT NULL REFERENCES units(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	28 seddb_name_id int REFERENCES seddb_name(id),
8411 b8c6cb36607e SCHEMA CHANGE: There is no boolean data type in oracle, and therefore it's no good idea to use it in postgresql. Tom Gottfried <tom@intevation.de> parents: 7743 diff changeset	29 CHECK(km_up IN(0,1))
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	31
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 -- Bruecke, Haefen, etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	33 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	34
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	35 CREATE TABLE attributes (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	36 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	37 value VARCHAR(256) NOT NULL UNIQUE
164 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 -- 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	41 CREATE SEQUENCE RANGES_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 ranges (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	44 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	45 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	46 a NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	47 b NUMERIC,
7005 dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges. Tom Gottfried <tom.gottfried@intevation.de> parents: 6871 diff changeset	48 UNIQUE (river_id, a, b),
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges. Tom Gottfried <tom.gottfried@intevation.de> parents: 6871 diff changeset	49 CHECK (a < b)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	50 );
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	51
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	52
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	53 -- Lage 'links', 'rechts', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	54 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	55
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	56 CREATE TABLE positions (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	57 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	58 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	59 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	60
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	61 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	62 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	63
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	64 CREATE TABLE edges (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	65 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	66 top NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	67 bottom NUMERIC
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	68 );
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	69
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	70 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	71 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	72
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	73 CREATE TABLE annotation_types (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	74 id int PRIMARY KEY NOT NULL,
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	75 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	76 );
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	77
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	78 -- 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	79 -- plus its position.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	80 CREATE SEQUENCE ANNOTATIONS_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 annotations (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	83 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	84 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	85 attribute_id int NOT NULL REFERENCES attributes(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	86 position_id int REFERENCES positions(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	87 edge_id int REFERENCES edges(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	88 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	89 );
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 -- Pegel
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	92 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	93
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94 CREATE TABLE gauges (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	95 id int PRIMARY KEY NOT NULL,
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	96 name VARCHAR(256) NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	97 -- remove river id here because range_id references river already
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	98 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	99 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	100 aeo NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	101 official_number int8,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	102
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	103 -- Pegelnullpunkt
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	104 datum NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105 -- Streckengueltigkeit
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	106 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	107
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	108 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	109 UNIQUE (official_number, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	110 UNIQUE (river_id, station)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	111 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	112
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	113 -- 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	114 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	115
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 CREATE TABLE main_value_types (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	117 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	118 name VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	120
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 -- 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	122 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	123
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 CREATE TABLE named_main_values (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	125 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	126 name VARCHAR(256) NOT NULL,
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	127 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	128 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	129
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	130 -- Table for time intervals
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	131 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	132
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	133 CREATE TABLE time_intervals (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	134 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	135 start_time TIMESTAMP NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	136 stop_time TIMESTAMP,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138 );
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	139
164 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 -- Stammdaten
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	142 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	143
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144 CREATE TABLE main_values (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	145 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	146 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	147 named_value_id int NOT NULL REFERENCES named_main_values(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	148 value NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	149
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	153 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	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 -- 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_TABLES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	158
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	159 CREATE TABLE discharge_tables (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	160 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	161 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	162 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	163 bfg_id VARCHAR(50),
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	164 kind int NOT NULL DEFAULT 0,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	165 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	166 UNIQUE(gauge_id, bfg_id, kind)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	168
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	169 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	170 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	171
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	172 CREATE TABLE discharge_table_values (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	173 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	174 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	175 q NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	176 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	177
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	178 UNIQUE (table_id, q, w)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	181 -- WST files
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	182 --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	183 CREATE TABLE wst_kinds (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	184 id int PRIMARY KEY NOT NULL,
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	185 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	186 );
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	187 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	188 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	189 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	190 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	191 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	192 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	193 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	194 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	195
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	196
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	197 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	198
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	199 CREATE TABLE wsts (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	200 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	201 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	202 description VARCHAR(256) NOT NULL,
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	203 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	204 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	205 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	206 );
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 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	209 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	210
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	211 CREATE TABLE wst_columns (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	212 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	213 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	214 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	215 description VARCHAR(256),
6502 3b93f439e954 Schema change: add new field 'source' to wst_columns, currently only for official lines Tom Gottfried <tom.gottfried@intevation.de> parents: 6300 diff changeset	216 source VARCHAR(256),
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	217 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	218
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	219 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	220
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	221 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	222 UNIQUE (wst_id, position)
164 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 -- 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	226 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	227
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	228 CREATE TABLE wst_column_values (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	229 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	230 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	231 position NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	232 w NUMERIC NOT NULL,
164 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 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	235 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	236 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	237
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	238 -- 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	239 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	240
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	241 CREATE TABLE wst_q_ranges (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	242 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	243 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	244 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	245 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	246
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	247 -- 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	248 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	249
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	250 CREATE TABLE wst_column_q_ranges (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	251 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	252 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	253 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	254
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	255 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	256 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	257
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	258 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	259
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	260 CREATE TABLE official_lines (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	261 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	262 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	263 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	264
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	265 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	266 );
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	267
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	268 CREATE VIEW wst_value_table AS
8658 3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	269 SELECT
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	270 wcv.position AS position,
8658 3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	271 w,
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	272 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	273 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	274 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	275 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	276 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	277 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	278 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	279 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	280 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	281 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	282 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	283 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	284 JOIN wst_column_values wcv
6177 573112007ec7 Remove stray semicolon from sql schema. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 6143 diff changeset	285 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	286 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	287 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	288
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	289 -- 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	290 CREATE VIEW wst_w_values AS
8658 3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	291 SELECT wcv."position" AS km,
3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	292 wcv.w AS w,
3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	293 wc."position" AS column_pos,
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	294 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	295 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	296 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	297 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	298 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	299
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	300 -- 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	301 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	302 SELECT wc.position AS column_pos,
8658 3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	303 wqr.q AS q,
3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8411 diff changeset	304 r.a AS a,
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	305 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	306 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	307 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	308 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	309 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	310 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	311 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	312
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	313 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	314
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	315 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	316
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	317 CREATE TABLE cross_sections (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	318 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	319 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	320 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	321 description VARCHAR(256)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	322 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	323
1205 5f1506fc7636 Made import of cross sections work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1203 diff changeset	324 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	325
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 CREATE TABLE cross_section_lines (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	327 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	328 km NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	329 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	330 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	331 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	332
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	333 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	334
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	335 CREATE TABLE cross_section_points (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	336 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	337 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	338 col_pos int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	339 x NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	340 y NUMERIC NOT NULL
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	341 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	342
3796 61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	343 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	344 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	345 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	346 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	347 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	348
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	349 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	350
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	351 CREATE SEQUENCE HYKS_ID_SEQ;
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 TABLE hyks (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	354 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	355 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	356 description VARCHAR(256) NOT NULL
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
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	359 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	360
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	361 CREATE TABLE hyk_entries (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	362 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	363 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	364 km NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	365 measure TIMESTAMP,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	366 UNIQUE (hyk_id, km)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	367 );
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 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	370
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	371 CREATE TABLE hyk_formations (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	372 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	373 formation_num int NOT NULL DEFAULT 0,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	374 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	375 top NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	376 bottom NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	377 distance_vl NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	378 distance_hf NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	379 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	380 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	381 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	382
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	383 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	384
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	385 CREATE TABLE hyk_flow_zone_types (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	386 id int PRIMARY KEY NOT NULL,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	387 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	388 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	389 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	390
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	391 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	392
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	393 CREATE TABLE hyk_flow_zones (
8965 f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	394 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	395 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	396 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	397 a NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted mschaefer parents: 8943 diff changeset	398 b NUMERIC NOT NULL,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	399 CHECK (a <= b)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	400 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	401
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	402 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	403 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	404 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	405 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	406 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	407 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	408 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	409 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	410 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	411 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	412 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	413
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	414 COMMIT;

Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 9650:a2a42a6bac6b