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

annotate backend/doc/schema/postgresql.sql @ 7602:c50dbbe17950

issue1596: Store table (cell) data twice: Once as (formatted) string as coming from server, once transformed into float (or string). The benefit is that now we can sort table data numerically, while keeping the formatted and i18ned display of values.

author	Felix Wolfsteller <felix.wolfsteller@intevation.de>
date	Wed, 27 Nov 2013 14:55:25 +0100
parents	dddf633fec05
children	44a9233c62eb

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,
7005 dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges. Tom Gottfried <tom.gottfried@intevation.de> parents: 6871 diff changeset	37 UNIQUE (river_id, a, b),
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges. Tom Gottfried <tom.gottfried@intevation.de> parents: 6871 diff changeset	38 CHECK (a < b)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39 );
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	40
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	42 -- Lage 'links', 'rechts', etc.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	43 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	44
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	45 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	47 value VARCHAR(256) NOT NULL UNIQUE
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	48 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	49
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	50 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	51 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	52
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	53 CREATE TABLE edges (
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	54 id int PRIMARY KEY NOT NULL,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	55 top NUMERIC,
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	56 bottom NUMERIC
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	57 );
bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	58
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	59 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	60 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	61
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	62 CREATE TABLE annotation_types (
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	63 id int PRIMARY KEY NOT NULL,
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	64 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	65 );
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	66
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	67 -- 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	68 -- plus its position.
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	69 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	70
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	72 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	73 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	74 attribute_id int NOT NULL REFERENCES attributes(id),
758 bf16268629d9 Added 'Kanten' model Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 755 diff changeset	75 position_id int REFERENCES positions(id),
763 8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	76 edge_id int REFERENCES edges(id),
8076f6a689d0 First part of flys/issue18 Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 758 diff changeset	77 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	78 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	80 -- Pegel
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	81 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	82
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	83 CREATE TABLE gauges (
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	84 id int PRIMARY KEY NOT NULL,
fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	85 name VARCHAR(256) NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5202 diff changeset	86 -- 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	87 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	88 station NUMERIC NOT NULL,
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2347 diff changeset	89 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	90 official_number int8,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	92 -- Pegelnullpunkt
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	93 datum NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94 -- Streckengueltigkeit
6871 5ce6a3d7d632 Undo misguided schema change from r5207 Tom Gottfried <tom@intevation.de> parents: 6817 diff changeset	95 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	96
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	97 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	98 UNIQUE (official_number, river_id),
194 0f0b98ef9b04 Fixed wrong unique constraint. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 174 diff changeset	99 UNIQUE (river_id, station)
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	102 -- 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	103 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	104
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105 CREATE TABLE main_value_types (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	106 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	107 name VARCHAR(256) NOT NULL UNIQUE
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	110 -- 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	111 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	112
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	113 CREATE TABLE named_main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	114 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	115 name VARCHAR(256) NOT NULL,
5684 88cbe798cbab Schema change: fixed constraints Tom Gottfried <tom@intevation.de> parents: 5321 diff changeset	116 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	117 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119 -- Table for time intervals
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	120 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	121
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123 id int PRIMARY KEY NOT NULL,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	124 start_time TIMESTAMP NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	127 );
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	128
164 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 -- Stammdaten
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	131 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	132
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	133 CREATE TABLE main_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	134 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	135 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	136 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	137 value NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	139 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	140
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	142 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	143 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	145 -- Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	146 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	147
167 15d515fe15f5 Added POJOs to be mapped to schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 164 diff changeset	148 CREATE TABLE discharge_tables (
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	149 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	150 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	151 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	152 bfg_id VARCHAR(50),
470 f4afea9b7537 Forget ',' in schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	153 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	154 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	155 UNIQUE(gauge_id, bfg_id, kind)
164 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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	158 -- Values of the Abflusstafeln
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	159 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	160
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	161 CREATE TABLE discharge_table_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	162 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	163 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	164 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	165 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	166
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167 UNIQUE (table_id, q, w)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	170 -- WST files
5202 646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	171 --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	172 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	173 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	174 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	175 );
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 (0, 'basedata');
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 (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	178 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	179 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	180 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	181 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	182 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	183 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	184
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst Tom Gottfried <tom@intevation.de> parents: 4991 diff changeset	185
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	186 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	187
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	188 CREATE TABLE wsts (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	189 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	190 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	191 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	192 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	193 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 UNIQUE (river_id, description)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	197 -- columns of WST files
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	198 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	199
171 4a83e14f40f9 Added entity and id annotations. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	200 CREATE TABLE wst_columns (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	201 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	202 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	203 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	204 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	205 source VARCHAR(256),
471 3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	206 position int NOT NULL DEFAULT 0,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	207
6011 d9a1cf0438a5 backout 6009:6ea171b6d8d2 because this was only really valid for fixations Tom Gottfried <tom@intevation.de> parents: 6009 diff changeset	208 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	209
471 3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	210 UNIQUE (wst_id, name),
3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 470 diff changeset	211 UNIQUE (wst_id, position)
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	212 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	213
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	214 -- w values in WST file column
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	215 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	216
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	217 CREATE TABLE wst_column_values (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	218 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	219 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	220 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	221 w NUMERIC NOT NULL,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	222
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	223 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	224 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	225 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	226
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	227 -- bind q values to range
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	228 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	229
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	230 CREATE TABLE wst_q_ranges (
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	231 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	232 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
168 86a1bd9cc50e More Hibernate/JPA stuff Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	233 q NUMERIC NOT NULL
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	234 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	235
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	236 -- bind q ranges to wst columns
170 88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	237 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 168 diff changeset	238
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	239 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	240 id int PRIMARY KEY NOT NULL,
4991 b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	241 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far) Tom Gottfried <tom.gottfried@intevation.de> parents: 4776 diff changeset	242 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	243
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	244 UNIQUE (wst_column_id, wst_q_range_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	245 );
324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	246
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	247 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	248
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	249 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	250 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	251 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	252 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	253
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	254 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	255 );
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	256
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	257 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	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 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	266 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	267 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	268 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	269 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	270 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	271 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	272 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	273 JOIN wst_column_values wcv
6177 573112007ec7 Remove stray semicolon from sql schema. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 6143 diff changeset	274 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	275 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	276 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	277
754 5bcf338eadb9 Added a new view to select ws of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 505 diff changeset	278 -- 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	279 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	280 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	281 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	282 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	283 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	284 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	285 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	286 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	287 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	288
755 a29fd0916803 Added a new view to select qs of a WST. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 754 diff changeset	289 -- 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 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	300 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	301
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	302 -- data for the cross-sections
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 SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	305
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	306 CREATE TABLE cross_sections (
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	307 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	308 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	309 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	310 description VARCHAR(256)
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	311 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	312
1205 5f1506fc7636 Made import of cross sections work. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1203 diff changeset	313 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	314
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 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	316 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	317 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	318 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	319 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	320 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1202 diff changeset	321
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	322 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	323
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	324 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	325 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	326 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	327 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	328 x NUMERIC NOT NULL,
6817 63a24b4fbebf fixed syntax errors in Postgres-schema. Tom Gottfried <tom@intevation.de> parents: 6502 diff changeset	329 y NUMERIC NOT NULL
1192 5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	330 );
5f8444df19e4 Add relations for cross sections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 763 diff changeset	331
3796 61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	332 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	333 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	334 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	335 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	336 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	337
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	338 -- Hydraulische Kenngroessen
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 SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	341
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	342 CREATE TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	343 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	344 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	345 description VARCHAR(256) NOT NULL
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	346 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	347
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	348 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	349
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	350 CREATE TABLE hyk_entries (
1212 dd948530fd57 Schema: Added missing columns in HYK tables. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1209 diff changeset	351 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	352 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	353 km NUMERIC NOT NULL,
1217 d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1214 diff changeset	354 measure TIMESTAMP,
1209 c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	355 UNIQUE (hyk_id, km)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	356 );
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 SEQUENCE HYK_FORMATIONS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	359
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	360 CREATE TABLE hyk_formations (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	361 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	362 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	363 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	364 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	365 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	366 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	367 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	368 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	369 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	370 );
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 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	373
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	374 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	375 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	376 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	377 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	378 );
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 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	381
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	382 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	383 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	384 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	385 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	386 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	387 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen" Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1205 diff changeset	388 CHECK (a <= b)
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
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	391 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	392 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	393 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	394 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	395 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	396 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	397 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	398 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	399 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	400 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	401 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	402
164 324e12ab7a05 Converted schema to be PostgreSQL compatible. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	403 COMMIT;

Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 7602:c50dbbe17950