lada/lada-server: db_schema/lada

annotate db_schema/lada_schema.sql @ 1046:434b46ead134 schema-update

Added unique constraint for probe.id_alt

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Fri, 09 Sep 2016 15:33:04 +0200
parents	d77178ccbcbc
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
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,
1008 17e9d1b2d471 Removed column name, added column kommentar in messprogramm Raimund Renkert <raimund.renkert@intevation.de> parents: 1007 diff changeset	117 kommentar character varying(1000),
900 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 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
1006 599bd9462e62 Removed duplicated netzbetreiber column. (mst_id already contains the Raimund Renkert <raimund.renkert@intevation.de> parents: 1005 diff changeset	120 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	121 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
1026 d77178ccbcbc Added FK to probe and messprogramm. Raimund Renkert <raimund.renkert@intevation.de> parents: 1010 diff changeset	122 ba_id integer REFERENCES stammdaten.betriebsart,
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	123 gem_id character varying(8) REFERENCES stammdaten.verwaltungseinheit,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	124 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	125 media_desk character varying(100),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	126 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	127 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	128 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	129 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	130 teilintervall_bis integer NOT NULL,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	131 intervall_offset integer,
989 b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	132 gueltig_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm. Tom Gottfried <tom@intevation.de> parents: 927 diff changeset	133 gueltig_bis integer NOT NULL,
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	134 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
900 26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	135 probe_kommentar character varying(80),
26d57ae6dd2f Improve readability of DDL for messprogramm table. Tom Gottfried <tom@intevation.de> parents: 899 diff changeset	136 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	137 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	138 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	139
741 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 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	142 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
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
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	145 CREATE TABLE messprogramm_mmt (
996 f77a9e85b122 Declare primary keys in tabel definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 995 diff changeset	146 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	147 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	148 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	149 messgroessen integer[],
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	150 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	151 );
926 9121d99a471e Use databse triggers for update on field 'letzteAenderung'. Raimund Renkert <raimund.renkert@intevation.de> parents: 914 diff changeset	152 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	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 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	156 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	157 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	158
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	159 CREATE TABLE probe (
994 5886384dcb92 Replaced explicit sequences by serials. Raimund Renkert <raimund.renkert@intevation.de> parents: 993 diff changeset	160 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	161 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	162 test boolean DEFAULT false NOT NULL,
1006 599bd9462e62 Removed duplicated netzbetreiber column. (mst_id already contains the Raimund Renkert <raimund.renkert@intevation.de> parents: 1005 diff changeset	163 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
599bd9462e62 Removed duplicated netzbetreiber column. (mst_id already contains the Raimund Renkert <raimund.renkert@intevation.de> parents: 1005 diff changeset	164 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	165 hauptproben_nr character varying(20),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	166 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
1026 d77178ccbcbc Added FK to probe and messprogramm. Raimund Renkert <raimund.renkert@intevation.de> parents: 1010 diff changeset	167 ba_id integer REFERENCES stammdaten.betriebsart,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	168 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	169 media_desk character varying(100),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	170 media character varying(100),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	171 umw_id character varying(3) REFERENCES stammdaten.umwelt,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	172 probeentnahme_beginn timestamp with time zone,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	173 probeentnahme_ende timestamp with time zone,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	174 mittelungsdauer bigint,
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	175 letzte_aenderung timestamp without time zone DEFAULT now(),
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	176 erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger,
f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	177 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	178 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	179 mpr_id integer REFERENCES messprogramm,
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	180 solldatum_beginn timestamp without time zone,
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	181 solldatum_ende timestamp without time zone,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	182 tree_modified timestamp without time zone DEFAULT now(),
1046 434b46ead134 Added unique constraint for probe.id_alt Raimund Renkert <raimund.renkert@intevation.de> parents: 1026 diff changeset	183 UNIQUE (mst_id, hauptproben_nr, id_alt)
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	184 );
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	185 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	186 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	187
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	188
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	189 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	190 -- 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	191 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	192
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	193 CREATE TABLE kommentar_p (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	194 id serial PRIMARY KEY,
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	195 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	196 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	197 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	198 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	199 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	200
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	201
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 -- 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	204 --
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 CREATE TABLE ortszuordnung (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	207 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	208 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	209 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	210 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	211 ortszusatztext character varying(100),
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	212 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	213 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	214 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	215 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	216 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	217
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 -- 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	221 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	222
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	223 CREATE TABLE zusatz_wert (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	224 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	225 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	226 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	227 messwert_pzs double precision,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	228 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	229 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	230 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	231 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	232 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	233 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	234 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	235 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	236
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	237
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	238 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	239 -- 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	240 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	241
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	242 CREATE TABLE messung (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	243 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	244 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	245 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	246 nebenproben_nr character varying(10),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	247 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	248 messdauer integer,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	249 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	250 fertig boolean DEFAULT false NOT NULL,
1003 552215760ba8 Moved messung.status FK constraint. Raimund Renkert <raimund.renkert@intevation.de> parents: 1002 diff changeset	251 status integer,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	252 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	253 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	254 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	255 );
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	256 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	257 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	258
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	259
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	260 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	261 -- 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	262 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	263
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	264 CREATE TABLE kommentar_m (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	265 id serial PRIMARY KEY,
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	266 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
999 813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	267 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	268 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	269 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	270 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	271
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	272
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	273 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	274 -- 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	275 --
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	276
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	277 CREATE TABLE messwert (
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	278 id serial PRIMARY KEY,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	279 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	280 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	281 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	282 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	283 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	284 nwg_zu_messwert double precision,
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	285 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	286 grenzwertueberschreitung boolean DEFAULT false,
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	287 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	288 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	289 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	290 );
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	291 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	292 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	293
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	294
813461e9a7be Reordered table creation to allow inline definition of FKs. Raimund Renkert <raimund.renkert@intevation.de> parents: 998 diff changeset	295 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	296 -- 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	297 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	298
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers. Raimund Renkert <raimund.renkert@intevation.de> parents: 741 diff changeset	299 CREATE TABLE status_protokoll (
996 f77a9e85b122 Declare primary keys in tabel definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 995 diff changeset	300 id serial PRIMARY KEY,
1009 f0ac901a4387 Added FK constraints. Raimund Renkert <raimund.renkert@intevation.de> parents: 1008 diff changeset	301 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	302 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	303 text character varying(1024),
1000 1489f0ade850 Moved FK constraints into table definition. Raimund Renkert <raimund.renkert@intevation.de> parents: 999 diff changeset	304 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
1010 c1e00e1fe5c8 Use status_kombi as FK in status_protokol Raimund Renkert <raimund.renkert@intevation.de> parents: 1009 diff changeset	305 status_kombi integer NOT NULL REFERENCES stammdaten.status_kombi,
992 9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	306 tree_modified timestamp without time zone DEFAULT now()
9002ebe2a8af Removed schema 'bund'. Raimund Renkert <raimund.renkert@intevation.de> parents: 989 diff changeset	307 );
1002 8ec5ee1c2dc6 Updated trigger and functions. Raimund Renkert <raimund.renkert@intevation.de> parents: 1001 diff changeset	308 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	309
1003 552215760ba8 Moved messung.status FK constraint. Raimund Renkert <raimund.renkert@intevation.de> parents: 1002 diff changeset	310 ALTER TABLE ONLY messung
552215760ba8 Moved messung.status FK constraint. Raimund Renkert <raimund.renkert@intevation.de> parents: 1002 diff changeset	311 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
552215760ba8 Moved messung.status FK constraint. Raimund Renkert <raimund.renkert@intevation.de> parents: 1002 diff changeset	312
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	313
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	314 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	315 -- 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	316 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	317
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	318 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	319
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 --
1004 c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	322 -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	323 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	324
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	325 CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	326
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	327
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	328 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	329 -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	330 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	331
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	332 CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	333
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	334
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	335 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	336 -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	337 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	338
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	339 CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	340
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	341
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	342 --
799 2059ac26fd49 Whitespace-cleanup in schema definitions. Tom Gottfried <tom@intevation.de> parents: 798 diff changeset	343 -- 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	344 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	345
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	346 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	347
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	348
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	349 --
1004 c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	350 -- 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	351 --
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	352
1004 c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	353 CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	354
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	355
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	356 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	357 -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	358 --
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	359
c0ff3965c266 Added index for "child"-tables and removed index for probe. Raimund Renkert <raimund.renkert@intevation.de> parents: 1003 diff changeset	360 CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id);
741 c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	361
c998673c6d1e Add initial DB schema script. Tom Gottfried <tom@intevation.de> parents: diff changeset	362
1005 e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	363 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	364 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	365 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	366
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	367 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	368
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	369
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	370 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	371 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	372 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	373
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	374 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	375
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	376
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	377 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	378 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	379 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	380
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	381 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	382
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	383
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	384 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	385 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	386 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	387
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	388 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	389
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	390
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	391 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	392 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	393 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	394
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	395 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	396
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	397
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	398 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	399 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	400 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	401
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	402 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	403
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	404
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	405 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	406 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	407 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	408
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	409 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	410
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	411
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	412 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	413 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	414 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	415
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	416 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	417
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	418
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	419 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	420 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	421 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	422
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	423 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	424
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	425
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	426 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	427 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	428 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	429
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	430 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	431
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	432
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	433 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	434 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	435 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	436
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	437 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	438
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	439
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	440 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	441 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	442 --
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	443
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	444 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	445
e9116ec4eb40 Moved comments to the end of the file. Raimund Renkert <raimund.renkert@intevation.de> parents: 1004 diff changeset	446
797 e15c981fe310 Do not create database objects in case of erroneous definitions. Tom Gottfried <tom@intevation.de> parents: 792 diff changeset	447 COMMIT;

Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1046:434b46ead134 schema-update