Mercurial > lada > lada-server

changeset 902:d465094946ef

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve readability of DDL for editable stammdaten tables.
author Tom Gottfried <tom@intevation.de>
date Thu, 24 Mar 2016 15:57:05 +0100
parents 56c9fb1d37c8
children 685ced261ea4
files db_schema/stammdaten_schema.sql
diffstat 1 files changed, 81 insertions(+), 131 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/stammdaten_schema.sql	Wed Mar 23 18:52:09 2016 +0100
+++ b/db_schema/stammdaten_schema.sql	Thu Mar 24 15:57:05 2016 +0100
@@ -77,6 +77,69 @@
 $$;
 
 
+CREATE SEQUENCE staat_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+CREATE TABLE staat (
+    id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
+    staat character varying(50) NOT NULL,
+    hkl_id smallint NOT NULL,
+    staat_iso character varying(2) NOT NULL,
+    staat_kurz character varying(5),
+    eu character(1) DEFAULT NULL::bpchar,
+    koord_x_extern character varying(22),
+    koord_y_extern character varying(22),
+    kda_id integer
+);
+
+ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
+
+
+CREATE TABLE verwaltungseinheit (
+    id character varying(8) PRIMARY KEY,
+    bundesland character varying(8) NOT NULL,
+    kda_id integer,
+    kreis character varying(8),
+    nuts character varying(10),
+    regbezirk character varying(8),
+    bezeichnung character varying(80) NOT NULL,
+    is_bundesland character(1) NOT NULL,
+    is_gemeinde character(1) NOT NULL,
+    is_landkreis character(1) NOT NULL,
+    is_regbezirk character(1) NOT NULL,
+    koord_x_extern character varying(22),
+    koord_y_extern character varying(22),
+    plz character varying(6),
+    longitude double precision,
+    latitude double precision
+);
+
+
+CREATE TABLE netz_betreiber (
+    id character varying(2) PRIMARY KEY,
+    netzbetreiber character varying(50),
+    idf_netzbetreiber character varying(1),
+    is_bmn boolean DEFAULT false,
+    mailverteiler character varying(512),
+    aktiv boolean DEFAULT false,
+    zust_mst_id character varying(5)
+);
+
+
+CREATE TABLE mess_stelle (
+    id character varying(5) PRIMARY KEY,
+    netzbetreiber_id character varying(2),
+    beschreibung character varying(300),
+    mess_stelle character varying(60),
+    mst_typ character varying(1),
+    amtskennung character varying(6)
+);
+
+
 CREATE SEQUENCE auth_id_seq
     START WITH 1
     INCREMENT BY 1
@@ -149,9 +212,9 @@
 CREATE TABLE datensatz_erzeuger (
     id integer PRIMARY KEY
         DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
+    netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
     da_erzeuger_id character varying(2),
-    mst_id character varying(5),
+    mst_id character varying(5) REFERENCES mess_stelle,
     bezeichnung character varying(120),
     letzte_aenderung timestamp without time zone
 );
@@ -336,17 +399,6 @@
 );
 
 
-CREATE TABLE mess_stelle (
-    id character varying(5) PRIMARY KEY,
-    netzbetreiber_id character varying(2),
-    beschreibung character varying(300),
-    mess_stelle character varying(60),
-    mst_typ character varying(1),
-    amtskennung character varying(6)
-);
-
-
-
 CREATE SEQUENCE messgroesse_id_seq
     START WITH 1
     INCREMENT BY 1
