# HG changeset patch # User mschaefer # Date 1520931353 -3600 # Node ID 4a6b6a3c279cdd5e02ef29908c3092fb2cce6a33 # Parent 11bf13cf046360cc4944de4b3b70df540e2309e8# Parent c7b200fe343b9b68bf7941b5ea53d577161d742c Merge diff -r 11bf13cf0463 -r 4a6b6a3c279c backend/doc/schema/postgresql-drop-spatial.sql --- a/backend/doc/schema/postgresql-drop-spatial.sql Fri Mar 09 18:47:06 2018 +0100 +++ b/backend/doc/schema/postgresql-drop-spatial.sql Tue Mar 13 09:55:53 2018 +0100 @@ -1,47 +1,43 @@ BEGIN; -DROP TABLE river_axes; +DROP TABLE axis_kinds CASCADE; +DROP TABLE river_axes CASCADE; DROP SEQUENCE RIVER_AXES_ID_SEQ; - -DROP TABLE river_axes_km; +DROP TABLE river_axes_km CASCADE; DROP SEQUENCE RIVER_AXES_KM_ID_SEQ; - -DROP TABLE cross_section_tracks; +DROP TABLE cross_section_track_kinds CASCADE; +DROP TABLE cross_section_tracks CASCADE; DROP SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; - -DROP TABLE buildings; +DROP TABLE building_kinds CASCADE; +DROP TABLE buildings CASCADE; DROP SEQUENCE BUILDINGS_ID_SEQ; - -DROP TABLE fixpoints; +DROP TABLE fixpoints CASCADE; DROP SEQUENCE FIXPOINTS_ID_SEQ; - -DROP TABLE floodplain; +DROP TABLE floodplain_kinds CASCADE; +DROP TABLE floodplain CASCADE; DROP SEQUENCE FLOODPLAIN_ID_SEQ; - -DROP TABLE dem; +DROP TABLE dem CASCADE; DROP SEQUENCE DEM_ID_SEQ; +DROP TABLE hws_kinds CASCADE; +DROP TABLE fed_states CASCADE; +DROP TABLE hws_lines CASCADE; +DROP SEQUENCE HWS_LINES_ID_SEQ; +DROP TABLE hws_points CASCADE; +DROP SEQUENCE HWS_POINTS_ID_SEQ; +DROP TABLE floodmap_kinds CASCADE; +DROP TABLE floodmaps CASCADE; +DROP SEQUENCE FLOODMAPS_ID_SEQ; +DROP TABLE sectie_kinds CASCADE; +DROP TABLE sobek_kinds CASCADE; +DROP TABLE boundary_kinds CASCADE; +DROP TABLE hydr_boundaries CASCADE; +DROP SEQUENCE HYDR_BOUNDARIES_ID_SEQ; +DROP TABLE hydr_boundaries_poly CASCADE; +DROP SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; +DROP TABLE jetty_kinds CASCADE; +DROP TABLE jetties CASCADE; +DROP SEQUENCE JETTIES_ID_SEQ; +DROP TABLE flood_marks CASCADE; +DROP SEQUENCE FLOOD_MARKS_ID_SEQ; -DROP TABLE hws_points; -DROP SEQUENCE HWS_POINTS_ID_SEQ; - -DROP TABLE hws_lines; -DROP SEQUENCE HWS_LINES_ID_SEQ; - -DROP TABLE floodmaps; -DROP SEQUENCE FLOODMAPS_ID_SEQ; - -DROP TABLE hydr_boundaries; -DROP SEQUENCE HYDR_BOUNDARIES_ID_SEQ; - -DROP TABLE hydr_boundaries_poly; -DROP SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; - -DROP TABLE fed_states; -DROP TABLE hws_kinds; -DROP TABLE sobek_kinds; -DROP TABLE sectie_kinds; -DROP TABLE boundary_kinds; -DROP TABLE axis_kinds; -DROP TABLE building_kinds; - -COMMIT; +COMMIT; \ No newline at end of file diff -r 11bf13cf0463 -r 4a6b6a3c279c backend/doc/schema/postgresql-minfo.sql --- a/backend/doc/schema/postgresql-minfo.sql Fri Mar 09 18:47:06 2018 +0100 +++ b/backend/doc/schema/postgresql-minfo.sql Tue Mar 13 09:55:53 2018 +0100 @@ -3,7 +3,7 @@ CREATE SEQUENCE LOCATION_SYSTEM_SEQ; CREATE TABLE location_system ( - id int NOT NULL, + id NUMERIC(9,0) 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 int NOT NULL, + id NUMERIC(9,0) NOT NULL, name VARCHAR(32) NOT NULL, - unit_id int NOT NULL, + unit_id NUMERIC(9,0) 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 int NOT NULL, + id NUMERIC(9,0) 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 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, + 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), evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), @@ -65,14 +65,16 @@ 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 int NOT NULL, - bed_height_id int NOT NULL, - station NUMERIC NOT NULL, - height NUMERIC, - uncertainty NUMERIC, - data_gap NUMERIC, - sounding_width NUMERIC, + 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, PRIMARY KEY(id), UNIQUE (station, bed_height_id), CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) @@ -83,9 +85,9 @@ CREATE SEQUENCE DEPTHS_ID_SEQ; CREATE TABLE depths ( - id int NOT NULL, - lower NUMERIC NOT NULL, - upper NUMERIC NOT NULL, + id NUMERIC(9,0) NOT NULL, + lower NUMERIC(6,2) NOT NULL, + upper NUMERIC(6,2) NOT NULL, PRIMARY KEY(id) ); @@ -93,9 +95,9 @@ CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; CREATE TABLE sediment_density ( - id int NOT NULL, - river_id int NOT NULL, - depth_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + river_id NUMERIC(9,0) NOT NULL, + depth_id NUMERIC(9,0) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, @@ -105,14 +107,15 @@ 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 int NOT NULL, - sediment_density_id int NOT NULL, - station NUMERIC NOT NULL, - shore_offset NUMERIC, - density NUMERIC NOT NULL, + 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, description VARCHAR(256), - year int, + year NUMERIC(4,0), PRIMARY KEY(id), CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE ); @@ -121,11 +124,11 @@ CREATE SEQUENCE POROSITY_ID_SEQ; CREATE TABLE porosity ( - id int NOT NULL, - river_id int NOT NULL, - depth_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + river_id NUMERIC(9,0) NOT NULL, + depth_id NUMERIC(9,0) NOT NULL, description VARCHAR(256), - time_interval_id int NOT NULL, + time_interval_id NUMERIC(9,0) 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), @@ -135,12 +138,13 @@ 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 int NOT NULL, - porosity_id int NOT NULL, - station NUMERIC NOT NULL, - shore_offset NUMERIC, - porosity NUMERIC NOT NULL, + 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, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE @@ -150,9 +154,9 @@ CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; CREATE TABLE morphologic_width ( - id int NOT NULL, - river_id int NOT NULL, - unit_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + river_id NUMERIC(9,0) NOT NULL, + unit_id NUMERIC(9,0) 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) @@ -162,10 +166,10 @@ CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; CREATE TABLE morphologic_width_values ( - id int NOT NULL, - morphologic_width_id int NOT NULL, - station NUMERIC NOT NULL, - width NUMERIC NOT NULL, + 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, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE @@ -175,10 +179,10 @@ CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; CREATE TABLE discharge_zone ( - id int NOT NULL, - river_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + river_id NUMERIC(9,0) NOT NULL, gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance - value NUMERIC NOT NULL, + value NUMERIC(8,3) NOT NULL, lower_discharge VARCHAR(64) NOT NULL, upper_discharge VARCHAR(64), PRIMARY KEY(id), @@ -189,8 +193,8 @@ CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; CREATE TABLE flow_velocity_model ( - id int NOT NULL, - discharge_zone_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + discharge_zone_id NUMERIC(9,0) 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 @@ -200,13 +204,13 @@ CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; CREATE TABLE flow_velocity_model_values ( - 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, + 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, 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 @@ -217,8 +221,8 @@ CREATE SEQUENCE FV_MEASURE_ID_SEQ; CREATE TABLE flow_velocity_measurements ( - id int NOT NULL, - river_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + river_id NUMERIC(9,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE @@ -227,13 +231,13 @@ CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; CREATE TABLE flow_velocity_measure_values ( - 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, + 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, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE @@ -243,10 +247,10 @@ CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; CREATE TABLE grain_fraction ( - id int NOT NULL, + id NUMERIC(9,0) NOT NULL, name VARCHAR(64) NOT NULL, - lower NUMERIC, - upper NUMERIC, + lower NUMERIC(6,3), + upper NUMERIC(6,3), PRIMARY KEY (id), UNIQUE(name, lower, upper) ); @@ -265,7 +269,7 @@ --lookup table for sediment load kinds CREATE TABLE sediment_load_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official'); @@ -274,14 +278,14 @@ CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ; CREATE TABLE sediment_load_ls ( - 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, + 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), description VARCHAR(256), - kind int, + kind NUMERIC(9,0), PRIMARY KEY (id), CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, @@ -301,10 +305,10 @@ CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ; CREATE TABLE sediment_load_ls_values ( - id int NOT NULL, - sediment_load_ls_id int NOT NULL, - station NUMERIC NOT NULL, - value NUMERIC NOT NULL, + 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, PRIMARY KEY (id), CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id) REFERENCES sediment_load_ls(id) ON DELETE CASCADE @@ -313,10 +317,10 @@ CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; CREATE TABLE measurement_station ( - id int NOT NULL, - range_id int NOT NULL, - reference_gauge_id int, - time_interval_id int, + 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), name VARCHAR(256) NOT NULL, measurement_type VARCHAR(64) NOT NULL, riverside VARCHAR(16), @@ -338,12 +342,12 @@ CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; CREATE TABLE sediment_load ( - id int NOT NULL, - grain_fraction_id int NOT NULL, - time_interval_id int NOT NULL, - sq_time_interval_id int, + 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), description VARCHAR(256), - kind int, + kind NUMERIC(9,0), PRIMARY KEY (id), CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) REFERENCES sediment_load_kinds(id), @@ -359,9 +363,9 @@ CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; CREATE TABLE sediment_load_values ( - id int NOT NULL, - sediment_load_id int NOT NULL, - measurement_station_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + sediment_load_id NUMERIC(9,0) NOT NULL, + measurement_station_id NUMERIC(9,0) NOT NULL, value DOUBLE PRECISION NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) @@ -374,8 +378,8 @@ CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation ( - id int NOT NULL, - time_interval_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + time_interval_id NUMERIC(9,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) @@ -385,19 +389,20 @@ CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; +--FIXME: adjust precision and scale of the numerics CREATE TABLE sq_relation_value ( - id int NOT NULL, - sq_relation_id int NOT NULL, - measurement_station_id int NOT NULL, + id NUMERIC(9,0) NOT NULL, + sq_relation_id NUMERIC(9,0) NOT NULL, + measurement_station_id NUMERIC(9,0) NOT NULL, parameter VARCHAR(1) NOT NULL, - a NUMERIC NOT NULL, - b NUMERIC NOT NULL, - qmax NUMERIC NOT NULL, - rsq NUMERIC, - ntot int, - noutl int, - cferguson NUMERIC, - cduan NUMERIC, + 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), PRIMARY KEY (id), UNIQUE(sq_relation_id, measurement_station_id, parameter), CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) diff -r 11bf13cf0463 -r 4a6b6a3c279c backend/doc/schema/postgresql-spatial.sql --- a/backend/doc/schema/postgresql-spatial.sql Fri Mar 09 18:47:06 2018 +0100 +++ b/backend/doc/schema/postgresql-spatial.sql Tue Mar 13 09:55:53 2018 +0100 @@ -1,7 +1,10 @@ BEGIN; +--FIXME: Adjust precision and scale to reasonable numbers +--FIXME: Make precision and scale equal for all km columns in the database + CREATE TABLE axis_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); @@ -11,9 +14,9 @@ -- Geodaesie/Flussachse+km/achse CREATE SEQUENCE RIVER_AXES_ID_SEQ; CREATE TABLE river_axes ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -25,10 +28,10 @@ -- Geodaesie/Flussachse+km/km.shp CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; CREATE TABLE river_axes_km ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - km FLOAT8 NOT NULL, - fedstate_km FLOAT8, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + km NUMERIC(7,3) NOT NULL, + fedstate_km NUMERIC(7,3), name VARCHAR(64), path VARCHAR(256) ); @@ -38,7 +41,7 @@ --Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE TABLE cross_section_track_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); @@ -46,11 +49,11 @@ CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, - km FLOAT8 NOT NULL, - z FLOAT8 NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, + km NUMERIC(16,12) NOT NULL, + z NUMERIC(16,12) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -58,7 +61,7 @@ ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); CREATE TABLE building_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); @@ -69,12 +72,12 @@ -- Geodaesie/Bauwerke CREATE SEQUENCE BUILDINGS_ID_SEQ; CREATE TABLE buildings ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256), -- Name taken from attributes, name VARCHAR(256), -- The layername - km FLOAT8, - kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, + km NUMERIC(15,11), + kind_id NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, path VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); @@ -84,11 +87,11 @@ -- Geodaesie/Festpunkte/Festpunkte.shp CREATE SEQUENCE FIXPOINTS_ID_SEQ; CREATE TABLE fixpoints ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - x FLOAT8, - y FLOAT8, - km FLOAT8 NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + x NUMERIC(15,11), + y NUMERIC(15,11), + km NUMERIC(15,11) NOT NULL, HPGP VARCHAR(64), name VARCHAR(64), path VARCHAR(256) @@ -99,7 +102,7 @@ -- Hydrologie/Hydr. Grenzen/talaue.shp CREATE TABLE floodplain_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); @@ -107,9 +110,9 @@ CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -120,16 +123,16 @@ -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(64), - range_id INT REFERENCES ranges(id) ON DELETE CASCADE, - time_interval_id INT REFERENCES time_intervals(id), + range_id NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE, + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), projection VARCHAR(32), - srid int NOT NULL, + srid NUMERIC(9,0) NOT NULL, elevation_state VARCHAR(32), format VARCHAR(32), - border_break BOOLEAN NOT NULL DEFAULT FALSE, + border_break NUMERIC(1,0) NOT NULL DEFAULT 0, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) NOT NULL @@ -139,7 +142,7 @@ -- Static lookup tables for Hochwasserschutzanlagen CREATE TABLE hws_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); @@ -147,7 +150,7 @@ INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); CREATE TABLE fed_states ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(23) NOT NULL ); INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); @@ -170,19 +173,19 @@ --Hydrologie/HW-Schutzanlagen/*Linien.shp CREATE SEQUENCE HWS_LINES_ID_SEQ; CREATE TABLE hws_lines ( - id int PRIMARY KEY NOT NULL, - ogr_fid int, - kind_id int REFERENCES hws_kinds(id) DEFAULT 2, - fed_state_id int REFERENCES fed_states(id), - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + ogr_fid NUMERIC(9,0), + kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, + fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(256), path VARCHAR(256), - official INT DEFAULT 0, + official NUMERIC(9,0) DEFAULT 0, agency VARCHAR(256), range VARCHAR(256), - shore_side INT DEFAULT 0, + shore_side NUMERIC(9,0) DEFAULT 0, source VARCHAR(256), - status_date TIMESTAMP, + status_date TIMESTAMP(0), description VARCHAR(256) ); SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); @@ -193,25 +196,25 @@ --Hydrologie/HW-Schutzanlagen/*Punkte.shp CREATE SEQUENCE HWS_POINTS_ID_SEQ; CREATE TABLE hws_points ( - id int PRIMARY KEY NOT NULL, - ogr_fid int, - kind_id int REFERENCES hws_kinds(id) DEFAULT 2, - fed_state_id int REFERENCES fed_states(id), - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + ogr_fid NUMERIC(9,0), + kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, + fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR, path VARCHAR, - official INT DEFAULT 0, + official NUMERIC(9,0) DEFAULT 0, agency VARCHAR, range VARCHAR, - shore_side INT DEFAULT 0, + shore_side NUMERIC(9,0) DEFAULT 0, source VARCHAR, status_date VARCHAR, description VARCHAR, - freeboard FLOAT8, - dike_km FLOAT8, - z FLOAT8, - z_target FLOAT8, - rated_level FLOAT8 + freeboard NUMERIC(19,5), + dike_km NUMERIC(19,5), + z NUMERIC(19,5), + z_target NUMERIC(19,5), + rated_level NUMERIC(19,5) ); SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); @@ -220,7 +223,7 @@ -- --Hydrologie/UeSG CREATE TABLE floodmap_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name varchar(64) NOT NULL ); INSERT INTO floodmap_kinds VALUES (200, 'Messung'); @@ -231,14 +234,14 @@ CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name varchar(64) NOT NULL, - kind int NOT NULL REFERENCES floodmap_kinds(id), - diff FLOAT8, - count int, - area FLOAT8, - perimeter FLOAT8, + kind NUMERIC(9,0) NOT NULL REFERENCES floodmap_kinds(id), + diff NUMERIC(19,5), + count NUMERIC(9,0), + area NUMERIC(19,5), + perimeter NUMERIC(19,5), waterbody varchar(64), path VARCHAR(256), source varchar(64) @@ -247,7 +250,7 @@ ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); CREATE TABLE sectie_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -256,7 +259,7 @@ INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); CREATE TABLE sobek_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -264,7 +267,7 @@ INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); CREATE TABLE boundary_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); @@ -274,12 +277,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, - sectie int REFERENCES sectie_kinds(id), - sobek int REFERENCES sobek_kinds(id), + kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), + sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); @@ -288,12 +291,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, - sectie int REFERENCES sectie_kinds(id), - sobek int REFERENCES sobek_kinds(id), + kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), + sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); @@ -301,7 +304,7 @@ CREATE TABLE jetty_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); @@ -310,10 +313,10 @@ CREATE SEQUENCE JETTIES_ID_SEQ; CREATE TABLE jetties ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - kind_id int REFERENCES jetty_kinds(id), + kind_id NUMERIC(9,0) REFERENCES jetty_kinds(id), km FLOAT8, z FLOAT8 ); @@ -322,13 +325,13 @@ CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; CREATE TABLE flood_marks ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - km FLOAT8, - z FLOAT8, + km NUMERIC(7,3), + z NUMERIC(16,12), location VARCHAR(64), - year int + year NUMERIC(4,0) ); SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ'); diff -r 11bf13cf0463 -r 4a6b6a3c279c backend/doc/schema/postgresql.sql --- a/backend/doc/schema/postgresql.sql Fri Mar 09 18:47:06 2018 +0100 +++ b/backend/doc/schema/postgresql.sql Tue Mar 13 09:55:53 2018 +0100 @@ -3,7 +3,7 @@ CREATE SEQUENCE UNITS_ID_SEQ; CREATE TABLE units ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(32) NOT NULL UNIQUE ); @@ -11,7 +11,7 @@ -- Lookup table for optional matching with differing river names in SedDB -- Add name here and set rivers.seddb_name_id to id CREATE TABLE seddb_name ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL ); @@ -19,13 +19,13 @@ CREATE SEQUENCE RIVERS_ID_SEQ; CREATE TABLE rivers ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, model_uuid CHAR(36) UNIQUE, - official_number int8, + official_number NUMERIC(12,0), name VARCHAR(256) NOT NULL UNIQUE, - km_up int DEFAULT 0 NOT NULL, - wst_unit_id int NOT NULL REFERENCES units(id), - seddb_name_id int REFERENCES seddb_name(id), + km_up NUMERIC(1,0) DEFAULT 0 NOT NULL, + wst_unit_id NUMERIC(9,0) NOT NULL REFERENCES units(id), + seddb_name_id NUMERIC(9,0) REFERENCES seddb_name(id), CHECK(km_up IN(0,1)) ); @@ -33,18 +33,19 @@ CREATE SEQUENCE ATTRIBUTES_ID_SEQ; CREATE TABLE attributes ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, value VARCHAR(256) NOT NULL UNIQUE ); -- segments from/to at a river CREATE SEQUENCE RANGES_ID_SEQ; +--FIXME: make precision and scale of a and b columns equal with the km columns of the other tables CREATE TABLE ranges ( - id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, - a NUMERIC NOT NULL, - b NUMERIC, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + a NUMERIC(14,10) NOT NULL, + b NUMERIC(14,10), UNIQUE (river_id, a, b), CHECK (a < b) ); @@ -54,7 +55,7 @@ CREATE SEQUENCE POSITIONS_ID_SEQ; CREATE TABLE positions ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, value VARCHAR(256) NOT NULL UNIQUE ); @@ -62,16 +63,16 @@ CREATE SEQUENCE EDGES_ID_SEQ; CREATE TABLE edges ( - id int PRIMARY KEY NOT NULL, - top NUMERIC, - bottom NUMERIC + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + top NUMERIC(6,2), + bottom NUMERIC(6,2) ); -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; CREATE TABLE annotation_types ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); @@ -80,30 +81,31 @@ CREATE SEQUENCE ANNOTATIONS_ID_SEQ; CREATE TABLE annotations ( - id int PRIMARY KEY NOT NULL, - range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, - attribute_id int NOT NULL REFERENCES attributes(id), - position_id int REFERENCES positions(id), - edge_id int REFERENCES edges(id), - type_id int REFERENCES annotation_types(id) + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, + attribute_id NUMERIC(9,0) NOT NULL REFERENCES attributes(id), + position_id NUMERIC(9,0) REFERENCES positions(id), + edge_id NUMERIC(9,0) REFERENCES edges(id), + type_id NUMERIC(9,0) REFERENCES annotation_types(id) ); -- Pegel CREATE SEQUENCE GAUGES_ID_SEQ; +--FIXME: make precision and scale of station column equal with the km columns of the other tables CREATE TABLE gauges ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, -- remove river id here because range_id references river already - river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, - station NUMERIC NOT NULL, - aeo NUMERIC NOT NULL, - official_number int8, + river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + station NUMERIC(8,4) NOT NULL, + aeo NUMERIC(9,2) NOT NULL, + official_number NUMERIC(12,0), -- Pegelnullpunkt - datum NUMERIC NOT NULL, + datum NUMERIC(6,2) NOT NULL, -- Streckengueltigkeit - range_id int REFERENCES ranges (id) ON DELETE CASCADE, + range_id NUMERIC(9,0) REFERENCES ranges (id) ON DELETE CASCADE, UNIQUE (name, river_id), UNIQUE (official_number, river_id), @@ -114,7 +116,7 @@ CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; CREATE TABLE main_value_types ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); @@ -122,18 +124,18 @@ CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; CREATE TABLE named_main_values ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, - type_id int NOT NULL REFERENCES main_value_types(id) + type_id NUMERIC(9,0) NOT NULL REFERENCES main_value_types(id) ); -- Table for time intervals CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; CREATE TABLE time_intervals ( - id int PRIMARY KEY NOT NULL, - start_time TIMESTAMP NOT NULL, - stop_time TIMESTAMP, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + start_time TIMESTAMP(0) NOT NULL, + stop_time TIMESTAMP(0), CHECK (start_time <= stop_time) ); @@ -142,12 +144,12 @@ CREATE SEQUENCE MAIN_VALUES_ID_SEQ; CREATE TABLE main_values ( - id int PRIMARY KEY NOT NULL, - gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, - named_value_id int NOT NULL REFERENCES named_main_values(id), - value NUMERIC NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, + named_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id), + value NUMERIC(12,2) NOT NULL, - time_interval_id int REFERENCES time_intervals(id), + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), -- TODO: better checks UNIQUE (gauge_id, named_value_id, time_interval_id) @@ -157,12 +159,12 @@ CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; CREATE TABLE discharge_tables ( - id int PRIMARY KEY NOT NULL, - gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL, bfg_id VARCHAR(50), - kind int NOT NULL DEFAULT 0, - time_interval_id int REFERENCES time_intervals(id), + kind NUMERIC(9,0) NOT NULL DEFAULT 0, + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), UNIQUE(gauge_id, bfg_id, kind) ); @@ -170,10 +172,10 @@ CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; CREATE TABLE discharge_table_values ( - id int PRIMARY KEY NOT NULL, - table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE, - q NUMERIC NOT NULL, - w NUMERIC NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + table_id NUMERIC(9,0) NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE, + q NUMERIC(9,4) NOT NULL, + w NUMERIC(6,2) NOT NULL, UNIQUE (table_id, q, w) ); @@ -181,7 +183,7 @@ -- WST files --lookup table for wst kinds CREATE TABLE wst_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); @@ -197,10 +199,10 @@ CREATE SEQUENCE WSTS_ID_SEQ; CREATE TABLE wsts ( - id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL, - kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, + kind NUMERIC(9,0) NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, -- TODO: more meta infos UNIQUE (river_id, description) ); @@ -209,14 +211,14 @@ CREATE SEQUENCE WST_COLUMNS_ID_SEQ; CREATE TABLE wst_columns ( - id int PRIMARY KEY NOT NULL, - wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + wst_id NUMERIC(9,0) NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, name VARCHAR(256) NOT NULL, description VARCHAR(256), source VARCHAR(256), - position int NOT NULL DEFAULT 0, + position NUMERIC(9,0) NOT NULL DEFAULT 0, - time_interval_id int REFERENCES time_intervals(id), + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), UNIQUE (wst_id, name), UNIQUE (wst_id, position) @@ -226,10 +228,10 @@ CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; CREATE TABLE wst_column_values ( - id int PRIMARY KEY NOT NULL, - wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, - position NUMERIC NOT NULL, - w NUMERIC NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, + position NUMERIC(9,5) NOT NULL, + w NUMERIC(9,5) NOT NULL, UNIQUE (position, wst_column_id), UNIQUE (position, wst_column_id, w) @@ -239,18 +241,18 @@ CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; CREATE TABLE wst_q_ranges ( - id int PRIMARY KEY NOT NULL, - range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, - q NUMERIC NOT NULL + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, + q NUMERIC(10,5) NOT NULL ); -- bind q ranges to wst columns CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; CREATE TABLE wst_column_q_ranges ( - id int PRIMARY KEY NOT NULL, - wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, - wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, + wst_q_range_id NUMERIC(9,0) NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, UNIQUE (wst_column_id, wst_q_range_id) ); @@ -258,9 +260,9 @@ CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; CREATE TABLE official_lines ( - id int PRIMARY KEY NOT NULL, - wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, - named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, + named_main_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, UNIQUE (wst_column_id, named_main_value_id) ); @@ -315,29 +317,29 @@ CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; CREATE TABLE cross_sections ( - id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, - time_interval_id int REFERENCES time_intervals(id), + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), description VARCHAR(256) ); CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; CREATE TABLE cross_section_lines ( - id int PRIMARY KEY NOT NULL, - km NUMERIC NOT NULL, - cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + km NUMERIC(9,5) NOT NULL, + cross_section_id NUMERIC(9,0) NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE, UNIQUE (km, cross_section_id) ); CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; CREATE TABLE cross_section_points ( - id int PRIMARY KEY NOT NULL, - cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE, - col_pos int NOT NULL, - x NUMERIC NOT NULL, - y NUMERIC NOT NULL + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + cross_section_line_id NUMERIC(9,0) NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE, + col_pos NUMERIC(9,0) NOT NULL, + x NUMERIC(7,2) NOT NULL, + y NUMERIC(7,2) NOT NULL ); -- Indices for faster access of the points @@ -351,39 +353,40 @@ CREATE SEQUENCE HYKS_ID_SEQ; CREATE TABLE hyks ( - id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL ); CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; +--FIXME: make precision and scale of km column equal with the km columns of the other tables CREATE TABLE hyk_entries ( - id int PRIMARY KEY NOT NULL, - hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, - km NUMERIC NOT NULL, - measure TIMESTAMP, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + hyk_id NUMERIC(9,0) NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, + km NUMERIC(7,2) NOT NULL, + measure TIMESTAMP(0), UNIQUE (hyk_id, km) ); CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; CREATE TABLE hyk_formations ( - id int PRIMARY KEY NOT NULL, - formation_num int NOT NULL DEFAULT 0, - hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE, - top NUMERIC NOT NULL, - bottom NUMERIC NOT NULL, - distance_vl NUMERIC NOT NULL, - distance_hf NUMERIC NOT NULL, - distance_vr NUMERIC NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + formation_num NUMERIC(9,0) NOT NULL DEFAULT 0, + hyk_entry_id NUMERIC(9,0) NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE, + top NUMERIC(6,2) NOT NULL, + bottom NUMERIC(6,2) NOT NULL, + distance_vl NUMERIC(8,2) NOT NULL, + distance_hf NUMERIC(8,2) NOT NULL, + distance_vr NUMERIC(8,2) NOT NULL, UNIQUE (hyk_entry_id, formation_num) ); CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; CREATE TABLE hyk_flow_zone_types ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(256) ); @@ -391,11 +394,11 @@ CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; CREATE TABLE hyk_flow_zones ( - id int PRIMARY KEY NOT NULL, - formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE, - type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), - a NUMERIC NOT NULL, - b NUMERIC NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + formation_id NUMERIC(9,0) NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE, + type_id NUMERIC(9,0) NOT NULL REFERENCES hyk_flow_zone_types(id), + a NUMERIC(7,2) NOT NULL, + b NUMERIC(7,2) NOT NULL, CHECK (a <= b) );