2 -- PostgreSQL database dump
5 SET client_encoding = 'UNICODE';
6 SET check_function_bodies = false;
9 -- TOC entry 4 (OID 2200)
10 -- Name: public; Type: ACL; Schema: -; Owner: postgres
13 REVOKE ALL ON SCHEMA public FROM PUBLIC;
14 REVOKE ALL ON SCHEMA public FROM postgres;
15 GRANT ALL ON SCHEMA public TO PUBLIC;
18 SET search_path = public, pg_catalog;
21 -- TOC entry 5 (OID 536004)
22 -- Name: pages; Type: TABLE; Schema: public; Owner: rich
30 description text NOT NULL,
31 creation_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
32 last_modified_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
33 hostid integer NOT NULL,
42 -- TOC entry 6 (OID 536004)
43 -- Name: pages; Type: ACL; Schema: public; Owner: rich
46 REVOKE ALL ON TABLE pages FROM PUBLIC;
47 GRANT ALL ON TABLE pages TO "www-data";
51 -- TOC entry 38 (OID 536004)
52 -- Name: pages_id_seq; Type: ACL; Schema: public; Owner: rich
55 REVOKE ALL ON TABLE pages_id_seq FROM PUBLIC;
56 GRANT ALL ON TABLE pages_id_seq TO "www-data";
60 -- TOC entry 7 (OID 536021)
61 -- Name: contents; Type: TABLE; Schema: public; Owner: rich
64 CREATE TABLE contents (
66 pageid integer NOT NULL,
67 ordering integer NOT NULL,
69 content text NOT NULL,
75 -- TOC entry 8 (OID 536021)
76 -- Name: contents; Type: ACL; Schema: public; Owner: rich
79 REVOKE ALL ON TABLE contents FROM PUBLIC;
80 GRANT ALL ON TABLE contents TO "www-data";
84 -- TOC entry 39 (OID 536021)
85 -- Name: contents_id_seq; Type: ACL; Schema: public; Owner: rich
88 REVOKE ALL ON TABLE contents_id_seq FROM PUBLIC;
89 GRANT ALL ON TABLE contents_id_seq TO "www-data";
93 -- TOC entry 9 (OID 536371)
94 -- Name: hosts; Type: TABLE; Schema: public; Owner: rich
99 canonical_hostname text NOT NULL,
101 edit_anon boolean DEFAULT true NOT NULL,
102 create_account_anon boolean DEFAULT true NOT NULL,
105 mailing_list boolean DEFAULT false NOT NULL,
106 is_template boolean DEFAULT false NOT NULL,
107 search_box boolean DEFAULT true NOT NULL
112 -- TOC entry 10 (OID 536371)
113 -- Name: hosts; Type: ACL; Schema: public; Owner: rich
116 REVOKE ALL ON TABLE hosts FROM PUBLIC;
117 GRANT ALL ON TABLE hosts TO "www-data";
121 -- TOC entry 40 (OID 536371)
122 -- Name: hosts_id_seq; Type: ACL; Schema: public; Owner: rich
125 REVOKE ALL ON TABLE hosts_id_seq FROM PUBLIC;
126 GRANT ALL ON TABLE hosts_id_seq TO "www-data";
130 -- TOC entry 11 (OID 536379)
131 -- Name: hostnames; Type: TABLE; Schema: public; Owner: rich
134 CREATE TABLE hostnames (
135 hostid integer NOT NULL,
141 -- TOC entry 12 (OID 536379)
142 -- Name: hostnames; Type: ACL; Schema: public; Owner: rich
145 REVOKE ALL ON TABLE hostnames FROM PUBLIC;
146 GRANT ALL ON TABLE hostnames TO "www-data";
150 -- TOC entry 13 (OID 536915)
151 -- Name: email_notify; Type: TABLE; Schema: public; Owner: rich
154 CREATE TABLE email_notify (
155 hostid integer NOT NULL,
162 -- TOC entry 14 (OID 536915)
163 -- Name: email_notify; Type: ACL; Schema: public; Owner: rich
166 REVOKE ALL ON TABLE email_notify FROM PUBLIC;
167 GRANT ALL ON TABLE email_notify TO "www-data";
171 -- TOC entry 15 (OID 537151)
172 -- Name: images; Type: TABLE; Schema: public; Owner: rich
175 CREATE TABLE images (
177 hostid integer NOT NULL,
180 image bytea NOT NULL,
181 width integer NOT NULL,
182 height integer NOT NULL,
187 mime_type text NOT NULL,
192 upload_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
197 -- TOC entry 16 (OID 537151)
198 -- Name: images; Type: ACL; Schema: public; Owner: rich
201 REVOKE ALL ON TABLE images FROM PUBLIC;
202 GRANT ALL ON TABLE images TO "www-data";
206 -- TOC entry 41 (OID 537151)
207 -- Name: images_id_seq; Type: ACL; Schema: public; Owner: rich
210 REVOKE ALL ON TABLE images_id_seq FROM PUBLIC;
211 GRANT ALL ON TABLE images_id_seq TO "www-data";
215 -- TOC entry 17 (OID 537166)
216 -- Name: files; Type: TABLE; Schema: public; Owner: rich
221 hostid integer NOT NULL,
224 content bytea NOT NULL,
226 mime_type text NOT NULL,
227 upload_date timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
232 -- TOC entry 18 (OID 537166)
233 -- Name: files; Type: ACL; Schema: public; Owner: rich
236 REVOKE ALL ON TABLE files FROM PUBLIC;
237 GRANT ALL ON TABLE files TO "www-data";
241 -- TOC entry 42 (OID 537166)
242 -- Name: files_id_seq; Type: ACL; Schema: public; Owner: rich
245 REVOKE ALL ON TABLE files_id_seq FROM PUBLIC;
246 GRANT ALL ON TABLE files_id_seq TO "www-data";
250 -- TOC entry 19 (OID 540816)
251 -- Name: users; Type: TABLE; Schema: public; Owner: rich
256 hostid integer NOT NULL,
258 "password" text NOT NULL,
260 registration_date date DEFAULT ('now'::text)::date NOT NULL,
261 can_edit boolean DEFAULT true NOT NULL,
262 can_manage_users boolean DEFAULT false NOT NULL,
263 can_manage_contacts boolean DEFAULT false NOT NULL,
264 can_manage_site boolean DEFAULT false NOT NULL,
265 can_edit_global_css boolean DEFAULT false NOT NULL,
266 force_password_change boolean DEFAULT false NOT NULL
271 -- TOC entry 20 (OID 540816)
272 -- Name: users; Type: ACL; Schema: public; Owner: rich
275 REVOKE ALL ON TABLE users FROM PUBLIC;
276 GRANT ALL ON TABLE users TO "www-data";
280 -- TOC entry 43 (OID 540816)
281 -- Name: users_id_seq; Type: ACL; Schema: public; Owner: rich
284 REVOKE ALL ON TABLE users_id_seq FROM PUBLIC;
285 GRANT ALL ON TABLE users_id_seq TO "www-data";
289 -- TOC entry 21 (OID 540832)
290 -- Name: usercookies; Type: TABLE; Schema: public; Owner: rich
293 CREATE TABLE usercookies (
294 userid integer NOT NULL,
300 -- TOC entry 22 (OID 540832)
301 -- Name: usercookies; Type: ACL; Schema: public; Owner: rich
304 REVOKE ALL ON TABLE usercookies FROM PUBLIC;
305 GRANT ALL ON TABLE usercookies TO "www-data";
309 -- TOC entry 23 (OID 540961)
310 -- Name: sitemenu; Type: TABLE; Schema: public; Owner: rich
313 CREATE TABLE sitemenu (
314 hostid integer NOT NULL,
317 ordering integer NOT NULL
322 -- TOC entry 24 (OID 540961)
323 -- Name: sitemenu; Type: ACL; Schema: public; Owner: rich
326 REVOKE ALL ON TABLE sitemenu FROM PUBLIC;
327 GRANT ALL ON TABLE sitemenu TO "www-data";
331 -- TOC entry 25 (OID 542605)
332 -- Name: contacts; Type: TABLE; Schema: public; Owner: rich
335 CREATE TABLE contacts (
337 hostid integer NOT NULL,
339 subject text NOT NULL
344 -- TOC entry 26 (OID 542605)
345 -- Name: contacts; Type: ACL; Schema: public; Owner: rich
348 REVOKE ALL ON TABLE contacts FROM PUBLIC;
349 GRANT ALL ON TABLE contacts TO "www-data";
353 -- TOC entry 44 (OID 542605)
354 -- Name: contacts_id_seq; Type: ACL; Schema: public; Owner: rich
357 REVOKE ALL ON TABLE contacts_id_seq FROM PUBLIC;
358 GRANT ALL ON TABLE contacts_id_seq TO "www-data";
362 -- TOC entry 27 (OID 542617)
363 -- Name: contact_emails; Type: TABLE; Schema: public; Owner: rich
366 CREATE TABLE contact_emails (
367 contactid integer NOT NULL,
373 -- TOC entry 28 (OID 542617)
374 -- Name: contact_emails; Type: ACL; Schema: public; Owner: rich
377 REVOKE ALL ON TABLE contact_emails FROM PUBLIC;
378 GRANT ALL ON TABLE contact_emails TO "www-data";
382 -- TOC entry 29 (OID 543499)
383 -- Name: themes; Type: TABLE; Schema: public; Owner: rich
386 CREATE TABLE themes (
387 theme_css text NOT NULL,
389 description text NOT NULL
394 -- TOC entry 30 (OID 543499)
395 -- Name: themes; Type: ACL; Schema: public; Owner: rich
398 REVOKE ALL ON TABLE themes FROM PUBLIC;
399 GRANT SELECT ON TABLE themes TO "www-data";
403 -- TOC entry 31 (OID 543592)
404 -- Name: server_settings; Type: TABLE; Schema: public; Owner: rich
407 CREATE TABLE server_settings (
408 "version" integer NOT NULL,
414 -- TOC entry 33 (OID 543592)
415 -- Name: server_settings; Type: ACL; Schema: public; Owner: rich
418 REVOKE ALL ON TABLE server_settings FROM PUBLIC;
419 GRANT SELECT ON TABLE server_settings TO "www-data";
423 -- TOC entry 34 (OID 543754)
424 -- Name: page_emails; Type: TABLE; Schema: public; Owner: rich
427 CREATE TABLE page_emails (
428 hostid integer NOT NULL,
431 entry_date date DEFAULT ('now'::text)::date NOT NULL,
432 last_sent date DEFAULT ('now'::text)::date NOT NULL,
434 opt_out text NOT NULL
439 -- TOC entry 35 (OID 543754)
440 -- Name: page_emails; Type: ACL; Schema: public; Owner: rich
443 REVOKE ALL ON TABLE page_emails FROM PUBLIC;
444 GRANT ALL ON TABLE page_emails TO "www-data";
448 -- TOC entry 36 (OID 543785)
449 -- Name: mailing_lists; Type: TABLE; Schema: public; Owner: rich
452 CREATE TABLE mailing_lists (
453 hostid integer NOT NULL,
455 entry_date date DEFAULT ('now'::text)::date NOT NULL,
457 opt_out text NOT NULL,
463 -- TOC entry 37 (OID 543785)
464 -- Name: mailing_lists; Type: ACL; Schema: public; Owner: rich
467 REVOKE ALL ON TABLE mailing_lists FROM PUBLIC;
468 GRANT ALL ON TABLE mailing_lists TO "www-data";
472 -- TOC entry 49 (OID 536388)
473 -- Name: hostnames_hostid_name_uq; Type: INDEX; Schema: public; Owner: rich
476 CREATE UNIQUE INDEX hostnames_hostid_name_uq ON hostnames USING btree (hostid, name);
480 -- TOC entry 50 (OID 536389)
481 -- Name: hostnams_name_uq; Type: INDEX; Schema: public; Owner: rich
484 CREATE UNIQUE INDEX hostnams_name_uq ON hostnames USING btree (name);
488 -- TOC entry 46 (OID 536419)
489 -- Name: pages_url_uq; Type: INDEX; Schema: public; Owner: rich
492 CREATE UNIQUE INDEX pages_url_uq ON pages USING btree (hostid, url);
496 -- TOC entry 51 (OID 536924)
497 -- Name: email_notify_email_uq; Type: INDEX; Schema: public; Owner: rich
500 CREATE UNIQUE INDEX email_notify_email_uq ON email_notify USING btree (hostid, email);
504 -- TOC entry 52 (OID 540251)
505 -- Name: images_name_uq; Type: INDEX; Schema: public; Owner: rich
508 CREATE UNIQUE INDEX images_name_uq ON images USING btree (hostid, name);
512 -- TOC entry 54 (OID 540252)
513 -- Name: files_name_uq; Type: INDEX; Schema: public; Owner: rich
516 CREATE UNIQUE INDEX files_name_uq ON files USING btree (hostid, name);
520 -- TOC entry 57 (OID 540831)
521 -- Name: users_name_uq; Type: INDEX; Schema: public; Owner: rich
524 CREATE UNIQUE INDEX users_name_uq ON users USING btree (hostid, name);
528 -- TOC entry 56 (OID 540946)
529 -- Name: users_id_uq; Type: INDEX; Schema: public; Owner: rich
532 CREATE UNIQUE INDEX users_id_uq ON users USING btree (hostid, id);
536 -- TOC entry 59 (OID 540970)
537 -- Name: sitemenu_ordering_uq; Type: INDEX; Schema: public; Owner: rich
540 CREATE UNIQUE INDEX sitemenu_ordering_uq ON sitemenu USING btree (hostid, ordering);
544 -- TOC entry 60 (OID 540971)
545 -- Name: sitemenu_url_uq; Type: INDEX; Schema: public; Owner: rich
548 CREATE UNIQUE INDEX sitemenu_url_uq ON sitemenu USING btree (hostid, url);
552 -- TOC entry 63 (OID 542626)
553 -- Name: contact_emails_uq; Type: INDEX; Schema: public; Owner: rich
556 CREATE UNIQUE INDEX contact_emails_uq ON contact_emails USING btree (contactid, email);
560 -- TOC entry 64 (OID 543505)
561 -- Name: themes_theme_css_uq; Type: INDEX; Schema: public; Owner: rich
564 CREATE UNIQUE INDEX themes_theme_css_uq ON themes USING btree (theme_css);
568 -- TOC entry 65 (OID 543763)
569 -- Name: page_emails_email_uq; Type: INDEX; Schema: public; Owner: rich
572 CREATE UNIQUE INDEX page_emails_email_uq ON page_emails USING btree (hostid, url, email);
576 -- TOC entry 66 (OID 543795)
577 -- Name: mailing_lists_email_uq; Type: INDEX; Schema: public; Owner: rich
580 CREATE UNIQUE INDEX mailing_lists_email_uq ON mailing_lists USING btree (hostid, email);
584 -- TOC entry 61 (OID 543880)
585 -- Name: contacts_name_uq; Type: INDEX; Schema: public; Owner: rich
588 CREATE UNIQUE INDEX contacts_name_uq ON contacts USING btree (hostid, name);
592 -- TOC entry 45 (OID 536012)
593 -- Name: pages_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
596 ALTER TABLE ONLY pages
597 ADD CONSTRAINT pages_pkey PRIMARY KEY (id);
601 -- TOC entry 47 (OID 536027)
602 -- Name: contents_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
605 ALTER TABLE ONLY contents
606 ADD CONSTRAINT contents_pkey PRIMARY KEY (id);
610 -- TOC entry 48 (OID 536377)
611 -- Name: hosts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
614 ALTER TABLE ONLY hosts
615 ADD CONSTRAINT hosts_pkey PRIMARY KEY (id);
619 -- TOC entry 53 (OID 537158)
620 -- Name: images_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
623 ALTER TABLE ONLY images
624 ADD CONSTRAINT images_pkey PRIMARY KEY (id);
628 -- TOC entry 55 (OID 537173)
629 -- Name: files_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
632 ALTER TABLE ONLY files
633 ADD CONSTRAINT files_pkey PRIMARY KEY (id);
637 -- TOC entry 58 (OID 540825)
638 -- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
641 ALTER TABLE ONLY users
642 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
646 -- TOC entry 62 (OID 542611)
647 -- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
650 ALTER TABLE ONLY contacts
651 ADD CONSTRAINT contacts_pkey PRIMARY KEY (id);
655 -- TOC entry 71 (OID 536029)
656 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
659 ALTER TABLE ONLY contents
660 ADD CONSTRAINT "$1" FOREIGN KEY (pageid) REFERENCES pages(id);
664 -- TOC entry 74 (OID 536384)
665 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
668 ALTER TABLE ONLY hostnames
669 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
673 -- TOC entry 72 (OID 536394)
674 -- Name: hosts_hostname_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
677 ALTER TABLE ONLY hosts
678 ADD CONSTRAINT hosts_hostname_cn FOREIGN KEY (id, canonical_hostname) REFERENCES hostnames(hostid, name) DEFERRABLE;
682 -- TOC entry 67 (OID 536404)
683 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
686 ALTER TABLE ONLY pages
687 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
691 -- TOC entry 75 (OID 536920)
692 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
695 ALTER TABLE ONLY email_notify
696 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
700 -- TOC entry 76 (OID 537160)
701 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
704 ALTER TABLE ONLY images
705 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
709 -- TOC entry 77 (OID 537175)
710 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
713 ALTER TABLE ONLY files
714 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
718 -- TOC entry 68 (OID 539155)
719 -- Name: pages_redirect_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
722 ALTER TABLE ONLY pages
723 ADD CONSTRAINT pages_redirect_cn FOREIGN KEY (hostid, redirect) REFERENCES pages(hostid, url) DEFERRABLE;
727 -- TOC entry 78 (OID 540827)
728 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
731 ALTER TABLE ONLY users
732 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
736 -- TOC entry 79 (OID 540837)
737 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
740 ALTER TABLE ONLY usercookies
741 ADD CONSTRAINT "$1" FOREIGN KEY (userid) REFERENCES users(id);
745 -- TOC entry 69 (OID 540942)
746 -- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: rich
749 ALTER TABLE ONLY pages
750 ADD CONSTRAINT "$2" FOREIGN KEY (logged_user) REFERENCES users(id);
754 -- TOC entry 70 (OID 540947)
755 -- Name: pages_user_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
758 ALTER TABLE ONLY pages
759 ADD CONSTRAINT pages_user_cn FOREIGN KEY (hostid, logged_user) REFERENCES users(hostid, id);
763 -- TOC entry 80 (OID 540966)
764 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
767 ALTER TABLE ONLY sitemenu
768 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
772 -- TOC entry 81 (OID 540972)
773 -- Name: sitemenu_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
776 ALTER TABLE ONLY sitemenu
777 ADD CONSTRAINT sitemenu_url_cn FOREIGN KEY (hostid, url) REFERENCES pages(hostid, url) DEFERRABLE;
781 -- TOC entry 82 (OID 542613)
782 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
785 ALTER TABLE ONLY contacts
786 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
790 -- TOC entry 83 (OID 542622)
791 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
794 ALTER TABLE ONLY contact_emails
795 ADD CONSTRAINT "$1" FOREIGN KEY (contactid) REFERENCES contacts(id);
799 -- TOC entry 73 (OID 543506)
800 -- Name: hosts_theme_css_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
803 ALTER TABLE ONLY hosts
804 ADD CONSTRAINT hosts_theme_css_cn FOREIGN KEY (theme_css) REFERENCES themes(theme_css);
808 -- TOC entry 84 (OID 543759)
809 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
812 ALTER TABLE ONLY page_emails
813 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
817 -- TOC entry 85 (OID 543764)
818 -- Name: page_emails_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
821 ALTER TABLE ONLY page_emails
822 ADD CONSTRAINT page_emails_url_cn FOREIGN KEY (hostid, url) REFERENCES pages(hostid, url) DEFERRABLE;
826 -- TOC entry 86 (OID 543791)
827 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
830 ALTER TABLE ONLY mailing_lists
831 ADD CONSTRAINT "$1" FOREIGN KEY (hostid) REFERENCES hosts(id);
835 -- TOC entry 3 (OID 2200)
836 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
839 COMMENT ON SCHEMA public IS 'Standard public namespace';
843 -- TOC entry 32 (OID 543592)
844 -- Name: TABLE server_settings; Type: COMMENT; Schema: public; Owner: rich
847 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.';