Mercurial > lada > lada-server
changeset 845:47dc3c4e42dd
Updated db schema for filter queries.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Wed, 27 Jan 2016 14:49:19 +0100 |
| parents | 670f58112734 |
| children | cafeadbeed5b |
| files | db_schema/lada_schema.sql |
| diffstat | 1 files changed, 349 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/db_schema/lada_schema.sql Thu Jan 21 15:37:06 2016 +0100 +++ b/db_schema/lada_schema.sql Wed Jan 27 14:49:19 2016 +0100 @@ -1100,7 +1100,102 @@ -- --- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- Name: favorite; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE favorite ( + id integer NOT NULL, + user_id integer NOT NULL, + query_id integer NOT NULL +); + + +-- +-- Name: favorite_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE favorite_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: favorite_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; + + +-- +-- Name: filter; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE filter ( + id integer NOT NULL, + query_id integer NOT NULL, + data_index character varying(50) NOT NULL, + type character varying(10) NOT NULL, + label character varying(50) NOT NULL, + multiselect boolean +); + + +-- +-- Name: filter_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE filter_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: filter_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE filter_id_seq OWNED BY filter.id; + + +-- +-- Name: filter_value; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE filter_value ( + id integer NOT NULL, + query_id integer NOT NULL, + user_id integer NOT NULL, + filter_id integer NOT NULL, + value text +); + + +-- +-- Name: filter_value_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE filter_value_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: filter_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id; + + +-- +-- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: -- CREATE TABLE koordinaten_art ( @@ -1130,7 +1225,36 @@ -- --- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- Name: lada_user; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE lada_user ( + id integer NOT NULL, + name character varying(80) NOT NULL +); + + +-- +-- Name: lada_user_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE lada_user_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: lada_user_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id; + + +-- +-- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: -- CREATE TABLE mess_einheit ( @@ -1523,6 +1647,72 @@ -- +-- Name: query; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE query ( + id integer NOT NULL, + name character varying(80) NOT NULL, + type character varying(30) NOT NULL, + sql character varying(1500) NOT NULL, + description character varying(100) +); + + +-- +-- Name: query_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE query_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: query_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE query_id_seq OWNED BY query.id; + + +-- +-- Name: result; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE result ( + id integer NOT NULL, + query_id integer NOT NULL, + data_index character varying(50) NOT NULL, + header character varying(50) NOT NULL, + width integer, + flex boolean, + index integer +); + + +-- +-- Name: result_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE result_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: result_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE result_id_seq OWNED BY result.id; + + +-- -- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: - -- @@ -2230,6 +2420,27 @@ -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - -- +ALTER TABLE ONLY favorite ALTER COLUMN id SET DEFAULT nextval('favorite_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter ALTER COLUMN id SET DEFAULT nextval('filter_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value ALTER COLUMN id SET DEFAULT nextval('filter_value_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass); @@ -2237,6 +2448,13 @@ -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - -- +ALTER TABLE ONLY lada_user ALTER COLUMN id SET DEFAULT nextval('lada_user_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass); @@ -2293,6 +2511,20 @@ -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - -- +ALTER TABLE ONLY query ALTER COLUMN id SET DEFAULT nextval('query_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY result ALTER COLUMN id SET DEFAULT nextval('result_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + ALTER TABLE ONLY staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass); @@ -2567,7 +2799,31 @@ -- --- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- Name: favorite_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_pkey PRIMARY KEY (id); + + +-- +-- Name: filter_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY filter + ADD CONSTRAINT filter_pkey PRIMARY KEY (id); + + +-- +-- Name: filter_value_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_pkey PRIMARY KEY (id); + + +-- +-- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: -- ALTER TABLE ONLY koordinaten_art @@ -2575,7 +2831,23 @@ -- --- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- Name: lada_user_name_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY lada_user + ADD CONSTRAINT lada_user_name_key UNIQUE (name); + + +-- +-- Name: lada_user_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY lada_user + ADD CONSTRAINT lada_user_pkey PRIMARY KEY (id); + + +-- +-- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: -- ALTER TABLE ONLY mess_einheit @@ -2719,7 +2991,23 @@ -- --- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- Name: query_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY query + ADD CONSTRAINT query_pkey PRIMARY KEY (id); + + +-- +-- Name: result_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY result + ADD CONSTRAINT result_pkey PRIMARY KEY (id); + + +-- +-- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: -- ALTER TABLE ONLY staat @@ -3368,6 +3656,54 @@ -- +-- Name: favorite_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: favorite_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id); + + +-- +-- Name: filter_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter + ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: filter_value_filter_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id); + + +-- +-- Name: filter_value_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: filter_value_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id); + + +-- -- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - -- @@ -3480,6 +3816,14 @@ -- +-- Name: result_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY result + ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - --