Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 845:47dc3c4e42dd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated db schema for filter queries.
author Raimund Renkert <raimund.renkert@intevation.de>
date Wed, 27 Jan 2016 14:49:19 +0100
parents 6e5a0edba363
children d47e6b8f3897
comparison
equal deleted inserted replaced
843:670f58112734 845:47dc3c4e42dd
1098 1098
1099 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; 1099 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
1100 1100
1101 1101
1102 -- 1102 --
1103 -- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 1103 -- Name: favorite; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1104 --
1105
1106 CREATE TABLE favorite (
1107 id integer NOT NULL,
1108 user_id integer NOT NULL,
1109 query_id integer NOT NULL
1110 );
1111
1112
1113 --
1114 -- Name: favorite_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1115 --
1116
1117 CREATE SEQUENCE favorite_id_seq
1118 START WITH 1
1119 INCREMENT BY 1
1120 NO MINVALUE
1121 NO MAXVALUE
1122 CACHE 1;
1123
1124
1125 --
1126 -- Name: favorite_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1127 --
1128
1129 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
1130
1131
1132 --
1133 -- Name: filter; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1134 --
1135
1136 CREATE TABLE filter (
1137 id integer NOT NULL,
1138 query_id integer NOT NULL,
1139 data_index character varying(50) NOT NULL,
1140 type character varying(10) NOT NULL,
1141 label character varying(50) NOT NULL,
1142 multiselect boolean
1143 );
1144
1145
1146 --
1147 -- Name: filter_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1148 --
1149
1150 CREATE SEQUENCE filter_id_seq
1151 START WITH 1
1152 INCREMENT BY 1
1153 NO MINVALUE
1154 NO MAXVALUE
1155 CACHE 1;
1156
1157
1158 --
1159 -- Name: filter_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1160 --
1161
1162 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
1163
1164
1165 --
1166 -- Name: filter_value; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1167 --
1168
1169 CREATE TABLE filter_value (
1170 id integer NOT NULL,
1171 query_id integer NOT NULL,
1172 user_id integer NOT NULL,
1173 filter_id integer NOT NULL,
1174 value text
1175 );
1176
1177
1178 --
1179 -- Name: filter_value_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1180 --
1181
1182 CREATE SEQUENCE filter_value_id_seq
1183 START WITH 1
1184 INCREMENT BY 1
1185 NO MINVALUE
1186 NO MAXVALUE
1187 CACHE 1;
1188
1189
1190 --
1191 -- Name: filter_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1192 --
1193
1194 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
1195
1196
1197 --
1198 -- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1104 -- 1199 --
1105 1200
1106 CREATE TABLE koordinaten_art ( 1201 CREATE TABLE koordinaten_art (
1107 id integer NOT NULL, 1202 id integer NOT NULL,
1108 koordinatenart character varying(50), 1203 koordinatenart character varying(50),
1128 1223
1129 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id; 1224 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
1130 1225
1131 1226
1132 -- 1227 --
1133 -- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 1228 -- Name: lada_user; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1229 --
1230
1231 CREATE TABLE lada_user (
1232 id integer NOT NULL,
1233 name character varying(80) NOT NULL
1234 );
1235
1236
1237 --
1238 -- Name: lada_user_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1239 --
1240
1241 CREATE SEQUENCE lada_user_id_seq
1242 START WITH 1
1243 INCREMENT BY 1
1244 NO MINVALUE
1245 NO MAXVALUE
1246 CACHE 1;
1247
1248
1249 --
1250 -- Name: lada_user_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1251 --
1252
1253 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
1254
1255
1256 --
1257 -- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1134 -- 1258 --
1135 1259
1136 CREATE TABLE mess_einheit ( 1260 CREATE TABLE mess_einheit (
1137 id integer NOT NULL, 1261 id integer NOT NULL,
1138 beschreibung character varying(50), 1262 beschreibung character varying(50),
1521 1645
1522 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; 1646 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
1523 1647
1524 1648
1525 -- 1649 --
1650 -- Name: query; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1651 --
1652
1653 CREATE TABLE query (
1654 id integer NOT NULL,
1655 name character varying(80) NOT NULL,
1656 type character varying(30) NOT NULL,
1657 sql character varying(1500) NOT NULL,
1658 description character varying(100)
1659 );
1660
1661
1662 --
1663 -- Name: query_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1664 --
1665
1666 CREATE SEQUENCE query_id_seq
1667 START WITH 1
1668 INCREMENT BY 1
1669 NO MINVALUE
1670 NO MAXVALUE
1671 CACHE 1;
1672
1673
1674 --
1675 -- Name: query_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1676 --
1677
1678 ALTER SEQUENCE query_id_seq OWNED BY query.id;
1679
1680
1681 --
1682 -- Name: result; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1683 --
1684
1685 CREATE TABLE result (
1686 id integer NOT NULL,
1687 query_id integer NOT NULL,
1688 data_index character varying(50) NOT NULL,
1689 header character varying(50) NOT NULL,
1690 width integer,
1691 flex boolean,
1692 index integer
1693 );
1694
1695
1696 --
1697 -- Name: result_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1698 --
1699
1700 CREATE SEQUENCE result_id_seq
1701 START WITH 1
1702 INCREMENT BY 1
1703 NO MINVALUE
1704 NO MAXVALUE
1705 CACHE 1;
1706
1707
1708 --
1709 -- Name: result_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1710 --
1711
1712 ALTER SEQUENCE result_id_seq OWNED BY result.id;
1713
1714
1715 --
1526 -- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: - 1716 -- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: -
1527 -- 1717 --
1528 1718
1529 CREATE VIEW s_00_view AS 1719 CREATE VIEW s_00_view AS
1530 SELECT deskriptoren.s_xx AS s00, 1720 SELECT deskriptoren.s_xx AS s00,
2228 2418
2229 -- 2419 --
2230 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - 2420 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2231 -- 2421 --
2232 2422
2423 ALTER TABLE ONLY favorite ALTER COLUMN id SET DEFAULT nextval('favorite_id_seq'::regclass);
2424
2425
2426 --
2427 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2428 --
2429
2430 ALTER TABLE ONLY filter ALTER COLUMN id SET DEFAULT nextval('filter_id_seq'::regclass);
2431
2432
2433 --
2434 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2435 --
2436
2437 ALTER TABLE ONLY filter_value ALTER COLUMN id SET DEFAULT nextval('filter_value_id_seq'::regclass);
2438
2439
2440 --
2441 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2442 --
2443
2233 ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass); 2444 ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass);
2234 2445
2235 2446
2236 -- 2447 --
2237 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - 2448 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2238 -- 2449 --
2239 2450
2451 ALTER TABLE ONLY lada_user ALTER COLUMN id SET DEFAULT nextval('lada_user_id_seq'::regclass);
2452
2453
2454 --
2455 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2456 --
2457
2240 ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass); 2458 ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass);
2241 2459
2242 2460
2243 -- 2461 --
2244 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - 2462 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2285 -- 2503 --
2286 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - 2504 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2287 -- 2505 --
2288 2506
2289 ALTER TABLE ONLY probenehmer ALTER COLUMN id SET DEFAULT nextval('probenehmer_id_seq'::regclass); 2507 ALTER TABLE ONLY probenehmer ALTER COLUMN id SET DEFAULT nextval('probenehmer_id_seq'::regclass);
2508
2509
2510 --
2511 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2512 --
2513
2514 ALTER TABLE ONLY query ALTER COLUMN id SET DEFAULT nextval('query_id_seq'::regclass);
2515
2516
2517 --
2518 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2519 --
2520
2521 ALTER TABLE ONLY result ALTER COLUMN id SET DEFAULT nextval('result_id_seq'::regclass);
2290 2522
2291 2523
2292 -- 2524 --
2293 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - 2525 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2294 -- 2526 --
2565 ALTER TABLE ONLY deskriptor_umwelt 2797 ALTER TABLE ONLY deskriptor_umwelt
2566 ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id); 2798 ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id);
2567 2799
2568 2800
2569 -- 2801 --
2570 -- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2802 -- Name: favorite_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2803 --
2804
2805 ALTER TABLE ONLY favorite
2806 ADD CONSTRAINT favorite_pkey PRIMARY KEY (id);
2807
2808
2809 --
2810 -- Name: filter_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2811 --
2812
2813 ALTER TABLE ONLY filter
2814 ADD CONSTRAINT filter_pkey PRIMARY KEY (id);
2815
2816
2817 --
2818 -- Name: filter_value_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2819 --
2820
2821 ALTER TABLE ONLY filter_value
2822 ADD CONSTRAINT filter_value_pkey PRIMARY KEY (id);
2823
2824
2825 --
2826 -- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2571 -- 2827 --
2572 2828
2573 ALTER TABLE ONLY koordinaten_art 2829 ALTER TABLE ONLY koordinaten_art
2574 ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id); 2830 ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id);
2575 2831
2576 2832
2577 -- 2833 --
2578 -- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2834 -- Name: lada_user_name_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2835 --
2836
2837 ALTER TABLE ONLY lada_user
2838 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
2839
2840
2841 --
2842 -- Name: lada_user_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2843 --
2844
2845 ALTER TABLE ONLY lada_user
2846 ADD CONSTRAINT lada_user_pkey PRIMARY KEY (id);
2847
2848
2849 --
2850 -- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2579 -- 2851 --
2580 2852
2581 ALTER TABLE ONLY mess_einheit 2853 ALTER TABLE ONLY mess_einheit
2582 ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id); 2854 ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id);
2583 2855
2717 ALTER TABLE ONLY probenehmer 2989 ALTER TABLE ONLY probenehmer
2718 ADD CONSTRAINT probenehmer_pkey PRIMARY KEY (id); 2990 ADD CONSTRAINT probenehmer_pkey PRIMARY KEY (id);
2719 2991
2720 2992
2721 -- 2993 --
2722 -- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2994 -- Name: query_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2995 --
2996
2997 ALTER TABLE ONLY query
2998 ADD CONSTRAINT query_pkey PRIMARY KEY (id);
2999
3000
3001 --
3002 -- Name: result_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3003 --
3004
3005 ALTER TABLE ONLY result
3006 ADD CONSTRAINT result_pkey PRIMARY KEY (id);
3007
3008
3009 --
3010 -- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2723 -- 3011 --
2724 3012
2725 ALTER TABLE ONLY staat 3013 ALTER TABLE ONLY staat
2726 ADD CONSTRAINT staat_pkey PRIMARY KEY (id); 3014 ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
2727 3015
3366 ALTER TABLE ONLY datensatz_erzeuger 3654 ALTER TABLE ONLY datensatz_erzeuger
3367 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); 3655 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3368 3656
3369 3657
3370 -- 3658 --
3659 -- Name: favorite_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3660 --
3661
3662 ALTER TABLE ONLY favorite
3663 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3664
3665
3666 --
3667 -- Name: favorite_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3668 --
3669
3670 ALTER TABLE ONLY favorite
3671 ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
3672
3673
3674 --
3675 -- Name: filter_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3676 --
3677
3678 ALTER TABLE ONLY filter
3679 ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3680
3681
3682 --
3683 -- Name: filter_value_filter_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3684 --
3685
3686 ALTER TABLE ONLY filter_value
3687 ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
3688
3689
3690 --
3691 -- Name: filter_value_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3692 --
3693
3694 ALTER TABLE ONLY filter_value
3695 ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3696
3697
3698 --
3699 -- Name: filter_value_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3700 --
3701
3702 ALTER TABLE ONLY filter_value
3703 ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
3704
3705
3706 --
3371 -- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3707 -- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3372 -- 3708 --
3373 3709
3374 ALTER TABLE ONLY deskriptoren 3710 ALTER TABLE ONLY deskriptoren
3375 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); 3711 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
3475 -- Name: probenehmer_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3811 -- Name: probenehmer_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3476 -- 3812 --
3477 3813
3478 ALTER TABLE ONLY probenehmer 3814 ALTER TABLE ONLY probenehmer
3479 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); 3815 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3816
3817
3818 --
3819 -- Name: result_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3820 --
3821
3822 ALTER TABLE ONLY result
3823 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3480 3824
3481 3825
3482 -- 3826 --
3483 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3827 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3484 -- 3828 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)