Mercurial > lada > lada-server

comparison db_schema/stammdaten_schema.sql @ 1015:9f903c54141b schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reordered tables.
author Raimund Renkert <raimund.renkert@intevation.de>
date Mon, 04 Jul 2016 12:04:50 +0200
parents 6b204c617c1f
children 8912b6f1776d
comparison
equal deleted inserted replaced
1014:6b204c617c1f 1015:9f903c54141b
83 return (result); 83 return (result);
84 end; 84 end;
85 $$; 85 $$;
86 86
87 87
88 CREATE TABLE koordinaten_art (
89 id serial PRIMARY KEY,
90 koordinatenart character varying(50),
91 idf_geo_key character varying(1)
92 );
93
94
95 CREATE TABLE mess_einheit (
96 id serial PRIMARY KEY,
97 beschreibung character varying(50),
98 einheit character varying(12),
99 eudf_messeinheit_id character varying(8),
100 umrechnungs_faktor_eudf bigint
101 );
102
103
104 CREATE TABLE umwelt (
105 id character varying(3) PRIMARY KEY,
106 beschreibung character varying(300),
107 umwelt_bereich character varying(80) NOT NULL,
108 meh_id integer REFERENCES mess_einheit
109 );
110
111
88 CREATE TABLE staat ( 112 CREATE TABLE staat (
89 id serial PRIMARY KEY, 113 id serial PRIMARY KEY,
90 staat character varying(50) NOT NULL, 114 staat character varying(50) NOT NULL,
91 hkl_id smallint NOT NULL, 115 hkl_id smallint NOT NULL,
92 staat_iso character varying(2) NOT NULL, 116 staat_iso character varying(2) NOT NULL,
136 mst_typ character varying(1), 160 mst_typ character varying(1),
137 amtskennung character varying(6) 161 amtskennung character varying(6)
138 ); 162 );
139 163
140 164
141 CREATE TABLE auth (
142 id serial PRIMARY KEY,
143 ldap_group character varying(40) NOT NULL,
144 netzbetreiber_id character varying(2),
145 mst_id character varying(5),
146 labor_mst_id character varying(5),
147 funktion_id smallint
148 );
149
150
151 CREATE TABLE auth_funktion ( 165 CREATE TABLE auth_funktion (
152 id smallint PRIMARY KEY, 166 id smallint PRIMARY KEY,
153 funktion character varying(40) UNIQUE NOT NULL 167 funktion character varying(40) UNIQUE NOT NULL
154 ); 168 );
155 INSERT INTO auth_funktion VALUES (0, 'Erfasser'); 169 INSERT INTO auth_funktion VALUES (0, 'Erfasser');
156 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser'); 170 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser');
157 INSERT INTO auth_funktion VALUES (2, 'Status-Land'); 171 INSERT INTO auth_funktion VALUES (2, 'Status-Land');
158 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); 172 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle');
159 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); 173 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
174
175
176 CREATE TABLE auth (
177 id serial PRIMARY KEY,
178 ldap_group character varying(40) NOT NULL,
179 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
180 mst_id character varying(5) REFERENCES mess_stelle,
181 labor_mst_id character varying(5) REFERENCES mess_stelle,
182 funktion_id smallint REFERENCES auth_funktion
183 );
160 184
161 185
162 CREATE TABLE auth_lst_umw ( 186 CREATE TABLE auth_lst_umw (
163 id serial PRIMARY KEY, 187 id serial PRIMARY KEY,
164 mst_id character varying(5), 188 mst_id character varying(5),
271 CREATE TABLE filter_value ( 295 CREATE TABLE filter_value (
272 id serial PRIMARY KEY, 296 id serial PRIMARY KEY,
273 user_id integer NOT NULL REFERENCES lada_user, 297 user_id integer NOT NULL REFERENCES lada_user,
274 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE, 298 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
275 value text 299 value text
276 );
277
278
279 CREATE TABLE koordinaten_art (
280 id serial PRIMARY KEY,
281 koordinatenart character varying(50),
282 idf_geo_key character varying(1)
283 );
284
285
286 CREATE TABLE mess_einheit (
287 id serial PRIMARY KEY,
288 beschreibung character varying(50),
289 einheit character varying(12),
290 eudf_messeinheit_id character varying(8),
291 umrechnungs_faktor_eudf bigint
292 ); 300 );
293 301
294 302
295 CREATE TABLE mess_methode ( 303 CREATE TABLE mess_methode (
296 id character varying(2) PRIMARY KEY, 304 id character varying(2) PRIMARY KEY,
571 ON zu.id = r.zu_id 579 ON zu.id = r.zu_id
572 ); 580 );
573 -- Status workflow 581 -- Status workflow
574 582
575 583
576 CREATE TABLE umwelt (
577 id character varying(3) PRIMARY KEY,
578 beschreibung character varying(300),
579 umwelt_bereich character varying(80) NOT NULL,
580 meh_id integer
581 );
582
583
584 ALTER TABLE ONLY lada_user 584 ALTER TABLE ONLY lada_user
585 ADD CONSTRAINT lada_user_name_key UNIQUE (name); 585 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
586 586
587 587
588 ALTER TABLE ONLY mg_grp 588 ALTER TABLE ONLY mg_grp
605 605
606 606
607 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); 607 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
608 608
609 609
610
611 ALTER TABLE ONLY auth
612 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
613
614
615
616 ALTER TABLE ONLY auth
617 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
618
619
620
621 ALTER TABLE ONLY auth_lst_umw
622 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
623
624
625
626 ALTER TABLE ONLY auth_lst_umw
627 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
628
629
630
631 ALTER TABLE ONLY auth
632 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
633
634
635
636 ALTER TABLE ONLY auth
637 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
638
639
640
641 ALTER TABLE ONLY deskriptoren
642 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
643
644
645
646 ALTER TABLE ONLY pflicht_messgroesse
647 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
648
649
650
651 ALTER TABLE ONLY pflicht_messgroesse
652 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
653
654
655
656 ALTER TABLE ONLY pflicht_messgroesse
657 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
658
659
660
661 ALTER TABLE ONLY proben_zusatz
662 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
663
664
665
666 ALTER TABLE ONLY staat
667 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
668
669
670
671
672
673 ALTER TABLE ONLY umwelt
674 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
675
676
677
678 ALTER TABLE ONLY verwaltungseinheit
679 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
680
681
682 COMMIT; 610 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)