@@ -395,7 +447,7 @@
 CREATE TABLE messprogramm_kategorie (
     id integer PRIMARY KEY
         DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
+    netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
     mpl_id character varying(3),
     bezeichnung character varying(120),
     letzte_aenderung timestamp without time zone
@@ -425,14 +477,9 @@
   WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
 
 
-CREATE TABLE netz_betreiber (
-    id character varying(2) PRIMARY KEY,
-    netzbetreiber character varying(50),
-    idf_netzbetreiber character varying(1),
-    is_bmn boolean DEFAULT false,
-    mailverteiler character varying(512),
-    aktiv boolean DEFAULT false,
-    zust_mst_id character varying(5)
+CREATE TABLE ort_typ (
+    id smallint PRIMARY KEY,
+    ort_typ character varying(60)
 );
 
 
@@ -445,14 +492,14 @@
 
 CREATE TABLE ort (
     id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
+    netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
     ort_id character varying(10),
     langtext character varying(100),
-    staat_id smallint,
-    gem_id character varying(8),
+    staat_id smallint REFERENCES staat,
+    gem_id character varying(8) REFERENCES verwaltungseinheit,
     unscharf character(1) DEFAULT NULL::bpchar,
     nuts_code character varying(10),
-    kda_id integer,
+    kda_id integer REFERENCES koordinaten_art,
     koord_x_extern character varying(22),
     koord_y_extern character varying(22),
     hoehe_land real,
@@ -461,7 +508,7 @@
     longitude double precision,
     geom public.geometry(Point,4326),
     shape public.geometry(MultiPolygon,4326),
-    ort_typ smallint,
+    ort_typ smallint REFERENCES ort_typ,
     kurztext character varying(15),
     berichtstext character varying(70),
     zone character varying(1),
@@ -473,13 +520,13 @@
     oz_id integer
 );
 
-ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
-
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
 
-CREATE TABLE ort_typ (
-    id smallint PRIMARY KEY,
-    ort_typ character varying(60)
-);
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
+
+ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
 
 
 CREATE TABLE ortszuordnung_typ (
@@ -542,7 +589,7 @@
 
 CREATE TABLE probenehmer (
     id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
+    netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
     prn_id character varying(9),
     bearbeiter character varying(25),
     bemerkung character varying(60),
@@ -600,28 +647,6 @@
 
 
 
-CREATE SEQUENCE staat_id_seq
-    START WITH 1
-    INCREMENT BY 1
-    NO MINVALUE
-    NO MAXVALUE
-    CACHE 1;
-
-CREATE TABLE staat (
-    id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
-    staat character varying(50) NOT NULL,
-    hkl_id smallint NOT NULL,
-    staat_iso character varying(2) NOT NULL,
-    staat_kurz character varying(5),
-    eu character(1) DEFAULT NULL::bpchar,
-    koord_x_extern character varying(22),
-    koord_y_extern character varying(22),
-    kda_id integer
-);
-
-ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
-
-
 -- Status workflow
 CREATE TABLE status_stufe (
     id integer PRIMARY KEY,
@@ -745,26 +770,6 @@
 );
 
 
-CREATE TABLE verwaltungseinheit (
-    id character varying(8) PRIMARY KEY,
-    bundesland character varying(8) NOT NULL,
-    kda_id integer,
-    kreis character varying(8),
-    nuts character varying(10),
-    regbezirk character varying(8),
-    bezeichnung character varying(80) NOT NULL,
-    is_bundesland character(1) NOT NULL,
-    is_gemeinde character(1) NOT NULL,
-    is_landkreis character(1) NOT NULL,
-    is_regbezirk character(1) NOT NULL,
-    koord_x_extern character varying(22),
-    koord_y_extern character varying(22),
-    plz character varying(6),
-    longitude double precision,
-    latitude double precision
-);
-
-
 
 
 ALTER TABLE ONLY lada_user
@@ -824,16 +829,6 @@
 
 
 
-ALTER TABLE ONLY datensatz_erzeuger
-    ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
-
-
-
-ALTER TABLE ONLY datensatz_erzeuger
-    ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
 ALTER TABLE ONLY favorite
     ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
 
@@ -864,46 +859,6 @@
 
 
 
-ALTER TABLE ONLY messprogramm_kategorie
-    ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
-
-
-
 ALTER TABLE ONLY pflicht_messgroesse
     ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
 
@@ -924,11 +879,6 @@
 
 
 
-ALTER TABLE ONLY probenehmer
-    ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
 ALTER TABLE ONLY result
     ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)