Created
October 20, 2019 09:19
-
-
Save geoextra/0ed6596487e576f243754921df8ffa8e to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- PostgreSQL database dump | |
| -- | |
| -- Dumped from database version 11.5 (Debian 11.5-3.pgdg100+1) | |
| -- Dumped by pg_dump version 11.5 (Debian 11.5-3.pgdg100+1) | |
| -- Started on 2019-10-20 09:17:52 UTC | |
| 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 xmloption = content; | |
| SET client_min_messages = warning; | |
| SET row_security = off; | |
| -- | |
| -- TOC entry 3 (class 3079 OID 16467) | |
| -- Name: isn; Type: EXTENSION; Schema: -; Owner: | |
| -- | |
| CREATE EXTENSION IF NOT EXISTS isn WITH SCHEMA public; | |
| -- | |
| -- TOC entry 3479 (class 0 OID 0) | |
| -- Dependencies: 3 | |
| -- Name: EXTENSION isn; Type: COMMENT; Schema: -; Owner: | |
| -- | |
| COMMENT ON EXTENSION isn IS 'data types for international product numbering standards'; | |
| -- | |
| -- TOC entry 2 (class 3079 OID 17255) | |
| -- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: | |
| -- | |
| CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public; | |
| -- | |
| -- TOC entry 3480 (class 0 OID 0) | |
| -- Dependencies: 2 | |
| -- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: | |
| -- | |
| COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)'; | |
| SET default_tablespace = ''; | |
| SET default_with_oids = false; | |
| -- | |
| -- TOC entry 208 (class 1259 OID 17324) | |
| -- Name: author; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.author ( | |
| id integer NOT NULL, | |
| firstname character varying(100), | |
| lastname character varying(100) NOT NULL | |
| ); | |
| ALTER TABLE public.author OWNER TO postgres; | |
| -- | |
| -- TOC entry 207 (class 1259 OID 17322) | |
| -- Name: author_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres | |
| -- | |
| CREATE SEQUENCE public.author_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.author_id_seq OWNER TO postgres; | |
| -- | |
| -- TOC entry 3481 (class 0 OID 0) | |
| -- Dependencies: 207 | |
| -- Name: author_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres | |
| -- | |
| ALTER SEQUENCE public.author_id_seq OWNED BY public.author.id; | |
| -- | |
| -- TOC entry 200 (class 1259 OID 17227) | |
| -- Name: book; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.book ( | |
| isbn public.isbn NOT NULL, | |
| title character varying(100) NOT NULL, | |
| description text, | |
| year integer NOT NULL, | |
| genre character varying(15) NOT NULL, | |
| price money, | |
| author integer NOT NULL | |
| ); | |
| ALTER TABLE public.book OWNER TO postgres; | |
| -- | |
| -- TOC entry 198 (class 1259 OID 17189) | |
| -- Name: copy; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.copy ( | |
| id integer NOT NULL, | |
| book public.isbn NOT NULL | |
| ); | |
| ALTER TABLE public.copy OWNER TO postgres; | |
| -- | |
| -- TOC entry 199 (class 1259 OID 17192) | |
| -- Name: copy_signiture_seq; Type: SEQUENCE; Schema: public; Owner: postgres | |
| -- | |
| CREATE SEQUENCE public.copy_signiture_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.copy_signiture_seq OWNER TO postgres; | |
| -- | |
| -- TOC entry 3482 (class 0 OID 0) | |
| -- Dependencies: 199 | |
| -- Name: copy_signiture_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres | |
| -- | |
| ALTER SEQUENCE public.copy_signiture_seq OWNED BY public.copy.id; | |
| -- | |
| -- TOC entry 202 (class 1259 OID 17270) | |
| -- Name: customer; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.customer ( | |
| id integer NOT NULL, | |
| first_name character varying(255), | |
| last_name character varying(255), | |
| date_of_birth date, | |
| e_mail character varying(255), | |
| phone_number integer | |
| ); | |
| ALTER TABLE public.customer OWNER TO postgres; | |
| -- | |
| -- TOC entry 201 (class 1259 OID 17268) | |
| -- Name: customer_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres | |
| -- | |
| CREATE SEQUENCE public.customer_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.customer_id_seq OWNER TO postgres; | |
| -- | |
| -- TOC entry 3483 (class 0 OID 0) | |
| -- Dependencies: 201 | |
| -- Name: customer_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres | |
| -- | |
| ALTER SEQUENCE public.customer_id_seq OWNED BY public.customer.id; | |
| -- | |
| -- TOC entry 204 (class 1259 OID 17281) | |
| -- Name: lending; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.lending ( | |
| id integer NOT NULL, | |
| lending_date date NOT NULL, | |
| return_date date, | |
| customer integer, | |
| copy integer | |
| ); | |
| ALTER TABLE public.lending OWNER TO postgres; | |
| -- | |
| -- TOC entry 203 (class 1259 OID 17279) | |
| -- Name: lending_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres | |
| -- | |
| CREATE SEQUENCE public.lending_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.lending_id_seq OWNER TO postgres; | |
| -- | |
| -- TOC entry 3484 (class 0 OID 0) | |
| -- Dependencies: 203 | |
| -- Name: lending_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres | |
| -- | |
| ALTER SEQUENCE public.lending_id_seq OWNED BY public.lending.id; | |
| -- | |
| -- TOC entry 210 (class 1259 OID 24581) | |
| -- Name: openLendings; Type: VIEW; Schema: public; Owner: postgres | |
| -- | |
| CREATE VIEW public."openLendings" AS | |
| SELECT customer.id, | |
| customer.last_name, | |
| date_part('day'::text, (now() - (lending.lending_date)::timestamp with time zone)) AS dayslended, | |
| ((21)::double precision - date_part('day'::text, (now() - (lending.lending_date)::timestamp with time zone))) AS daysleft, | |
| book.isbn, | |
| book.title | |
| FROM (((public.lending | |
| LEFT JOIN public.customer ON ((lending.customer = customer.id))) | |
| LEFT JOIN public.copy ON ((lending.copy = copy.id))) | |
| LEFT JOIN public.book ON ((copy.book OPERATOR(public.=) book.isbn))) | |
| WHERE (lending.return_date IS NULL); | |
| ALTER TABLE public."openLendings" OWNER TO postgres; | |
| -- | |
| -- TOC entry 206 (class 1259 OID 17292) | |
| -- Name: rating; Type: TABLE; Schema: public; Owner: postgres | |
| -- | |
| CREATE TABLE public.rating ( | |
| id integer NOT NULL, | |
| comment text, | |
| rating integer, | |
| customer integer, | |
| book public.isbn, | |
| CONSTRAINT checkbetween1and10 CHECK (((rating < 10) AND (rating > 0))) | |
| ); | |
| ALTER TABLE public.rating OWNER TO postgres; | |
| -- | |
| -- TOC entry 205 (class 1259 OID 17290) | |
| -- Name: rating_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres | |
| -- | |
| CREATE SEQUENCE public.rating_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.rating_id_seq OWNER TO postgres; | |
| -- | |
| -- TOC entry 3485 (class 0 OID 0) | |
| -- Dependencies: 205 | |
| -- Name: rating_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres | |
| -- | |
| ALTER SEQUENCE public.rating_id_seq OWNED BY public.rating.id; | |
| -- | |
| -- TOC entry 209 (class 1259 OID 17371) | |
| -- Name: viewAverageRating; Type: VIEW; Schema: public; Owner: postgres | |
| -- | |
| CREATE VIEW public."viewAverageRating" AS | |
| SELECT | |
| NULL::public.isbn AS isbn, | |
| NULL::character varying(100) AS title, | |
| NULL::bigint AS counts, | |
| NULL::numeric AS average; | |
| ALTER TABLE public."viewAverageRating" OWNER TO postgres; | |
| -- | |
| -- TOC entry 3486 (class 0 OID 0) | |
| -- Dependencies: 209 | |
| -- Name: VIEW "viewAverageRating"; Type: COMMENT; Schema: public; Owner: postgres | |
| -- | |
| COMMENT ON VIEW public."viewAverageRating" IS 'Shows the title and isbn of all book which have at least one rating and show the average rating. The books will be ordered by their average rating.'; | |
| -- | |
| -- TOC entry 3305 (class 2604 OID 17327) | |
| -- Name: author id; Type: DEFAULT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.author ALTER COLUMN id SET DEFAULT nextval('public.author_id_seq'::regclass); | |
| -- | |
| -- TOC entry 3300 (class 2604 OID 17194) | |
| -- Name: copy id; Type: DEFAULT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.copy ALTER COLUMN id SET DEFAULT nextval('public.copy_signiture_seq'::regclass); | |
| -- | |
| -- TOC entry 3301 (class 2604 OID 17273) | |
| -- Name: customer id; Type: DEFAULT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.customer ALTER COLUMN id SET DEFAULT nextval('public.customer_id_seq'::regclass); | |
| -- | |
| -- TOC entry 3302 (class 2604 OID 17284) | |
| -- Name: lending id; Type: DEFAULT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.lending ALTER COLUMN id SET DEFAULT nextval('public.lending_id_seq'::regclass); | |
| -- | |
| -- TOC entry 3303 (class 2604 OID 17295) | |
| -- Name: rating id; Type: DEFAULT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.rating ALTER COLUMN id SET DEFAULT nextval('public.rating_id_seq'::regclass); | |
| -- | |
| -- TOC entry 3473 (class 0 OID 17324) | |
| -- Dependencies: 208 | |
| -- Data for Name: author; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.author (id, firstname, lastname) FROM stdin; | |
| 1 Herman Melville | |
| 2 Astrid Lindgren | |
| 5 J.K Rowling | |
| 6 Stephen Hawking | |
| 7 Albert Camus | |
| 8 Johann Wolfgang von Goethe | |
| 9 Ton van Reen | |
| 11 Aldous Huxley | |
| \. | |
| -- | |
| -- TOC entry 3465 (class 0 OID 17227) | |
| -- Dependencies: 200 | |
| -- Data for Name: book; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.book (isbn, title, description, year, genre, price, author) FROM stdin; | |
| 0-393-09670-X Moby-Dick An Authoritative Text, Reviews and Letters by Melville, Analogues and Sources, Criticism. 1967 Novel $9.99 1 | |
| 963-627-173-9 Pippi Langkous Pippi ziet er wat raar uit met haar stijve vlechten, vreemde jurken en veel te grote schoenen. Maar voor haar buurkinderen is zij de sterkste, de stoutste en vooral de leukste kameraad. 1945 Child book \N 2 | |
| 0-545-01022-5 Harry Potter and relics of death Seventh and final novel of the Harry Potter series 2007 Fantasy \N 5 | |
| 0-7475-3269-9 Harry Potter and the Philosopher's Stone First novel of the Harry Potter series 1997 Fantasy $14.99 5 | |
| 81-7992-591-9 The theory of everything A book on Gravity, Black holes, matter-anti matter, light, quantum mechanics, time and space. 2004 Science \N 6 | |
| 0-679-72020-0 The stranger Through the story of an ordinary man unwittingly drawn into a senseless murder on an Algerian beach, Camus explored what he termed "the nakedness of man faced with the absurd." 1942 Fiction \N 7 | |
| 0-86547-076-6 Italian journey Goethe describes his travels through eighteenth-century Italy and the unusual characters and incidents he encountered along the way 1788 Diary \N 8 | |
| 90-269-5629-0 Het winterjaar About a boy who is confronted with the illness and death of his father 1986 Novel \N 9 | |
| \. | |
| -- | |
| -- TOC entry 3463 (class 0 OID 17189) | |
| -- Dependencies: 198 | |
| -- Data for Name: copy; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.copy (id, book) FROM stdin; | |
| 1 0-393-09670-X | |
| 2 0-393-09670-X | |
| 3 81-7992-591-9 | |
| 4 0-679-72020-0 | |
| 5 81-7992-591-9 | |
| 6 0-7475-3269-9 | |
| 7 0-7475-3269-9 | |
| 8 0-7475-3269-9 | |
| 9 0-545-01022-5 | |
| 10 0-86547-076-6 | |
| \. | |
| -- | |
| -- TOC entry 3467 (class 0 OID 17270) | |
| -- Dependencies: 202 | |
| -- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.customer (id, first_name, last_name, date_of_birth, e_mail, phone_number) FROM stdin; | |
| 1 Freddy Mercury 1996-03-17 [email protected] 1234579 | |
| 2 Thomas Edison 1847-02-11 [email protected] 880022112 | |
| 4 Timothy Jefferson 1985-09-25 [email protected] 563247210 | |
| 3 Steppen Genius 1997-02-25 [email protected] 621761033 | |
| 5 Nick McNick 1998-12-17 [email protected] 887462133 | |
| 6 Jonathon Miles 1998-11-17 [email protected] 660500210 | |
| 7 Milo Miles 1998-11-17 [email protected] 660500211 | |
| 8 Tinker Miles 1998-11-17 [email protected] 660500212 | |
| \. | |
| -- | |
| -- TOC entry 3469 (class 0 OID 17281) | |
| -- Dependencies: 204 | |
| -- Data for Name: lending; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.lending (id, lending_date, return_date, customer, copy) FROM stdin; | |
| 3 2019-10-01 \N 1 2 | |
| 4 2019-10-04 2019-10-15 1 1 | |
| 1 2019-08-26 2019-09-16 1 3 | |
| 2 2019-09-02 2019-09-23 6 4 | |
| 5 2019-10-02 \N 2 5 | |
| 6 2019-10-10 \N 3 6 | |
| 7 2019-09-27 \N 4 7 | |
| 8 2019-09-30 \N 8 9 | |
| 9 2019-09-30 2019-10-16 7 10 | |
| 10 2019-10-07 \N 5 10 | |
| 11 2019-10-15 \N 6 1 | |
| 12 2019-10-19 \N 6 2 | |
| \. | |
| -- | |
| -- TOC entry 3471 (class 0 OID 17292) | |
| -- Dependencies: 206 | |
| -- Data for Name: rating; Type: TABLE DATA; Schema: public; Owner: postgres | |
| -- | |
| COPY public.rating (id, comment, rating, customer, book) FROM stdin; | |
| 1 Nice but too much water 7 1 0-393-09670-X | |
| 2 Got better at the end 8 1 0-393-09670-X | |
| 3 A bit dark with the black holes 6 2 81-7992-591-9 | |
| 4 Still haven't received my Hogwarts letter 9 5 0-7475-3269-9 | |
| 5 A bit unrealistic with those braids to be honest 4 3 963-627-173-9 | |
| 7 Good literary work, writing style is improvable 8 7 963-627-173-9 | |
| 8 Typical Dutch literature 3 6 90-269-5629-0 | |
| 9 The Battle of Hogwarts is still heartbreaking 8 8 0-545-01022-5 | |
| 10 My French is not so good 5 6 0-679-72020-0 | |
| 11 Strange story with these sticks that can do anything 6 5 0-7475-3269-9 | |
| 6 A German in Italy? Suspicious... 6 4 0-86547-076-6 | |
| \. | |
| -- | |
| -- TOC entry 3487 (class 0 OID 0) | |
| -- Dependencies: 207 | |
| -- Name: author_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres | |
| -- | |
| SELECT pg_catalog.setval('public.author_id_seq', 12, true); | |
| -- | |
| -- TOC entry 3488 (class 0 OID 0) | |
| -- Dependencies: 199 | |
| -- Name: copy_signiture_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres | |
| -- | |
| SELECT pg_catalog.setval('public.copy_signiture_seq', 2, true); | |
| -- | |
| -- TOC entry 3489 (class 0 OID 0) | |
| -- Dependencies: 201 | |
| -- Name: customer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres | |
| -- | |
| SELECT pg_catalog.setval('public.customer_id_seq', 1, true); | |
| -- | |
| -- TOC entry 3490 (class 0 OID 0) | |
| -- Dependencies: 203 | |
| -- Name: lending_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres | |
| -- | |
| SELECT pg_catalog.setval('public.lending_id_seq', 4, true); | |
| -- | |
| -- TOC entry 3491 (class 0 OID 0) | |
| -- Dependencies: 205 | |
| -- Name: rating_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres | |
| -- | |
| SELECT pg_catalog.setval('public.rating_id_seq', 2, true); | |
| -- | |
| -- TOC entry 3333 (class 2606 OID 17329) | |
| -- Name: author author_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.author | |
| ADD CONSTRAINT author_pkey PRIMARY KEY (id); | |
| -- | |
| -- TOC entry 3307 (class 2606 OID 17199) | |
| -- Name: copy copy_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.copy | |
| ADD CONSTRAINT copy_pkey PRIMARY KEY (id); | |
| -- | |
| -- TOC entry 3316 (class 2606 OID 17278) | |
| -- Name: customer customer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.customer | |
| ADD CONSTRAINT customer_pkey PRIMARY KEY (id); | |
| -- | |
| -- TOC entry 3312 (class 2606 OID 17234) | |
| -- Name: book isbn_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.book | |
| ADD CONSTRAINT isbn_pkey PRIMARY KEY (isbn); | |
| -- | |
| -- TOC entry 3321 (class 2606 OID 17289) | |
| -- Name: lending lending_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_pkey PRIMARY KEY (id); | |
| -- | |
| -- TOC entry 3327 (class 2606 OID 17301) | |
| -- Name: rating rating_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_pkey PRIMARY KEY (id); | |
| -- | |
| -- TOC entry 3330 (class 1259 OID 24588) | |
| -- Name: author_lastname_firstname_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX author_lastname_firstname_idx ON public.author USING btree (lastname, firstname); | |
| -- | |
| -- TOC entry 3331 (class 1259 OID 24587) | |
| -- Name: author_lastname_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX author_lastname_idx ON public.author USING btree (lastname); | |
| -- | |
| -- TOC entry 3308 (class 1259 OID 24590) | |
| -- Name: book_genre_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX book_genre_idx ON public.book USING btree (genre); | |
| -- | |
| -- TOC entry 3309 (class 1259 OID 24589) | |
| -- Name: book_title_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX book_title_idx ON public.book USING btree (title); | |
| -- | |
| -- TOC entry 3310 (class 1259 OID 24591) | |
| -- Name: book_year_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX book_year_idx ON public.book USING btree (year); | |
| -- | |
| -- TOC entry 3313 (class 1259 OID 24593) | |
| -- Name: customer_last_name_first_name_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX customer_last_name_first_name_idx ON public.customer USING btree (last_name, first_name); | |
| -- | |
| -- TOC entry 3314 (class 1259 OID 24592) | |
| -- Name: customer_last_name_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX customer_last_name_idx ON public.customer USING btree (last_name); | |
| -- | |
| -- TOC entry 3317 (class 1259 OID 17350) | |
| -- Name: fki_lending_copy_fkey; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX fki_lending_copy_fkey ON public.lending USING btree (copy); | |
| -- | |
| -- TOC entry 3318 (class 1259 OID 24594) | |
| -- Name: lending_lending_date_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX lending_lending_date_idx ON public.lending USING btree (lending_date); | |
| -- | |
| -- TOC entry 3319 (class 1259 OID 24595) | |
| -- Name: lending_lending_date_idx1; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX lending_lending_date_idx1 ON public.lending USING btree (lending_date DESC); | |
| -- | |
| -- TOC entry 3322 (class 1259 OID 24596) | |
| -- Name: lending_return_date_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX lending_return_date_idx ON public.lending USING btree (return_date); | |
| -- | |
| -- TOC entry 3323 (class 1259 OID 24597) | |
| -- Name: lending_return_date_idx1; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX lending_return_date_idx1 ON public.lending USING btree (return_date DESC); | |
| -- | |
| -- TOC entry 3324 (class 1259 OID 24600) | |
| -- Name: rating_book_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX rating_book_idx ON public.rating USING btree (book); | |
| -- | |
| -- TOC entry 3325 (class 1259 OID 24601) | |
| -- Name: rating_customer_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX rating_customer_idx ON public.rating USING btree (customer); | |
| -- | |
| -- TOC entry 3328 (class 1259 OID 24598) | |
| -- Name: rating_rating_idx; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX rating_rating_idx ON public.rating USING btree (rating DESC); | |
| -- | |
| -- TOC entry 3329 (class 1259 OID 24599) | |
| -- Name: rating_rating_idx1; Type: INDEX; Schema: public; Owner: postgres | |
| -- | |
| CREATE INDEX rating_rating_idx1 ON public.rating USING btree (rating); | |
| -- | |
| -- TOC entry 3461 (class 2618 OID 17374) | |
| -- Name: viewAverageRating _RETURN; Type: RULE; Schema: public; Owner: postgres | |
| -- | |
| CREATE OR REPLACE VIEW public."viewAverageRating" WITH (security_barrier='false') AS | |
| SELECT b.isbn, | |
| b.title, | |
| count(rating.rating) AS counts, | |
| round(avg(rating.rating), 1) AS average | |
| FROM (public.rating | |
| LEFT JOIN public.book b ON ((rating.book OPERATOR(public.=) b.isbn))) | |
| GROUP BY b.isbn | |
| ORDER BY (avg(rating.rating)) DESC; | |
| -- | |
| -- TOC entry 3335 (class 2606 OID 17340) | |
| -- Name: book book_author_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.book | |
| ADD CONSTRAINT book_author_fkey FOREIGN KEY (author) REFERENCES public.author(id) ON DELETE CASCADE NOT VALID; | |
| -- | |
| -- TOC entry 3334 (class 2606 OID 17351) | |
| -- Name: copy copy_book_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.copy | |
| ADD CONSTRAINT copy_book_fkey FOREIGN KEY (book) REFERENCES public.book(isbn) ON DELETE CASCADE NOT VALID; | |
| -- | |
| -- TOC entry 3337 (class 2606 OID 17345) | |
| -- Name: lending lending_copy_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_copy_fkey FOREIGN KEY (copy) REFERENCES public.copy(id) NOT VALID; | |
| -- | |
| -- TOC entry 3336 (class 2606 OID 17302) | |
| -- Name: lending lending_customer_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_customer_fkey FOREIGN KEY (customer) REFERENCES public.customer(id) NOT VALID; | |
| -- | |
| -- TOC entry 3338 (class 2606 OID 17361) | |
| -- Name: rating rating_book_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_book_fkey FOREIGN KEY (book) REFERENCES public.book(isbn) ON DELETE CASCADE NOT VALID; | |
| -- | |
| -- TOC entry 3339 (class 2606 OID 17366) | |
| -- Name: rating rating_customer_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres | |
| -- | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_customer_fkey FOREIGN KEY (customer) REFERENCES public.customer(id) ON DELETE CASCADE NOT VALID; | |
| -- Completed on 2019-10-20 09:17:52 UTC | |
| -- | |
| -- PostgreSQL database dump complete | |
| -- | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment