diff options
author | thomascube <thomas@roundcube.net> | 2010-04-15 06:33:30 +0000 |
---|---|---|
committer | thomascube <thomas@roundcube.net> | 2010-04-15 06:33:30 +0000 |
commit | a35062a1eba5c6c15f703686cd4fecc5536d74df (patch) | |
tree | bde3c4df3b2cc4073cd6c2b4a6af0f8700f17305 /bin | |
parent | 58892824a6ee7f9a545372730b615ec9abafbe5c (diff) |
Always set changed date when marking a DB record as deleted + provide a cleanup script
Diffstat (limited to 'bin')
-rwxr-xr-x | bin/cleandb.php | 77 |
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"; +} + +?> |