Mercurial > lada > lada-server

comparison db_schema/stammdaten_schema.sql @ 954:379480a94c81

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use ON DELETE CASCADE to ease removing queries from database.
author Tom Gottfried <tom@intevation.de>
date Tue, 24 May 2016 17:15:44 +0200
parents 9121d99a471e
children ad69878b7280
comparison
equal deleted inserted replaced
953:df78f385aadd 954:379480a94c81
299 ); 299 );
300 300
301 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; 301 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
302 302
303 303
304 CREATE SEQUENCE lada_user_id_seq
305 START WITH 1
306 INCREMENT BY 1
307 NO MINVALUE
308 NO MAXVALUE
309 CACHE 1;
310
311 CREATE TABLE lada_user (
312 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass),
313 name character varying(80) NOT NULL
314 );
315
316 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
317
318
319 CREATE SEQUENCE query_id_seq
320 START WITH 1
321 INCREMENT BY 1
322 NO MINVALUE
323 NO MAXVALUE
324 CACHE 1;
325
326 CREATE TABLE query (
327 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass),
328 name character varying(80) NOT NULL,
329 type character varying(30) NOT NULL,
330 sql character varying(1500) NOT NULL,
331 description character varying(100)
332 );
333
334 ALTER SEQUENCE query_id_seq OWNED BY query.id;
335
336
304 CREATE SEQUENCE favorite_id_seq 337 CREATE SEQUENCE favorite_id_seq
305 START WITH 1 338 START WITH 1
306 INCREMENT BY 1 339 INCREMENT BY 1
307 NO MINVALUE 340 NO MINVALUE
308 NO MAXVALUE 341 NO MAXVALUE
309 CACHE 1; 342 CACHE 1;
310 343
311 CREATE TABLE favorite ( 344 CREATE TABLE favorite (
312 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass), 345 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass),
313 user_id integer NOT NULL, 346 user_id integer NOT NULL REFERENCES lada_user,
314 query_id integer NOT NULL 347 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE
315 ); 348 );
316 349
317 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; 350 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
318 351
319 352
324 NO MAXVALUE 357 NO MAXVALUE
325 CACHE 1; 358 CACHE 1;
326 359
327 CREATE TABLE filter ( 360 CREATE TABLE filter (
328 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass), 361 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass),
329 query_id integer NOT NULL, 362 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
330 data_index character varying(50) NOT NULL, 363 data_index character varying(50) NOT NULL,
331 type character varying(10) NOT NULL, 364 type character varying(10) NOT NULL,
332 label character varying(50) NOT NULL, 365 label character varying(50) NOT NULL,
333 multiselect boolean 366 multiselect boolean
334 ); 367 );
343 NO MAXVALUE 376 NO MAXVALUE
344 CACHE 1; 377 CACHE 1;
345 378
346 CREATE TABLE filter_value ( 379 CREATE TABLE filter_value (
347 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass), 380 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass),
348 user_id integer NOT NULL, 381 user_id integer NOT NULL REFERENCES lada_user,
349 filter_id integer NOT NULL, 382 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
350 value text 383 value text
351 ); 384 );
352 385
353 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id; 386 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
354 387
365 koordinatenart character varying(50), 398 koordinatenart character varying(50),
366 idf_geo_key character varying(1) 399 idf_geo_key character varying(1)
367 ); 400 );
368 401
369 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id; 402 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
370
371
372 CREATE SEQUENCE lada_user_id_seq
373 START WITH 1
374 INCREMENT BY 1
375 NO MINVALUE
376 NO MAXVALUE
377 CACHE 1;
378
379 CREATE TABLE lada_user (
380 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass),
381 name character varying(80) NOT NULL
382 );
383
384 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
385 403
386 404
387 CREATE SEQUENCE mess_einheit_id_seq 405 CREATE SEQUENCE mess_einheit_id_seq
388 START WITH 1 406 START WITH 1
389 INCREMENT BY 1 407 INCREMENT BY 1
622 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 640 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
623 641
624 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; 642 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
625 643
626 644
627 CREATE SEQUENCE query_id_seq
628 START WITH 1
629 INCREMENT BY 1
630 NO MINVALUE
631 NO MAXVALUE
632 CACHE 1;
633
634 CREATE TABLE query (
635 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass),
636 name character varying(80) NOT NULL,
637 type character varying(30) NOT NULL,
638 sql character varying(1500) NOT NULL,
639 description character varying(100)
640 );
641
642 ALTER SEQUENCE query_id_seq OWNED BY query.id;
643
644
645 CREATE SEQUENCE result_id_seq 645 CREATE SEQUENCE result_id_seq
646 START WITH 1 646 START WITH 1
647 INCREMENT BY 1 647 INCREMENT BY 1
648 NO MINVALUE 648 NO MINVALUE
649 NO MAXVALUE 649 NO MAXVALUE
650 CACHE 1; 650 CACHE 1;
651 651
652 CREATE TABLE result ( 652 CREATE TABLE result (
653 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass), 653 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass),
654 query_id integer NOT NULL, 654 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
655 data_index character varying(50) NOT NULL, 655 data_index character varying(50) NOT NULL,
656 header character varying(50) NOT NULL, 656 header character varying(50) NOT NULL,
657 width integer, 657 width integer,
658 flex boolean, 658 flex boolean,
659 index integer 659 index integer
843 ALTER TABLE ONLY auth 843 ALTER TABLE ONLY auth
844 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); 844 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
845 845
846 846
847 847
848 ALTER TABLE ONLY favorite
849 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
850
851
852
853 ALTER TABLE ONLY favorite
854 ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
855
856
857
858 ALTER TABLE ONLY filter
859 ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
860
861
862
863 ALTER TABLE ONLY filter_value
864 ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
865
866
867
868 ALTER TABLE ONLY filter_value
869 ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
870
871
872
873 ALTER TABLE ONLY deskriptoren 848 ALTER TABLE ONLY deskriptoren
874 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); 849 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
875 850
876 851
877 852
893 ALTER TABLE ONLY proben_zusatz 868 ALTER TABLE ONLY proben_zusatz
894 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); 869 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
895 870
896 871
897 872
898 ALTER TABLE ONLY result
899 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
900
901
902
903 ALTER TABLE ONLY staat 873 ALTER TABLE ONLY staat
904 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); 874 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
905 875
906 876
907 877
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)