diff db_schema/lada_schema.sql @ 999:813461e9a7be schema-update

Reordered table creation to allow inline definition of FKs.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 18:17:23 +0200
parents c22a99d5c6f1
children 1489f0ade850
line wrap: on
line diff
--- a/db_schema/lada_schema.sql	Fri Jul 01 18:11:48 2016 +0200
+++ b/db_schema/lada_schema.sql	Fri Jul 01 18:17:23 2016 +0200
@@ -164,32 +164,6 @@
 
 
 --
--- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE kommentar_m (
-    id serial PRIMARY KEY,
-    erzeuger character varying(5) NOT NULL,
-    datum timestamp without time zone DEFAULT now(),
-    text character varying(1024),
-    messungs_id integer NOT NULL
-);
-
-
---
--- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE kommentar_p (
-    id serial PRIMARY KEY,
-    erzeuger character varying(5) NOT NULL,
-    datum timestamp without time zone DEFAULT now(),
-    text character varying(1024),
-    probe_id integer NOT NULL
-);
-
-
---
 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
 --
 
@@ -244,69 +218,6 @@
 
 
 --
--- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE messung (
-    id serial PRIMARY KEY,
-    id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
-    probe_id integer NOT NULL,
-    nebenproben_nr character varying(10),
-    mmt_id character varying(2) NOT NULL,
-    messdauer integer,
-    messzeitpunkt timestamp with time zone,
-    fertig boolean DEFAULT false NOT NULL,
-    status integer,
-    letzte_aenderung timestamp without time zone DEFAULT now(),
-    geplant boolean DEFAULT false NOT NULL,
-    tree_modified timestamp without time zone DEFAULT now()
-);
-
-
---
--- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE messwert (
-    id serial PRIMARY KEY,
-    messungs_id integer NOT NULL,
-    messgroesse_id integer NOT NULL,
-    messwert_nwg character varying(1),
-    messwert double precision NOT NULL,
-    messfehler real,
-    nwg_zu_messwert double precision,
-    meh_id smallint NOT NULL,
-    grenzwertueberschreitung boolean DEFAULT false,
-    letzte_aenderung timestamp without time zone DEFAULT now(),
-    tree_modified timestamp without time zone DEFAULT now(),
-    UNIQUE (messungs_id, messgroesse_id)
-);
-CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-
-
---
--- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE ortszuordnung (
-    id serial PRIMARY KEY,
-    probe_id integer NOT NULL,
-    ort_id bigint NOT NULL,
-    ortszuordnung_typ character varying(1),
-    ortszusatztext character varying(100),
-    letzte_aenderung timestamp without time zone DEFAULT now(),
-    tree_modified timestamp without time zone DEFAULT now()
-);
-CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-
---
--- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
---
-
-COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
-
-
---
 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
 --
 
@@ -342,6 +253,61 @@
 );
 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
 
+
+--
+-- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE kommentar_p (
+    id serial PRIMARY KEY,
+    erzeuger character varying(5) NOT NULL,
+    datum timestamp without time zone DEFAULT now(),
+    text character varying(1024),
+    probe_id integer NOT NULL
+);
+
+
+--
+-- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE ortszuordnung (
+    id serial PRIMARY KEY,
+    probe_id integer NOT NULL,
+    ort_id bigint NOT NULL,
+    ortszuordnung_typ character varying(1),
+    ortszusatztext character varying(100),
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    tree_modified timestamp without time zone DEFAULT now()
+);
+CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
+
+
+--
+-- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE zusatz_wert (
+    id serial PRIMARY KEY,
+    probe_id integer NOT NULL,
+    pzs_id character varying(3) NOT NULL,
+    messwert_pzs double precision,
+    messfehler real,
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    nwg_zu_messwert double precision,
+    tree_modified timestamp without time zone DEFAULT now(),
+    UNIQUE (probe_id, pzs_id)
+);
+CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
+
+
+--
+-- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
+--
+
+COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
+
+
 --
 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
 --
@@ -413,6 +379,60 @@
 
 
 --
+-- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE messung (
+    id serial PRIMARY KEY,
+    id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
+    probe_id integer NOT NULL,
+    nebenproben_nr character varying(10),
+    mmt_id character varying(2) NOT NULL,
+    messdauer integer,
+    messzeitpunkt timestamp with time zone,
+    fertig boolean DEFAULT false NOT NULL,
+    status integer,
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    geplant boolean DEFAULT false NOT NULL,
+    tree_modified timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE kommentar_m (
+    id serial PRIMARY KEY,
+    erzeuger character varying(5) NOT NULL,
+    datum timestamp without time zone DEFAULT now(),
+    text character varying(1024),
+    messungs_id integer NOT NULL
+);
+
+
+--
+-- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE messwert (
+    id serial PRIMARY KEY,
+    messungs_id integer NOT NULL,
+    messgroesse_id integer NOT NULL,
+    messwert_nwg character varying(1),
+    messwert double precision NOT NULL,
+    messfehler real,
+    nwg_zu_messwert double precision,
+    meh_id smallint NOT NULL,
+    grenzwertueberschreitung boolean DEFAULT false,
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    tree_modified timestamp without time zone DEFAULT now(),
+    UNIQUE (messungs_id, messgroesse_id)
+);
+CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
+
+
+--
 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
 --
 
@@ -429,24 +449,6 @@
 
 
 --
--- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
---
-
-CREATE TABLE zusatz_wert (
-    id serial PRIMARY KEY,
-    probe_id integer NOT NULL,
-    pzs_id character varying(3) NOT NULL,
-    messwert_pzs double precision,
-    messfehler real,
-    letzte_aenderung timestamp without time zone DEFAULT now(),
-    nwg_zu_messwert double precision,
-    tree_modified timestamp without time zone DEFAULT now(),
-    UNIQUE (probe_id, pzs_id)
-);
-CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-
-
---
 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
 --
 
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)