Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 993:196800bb22b0 schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Replaced translation tables for probe and messung with id_alt columns.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 17:46:39 +0200
parents 9002ebe2a8af
children 5886384dcb92
comparison
equal deleted inserted replaced
992:9002ebe2a8af 993:196800bb22b0
368 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: 368 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
369 -- 369 --
370 370
371 CREATE TABLE messung ( 371 CREATE TABLE messung (
372 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, 372 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL,
373 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
373 probe_id integer NOT NULL, 374 probe_id integer NOT NULL,
374 nebenproben_nr character varying(10), 375 nebenproben_nr character varying(10),
375 mmt_id character varying(2) NOT NULL, 376 mmt_id character varying(2) NOT NULL,
376 messdauer integer, 377 messdauer integer,
377 messzeitpunkt timestamp with time zone, 378 messzeitpunkt timestamp with time zone,
379 status integer, 380 status integer,
380 letzte_aenderung timestamp without time zone DEFAULT now(), 381 letzte_aenderung timestamp without time zone DEFAULT now(),
381 geplant boolean DEFAULT false NOT NULL, 382 geplant boolean DEFAULT false NOT NULL,
382 tree_modified timestamp without time zone DEFAULT now() 383 tree_modified timestamp without time zone DEFAULT now()
383 ); 384 );
384
385
386 --
387 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
388 --
389
390 CREATE TABLE messung_translation (
391 id integer NOT NULL,
392 messungs_id integer NOT NULL,
393 messungs_id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL
394 );
395
396
397 --
398 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
399 --
400
401 CREATE SEQUENCE messung_translation_id_seq
402 START WITH 1
403 INCREMENT BY 1
404 NO MINVALUE
405 NO MAXVALUE
406 CACHE 1;
407
408
409 --
410 -- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
411 --
412
413 ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id;
414 385
415 386
416 -- 387 --
417 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: 388 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
418 -- 389 --
459 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: 430 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
460 -- 431 --
461 432
462 CREATE TABLE probe ( 433 CREATE TABLE probe (
463 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), 434 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass),
435 id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL,
464 test boolean DEFAULT false NOT NULL, 436 test boolean DEFAULT false NOT NULL,
465 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, 437 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
466 mst_id character varying(5) NOT NULL 438 mst_id character varying(5) NOT NULL
467 REFERENCES stammdaten.mess_stelle, 439 REFERENCES stammdaten.mess_stelle,
468 labor_mst_id character varying(5) NOT NULL 440 labor_mst_id character varying(5) NOT NULL
558 -- 530 --
559 531
560 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; 532 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
561 533
562 534
563
564 --
565 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
566 --
567
568 CREATE TABLE probe_translation (
569 id integer NOT NULL,
570 probe_id integer NOT NULL,
571 probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL
572 );
573
574
575 --
576 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
577 --
578
579 CREATE SEQUENCE probe_translation_id_seq
580 START WITH 1
581 INCREMENT BY 1
582 NO MINVALUE
583 NO MAXVALUE
584 CACHE 1;
585
586
587 --
588 -- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
589 --
590
591 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id;
592
593
594 -- 535 --
595 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: 536 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
596 -- 537 --
597 538
598 CREATE TABLE status_protokoll ( 539 CREATE TABLE status_protokoll (
698 639
699 -- 640 --
700 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 641 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
701 -- 642 --
702 643
703 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass); 644 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('land.messwert_id_seq'::regclass);
645
646
647 --
648 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
649 --
650
651 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
652
653
654 --
655 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
656 --
657
658 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
704 659
705 660
706 -- 661 --
707 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 662 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
708 -- 663 --
709 664
710 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('land.messwert_id_seq'::regclass); 665 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('land.ort_id_seq'::regclass);
711
712
713 --
714 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
715 --
716
717 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
718 666
719 667
720 -- 668 --
721 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - 669 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
722 -- 670 --
723 671
724 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
725
726
727 --
728 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
729 --
730
731 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('land.ort_id_seq'::regclass);
732
733
734 --
735 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
736 --
737
738 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now(); 672 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now();
739
740
741 --
742 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
743 --
744
745 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
746 673
747 674
748 -- 675 --
749 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 676 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
750 -- 677 --
811 ALTER TABLE ONLY messung 738 ALTER TABLE ONLY messung
812 ADD CONSTRAINT messung_pkey PRIMARY KEY (id); 739 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
813 740
814 741
815 -- 742 --
816 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
817 --
818
819 ALTER TABLE ONLY messung_translation
820 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
821
822
823 --
824 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
825 --
826
827 ALTER TABLE ONLY messung_translation
828 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
829
830
831 --
832 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 743 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
833 -- 744 --
834 745
835 ALTER TABLE ONLY messwert 746 ALTER TABLE ONLY messwert
836 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id); 747 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
851 ALTER TABLE ONLY ortszuordnung 762 ALTER TABLE ONLY ortszuordnung
852 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); 763 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
853 764
854 765
855 -- 766 --
856 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
857 --
858
859 ALTER TABLE ONLY probe_translation
860 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
861
862
863 --
864 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
865 --
866
867 ALTER TABLE ONLY probe_translation
868 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
869
870
871 --
872 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 767 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
873 -- 768 --
874 769
875 ALTER TABLE ONLY status_protokoll 770 ALTER TABLE ONLY status_protokoll
876 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); 771 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
900 795
901 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id); 796 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
902 797
903 798
904 -- 799 --
905 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
906 --
907
908 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id);
909
910
911 --
912 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 800 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
913 -- 801 --
914 802
915 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id); 803 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
916 804
918 -- 806 --
919 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 807 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
920 -- 808 --
921 809
922 CREATE INDEX probe_id_idx ON probe USING btree (id); 810 CREATE INDEX probe_id_idx ON probe USING btree (id);
923
924
925 --
926 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
927 --
928
929 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id);
930 811
931 812
932 -- 813 --
933 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: - 814 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
934 -- 815 --
1044 ALTER TABLE ONLY messung 925 ALTER TABLE ONLY messung
1045 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); 926 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
1046 927
1047 928
1048 -- 929 --
1049 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1050 --
1051
1052 ALTER TABLE ONLY messung_translation
1053 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
1054
1055
1056 --
1057 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 930 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1058 -- 931 --
1059 932
1060 ALTER TABLE ONLY messwert 933 ALTER TABLE ONLY messwert
1061 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); 934 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
1100 ALTER TABLE ONLY ortszuordnung 973 ALTER TABLE ONLY ortszuordnung
1101 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 974 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1102 975
1103 976
1104 -- 977 --
1105 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1106 --
1107
1108 ALTER TABLE ONLY probe_translation
1109 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1110
1111
1112 --
1113 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 978 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1114 -- 979 --
1115 980
1116 ALTER TABLE ONLY status_protokoll 981 ALTER TABLE ONLY status_protokoll
1117 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; 982 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)