Added the navigation box (see email).

[cocanwiki.git] / cocanwiki.sql

diff --git a/cocanwiki.sql b/cocanwiki.sql
index 15a09af..6a96318 100644 (file)
--- a/cocanwiki.sql
+++ b/cocanwiki.sql
@@ -48,7 +48,7 @@ GRANT ALL ON TABLE pages TO "www-data";
 
 
 --
--- TOC entry 39 (OID 536004)
+-- TOC entry 44 (OID 536004)
 -- Name: pages_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -81,7 +81,7 @@ GRANT ALL ON TABLE contents TO "www-data";
 
 
 --
--- TOC entry 40 (OID 536021)
+-- TOC entry 45 (OID 536021)
 -- Name: contents_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -104,7 +104,9 @@ CREATE TABLE hosts (
     feedback_email text,
     mailing_list boolean DEFAULT false NOT NULL,
     is_template boolean DEFAULT false NOT NULL,
-    search_box boolean DEFAULT true NOT NULL
+    search_box boolean DEFAULT true NOT NULL,
+    view_anon boolean DEFAULT true NOT NULL,
+    navigation boolean DEFAULT false NOT NULL
 );
 
 
@@ -118,7 +120,7 @@ GRANT ALL ON TABLE hosts TO "www-data";
 
 
 --
--- TOC entry 41 (OID 536371)
+-- TOC entry 46 (OID 536371)
 -- Name: hosts_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -203,7 +205,7 @@ GRANT ALL ON TABLE images TO "www-data";
 
 
 --
--- TOC entry 42 (OID 537151)
+-- TOC entry 47 (OID 537151)
 -- Name: images_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -238,7 +240,7 @@ GRANT ALL ON TABLE files TO "www-data";
 
 
 --
--- TOC entry 43 (OID 537166)
+-- TOC entry 48 (OID 537166)
 -- Name: files_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -277,7 +279,7 @@ GRANT ALL ON TABLE users TO "www-data";
 
 
 --
--- TOC entry 44 (OID 540816)
+-- TOC entry 49 (OID 540816)
 -- Name: users_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -350,7 +352,7 @@ GRANT ALL ON TABLE contacts TO "www-data";
 
 
 --
--- TOC entry 45 (OID 542605)
+-- TOC entry 50 (OID 542605)
 -- Name: contacts_id_seq; Type: ACL; Schema: public; Owner: rich
 --
 
@@ -406,7 +408,8 @@ GRANT SELECT ON TABLE themes TO "www-data";
 
 CREATE TABLE server_settings (
     "version" integer NOT NULL,
-    stats_page text
+    stats_page text,
+    crash_email text
 );
 
 
@@ -476,12 +479,75 @@ GRANT ALL ON TABLE mailing_lists TO "www-data";
 CREATE TABLE links (
     hostid integer NOT NULL,
     from_url text NOT NULL,
-    to_url text NOT NULL
+    to_url text NOT NULL,
+    CONSTRAINT links_not_selfref_cn CHECK ((from_url <> to_url))
 );
 
 
 --
