Mercurial > dive4elements > river

comparison backend/doc/schema/oracle-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 NUMBER(38,0) NOT NULL, 41 id NUMBER(38,0) NOT NULL,
42 river_id NUMBER(38,0) NOT NULL, 42 river_id NUMBER(38,0) NOT NULL,
43 year NUMBER(38,0), 43 year NUMBER(38,0),
44 sounding_width NUMBER(38,0), 44 sounding_width NUMBER(38,0),
45 type_id NUMBER(38,0) NOT NULL, 45 type_id NUMBER(38,0) NOT NULL,
48 old_elevation_model_id NUMBER(38,0), 48 old_elevation_model_id NUMBER(38,0),
49 range_id NUMBER(38,0), 49 range_id NUMBER(38,0),
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
66
67 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ;
68
69 CREATE TABLE bed_height_values (
65 id NUMBER(38,0) NOT NULL, 70 id NUMBER(38,0) NOT NULL,
66 bed_height_single_id NUMBER(38,0) NOT NULL, 71 bed_height_id NUMBER(38,0) NOT NULL,
67 station DOUBLE PRECISION NOT NULL, 72 station DOUBLE PRECISION NOT NULL,
68 height DOUBLE PRECISION, 73 height DOUBLE PRECISION,
69 uncertainty DOUBLE PRECISION, 74 uncertainty DOUBLE PRECISION,
70 data_gap DOUBLE PRECISION, 75 data_gap DOUBLE PRECISION,
71 sounding_width DOUBLE PRECISION, 76 sounding_width DOUBLE PRECISION,
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