415 lines
11 KiB
SQL
415 lines
11 KiB
SQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
-- Dumped from database version 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)
|
|
-- Dumped by pg_dump version 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)
|
|
|
|
-- Started on 2020-11-10 15:04:23 CET
|
|
|
|
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;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- TOC entry 208 (class 1259 OID 17923)
|
|
-- Name: berichten; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.berichten (
|
|
berichtid integer NOT NULL,
|
|
bericht character varying(140) NOT NULL,
|
|
datumontvangen timestamp without time zone NOT NULL,
|
|
naamposter character varying(32),
|
|
moderatorid integer,
|
|
datumgekeurd timestamp without time zone,
|
|
statusid integer NOT NULL,
|
|
locatieid integer NOT NULL,
|
|
twitterid bigint
|
|
);
|
|
|
|
|
|
ALTER TABLE public.berichten OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 207 (class 1259 OID 17921)
|
|
-- Name: berichten_berichtid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.berichten_berichtid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER TABLE public.berichten_berichtid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 3006 (class 0 OID 0)
|
|
-- Dependencies: 207
|
|
-- Name: berichten_berichtid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.berichten_berichtid_seq OWNED BY public.berichten.berichtid;
|
|
|
|
|
|
--
|
|
-- TOC entry 202 (class 1259 OID 17898)
|
|
-- Name: locaties; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.locaties (
|
|
locatieid integer NOT NULL,
|
|
naam character varying(64) NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.locaties OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 204 (class 1259 OID 17905)
|
|
-- Name: moderators; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.moderators (
|
|
moderatorid integer NOT NULL,
|
|
naam character varying(64) NOT NULL,
|
|
hash character varying(255),
|
|
maylogin boolean NOT NULL,
|
|
lastseen timestamp without time zone
|
|
);
|
|
|
|
|
|
ALTER TABLE public.moderators OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 203 (class 1259 OID 17903)
|
|
-- Name: moderators_moderatorid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.moderators_moderatorid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER TABLE public.moderators_moderatorid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 3010 (class 0 OID 0)
|
|
-- Dependencies: 203
|
|
-- Name: moderators_moderatorid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.moderators_moderatorid_seq OWNED BY public.moderators.moderatorid;
|
|
|
|
|
|
--
|
|
-- TOC entry 206 (class 1259 OID 17916)
|
|
-- Name: statuses; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.statuses (
|
|
statusid integer NOT NULL,
|
|
status character varying(32)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.statuses OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 205 (class 1259 OID 17911)
|
|
-- Name: twitterberichten; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.twitterberichten (
|
|
twitterid bigint NOT NULL,
|
|
datumpublished timestamp without time zone NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.twitterberichten OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 2852 (class 2604 OID 17926)
|
|
-- Name: berichten berichtid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten ALTER COLUMN berichtid SET DEFAULT nextval('public.berichten_berichtid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 2851 (class 2604 OID 17908)
|
|
-- Name: moderators moderatorid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.moderators ALTER COLUMN moderatorid SET DEFAULT nextval('public.moderators_moderatorid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 2999 (class 0 OID 17923)
|
|
-- Dependencies: 208
|
|
-- Data for Name: berichten; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.berichten (berichtid, bericht, datumontvangen, naamposter, moderatorid, datumgekeurd, statusid, locatieid, twitterid) FROM stdin;
|
|
23 Dit is een anoniem bericht 2020-11-04 19:19:40 Anoniem 4 2020-11-04 19:21:18 3 2 \N
|
|
24 Dit is een mooi station 2020-11-04 19:19:58 reiziger 4 2020-11-04 19:21:21 5 2 1324069239356010496
|
|
25 jku89ghy45j789m34gt5j79834g5hjn7gm9345hn70mj39g458 2020-11-04 19:20:12 spammer 4 2020-11-04 19:21:24 3 2 \N
|
|
26 de treinen komen laat aan 2020-11-04 19:20:49 censuur 4 2020-11-04 19:21:25 3 2 \N
|
|
27 dit is een bericht 2020-11-04 19:22:36 naam \N \N 1 2 \N
|
|
28 nog een test bericht 2020-11-04 19:23:10 hier uw naam \N \N 1 2 \N
|
|
29 natuurlijk nog een bericht 2020-11-04 19:23:33 jaja nog een naam \N \N 1 2 \N
|
|
30 dit is een bericht op utrecht centraal 2020-11-04 19:24:00 Anoniem \N \N 1 1 \N
|
|
31 dit is een andere locatie! 2020-11-04 19:24:16 naam \N \N 1 1 \N
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 2993 (class 0 OID 17898)
|
|
-- Dependencies: 202
|
|
-- Data for Name: locaties; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.locaties (locatieid, naam) FROM stdin;
|
|
1 Utrecht Centraal
|
|
2 Amsterdam Centraal
|
|
3 Rotterdam Centraal
|
|
4 Zwolle
|
|
5 Groningen
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 2995 (class 0 OID 17905)
|
|
-- Dependencies: 204
|
|
-- Data for Name: moderators; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.moderators (moderatorid, naam, hash, maylogin, lastseen) FROM stdin;
|
|
4 test a3eec2461d6637164d836db2c9f19795357c80c1420ccc13386839ae1104b53a t \N
|
|
3 administrator a3eec2461d6637164d836db2c9f19795357c80c1420ccc13386839ae1104b53a f \N
|
|
5 moderator cb9644ce70d89929b7af40a2611b0ef3bbd5105a43b3c45fedcbdddcecf25edc t \N
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 2997 (class 0 OID 17916)
|
|
-- Dependencies: 206
|
|
-- Data for Name: statuses; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.statuses (statusid, status) FROM stdin;
|
|
1 pending moderation
|
|
2 moderation lock
|
|
3 moderation rejected
|
|
4 moderation accepted
|
|
5 published
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 2996 (class 0 OID 17911)
|
|
-- Dependencies: 205
|
|
-- Data for Name: twitterberichten; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.twitterberichten (twitterid, datumpublished) FROM stdin;
|
|
1324069239356010496 2020-11-04 19:21:21
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 3014 (class 0 OID 0)
|
|
-- Dependencies: 207
|
|
-- Name: berichten_berichtid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.berichten_berichtid_seq', 31, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 3015 (class 0 OID 0)
|
|
-- Dependencies: 203
|
|
-- Name: moderators_moderatorid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.moderators_moderatorid_seq', 5, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 2862 (class 2606 OID 17928)
|
|
-- Name: berichten berichten_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten
|
|
ADD CONSTRAINT berichten_pkey PRIMARY KEY (berichtid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2854 (class 2606 OID 17902)
|
|
-- Name: locaties locaties_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.locaties
|
|
ADD CONSTRAINT locaties_pkey PRIMARY KEY (locatieid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2856 (class 2606 OID 17910)
|
|
-- Name: moderators moderators_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.moderators
|
|
ADD CONSTRAINT moderators_pkey PRIMARY KEY (moderatorid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2860 (class 2606 OID 17920)
|
|
-- Name: statuses statuses_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.statuses
|
|
ADD CONSTRAINT statuses_pkey PRIMARY KEY (statusid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2858 (class 2606 OID 17915)
|
|
-- Name: twitterberichten twitterberichten_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.twitterberichten
|
|
ADD CONSTRAINT twitterberichten_pkey PRIMARY KEY (twitterid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2864 (class 2606 OID 17934)
|
|
-- Name: berichten berichten_locatieid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten
|
|
ADD CONSTRAINT berichten_locatieid_fkey FOREIGN KEY (locatieid) REFERENCES public.locaties(locatieid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2865 (class 2606 OID 17939)
|
|
-- Name: berichten berichten_moderatorid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten
|
|
ADD CONSTRAINT berichten_moderatorid_fkey FOREIGN KEY (moderatorid) REFERENCES public.moderators(moderatorid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2866 (class 2606 OID 17944)
|
|
-- Name: berichten berichten_statusid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten
|
|
ADD CONSTRAINT berichten_statusid_fkey FOREIGN KEY (statusid) REFERENCES public.statuses(statusid);
|
|
|
|
|
|
--
|
|
-- TOC entry 2863 (class 2606 OID 17929)
|
|
-- Name: berichten berichten_twitterid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.berichten
|
|
ADD CONSTRAINT berichten_twitterid_fkey FOREIGN KEY (twitterid) REFERENCES public.twitterberichten(twitterid);
|
|
|
|
|
|
--
|
|
-- TOC entry 3005 (class 0 OID 0)
|
|
-- Dependencies: 208
|
|
-- Name: TABLE berichten; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON TABLE public.berichten TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3007 (class 0 OID 0)
|
|
-- Dependencies: 207
|
|
-- Name: SEQUENCE berichten_berichtid_seq; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON SEQUENCE public.berichten_berichtid_seq TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3008 (class 0 OID 0)
|
|
-- Dependencies: 202
|
|
-- Name: TABLE locaties; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON TABLE public.locaties TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3009 (class 0 OID 0)
|
|
-- Dependencies: 204
|
|
-- Name: TABLE moderators; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON TABLE public.moderators TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3011 (class 0 OID 0)
|
|
-- Dependencies: 203
|
|
-- Name: SEQUENCE moderators_moderatorid_seq; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON SEQUENCE public.moderators_moderatorid_seq TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3012 (class 0 OID 0)
|
|
-- Dependencies: 206
|
|
-- Name: TABLE statuses; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON TABLE public.statuses TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 3013 (class 0 OID 0)
|
|
-- Dependencies: 205
|
|
-- Name: TABLE twitterberichten; Type: ACL; Schema: public; Owner: postgres
|
|
--
|
|
|
|
GRANT ALL ON TABLE public.twitterberichten TO pyuser;
|
|
|
|
|
|
--
|
|
-- TOC entry 1707 (class 826 OID 17622)
|
|
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: -; Owner: postgres
|
|
--
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL ON TABLES TO pyuser;
|
|
|
|
|
|
-- Completed on 2020-11-10 15:04:24 CET
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|