Mercurial > dive4elements > river
view flys-backend/doc/schema/oracle-minfo.sql @ 4214:e39ac9767a69
Implement UserService method to remove current user from session
For being able to logout a user UserService must provide a method the remove the
current user object from the session.
Part of flys/issue916 (Logout: "Abmelden" Knopf mit Funktion belegen)
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Tue, 23 Oct 2012 09:57:42 +0200 |
parents | fc32a4955087 |
children | 504cd5801785 |
line wrap: on
line source
SET AUTOCOMMIT ON; CREATE SEQUENCE LOCATION_SYSTEM_SEQ; CREATE TABLE location_system ( id NUMBER(38,0) NOT NULL, name VARCHAR(32) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) ); CREATE SEQUENCE ELEVATION_MODEL_SEQ; CREATE TABLE elevation_model ( id NUMBER(38,0) NOT NULL, name VARCHAR(32) NOT NULL, unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; CREATE TABLE bed_height_type ( id NUMBER(38,0) NOT NULL, name VARCHAR(16) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) ); CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; CREATE TABLE bed_height_single ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, year NUMBER(38,0) NOT NULL, sounding_width NUMBER(38,0) NOT NULL, type_id NUMBER(38,0) NOT NULL, location_system_id NUMBER(38,0) NOT NULL, cur_elevation_model_id NUMBER(38,0) NOT NULL, old_elevation_model_id NUMBER(38,0), range_id NUMBER(38,0) NOT NULL, evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ); CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; CREATE TABLE bed_height_epoch ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, -- sounding_with NUMBER(38,0) NOT NULL, -- type_id NUMBER(38,0) NOT NULL, cur_elevation_model_id NUMBER(38,0) NOT NULL, old_elevation_model_id NUMBER(38,0), range_id NUMBER(38,0) NOT NULL, evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ); CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; CREATE TABLE bed_height_single_values ( id NUMBER(38,0) NOT NULL, bed_height_single_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, height NUMBER(38,2), uncertainty NUMBER(38,2), data_gap NUMBER(38,2), sounding_width NUMBER(38,2), width NUMBER(38,2), PRIMARY KEY(id), CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ); CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; CREATE TABLE bed_height_epoch_values ( id NUMBER(38,0) NOT NULL, bed_height_epoch_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, height NUMBER(38,2), PRIMARY KEY(id), CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ); CREATE SEQUENCE DEPTHS_ID_SEQ; CREATE TABLE depths ( id NUMBER(38,0) NOT NULL, lower NUMBER(38,2) NOT NULL, upper NUMBER(38,2) NOT NULL, unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; CREATE TABLE sediment_density ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, depth_id NUMBER(38,0) NOT NULL, unit_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; CREATE TABLE sediment_density_values ( id NUMBER(38,0) NOT NULL, sediment_density_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, density NUMBER(38,2) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ); CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; CREATE TABLE morphologic_width ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) ); CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; CREATE TABLE morphologic_width_values ( id NUMBER(38,0) NOT NULL, morphologic_width_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, width NUMBER(38,3) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ); CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; CREATE TABLE discharge_zone ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance value NUMBER(38,3) NOT NULL, lower_discharge VARCHAR(16) NOT NULL, upper_discharge VARCHAR(16), PRIMARY KEY(id), CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ); CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; CREATE TABLE flow_velocity_model ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, discharge_zone_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ); CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; CREATE TABLE flow_velocity_model_values ( id NUMBER(38,0) NOT NULL, flow_velocity_model_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, q NUMBER(38,3) NOT NULL, total_channel NUMBER(38,3) NOT NULL, main_channel NUMBER(38,3) NOT NULL, shear_stress NUMBER(38,3) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ); CREATE SEQUENCE FV_MEASURE_ID_SEQ; CREATE TABLE flow_velocity_measurements ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ); CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; CREATE TABLE flow_velocity_measure_values ( id NUMBER(38,0) NOT NULL, measurements_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, datetime TIMESTAMP, w NUMBER(38,3) NOT NULL, q NUMBER(38,3) NOT NULL, v NUMBER(38,3) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ); CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; CREATE TABLE grain_fraction ( id NUMBER(38,0) NOT NULL, name VARCHAR(64) NOT NULL, lower NUMBER(38,3), upper NUMBER(38,3), unit_id NUMBER(38,0), PRIMARY KEY (id), CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; CREATE TABLE sediment_yield ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, grain_fraction_id NUMBER(38,0), unit_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ); CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; CREATE TABLE sediment_yield_values ( id NUMBER(38,0) NOT NULL, sediment_yield_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, value NUMBER(38,3) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ); CREATE SEQUENCE WATERLEVEL_ID_SEQ; CREATE TABLE waterlevel ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, unit_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; CREATE TABLE waterlevel_q_range ( id NUMBER(38,0) NOT NULL, waterlevel_id NUMBER(38,0) NOT NULL, q NUMBER(38,2) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ); CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; CREATE TABLE waterlevel_values ( id NUMBER(38,0) NOT NULL, waterlevel_q_range_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, w NUMBER(38,2) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ); CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; CREATE TABLE waterlevel_difference ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, unit_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id), CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; CREATE TABLE waterlevel_difference_column ( id NUMBER(38,0) NOT NULL, difference_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ); CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; CREATE TABLE waterlevel_difference_values ( id NUMBER(38,0) NOT NULL, column_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, value NUMBER(38,2) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ); CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ); CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; CREATE TABLE sq_relation_value ( id NUMBER(38,0) NOT NULL, sq_relation_id NUMBER(38,0) NOT NULL, parameter VARCHAR(16) NOT NULL, fraction VARCHAR(32) NOT NULL, function VARCHAR(32) NOT NULL, km NUMBER(38,3) NOT NULL, a NUMBER(38, 3) NOT NULL, b NUMBER(38,3) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) );