2 -- PostgreSQL database dump
5 SET client_encoding = 'UNICODE';
6 SET check_function_bodies = false;
8 SET SESSION AUTHORIZATION 'postgres';
11 -- TOC entry 4 (OID 2200)
12 -- Name: public; Type: ACL; Schema: -; Owner: postgres
15 REVOKE ALL ON SCHEMA public FROM PUBLIC;
16 REVOKE ALL ON SCHEMA public FROM postgres;
17 GRANT ALL ON SCHEMA public TO PUBLIC;
20 SET SESSION AUTHORIZATION 'rich';
22 SET search_path = public, pg_catalog;
25 -- TOC entry 5 (OID 536004)
26 -- Name: pages; Type: TABLE; Schema: public; Owner: rich
34 description text NOT NULL,
35 creation_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
36 last_modified_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
37 hostid integer NOT NULL,
46 -- TOC entry 6 (OID 536004)
47 -- Name: pages; Type: ACL; Schema: public; Owner: rich
50 REVOKE ALL ON TABLE pages FROM PUBLIC;
51 GRANT ALL ON TABLE pages TO "www-data";
54 SET SESSION AUTHORIZATION 'rich';
57 -- TOC entry 36 (OID 536004)
58 -- Name: pages_id_seq; Type: ACL; Schema: public; Owner: rich
61 REVOKE ALL ON TABLE pages_id_seq FROM PUBLIC;
62 GRANT ALL ON TABLE pages_id_seq TO "www-data";
65 SET SESSION AUTHORIZATION 'rich';
68 -- TOC entry 7 (OID 536021)
69 -- Name: contents; Type: TABLE; Schema: public; Owner: rich
72 CREATE TABLE contents (
74 pageid integer NOT NULL,
75 ordering integer NOT NULL,
77 content text NOT NULL,
83 -- TOC entry 8 (OID 536021)
84 -- Name: contents; Type: ACL; Schema: public; Owner: rich
87 REVOKE ALL ON TABLE contents FROM PUBLIC;
88 GRANT ALL ON TABLE contents TO "www-data";
91 SET SESSION AUTHORIZATION 'rich';
94 -- TOC entry 37 (OID 536021)
95 -- Name: contents_id_seq; Type: ACL; Schema: public; Owner: rich
98 REVOKE ALL ON TABLE contents_id_seq FROM PUBLIC;
99 GRANT ALL ON TABLE contents_id_seq TO "www-data";
102 SET SESSION AUTHORIZATION 'rich';
105 -- TOC entry 9 (OID 536371)
106 -- Name: hosts; Type: TABLE; Schema: public; Owner: rich
111 canonical_hostname text NOT NULL,
113 edit_anon boolean DEFAULT true NOT NULL,
114 create_account_anon boolean DEFAULT true NOT NULL,
121 -- TOC entry 10 (OID 536371)
122 -- Name: hosts; Type: ACL; Schema: public; Owner: rich
125 REVOKE ALL ON TABLE hosts FROM PUBLIC;
126 GRANT ALL ON TABLE hosts TO "www-data";
129 SET SESSION AUTHORIZATION 'rich';
132 -- TOC entry 38 (OID 536371)
133 -- Name: hosts_id_seq; Type: ACL; Schema: public; Owner: rich
136 REVOKE ALL ON TABLE hosts_id_seq FROM PUBLIC;
137 GRANT ALL ON TABLE hosts_id_seq TO "www-data";
140 SET SESSION AUTHORIZATION 'rich';
143 -- TOC entry 11 (OID 536379)
144 -- Name: hostnames; Type: TABLE; Schema: public; Owner: rich
147 CREATE TABLE hostnames (
148 hostid integer NOT NULL,
154 -- TOC entry 12 (OID 536379)
155 -- Name: hostnames; Type: ACL; Schema: public; Owner: rich
158 REVOKE ALL ON TABLE hostnames FROM PUBLIC;
159 GRANT ALL ON TABLE hostnames TO "www-data";
162 SET SESSION AUTHORIZATION 'rich';
165 -- TOC entry 13 (OID 536915)
166 -- Name: email_notify; Type: TABLE; Schema: public; Owner: rich
169 CREATE TABLE email_notify (
170 hostid integer NOT NULL,
177 -- TOC entry 14 (OID 536915)
178 -- Name: email_notify; Type: ACL; Schema: public; Owner: rich
181 REVOKE ALL ON TABLE email_notify FROM PUBLIC;
182 GRANT ALL ON TABLE email_notify TO "www-data";
185 SET SESSION AUTHORIZATION 'rich';
188 -- TOC entry 15 (OID 537151)
189 -- Name: images; Type: TABLE; Schema: public; Owner: rich
192 CREATE TABLE images (
194 hostid integer NOT NULL,
197 image bytea NOT NULL,
198 width integer NOT NULL,
199 height integer NOT NULL,
204 mime_type text NOT NULL,
209 upload_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
214 -- TOC entry 16 (OID 537151)
215 -- Name: images; Type: ACL; Schema: public; Owner: rich
218 REVOKE ALL ON TABLE images FROM PUBLIC;
219 GRANT ALL ON TABLE images TO "www-data";
222 SET SESSION AUTHORIZATION 'rich';
225 -- TOC entry 39 (OID 537151)
226 -- Name: images_id_seq; Type: ACL; Schema: public; Owner: rich
229 REVOKE ALL ON TABLE images_id_seq FROM PUBLIC;
230 GRANT ALL ON TABLE images_id_seq TO "www-data";
233 SET SESSION AUTHORIZATION 'rich';
236 -- TOC entry 17 (OID 537166)
237 -- Name: files; Type: TABLE; Schema: public; Owner: rich
242 hostid integer NOT NULL,
245 content bytea NOT NULL,
247 mime_type text NOT NULL,
248 upload_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
253 -- TOC entry 18 (OID 537166)
254 -- Name: files; Type: ACL; Schema: public; Owner: rich
257 REVOKE ALL ON TABLE files FROM PUBLIC;
258 GRANT ALL ON TABLE files TO "www-data";
261 SET SESSION AUTHORIZATION 'rich';
264 -- TOC entry 40 (OID 537166)
265 -- Name: files_id_seq; Type: ACL; Schema: public; Owner: rich
268 REVOKE ALL ON TABLE files_id_seq FROM PUBLIC;
269 GRANT ALL ON TABLE files_id_seq TO "www-data";
272 SET SESSION AUTHORIZATION 'rich';
275 -- TOC entry 19 (OID 540816)
276 -- Name: users; Type: TABLE; Schema: public; Owner: rich
281 hostid integer NOT NULL,
283 "password" text NOT NULL,
285 registration_date date DEFAULT ('now'::text)::date NOT NULL,
286 can_edit boolean DEFAULT true NOT NULL,
287 can_manage_users boolean DEFAULT false NOT NULL,
288 can_manage_contacts boolean DEFAULT false NOT NULL,
289 can_manage_site boolean DEFAULT false NOT NULL,
290 can_edit_global_css boolean DEFAULT false NOT NULL
295 -- TOC entry 20 (OID 540816)
296 -- Name: users; Type: ACL; Schema: public; Owner: rich
299 REVOKE ALL ON TABLE users FROM PUBLIC;
300 GRANT ALL ON TABLE users TO "www-data";
303 SET SESSION AUTHORIZATION 'rich';
306 -- TOC entry 41 (OID 540816)
307 -- Name: users_id_seq; Type: ACL; Schema: public; Owner: rich
310 REVOKE ALL ON TABLE users_id_seq FROM PUBLIC;
311 GRANT ALL ON TABLE users_id_seq TO "www-data";
314 SET SESSION AUTHORIZATION 'rich';
317 -- TOC entry 21 (OID 540832)
318 -- Name: usercookies; Type: TABLE; Schema: public; Owner: rich
321 CREATE TABLE usercookies (
322 userid integer NOT NULL,
328 -- TOC entry 22 (OID 540832)
329 -- Name: usercookies; Type: ACL; Schema: public; Owner: rich
332 REVOKE ALL ON TABLE usercookies FROM PUBLIC;
333 GRANT ALL ON TABLE usercookies TO "www-data";
336 SET SESSION AUTHORIZATION 'rich';
339 -- TOC entry 23 (OID 540961)
340 -- Name: sitemenu; Type: TABLE; Schema: public; Owner: rich
343 CREATE TABLE sitemenu (
344 hostid integer NOT NULL,
347 ordering integer NOT NULL
352 -- TOC entry 24 (OID 540961)
353 -- Name: sitemenu; Type: ACL; Schema: public; Owner: rich
356 REVOKE ALL ON TABLE sitemenu FROM PUBLIC;
357 GRANT ALL ON TABLE sitemenu TO "www-data";
360 SET SESSION AUTHORIZATION 'rich';
363 -- TOC entry 25 (OID 542605)
364 -- Name: contacts; Type: TABLE; Schema: public; Owner: rich
367 CREATE TABLE contacts (
369 hostid integer NOT NULL,
371 subject text NOT NULL
376 -- TOC entry 26 (OID 542605)
377 -- Name: contacts; Type: ACL; Schema: public; Owner: rich
380 REVOKE ALL ON TABLE contacts FROM PUBLIC;
381 GRANT ALL ON TABLE contacts TO "www-data";
384 SET SESSION AUTHORIZATION 'rich';
387 -- TOC entry 42 (OID 542605)
388 -- Name: contacts_id_seq; Type: ACL; Schema: public; Owner: rich
391 REVOKE ALL ON TABLE contacts_id_seq FROM PUBLIC;
392 GRANT ALL ON TABLE contacts_id_seq TO "www-data";
395 SET SESSION AUTHORIZATION 'rich';
398 -- TOC entry 27 (OID 542617)
399 -- Name: contact_emails; Type: TABLE; Schema: public; Owner: rich
402 CREATE TABLE contact_emails (
403 contactid integer NOT NULL,
409 -- TOC entry 28 (OID 542617)
410 -- Name: contact_emails; Type: ACL; Schema: public; Owner: rich
413 REVOKE ALL ON TABLE contact_emails FROM PUBLIC;
414 GRANT ALL ON TABLE contact_emails TO "www-data";
417 SET SESSION AUTHORIZATION 'rich';
420 -- TOC entry 29 (OID 543499)
421 -- Name: themes; Type: TABLE; Schema: public; Owner: rich
424 CREATE TABLE themes (
425 theme_css text NOT NULL,
427 description text NOT NULL
432 -- TOC entry 30 (OID 543499)
433 -- Name: themes; Type: ACL; Schema: public; Owner: rich
436 REVOKE ALL ON TABLE themes FROM PUBLIC;
437 GRANT SELECT ON TABLE themes TO "www-data";
440 SET SESSION AUTHORIZATION 'rich';
443 -- TOC entry 31 (OID 543592)
444 -- Name: server_settings; Type: TABLE; Schema: public; Owner: rich
447 CREATE TABLE server_settings (
448 "version" integer NOT NULL,
454 -- TOC entry 33 (OID 543592)
455 -- Name: server_settings; Type: ACL; Schema: public; Owner: rich
458 REVOKE ALL ON TABLE server_settings FROM PUBLIC;
459 GRANT SELECT ON TABLE server_settings TO "www-data";
462 SET SESSION AUTHORIZATION 'rich';
465 -- TOC entry 34 (OID 543754)
466 -- Name: page_emails; Type: TABLE; Schema: public; Owner: rich
469 CREATE TABLE page_emails (
470 hostid integer NOT NULL,
473 entry_date date DEFAULT ('now'::text)::date NOT NULL,
474 last_sent date DEFAULT ('now'::text)::date NOT NULL,
476 opt_out text NOT NULL
481 -- TOC entry 35 (OID 543754)
482 -- Name: page_emails; Type: ACL; Schema: public; Owner: rich
485 REVOKE ALL ON TABLE page_emails FROM PUBLIC;
486 GRANT ALL ON TABLE page_emails TO "www-data";
489 SET SESSION AUTHORIZATION 'rich';
492 -- TOC entry 47 (OID 536388)
493 -- Name: hostnames_hostid_name_uq; Type: INDEX; Schema: public; Owner: rich
496 CREATE UNIQUE INDEX hostnames_hostid_name_uq ON hostnames USING btree (hostid, name);
500 -- TOC entry 48 (OID 536389)
501 -- Name: hostnams_name_uq; Type: INDEX; Schema: public; Owner: rich
504 CREATE UNIQUE INDEX hostnams_name_uq ON hostnames USING btree (name);
508 -- TOC entry 44 (OID 536419)
509 -- Name: pages_url_uq; Type: INDEX; Schema: public; Owner: rich
512 CREATE UNIQUE INDEX pages_url_uq ON pages USING btree (hostid, url);
516 -- TOC entry 49 (OID 536924)
517 -- Name: email_notify_email_uq; Type: INDEX; Schema: public; Owner: rich
520 CREATE UNIQUE INDEX email_notify_email_uq ON email_notify USING btree (hostid, email);
524 -- TOC entry 50 (OID 540251)
525 -- Name: images_name_uq; Type: INDEX; Schema: public; Owner: rich
528 CREATE UNIQUE INDEX images_name_uq ON images USING btree (hostid, name);
532 -- TOC entry 52 (OID 540252)
533 -- Name: files_name_uq; Type: INDEX; Schema: public; Owner: rich
536 CREATE UNIQUE INDEX files_name_uq ON files USING btree (hostid, name);
540 -- TOC entry 55 (OID 540831)
541 -- Name: users_name_uq; Type: INDEX; Schema: public; Owner: rich
544 CREATE UNIQUE INDEX users_name_uq ON users USING btree (hostid, name);
548 -- TOC entry 54 (OID 540946)
549 -- Name: users_id_uq; Type: INDEX; Schema: public; Owner: rich
552 CREATE UNIQUE INDEX users_id_uq ON users USING btree (hostid, id);
556 -- TOC entry 57 (OID 540970)
557 -- Name: sitemenu_ordering_uq; Type: INDEX; Schema: public; Owner: rich
560 CREATE UNIQUE INDEX sitemenu_ordering_uq ON sitemenu USING btree (hostid, ordering);
564 -- TOC entry 58 (OID 540971)
565 -- Name: sitemenu_url_uq; Type: INDEX; Schema: public; Owner: rich
568 CREATE UNIQUE INDEX sitemenu_url_uq ON sitemenu USING btree (hostid, url);
572 -- TOC entry 60 (OID 542626)
573 -- Name: contact_emails_uq; Type: INDEX; Schema: public; Owner: rich
576 CREATE UNIQUE INDEX contact_emails_uq ON contact_emails USING btree (contactid, email);
580 -- TOC entry 61 (OID 543505)
581 -- Name: themes_theme_css_uq; Type: INDEX; Schema: public; Owner: rich
584 CREATE UNIQUE INDEX themes_theme_css_uq ON themes USING btree (theme_css);
588 -- TOC entry 62 (OID 543763)
589 -- Name: page_emails_email_uq; Type: INDEX; Schema: public; Owner: rich
592 CREATE UNIQUE INDEX page_emails_email_uq ON page_emails USING btree (hostid, url, email);
596 -- TOC entry 43 (OID 536012)
597 -- Name: pages_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
600 ALTER TABLE ONLY pages
601 ADD CONSTRAINT pages_pkey PRIMARY KEY (id);
605 -- TOC entry 45 (OID 536027)
606 -- Name: contents_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
609 ALTER TABLE ONLY contents
610 ADD CONSTRAINT contents_pkey PRIMARY KEY (id);
614 -- TOC entry 46 (OID 536377)
615 -- Name: hosts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
618 ALTER TABLE ONLY hosts
619 ADD CONSTRAINT hosts_pkey PRIMARY KEY (id);
623 -- TOC entry 51 (OID 537158)
624 -- Name: images_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
627 ALTER TABLE ONLY images
628 ADD CONSTRAINT images_pkey PRIMARY KEY (id);
632 -- TOC entry 53 (OID 537173)
633 -- Name: files_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
636 ALTER TABLE ONLY files
637 ADD CONSTRAINT files_pkey PRIMARY KEY (id);
641 -- TOC entry 56 (OID 540825)
642 -- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
645 ALTER TABLE ONLY users
646 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
650 -- TOC entry 59 (OID 542611)
651 -- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
654 ALTER TABLE ONLY contacts
655 ADD CONSTRAINT contacts_pkey PRIMARY KEY (id);
659 -- TOC entry 67 (OID 536029)
660 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
663 ALTER TABLE ONLY contents
664 ADD CONSTRAINT "$1" FOREIGN KEY (pageid) REFERENCES pages(id);
668 -- TOC entry 70 (OID 536384)
669 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
672 ALTER TABLE ONLY hostnames
673 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
677 -- TOC entry 68 (OID 536394)
678 -- Name: hosts_hostname_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
681 ALTER TABLE ONLY hosts
682 ADD CONSTRAINT hosts_hostname_cn FOREIGN KEY (id, canonical_hostname) REFERENCES hostnames(hostid, name) DEFERRABLE;
686 -- TOC entry 63 (OID 536404)
687 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
690 ALTER TABLE ONLY pages
691 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
695 -- TOC entry 71 (OID 536920)
696 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
699 ALTER TABLE ONLY email_notify
700 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
704 -- TOC entry 72 (OID 537160)
705 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
708 ALTER TABLE ONLY images
709 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
713 -- TOC entry 73 (OID 537175)
714 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
717 ALTER TABLE ONLY files
718 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
722 -- TOC entry 64 (OID 539155)
723 -- Name: pages_redirect_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
726 ALTER TABLE ONLY pages
727 ADD CONSTRAINT pages_redirect_cn FOREIGN KEY (hostid, redirect) REFERENCES pages(hostid, url) DEFERRABLE;
731 -- TOC entry 74 (OID 540827)
732 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
735 ALTER TABLE ONLY users
736 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
740 -- TOC entry 75 (OID 540837)
741 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
744 ALTER TABLE ONLY usercookies
745 ADD CONSTRAINT "$1" FOREIGN KEY (userid) REFERENCES users(id);
749 -- TOC entry 65 (OID 540942)
750 -- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: rich
753 ALTER TABLE ONLY pages
754 ADD CONSTRAINT "$2" FOREIGN KEY (logged_user) REFERENCES users(id);
758 -- TOC entry 66 (OID 540947)
759 -- Name: pages_user_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
762 ALTER TABLE ONLY pages
763 ADD CONSTRAINT pages_user_cn FOREIGN KEY (hostid, logged_user) REFERENCES users(hostid, id);
767 -- TOC entry 76 (OID 540966)
768 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
771 ALTER TABLE ONLY sitemenu
772 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
776 -- TOC entry 77 (OID 540972)
777 -- Name: sitemenu_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
780 ALTER TABLE ONLY sitemenu
781 ADD CONSTRAINT sitemenu_url_cn FOREIGN KEY (hostid, url) REFERENCES pages(hostid, url) DEFERRABLE;
785 -- TOC entry 78 (OID 542613)
786 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
789 ALTER TABLE ONLY contacts
790 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
794 -- TOC entry 79 (OID 542622)
795 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
798 ALTER TABLE ONLY contact_emails
799 ADD CONSTRAINT "$1" FOREIGN KEY (contactid) REFERENCES contacts(id);
803 -- TOC entry 69 (OID 543506)
804 -- Name: hosts_theme_css_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
807 ALTER TABLE ONLY hosts
808 ADD CONSTRAINT hosts_theme_css_cn FOREIGN KEY (theme_css) REFERENCES themes(theme_css);
812 -- TOC entry 80 (OID 543759)
813 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
816 ALTER TABLE ONLY page_emails
817 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
821 -- TOC entry 81 (OID 543764)
822 -- Name: page_emails_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
825 ALTER TABLE ONLY page_emails
826 ADD CONSTRAINT page_emails_url_cn FOREIGN KEY (hostid, url) REFERENCES pages(hostid, url) DEFERRABLE;
829 SET SESSION AUTHORIZATION 'postgres';
832 -- TOC entry 3 (OID 2200)
833 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
836 COMMENT ON SCHEMA public IS 'Standard public namespace';
839 SET SESSION AUTHORIZATION 'rich';
842 -- TOC entry 32 (OID 543592)
843 -- Name: TABLE server_settings; Type: COMMENT; Schema: public; Owner: rich
846 COMMENT ON TABLE server_settings IS 'This table contains global settings for the server. There should be only one row in this table. If you modify any setting, you must restart the webserver for the change to take effect. The "version" field is intended to be some sort of database version, but is currently unused.';