Mercurial > dive4elements > river
diff backend/doc/schema/postgresql-minfo.sql @ 8965:f89fb9e9abad
Datatype changes from rev 8942 reverted
| author | mschaefer |
|---|---|
| date | Tue, 03 Apr 2018 08:26:54 +0200 |
| parents | 71b17f731762 |
| children |
line wrap: on
line diff
--- a/backend/doc/schema/postgresql-minfo.sql Thu Mar 29 15:48:17 2018 +0200 +++ b/backend/doc/schema/postgresql-minfo.sql Tue Apr 03 08:26:54 2018 +0200 @@ -3,7 +3,7 @@ CREATE SEQUENCE LOCATION_SYSTEM_SEQ; CREATE TABLE location_system ( - id NUMERIC(9,0) NOT NULL, + id int NOT NULL, name VARCHAR(32) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) @@ -13,9 +13,9 @@ CREATE SEQUENCE ELEVATION_MODEL_SEQ; CREATE TABLE elevation_model ( - id NUMERIC(9,0) NOT NULL, + id int NOT NULL, name VARCHAR(32) NOT NULL, - unit_id NUMERIC(9,0) NOT NULL, + unit_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ); @@ -23,7 +23,7 @@ -- lookup table for bedheight types CREATE TABLE bed_height_type ( - id NUMERIC(9,0) NOT NULL, + id int NOT NULL, name VARCHAR(64) NOT NULL, PRIMARY KEY(id) ); @@ -38,14 +38,14 @@ CREATE SEQUENCE BED_HEIGHT_ID_SEQ; CREATE TABLE bed_height ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, - year NUMERIC(9,0), - type_id NUMERIC(9,0) NOT NULL, - location_system_id NUMERIC(9,0) NOT NULL, - cur_elevation_model_id NUMERIC(9,0) NOT NULL, - old_elevation_model_id NUMERIC(9,0), - range_id NUMERIC(9,0), + id int NOT NULL, + river_id int NOT NULL, + year int, + type_id int NOT NULL, + location_system_id int NOT NULL, + cur_elevation_model_id int NOT NULL, + old_elevation_model_id int, + range_id int, evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), @@ -65,16 +65,14 @@ CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ; ---FIXME: make precision and scale of station column equal with the km columns of the other tables ---FIXME: replace double precision with exact types CREATE TABLE bed_height_values ( - id NUMERIC(9,0) NOT NULL, - bed_height_id NUMERIC(9,0) NOT NULL, - station DOUBLE PRECISION NOT NULL, - height DOUBLE PRECISION, - uncertainty DOUBLE PRECISION, - data_gap DOUBLE PRECISION, - sounding_width DOUBLE PRECISION, + id int NOT NULL, + bed_height_id int NOT NULL, + station NUMERIC NOT NULL, + height NUMERIC, + uncertainty NUMERIC, + data_gap NUMERIC, + sounding_width NUMERIC, PRIMARY KEY(id), UNIQUE (station, bed_height_id), CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) @@ -85,9 +83,9 @@ CREATE SEQUENCE DEPTHS_ID_SEQ; CREATE TABLE depths ( - id NUMERIC(9,0) NOT NULL, - lower NUMERIC(6,2) NOT NULL, - upper NUMERIC(6,2) NOT NULL, + id int NOT NULL, + lower NUMERIC NOT NULL, + upper NUMERIC NOT NULL, PRIMARY KEY(id) ); @@ -95,9 +93,9 @@ CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; CREATE TABLE sediment_density ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, - depth_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + river_id int NOT NULL, + depth_id int NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, @@ -107,15 +105,14 @@ CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; ---FIXME: make precision and scale of station column equal with the km columns of the other tables CREATE TABLE sediment_density_values ( - id NUMERIC(9,0) NOT NULL, - sediment_density_id NUMERIC(9,0) NOT NULL, - station NUMERIC(6,2) NOT NULL, - shore_offset NUMERIC(6,2), - density NUMERIC(8,2) NOT NULL, + id int NOT NULL, + sediment_density_id int NOT NULL, + station NUMERIC NOT NULL, + shore_offset NUMERIC, + density NUMERIC NOT NULL, description VARCHAR(256), - year NUMERIC(4,0), + year int, PRIMARY KEY(id), CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE ); @@ -124,11 +121,11 @@ CREATE SEQUENCE POROSITY_ID_SEQ; CREATE TABLE porosity ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, - depth_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + river_id int NOT NULL, + depth_id int NOT NULL, description VARCHAR(256), - time_interval_id NUMERIC(9,0) NOT NULL, + time_interval_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), @@ -138,13 +135,12 @@ CREATE SEQUENCE POROSITY_VALUES_ID_SEQ; ---FIXME: make precision and scale of station column equal with the km columns of the other tables CREATE TABLE porosity_values ( - id NUMERIC(9,0) NOT NULL, - porosity_id NUMERIC(9,0) NOT NULL, - station DOUBLE PRECISION NOT NULL, - shore_offset DOUBLE PRECISION, - porosity DOUBLE PRECISION NOT NULL, + id int NOT NULL, + porosity_id int NOT NULL, + station NUMERIC NOT NULL, + shore_offset NUMERIC, + porosity NUMERIC NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE @@ -154,9 +150,9 @@ CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; CREATE TABLE morphologic_width ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, - unit_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + river_id int NOT NULL, + unit_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) @@ -166,10 +162,10 @@ CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; CREATE TABLE morphologic_width_values ( - id NUMERIC(9,0) NOT NULL, - morphologic_width_id NUMERIC(9,0) NOT NULL, - station NUMERIC(7,3) NOT NULL, - width NUMERIC(7,3) NOT NULL, + id int NOT NULL, + morphologic_width_id int NOT NULL, + station NUMERIC NOT NULL, + width NUMERIC NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE @@ -179,10 +175,10 @@ CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; CREATE TABLE discharge_zone ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + river_id int NOT NULL, gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance - value NUMERIC(8,3) NOT NULL, + value NUMERIC NOT NULL, lower_discharge VARCHAR(64) NOT NULL, upper_discharge VARCHAR(64), PRIMARY KEY(id), @@ -193,8 +189,8 @@ CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; CREATE TABLE flow_velocity_model ( - id NUMERIC(9,0) NOT NULL, - discharge_zone_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + discharge_zone_id int NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE @@ -204,13 +200,13 @@ CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; CREATE TABLE flow_velocity_model_values ( - id NUMERIC(9,0) NOT NULL, - flow_velocity_model_id NUMERIC(9,0) NOT NULL, - station NUMERIC(7,3) NOT NULL, - q NUMERIC(8,3) NOT NULL, - total_channel NUMERIC(5,3) NOT NULL, - main_channel NUMERIC(5,3) NOT NULL, - shear_stress NUMERIC(6,3) NOT NULL, + id int NOT NULL, + flow_velocity_model_id int NOT NULL, + station NUMERIC NOT NULL, + q NUMERIC NOT NULL, + total_channel NUMERIC NOT NULL, + main_channel NUMERIC NOT NULL, + shear_stress NUMERIC NOT NULL, PRIMARY KEY(id), UNIQUE (station, flow_velocity_model_id), CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE @@ -221,8 +217,8 @@ CREATE SEQUENCE FV_MEASURE_ID_SEQ; CREATE TABLE flow_velocity_measurements ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + river_id int NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE @@ -231,13 +227,13 @@ CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; CREATE TABLE flow_velocity_measure_values ( - id NUMERIC(9,0) NOT NULL, - measurements_id NUMERIC(9,0) NOT NULL, - station NUMERIC(7,3) NOT NULL, - datetime TIMESTAMP(0), - w NUMERIC(7,3) NOT NULL, - q NUMERIC(8,3) NOT NULL, - v NUMERIC(5,3) NOT NULL, + id int NOT NULL, + measurements_id int NOT NULL, + station NUMERIC NOT NULL, + datetime TIMESTAMP, + w NUMERIC NOT NULL, + q NUMERIC NOT NULL, + v NUMERIC NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE @@ -247,10 +243,10 @@ CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; CREATE TABLE grain_fraction ( - id NUMERIC(9,0) NOT NULL, + id int NOT NULL, name VARCHAR(64) NOT NULL, - lower NUMERIC(6,3), - upper NUMERIC(6,3), + lower NUMERIC, + upper NUMERIC, PRIMARY KEY (id), UNIQUE(name, lower, upper) ); @@ -266,26 +262,29 @@ INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200); INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2); +ALTER SEQUENCE GRAIN_FRACTION_ID_SEQ RESTART WITH 10; + --lookup table for sediment load kinds CREATE TABLE sediment_load_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official'); INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official'); + CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ; CREATE TABLE sediment_load_ls ( - id NUMERIC(9,0) NOT NULL, - river_id NUMERIC(9,0) NOT NULL, - grain_fraction_id NUMERIC(9,0), - unit_id NUMERIC(9,0) NOT NULL, - time_interval_id NUMERIC(9,0) NOT NULL, - sq_time_interval_id NUMERIC(9,0), + id int NOT NULL, + river_id int NOT NULL, + grain_fraction_id int, + unit_id int NOT NULL, + time_interval_id int NOT NULL, + sq_time_interval_id int, description VARCHAR(256), - kind NUMERIC(9,0), + kind int, PRIMARY KEY (id), CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, @@ -305,10 +304,10 @@ CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ; CREATE TABLE sediment_load_ls_values ( - id NUMERIC(9,0) NOT NULL, - sediment_load_ls_id NUMERIC(9,0) NOT NULL, - station NUMERIC(7,3) NOT NULL, - value NUMERIC(10,3) NOT NULL, + id int NOT NULL, + sediment_load_ls_id int NOT NULL, + station NUMERIC NOT NULL, + value NUMERIC NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id) REFERENCES sediment_load_ls(id) ON DELETE CASCADE @@ -317,10 +316,10 @@ CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; CREATE TABLE measurement_station ( - id NUMERIC(9,0) NOT NULL, - range_id NUMERIC(9,0) NOT NULL, - reference_gauge_id NUMERIC(9,0), - time_interval_id NUMERIC(9,0), + id int NOT NULL, + range_id int NOT NULL, + reference_gauge_id int, + time_interval_id int, name VARCHAR(256) NOT NULL, measurement_type VARCHAR(64) NOT NULL, riverside VARCHAR(16), @@ -342,12 +341,12 @@ CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; CREATE TABLE sediment_load ( - id NUMERIC(9,0) NOT NULL, - grain_fraction_id NUMERIC(9,0) NOT NULL, - time_interval_id NUMERIC(9,0) NOT NULL, - sq_time_interval_id NUMERIC(9,0), + id int NOT NULL, + grain_fraction_id int NOT NULL, + time_interval_id int NOT NULL, + sq_time_interval_id int, description VARCHAR(256), - kind NUMERIC(9,0), + kind int, PRIMARY KEY (id), CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) REFERENCES sediment_load_kinds(id), @@ -363,9 +362,9 @@ CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; CREATE TABLE sediment_load_values ( - id NUMERIC(9,0) NOT NULL, - sediment_load_id NUMERIC(9,0) NOT NULL, - measurement_station_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + sediment_load_id int NOT NULL, + measurement_station_id int NOT NULL, value DOUBLE PRECISION NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) @@ -378,8 +377,8 @@ CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation ( - id NUMERIC(9,0) NOT NULL, - time_interval_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + time_interval_id int NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) @@ -389,20 +388,19 @@ CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; ---FIXME: adjust precision and scale of the numerics CREATE TABLE sq_relation_value ( - id NUMERIC(9,0) NOT NULL, - sq_relation_id NUMERIC(9,0) NOT NULL, - measurement_station_id NUMERIC(9,0) NOT NULL, + id int NOT NULL, + sq_relation_id int NOT NULL, + measurement_station_id int NOT NULL, parameter VARCHAR(1) NOT NULL, - a NUMERIC(38,20) NOT NULL, - b NUMERIC(38,20) NOT NULL, - qmax NUMERIC(38,20) NOT NULL, - rsq NUMERIC(38,3), - ntot NUMERIC(9,0), - noutl NUMERIC(9,0), - cferguson NUMERIC(38,20), - cduan NUMERIC(38,20), + a NUMERIC NOT NULL, + b NUMERIC NOT NULL, + qmax NUMERIC NOT NULL, + rsq NUMERIC, + ntot int, + noutl int, + cferguson NUMERIC, + cduan NUMERIC, PRIMARY KEY (id), UNIQUE(sq_relation_id, measurement_station_id, parameter), CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id)