lada/lada-server: db_schema/stammdaten

annotate db_schema/stammdaten_schema.sql @ 1191:3923b41f8783

reorganize db setup

author	Michael Stanko <mstanko@bfs.de>
date	Fri, 25 Nov 2016 22:25:13 +0100
parents	1bc8ab13e1f7
children	289edf060550

rev	line source
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1 \set ON_ERROR_STOP on
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	2
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	3 BEGIN;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	4
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	5 SET statement_timeout = 0;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	6 SET lock_timeout = 0;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	7 SET client_encoding = 'UTF8';
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	8 SET standard_conforming_strings = on;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	9 SET check_function_bodies = false;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	10 SET client_min_messages = warning;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	11
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	12
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	13 CREATE SCHEMA stammdaten;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	14
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	15 SET search_path = stammdaten, pg_catalog;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	16
1175 60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	17 CREATE FUNCTION set_ort_id() RETURNS trigger
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	18 LANGUAGE plpgsql
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	19 AS $$
1179 612107caf961 Declared variable in db function set_ort_id. Raimund Renkert <raimund.renkert@intevation.de> parents: 1176 diff changeset	20 DECLARE value text;
1175 60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	21 BEGIN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	22 value = '#'::text \|\| lpad((NEW.id::character varying)::text, 9, '0'::text);
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	23 IF NEW.ort_id IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	24 NEW.ort_id = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	25 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	26 IF NEW.langtext IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	27 NEW.langtext = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	28 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	29 IF NEW.kurztext IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	30 NEW.kurztext = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	31 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	32 RETURN NEW;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	33 END;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	34 $$;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	35
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	36 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	37 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	38 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	39 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	40 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	41 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	42 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	43 $$;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	44
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	45 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	46 LANGUAGE plpgsql
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	47 AS $$
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	48 declare
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	49 result character varying(100);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	50 d00 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	51 d01 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	52 d02 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	53 d03 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	54 begin
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	55 if media_desk like 'D: %' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 d00 := substring(media_desk,4,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 d01 := substring(media_desk,7,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	58 d02 := substring(media_desk,10,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 d03 := substring(media_desk,13,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	60 if d00 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	61 result := null;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	62 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	63 if d01 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 where s00.ebene = 0 and s00.sn = d00::smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	66 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 if d02 = '00' or d00 <> '01' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	68 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	70 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	72 where s00.ebene = 0 and s00.sn = d00::smallint);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	74 if d03 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	75 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	76 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	77 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	78 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	79 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	80 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	81 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	82 where s00.ebene = 0 and s00.sn = d00::smallint));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	83 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	84 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	85 where s03.ebene = 3 and s03.sn = d03::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	86 and s03.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	87 (select s02.id FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	88 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	89 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	90 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	91 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	92 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	93 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	94 where s00.ebene = 0 and s00.sn = d00::smallint)));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	95 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	96 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	97 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	98 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	99 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	100 result := null;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	101 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	102 return (result);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	103 end;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	104 $$;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	105
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	106
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	107 CREATE TABLE koordinaten_art (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	108 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	109 koordinatenart character varying(50),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	110 idf_geo_key character varying(1)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	111 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	112
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	113
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	114 CREATE TABLE mess_einheit (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	115 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	116 beschreibung character varying(50),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	117 einheit character varying(12),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	118 eudf_messeinheit_id character varying(8),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	119 umrechnungs_faktor_eudf bigint
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	120 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	121
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	122
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	123 CREATE TABLE umwelt (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	124 id character varying(3) PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	125 beschreibung character varying(300),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	126 umwelt_bereich character varying(80) NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	127 meh_id integer REFERENCES mess_einheit,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	128 UNIQUE (umwelt_bereich)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	129 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	130
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	131
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	132 CREATE TABLE betriebsart (
1119 d1d7d684800c Switch back to 1-based id of betriebsart. Tom Gottfried <tom@intevation.de> parents: 1115 diff changeset	133 id smallint PRIMARY KEY,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	134 name character varying(30) NOT NULL
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	135 );
1119 d1d7d684800c Switch back to 1-based id of betriebsart. Tom Gottfried <tom@intevation.de> parents: 1115 diff changeset	136 INSERT INTO betriebsart VALUES(1, 'Normal-/Routinebetrieb');
d1d7d684800c Switch back to 1-based id of betriebsart. Tom Gottfried <tom@intevation.de> parents: 1115 diff changeset	137 INSERT INTO betriebsart VALUES(2, 'Störfall-/Intensivbetrieb');
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	138
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	139
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	140 CREATE TABLE staat (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	141 id serial PRIMARY KEY,
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	142 staat character varying(50) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	143 hkl_id smallint NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	144 staat_iso character varying(2) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	145 staat_kurz character varying(5),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	146 eu character(1) DEFAULT NULL::bpchar,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	147 koord_x_extern character varying(22),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	148 koord_y_extern character varying(22),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	149 kda_id integer REFERENCES koordinaten_art
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	150 );
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	151
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	152 CREATE TABLE verwaltungseinheit (
1188 1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	153 id character varying(8) NOT NULL PRIMARY KEY,
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	154 bezeichnung character varying(80) NOT NULL,
1188 1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	155 regbezirk character varying(8),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	156 kreis character varying(8),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	157 bundesland character varying(8) NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	158 is_gemeinde boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	159 is_landkreis boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	160 is_regbezirk boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	161 is_bundesland boolean DEFAULT false NOT NULL,
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	162 plz character varying(6),
1188 1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	163 nuts character varying(10),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	164 mittelpunkt public.geometry(Point)
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	165 );
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	166
1188 1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	167 CREATE TABLE verwaltungsgrenze (
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	168 id serial PRIMARY KEY,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	169 gem_id character varying(8) NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	170 shape public.geometry(MultiPolygon)
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit Michael Stanko <mstanko@bfs.de> parents: 1179 diff changeset	171 );
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	172
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	173 CREATE TABLE netz_betreiber (
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	174 id character varying(2) PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	175 netzbetreiber character varying(50),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	176 idf_netzbetreiber character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	177 is_bmn boolean DEFAULT false,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	178 mailverteiler character varying(512),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	179 aktiv boolean DEFAULT false
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	180 );
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	181
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	182
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	183 CREATE TABLE mess_stelle (
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	184 id character varying(5) PRIMARY KEY,
968 69b954343acc Add missing FK-constraint. Tom Gottfried <tom@intevation.de> parents: 965 diff changeset	185 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	186 beschreibung character varying(300),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	187 mess_stelle character varying(60),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	188 mst_typ character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	189 amtskennung character varying(6)
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	190 );
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	191
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	192
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	193 CREATE TABLE auth_funktion (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	194 id smallint PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	195 funktion character varying(40) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	196 );
869 fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	197 INSERT INTO auth_funktion VALUES (0, 'Erfasser');
fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	198 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser');
fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	199 INSERT INTO auth_funktion VALUES (2, 'Status-Land');
fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	200 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle');
fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	201 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	202
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	203
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	204 CREATE TABLE auth (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	205 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	206 ldap_group character varying(40) NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	207 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	208 mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	209 labor_mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	210 funktion_id smallint REFERENCES auth_funktion
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	211 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	212
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	213
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	214 CREATE TABLE auth_lst_umw (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	215 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	216 mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	217 umw_id character varying(3) REFERENCES umwelt
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	218 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	219
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	220
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	221 CREATE TABLE datenbasis (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	222 id serial PRIMARY KEY,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	223 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	224 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	225 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	226
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	227
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	228 CREATE TABLE datensatz_erzeuger (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	229 id serial PRIMARY KEY,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	230 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	231 datensatz_erzeuger_id character varying(2) NOT NULL,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	232 mst_id character varying(5) NOT NULL REFERENCES mess_stelle,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	233 bezeichnung character varying(120) NOT NULL,
905 df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber. Tom Gottfried <tom@intevation.de> parents: 904 diff changeset	234 letzte_aenderung timestamp without time zone,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	235 UNIQUE(datensatz_erzeuger_id, netzbetreiber_id)
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	236 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	237 CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	238
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	239 CREATE TABLE de_vg (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	240 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	241 use double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	242 rs character varying(12),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	243 gf double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	244 rau_rs character varying(12),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	245 gen character varying(50),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	246 des character varying(75),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	247 isn double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	248 bemerk character varying(75),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	249 nambild character varying(16),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	250 ags character varying(12),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	251 rs_alt character varying(20),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	252 wirksamkei date,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	253 debkg_id character varying(16),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	254 length numeric,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	255 shape_area numeric,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	256 geom public.geometry(MultiPolygon,4326)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	257 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	258
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	259
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	260 CREATE TABLE deskriptor_umwelt (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	261 id serial PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	262 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	263 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	264 s02 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	265 s03 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	266 s04 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	267 s05 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	268 s06 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	269 s07 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	270 s08 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	271 s09 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	272 s10 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	273 s11 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	274 s12 integer,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	275 umw_id character varying(3) NOT NULL REFERENCES umwelt
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	276 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	277
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	278
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	279 CREATE TABLE deskriptoren (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	280 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	281 vorgaenger integer REFERENCES deskriptoren,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	282 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	283 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	284 sn smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	285 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	286 bedeutung character varying(300)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	287 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	288
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	289
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	290 CREATE TABLE lada_user (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	291 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	292 name character varying(80) NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	293 UNIQUE (name)
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	294 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	295
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	296
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	297 CREATE TABLE query_type (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	298 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	299 type character varying(30) NOT NULL
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	300 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	301 INSERT INTO query_type VALUES(0, 'probe');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	302 INSERT INTO query_type VALUES(1, 'messung');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	303 INSERT INTO query_type VALUES(2, 'messprogramm');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	304 INSERT INTO query_type VALUES(3, 'ort');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	305 INSERT INTO query_type VALUES(4, 'probenehmer');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	306 INSERT INTO query_type VALUES(5, 'datensatzerzeuger');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	307 INSERT INTO query_type VALUES(6, 'messprogrammkategorie');
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	308
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	309
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	310 CREATE TABLE query (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	311 id serial PRIMARY KEY,
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	312 name character varying(80) NOT NULL,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	313 type integer NOT NULL REFERENCES query_type,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	314 sql character varying(2500) NOT NULL,
1093 effef57f3f7e Complement and correct data model and docs for queries. Tom Gottfried <tom@intevation.de> parents: 1056 diff changeset	315 description character varying(100),
effef57f3f7e Complement and correct data model and docs for queries. Tom Gottfried <tom@intevation.de> parents: 1056 diff changeset	316 UNIQUE (name, type)
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	317 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	318
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	319
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	320 CREATE TABLE favorite (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	321 id serial PRIMARY KEY,
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	322 user_id integer NOT NULL REFERENCES lada_user,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	323 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	324 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	325
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	326
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	327 CREATE TABLE filter_type (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	328 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	329 type character varying(10) NOT NULL
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	330 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	331 INSERT INTO filter_type VALUES(0, 'text');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	332 INSERT INTO filter_type VALUES(1, 'listmst');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	333 INSERT INTO filter_type VALUES(2, 'listnetz');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	334 INSERT INTO filter_type VALUES(3, 'listumw');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	335 INSERT INTO filter_type VALUES(4, 'liststatus');
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	336 INSERT INTO filter_type VALUES(5, 'number');
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	337
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	338
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	339 CREATE TABLE filter (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	340 id serial PRIMARY KEY,
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	341 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	342 data_index character varying(50) NOT NULL,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	343 type integer NOT NULL REFERENCES filter_type,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	344 label character varying(50) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	345 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	346 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	347
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	348
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	349 CREATE TABLE filter_value (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	350 id serial PRIMARY KEY,
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	351 user_id integer NOT NULL REFERENCES lada_user,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	352 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	353 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	354 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	355
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	356
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	357 CREATE TABLE mess_methode (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	358 id character varying(2) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	359 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	360 messmethode character varying(50)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	361 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	362
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	363
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	364 CREATE TABLE messgroesse (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	365 id serial PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	366 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	367 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	368 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	369 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	370 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	371 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	372 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	373 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	374
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	375
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	376 CREATE TABLE messgroessen_gruppe (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	377 id serial PRIMARY KEY,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	378 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	379 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	380 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	381
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	382
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	383 CREATE TABLE messprogramm_kategorie (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	384 id serial PRIMARY KEY,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	385 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	386 code character varying(3) NOT NULL,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	387 bezeichnung character varying(120) NOT NULL,
905 df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber. Tom Gottfried <tom@intevation.de> parents: 904 diff changeset	388 letzte_aenderung timestamp without time zone,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	389 UNIQUE(code, netzbetreiber_id)
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	390 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	391 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	392
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	393
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	394 CREATE TABLE mg_grp (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	395 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	396 messgroesse_id integer NOT NULL REFERENCES messgroesse
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	397 );
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	398 ALTER TABLE ONLY mg_grp
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	399 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	400
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	401
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	402 CREATE TABLE mmt_messgroesse_grp (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	403 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	404 mmt_id character varying(2) NOT NULL REFERENCES mess_methode
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	405 );
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	406 ALTER TABLE ONLY mmt_messgroesse_grp
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	407 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	408
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	409
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	410
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	411
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	412 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	413 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	414 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	415 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	416 mg_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	417 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	418
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	419
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	420 CREATE TABLE ort_typ (
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	421 id smallint PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	422 ort_typ character varying(60)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	423 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	424
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	425 CREATE TABLE kta (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	426 id serial NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	427 code character varying(7),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	428 bezeichnung character varying(80),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	429 CONSTRAINT kta_pkey PRIMARY KEY (id)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	430 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	431 COMMENT ON TABLE kta
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	432 IS 'kernteschnische Anlagen';
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	433
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	434 CREATE TABLE ortszusatz (
1143 b49c1fb84040 Updated stammdaten schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 1119 diff changeset	435 ozs_id character varying(7) PRIMARY KEY,
b49c1fb84040 Updated stammdaten schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 1119 diff changeset	436 ortszusatz character varying(80) NOT NULL
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	437 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	438
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	439 CREATE TABLE ort (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	440 id serial PRIMARY KEY,
912 d8c66007fa14 netzbetreiber_id is mandatory for ort. Tom Gottfried <tom@intevation.de> parents: 905 diff changeset	441 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	442 ort_id character varying(10) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	443 langtext character varying(100) NOT NULL,
1176 33dc7dc6b7bc Removed NOT NULL constraint from stammdaten.ort.staat_id. Raimund Renkert <raimund.renkert@intevation.de> parents: 1175 diff changeset	444 staat_id smallint REFERENCES staat,
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	445 gem_id character varying(8) REFERENCES verwaltungseinheit,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	446 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	447 nuts_code character varying(10),
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	448 kda_id integer NOT NULL REFERENCES koordinaten_art,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	449 koord_x_extern character varying(22) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	450 koord_y_extern character varying(22) NOT NULL,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	451 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	452 letzte_aenderung timestamp without time zone DEFAULT now(),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	453 latitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	454 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	455 geom public.geometry(Point,4326),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	456 shape public.geometry(MultiPolygon,4326),
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	457 ort_typ smallint REFERENCES ort_typ,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	458 kurztext character varying(15) NOT NULL,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	459 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	460 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	461 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	462 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	463 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	464 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	465 anlage_id integer,
1143 b49c1fb84040 Updated stammdaten schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 1119 diff changeset	466 oz_id character varying(7) REFERENCES ortszusatz(ozs_id),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	467 hoehe_ueber_nn real,
905 df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber. Tom Gottfried <tom@intevation.de> parents: 904 diff changeset	468 UNIQUE(ort_id, netzbetreiber_id)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	469 );
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	470
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	471 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1175 60b91dbb98cc Added trigger to fill stammdaten.ort attributes. Raimund Renkert <raimund.renkert@intevation.de> parents: 1143 diff changeset	472 CREATE TRIGGER set_ort_id_ort BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE set_ort_id();
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	473
902 d465094946ef Improve readability of DDL for editable stammdaten tables. Tom Gottfried <tom@intevation.de> parents: 891 diff changeset	474 ALTER TABLE ONLY ort
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	475 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id);
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	476
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	477
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	478 CREATE TABLE ortszuordnung_typ (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	479 id character(1) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	480 ortstyp character varying(60)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	481 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	482
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	483
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	484 CREATE TABLE pflicht_messgroesse (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	485 id serial PRIMARY KEY,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	486 messgroesse_id integer,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	487 mmt_id character varying(2) REFERENCES mess_methode,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	488 umw_id character varying(3) REFERENCES umwelt,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	489 datenbasis_id smallint NOT NULL REFERENCES datenbasis
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	490 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	491
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	492
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	493 CREATE TABLE proben_zusatz (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	494 id character varying(3) PRIMARY KEY,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	495 meh_id integer REFERENCES mess_einheit,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	496 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	497 zusatzwert character varying(7) NOT NULL,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	498 eudf_keyword character varying(40),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	499 UNIQUE (eudf_keyword)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	500 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	501
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	502
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	503 CREATE TABLE probenart (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	504 id serial PRIMARY KEY,
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	505 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	506 probenart character varying(5) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	507 probenart_eudf_id character varying(1) NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	508 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	509
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	510
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	511 CREATE TABLE probenehmer (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	512 id serial PRIMARY KEY,
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	513 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	514 prn_id character varying(9) NOT NULL,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	515 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	516 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	517 betrieb character varying(80),
904 825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	518 bezeichnung character varying(80) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL. Tom Gottfried <tom@intevation.de> parents: 902 diff changeset	519 kurz_bezeichnung character varying(10) NOT NULL,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	520 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	521 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	522 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	523 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	524 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	525 typ character(1),
905 df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber. Tom Gottfried <tom@intevation.de> parents: 904 diff changeset	526 letzte_aenderung timestamp without time zone,
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber. Tom Gottfried <tom@intevation.de> parents: 904 diff changeset	527 UNIQUE(prn_id, netzbetreiber_id)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	528 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 912 diff changeset	529 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	530
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	531
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	532 CREATE TABLE result (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	533 id serial PRIMARY KEY,
954 379480a94c81 Use ON DELETE CASCADE to ease removing queries from database. Tom Gottfried <tom@intevation.de> parents: 926 diff changeset	534 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	535 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	536 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	537 width integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	538 flex boolean,
1093 effef57f3f7e Complement and correct data model and docs for queries. Tom Gottfried <tom@intevation.de> parents: 1056 diff changeset	539 index integer NOT NULL,
effef57f3f7e Complement and correct data model and docs for queries. Tom Gottfried <tom@intevation.de> parents: 1056 diff changeset	540 UNIQUE (query_id, index),
988 5efb32a41b24 Fix CHECK-Constraint definition. Tom Gottfried <tom@intevation.de> parents: 987 diff changeset	541 UNIQUE (query_id, data_index)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	542 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	543
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	544
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	545 -- Status workflow
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	546 CREATE TABLE status_stufe (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	547 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	548 stufe character varying(50) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	549 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	550 INSERT INTO status_stufe VALUES (1, 'MST');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	551 INSERT INTO status_stufe VALUES (2, 'LAND');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	552 INSERT INTO status_stufe VALUES (3, 'LST');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	553
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	554
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	555 CREATE TABLE status_wert (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	556 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	557 wert character varying(50) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	558 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	559 INSERT INTO status_wert VALUES (0, 'nicht vergeben');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	560 INSERT INTO status_wert VALUES (1, 'plausibel');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	561 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	562 INSERT INTO status_wert VALUES (3, 'nicht plausibel');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	563 INSERT INTO status_wert VALUES (4, 'Rückfrage');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	564 INSERT INTO status_wert VALUES (7, 'nicht lieferbar');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	565 INSERT INTO status_wert VALUES (8, 'zurückgesetzt');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	566
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	567
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	568 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	569 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	570 stufe_id integer REFERENCES status_stufe NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	571 wert_id integer REFERENCES status_wert NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	572 UNIQUE(stufe_id, wert_id)
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	573 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	574 INSERT INTO status_kombi VALUES (1, 1, 0);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	575 INSERT INTO status_kombi VALUES (2, 1, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	576 INSERT INTO status_kombi VALUES (3, 1, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	577 INSERT INTO status_kombi VALUES (4, 1, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	578 INSERT INTO status_kombi VALUES (5, 1, 7);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	579 INSERT INTO status_kombi VALUES (6, 2, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	580 INSERT INTO status_kombi VALUES (7, 2, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	581 INSERT INTO status_kombi VALUES (8, 2, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	582 INSERT INTO status_kombi VALUES (9, 2, 4);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	583 INSERT INTO status_kombi VALUES (10, 3, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	584 INSERT INTO status_kombi VALUES (11, 3, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	585 INSERT INTO status_kombi VALUES (12, 3, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	586 INSERT INTO status_kombi VALUES (13, 3, 4);
1115 2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	587 INSERT INTO status_kombi VALUES (14, 1, 8);
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	588 INSERT INTO status_kombi VALUES (15, 2, 8);
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	589 INSERT INTO status_kombi VALUES (16, 3, 8);
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	590
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	591
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	592 CREATE TABLE status_reihenfolge (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	593 id serial PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	594 von_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	595 zu_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	596 UNIQUE(von_id, zu_id)
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	597 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	598
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	599 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	600 DECLARE kombi_from RECORD;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	601 DECLARE s_from integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	602 DECLARE w_from integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	603 DECLARE kombi_to RECORD;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	604 DECLARE s_to integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	605 DECLARE w_to integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	606
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	607 BEGIN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	608 FOR kombi_from IN SELECT * FROM status_kombi LOOP
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	609 s_from := kombi_from.stufe_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	610 w_from := kombi_from.wert_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	611
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	612 FOR kombi_to IN SELECT * FROM status_kombi LOOP
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	613 s_to := kombi_to.stufe_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	614 w_to := kombi_to.wert_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	615
884 fa7278bd8d73 Allow 'editing' of status 'Rückfrage' Tom Gottfried <tom@intevation.de> parents: 879 diff changeset	616 IF s_from = s_to AND w_to <> 0 THEN
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	617 -- At the same 'stufe', all permutations occur,
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	618 -- but 'nicht vergeben' is only allowed for von_id
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	619 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	620 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	621
1115 2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	622 ELSEIF s_to = s_from + 1
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	623 AND w_from <> 0 AND w_from <> 4
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	624 AND w_from <> 8 AND w_to <> 8 THEN
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	625 -- Going to the next 'stufe' all available status_kombi are allowed
1115 2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	626 -- in case current wert is not 'nicht vergeben', 'Rückfrage' or
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	627 -- 'zurückgesetzt' and we are not trying to set 'zurückgesetzt'
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	628 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	629 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	630
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	631 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	632 -- After 'Rückfrage' follows 'MST' with
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	633 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ'
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	634 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	635 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	636
1115 2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	637 ELSEIF w_to = 8 AND s_from = s_to THEN
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	638 -- 'zurückgesetzt' can only be set on the same 'stufe'
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	639 INSERT INTO status_reihenfolge (von_id, zu_id)
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	640 VALUES (kombi_from.id, kombi_to.id);
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	641
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	642 ELSEIF w_from = 8 AND s_to = s_from - 1 THEN
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	643 -- after 'zurückgesetzt' always follows the next lower 'stufe'
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	644 INSERT INTO status_reihenfolge (von_id, zu_id)
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	645 VALUES (kombi_from.id, kombi_to.id);
2f42a7607bbd Client and server now expect these combinations to be modelled, too. Tom Gottfried <tom@intevation.de> parents: 1099 diff changeset	646
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	647 END IF;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	648 END LOOP;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	649 END LOOP;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	650 END;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	651 $$ LANGUAGE plpgsql;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	652
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	653 SELECT populate_status_reihenfolge();
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	654 DROP FUNCTION populate_status_reihenfolge();
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	655 ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	656 DROP SEQUENCE status_reihenfolge_id_seq;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	657
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	658
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	659 CREATE VIEW status_erreichbar AS (
879 6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	660 SELECT r.id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	661 zu.wert_id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	662 zu.stufe_id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	663 von.wert_id AS cur_wert,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	664 von.stufe_id AS cur_stufe
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	665 FROM stammdaten.status_reihenfolge r
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	666 JOIN stammdaten.status_kombi von
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	667 ON von.id = r.von_id
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	668 JOIN stammdaten.status_kombi zu
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	669 ON zu.id = r.zu_id
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	670 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	671 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	672
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	673 -- Mappings for import
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	674
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	675 CREATE TABLE messprogramm_transfer (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	676 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	677 messprogramm_s character varying(1) NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	678 messprogramm_c character varying(100) NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	679 ba_id integer NOT NULL REFERENCES betriebsart,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	680 UNIQUE (messprogramm_s)
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	681 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	682
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1093 diff changeset	683 -- Mappings for import
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	684
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	685 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	686
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	687
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	688 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1191:3923b41f8783