Mercurial > lada > lada-server

comparison db_schema/stammdaten_schema.sql @ 1097:186d602e031a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged branch schema-update into default.
author Tom Gottfried <tom@intevation.de>
date Fri, 14 Oct 2016 18:17:42 +0200
parents effef57f3f7e
children 46df70a27767
comparison
equal deleted inserted replaced
1096:565c8a67034d 1097:186d602e031a
83 return (result); 83 return (result);
84 end; 84 end;
85 $$; 85 $$;
86 86
87 87
88 CREATE SEQUENCE staat_id_seq 88 CREATE TABLE koordinaten_art (
89 START WITH 1 89 id serial PRIMARY KEY,
90 INCREMENT BY 1 90 koordinatenart character varying(50),
91 NO MINVALUE 91 idf_geo_key character varying(1)
92 NO MAXVALUE 92 );
93 CACHE 1; 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 UNIQUE (umwelt_bereich)
110 );
111
112
113 CREATE TABLE betriebsart (
114 id serial PRIMARY KEY,
115 name character varying(30) NOT NULL
116 );
117 INSERT INTO betriebsart VALUES(0, 'Normal-/Routinebetrieb');
118 INSERT INTO betriebsart VALUES(1, 'Störfall-/Intensivbetrieb');
119
94 120
95 CREATE TABLE staat ( 121 CREATE TABLE staat (
96 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), 122 id serial PRIMARY KEY,
97 staat character varying(50) NOT NULL, 123 staat character varying(50) NOT NULL,
98 hkl_id smallint NOT NULL, 124 hkl_id smallint NOT NULL,
99 staat_iso character varying(2) NOT NULL, 125 staat_iso character varying(2) NOT NULL,
100 staat_kurz character varying(5), 126 staat_kurz character varying(5),
101 eu character(1) DEFAULT NULL::bpchar, 127 eu character(1) DEFAULT NULL::bpchar,
102 koord_x_extern character varying(22), 128 koord_x_extern character varying(22),
103 koord_y_extern character varying(22), 129 koord_y_extern character varying(22),
104 kda_id integer 130 kda_id integer REFERENCES koordinaten_art
105 ); 131 );
106
107 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
108 132
109 133
110 CREATE TABLE verwaltungseinheit ( 134 CREATE TABLE verwaltungseinheit (
111 id character varying(8) PRIMARY KEY, 135 id character varying(8) PRIMARY KEY,
112 bundesland character varying(8) NOT NULL, 136 bundesland character varying(8) NOT NULL,
113 kda_id integer, 137 kda_id integer REFERENCES koordinaten_art,
114 kreis character varying(8), 138 kreis character varying(8),
115 nuts character varying(10), 139 nuts character varying(10),
116 regbezirk character varying(8), 140 regbezirk character varying(8),
117 bezeichnung character varying(80) NOT NULL, 141 bezeichnung character varying(80) NOT NULL,
118 is_bundesland character(1) NOT NULL, 142 is_bundesland character(1) NOT NULL,
131 id character varying(2) PRIMARY KEY, 155 id character varying(2) PRIMARY KEY,
132 netzbetreiber character varying(50), 156 netzbetreiber character varying(50),
133 idf_netzbetreiber character varying(1), 157 idf_netzbetreiber character varying(1),
134 is_bmn boolean DEFAULT false, 158 is_bmn boolean DEFAULT false,
135 mailverteiler character varying(512), 159 mailverteiler character varying(512),
136 aktiv boolean DEFAULT false, 160 aktiv boolean DEFAULT false
137 zust_mst_id character varying(5)
138 ); 161 );
139 162
140 163
141 CREATE TABLE mess_stelle ( 164 CREATE TABLE mess_stelle (
142 id character varying(5) PRIMARY KEY, 165 id character varying(5) PRIMARY KEY,
146 mst_typ character varying(1), 169 mst_typ character varying(1),
147 amtskennung character varying(6) 170 amtskennung character varying(6)
148 ); 171 );
149 172
150 173
151 CREATE SEQUENCE auth_id_seq
152 START WITH 1
153 INCREMENT BY 1
154 NO MINVALUE
155 NO MAXVALUE
156 CACHE 1;
157
158 CREATE TABLE auth (
159 id integer PRIMARY KEY DEFAULT nextval('auth_id_seq'::regclass),
160 ldap_group character varying(40) NOT NULL,
161 netzbetreiber_id character varying(2),
162 mst_id character varying(5),
163 labor_mst_id character varying(5),
164 funktion_id smallint
165 );
166
167 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
168
169
170 CREATE TABLE auth_funktion ( 174 CREATE TABLE auth_funktion (
171 id smallint PRIMARY KEY, 175 id smallint PRIMARY KEY,
172 funktion character varying(40) UNIQUE NOT NULL 176 funktion character varying(40) UNIQUE NOT NULL
173 ); 177 );
174 INSERT INTO auth_funktion VALUES (0, 'Erfasser'); 178 INSERT INTO auth_funktion VALUES (0, 'Erfasser');
176 INSERT INTO auth_funktion VALUES (2, 'Status-Land'); 180 INSERT INTO auth_funktion VALUES (2, 'Status-Land');
177 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); 181 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle');
178 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); 182 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
179 183
180 184
181 CREATE SEQUENCE auth_lst_umw_id_seq 185 CREATE TABLE auth (
182 START WITH 1 186 id serial PRIMARY KEY,
183 INCREMENT BY 1 187 ldap_group character varying(40) NOT NULL,
184 NO MINVALUE 188 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
185 NO MAXVALUE 189 mst_id character varying(5) REFERENCES mess_stelle,
186 CACHE 1; 190 labor_mst_id character varying(5) REFERENCES mess_stelle,
191 funktion_id smallint REFERENCES auth_funktion
192 );
193
187 194
188 CREATE TABLE auth_lst_umw ( 195 CREATE TABLE auth_lst_umw (
189 id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass), 196 id serial PRIMARY KEY,
190 lst_id character varying(5), 197 mst_id character varying(5) REFERENCES mess_stelle,
191 umw_id character varying(3) 198 umw_id character varying(3) REFERENCES umwelt
192 ); 199 );
193 200
194 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
195
196
197 CREATE SEQUENCE datenbasis_id_seq
198 START WITH 1
199 INCREMENT BY 1
200 NO MINVALUE
201 NO MAXVALUE
202 CACHE 1;
203 201
204 CREATE TABLE datenbasis ( 202 CREATE TABLE datenbasis (
205 id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass), 203 id serial PRIMARY KEY,
206 beschreibung character varying(30), 204 beschreibung character varying(30),
207 datenbasis character varying(6) 205 datenbasis character varying(6)
208 ); 206 );
209 207
210 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
211
212
213 CREATE SEQUENCE datensatz_erzeuger_id_seq
214 START WITH 1
215 INCREMENT BY 1
216 NO MINVALUE
217 NO MAXVALUE
218 CACHE 1;
219 208
220 CREATE TABLE datensatz_erzeuger ( 209 CREATE TABLE datensatz_erzeuger (
221 id integer PRIMARY KEY 210 id serial PRIMARY KEY,
222 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
223 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, 211 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
224 da_erzeuger_id character varying(2) NOT NULL, 212 datensatz_erzeuger_id character varying(2) NOT NULL,
225 mst_id character varying(5) NOT NULL REFERENCES mess_stelle, 213 mst_id character varying(5) NOT NULL REFERENCES mess_stelle,
226 bezeichnung character varying(120) NOT NULL, 214 bezeichnung character varying(120) NOT NULL,
227 letzte_aenderung timestamp without time zone, 215 letzte_aenderung timestamp without time zone,
228 UNIQUE(da_erzeuger_id, netzbetreiber_id) 216 UNIQUE(datensatz_erzeuger_id, netzbetreiber_id)
229 ); 217 );
230 CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 218 CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
231 219
232 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; 220 CREATE TABLE de_vg (
221 id serial PRIMARY KEY,
222 use double precision,
223 rs character varying(12),
224 gf double precision,
225 rau_rs character varying(12),
226 gen character varying(50),
227 des character varying(75),
228 isn double precision,
229 bemerk character varying(75),
230 nambild character varying(16),
231 ags character varying(12),
232 rs_alt character varying(20),
233 wirksamkei date,
234 debkg_id character varying(16),
235 length numeric,
236 shape_area numeric,
237 geom public.geometry(MultiPolygon,4326)
238 );
239
233 240
234 CREATE TABLE deskriptor_umwelt ( 241 CREATE TABLE deskriptor_umwelt (
235 id integer PRIMARY KEY, 242 id serial PRIMARY KEY,
236 s00 integer NOT NULL, 243 s00 integer NOT NULL,
237 s01 integer NOT NULL, 244 s01 integer NOT NULL,
238 s02 integer, 245 s02 integer,
239 s03 integer, 246 s03 integer,
240 s04 integer, 247 s04 integer,
244 s08 integer, 251 s08 integer,
245 s09 integer, 252 s09 integer,
246 s10 integer, 253 s10 integer,
247 s11 integer, 254 s11 integer,
248 s12 integer, 255 s12 integer,
249 umw_id character varying(3) NOT NULL 256 umw_id character varying(3) NOT NULL REFERENCES umwelt
250 ); 257 );
251 258
252
253 CREATE SEQUENCE deskriptoren_id_seq
254 START WITH 1
255 INCREMENT BY 1
256 NO MINVALUE
257 NO MAXVALUE
258 CACHE 1;
259 259
260 CREATE TABLE deskriptoren ( 260 CREATE TABLE deskriptoren (
261 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass), 261 id serial PRIMARY KEY,
262 vorgaenger integer, 262 vorgaenger integer REFERENCES deskriptoren,
263 ebene smallint, 263 ebene smallint,
264 s_xx integer, 264 s_xx integer,
265 sn smallint, 265 sn smallint,
266 beschreibung character varying(100), 266 beschreibung character varying(100),
267 bedeutung character varying(300) 267 bedeutung character varying(300)
268 ); 268 );
269 269
270 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
271
272
273 CREATE SEQUENCE lada_user_id_seq
274 START WITH 1
275 INCREMENT BY 1
276 NO MINVALUE
277 NO MAXVALUE
278 CACHE 1;
279 270
280 CREATE TABLE lada_user ( 271 CREATE TABLE lada_user (
281 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass), 272 id serial PRIMARY KEY,
282 name character varying(80) NOT NULL 273 name character varying(80) NOT NULL,
283 ); 274 UNIQUE (name)
284 275 );
285 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id; 276
286 277
287 278 CREATE TABLE query_type (
288 CREATE SEQUENCE query_id_seq 279 id serial PRIMARY KEY,
289 START WITH 1 280 type character varying(30) NOT NULL
290 INCREMENT BY 1 281 );
291 NO MINVALUE 282 INSERT INTO query_type VALUES(0, 'probe');
292 NO MAXVALUE 283 INSERT INTO query_type VALUES(1, 'messung');
293 CACHE 1; 284 INSERT INTO query_type VALUES(2, 'messprogramm');
285 INSERT INTO query_type VALUES(3, 'ort');
286 INSERT INTO query_type VALUES(4, 'probenehmer');
287 INSERT INTO query_type VALUES(5, 'datensatzerzeuger');
288 INSERT INTO query_type VALUES(6, 'messprogrammkategorie');
289
294 290
295 CREATE TABLE query ( 291 CREATE TABLE query (
296 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass), 292 id serial PRIMARY KEY,
297 name character varying(80) NOT NULL, 293 name character varying(80) NOT NULL,
298 type character varying(30) NOT NULL 294 type integer NOT NULL REFERENCES query_type,
299 CHECK(type IN('probe', 'messung', 'messprogramm', 'ort', 295 sql character varying(2500) NOT NULL,
300 'probenehmer', 'datensatzerzeuger', 'messprogrammkategorie')),
301 sql character varying(1500) NOT NULL,
302 description character varying(100), 296 description character varying(100),
303 UNIQUE (name, type) 297 UNIQUE (name, type)
304 ); 298 );
305 299
306 ALTER SEQUENCE query_id_seq OWNED BY query.id;
307
308
309 CREATE SEQUENCE favorite_id_seq
310 START WITH 1
311 INCREMENT BY 1
312 NO MINVALUE
313 NO MAXVALUE
314 CACHE 1;
315 300
316 CREATE TABLE favorite ( 301 CREATE TABLE favorite (
317 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass), 302 id serial PRIMARY KEY,
318 user_id integer NOT NULL REFERENCES lada_user, 303 user_id integer NOT NULL REFERENCES lada_user,
319 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE 304 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE
320 ); 305 );
321 306
322 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; 307
323 308 CREATE TABLE filter_type (
324 309 id serial PRIMARY KEY,
325 CREATE SEQUENCE filter_id_seq 310 type character varying(10) NOT NULL
326 START WITH 1 311 );
327 INCREMENT BY 1 312 INSERT INTO filter_type VALUES(0, 'text');
328 NO MINVALUE 313 INSERT INTO filter_type VALUES(1, 'listmst');
329 NO MAXVALUE 314 INSERT INTO filter_type VALUES(2, 'listnetz');
330 CACHE 1; 315 INSERT INTO filter_type VALUES(3, 'listumw');
316 INSERT INTO filter_type VALUES(4, 'liststatus');
317 INSERT INTO filter_type VALUES(5, 'number');
318
331 319
332 CREATE TABLE filter ( 320 CREATE TABLE filter (
333 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass), 321 id serial PRIMARY KEY,
334 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, 322 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
335 data_index character varying(50) NOT NULL, 323 data_index character varying(50) NOT NULL,
336 type character varying(10) NOT NULL 324 type integer NOT NULL REFERENCES filter_type,
337 CHECK(type IN('liststatus', 'listmst', 'listnetz', 'listumw', 'text')),
338 label character varying(50) NOT NULL, 325 label character varying(50) NOT NULL,
339 multiselect boolean 326 multiselect boolean
340 ); 327 );
341 328
342 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
343
344
345 CREATE SEQUENCE filter_value_id_seq
346 START WITH 1
347 INCREMENT BY 1
348 NO MINVALUE
349 NO MAXVALUE
350 CACHE 1;
351 329
352 CREATE TABLE filter_value ( 330 CREATE TABLE filter_value (
353 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass), 331 id serial PRIMARY KEY,
354 user_id integer NOT NULL REFERENCES lada_user, 332 user_id integer NOT NULL REFERENCES lada_user,
355 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE, 333 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
356 value text 334 value text
357 ); 335 );
358
359 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
360
361
362 CREATE SEQUENCE koordinaten_art_id_seq
363 START WITH 1
364 INCREMENT BY 1
365 NO MINVALUE
366 NO MAXVALUE
367 CACHE 1;
368
369 CREATE TABLE koordinaten_art (
370 id integer PRIMARY KEY DEFAULT nextval('koordinaten_art_id_seq'::regclass),
371 koordinatenart character varying(50),
372 idf_geo_key character varying(1)
373 );
374
375 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
376
377
378 CREATE SEQUENCE mess_einheit_id_seq
379 START WITH 1
380 INCREMENT BY 1
381 NO MINVALUE
382 NO MAXVALUE
383 CACHE 1;
384
385 CREATE TABLE mess_einheit (
386 id integer PRIMARY KEY DEFAULT nextval('mess_einheit_id_seq'::regclass),
387 beschreibung character varying(50),
388 einheit character varying(12),
389 eudf_messeinheit_id character varying(8),
390 umrechnungs_faktor_eudf bigint
391 );
392
393 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
394 336
395 337
396 CREATE TABLE mess_methode ( 338 CREATE TABLE mess_methode (
397 id character varying(2) PRIMARY KEY, 339 id character varying(2) PRIMARY KEY,
398 beschreibung character varying(300), 340 beschreibung character varying(300),
399 messmethode character varying(50) 341 messmethode character varying(50)
400 ); 342 );
401 343
402 344
403 CREATE SEQUENCE messgroesse_id_seq
404 START WITH 1
405 INCREMENT BY 1
406 NO MINVALUE
407 NO MAXVALUE
408 CACHE 1;
409
410 CREATE TABLE messgroesse ( 345 CREATE TABLE messgroesse (
411 id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass), 346 id serial PRIMARY KEY,
412 beschreibung character varying(300), 347 beschreibung character varying(300),
413 messgroesse character varying(50) NOT NULL, 348 messgroesse character varying(50) NOT NULL,
414 default_farbe character varying(9), 349 default_farbe character varying(9),
415 idf_nuklid_key character varying(6), 350 idf_nuklid_key character varying(6),
416 ist_leitnuklid boolean DEFAULT false, 351 ist_leitnuklid boolean DEFAULT false,
417 eudf_nuklid_id bigint, 352 eudf_nuklid_id bigint,
418 kennung_bvl character varying(7) 353 kennung_bvl character varying(7)
419 ); 354 );
420 355
421 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
422
423
424 CREATE SEQUENCE messgroessen_gruppe_id_seq
425 START WITH 1
426 INCREMENT BY 1
427 NO MINVALUE
428 NO MAXVALUE
429 CACHE 1;
430 356
431 CREATE TABLE messgroessen_gruppe ( 357 CREATE TABLE messgroessen_gruppe (
432 id integer PRIMARY KEY 358 id serial PRIMARY KEY,
433 DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass),
434 bezeichnung character varying(80), 359 bezeichnung character varying(80),
435 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar 360 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
436 ); 361 );
437 362
438 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
439
440
441 CREATE SEQUENCE messprogramm_kategorie_id_seq
442 START WITH 1
443 INCREMENT BY 1
444 NO MINVALUE
445 NO MAXVALUE
446 CACHE 1;
447 363
448 CREATE TABLE messprogramm_kategorie ( 364 CREATE TABLE messprogramm_kategorie (
449 id integer PRIMARY KEY 365 id serial PRIMARY KEY,
450 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
451 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, 366 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
452 mpl_id character varying(3) NOT NULL, 367 code character varying(3) NOT NULL,
453 bezeichnung character varying(120) NOT NULL, 368 bezeichnung character varying(120) NOT NULL,
454 letzte_aenderung timestamp without time zone, 369 letzte_aenderung timestamp without time zone,
455 UNIQUE(mpl_id, netzbetreiber_id) 370 UNIQUE(code, netzbetreiber_id)
456 ); 371 );
457 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 372 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
458 373
459 ALTER SEQUENCE messprogramm_kategorie_id_seq
460 OWNED BY messprogramm_kategorie.id;
461
462 374
463 CREATE TABLE mg_grp ( 375 CREATE TABLE mg_grp (
464 messgroessengruppe_id integer NOT NULL, 376 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
465 messgroesse_id integer NOT NULL 377 messgroesse_id integer NOT NULL REFERENCES messgroesse
466 ); 378 );
379 ALTER TABLE ONLY mg_grp
380 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
467 381
468 382
469 CREATE TABLE mmt_messgroesse_grp ( 383 CREATE TABLE mmt_messgroesse_grp (
470 messgroessengruppe_id integer NOT NULL, 384 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
471 mmt_id character varying(2) NOT NULL 385 mmt_id character varying(2) NOT NULL REFERENCES mess_methode
472 ); 386 );
387 ALTER TABLE ONLY mmt_messgroesse_grp
388 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
389
390
473 391
474 392
475 CREATE VIEW mmt_messgroesse AS 393 CREATE VIEW mmt_messgroesse AS
476 SELECT mmt_messgroesse_grp.mmt_id, 394 SELECT mmt_messgroesse_grp.mmt_id,
477 mg_grp.messgroesse_id 395 mg_grp.messgroesse_id
483 CREATE TABLE ort_typ ( 401 CREATE TABLE ort_typ (
484 id smallint PRIMARY KEY, 402 id smallint PRIMARY KEY,
485 ort_typ character varying(60) 403 ort_typ character varying(60)
486 ); 404 );
487 405
488 406 CREATE TABLE kta (
489 CREATE SEQUENCE ort_id_seq 407 id serial NOT NULL,
490 START WITH 1 408 code character varying(7),
491 INCREMENT BY 1 409 bezeichnung character varying(80),
492 NO MINVALUE 410 CONSTRAINT kta_pkey PRIMARY KEY (id)
493 NO MAXVALUE 411 );
494 CACHE 1; 412 COMMENT ON TABLE kta
413 IS 'kernteschnische Anlagen';
414
415 CREATE TABLE ortszusatz (
416 id serial NOT NULL,
417 code character varying(7),
418 bezeichnung character varying(80),
419 CONSTRAINT ortszusatz_pkey PRIMARY KEY (id)
420 );
495 421
496 CREATE TABLE ort ( 422 CREATE TABLE ort (
497 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), 423 id serial PRIMARY KEY,
498 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, 424 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
499 ort_id character varying(10) NOT NULL, 425 ort_id character varying(10) NOT NULL,
500 langtext character varying(100) NOT NULL, 426 langtext character varying(100) NOT NULL,
501 staat_id smallint NOT NULL REFERENCES staat, 427 staat_id smallint NOT NULL REFERENCES staat,
502 gem_id character varying(8) REFERENCES verwaltungseinheit, 428 gem_id character varying(8) REFERENCES verwaltungseinheit,
519 zustaendigkeit character varying(10), 445 zustaendigkeit character varying(10),
520 mp_art character varying(10), 446 mp_art character varying(10),
521 aktiv character(1), 447 aktiv character(1),
522 anlage_id integer, 448 anlage_id integer,
523 oz_id integer, 449 oz_id integer,
450 hoehe_ueber_nn real,
524 UNIQUE(ort_id, netzbetreiber_id) 451 UNIQUE(ort_id, netzbetreiber_id)
525 ); 452 );
453
526 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 454 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
527 455
528 ALTER TABLE ONLY ort 456 ALTER TABLE ONLY ort
529 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); 457 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id);
530 458
531 ALTER TABLE ONLY ort 459 ALTER TABLE ONLY ort
532 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); 460 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
533
534 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
535 461
536 462
537 CREATE TABLE ortszuordnung_typ ( 463 CREATE TABLE ortszuordnung_typ (
538 id character(1) PRIMARY KEY, 464 id character(1) PRIMARY KEY,
539 ortstyp character varying(60) 465 ortstyp character varying(60)
540 ); 466 );
541 467
542 468
543 CREATE SEQUENCE pflicht_messgroesse_id_seq
544 START WITH 1
545 INCREMENT BY 1
546 NO MINVALUE
547 NO MAXVALUE
548 CACHE 1;
549
550 CREATE TABLE pflicht_messgroesse ( 469 CREATE TABLE pflicht_messgroesse (
551 id integer PRIMARY KEY 470 id serial PRIMARY KEY,
552 DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass),
553 messgroesse_id integer, 471 messgroesse_id integer,
554 mmt_id character varying(2), 472 mmt_id character varying(2) REFERENCES mess_methode,
555 umw_id character varying(3), 473 umw_id character varying(3) REFERENCES umwelt,
556 datenbasis_id smallint NOT NULL 474 datenbasis_id smallint NOT NULL REFERENCES datenbasis
557 ); 475 );
558
559 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
560 476
561 477
562 CREATE TABLE proben_zusatz ( 478 CREATE TABLE proben_zusatz (
563 id character varying(3) PRIMARY KEY, 479 id character varying(3) PRIMARY KEY,
564 meh_id integer, 480 meh_id integer REFERENCES mess_einheit,
565 beschreibung character varying(50) NOT NULL, 481 beschreibung character varying(50) NOT NULL,
566 zusatzwert character varying(7) NOT NULL, 482 zusatzwert character varying(7) NOT NULL,
567 eudf_keyword character varying(40) 483 eudf_keyword character varying(40),
568 ); 484 UNIQUE (eudf_keyword)
569 485 );
570 486
571 CREATE SEQUENCE probenart_id_seq
572 START WITH 1
573 INCREMENT BY 1
574 NO MINVALUE
575 NO MAXVALUE
576 CACHE 1;
577 487
578 CREATE TABLE probenart ( 488 CREATE TABLE probenart (
579 id integer PRIMARY KEY DEFAULT nextval('probenart_id_seq'::regclass), 489 id serial PRIMARY KEY,
580 beschreibung character varying(30), 490 beschreibung character varying(30),
581 probenart character varying(5) NOT NULL, 491 probenart character varying(5) NOT NULL,
582 probenart_eudf_id character varying(1) NOT NULL 492 probenart_eudf_id character varying(1) NOT NULL
583 ); 493 );
584 494
585 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
586
587
588 CREATE SEQUENCE probenehmer_id_seq
589 START WITH 1
590 INCREMENT BY 1
591 NO MINVALUE
592 NO MAXVALUE
593 CACHE 1;
594 495
595 CREATE TABLE probenehmer ( 496 CREATE TABLE probenehmer (
596 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), 497 id serial PRIMARY KEY,
597 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, 498 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
598 prn_id character varying(9) NOT NULL, 499 prn_id character varying(9) NOT NULL,
599 bearbeiter character varying(25), 500 bearbeiter character varying(25),
600 bemerkung character varying(60), 501 bemerkung character varying(60),
601 betrieb character varying(80), 502 betrieb character varying(80),
610 letzte_aenderung timestamp without time zone, 511 letzte_aenderung timestamp without time zone,
611 UNIQUE(prn_id, netzbetreiber_id) 512 UNIQUE(prn_id, netzbetreiber_id)
612 ); 513 );
613 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 514 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
614 515
615 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
616
617
618 CREATE SEQUENCE result_id_seq
619 START WITH 1
620 INCREMENT BY 1
621 NO MINVALUE
622 NO MAXVALUE
623 CACHE 1;
624 516
625 CREATE TABLE result ( 517 CREATE TABLE result (
626 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass), 518 id serial PRIMARY KEY,
627 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, 519 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
628 data_index character varying(50) NOT NULL, 520 data_index character varying(50) NOT NULL,
629 header character varying(50) NOT NULL, 521 header character varying(50) NOT NULL,
630 width integer, 522 width integer,
631 flex boolean, 523 flex boolean,
632 index integer NOT NULL, 524 index integer NOT NULL,
633 UNIQUE (query_id, index), 525 UNIQUE (query_id, index),
634 UNIQUE (query_id, data_index) 526 UNIQUE (query_id, data_index)
635 ); 527 );
636
637 ALTER SEQUENCE result_id_seq OWNED BY result.id;
638
639 528
640 529
641 -- Status workflow 530 -- Status workflow
642 CREATE TABLE status_stufe ( 531 CREATE TABLE status_stufe (
643 id integer PRIMARY KEY, 532 id integer PRIMARY KEY,
681 INSERT INTO status_kombi VALUES (11, 3, 2); 570 INSERT INTO status_kombi VALUES (11, 3, 2);
682 INSERT INTO status_kombi VALUES (12, 3, 3); 571 INSERT INTO status_kombi VALUES (12, 3, 3);
683 INSERT INTO status_kombi VALUES (13, 3, 4); 572 INSERT INTO status_kombi VALUES (13, 3, 4);
684 573
685 574
686 CREATE SEQUENCE status_reihenfolge_id_seq;
687 CREATE TABLE status_reihenfolge ( 575 CREATE TABLE status_reihenfolge (
688 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), 576 id serial PRIMARY KEY,
689 von_id integer REFERENCES status_kombi NOT NULL, 577 von_id integer REFERENCES status_kombi NOT NULL,
690 zu_id integer REFERENCES status_kombi NOT NULL, 578 zu_id integer REFERENCES status_kombi NOT NULL,
691 UNIQUE(von_id, zu_id) 579 UNIQUE(von_id, zu_id)
692 ); 580 );
693 581
750 JOIN stammdaten.status_kombi zu 638 JOIN stammdaten.status_kombi zu
751 ON zu.id = r.zu_id 639 ON zu.id = r.zu_id
752 ); 640 );
753 -- Status workflow 641 -- Status workflow
754 642
755 643 -- Mappings for import
756 CREATE TABLE umwelt ( 644
757 id character varying(3) PRIMARY KEY, 645 CREATE TABLE messprogramm_transfer (
758 beschreibung character varying(300), 646 id serial PRIMARY KEY,
759 umwelt_bereich character varying(80) NOT NULL, 647 messprogramm_s character varying(1) NOT NULL,
760 meh_id integer 648 messprogramm_c character varying(100) NOT NULL,
761 ); 649 ba_id integer NOT NULL REFERENCES betriebsart,
762 650 datenbasis_id integer NOT NULL REFERENCES datenbasis,
763 651 UNIQUE (messprogramm_s)
764 652 );
765 653
766 ALTER TABLE ONLY lada_user 654 -- Mappings for import
767 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
768
769
770 ALTER TABLE ONLY mg_grp
771 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
772
773
774 ALTER TABLE ONLY mmt_messgroesse_grp
775 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
776
777
778 ALTER TABLE ONLY proben_zusatz
779 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
780
781
782 ALTER TABLE ONLY umwelt
783 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
784
785 655
786 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); 656 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
787 657
788 658
789
790 ALTER TABLE ONLY auth
791 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
792
793
794
795 ALTER TABLE ONLY auth
796 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
797
798
799
800 ALTER TABLE ONLY auth_lst_umw
801 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
802
803
804
805 ALTER TABLE ONLY auth_lst_umw
806 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
807
808
809
810 ALTER TABLE ONLY auth
811 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
812
813
814
815 ALTER TABLE ONLY auth
816 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
817
818
819
820 ALTER TABLE ONLY deskriptoren
821 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
822
823
824
825 ALTER TABLE ONLY pflicht_messgroesse
826 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
827
828
829
830 ALTER TABLE ONLY pflicht_messgroesse
831 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
832
833
834
835 ALTER TABLE ONLY pflicht_messgroesse
836 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
837
838
839
840 ALTER TABLE ONLY proben_zusatz
841 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
842
843
844
845 ALTER TABLE ONLY staat
846 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
847
848
849
850
851
852 ALTER TABLE ONLY umwelt
853 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
854
855
856
857 ALTER TABLE ONLY verwaltungseinheit
858 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
859
860
861 COMMIT; 659 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)