diff options
Diffstat (limited to 'db/dump.sql')
| -rw-r--r-- | db/dump.sql | 250 |
1 files changed, 249 insertions, 1 deletions
diff --git a/db/dump.sql b/db/dump.sql index 5cb3a51..9486eb6 100644 --- a/db/dump.sql +++ b/db/dump.sql @@ -32,6 +32,28 @@ COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions'; -- +-- Name: assignment_state; Type: TYPE; Schema: public; Owner: postgres +-- + +CREATE TYPE public.assignment_state AS ENUM ( + 'AWAITING_GARDENER_NOTIFICATION', + 'AWAITING_FINISH', + 'AWAITING_WATERMARKING', + 'AWAITING_OWNER_NOTIFICATION', + 'DONE' +); + + +ALTER TYPE public.assignment_state OWNER TO postgres; + +-- +-- Name: TYPE assignment_state; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TYPE public.assignment_state IS 'The states for an assignment.'; + + +-- -- Name: role; Type: TYPE; Schema: public; Owner: postgres -- @@ -55,6 +77,120 @@ SET default_tablespace = ''; SET default_table_access_method = heap; -- +-- Name: assignments; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE public.assignments ( + id integer NOT NULL, + gardener_id integer NOT NULL, + cemetary_plot_id integer NOT NULL, + date date, + state public.assignment_state DEFAULT 'AWAITING_GARDENER_NOTIFICATION'::public.assignment_state NOT NULL, + note text +); + + +ALTER TABLE public.assignments OWNER TO postgres; + +-- +-- Name: COLUMN assignments.note; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON COLUMN public.assignments.note IS 'When an assignment is finished, the gardener can supply a note.'; + + +-- +-- Name: assignments_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +ALTER TABLE public.assignments ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.assignments_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: cemetary_plots; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE public.cemetary_plots ( + id integer NOT NULL, + address text NOT NULL, + owner_id integer NOT NULL, + assignment_interval interval day DEFAULT '14 days'::interval NOT NULL +); + + +ALTER TABLE public.cemetary_plots OWNER TO postgres; + +-- +-- Name: COLUMN cemetary_plots.assignment_interval; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON COLUMN public.cemetary_plots.assignment_interval IS 'The interval between when cleaning assignments. If there was a job at time N, the next one would be at N + assignment_interval.'; + + +-- +-- Name: cementary_plots_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +ALTER TABLE public.cemetary_plots ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.cementary_plots_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: images; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE public.images ( + id integer NOT NULL, + s3_path text NOT NULL, + original_filename text NOT NULL, + assignment_id integer +); + + +ALTER TABLE public.images OWNER TO postgres; + +-- +-- Name: TABLE images; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE public.images IS 'This table stores the images uploaded when finishing an assignment.'; + + +-- +-- Name: COLUMN images.assignment_id; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON COLUMN public.images.assignment_id IS 'Reference to the owning assignment'; + + +-- +-- Name: images_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +ALTER TABLE public.images ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.images_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- -- Name: sessions; Type: TABLE; Schema: public; Owner: postgres -- @@ -105,12 +241,44 @@ ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( -- +-- Data for Name: assignments; Type: TABLE DATA; Schema: public; Owner: postgres +-- + +COPY public.assignments (id, gardener_id, cemetary_plot_id, date, state, note) FROM stdin; +2 1 2 2025-03-05 AWAITING_GARDENER_NOTIFICATION \N +1 1 1 2025-02-28 AWAITING_WATERMARKING \N +\. + + +-- +-- Data for Name: cemetary_plots; Type: TABLE DATA; Schema: public; Owner: postgres +-- + +COPY public.cemetary_plots (id, address, owner_id, assignment_interval) FROM stdin; +1 24B, Kirkevej 64, Egå 8328 4 21 days +2 26C, Kirkevej 64, Egå 8328 5 14 days +\. + + +-- +-- Data for Name: images; Type: TABLE DATA; Schema: public; Owner: postgres +-- + +COPY public.images (id, s3_path, original_filename, assignment_id) FROM stdin; +3 fe5e7c27-99c4-4d4b-ba77-fcad5c6607e3 eva.jpeg 1 +\. + + +-- -- Data for Name: sessions; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.sessions (token, user_id, expires_at) FROM stdin; je2bic5i5jou7woob4ehreoqojuir4co 1 2025-03-19 18:04:35.904+01 27ghlb4vzh543kxpnkniip2oai6q4dvx 1 2025-03-19 18:04:47.722+01 +bjt7wgbfrlzrg7fhphsxezpc7fornezv 1 2025-03-19 20:42:57.838+01 +bgrgdtxmiq44ig6dh32pop4wkterqj6r 1 2025-03-20 18:03:54.199+01 +q6ifvlkjdoncw5usnb37da3sf4y3fxyq 1 2025-03-20 20:01:53.318+01 \. @@ -122,14 +290,54 @@ COPY public.users (id, role, first_name, last_name, password_hash, email) FROM s 1 gardener Emil Blomst $2a$06$vw1iVTLDQzeUwmhyW17ZzeJL.gyC4xWWzAwvJO8dsZi7Jdg2w5zbu [email protected] 2 gardener Sarah Hækkesen $2a$06$RyzzMad/jmKZwg9hRDymCO/KvxiSUFcdYQZdxB9trHHOU3tKhtFIK sarah.hækkesen@kirkefætrene.dk 3 gardener Sten Graversen $2a$06$/InJ2HFv1/z0YVXMwoHWZuV7aTlWl3GH9csKEvN5gb4nEPSECEdFq [email protected] +4 owner Lotte Ejersen $2a$06$C1JTdp2y9lIO57S7kGsBruE2oLj87AYpNNrZqx/5Zgap5lqcDj.my [email protected] +5 owner Mads Hylgaard Jensen $2a$06$FuFHsoFo1cPJ9ms9TceVG..2BAHAyJpE2NyBUTH9JnG6v8GWuOh4u [email protected] +6 owner Lukas Ibasen $2a$06$p2nOFXtLC0YE/7TTxqzUOeEfpPKVjVZr2sge1KBFT/7lrx2rpsvO2 [email protected] \. -- +-- Name: assignments_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres +-- + +SELECT pg_catalog.setval('public.assignments_id_seq', 2, true); + + +-- +-- Name: cementary_plots_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres +-- + +SELECT pg_catalog.setval('public.cementary_plots_id_seq', 2, true); + + +-- +-- Name: images_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres +-- + +SELECT pg_catalog.setval('public.images_id_seq', 3, true); + + +-- -- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- -SELECT pg_catalog.setval('public.users_id_seq', 3, true); +SELECT pg_catalog.setval('public.users_id_seq', 6, true); + + +-- +-- Name: assignments assignments_id_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.assignments + ADD CONSTRAINT assignments_id_pkey PRIMARY KEY (id); + + +-- +-- Name: cemetary_plots cemetary_plots_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.cemetary_plots + ADD CONSTRAINT cemetary_plots_pkey PRIMARY KEY (id); -- @@ -148,6 +356,14 @@ COMMENT ON CONSTRAINT email_uniqueness ON public.users IS 'Multiple users cannot -- +-- Name: images images_s3_path_key; Type: CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.images + ADD CONSTRAINT images_s3_path_key UNIQUE (s3_path); + + +-- -- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- @@ -164,6 +380,38 @@ ALTER TABLE ONLY public.users -- +-- Name: assignments assignments_cemetary_plot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.assignments + ADD CONSTRAINT assignments_cemetary_plot_id_fkey FOREIGN KEY (cemetary_plot_id) REFERENCES public.cemetary_plots(id); + + +-- +-- Name: assignments assignments_gardener_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.assignments + ADD CONSTRAINT assignments_gardener_id_fkey FOREIGN KEY (gardener_id) REFERENCES public.users(id); + + +-- +-- Name: cemetary_plots cemetary_plots_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.cemetary_plots + ADD CONSTRAINT cemetary_plots_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id); + + +-- +-- Name: images images_assignment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.images + ADD CONSTRAINT images_assignment_id_fkey FOREIGN KEY (assignment_id) REFERENCES public.assignments(id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- -- Name: sessions sessions_userid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- |