1 #!/usr/bin/ocamlrun /usr/bin/ocaml
4 #directory "+postgres";;
10 #load "postgres.cma";;
12 #load "dbi_postgres.cmo";;
17 module DB = Dbi_postgres
22 print_endline ("*** warning *** This script deletes all mail from wiki.merjis.com (hostid=" ^ string_of_int hostid ^ ")");
23 print_endline "to continue type 'yes'";
24 let line = read_line () in
25 if line <> "yes" then exit 1
27 let dbh = new DB.connection "cocanwiki"
30 let sth = dbh#prepare_cached "select id, subject from messages
32 sth#execute [`Int hostid];
34 let msgs = sth#map (function [`Int id; `String subject] -> id, subject
35 | _ -> assert false) in
37 (* Delete the pages. *)
39 (fun (msgid, subject) ->
40 let title = sprintf "Mail/%s (%d)" subject msgid in
41 let sth = dbh#prepare_cached "select id, url from pages
42 where hostid = ? and title = ?
43 and url is not null" in
44 sth#execute [`Int hostid; `String title];
46 match sth#fetch1 () with
47 [ `Int id; `String url ] -> id, url
48 | _ -> assert false in
50 (* This URL might appear in a few other tables. Look at the
51 * possible constraints:
53 * pages_redirect_cn (redirect to this page) unlikely
54 * sitemenu_url_cn (in the site menu) no
55 * page_emails_url_cn (email notify) possible, but not likely
56 * links_from_cn (links from) YES
57 * recently_visited_url_cn (recently_visited) VERY LIKELY
59 let sth = dbh#prepare_cached "delete from links
60 where hostid = ? and from_url = ?" in
61 sth#execute [`Int hostid; `String url];
62 let sth = dbh#prepare_cached "delete from recently_visited
63 where hostid = ? and url = ?" in
64 sth#execute [`Int hostid; `String url];
66 (* Mark the URL as deleted. What effect this has on the database
67 * consistency is not really clear, but I think it should be ok.
69 let sth = dbh#prepare_cached "update pages set url_deleted = url,
71 where hostid = ? and id = ?" in
72 sth#execute [`Int hostid; `Int pageid]
75 (* Delete the messages. *)
78 let sth = dbh#prepare_cached "delete from msg_references
79 where message_id = ?" in
80 sth#execute [`Int msgid];
82 let sth = dbh#prepare_cached "delete from messages where id = ?" in
83 sth#execute [`Int msgid]