lada/lada-server: db_schema/lada

annotate db_schema/lada_schema.sql @ 1290:14876c62f692

Push down refreshing of persisted objects deeper into the stack. There are more places besides creation of Probe objects where it is useful to return within the response what has been really written to the database (including modifications by the database itself) instead of merely the request data, e.g. creation of Ort objects, which includes database generated ort_ids.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 08 Feb 2017 18:02:05 +0100
parents	9dfb52db6a0f
children

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
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	19 SET search_path = land, pg_catalog;
751 37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	20
1164 84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	21 CREATE FUNCTION set_messung_id_alt() RETURNS trigger
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	22 LANGUAGE plpgsql
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	23 AS $$
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	24 BEGIN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	25 IF NEW.id_alt IS NULL THEN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	26 NEW.id_alt = (
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	27 SELECT count(*)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	28 FROM land.messung
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	29 WHERE probe_id = NEW.probe_id) + 1;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	30 END IF;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	31 RETURN NEW;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	32 END;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	33 $$;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	34
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	35 CREATE FUNCTION set_messung_status() RETURNS trigger
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	36 LANGUAGE plpgsql
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	37 AS $$
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	38 DECLARE status_id integer;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	39 BEGIN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	40 INSERT INTO land.status_protokoll
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	41 (mst_id, datum, text, messungs_id, status_kombi)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	42 VALUES ((SELECT mst_id
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	43 FROM land.probe
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	44 WHERE id = NEW.probe_id),
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	45 now(), '', NEW.id, 1)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	46 RETURNING id into status_id;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	47 UPDATE land.messung SET status = status_id where id = NEW.id;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	48 RETURN NEW;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	49 END;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	50 $$;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	51
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	52 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	53 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	54 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	55 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	56 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	57 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	58 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	59 $$;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	60
741 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 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	63 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	65
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	66 CREATE FUNCTION update_tree_modified() RETURNS trigger
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	68 AS $$
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 BEGIN
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	70 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 RETURN NEW;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	72 END;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 $$;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	74
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	75
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	76 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	77 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	78 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	79
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	80 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	81 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	82 AS $$
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	83 BEGIN
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	84 RAISE NOTICE 'messung is %',NEW.id;
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	85 NEW.tree_modified = now();
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	86 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	87 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	88 RETURN NEW;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	89 END;
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
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	92
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	93 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	94 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	95 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	96
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	97 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	98 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	99 AS $$
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	100 BEGIN
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	101 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	102 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	103 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	104 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	105 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	106 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	107 RETURN NEW;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	108 END;
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
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	112 SET default_tablespace = '';
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	113
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	114 SET default_with_oids = false;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	115
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	116
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	117 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	118 -- 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	119 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	120
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	121 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	122 START WITH 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	123 INCREMENT BY 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	124 NO MINVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	125 NO MAXVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	126 CACHE 1;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	127
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	128
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	129 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	130 -- 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	131 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	132
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	133 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	134 START WITH 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	135 INCREMENT BY 1
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	136 NO MINVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	137 NO MAXVALUE
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	138 CACHE 1;
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	139
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	140
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	141 --
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	142 -- 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	143 --
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	144
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	145 CREATE TABLE messprogramm (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	146 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	147 kommentar character varying(1000),
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	148 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	149 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	150 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	151 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
1120 7061388e0af7 Rev d1d7d684800c missed to adapt default FK value. Tom Gottfried <tom@intevation.de> parents: 1097 diff changeset	152 ba_id integer DEFAULT 1 REFERENCES stammdaten.betriebsart,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	153 gem_id character varying(8) REFERENCES stammdaten.verwaltungseinheit,
1122 1ef08710aaeb Do not allow empty string for media descriptor. Tom Gottfried <tom@intevation.de> parents: 1120 diff changeset	154 media_desk character varying(100) CHECK(media_desk LIKE '% %'),
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	155 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	156 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	157 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	158 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	159 teilintervall_bis integer NOT NULL,
1059 a435b9fd1d85 Set default in data model instead of on generation of Probe objects. Tom Gottfried <tom@intevation.de> parents: 1054 diff changeset	160 intervall_offset integer NOT NULL DEFAULT 0,
1077 7143cd96d3de Calendar.DAY_OF_YEAR is 1-based. Tom Gottfried <tom@intevation.de> parents: 1066 diff changeset	161 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365),
7143cd96d3de Calendar.DAY_OF_YEAR is 1-based. Tom Gottfried <tom@intevation.de> parents: 1066 diff changeset	162 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	163 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
1189 4ee336c65ab3 Add MPL to Messprogramm Evi Huber <ehuber@bfs.de> parents: 1168 diff changeset	164 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	165 probe_kommentar character varying(80),
1044 a796e68da1cf A period with end before start is not meaningful. Tom Gottfried <tom@intevation.de> parents: 1019 diff changeset	166 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL,
1063 7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	167 CHECK (probenintervall = 'J'
1082 6499f2410c42 Fix validation of subintervall for yearly samples. Tom Gottfried <tom@intevation.de> parents: 1077 diff changeset	168 AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis
6499f2410c42 Fix validation of subintervall for yearly samples. Tom Gottfried <tom@intevation.de> parents: 1077 diff changeset	169 AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis
1063 7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	170 AND intervall_offset BETWEEN 0 AND 364
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	171 OR probenintervall = 'H'
1065 7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	172 AND teilintervall_von BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	173 AND teilintervall_bis BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	174 AND intervall_offset BETWEEN 0 AND 183
1063 7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	175 OR probenintervall = 'Q'
1065 7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	176 AND teilintervall_von BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	177 AND teilintervall_bis BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima. Tom Gottfried <tom@intevation.de> parents: 1063 diff changeset	178 AND intervall_offset BETWEEN 0 AND 91
1063 7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	179 OR probenintervall = 'M'
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	180 AND teilintervall_von BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	181 AND teilintervall_bis BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	182 AND intervall_offset BETWEEN 0 AND 30
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	183 OR probenintervall = 'W4'
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	184 AND teilintervall_von BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	185 AND teilintervall_bis BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	186 AND intervall_offset BETWEEN 0 AND 27
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	187 OR probenintervall = 'W2'
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	188 AND teilintervall_von BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	189 AND teilintervall_bis BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	190 AND intervall_offset BETWEEN 0 AND 13
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	191 OR probenintervall = 'W'
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	192 AND teilintervall_von BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	193 AND teilintervall_bis BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	194 AND intervall_offset BETWEEN 0 AND 6
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	195 OR probenintervall = 'T'
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	196 AND teilintervall_von = 1
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	197 AND teilintervall_bis = 1
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	198 AND intervall_offset = 0
7788a805a98d Complement data model and validation of sub-intervalls. Tom Gottfried <tom@intevation.de> parents: 1059 diff changeset	199 ),
1054 3c9616e5439f Remove wrong period check. Tom Gottfried <tom@intevation.de> parents: 1045 diff changeset	200 CHECK (teilintervall_von <= teilintervall_bis)
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	201 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	202 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	203
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	204
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	205 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	206 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	207 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	208
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	209 CREATE TABLE messprogramm_mmt (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	210 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	211 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	212 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	213 messgroessen integer[],
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	214 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	215 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	216 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	217
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	218
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	219 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	220 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	221 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	222
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	223 CREATE TABLE probe (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	224 id serial PRIMARY KEY,
1129 48c0132dbc85 Adapt definition to LAF and validate uniqueness of idAlt. Tom Gottfried <tom@intevation.de> parents: 1126 diff changeset	225 id_alt character varying(16) UNIQUE NOT NULL
1126 70b51893f15b Fix UNIQUE-constraint and respective validation. Tom Gottfried <tom@intevation.de> parents: 1122 diff changeset	226 DEFAULT 'sss'
70b51893f15b Fix UNIQUE-constraint and respective validation. Tom Gottfried <tom@intevation.de> parents: 1122 diff changeset	227 \|\| lpad(nextval('land.probe_probe_id_seq')::varchar, 12, '0')
70b51893f15b Fix UNIQUE-constraint and respective validation. Tom Gottfried <tom@intevation.de> parents: 1122 diff changeset	228 \|\| 'Y',
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	229 test boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	230 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	231 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	232 hauptproben_nr character varying(20),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	233 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	234 ba_id integer REFERENCES stammdaten.betriebsart,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	235 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
1122 1ef08710aaeb Do not allow empty string for media descriptor. Tom Gottfried <tom@intevation.de> parents: 1120 diff changeset	236 media_desk character varying(100) CHECK(media_desk LIKE '% %'),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	237 media character varying(100),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	238 umw_id character varying(3) REFERENCES stammdaten.umwelt,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	239 probeentnahme_beginn timestamp with time zone,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	240 probeentnahme_ende timestamp with time zone,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	241 mittelungsdauer bigint,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	242 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	243 erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	244 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	245 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	246 mpr_id integer REFERENCES messprogramm,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	247 solldatum_beginn timestamp without time zone,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	248 solldatum_ende timestamp without time zone,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	249 tree_modified timestamp without time zone DEFAULT now(),
1126 70b51893f15b Fix UNIQUE-constraint and respective validation. Tom Gottfried <tom@intevation.de> parents: 1122 diff changeset	250 UNIQUE (mst_id, hauptproben_nr),
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	251 CHECK(solldatum_beginn <= solldatum_ende)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	252 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	253 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	254 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	255
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	256
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	257 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	258 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	259 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	260
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	261 CREATE TABLE kommentar_p (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	262 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	263 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	264 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	265 text character varying(1024),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	266 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	267 );
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	268
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	269
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	270 --
808 2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	271 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	272 --
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	273
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	274 CREATE TABLE ortszuordnung (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	275 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	276 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1215 6e393b614edf The referencing column does not need to be wider than what it refereces. Tom Gottfried <tom@intevation.de> parents: 1189 diff changeset	277 ort_id integer NOT NULL REFERENCES stammdaten.ort,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	278 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	279 ortszusatztext character varying(100),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	280 letzte_aenderung timestamp without time zone DEFAULT now(),
1168 cd9bf2430eef Let the database enforce,too, what rev 46db98d060ae introduced. Tom Gottfried <tom@intevation.de> parents: 1164 diff changeset	281 tree_modified timestamp without time zone DEFAULT now(),
cd9bf2430eef Let the database enforce,too, what rev 46db98d060ae introduced. Tom Gottfried <tom@intevation.de> parents: 1164 diff changeset	282 EXCLUDE (probe_id WITH =) WHERE (ortszuordnung_typ = 'E')
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	283 );
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	284 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	285 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	286
1262 9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	287 --
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	288 -- Name: ortszuordnung_mp; Type: TABLE; Schema: land; Owner: -; Tablespace:
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	289 --
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	290
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	291 CREATE TABLE ortszuordnung_mp (
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	292 id serial PRIMARY KEY,
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	293 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	294 ort_id integer NOT NULL REFERENCES stammdaten.ort,
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	295 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	296 ortszusatztext character varying(100),
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	297 letzte_aenderung timestamp without time zone DEFAULT now(),
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	298 tree_modified timestamp without time zone DEFAULT now(),
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	299 EXCLUDE (messprogramm_id WITH =) WHERE (ortszuordnung_typ = 'E')
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	300 );
9dfb52db6a0f Make use of the ort factory in ort service. Raimund Renkert <raimund.renkert@intevation.de> parents: 1215 diff changeset	301 CREATE TRIGGER letzte_aenderung_ortszuordnung_mp BEFORE UPDATE ON ortszuordnung_mp FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	302
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	303 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	304 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	305 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	306
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	307 CREATE TABLE zusatz_wert (
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	308 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	309 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	310 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	311 messwert_pzs double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	312 messfehler real,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	313 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	314 nwg_zu_messwert double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	315 tree_modified timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	316 UNIQUE (probe_id, pzs_id)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	317 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	318 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	319 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	320
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	321
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	322 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	323 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	324 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	325
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	326 CREATE TABLE messung (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	327 id serial PRIMARY KEY,
1164 84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	328 id_alt integer NOT NULL,
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	329 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	330 nebenproben_nr character varying(10),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	331 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	332 messdauer integer,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	333 messzeitpunkt timestamp with time zone,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	334 fertig boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	335 status integer,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	336 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	337 geplant boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	338 tree_modified timestamp without time zone DEFAULT now()
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	339 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	340 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	341 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
1164 84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	342 CREATE TRIGGER id_alt_messung BEFORE INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_id_alt();
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects. Raimund Renkert <raimund.renkert@intevation.de> parents: 1129 diff changeset	343 CREATE TRIGGER status_messung AFTER INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_status();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	344
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	345 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	346 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	347 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	348
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	349 CREATE TABLE kommentar_m (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	350 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	351 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	352 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	353 text character varying(1024),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	354 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	355 );
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	356
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	357
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	358 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	359 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	360 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	361
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	362 CREATE TABLE messwert (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	363 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	364 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	365 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	366 messwert_nwg character varying(1),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	367 messwert double precision NOT NULL,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	368 messfehler real,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	369 nwg_zu_messwert double precision,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	370 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	371 grenzwertueberschreitung boolean DEFAULT false,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	372 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	373 tree_modified timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	374 UNIQUE (messungs_id, messgroesse_id)
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	375 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	376 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	377 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	378
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	379 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	380 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	381 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	382
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	383 CREATE TABLE status_protokoll (
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	384 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	385 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	386 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	387 text character varying(1024),
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	388 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	389 status_kombi integer NOT NULL REFERENCES stammdaten.status_kombi,
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	390 tree_modified timestamp without time zone DEFAULT now()
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	391 );
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	392 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	393
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	394 ALTER TABLE ONLY messung
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	395 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	396
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	397
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	398 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	399 -- 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	400 --
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 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	403
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	404
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	405 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	406 -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	407 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	408
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	409 CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id);
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	410
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	411
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	412 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	413 -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	414 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	415
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	416 CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id);
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	417
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	418
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	419 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	420 -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	421 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	422
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	423 CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id);
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	424
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	425
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	426 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	427 -- 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	428 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	429
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	430 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	431
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	432
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	433 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	434 -- Name: status_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	435 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	436
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	437 CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id);
751 37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	438
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	439
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	440 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	441 -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	442 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	443
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	444 CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id);
751 37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	445
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	446
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	447 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	448 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	449 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	450
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	451 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
808 2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	452
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	453
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	454 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	455 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
808 2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	456 --
2b0d99fbeecc Updated database schema. Raimund Renkert <raimund.renkert@intevation.de> parents: 799 diff changeset	457
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	458 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	459
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	460
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	461 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	462 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
751 37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	463 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	464
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	465 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	466
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	467
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	468 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	469 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	470 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	471
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	472 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	473
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	474
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	475 --
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	476 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	477 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	478
1097 186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	479 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	480
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	481
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	482 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	483 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	484 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	485
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	486 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	487
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	488
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	489 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	490 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	491 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	492
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	493 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	494
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	495
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	496 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	497 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	498 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	499
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	500 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	501
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	502
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	503 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	504 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	505 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	506
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	507 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	508
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	509
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	510 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	511 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	512 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	513
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	514 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	515
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	516
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	517 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	518 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	519 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	520
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	521 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	522
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	523
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	524 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	525 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	526 --
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	527
186d602e031a Merged branch schema-update into default. Tom Gottfried <tom@intevation.de> parents: 1082 diff changeset	528 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	529
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	530
797 e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	531 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1290:14876c62f692