Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 8559:6d8d7425a6b5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Bed heights are just bed heights since a while ('single' is obsolete).
author "Tom Gottfried <tom@intevation.de>"
date Mon, 16 Feb 2015 11:08:33 +0100
parents 3a0522f1a532
children 6fcf4717605f
comparison
equal deleted inserted replaced
8558:d0ea092a32f5 8559:6d8d7425a6b5
33 INSERT INTO bed_height_type VALUES (4, 'DGM'); 33 INSERT INTO bed_height_type VALUES (4, 'DGM');
34 INSERT INTO bed_height_type VALUES (5, 'TIN'); 34 INSERT INTO bed_height_type VALUES (5, 'TIN');
35 INSERT INTO bed_height_type VALUES (6, 'Modell'); 35 INSERT INTO bed_height_type VALUES (6, 'Modell');
36 36
37 37
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; 38 CREATE SEQUENCE BED_HEIGHT_ID_SEQ;
39 39
40 CREATE TABLE bed_height_single ( 40 CREATE TABLE bed_height (
41 id int NOT NULL, 41 id int NOT NULL,
42 river_id int NOT NULL, 42 river_id int NOT NULL,
43 year int, 43 year int,
44 sounding_width int, 44 sounding_width int,
45 type_id int NOT NULL, 45 type_id int NOT NULL,
48 old_elevation_model_id int, 48 old_elevation_model_id int,
49 range_id int, 49 range_id int,
50 evaluation_by VARCHAR(255), 50 evaluation_by VARCHAR(255),
51 description VARCHAR(255), 51 description VARCHAR(255),
52 PRIMARY KEY(id), 52 PRIMARY KEY(id),
53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 53 CONSTRAINT fk_bed_river_id FOREIGN KEY (river_id)
54 REFERENCES rivers(id) ON DELETE CASCADE,
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), 55 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), 56 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id)
56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), 57 REFERENCES location_system(id),
57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), 58 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id)
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE 59 REFERENCES elevation_model(id),
59 ); 60 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id)
60 61 REFERENCES elevation_model(id),
61 62 CONSTRAINT fk_range FOREIGN KEY (range_id)
62 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; 63 REFERENCES ranges(id) ON DELETE CASCADE
63 64 );
64 CREATE TABLE bed_height_single_values ( 65
65 id int NOT NULL, 66
66 bed_height_single_id int NOT NULL, 67 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ;
68
69 CREATE TABLE bed_height_values (
70 id int NOT NULL,
71 bed_height_id int NOT NULL,
67 station NUMERIC NOT NULL, 72 station NUMERIC NOT NULL,
68 height NUMERIC, 73 height NUMERIC,
69 uncertainty NUMERIC, 74 uncertainty NUMERIC,
70 data_gap NUMERIC, 75 data_gap NUMERIC,
71 sounding_width NUMERIC, 76 sounding_width NUMERIC,
72 PRIMARY KEY(id), 77 PRIMARY KEY(id),
73 UNIQUE (station, bed_height_single_id), 78 UNIQUE (station, bed_height_id),
74 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE 79 CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id)
80 REFERENCES bed_height(id) ON DELETE CASCADE
75 ); 81 );
76 82
77 83
78 CREATE SEQUENCE DEPTHS_ID_SEQ; 84 CREATE SEQUENCE DEPTHS_ID_SEQ;
79 85
http://dive4elements.wald.intevation.org