lada/lada-server: db_schema/stammdaten

annotate db_schema/stammdaten_schema.sql @ 1161:ea6b062e5305 pgaudit

Use pgaudit to generate an audit trail. Upgrade to PostgreSQL 9.5 because it is a requirement for pgaudit. pgaudit/analyze can be used to transfer the audit trail into the database, but it seems to be easy to do this with pgaudit directly with some changes to the code.

author	Tom Gottfried <tom@intevation.de>
date	Tue, 08 Nov 2016 19:21:24 +0100
parents	b49c1fb84040
children	60b91dbb98cc

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

Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1161:ea6b062e5305 pgaudit