Christmas cookie ranking site
eldridge.cam
1--
2-- PostgreSQL database dump
3--
4
5\restrict qXshd39oiCznDbwHeqMZZXGFQFkDW6ZtqaC6QTgRr4Sx5B2LqyfNtAK3MzBi5md
6
7-- Dumped from database version 17.7 (Debian 17.7-3.pgdg13+1)
8-- Dumped by pg_dump version 17.7 (Debian 17.7-3.pgdg13+1)
9
10SET statement_timeout = 0;
11SET lock_timeout = 0;
12SET idle_in_transaction_session_timeout = 0;
13SET transaction_timeout = 0;
14SET client_encoding = 'UTF8';
15SET standard_conforming_strings = on;
16SELECT pg_catalog.set_config('search_path', '', false);
17SET check_function_bodies = false;
18SET xmloption = content;
19SET client_min_messages = warning;
20SET row_security = off;
21
22--
23-- Name: citext; Type: EXTENSION; Schema: -; Owner: -
24--
25
26CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
27
28
29--
30-- Name: EXTENSION citext; Type: COMMENT; Schema: -; Owner: -
31--
32
33COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings';
34
35
36SET default_tablespace = '';
37
38SET default_table_access_method = heap;
39
40--
41-- Name: accounts; Type: TABLE; Schema: public; Owner: -
42--
43
44CREATE TABLE public.accounts (
45 id public.citext NOT NULL,
46 created_at timestamp with time zone DEFAULT now() NOT NULL,
47 CONSTRAINT account_id_max_length CHECK ((length((id)::text) <= 128))
48);
49
50
51--
52-- Name: comments; Type: TABLE; Schema: public; Owner: -
53--
54
55CREATE TABLE public.comments (
56 id bigint NOT NULL,
57 account_id public.citext NOT NULL,
58 review_id bigint NOT NULL,
59 comment text NOT NULL,
60 created_at timestamp with time zone DEFAULT now() NOT NULL,
61 CONSTRAINT comment_max_length CHECK ((length(comment) <= 2048))
62);
63
64
65--
66-- Name: comments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
67--
68
69ALTER TABLE public.comments ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
70 SEQUENCE NAME public.comments_id_seq
71 START WITH 1
72 INCREMENT BY 1
73 NO MINVALUE
74 NO MAXVALUE
75 CACHE 1
76);
77
78
79--
80-- Name: cookies; Type: TABLE; Schema: public; Owner: -
81--
82
83CREATE TABLE public.cookies (
84 id text NOT NULL,
85 name text NOT NULL,
86 year integer NOT NULL,
87 description text NOT NULL,
88 ordering integer NOT NULL,
89 image_url text
90);
91
92
93--
94-- Name: rankings; Type: TABLE; Schema: public; Owner: -
95--
96
97CREATE TABLE public.rankings (
98 id bigint NOT NULL,
99 account_id public.citext NOT NULL,
100 cookie_id text NOT NULL,
101 year integer NOT NULL,
102 ranking integer NOT NULL,
103 created_at timestamp with time zone DEFAULT now() NOT NULL
104);
105
106
107--
108-- Name: rankings_id_seq; Type: SEQUENCE; Schema: public; Owner: -
109--
110
111ALTER TABLE public.rankings ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
112 SEQUENCE NAME public.rankings_id_seq
113 START WITH 1
114 INCREMENT BY 1
115 NO MINVALUE
116 NO MAXVALUE
117 CACHE 1
118);
119
120
121--
122-- Name: reviews; Type: TABLE; Schema: public; Owner: -
123--
124
125CREATE TABLE public.reviews (
126 id bigint NOT NULL,
127 account_id public.citext NOT NULL,
128 cookie_id text NOT NULL,
129 comment text NOT NULL,
130 year integer NOT NULL,
131 created_at timestamp with time zone DEFAULT now() NOT NULL,
132 CONSTRAINT comment_max_length CHECK ((length(comment) <= 2048))
133);
134
135
136--
137-- Name: reviews_id_seq; Type: SEQUENCE; Schema: public; Owner: -
138--
139
140ALTER TABLE public.reviews ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
141 SEQUENCE NAME public.reviews_id_seq
142 START WITH 1
143 INCREMENT BY 1
144 NO MINVALUE
145 NO MAXVALUE
146 CACHE 1
147);
148
149
150--
151-- Name: sessions; Type: TABLE; Schema: public; Owner: -
152--
153
154CREATE TABLE public.sessions (
155 id uuid DEFAULT gen_random_uuid() NOT NULL,
156 account_id public.citext,
157 created_at timestamp with time zone DEFAULT now() NOT NULL,
158 expires_at timestamp with time zone DEFAULT (now() + '30 days'::interval) NOT NULL
159);
160
161
162--
163-- Name: accounts accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -
164--
165
166ALTER TABLE ONLY public.accounts
167 ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
168
169
170--
171-- Name: comments comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
172--
173
174ALTER TABLE ONLY public.comments
175 ADD CONSTRAINT comments_pkey PRIMARY KEY (id);
176
177
178--
179-- Name: cookies cookies_pkey; Type: CONSTRAINT; Schema: public; Owner: -
180--
181
182ALTER TABLE ONLY public.cookies
183 ADD CONSTRAINT cookies_pkey PRIMARY KEY (id, year);
184
185
186--
187-- Name: cookies cookies_year_ordering_key; Type: CONSTRAINT; Schema: public; Owner: -
188--
189
190ALTER TABLE ONLY public.cookies
191 ADD CONSTRAINT cookies_year_ordering_key UNIQUE (year, ordering) DEFERRABLE INITIALLY DEFERRED;
192
193
194--
195-- Name: rankings rankings_account_id_cookie_id_year_key; Type: CONSTRAINT; Schema: public; Owner: -
196--
197
198ALTER TABLE ONLY public.rankings
199 ADD CONSTRAINT rankings_account_id_cookie_id_year_key UNIQUE (account_id, cookie_id, year);
200
201
202--
203-- Name: rankings rankings_account_id_year_ranking_key; Type: CONSTRAINT; Schema: public; Owner: -
204--
205
206ALTER TABLE ONLY public.rankings
207 ADD CONSTRAINT rankings_account_id_year_ranking_key UNIQUE (account_id, year, ranking);
208
209
210--
211-- Name: rankings rankings_pkey; Type: CONSTRAINT; Schema: public; Owner: -
212--
213
214ALTER TABLE ONLY public.rankings
215 ADD CONSTRAINT rankings_pkey PRIMARY KEY (id);
216
217
218--
219-- Name: reviews reviews_pkey; Type: CONSTRAINT; Schema: public; Owner: -
220--
221
222ALTER TABLE ONLY public.reviews
223 ADD CONSTRAINT reviews_pkey PRIMARY KEY (id);
224
225
226--
227-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
228--
229
230ALTER TABLE ONLY public.sessions
231 ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);
232
233
234--
235-- Name: comments comments_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
236--
237
238ALTER TABLE ONLY public.comments
239 ADD CONSTRAINT comments_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
240
241
242--
243-- Name: comments comments_review_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
244--
245
246ALTER TABLE ONLY public.comments
247 ADD CONSTRAINT comments_review_id_fkey FOREIGN KEY (review_id) REFERENCES public.reviews(id) ON UPDATE CASCADE ON DELETE CASCADE;
248
249
250--
251-- Name: rankings rankings_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
252--
253
254ALTER TABLE ONLY public.rankings
255 ADD CONSTRAINT rankings_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
256
257
258--
259-- Name: rankings rankings_cookie_id_year_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
260--
261
262ALTER TABLE ONLY public.rankings
263 ADD CONSTRAINT rankings_cookie_id_year_fkey FOREIGN KEY (cookie_id, year) REFERENCES public.cookies(id, year) ON UPDATE CASCADE ON DELETE CASCADE;
264
265
266--
267-- Name: reviews reviews_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
268--
269
270ALTER TABLE ONLY public.reviews
271 ADD CONSTRAINT reviews_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
272
273
274--
275-- Name: reviews reviews_cookie_id_year_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
276--
277
278ALTER TABLE ONLY public.reviews
279 ADD CONSTRAINT reviews_cookie_id_year_fkey FOREIGN KEY (cookie_id, year) REFERENCES public.cookies(id, year) ON UPDATE CASCADE ON DELETE CASCADE;
280
281
282--
283-- Name: sessions sessions_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
284--
285
286ALTER TABLE ONLY public.sessions
287 ADD CONSTRAINT sessions_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.accounts(id) ON UPDATE CASCADE ON DELETE SET NULL;
288
289
290--
291-- PostgreSQL database dump complete
292--
293
294\unrestrict qXshd39oiCznDbwHeqMZZXGFQFkDW6ZtqaC6QTgRr4Sx5B2LqyfNtAK3MzBi5md
295