lada/lada-server: db_schema/lada

annotate db_schema/lada_schema.sql @ 1002:8ec5ee1c2dc6 schema-update

Updated trigger and functions.

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Fri, 01 Jul 2016 19:46:11 +0200
parents	1c4aa7ea63aa
children	552215760ba8

rev	line source
797 e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	1 \set ON_ERROR_STOP on
e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	2
e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	3 BEGIN;
e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	4
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	5
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	6 SET statement_timeout = 0;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	7 SET lock_timeout = 0;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	8 SET client_encoding = 'UTF8';
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	9 SET standard_conforming_strings = on;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	10 SET check_function_bodies = false;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	11 SET client_min_messages = warning;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	12
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	13 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	14 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	15 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	16
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	17 CREATE SCHEMA land;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	18
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	19 SET search_path = land, pg_catalog;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	20
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	21
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	22 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	23 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	24 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	25 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	26 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	27 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	28 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	29 $$;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	30
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	31
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 --
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	33 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	34 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	35
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	36 CREATE FUNCTION update_tree_modified() RETURNS trigger
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	37 LANGUAGE plpgsql
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	38 AS $$
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	39 BEGIN
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	40 NEW.tree_modified = now();
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	41 RETURN NEW;
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	42 END;
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	43 $$;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	44
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	45
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	46 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	47 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	48 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	49
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	50 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	51 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	52 AS $$
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	53 BEGIN
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	54 RAISE NOTICE 'messung is %',NEW.id;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	55 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	57 UPDATE land.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	58 RETURN NEW;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 END;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	60 $$;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	61
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	62
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	63 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	66
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	67 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	68 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 AS $$
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	70 BEGIN
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	72 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	74 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
824 6e5a0edba363 Fixed database function. Raimund Renkert <raimund.renkert@intevation.de> parents: 808 diff changeset	75 UPDATE land.ortszuordnung SET tree_modified = now() WHERE probe_id = NEW.id;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	76 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	77 RETURN NEW;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	78 END;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	79 $$;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	80
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	81
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	82 SET default_tablespace = '';
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	83
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	84 SET default_with_oids = false;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	85
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	86
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	87 --
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	88 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	89 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	90
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	91 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	92 START WITH 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	93 INCREMENT BY 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	94 NO MINVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	95 NO MAXVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	96 CACHE 1;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	97
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	98
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	99 --
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	100 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	101 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	102
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	103 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	104 START WITH 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	105 INCREMENT BY 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	106 NO MINVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	107 NO MAXVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	108 CACHE 1;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	109
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	110
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	111 --
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	112 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	113 --
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	114
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	115 CREATE TABLE messprogramm (
994 5886384dcb92 Replaced explicit sequences by serials. Raimund Renkert <raimund.renkert@intevation.de> parents: 993 diff changeset	116 id serial PRIMARY KEY,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	117 name character varying(256),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	118 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	119 netzbetreiber_id character varying(2) NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	120 REFERENCES stammdaten.netz_betreiber,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	121 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
901 56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id. Tom Gottfried <tom@intevation.de> parents: 900 diff changeset	122 labor_mst_id character varying(5) NOT NULL
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id. Tom Gottfried <tom@intevation.de> parents: 900 diff changeset	123 REFERENCES stammdaten.mess_stelle,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	124 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	125 ba_id character varying(1),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	126 gem_id character varying(8),
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	127 ort_id integer NOT NULL REFERENCES stammdaten.ort,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	128 media_desk character varying(100),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	129 umw_id character varying(3) REFERENCES stammdaten.umwelt,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	130 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	131 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	132 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	133 teilintervall_bis integer NOT NULL,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	134 intervall_offset integer,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	135 gueltig_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	136 gueltig_bis integer NOT NULL,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	137 probe_nehmer_id integer,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	138 probe_kommentar character varying(80),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	139 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	140 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	141 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	142
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	143
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	144 --
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	145 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	146 --
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	147
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	148 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	149
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	150
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	151 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	152 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	153 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	154
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	155 CREATE TABLE messprogramm_mmt (
996 f77a9e85b122 Declare primary keys in tabel definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 995 diff changeset	156 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	157 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	158 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode,
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	159 messgroessen integer[],
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	160 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	161 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	162 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	163
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	164
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	165 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	166 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	167 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	168
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	169 CREATE TABLE probe (
994 5886384dcb92 Replaced explicit sequences by serials. Raimund Renkert <raimund.renkert@intevation.de> parents: 993 diff changeset	170 id serial PRIMARY KEY,
993 196800bb22b0 Replaced translation tables for probe and messung with id_alt columns. Raimund Renkert <raimund.renkert@intevation.de> parents: 992 diff changeset	171 id_alt character varying(20) DEFAULT (('sss'::text \|\| lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) \|\| 'Y'::text) NOT NULL,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	172 test boolean DEFAULT false NOT NULL,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	173 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	174 mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	175 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	176 labor_mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	177 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	178 hauptproben_nr character varying(20),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	179 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	180 ba_id character varying(1),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	181 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	182 media_desk character varying(100),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	183 media character varying(100),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	184 umw_id character varying(3) REFERENCES stammdaten.umwelt,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	185 probeentnahme_beginn timestamp with time zone,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	186 probeentnahme_ende timestamp with time zone,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	187 mittelungsdauer bigint,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	188 letzte_aenderung timestamp without time zone DEFAULT now(),
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	189 erzeuger_id integer,
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	190 probe_nehmer_id integer,
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	191 mp_kat character(1),
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	192 mpl_id character varying(3),
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	193 mpr_id integer,
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	194 solldatum_beginn timestamp without time zone,
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	195 solldatum_ende timestamp without time zone,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	196 tree_modified timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	197 UNIQUE (mst_id, hauptproben_nr)
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	198 );
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	199 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	200 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
899 4bbfc9cff5d7 Improve readability of DDL for probe tables. Tom Gottfried <tom@intevation.de> parents: 898 diff changeset	201
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	202
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	203 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	204 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	205 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	206
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	207 CREATE TABLE kommentar_p (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	208 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	209 erzeuger character varying(5) NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	210 datum timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	211 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	212 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	213 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	214
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	215
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	216 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	217 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	218 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	219
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	220 CREATE TABLE ortszuordnung (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	221 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	222 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	223 ort_id bigint NOT NULL REFERENCES stammdaten.ort,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	224 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	225 ortszusatztext character varying(100),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	226 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	227 tree_modified timestamp without time zone DEFAULT now()
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	228 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	229 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	230 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	231
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	232
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	233 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	234 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	235 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	236
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	237 CREATE TABLE zusatz_wert (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	238 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	239 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	240 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	241 messwert_pzs double precision,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	242 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	243 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	244 nwg_zu_messwert double precision,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	245 tree_modified timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	246 UNIQUE (probe_id, pzs_id)
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	247 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	248 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	249 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	250
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	251
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	252 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	253 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	254 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	255
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	256 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	257
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	258
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	259 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	260 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	261 --
899 4bbfc9cff5d7 Improve readability of DDL for probe tables. Tom Gottfried <tom@intevation.de> parents: 898 diff changeset	262
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	263 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
899 4bbfc9cff5d7 Improve readability of DDL for probe tables. Tom Gottfried <tom@intevation.de> parents: 898 diff changeset	264
4bbfc9cff5d7 Improve readability of DDL for probe tables. Tom Gottfried <tom@intevation.de> parents: 898 diff changeset	265
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	266 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	267 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	268 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	269
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	270 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	271
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	272
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	273 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	274 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	275 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	276
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	277 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	278
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	279
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	280 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	281 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	282 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	283
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	284 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	285
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	286
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	287 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	288 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	289 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	290
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	291 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	292
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	293
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	294 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	295 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	296 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	297
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	298 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	299
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	300
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	301 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	302 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	303 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	304
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	305 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	306
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	307
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	308 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	309 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	310 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	311
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	312 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	313
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	314
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	315 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	316 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	317 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	318
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	319 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	320
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	321
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	322 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	323 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	324 --
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	325
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	326 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	327
899 4bbfc9cff5d7 Improve readability of DDL for probe tables. Tom Gottfried <tom@intevation.de> parents: 898 diff changeset	328
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	329 --
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	330 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	331 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	332
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	333 CREATE TABLE messung (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	334 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	335 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	336 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	337 nebenproben_nr character varying(10),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	338 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	339 messdauer integer,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	340 messzeitpunkt timestamp with time zone,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	341 fertig boolean DEFAULT false NOT NULL,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	342 status integer REFERENCES status_protokoll,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	343 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	344 geplant boolean DEFAULT false NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	345 tree_modified timestamp without time zone DEFAULT now()
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	346 );
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	347 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	348 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	349
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	350
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	351 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	352 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	353 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	354
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	355 CREATE TABLE kommentar_m (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	356 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	357 erzeuger character varying(5) NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	358 datum timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	359 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	360 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	361 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	362
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	363
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	364 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	365 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	366 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	367
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	368 CREATE TABLE messwert (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	369 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	370 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	371 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	372 messwert_nwg character varying(1),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	373 messwert double precision NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	374 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	375 nwg_zu_messwert double precision,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	376 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	377 grenzwertueberschreitung boolean DEFAULT false,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	378 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	379 tree_modified timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	380 UNIQUE (messungs_id, messgroesse_id)
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	381 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	382 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	383 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	384
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	385
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	386 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	387 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
751 37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	388 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	389
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	390 CREATE TABLE status_protokoll (
996 f77a9e85b122 Declare primary keys in tabel definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 995 diff changeset	391 id serial PRIMARY KEY,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	392 erzeuger character varying(5) NOT NULL,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	393 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	394 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	395 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	396 status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe,
1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	397 status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	398 tree_modified timestamp without time zone DEFAULT now()
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	399 );
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	400 CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	401
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	402
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	403 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	404 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	405 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	406
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	407 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	408
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	409
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	410 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	411 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	412 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	413
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	414 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	415
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	416
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	417 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	418 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	419 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	420
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	421 CREATE INDEX probe_id_idx ON probe USING btree (id);
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	422
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	423
797 e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	424 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1002:8ec5ee1c2dc6 schema-update