Mercurial > lada > lada-server

comparison db_schema/stammdaten_schema.sql @ 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 d4d768295068
children 825d82b8ad6e
comparison
equal deleted inserted replaced
901:56c9fb1d37c8 902:d465094946ef
75 return (result); 75 return (result);
76 end; 76 end;
77 $$; 77 $$;
78 78
79 79
80 CREATE SEQUENCE staat_id_seq
81 START WITH 1
82 INCREMENT BY 1
83 NO MINVALUE
84 NO MAXVALUE
85 CACHE 1;
86
87 CREATE TABLE staat (
88 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
89 staat character varying(50) NOT NULL,
90 hkl_id smallint NOT NULL,
91 staat_iso character varying(2) NOT NULL,
92 staat_kurz character varying(5),
93 eu character(1) DEFAULT NULL::bpchar,
94 koord_x_extern character varying(22),
95 koord_y_extern character varying(22),
96 kda_id integer
97 );
98
99 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
100
101
102 CREATE TABLE verwaltungseinheit (
103 id character varying(8) PRIMARY KEY,
104 bundesland character varying(8) NOT NULL,
105 kda_id integer,
106 kreis character varying(8),
107 nuts character varying(10),
108 regbezirk character varying(8),
109 bezeichnung character varying(80) NOT NULL,
110 is_bundesland character(1) NOT NULL,
111 is_gemeinde character(1) NOT NULL,
112 is_landkreis character(1) NOT NULL,
113 is_regbezirk character(1) NOT NULL,
114 koord_x_extern character varying(22),
115 koord_y_extern character varying(22),
116 plz character varying(6),
117 longitude double precision,
118 latitude double precision
119 );
120
121
122 CREATE TABLE netz_betreiber (
123 id character varying(2) PRIMARY KEY,
124 netzbetreiber character varying(50),
125 idf_netzbetreiber character varying(1),
126 is_bmn boolean DEFAULT false,
127 mailverteiler character varying(512),
128 aktiv boolean DEFAULT false,
129 zust_mst_id character varying(5)
130 );
131
132
133 CREATE TABLE mess_stelle (
134 id character varying(5) PRIMARY KEY,
135 netzbetreiber_id character varying(2),
136 beschreibung character varying(300),
137 mess_stelle character varying(60),
138 mst_typ character varying(1),
139 amtskennung character varying(6)
140 );
141
142
80 CREATE SEQUENCE auth_id_seq 143 CREATE SEQUENCE auth_id_seq
81 START WITH 1 144 START WITH 1
82 INCREMENT BY 1 145 INCREMENT BY 1
83 NO MINVALUE 146 NO MINVALUE
84 NO MAXVALUE 147 NO MAXVALUE
147 CACHE 1; 210 CACHE 1;
148 211
149 CREATE TABLE datensatz_erzeuger ( 212 CREATE TABLE datensatz_erzeuger (
150 id integer PRIMARY KEY 213 id integer PRIMARY KEY
151 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), 214 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
152 netzbetreiber_id character varying(2), 215 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
153 da_erzeuger_id character varying(2), 216 da_erzeuger_id character varying(2),
154 mst_id character varying(5), 217 mst_id character varying(5) REFERENCES mess_stelle,
155 bezeichnung character varying(120), 218 bezeichnung character varying(120),
156 letzte_aenderung timestamp without time zone 219 letzte_aenderung timestamp without time zone
157 ); 220 );
158 221
159 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; 222 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
334 beschreibung character varying(300), 397 beschreibung character varying(300),
335 messmethode character varying(50) 398 messmethode character varying(50)
336 ); 399 );
337 400
338 401
339 CREATE TABLE mess_stelle (
340 id character varying(5) PRIMARY KEY,
341 netzbetreiber_id character varying(2),
342 beschreibung character varying(300),
343 mess_stelle character varying(60),
344 mst_typ character varying(1),
345 amtskennung character varying(6)
346 );
347
348
349
350 CREATE SEQUENCE messgroesse_id_seq 402 CREATE SEQUENCE messgroesse_id_seq
351 START WITH 1 403 START WITH 1
352 INCREMENT BY 1 404 INCREMENT BY 1
353 NO MINVALUE 405 NO MINVALUE
354 NO MAXVALUE 406 NO MAXVALUE
393 CACHE 1; 445 CACHE 1;
394 446
395 CREATE TABLE messprogramm_kategorie ( 447 CREATE TABLE messprogramm_kategorie (
396 id integer PRIMARY KEY 448 id integer PRIMARY KEY
397 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass), 449 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
398 netzbetreiber_id character varying(2), 450 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
399 mpl_id character varying(3), 451 mpl_id character varying(3),
400 bezeichnung character varying(120), 452 bezeichnung character varying(120),
401 letzte_aenderung timestamp without time zone 453 letzte_aenderung timestamp without time zone
402 ); 454 );
403 455
423 FROM mmt_messgroesse_grp, 475 FROM mmt_messgroesse_grp,
424 mg_grp 476 mg_grp
425 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); 477 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
426 478
427 479
428 CREATE TABLE netz_betreiber ( 480 CREATE TABLE ort_typ (
429 id character varying(2) PRIMARY KEY, 481 id smallint PRIMARY KEY,
430 netzbetreiber character varying(50), 482 ort_typ character varying(60)
431 idf_netzbetreiber character varying(1),
432 is_bmn boolean DEFAULT false,
433 mailverteiler character varying(512),
434 aktiv boolean DEFAULT false,
435 zust_mst_id character varying(5)
436 ); 483 );
437 484
438 485
439 CREATE SEQUENCE ort_id_seq 486 CREATE SEQUENCE ort_id_seq
440 START WITH 1 487 START WITH 1
443 NO MAXVALUE 490 NO MAXVALUE
444 CACHE 1; 491 CACHE 1;
445 492
446 CREATE TABLE ort ( 493 CREATE TABLE ort (
447 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), 494 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
448 netzbetreiber_id character varying(2), 495 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
449 ort_id character varying(10), 496 ort_id character varying(10),
450 langtext character varying(100), 497 langtext character varying(100),
451 staat_id smallint, 498 staat_id smallint REFERENCES staat,
452 gem_id character varying(8), 499 gem_id character varying(8) REFERENCES verwaltungseinheit,
453 unscharf character(1) DEFAULT NULL::bpchar, 500 unscharf character(1) DEFAULT NULL::bpchar,
454 nuts_code character varying(10), 501 nuts_code character varying(10),
455 kda_id integer, 502 kda_id integer REFERENCES koordinaten_art,
456 koord_x_extern character varying(22), 503 koord_x_extern character varying(22),
457 koord_y_extern character varying(22), 504 koord_y_extern character varying(22),
458 hoehe_land real, 505 hoehe_land real,
459 letzte_aenderung timestamp without time zone DEFAULT now(), 506 letzte_aenderung timestamp without time zone DEFAULT now(),
460 latitude double precision, 507 latitude double precision,
461 longitude double precision, 508 longitude double precision,
462 geom public.geometry(Point,4326), 509 geom public.geometry(Point,4326),
463 shape public.geometry(MultiPolygon,4326), 510 shape public.geometry(MultiPolygon,4326),
464 ort_typ smallint, 511 ort_typ smallint REFERENCES ort_typ,
465 kurztext character varying(15), 512 kurztext character varying(15),
466 berichtstext character varying(70), 513 berichtstext character varying(70),
467 zone character varying(1), 514 zone character varying(1),
468 sektor character varying(2), 515 sektor character varying(2),
469 zustaendigkeit character varying(10), 516 zustaendigkeit character varying(10),
471 aktiv character(1), 518 aktiv character(1),
472 anlage_id integer, 519 anlage_id integer,
473 oz_id integer 520 oz_id integer
474 ); 521 );
475 522
523 ALTER TABLE ONLY ort
524 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
525
526 ALTER TABLE ONLY ort
527 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
528
476 ALTER SEQUENCE ort_id_seq OWNED BY ort.id; 529 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
477
478
479 CREATE TABLE ort_typ (
480 id smallint PRIMARY KEY,
481 ort_typ character varying(60)
482 );
483 530
484 531
485 CREATE TABLE ortszuordnung_typ ( 532 CREATE TABLE ortszuordnung_typ (
486 id character(1) PRIMARY KEY, 533 id character(1) PRIMARY KEY,
487 ortstyp character varying(60) 534 ortstyp character varying(60)
540 NO MAXVALUE 587 NO MAXVALUE
541 CACHE 1; 588 CACHE 1;
542 589
543 CREATE TABLE probenehmer ( 590 CREATE TABLE probenehmer (
544 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), 591 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
545 netzbetreiber_id character varying(2), 592 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
546 prn_id character varying(9), 593 prn_id character varying(9),
547 bearbeiter character varying(25), 594 bearbeiter character varying(25),
548 bemerkung character varying(60), 595 bemerkung character varying(60),
549 betrieb character varying(80), 596 betrieb character varying(80),
550 bezeichnung character varying(80), 597 bezeichnung character varying(80),
596 index integer 643 index integer
597 ); 644 );
598 645
599 ALTER SEQUENCE result_id_seq OWNED BY result.id; 646 ALTER SEQUENCE result_id_seq OWNED BY result.id;
600 647
601
602
603 CREATE SEQUENCE staat_id_seq
604 START WITH 1
605 INCREMENT BY 1
606 NO MINVALUE
607 NO MAXVALUE
608 CACHE 1;
609
610 CREATE TABLE staat (
611 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
612 staat character varying(50) NOT NULL,
613 hkl_id smallint NOT NULL,
614 staat_iso character varying(2) NOT NULL,
615 staat_kurz character varying(5),
616 eu character(1) DEFAULT NULL::bpchar,
617 koord_x_extern character varying(22),
618 koord_y_extern character varying(22),
619 kda_id integer
620 );
621
622 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
623 648
624 649
625 -- Status workflow 650 -- Status workflow
626 CREATE TABLE status_stufe ( 651 CREATE TABLE status_stufe (
627 id integer PRIMARY KEY, 652 id integer PRIMARY KEY,
743 umwelt_bereich character varying(80) NOT NULL, 768 umwelt_bereich character varying(80) NOT NULL,
744 meh_id integer 769 meh_id integer
745 ); 770 );
746 771
747 772
748 CREATE TABLE verwaltungseinheit (
749 id character varying(8) PRIMARY KEY,
750 bundesland character varying(8) NOT NULL,
751 kda_id integer,
752 kreis character varying(8),
753 nuts character varying(10),
754 regbezirk character varying(8),
755 bezeichnung character varying(80) NOT NULL,
756 is_bundesland character(1) NOT NULL,
757 is_gemeinde character(1) NOT NULL,
758 is_landkreis character(1) NOT NULL,
759 is_regbezirk character(1) NOT NULL,
760 koord_x_extern character varying(22),
761 koord_y_extern character varying(22),
762 plz character varying(6),
763 longitude double precision,
764 latitude double precision
765 );
766
767
768 773
769 774
770 ALTER TABLE ONLY lada_user 775 ALTER TABLE ONLY lada_user
771 ADD CONSTRAINT lada_user_name_key UNIQUE (name); 776 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
772 777
822 ALTER TABLE ONLY auth 827 ALTER TABLE ONLY auth
823 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); 828 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
824 829
825 830
826 831
827 ALTER TABLE ONLY datensatz_erzeuger
828 ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
829
830
831
832 ALTER TABLE ONLY datensatz_erzeuger
833 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
834
835
836
837 ALTER TABLE ONLY favorite 832 ALTER TABLE ONLY favorite
838 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); 833 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
839 834
840 835
841 836
862 ALTER TABLE ONLY deskriptoren 857 ALTER TABLE ONLY deskriptoren
863 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); 858 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
864 859
865 860
866 861
867 ALTER TABLE ONLY messprogramm_kategorie
868 ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
869
870
871
872 ALTER TABLE ONLY ort
873 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
874
875
876
877 ALTER TABLE ONLY ort
878 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
879
880
881
882 ALTER TABLE ONLY ort
883 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
884
885
886
887 ALTER TABLE ONLY ort
888 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
889
890
891
892 ALTER TABLE ONLY ort
893 ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
894
895
896
897 ALTER TABLE ONLY ort
898 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
899
900
901
902 ALTER TABLE ONLY ort
903 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
904
905
906
907 ALTER TABLE ONLY pflicht_messgroesse 862 ALTER TABLE ONLY pflicht_messgroesse
908 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); 863 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
909 864
910 865
911 866
922 ALTER TABLE ONLY proben_zusatz 877 ALTER TABLE ONLY proben_zusatz
923 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); 878 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
924 879
925 880
926 881
927 ALTER TABLE ONLY probenehmer
928 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
929
930
931
932 ALTER TABLE ONLY result 882 ALTER TABLE ONLY result
933 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); 883 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
934 884
935 885
936 886
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)