Mercurial > dive4elements > river
changeset 8965:f89fb9e9abad
Datatype changes from rev 8942 reverted
| author | mschaefer |
|---|---|
| date | Tue, 03 Apr 2018 08:26:54 +0200 |
| parents | 45f1ad66560e |
| children | 5b23efe99e80 |
| files | backend/doc/schema/postgresql-minfo.sql backend/doc/schema/postgresql-setup.sh backend/doc/schema/postgresql-spatial.sql backend/doc/schema/postgresql.sql |
| diffstat | 4 files changed, 299 insertions(+), 306 deletions(-) [+] |
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)
--- a/backend/doc/schema/postgresql-setup.sh Thu Mar 29 15:48:17 2018 +0200 +++ b/backend/doc/schema/postgresql-setup.sh Tue Apr 03 08:26:54 2018 +0200 @@ -29,3 +29,4 @@ psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql.sql psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-spatial.sql psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-minfo.sql +psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-sinfo-uinfo.sql
--- a/backend/doc/schema/postgresql-spatial.sql Thu Mar 29 15:48:17 2018 +0200 +++ b/backend/doc/schema/postgresql-spatial.sql Tue Apr 03 08:26:54 2018 +0200 @@ -1,10 +1,7 @@ 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 NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); @@ -14,9 +11,9 @@ -- Geodaesie/Flussachse+km/achse CREATE SEQUENCE RIVER_AXES_ID_SEQ; CREATE TABLE river_axes ( - 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, + 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, name VARCHAR(64), path VARCHAR(256) ); @@ -28,10 +25,10 @@ -- Geodaesie/Flussachse+km/km.shp CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; CREATE TABLE river_axes_km ( - 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), + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, + km FLOAT8 NOT NULL, + fedstate_km FLOAT8, name VARCHAR(64), path VARCHAR(256) ); @@ -41,7 +38,7 @@ --Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE TABLE cross_section_track_kinds( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); @@ -49,11 +46,11 @@ CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( - 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, + 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, name VARCHAR(64), path VARCHAR(256) ); @@ -61,7 +58,7 @@ ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); CREATE TABLE building_kinds( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); @@ -72,12 +69,12 @@ -- Geodaesie/Bauwerke CREATE SEQUENCE BUILDINGS_ID_SEQ; CREATE TABLE buildings ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256), -- Name taken from attributes, name VARCHAR(256), -- The layername - km NUMERIC(15,11), - kind_id NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, + km FLOAT8, + kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, path VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); @@ -87,11 +84,11 @@ -- Geodaesie/Festpunkte/Festpunkte.shp CREATE SEQUENCE FIXPOINTS_ID_SEQ; CREATE TABLE fixpoints ( - 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, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, + x FLOAT8, + y FLOAT8, + km FLOAT8 NOT NULL, HPGP VARCHAR(64), name VARCHAR(64), path VARCHAR(256) @@ -102,7 +99,7 @@ -- Hydrologie/Hydr. Grenzen/talaue.shp CREATE TABLE floodplain_kinds( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); @@ -110,9 +107,9 @@ CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain ( - 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, + 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, name VARCHAR(64), path VARCHAR(256) ); @@ -123,16 +120,16 @@ -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(64), - range_id NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE, - time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), + range_id INT REFERENCES ranges(id) ON DELETE CASCADE, + time_interval_id INT REFERENCES time_intervals(id), projection VARCHAR(32), - srid NUMERIC(9,0) NOT NULL, + srid int NOT NULL, elevation_state VARCHAR(32), format VARCHAR(32), - border_break NUMERIC(1,0) NOT NULL DEFAULT 0, + border_break BOOLEAN NOT NULL DEFAULT FALSE, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) NOT NULL @@ -142,7 +139,7 @@ -- Static lookup tables for Hochwasserschutzanlagen CREATE TABLE hws_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); @@ -150,7 +147,7 @@ INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); CREATE TABLE fed_states ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(23) NOT NULL ); INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); @@ -173,19 +170,19 @@ --Hydrologie/HW-Schutzanlagen/*Linien.shp CREATE SEQUENCE HWS_LINES_ID_SEQ; CREATE TABLE hws_lines ( - 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, + 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, name VARCHAR(256), path VARCHAR(256), - official NUMERIC(9,0) DEFAULT 0, + official INT DEFAULT 0, agency VARCHAR(256), range VARCHAR(256), - shore_side NUMERIC(9,0) DEFAULT 0, + shore_side INT DEFAULT 0, source VARCHAR(256), - status_date TIMESTAMP(0), + status_date TIMESTAMP, description VARCHAR(256) ); SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); @@ -196,25 +193,25 @@ --Hydrologie/HW-Schutzanlagen/*Punkte.shp CREATE SEQUENCE HWS_POINTS_ID_SEQ; CREATE TABLE hws_points ( - 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, + 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, name VARCHAR, path VARCHAR, - official NUMERIC(9,0) DEFAULT 0, + official INT DEFAULT 0, agency VARCHAR, range VARCHAR, - shore_side NUMERIC(9,0) DEFAULT 0, + shore_side INT DEFAULT 0, source VARCHAR, status_date VARCHAR, description VARCHAR, - freeboard NUMERIC(19,5), - dike_km NUMERIC(19,5), - z NUMERIC(19,5), - z_target NUMERIC(19,5), - rated_level NUMERIC(19,5) + freeboard FLOAT8, + dike_km FLOAT8, + z FLOAT8, + z_target FLOAT8, + rated_level FLOAT8 ); SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); @@ -223,7 +220,7 @@ -- --Hydrologie/UeSG CREATE TABLE floodmap_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name varchar(64) NOT NULL ); INSERT INTO floodmap_kinds VALUES (200, 'Messung'); @@ -234,14 +231,14 @@ CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, name varchar(64) NOT NULL, - 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), + kind int NOT NULL REFERENCES floodmap_kinds(id), + diff FLOAT8, + count int, + area FLOAT8, + perimeter FLOAT8, waterbody varchar(64), path VARCHAR(256), source varchar(64) @@ -250,7 +247,7 @@ ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); CREATE TABLE sectie_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -259,7 +256,7 @@ INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); CREATE TABLE sobek_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -267,7 +264,7 @@ INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); CREATE TABLE boundary_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); @@ -277,12 +274,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - 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), + kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie int REFERENCES sectie_kinds(id), + sobek int REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); @@ -291,12 +288,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - 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), + kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie int REFERENCES sectie_kinds(id), + sobek int REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); @@ -304,7 +301,7 @@ CREATE TABLE jetty_kinds( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); @@ -313,10 +310,10 @@ CREATE SEQUENCE JETTIES_ID_SEQ; CREATE TABLE jetties ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - kind_id NUMERIC(9,0) REFERENCES jetty_kinds(id), + kind_id int REFERENCES jetty_kinds(id), km FLOAT8, z FLOAT8 ); @@ -325,13 +322,13 @@ CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; CREATE TABLE flood_marks ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - km NUMERIC(7,3), - z NUMERIC(16,12), + km FLOAT8, + z FLOAT8, location VARCHAR(64), - year NUMERIC(4,0) + year int ); SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');
--- a/backend/doc/schema/postgresql.sql Thu Mar 29 15:48:17 2018 +0200 +++ b/backend/doc/schema/postgresql.sql Tue Apr 03 08:26:54 2018 +0200 @@ -3,7 +3,7 @@ CREATE SEQUENCE UNITS_ID_SEQ; CREATE TABLE units ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int 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 NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL ); @@ -19,13 +19,13 @@ CREATE SEQUENCE RIVERS_ID_SEQ; CREATE TABLE rivers ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, model_uuid CHAR(36) UNIQUE, - official_number NUMERIC(12,0), + official_number int8, name VARCHAR(256) NOT NULL UNIQUE, - 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), + km_up int DEFAULT 0 NOT NULL, + wst_unit_id int NOT NULL REFERENCES units(id), + seddb_name_id int REFERENCES seddb_name(id), CHECK(km_up IN(0,1)) ); @@ -33,19 +33,18 @@ CREATE SEQUENCE ATTRIBUTES_ID_SEQ; CREATE TABLE attributes ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int 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 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), + id int PRIMARY KEY NOT NULL, + river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + a NUMERIC NOT NULL, + b NUMERIC, UNIQUE (river_id, a, b), CHECK (a < b) ); @@ -55,7 +54,7 @@ CREATE SEQUENCE POSITIONS_ID_SEQ; CREATE TABLE positions ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, value VARCHAR(256) NOT NULL UNIQUE ); @@ -63,16 +62,16 @@ CREATE SEQUENCE EDGES_ID_SEQ; CREATE TABLE edges ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - top NUMERIC(6,2), - bottom NUMERIC(6,2) + id int PRIMARY KEY NOT NULL, + top NUMERIC, + bottom NUMERIC ); -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; CREATE TABLE annotation_types ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); @@ -81,31 +80,30 @@ CREATE SEQUENCE ANNOTATIONS_ID_SEQ; CREATE TABLE annotations ( - 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) + 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) ); -- 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 NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, -- remove river id here because range_id references river already - 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), + river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + station NUMERIC NOT NULL, + aeo NUMERIC NOT NULL, + official_number int8, -- Pegelnullpunkt - datum NUMERIC(6,2) NOT NULL, + datum NUMERIC NOT NULL, -- Streckengueltigkeit - range_id NUMERIC(9,0) REFERENCES ranges (id) ON DELETE CASCADE, + range_id int REFERENCES ranges (id) ON DELETE CASCADE, UNIQUE (name, river_id), UNIQUE (official_number, river_id), @@ -116,7 +114,7 @@ CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; CREATE TABLE main_value_types ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); @@ -124,18 +122,18 @@ CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; CREATE TABLE named_main_values ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, - type_id NUMERIC(9,0) NOT NULL REFERENCES main_value_types(id) + type_id int NOT NULL REFERENCES main_value_types(id) ); -- Table for time intervals CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; CREATE TABLE time_intervals ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - start_time TIMESTAMP(0) NOT NULL, - stop_time TIMESTAMP(0), + id int PRIMARY KEY NOT NULL, + start_time TIMESTAMP NOT NULL, + stop_time TIMESTAMP, CHECK (start_time <= stop_time) ); @@ -144,12 +142,12 @@ CREATE SEQUENCE MAIN_VALUES_ID_SEQ; CREATE TABLE main_values ( - 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, + 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, - time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), + time_interval_id int REFERENCES time_intervals(id), -- TODO: better checks UNIQUE (gauge_id, named_value_id, time_interval_id) @@ -159,12 +157,12 @@ CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; CREATE TABLE discharge_tables ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL, bfg_id VARCHAR(50), - kind NUMERIC(9,0) NOT NULL DEFAULT 0, - time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), + kind int NOT NULL DEFAULT 0, + time_interval_id int REFERENCES time_intervals(id), UNIQUE(gauge_id, bfg_id, kind) ); @@ -172,10 +170,10 @@ CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; CREATE TABLE discharge_table_values ( - 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, + 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, UNIQUE (table_id, q, w) ); @@ -183,7 +181,7 @@ -- WST files --lookup table for wst kinds CREATE TABLE wst_kinds ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); @@ -199,10 +197,10 @@ CREATE SEQUENCE WSTS_ID_SEQ; CREATE TABLE wsts ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL, - kind NUMERIC(9,0) NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, + kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, -- TODO: more meta infos UNIQUE (river_id, description) ); @@ -211,14 +209,14 @@ CREATE SEQUENCE WST_COLUMNS_ID_SEQ; CREATE TABLE wst_columns ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - wst_id NUMERIC(9,0) NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, name VARCHAR(256) NOT NULL, description VARCHAR(256), source VARCHAR(256), - position NUMERIC(9,0) NOT NULL DEFAULT 0, + position int NOT NULL DEFAULT 0, - time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), + time_interval_id int REFERENCES time_intervals(id), UNIQUE (wst_id, name), UNIQUE (wst_id, position) @@ -228,10 +226,10 @@ CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; CREATE TABLE wst_column_values ( - 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, + 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, UNIQUE (position, wst_column_id), UNIQUE (position, wst_column_id, w) @@ -241,18 +239,18 @@ CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; CREATE TABLE wst_q_ranges ( - 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 + id int PRIMARY KEY NOT NULL, + range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, + q NUMERIC NOT NULL ); -- bind q ranges to wst columns CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; CREATE TABLE wst_column_q_ranges ( - 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, + 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, UNIQUE (wst_column_id, wst_q_range_id) ); @@ -260,9 +258,9 @@ CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; CREATE TABLE official_lines ( - 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, + 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, UNIQUE (wst_column_id, named_main_value_id) ); @@ -317,29 +315,29 @@ CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; CREATE TABLE cross_sections ( - 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), + 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), description VARCHAR(256) ); CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; CREATE TABLE cross_section_lines ( - 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, + id int PRIMARY KEY NOT NULL, + km NUMERIC NOT NULL, + cross_section_id int 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 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 + 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 ); -- Indices for faster access of the points @@ -353,40 +351,39 @@ CREATE SEQUENCE HYKS_ID_SEQ; CREATE TABLE hyks ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, - river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, + id int PRIMARY KEY NOT NULL, + river_id int 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 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), + id int PRIMARY KEY NOT NULL, + hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, + km NUMERIC NOT NULL, + measure TIMESTAMP, UNIQUE (hyk_id, km) ); CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; CREATE TABLE hyk_formations ( - 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, + 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, UNIQUE (hyk_entry_id, formation_num) ); CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; CREATE TABLE hyk_flow_zone_types ( - id NUMERIC(9,0) PRIMARY KEY NOT NULL, + id int PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(256) ); @@ -394,11 +391,11 @@ CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; CREATE TABLE hyk_flow_zones ( - 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, + 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, CHECK (a <= b) );