Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 994:5886384dcb92 schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Replaced explicit sequences by serials.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 17:59:24 +0200
parents 196800bb22b0
children c21d2e3c988a
comparison
equal deleted inserted replaced
993:196800bb22b0 994:5886384dcb92
132 RETURN NEW; 132 RETURN NEW;
133 END; 133 END;
134 $$; 134 $$;
135 135
136 136
137 --
138 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: land; Owner: -
139 --
140
141 CREATE SEQUENCE kommentar_id_seq
142 START WITH 1
143 INCREMENT BY 1
144 NO MINVALUE
145 NO MAXVALUE
146 CACHE 1;
147
148
149 SET default_tablespace = ''; 137 SET default_tablespace = '';
150 138
151 SET default_with_oids = false; 139 SET default_with_oids = false;
152
153
154 --
155 -- Name: messung_id_seq; Type: SEQUENCE; Schema: land; Owner: -
156 --
157
158 CREATE SEQUENCE messung_id_seq
159 START WITH 1
160 INCREMENT BY 1
161 NO MINVALUE
162 NO MAXVALUE
163 CACHE 1;
164 140
165 141
166 -- 142 --
167 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: - 143 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: -
168 -- 144 --
174 NO MAXVALUE 150 NO MAXVALUE
175 CACHE 1; 151 CACHE 1;
176 152
177 153
178 -- 154 --
179 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: land; Owner: -
180 --
181
182 CREATE SEQUENCE messung_messungs_id_seq
183 START WITH 1
184 INCREMENT BY 1
185 NO MINVALUE
186 NO MAXVALUE
187 CACHE 1;
188
189
190 --
191 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: land; Owner: -
192 --
193
194 CREATE SEQUENCE messwert_id_seq
195 START WITH 1
196 INCREMENT BY 1
197 NO MINVALUE
198 NO MAXVALUE
199 CACHE 1;
200
201
202 --
203 -- Name: ort_id_seq; Type: SEQUENCE; Schema: land; Owner: -
204 --
205
206 CREATE SEQUENCE ort_id_seq
207 START WITH 1
208 INCREMENT BY 1
209 NO MINVALUE
210 NO MAXVALUE
211 CACHE 1;
212
213
214
215 --
216 -- Name: probe_id_seq; Type: SEQUENCE; Schema: land; Owner: -
217 --
218
219 CREATE SEQUENCE probe_id_seq
220 START WITH 1
221 INCREMENT BY 1
222 NO MINVALUE
223 NO MAXVALUE
224 CACHE 1;
225
226
227 --
228 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - 155 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: -
229 -- 156 --
230 157
231 CREATE SEQUENCE probe_probe_id_seq 158 CREATE SEQUENCE probe_probe_id_seq
232 START WITH 1 159 START WITH 1
235 NO MAXVALUE 162 NO MAXVALUE
236 CACHE 1; 163 CACHE 1;
237 164
238 165
239 -- 166 --
240 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: land; Owner: -
241 --
242
243 CREATE SEQUENCE zusatz_wert_id_seq
244 START WITH 1
245 INCREMENT BY 1
246 NO MINVALUE
247 NO MAXVALUE
248 CACHE 1;
249
250 --
251 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: 167 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
252 -- 168 --
253 169
254 CREATE TABLE kommentar_m ( 170 CREATE TABLE kommentar_m (
255 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, 171 id serial NOT NULL,
256 erzeuger character varying(5) NOT NULL, 172 erzeuger character varying(5) NOT NULL,
257 datum timestamp without time zone DEFAULT now(), 173 datum timestamp without time zone DEFAULT now(),
258 text character varying(1024), 174 text character varying(1024),
259 messungs_id integer NOT NULL 175 messungs_id integer NOT NULL
260 ); 176 );
263 -- 179 --
264 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: 180 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
265 -- 181 --
266 182
267 CREATE TABLE kommentar_p ( 183 CREATE TABLE kommentar_p (
268 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, 184 id serial NOT NULL,
269 erzeuger character varying(5) NOT NULL, 185 erzeuger character varying(5) NOT NULL,
270 datum timestamp without time zone DEFAULT now(), 186 datum timestamp without time zone DEFAULT now(),
271 text character varying(1024), 187 text character varying(1024),
272 probe_id integer NOT NULL 188 probe_id integer NOT NULL
273 ); 189 );
274 190
275 191
276 -- 192 --
277 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: -
278 --
279
280 CREATE SEQUENCE messprogramm_id_seq
281 START WITH 1
282 INCREMENT BY 1
283 NO MINVALUE
284 NO MAXVALUE
285 CACHE 1;
286
287
288 --
289 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace: 193 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
290 -- 194 --
291 195
292 CREATE TABLE messprogramm ( 196 CREATE TABLE messprogramm (
293 id integer PRIMARY KEY DEFAULT nextval('messprogramm_id_seq'::regclass), 197 id serial PRIMARY KEY,
294 name character varying(256), 198 name character varying(256),
295 test boolean DEFAULT false NOT NULL, 199 test boolean DEFAULT false NOT NULL,
296 netzbetreiber_id character varying(2) NOT NULL 200 netzbetreiber_id character varying(2) NOT NULL
297 REFERENCES stammdaten.netz_betreiber, 201 REFERENCES stammdaten.netz_betreiber,
298 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, 202 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
315 probe_kommentar character varying(80), 219 probe_kommentar character varying(80),
316 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL 220 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
317 ); 221 );
318 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 222 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
319 223
320 --
321 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
322 --
323
324 ALTER SEQUENCE messprogramm_id_seq OWNED BY messprogramm.id;
325
326 224
327 -- 225 --
328 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: - 226 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
329 -- 227 --
330 228
334 -- 232 --
335 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: 233 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
336 -- 234 --
337 235
338 CREATE TABLE messprogramm_mmt ( 236 CREATE TABLE messprogramm_mmt (
339 id integer NOT NULL, 237 id serial NOT NULL,
340 messprogramm_id integer NOT NULL, 238 messprogramm_id integer NOT NULL,
341 mmt_id character varying(2) NOT NULL, 239 mmt_id character varying(2) NOT NULL,
342 messgroessen integer[], 240 messgroessen integer[],
343 letzte_aenderung timestamp without time zone DEFAULT now() 241 letzte_aenderung timestamp without time zone DEFAULT now()
344 ); 242 );
345 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 243 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
346 244
347 245
348 -- 246 --
349 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: -
350 --
351
352 CREATE SEQUENCE messprogramm_mmt_id_seq
353 START WITH 1
354 INCREMENT BY 1
355 NO MINVALUE
356 NO MAXVALUE
357 CACHE 1;
358
359
360 --
361 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
362 --
363
364 ALTER SEQUENCE messprogramm_mmt_id_seq OWNED BY messprogramm_mmt.id;
365
366
367 --
368 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: 247 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
369 -- 248 --
370 249
371 CREATE TABLE messung ( 250 CREATE TABLE messung (
372 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, 251 id serial NOT NULL,
373 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL, 252 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
374 probe_id integer NOT NULL, 253 probe_id integer NOT NULL,
375 nebenproben_nr character varying(10), 254 nebenproben_nr character varying(10),
376 mmt_id character varying(2) NOT NULL, 255 mmt_id character varying(2) NOT NULL,
377 messdauer integer, 256 messdauer integer,
387 -- 266 --
388 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: 267 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
389 -- 268 --
390 269
391 CREATE TABLE messwert ( 270 CREATE TABLE messwert (
392 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL, 271 id serial NOT NULL,
393 messungs_id integer NOT NULL, 272 messungs_id integer NOT NULL,
394 messgroesse_id integer NOT NULL, 273 messgroesse_id integer NOT NULL,
395 messwert_nwg character varying(1), 274 messwert_nwg character varying(1),
396 messwert double precision NOT NULL, 275 messwert double precision NOT NULL,
397 messfehler real, 276 messfehler real,
407 -- 286 --
408 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: 287 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
409 -- 288 --
410 289
411 CREATE TABLE ortszuordnung ( 290 CREATE TABLE ortszuordnung (
412 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL, 291 id serial NOT NULL,
413 probe_id integer NOT NULL, 292 probe_id integer NOT NULL,
414 ort_id bigint NOT NULL, 293 ort_id bigint NOT NULL,
415 ortszuordnung_typ character varying(1), 294 ortszuordnung_typ character varying(1),
416 ortszusatztext character varying(100), 295 ortszusatztext character varying(100),
417 letzte_aenderung timestamp without time zone DEFAULT now(), 296 letzte_aenderung timestamp without time zone DEFAULT now(),
429 -- 308 --
430 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: 309 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
431 -- 310 --
432 311
433 CREATE TABLE probe ( 312 CREATE TABLE probe (
434 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), 313 id serial PRIMARY KEY,
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, 314 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,
436 test boolean DEFAULT false NOT NULL, 315 test boolean DEFAULT false NOT NULL,
437 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, 316 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
438 mst_id character varying(5) NOT NULL 317 mst_id character varying(5) NOT NULL
439 REFERENCES stammdaten.mess_stelle, 318 REFERENCES stammdaten.mess_stelle,
535 -- 414 --
536 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: 415 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
537 -- 416 --
538 417
539 CREATE TABLE status_protokoll ( 418 CREATE TABLE status_protokoll (
540 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, 419 id serial NOT NULL,
541 erzeuger character varying(5) NOT NULL, 420 erzeuger character varying(5) NOT NULL,
542 datum timestamp without time zone DEFAULT now(), 421 datum timestamp without time zone DEFAULT now(),
543 text character varying(1024), 422 text character varying(1024),
544 messungs_id integer NOT NULL, 423 messungs_id integer NOT NULL,
545 status_stufe integer NOT NULL, 424 status_stufe integer NOT NULL,
551 -- 430 --
552 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 431 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
553 -- 432 --
554 433
555 CREATE TABLE zusatz_wert ( 434 CREATE TABLE zusatz_wert (
556 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL, 435 id serial NOT NULL,
557 probe_id integer NOT NULL, 436 probe_id integer NOT NULL,
558 pzs_id character varying(3) NOT NULL, 437 pzs_id character varying(3) NOT NULL,
559 messwert_pzs double precision, 438 messwert_pzs double precision,
560 messfehler real, 439 messfehler real,
561 letzte_aenderung timestamp without time zone DEFAULT now(), 440 letzte_aenderung timestamp without time zone DEFAULT now(),
564 ); 443 );
565 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 444 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
566 445
567 446
568 -- 447 --
569 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
570 --
571
572 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
573
574
575 --
576 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - 448 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
577 -- 449 --
578 450
579 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); 451 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
580 452
581 453
582 SET search_path = land, pg_catalog; 454 SET search_path = land, pg_catalog;
583 455
584 -- 456 --
585 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
586 --
587
588 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass);
589
590
591 --
592 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - 457 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
593 -- 458 --
594 459
595 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now(); 460 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
596 461
597 462
598 -- 463 --
599 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
600 --
601
602 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass);
603
604
605 --
606 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - 464 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
607 -- 465 --
608 466
609 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now(); 467 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
610 468
611 469
612 -- 470 --
613 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
614 --
615
616 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass);
617
618
619 --
620 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
621 --
622
623 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('land.messung_id_seq'::regclass);
624
625
626 --
627 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: - 471 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
628 -- 472 --
629 473
630 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false; 474 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
631 475
636 480
637 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now(); 481 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now();
638 482
639 483
640 -- 484 --
641 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
642 --
643
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: - 485 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
649 -- 486 --
650 487
651 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false; 488 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
652 489
657 494
658 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now(); 495 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
659 496
660 497
661 -- 498 --
662 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
663 --
664
665 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('land.ort_id_seq'::regclass);
666
667
668 --
669 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - 499 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
670 -- 500 --
671 501
672 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now(); 502 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now();
673 503
674 504
675 -- 505 --
676 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
677 --
678
679 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass);
680
681
682 --
683 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - 506 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
684 -- 507 --
685 508
686 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); 509 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
687 510
689 -- 512 --
690 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: - 513 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: -
691 -- 514 --
692 515
693 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now(); 516 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now();
694
695
696 --
697 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
698 --
699
700 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('land.zusatz_wert_id_seq'::regclass);
701 517
702 518
703 -- 519 --
704 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - 520 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
705 -- 521 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)