--- TOC entry 50 (OID 536388)
+-- TOC entry 39 (OID 544445)
+-- Name: links; Type: ACL; Schema: public; Owner: rich
+--
+
+REVOKE ALL ON TABLE links FROM PUBLIC;
+GRANT ALL ON TABLE links TO "www-data";
+
+
+--
+-- TOC entry 40 (OID 547939)
+-- Name: templates; Type: TABLE; Schema: public; Owner: rich
+--
+
+CREATE TABLE templates (
+    id serial NOT NULL,
+    title_regexp text NOT NULL,
+    url_regexp text NOT NULL,
+    extension text NOT NULL,
+    ordering integer NOT NULL
+);
+
+
+--
+-- TOC entry 41 (OID 547939)
+-- Name: templates; Type: ACL; Schema: public; Owner: rich
+--
+
+REVOKE ALL ON TABLE templates FROM PUBLIC;
+GRANT SELECT ON TABLE templates TO "www-data";
+
+
+--
+-- TOC entry 51 (OID 547939)
+-- Name: templates_id_seq; Type: ACL; Schema: public; Owner: rich
+--
+
+REVOKE ALL ON TABLE templates_id_seq FROM PUBLIC;
+
+
+--
+-- TOC entry 42 (OID 551123)
+-- Name: recently_visited; Type: TABLE; Schema: public; Owner: rich
+--
+
+CREATE TABLE recently_visited (
+    userid integer NOT NULL,
+    hostid integer NOT NULL,
+    url text NOT NULL,
+    visit_time timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
+);
+
+
+--
+-- TOC entry 43 (OID 551123)
+-- Name: recently_visited; Type: ACL; Schema: public; Owner: rich
+--
+
+REVOKE ALL ON TABLE recently_visited FROM PUBLIC;
+GRANT ALL ON TABLE recently_visited TO "www-data";
+
+
+--
+-- TOC entry 56 (OID 536388)
 -- Name: hostnames_hostid_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -489,7 +555,7 @@ CREATE UNIQUE INDEX hostnames_hostid_name_uq ON hostnames USING btree (hostid, n
 
 
 --
--- TOC entry 51 (OID 536389)
+-- TOC entry 57 (OID 536389)
 -- Name: hostnams_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -497,7 +563,7 @@ CREATE UNIQUE INDEX hostnams_name_uq ON hostnames USING btree (name);
 
 
 --
--- TOC entry 47 (OID 536419)
+-- TOC entry 53 (OID 536419)
 -- Name: pages_url_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -505,7 +571,7 @@ CREATE UNIQUE INDEX pages_url_uq ON pages USING btree (hostid, url);
 
 
 --
--- TOC entry 52 (OID 536924)
+-- TOC entry 58 (OID 536924)
 -- Name: email_notify_email_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -513,7 +579,7 @@ CREATE UNIQUE INDEX email_notify_email_uq ON email_notify USING btree (hostid, e
 
 
 --
--- TOC entry 53 (OID 540251)
+-- TOC entry 59 (OID 540251)
 -- Name: images_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -521,7 +587,7 @@ CREATE UNIQUE INDEX images_name_uq ON images USING btree (hostid, name);
 
 
 --
--- TOC entry 55 (OID 540252)
+-- TOC entry 61 (OID 540252)
 -- Name: files_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -529,7 +595,7 @@ CREATE UNIQUE INDEX files_name_uq ON files USING btree (hostid, name);
 
 
 --
--- TOC entry 58 (OID 540831)
+-- TOC entry 64 (OID 540831)
 -- Name: users_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -537,7 +603,7 @@ CREATE UNIQUE INDEX users_name_uq ON users USING btree (hostid, name);
 
 
 --
--- TOC entry 57 (OID 540946)
+-- TOC entry 63 (OID 540946)
 -- Name: users_id_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -545,7 +611,7 @@ CREATE UNIQUE INDEX users_id_uq ON users USING btree (hostid, id);
 
 
 --
--- TOC entry 60 (OID 540970)
+-- TOC entry 66 (OID 540970)
 -- Name: sitemenu_ordering_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -553,7 +619,7 @@ CREATE UNIQUE INDEX sitemenu_ordering_uq ON sitemenu USING btree (hostid, orderi
 
 
 --
--- TOC entry 61 (OID 540971)
+-- TOC entry 67 (OID 540971)
 -- Name: sitemenu_url_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -561,7 +627,7 @@ CREATE UNIQUE INDEX sitemenu_url_uq ON sitemenu USING btree (hostid, url);
 
 
 --
--- TOC entry 64 (OID 542626)
+-- TOC entry 70 (OID 542626)
 -- Name: contact_emails_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -569,7 +635,7 @@ CREATE UNIQUE INDEX contact_emails_uq ON contact_emails USING btree (contactid,
 
 
 --
--- TOC entry 65 (OID 543505)
+-- TOC entry 71 (OID 543505)
 -- Name: themes_theme_css_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -577,7 +643,7 @@ CREATE UNIQUE INDEX themes_theme_css_uq ON themes USING btree (theme_css);
 
 
 --
--- TOC entry 66 (OID 543763)
+-- TOC entry 72 (OID 543763)
 -- Name: page_emails_email_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -585,7 +651,7 @@ CREATE UNIQUE INDEX page_emails_email_uq ON page_emails USING btree (hostid, url
 
 
 --
--- TOC entry 67 (OID 543795)
+-- TOC entry 73 (OID 543795)
 -- Name: mailing_lists_email_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -593,7 +659,7 @@ CREATE UNIQUE INDEX mailing_lists_email_uq ON mailing_lists USING btree (hostid,
 
 
 --
--- TOC entry 62 (OID 543880)
+-- TOC entry 68 (OID 543880)
 -- Name: contacts_name_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -601,7 +667,7 @@ CREATE UNIQUE INDEX contacts_name_uq ON contacts USING btree (hostid, name);
 
 
 --
--- TOC entry 68 (OID 544454)
+-- TOC entry 74 (OID 544454)
 -- Name: links_uq; Type: INDEX; Schema: public; Owner: rich
 --
 
@@ -609,7 +675,39 @@ CREATE UNIQUE INDEX links_uq ON links USING btree (hostid, from_url, to_url);
 
 
 --
--- TOC entry 46 (OID 536012)
+-- TOC entry 75 (OID 547951)
+-- Name: templates_ext_ord_uq; Type: INDEX; Schema: public; Owner: rich
+--
+
+CREATE UNIQUE INDEX templates_ext_ord_uq ON templates USING btree (extension, ordering);
+
+
+--
+-- TOC entry 77 (OID 547952)
+-- Name: templates_title_uq; Type: INDEX; Schema: public; Owner: rich
+--
+
+CREATE UNIQUE INDEX templates_title_uq ON templates USING btree (title_regexp);
+
+
+--
+-- TOC entry 78 (OID 547953)
+-- Name: templates_url_uq; Type: INDEX; Schema: public; Owner: rich
+--
+
+CREATE UNIQUE INDEX templates_url_uq ON templates USING btree (url_regexp);
+
+
+--
+-- TOC entry 79 (OID 551151)
+-- Name: recently_visited_uq; Type: INDEX; Schema: public; Owner: rich
+--
+
+CREATE UNIQUE INDEX recently_visited_uq ON recently_visited USING btree (userid, hostid, url);
+
+
+--
+-- TOC entry 52 (OID 536012)
 -- Name: pages_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -618,7 +716,7 @@ ALTER TABLE ONLY pages
 
 
 --
--- TOC entry 48 (OID 536027)
+-- TOC entry 54 (OID 536027)
 -- Name: contents_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -627,7 +725,7 @@ ALTER TABLE ONLY contents
 
 
 --
--- TOC entry 49 (OID 536377)
+-- TOC entry 55 (OID 536377)
 -- Name: hosts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -636,7 +734,7 @@ ALTER TABLE ONLY hosts
 
 
 --
--- TOC entry 54 (OID 537158)
+-- TOC entry 60 (OID 537158)
 -- Name: images_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -645,7 +743,7 @@ ALTER TABLE ONLY images
 
 
 --
--- TOC entry 56 (OID 537173)
+-- TOC entry 62 (OID 537173)
 -- Name: files_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -654,7 +752,7 @@ ALTER TABLE ONLY files
 
 
 --
--- TOC entry 59 (OID 540825)
+-- TOC entry 65 (OID 540825)
 -- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -663,7 +761,7 @@ ALTER TABLE ONLY users
 
 
 --
--- TOC entry 63 (OID 542611)
+-- TOC entry 69 (OID 542611)
 -- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -672,7 +770,16 @@ ALTER TABLE ONLY contacts
 
 
 --
--- TOC entry 73 (OID 536029)
+-- TOC entry 76 (OID 547945)
+-- Name: templates_pkey; Type: CONSTRAINT; Schema: public; Owner: rich
+--
+
+ALTER TABLE ONLY templates
+    ADD CONSTRAINT templates_pkey PRIMARY KEY (id);
+
+
+--
+-- TOC entry 84 (OID 536029)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -681,7 +788,7 @@ ALTER TABLE ONLY contents
 
 
 --
--- TOC entry 76 (OID 536384)
+-- TOC entry 87 (OID 536384)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -690,7 +797,7 @@ ALTER TABLE ONLY hostnames
 
 
 --
--- TOC entry 74 (OID 536394)
+-- TOC entry 85 (OID 536394)
 -- Name: hosts_hostname_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -699,7 +806,7 @@ ALTER TABLE ONLY hosts
 
 
 --
--- TOC entry 69 (OID 536404)
+-- TOC entry 80 (OID 536404)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -708,7 +815,7 @@ ALTER TABLE ONLY pages
 
 
 --
--- TOC entry 77 (OID 536920)
+-- TOC entry 88 (OID 536920)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -717,7 +824,7 @@ ALTER TABLE ONLY email_notify
 
 
 --
--- TOC entry 78 (OID 537160)
+-- TOC entry 89 (OID 537160)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -726,7 +833,7 @@ ALTER TABLE ONLY images
 
 
 --
--- TOC entry 79 (OID 537175)
+-- TOC entry 90 (OID 537175)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -735,7 +842,7 @@ ALTER TABLE ONLY files
 
 
 --
--- TOC entry 70 (OID 539155)
+-- TOC entry 81 (OID 539155)
 -- Name: pages_redirect_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -744,7 +851,7 @@ ALTER TABLE ONLY pages
 
 
 --
--- TOC entry 80 (OID 540827)
+-- TOC entry 91 (OID 540827)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -753,7 +860,7 @@ ALTER TABLE ONLY users
 
 
 --
--- TOC entry 81 (OID 540837)
+-- TOC entry 92 (OID 540837)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -762,7 +869,7 @@ ALTER TABLE ONLY usercookies
 
 
 --
--- TOC entry 71 (OID 540942)
+-- TOC entry 82 (OID 540942)
 -- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -771,7 +878,7 @@ ALTER TABLE ONLY pages
 
 
 --
--- TOC entry 72 (OID 540947)
+-- TOC entry 83 (OID 540947)
 -- Name: pages_user_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -780,7 +887,7 @@ ALTER TABLE ONLY pages
 
 
 --
--- TOC entry 82 (OID 540966)
+-- TOC entry 93 (OID 540966)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -789,7 +896,7 @@ ALTER TABLE ONLY sitemenu
 
 
 --
--- TOC entry 83 (OID 540972)
+-- TOC entry 94 (OID 540972)
 -- Name: sitemenu_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -798,7 +905,7 @@ ALTER TABLE ONLY sitemenu
 
 
 --
--- TOC entry 84 (OID 542613)
+-- TOC entry 95 (OID 542613)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -807,7 +914,7 @@ ALTER TABLE ONLY contacts
 
 
 --
--- TOC entry 85 (OID 542622)
+-- TOC entry 96 (OID 542622)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -816,7 +923,7 @@ ALTER TABLE ONLY contact_emails
 
 
 --
--- TOC entry 75 (OID 543506)
+-- TOC entry 86 (OID 543506)
 -- Name: hosts_theme_css_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -825,7 +932,7 @@ ALTER TABLE ONLY hosts
 
 
 --
--- TOC entry 86 (OID 543759)
+-- TOC entry 97 (OID 543759)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -834,7 +941,7 @@ ALTER TABLE ONLY page_emails
 
 
 --
--- TOC entry 87 (OID 543764)
+-- TOC entry 98 (OID 543764)
 -- Name: page_emails_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -843,7 +950,7 @@ ALTER TABLE ONLY page_emails
 
 
 --
--- TOC entry 88 (OID 543791)
+-- TOC entry 99 (OID 543791)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -852,7 +959,7 @@ ALTER TABLE ONLY mailing_lists
 
 
 --
--- TOC entry 89 (OID 544450)
+-- TOC entry 100 (OID 544450)
 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -861,7 +968,7 @@ ALTER TABLE ONLY links
 
 
 --
--- TOC entry 90 (OID 544455)
+-- TOC entry 101 (OID 544455)
 -- Name: links_from_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
@@ -870,12 +977,39 @@ ALTER TABLE ONLY links
 
 
 --
--- TOC entry 91 (OID 544459)
--- Name: links_to_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
+-- TOC entry 102 (OID 551129)
+-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: rich
 --
 
-ALTER TABLE ONLY links
-    ADD CONSTRAINT links_to_cn FOREIGN KEY (hostid, to_url) REFERENCES pages(hostid, url) DEFERRABLE;
+ALTER TABLE ONLY recently_visited
+    ADD CONSTRAINT "$1" FOREIGN KEY (userid) REFERENCES users(id);
+
+
+--
+-- TOC entry 103 (OID 551133)
+-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: rich
+--
+
+ALTER TABLE ONLY recently_visited
+    ADD CONSTRAINT "$2" FOREIGN KEY (hostid) REFERENCES hosts(id);
+
+
+--
+-- TOC entry 104 (OID 551137)
+-- Name: recently_visited_url_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
+--
+
+ALTER TABLE ONLY recently_visited
+    ADD CONSTRAINT recently_visited_url_cn FOREIGN KEY (hostid, url) REFERENCES pages(hostid, url) DEFERRABLE;
+
+
+--
+-- TOC entry 105 (OID 551141)
+-- Name: recently_visited_userid_cn; Type: FK CONSTRAINT; Schema: public; Owner: rich
+--
+
+ALTER TABLE ONLY recently_visited
+    ADD CONSTRAINT recently_visited_userid_cn FOREIGN KEY (hostid, userid) REFERENCES users(hostid, id);
 
 
 --
@@ -894,3 +1028,51 @@ COMMENT ON SCHEMA public IS 'Standard public namespace';
 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.';
 
 
+--
+-- PostgreSQL database dump
+--
+
+SET client_encoding = 'UNICODE';
+SET check_function_bodies = false;
+
+SET search_path = public, pg_catalog;
+
+--
+-- Data for TOC entry 3 (OID 547939)
+-- Name: templates; Type: TABLE DATA; Schema: public; Owner: rich
+--
+
+COPY templates (id, title_regexp, url_regexp, extension, ordering) FROM stdin;
+1      ^(199[0-9]|20[0-9][0-9])/(0[1-9]|1[0-2])/(0[1-9]|1[0-9]|2[0-9]|3[01])$  ^(199[0-9]|20[0-9][0-9])/(0[1-9]|1[0-2])/(0[1-9]|1[0-9]|2[0-9]|3[01])$  calendar        10
+3      ^(199[0-9]|20[0-9][0-9])/(0[1-9]|1[0-2])$       ^(199[0-9]|20[0-9][0-9])/(0[1-9]|1[0-2])$       calendar        20
+4      ^(199[0-9]|20[0-9][0-9])$       ^(199[0-9]|20[0-9][0-9])$       calendar        30
+\.
+
+
+--
+-- TOC entry 2 (OID 547937)
+-- Name: templates_id_seq; Type: SEQUENCE SET; Schema: public; Owner: rich
+--
+
+SELECT pg_catalog.setval('templates_id_seq', 4, true);
+
+
+--
+-- PostgreSQL database dump
+--
+
+SET client_encoding = 'UNICODE';
+SET check_function_bodies = false;
+
+SET search_path = public, pg_catalog;
+
+--
+-- Data for TOC entry 2 (OID 543499)
+-- Name: themes; Type: TABLE DATA; Schema: public; Owner: rich
+--
+
+COPY themes (theme_css, name, description) FROM stdin;
+/_css/easyweb.css      Merjis Easy Web Marketing       This is the easy web marketing stylesheet developed by Merjis Ltd.  Please see http://www.merjis.com/
+\.
+
+