summaryrefslogtreecommitdiff
path: root/bin
diff options
context:
space:
mode:
authorthomascube <thomas@roundcube.net>2010-04-15 06:33:30 +0000
committerthomascube <thomas@roundcube.net>2010-04-15 06:33:30 +0000
commita35062a1eba5c6c15f703686cd4fecc5536d74df (patch)
treebde3c4df3b2cc4073cd6c2b4a6af0f8700f17305 /bin
parent58892824a6ee7f9a545372730b615ec9abafbe5c (diff)
Always set changed date when marking a DB record as deleted + provide a cleanup script
Diffstat (limited to 'bin')
-rwxr-xr-xbin/cleandb.php77
1 files changed, 77 insertions, 0 deletions
diff --git a/bin/cleandb.php b/bin/cleandb.php
new file mode 100755
index 000000000..c40a1cd6b
--- /dev/null
+++ b/bin/cleandb.php
@@ -0,0 +1,77 @@
+#!/usr/bin/env php
+<?php
+/*
+
+ +-----------------------------------------------------------------------+
+ | bin/cleandb.php |
+ | |
+ | This file is part of the RoundCube Webmail client |
+ | Copyright (C) 2010, RoundCube Dev. - Switzerland |
+ | Licensed under the GNU GPL |
+ | |
+ | PURPOSE: |
+ | Finally remove all db records marked as deleted some time ago |
+ | |
+ +-----------------------------------------------------------------------+
+ | Author: Thomas Bruederli <roundcube@gmail.com> |
+ +-----------------------------------------------------------------------+
+
+ $Id$
+
+*/
+
+if (php_sapi_name() != 'cli') {
+ die('Not on the "shell" (php-cli).');
+}
+
+define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
+require INSTALL_PATH.'program/include/iniset.php';
+
+// mapping for table name => primary key
+$primary_keys = array(
+ 'contacts' => "contact_id",
+ 'contactgroups' => "contactgroup_id",
+);
+
+// connect to DB
+$RCMAIL = rcmail::get_instance();
+$db = $RCMAIL->get_dbh();
+
+if (!$db->is_connected() || $db->is_error)
+ die("No DB connection");
+
+// remove all deleted records older than two days
+$threshold = date('Y-m-d 00:00:00', time() - 2 * 86400);
+
+foreach (array('contacts','contactgroups','identities') as $table) {
+ // also delete linked records
+ // could be skipped for databases which respect foreign key constraints
+ if ($table == 'contacts' || $table == 'contactgroups') {
+ $ids = array();
+ $pk = $primary_keys[$table];
+
+ $result = $db->query(
+ "SELECT $pk FROM ".get_table_name($table)."
+ WHERE del=1 AND changed < ".$db->quote($threshold));
+
+ while ($result && ($sql_arr = $db->fetch_assoc($result)))
+ $ids[] = $sql_arr[$pk];
+
+ if (count($ids)) {
+ $db->query(
+ "DELETE FROM ".get_table_name('contactgroupmembers')."
+ WHERE $pk IN (".join(',', $ids).")");
+
+ echo $db->affected_rows() . " records deleted from '".get_table_name('contactgroupmembers')."'\n";
+ }
+ }
+
+ // delete outdated records
+ $db->query(
+ "DELETE FROM ".get_table_name($table)."
+ WHERE del=1 AND changed < ".$db->quote($threshold));
+
+ echo $db->affected_rows() . " records deleted from '$table'\n";
+}
+
+?>