lada/lada-server: db_schema/stammdaten

annotate db_schema/stammdaten_schema.sql @ 883:2e264556f186

Avoid NPE by rejecting invalid requests.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 12 Feb 2016 14:22:53 +0100
parents	6bd149c352a9
children	fa7278bd8d73

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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	17
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	18 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	19 LANGUAGE plpgsql
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	20 AS $$
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 declare
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	22 result character varying(100);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	23 d00 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	24 d01 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	25 d02 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	26 d03 smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	27 begin
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	28 if media_desk like 'D: %' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	29 d00 := substring(media_desk,4,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 d01 := substring(media_desk,7,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 d02 := substring(media_desk,10,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 d03 := substring(media_desk,13,2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 if d00 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	34 result := null;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	35 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	36 if d01 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	37 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	38 where s00.ebene = 0 and s00.sn = d00::smallint;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	39 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	40 if d02 = '00' or d00 <> '01' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	41 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	42 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	43 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	44 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	45 where s00.ebene = 0 and s00.sn = d00::smallint);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	46 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	47 if d03 = '00' then
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	48 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	49 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	50 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	51 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	52 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	53 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	54 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	55 where s00.ebene = 0 and s00.sn = d00::smallint));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	58 where s03.ebene = 3 and s03.sn = d03::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 and s03.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	60 (select s02.id FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	61 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	62 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	63 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	66 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 where s00.ebene = 0 and s00.sn = d00::smallint)));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	68 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	70 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	72 else
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 result := null;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	74 end if;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	75 return (result);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	76 end;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	77 $$;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	78
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	79
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	80 CREATE SEQUENCE auth_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	81 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	82 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	83 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	84 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	85 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	86
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	87 CREATE TABLE auth (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	88 id integer PRIMARY KEY DEFAULT nextval('auth_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	89 ldap_group character varying(40) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	90 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	91 mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	92 labor_mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	93 funktion_id smallint
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	94 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	95
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	96 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	97
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	98
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	99 CREATE TABLE auth_funktion (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	100 id smallint PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	101 funktion character varying(40) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	102 );
869 fc8349057de1 Functions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 868 diff changeset	103 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	104 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	105 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	106 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	107 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	108
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	109
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	110 CREATE SEQUENCE auth_lst_umw_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	111 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	112 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	113 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	114 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	115 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	116
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	117 CREATE TABLE auth_lst_umw (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	118 id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	119 lst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	120 umw_id character varying(3)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	121 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	122
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	123 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	124
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	125
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	126 CREATE SEQUENCE datenbasis_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	127 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	128 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	129 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	130 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	131 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	132
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	133 CREATE TABLE datenbasis (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	134 id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	135 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	136 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	137 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	138
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	139 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	140
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	141
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	142 CREATE SEQUENCE datensatz_erzeuger_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	143 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	144 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	145 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	146 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	147 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	148
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	149 CREATE TABLE datensatz_erzeuger (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	150 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	151 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	152 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	153 da_erzeuger_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	154 mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	155 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	156 letzte_aenderung timestamp without time zone
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	157 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	158
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	159 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	160
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	161
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	162 CREATE SEQUENCE de_vg_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	163 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	164 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	165 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	166 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	167 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	168
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	169 CREATE TABLE de_vg (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	170 id integer PRIMARY KEY DEFAULT nextval('de_vg_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	171 use double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	172 rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	173 gf double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	174 rau_rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	175 gen character varying(50),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	176 des character varying(75),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	177 isn double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	178 bemerk character varying(75),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	179 nambild character varying(16),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	180 ags character varying(12),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	181 rs_alt character varying(20),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	182 wirksamkei date,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	183 debkg_id character varying(16),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	184 length numeric,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	185 shape_area numeric,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	186 geom public.geometry(MultiPolygon,4326)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	187 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	188
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	189 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	190
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	191
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	192 CREATE TABLE deskriptor_umwelt (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	193 id integer PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	194 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	195 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	196 s02 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	197 s03 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	198 s04 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	199 s05 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	200 s06 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	201 s07 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	202 s08 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	203 s09 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	204 s10 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	205 s11 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	206 s12 integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	207 umw_id character varying(3) NOT NULL
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	208 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	209
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	210
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	211 CREATE SEQUENCE deskriptoren_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	212 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	213 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	214 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	215 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	216 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	217
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	218 CREATE TABLE deskriptoren (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	219 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	220 vorgaenger integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	221 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	222 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	223 sn smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	224 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	225 bedeutung character varying(300)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	226 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	227
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	228 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	229
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	230
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	231 CREATE SEQUENCE favorite_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	232 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	233 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	234 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	235 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	236 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	237
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	238 CREATE TABLE favorite (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	239 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	240 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	241 query_id integer NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	242 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	243
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	244 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	245
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	246
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	247 CREATE SEQUENCE filter_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	248 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	249 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	250 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	251 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	252 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	253
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	254 CREATE TABLE filter (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	255 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	256 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	257 data_index character varying(50) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	258 type character varying(10) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	259 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	260 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	261 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	262
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	263 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	264
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	265
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	266 CREATE SEQUENCE filter_value_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	267 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	268 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	269 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	270 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	271 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	272
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	273 CREATE TABLE filter_value (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	274 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	275 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	276 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	277 filter_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	278 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	279 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	280
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	281 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	282
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	283
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	284 CREATE SEQUENCE koordinaten_art_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	285 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	286 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	287 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	288 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	289 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	290
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	291 CREATE TABLE koordinaten_art (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	292 id integer PRIMARY KEY DEFAULT nextval('koordinaten_art_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	293 koordinatenart character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	294 idf_geo_key character varying(1)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	295 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	296
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	297 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	298
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	299
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	300 CREATE SEQUENCE lada_user_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	301 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	302 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	303 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	304 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	305 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	306
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	307 CREATE TABLE lada_user (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	308 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	309 name character varying(80) NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	310 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	311
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	312 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	313
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	314
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	315 CREATE SEQUENCE mess_einheit_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	316 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	317 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	318 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	319 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	320 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	321
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	322 CREATE TABLE mess_einheit (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	323 id integer PRIMARY KEY DEFAULT nextval('mess_einheit_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	324 beschreibung character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	325 einheit character varying(12),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	326 eudf_messeinheit_id character varying(8),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	327 umrechnungs_faktor_eudf bigint
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	328 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	329
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	330 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	331
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	332
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	333 CREATE TABLE mess_methode (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	334 id character varying(2) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	335 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	336 messmethode character varying(50)
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	339
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	340 CREATE TABLE mess_stelle (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	341 id character varying(5) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	342 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	343 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	344 mess_stelle character varying(60),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	345 mst_typ character varying(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	346 amtskennung character varying(6)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	347 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	348
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	349
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	350
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	351 CREATE SEQUENCE messgroesse_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	352 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	353 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	354 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	355 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	356 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	357
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	358 CREATE TABLE messgroesse (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	359 id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	360 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	361 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	362 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	363 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	364 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	365 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	366 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	367 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	368
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	369 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	370
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	371
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	372 CREATE SEQUENCE messgroessen_gruppe_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	373 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	374 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	375 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	376 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	377 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	378
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	379 CREATE TABLE messgroessen_gruppe (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	380 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	381 DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	382 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	383 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	384 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	385
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	386 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	387
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	388
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	389 CREATE SEQUENCE messprogramm_kategorie_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	390 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	391 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	392 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	393 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	394 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	395
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	396 CREATE TABLE messprogramm_kategorie (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	397 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	398 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	399 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	400 mpl_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	401 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	402 letzte_aenderung timestamp without time zone
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	403 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	404
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	405 ALTER SEQUENCE messprogramm_kategorie_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	406 OWNED BY messprogramm_kategorie.id;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	407
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	408
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	409 CREATE TABLE mg_grp (
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	410 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	411 messgroesse_id integer NOT NULL
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	412 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	413
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	414
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	415 CREATE TABLE mmt_messgroesse_grp (
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	416 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	417 mmt_id character varying(2) NOT NULL
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	418 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	419
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	420
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	421 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	422 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	423 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	424 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	425 mg_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	426 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	427
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	428
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	429 CREATE TABLE netz_betreiber (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	430 id character varying(2) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	431 netzbetreiber character varying(50),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	432 idf_netzbetreiber character varying(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	433 is_bmn boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	434 mailverteiler character varying(512),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	435 aktiv boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	436 zust_mst_id character varying(5)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	437 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	438
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	439
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	440 CREATE SEQUENCE ort_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	441 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	442 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	443 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	444 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	445 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	446
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	447 CREATE TABLE ort (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	448 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	449 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	450 ort_id character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	451 langtext character varying(100),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	452 staat_id smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	453 gem_id character varying(8),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	454 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	455 nuts_code character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	456 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	457 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	458 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	459 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	460 letzte_aenderung timestamp without time zone DEFAULT now(),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	461 latitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	462 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	463 geom public.geometry(Point,4326),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	464 shape public.geometry(MultiPolygon,4326),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	465 ort_typ smallint,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	466 kurztext character varying(15),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	467 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	468 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	469 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	470 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	471 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	472 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	473 anlage_id integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	474 oz_id integer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	475 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	476
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	477 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	478
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	479
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	480 CREATE TABLE ort_typ (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	481 id smallint PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	482 ort_typ character varying(60)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	483 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	484
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	485
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	486 CREATE TABLE ortszuordnung_typ (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	487 id character(1) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	488 ortstyp character varying(60)
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	492 CREATE SEQUENCE pflicht_messgroesse_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	493 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	494 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	495 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	496 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	497 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	498
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	499 CREATE TABLE pflicht_messgroesse (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	500 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	501 DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	502 messgroesse_id integer,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	503 mmt_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	504 umw_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	505 datenbasis_id smallint NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	506 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	507
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	508 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	509
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	510
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	511 CREATE TABLE proben_zusatz (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	512 id character varying(3) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	513 meh_id integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	514 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	515 zusatzwert character varying(7) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	516 eudf_keyword character varying(40)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	517 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	518
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	519
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	520 CREATE SEQUENCE probenart_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	521 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	522 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	523 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	524 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	525 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	526
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	527 CREATE TABLE probenart (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	528 id integer PRIMARY KEY DEFAULT nextval('probenart_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	529 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	530 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	531 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	532 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	533
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	534 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	535
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	536
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	537 CREATE SEQUENCE probenehmer_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	538 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	539 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	540 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	541 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	542 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	543
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	544 CREATE TABLE probenehmer (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	545 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	546 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	547 prn_id character varying(9),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	548 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	549 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	550 betrieb character varying(80),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	551 bezeichnung character varying(80),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	552 kurz_bezeichnung character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	553 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	554 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	555 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	556 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	557 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	558 typ character(1),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	559 letzte_aenderung timestamp without time zone
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	560 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	561
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	562 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	563
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	564
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	565 CREATE SEQUENCE query_id_seq
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	566 START WITH 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	567 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	568 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	569 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	570 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	571
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	572 CREATE TABLE query (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	573 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	574 name character varying(80) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	575 type character varying(30) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	576 sql character varying(1500) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	577 description character varying(100)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	578 );
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	579
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	580 ALTER SEQUENCE query_id_seq OWNED BY query.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	581
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	582
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	583 CREATE SEQUENCE result_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	584 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	585 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	586 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	587 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	588 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	589
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	590 CREATE TABLE result (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	591 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	592 query_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	593 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	594 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	595 width integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	596 flex boolean,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	597 index integer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	598 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	599
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	600 ALTER SEQUENCE result_id_seq OWNED BY result.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	601
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	602
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	603
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	604 CREATE SEQUENCE staat_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	605 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	606 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	607 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	608 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	609 CACHE 1;
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	610
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	611 CREATE TABLE staat (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	612 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	613 staat character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	614 hkl_id smallint NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	615 staat_iso character varying(2) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	616 staat_kurz character varying(5),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	617 eu character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	618 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	619 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	620 kda_id integer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	621 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	622
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	623 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	624
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	625
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	626 -- Status workflow
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	627 CREATE TABLE status_stufe (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	628 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	629 stufe character varying(50) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	630 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	631 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	632 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	633 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	634
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	635
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	636 CREATE TABLE status_wert (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	637 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	638 wert character varying(50) UNIQUE NOT NULL
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	639 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	640 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	641 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	642 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	643 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	644 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	645 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	646 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	647
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	648
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	649 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	650 id integer PRIMARY KEY,
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	651 stufe_id integer REFERENCES status_stufe NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	652 wert_id integer REFERENCES status_wert NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	653 UNIQUE(stufe_id, wert_id)
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	654 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	655 -- 'zurückgesetzt' is left out here deliberately!
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	656 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	657 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	658 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	659 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	660 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	661 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	662 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	663 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	664 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	665 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	666 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	667 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	668 INSERT INTO status_kombi VALUES (13, 3, 4);
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	669
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	670
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	671 CREATE SEQUENCE status_reihenfolge_id_seq;
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	672 CREATE TABLE status_reihenfolge (
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	673 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'),
872 e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	674 von_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	675 zu_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow. Tom Gottfried <tom@intevation.de> parents: 871 diff changeset	676 UNIQUE(von_id, zu_id)
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	677 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	678
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	679 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	680 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	681 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	682 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	683 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	684 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	685 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	686
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	687 BEGIN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	688 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	689 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	690 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	691
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	692 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	693 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	694 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	695
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	696 IF s_from = s_to AND w_to <> 0 AND w_from <> 4 THEN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	697 -- 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	698 -- 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	699 -- and 'Rückfrage' is only allowed for 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	700 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	701 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	702
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	703 ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	704 -- Going to the next 'stufe' all available status_kombi are allowed
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	705 -- in case current wert is not 'nicht vergeben' or '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	706 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	707 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	708
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	709 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	710 -- 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	711 -- '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	712 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	713 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	714
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	715 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	716 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	717 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	718 END;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	719 $$ 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	720
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	721 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	722 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	723 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	724 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	725
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	726
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	727 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	728 SELECT r.id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	729 zu.wert_id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	730 zu.stufe_id,
6bd149c352a9 Available status values need to be filtered by the users functions. Tom Gottfried <tom@intevation.de> parents: 873 diff changeset	731 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	732 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	733 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	734 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	735 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	736 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	737 ON zu.id = r.zu_id
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	738 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	739 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	740
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	741
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	742 CREATE TABLE umwelt (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	743 id character varying(3) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	744 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	745 umwelt_bereich character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	746 meh_id integer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	747 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	748
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	749
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	750 CREATE TABLE verwaltungseinheit (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	751 id character varying(8) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	752 bundesland character varying(8) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	753 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	754 kreis character varying(8),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	755 nuts character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	756 regbezirk character varying(8),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	757 bezeichnung character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	758 is_bundesland character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	759 is_gemeinde character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	760 is_landkreis character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	761 is_regbezirk character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	762 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	763 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	764 plz character varying(6),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	765 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	766 latitude double precision
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	767 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	768
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	769
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	770
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	771
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	772 ALTER TABLE ONLY lada_user
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	773 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	774
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	775
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	776 ALTER TABLE ONLY mg_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	777 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	778
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	779
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	780 ALTER TABLE ONLY mmt_messgroesse_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	781 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	782
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	783
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	784 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	785 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	786
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	787
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	788 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	789 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	790
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	791
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	792 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	793
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	794
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	795 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	796
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	797
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	798
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	799 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	800 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	801
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	802
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	803
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	804 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	805 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	806
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	807
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	808
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	809 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	810 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	811
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	812
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	813
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	814 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	815 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	816
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	817
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	818
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	819 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	820 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	821
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	822
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	823
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	824 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	825 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	826
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	827
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	828
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	829 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	830 ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	831
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	832
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	833
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	834 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	835 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	836
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	837
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	838
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	839 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	840 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	841
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	842
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	843
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	844 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	845 ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	846
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	847
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	848
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	849 ALTER TABLE ONLY filter
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	850 ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	851
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	852
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	853
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	854 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	855 ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	856
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	857
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	858
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	859 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	860 ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	861
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	862
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	863
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	864 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	865 ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	866
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	867
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	868
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	869 ALTER TABLE ONLY deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	870 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	871
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	872
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	873
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	874 ALTER TABLE ONLY messprogramm_kategorie
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	875 ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	876
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	877
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	878
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	879 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	880 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	881
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	882
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	883
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	884 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	885 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	886
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	887
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	888
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	889 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	890 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	891
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	892
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	893
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	894 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	895 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	896
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	897
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	898
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	899 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	900 ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	901
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	902
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	903
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	904 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	905 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	906
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	907
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	908
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	909 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	910 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	911
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	912
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	913
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	914 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	915 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	916
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	917
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	918
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	919 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	920 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	921
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	922
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	923
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	924 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	925 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	926
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	927
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	928
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	929 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	930 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	931
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	932
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	933
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	934 ALTER TABLE ONLY probenehmer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	935 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	936
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	937
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	938
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	939 ALTER TABLE ONLY result
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	940 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	941
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	942
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	943
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	944 ALTER TABLE ONLY staat
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	945 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	946
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	947
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	948
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	949
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	950
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	951 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	952 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	953
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	954
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	955
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	956 ALTER TABLE ONLY verwaltungseinheit
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	957 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	958
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	959
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	960 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 883:2e264556f186