291 lines
6.8 KiB
SQL
291 lines
6.8 KiB
SQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
-- Dumped from database version 10.4
|
|
-- Dumped by pg_dump version 10.4
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
|
|
--
|
|
|
|
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
|
|
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_with_oids = false;
|
|
|
|
--
|
|
-- Name: Done_Purchase; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Done_Purchase" (
|
|
purchased_item_id character varying(10) NOT NULL,
|
|
username character varying(10) NOT NULL,
|
|
name character varying(30) NOT NULL,
|
|
date date NOT NULL,
|
|
count integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Done_Purchase" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: Group; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Group" (
|
|
group_id character varying(10) NOT NULL,
|
|
sl_id character varying(10) NOT NULL,
|
|
name character varying(25) NOT NULL,
|
|
color character varying(20) NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Group" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: Item; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Item" (
|
|
item_id character varying(10) NOT NULL,
|
|
group_id character varying(10) NOT NULL,
|
|
sl_id character varying(10) NOT NULL,
|
|
name character varying(30) NOT NULL,
|
|
count integer NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Item" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: Shoppinglist; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Shoppinglist" (
|
|
sl_id character varying(8) NOT NULL,
|
|
name character varying(20) NOT NULL,
|
|
description character varying(100)
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Shoppinglist" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: Shoppinglist_admin; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Shoppinglist_admin" (
|
|
username character varying NOT NULL,
|
|
sl_id character varying(10) NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Shoppinglist_admin" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: Shoppinglist_member; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."Shoppinglist_member" (
|
|
username character varying NOT NULL,
|
|
sl_id character varying(10) NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Shoppinglist_member" OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: User; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public."User" (
|
|
username character varying NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public."User" OWNER TO postgres;
|
|
|
|
--
|
|
-- Data for Name: Done_Purchase; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Done_Purchase" (purchased_item_id, username, name, date, count) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: Group; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Group" (group_id, sl_id, name, color) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: Item; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Item" (item_id, group_id, sl_id, name, count) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: Shoppinglist; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Shoppinglist" (sl_id, name, description) FROM stdin;
|
|
0 Billa Einkäufe bei Billa
|
|
1 Spar Einkäufe bei Spar
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: Shoppinglist_admin; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Shoppinglist_admin" (username, sl_id) FROM stdin;
|
|
testuser 0
|
|
testuser2 1
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: Shoppinglist_member; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."Shoppinglist_member" (username, sl_id) FROM stdin;
|
|
testuser 1
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: User; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public."User" (username) FROM stdin;
|
|
testuser
|
|
testuser2
|
|
\.
|
|
|
|
|
|
--
|
|
-- Name: Done_Purchase Done_Purchase_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Done_Purchase"
|
|
ADD CONSTRAINT "Done_Purchase_pkey" PRIMARY KEY (purchased_item_id, username);
|
|
|
|
|
|
--
|
|
-- Name: Group Group_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Group"
|
|
ADD CONSTRAINT "Group_pkey" PRIMARY KEY (sl_id, group_id);
|
|
|
|
|
|
--
|
|
-- Name: Item Item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Item"
|
|
ADD CONSTRAINT "Item_pkey" PRIMARY KEY (item_id, group_id, sl_id);
|
|
|
|
|
|
--
|
|
-- Name: Shoppinglist Shoppinglist_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Shoppinglist"
|
|
ADD CONSTRAINT "Shoppinglist_pkey" PRIMARY KEY (sl_id);
|
|
|
|
|
|
--
|
|
-- Name: User User_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."User"
|
|
ADD CONSTRAINT "User_pkey" PRIMARY KEY (username);
|
|
|
|
|
|
--
|
|
-- Name: Done_Purchase Done_Purchase_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Done_Purchase"
|
|
ADD CONSTRAINT "Done_Purchase_username_fkey" FOREIGN KEY (username) REFERENCES public."User"(username);
|
|
|
|
|
|
--
|
|
-- Name: Group Group_sl_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Group"
|
|
ADD CONSTRAINT "Group_sl_id_fkey" FOREIGN KEY (sl_id) REFERENCES public."Shoppinglist"(sl_id);
|
|
|
|
|
|
--
|
|
-- Name: Item Item_sl_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Item"
|
|
ADD CONSTRAINT "Item_sl_id_fkey" FOREIGN KEY (sl_id, group_id) REFERENCES public."Group"(sl_id, group_id);
|
|
|
|
|
|
--
|
|
-- Name: Shoppinglist_admin Shoppinglist_admin_sl_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Shoppinglist_admin"
|
|
ADD CONSTRAINT "Shoppinglist_admin_sl_id_fkey" FOREIGN KEY (sl_id) REFERENCES public."Shoppinglist"(sl_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: Shoppinglist_admin Shoppinglist_admin_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Shoppinglist_admin"
|
|
ADD CONSTRAINT "Shoppinglist_admin_username_fkey" FOREIGN KEY (username) REFERENCES public."User"(username) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: Shoppinglist_member Shoppinglist_member_sl_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Shoppinglist_member"
|
|
ADD CONSTRAINT "Shoppinglist_member_sl_id_fkey" FOREIGN KEY (sl_id) REFERENCES public."Shoppinglist"(sl_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: Shoppinglist_member Shoppinglist_member_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public."Shoppinglist_member"
|
|
ADD CONSTRAINT "Shoppinglist_member_username_fkey" FOREIGN KEY (username) REFERENCES public."User"(username) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|