lada/lada-server: db_schema/stammdaten

annotate db_schema/stammdaten_schema.sql @ 871:53d739275c50

Status workflow definitions are needed and assumed to be given by the server. These are not data to be changed by any user, thus part of the schema.

author	Tom Gottfried <tom@intevation.de>
date	Mon, 08 Feb 2016 18:23:41 +0100
parents	82a51cafa0fe
children	e161d8f2d978

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,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	101 funktion character varying(40)
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	604 CREATE VIEW s_00_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	605 SELECT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	606 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	607 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	608 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	609 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	610 WHERE (deskriptoren.ebene = 0);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	611
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	612
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	613
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	614 CREATE VIEW s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	615 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	616 d2.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	617 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	618 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	619 d1.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	620 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	621 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	622 WHERE (d1.ebene = 1);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	623
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	626 CREATE VIEW s_02_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	627 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	628 d2.s_xx AS s02
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	629 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	630 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	631 WHERE (d2.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	632
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	633
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	634
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	635 CREATE VIEW s_02_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	636 SELECT DISTINCT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	637 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	638 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	639 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	640 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	641 WHERE (deskriptoren.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	642
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	643
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	644
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	645 CREATE VIEW s_03_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	646 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	647 d2.s_xx AS s03
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	648 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	649 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	650 WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	651
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	652
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	653
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	654 CREATE VIEW s_03_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	655 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	656 d2.s_xx AS s02,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	657 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	658 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	659 d1.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	660 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	661 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	662 WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	663 UNION
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	664 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	665 NULL::integer AS s02,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	666 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	667 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	668 d1.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	669 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	670 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	671 WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	672
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	673
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	674
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	675 CREATE VIEW s_04_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	676 SELECT DISTINCT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	677 d2.s_xx AS s04
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	678 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	679 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	680 WHERE (d2.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	681
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	682
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	683
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	684 CREATE VIEW s_04_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	685 SELECT DISTINCT deskriptoren.s_xx AS s04,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	686 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	687 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	688 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	689 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	690 WHERE (deskriptoren.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	691
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	692
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	693
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	694 CREATE VIEW s_05_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	695 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	696 d2.s_xx AS s05
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	697 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	698 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	699 WHERE (d2.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	700
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	701
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	702
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	703 CREATE VIEW s_05_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	704 SELECT DISTINCT deskriptoren.s_xx AS s05,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	705 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	706 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	707 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	708 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	709 WHERE (deskriptoren.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	710
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	711
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	712
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	713 CREATE VIEW s_06_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	714 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	715 d2.s_xx AS s06
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	716 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	717 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	718 WHERE (d2.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	719
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	720
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	721
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	722 CREATE VIEW s_06_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	723 SELECT DISTINCT deskriptoren.s_xx AS s06,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	724 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	725 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	726 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	727 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	728 WHERE (deskriptoren.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	729
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	730
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	731
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	732 CREATE VIEW s_07_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	733 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	734 d2.s_xx AS s07
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	735 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	736 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	737 WHERE (d2.ebene = 7);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	738
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	739
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	740
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	741 CREATE VIEW s_07_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	742 SELECT DISTINCT deskriptoren.s_xx AS s07,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	743 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	744 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	745 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	746 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	747 WHERE (deskriptoren.ebene = 7);
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	751 CREATE VIEW s_08_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	752 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	753 d2.s_xx AS s08
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	754 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	755 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	756 WHERE (d2.ebene = 8);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	757
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	758
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	759
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	760 CREATE VIEW s_08_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	761 SELECT DISTINCT deskriptoren.s_xx AS s08,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	762 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	763 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	764 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	765 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	766 WHERE (deskriptoren.ebene = 8);
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	769
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	770 CREATE VIEW s_09_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	771 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	772 d2.s_xx AS s09
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	773 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	774 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	775 WHERE (d2.ebene = 9);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	776
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	777
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 CREATE VIEW s_09_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	780 SELECT DISTINCT deskriptoren.s_xx AS s09,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	781 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	782 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	783 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	784 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	785 WHERE (deskriptoren.ebene = 9);
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	789 CREATE VIEW s_10_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	790 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	791 d2.s_xx AS s10
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	792 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	793 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	794 WHERE (d2.ebene = 10);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	795
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 CREATE VIEW s_10_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	799 SELECT DISTINCT deskriptoren.s_xx AS s10,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	800 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	801 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	802 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	803 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	804 WHERE (deskriptoren.ebene = 10);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	805
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 CREATE VIEW s_11_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	809 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	810 d2.s_xx AS s11
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	811 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	812 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	813 WHERE (d2.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	814
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	815
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 CREATE VIEW s_11_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	818 SELECT DISTINCT deskriptoren.s_xx AS s11,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	819 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	820 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	821 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	822 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	823 WHERE (deskriptoren.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	824
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	825
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 CREATE VIEW s_12_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	828 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	829 d2.s_xx AS s12
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	830 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	831 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	832 WHERE (d2.ebene = 12);
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	835
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	836 CREATE VIEW s_12_view AS
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	837 SELECT DISTINCT deskriptoren.s_xx AS s12,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	838 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	839 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	840 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	841 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	842 WHERE (deskriptoren.ebene = 12);
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
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	845
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	846 CREATE SEQUENCE staat_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	847 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	848 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	849 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	850 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	851 CACHE 1;
866 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 CREATE TABLE staat (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	854 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	855 staat character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	856 hkl_id smallint NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	857 staat_iso character varying(2) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	858 staat_kurz character varying(5),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	859 eu character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	860 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	861 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	862 kda_id integer
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
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	865 ALTER SEQUENCE staat_id_seq OWNED BY staat.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
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	868 -- Status workflow
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	869 CREATE TABLE status_stufe (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	870 id integer PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	871 stufe character varying(50)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	872 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	873 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	874 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	875 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	876
866 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 CREATE TABLE status_wert (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	879 id integer PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	880 wert character varying(50)
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	881 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	882 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	883 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	884 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	885 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	886 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	887 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	888 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	889
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	890
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	891 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	892 id integer PRIMARY KEY,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	893 stufe_id integer REFERENCES status_stufe,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	894 wert_id integer REFERENCES status_wert
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	895 );
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	896 -- '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	897 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	898 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	899 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	900 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	901 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	902 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	903 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	904 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	905 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	906 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	907 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	908 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	909 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	910
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	911
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	912 CREATE SEQUENCE status_reihenfolge_id_seq;
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	913 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	914 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'),
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	915 von_id integer REFERENCES status_kombi,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	916 zu_id integer REFERENCES status_kombi
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	917 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	918
871 53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	919 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	920 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	921 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	922 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	923 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	924 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	925 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	926
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	927 BEGIN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	928 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	929 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	930 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	931
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	932 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	933 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	934 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	935
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	936 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	937 -- 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	938 -- 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	939 -- 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	940 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	941 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	942
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	943 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	944 -- 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	945 -- 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	946 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	947 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	948
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	949 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	950 -- 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	951 -- '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	952 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	953 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	954
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	955 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	956 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	957 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	958 END;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	959 $$ 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	960
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	961 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	962 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	963 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	964 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	965
53d739275c50 Status workflow definitions are needed and assumed to be given by the server. Tom Gottfried <tom@intevation.de> parents: 870 diff changeset	966
870 82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	967 CREATE VIEW status_erreichbar AS (
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	968 SELECT DISTINCT k.wert_id,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	969 j.wert_id AS cur_wert,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	970 j.stufe_id AS cur_stufe
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	971 FROM stammdaten.status_kombi k
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	972 JOIN (SELECT r.zu_id,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	973 kom.wert_id,
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	974 kom.stufe_id
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	975 FROM stammdaten.status_reihenfolge r
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	976 JOIN stammdaten.status_kombi kom
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	977 ON kom.id = r.von_id) j
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	978 ON j.zu_id = k.id
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	979 );
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	980 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place. Tom Gottfried <tom@intevation.de> parents: 869 diff changeset	981
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	982
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	983 CREATE TABLE umwelt (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	984 id character varying(3) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	985 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	986 umwelt_bereich character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	987 meh_id integer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	988 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	989
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	990
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	991 CREATE TABLE verwaltungseinheit (
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	992 id character varying(8) PRIMARY KEY,
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	993 bundesland character varying(8) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	994 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	995 kreis character varying(8),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	996 nuts character varying(10),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	997 regbezirk character varying(8),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	998 bezeichnung character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	999 is_bundesland character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1000 is_gemeinde character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1001 is_landkreis character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1002 is_regbezirk character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1003 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1004 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1005 plz character varying(6),
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1006 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1007 latitude double precision
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1008 );
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1009
868 6cefa1468832 Use inline primary key definitions and remove autogenerated comments. Tom Gottfried <tom@intevation.de> parents: 867 diff changeset	1010
866 d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1011
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1012
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1013 ALTER TABLE ONLY lada_user
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1014 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1015
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1016
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1017 ALTER TABLE ONLY mg_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1018 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1019
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1020
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1021 ALTER TABLE ONLY mmt_messgroesse_grp
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1022 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	1023
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1024
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1025 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1026 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1027
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1028
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1029 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1030 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1031
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1032
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1033 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	1034
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1035
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1036 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1037
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1038
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1039
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1040 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1041 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	1042
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1043
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1044
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1045 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1046 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	1047
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1048
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1049
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1050 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1051 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	1052
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1053
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1054
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1055 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1056 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	1057
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1058
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1059
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1060 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1061 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	1062
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1063
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1064
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1065 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1066 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	1067
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1068
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1069
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1070 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1071 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	1072
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1073
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1074
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1075 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1076 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	1077
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1078
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1079
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1080 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1081 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	1082
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1083
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1084
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1085 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1086 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	1087
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1088
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1089
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1090 ALTER TABLE ONLY filter
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1091 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	1092
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1093
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1094
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1095 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1096 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	1097
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1098
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1099
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1100 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1101 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	1102
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1103
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1104
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1105 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1106 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	1107
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1108
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1109
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1110 ALTER TABLE ONLY deskriptoren
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1111 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1112
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1113
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1114
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1115 ALTER TABLE ONLY messprogramm_kategorie
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1116 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	1117
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1118
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1119
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1120 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1121 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	1122
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1123
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1124
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1125 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1126 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	1127
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1128
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1129
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1130 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1131 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	1132
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1133
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1134
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1135 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1136 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	1137
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1138
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1139
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1140 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1141 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	1142
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1143
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1144
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1145 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1146 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	1147
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1148
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1149
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1150 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1151 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	1152
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1153
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1154
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1155 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1156 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	1157
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1158
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1159
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1160 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1161 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	1162
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1163
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1164
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1165 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1166 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	1167
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1168
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1169
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1170 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1171 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	1172
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1173
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1174
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1175 ALTER TABLE ONLY probenehmer
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1176 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	1177
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1178
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1179
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1180 ALTER TABLE ONLY result
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1181 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	1182
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1183
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1184
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1185 ALTER TABLE ONLY staat
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1186 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	1187
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1188
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1189
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1190
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1191
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1192 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1193 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	1194
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1195
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1196
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1197 ALTER TABLE ONLY verwaltungseinheit
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1198 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	1199
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1200
d47e6b8f3897 Reorganise database setup scripts. Tom Gottfried <tom@intevation.de> parents: diff changeset	1201 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 871:53d739275c50