Mercurial > dive4elements > river
diff backend/doc/schema/postgresql.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.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) );