From 28a6896877c6aee0956a22fc8a068a1f3e8eddd9 Mon Sep 17 00:00:00 2001
From: rich <rich>
Date: Mon, 11 Oct 2004 16:55:45 +0000
Subject: [PATCH] Added a tool for deleting all imported mail in the Merjis
 wiki.  (Just for testing, obviously ...)

---
 MANIFEST             |  3 +-
 tools/delete_mail.ml | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 88 insertions(+), 1 deletion(-)
 create mode 100755 tools/delete_mail.ml

diff --git a/MANIFEST b/MANIFEST
index f4b6bf7..e72c258 100644
--- a/MANIFEST
+++ b/MANIFEST
@@ -235,4 +235,5 @@ templates/upload_file_form.html
 templates/upload_image_form.html
 templates/users.html
 templates/visualise_links.html
-templates/what_links_here.html
\ No newline at end of file
+templates/what_links_here.html
+tools/delete_mail.ml
\ No newline at end of file
diff --git a/tools/delete_mail.ml b/tools/delete_mail.ml
new file mode 100755
index 0000000..028390e
--- /dev/null
+++ b/tools/delete_mail.ml
@@ -0,0 +1,86 @@
+#!/usr/bin/ocamlrun /usr/bin/ocaml
+
+#directory "+pcre";;
+#directory "+postgres";;
+#directory "+dbi";;
+#directory "+extlib";;
+
+#load "unix.cma";;
+#load "pcre.cma";;
+#load "postgres.cma";;
+#load "dbi.cma";;
+#load "dbi_postgres.cmo";;
+#load "extLib.cma";;
+
+open Printf
+
+module DB = Dbi_postgres
+
+let hostid = 7
+
+let () =
+  print_endline ("*** warning *** This script deletes all mail from wiki.merjis.com (hostid=" ^ string_of_int hostid ^ ")");
+  print_endline "to continue type 'yes'";
+  let line = read_line () in
+  if line <> "yes" then exit 1
+
+let dbh = new DB.connection "cocanwiki"
+
+let () =
+  let sth = dbh#prepare_cached "select id, subject from messages
+                                 where hostid = ?" in
+  sth#execute [`Int hostid];
+
+  let msgs = sth#map (function [`Int id; `String subject] -> id, subject
+			| _ -> assert false) in
+
+  (* Delete the pages. *)
+  List.iter
+    (fun (msgid, subject) ->
+       let title = sprintf "Mail/%s (%d)" subject msgid in
+       let sth = dbh#prepare_cached "select id, url from pages
+                                      where hostid = ? and title = ?
+                                        and url is not null" in
+       sth#execute [`Int hostid; `String title];
+       let pageid, url =
+	 match sth#fetch1 () with
+	     [ `Int id; `String url ] -> id, url
+	   | _ -> assert false in
+
+       (* This URL might appear in a few other tables.  Look at the
+	* possible constraints:
+	*
+	* pages_redirect_cn (redirect to this page) unlikely
+	* sitemenu_url_cn (in the site menu) no
+	* page_emails_url_cn (email notify) possible, but not likely
+	* links_from_cn (links from) YES
+	* recently_visited_url_cn (recently_visited) VERY LIKELY
+	*)
+       let sth = dbh#prepare_cached "delete from links
+                                      where hostid = ? and from_url = ?" in
+       sth#execute [`Int hostid; `String url];
+       let sth = dbh#prepare_cached "delete from recently_visited
+                                      where hostid = ? and url = ?" in
+       sth#execute [`Int hostid; `String url];
+
+       (* Mark the URL as deleted.  What effect this has on the database
+	* consistency is not really clear, but I think it should be ok.
+	*)
+       let sth = dbh#prepare_cached "update pages set url_deleted = url,
+                                                      url = null
+                                      where hostid = ? and id = ?" in
+       sth#execute [`Int hostid; `Int pageid]
+    ) msgs;
+
+  (* Delete the messages. *)
+  List.iter
+    (fun (msgid, _) ->
+       let sth = dbh#prepare_cached "delete from msg_references
+                                      where message_id = ?" in
+       sth#execute [`Int msgid];
+
+       let sth = dbh#prepare_cached "delete from messages where id = ?" in
+       sth#execute [`Int msgid]
+    ) msgs;
+
+  dbh#commit ()
-- 
1.8.3.1