January 12th 2009
Another script in PHP using API removeUserData dom.lalot à gmail.com
Sorry for my poor coding, I'm a Perl programmer not a PHP one.
<code>
#!/usr/bin/env php
<?php
@define('AUTH_HANDLER', true);
@define('HORDE_BASE', dirname(__FILE__));
# require_once HORDE_BASE . '/lib/base.php';
// Do CLI checks and environment setup first.
require_once HORDE_BASE . '/lib/core.php';
require_once 'Horde/CLI.php';
// Make sure no one runs this from the web.
if (!Horde_CLI::runningFromCLI()) {
exit("Must be run from the command line\n");
}
// Load the CLI environment - make sure there's no time limit, init some
// variables, etc.
$cli = &Horde_CLI::singleton();
$cli->init();
// Include needed libraries.
require_once HORDE_BASE . '/lib/base.php';
// Authenticate as administrator.
if (!count($conf['auth']['admins'])) {
exit("You must have at least one administrator configured to run the alarms.php script.\n");
}
$auth = &Auth::singleton($conf['auth']['driver']);
$auth->setAuth($conf['auth']['admins'][0], array());
require_once HORDE_BASE . '/lib/Horde/Auth.php';
global $conf;
require_once 'DB.php';
$db = &DB::connect($conf['sql']);
if (is_a($db, 'PEAR_Error')) {
Horde::fatal($db, __FILE__, __LINE__);
}
$validusers=$auth->listUsers();
$valides=count($validusers);
if ($valides==0){
echo "Can't list users, your auth driver has no listusers capability\n";
exit;
}
echo "$valides valid users found\n";
$valides=array();
foreach($validusers as $value){
$valides[$value]=1;
}
// Looking at prefs to get most of old user data
$db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS);
$result = $db->query('SELECT pref_uid FROM horde_prefs WHERE pref_uid like \'%\'');
if (is_a($result, 'PEAR_Error')) {
$cli->message($result->toString(), 'cli.error');
exit;
}
$uid = array();
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$login=$row['pref_uid'];
$uid[$login] = $login;
}
$total=count($uid);
echo "$total data user found\n";
exit; # Drop this line if you want to remove data
//now compare valid and data found and purge
foreach ($uid as $value) {
if (!array_key_exists($value,$valides)){
echo "Not Found $value\n";
Auth::removeUserData($value);
$supp++;
}
}
echo "suppressed $supp\n";
?>
</code>
Here is a database cleanup script that was first posted on the Horde list by Jacques Beaudoin <jacques-beaudoin@cspi.qc.ca>
-
I changed it a little.
It really should be PHP-ized and get the database information from the configs
I think it needs the history database in it - for horde 3.1 and later
--------- hordedel
<code>
#!/bin/bash
#
# Origional From Beaudoin <jacques-beaudoin@cspi.qc.ca>
# made it a little more flexible Bill Graham <grahamcw@hurleybulldogs.com>
#
# hordedel
# cleanup of horde databases after user deletion.
#
# Exemple usage hordedel "useridname""
#
USER="$1"
# horde database
DB="yourhordedatabase"
# horde mysql userid
SQLUSER="yourhordeuserid"
# horde mysql passwd
SQLPWD="yourdatabasepassword"
#
# Count records before delete
#
HP_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_prefs WHERE pref_uid='$USER'"`
KE_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.kronolith_events WHERE calendar_id='$USER'"`
MM_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.mnemo_memos WHERE memo_owner='$USER'"`
TO_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.turba_objects WHERE owner_id='$USER'"`
NT_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.nag_tasks WHERE task_owner='$USER'"`
HD_B=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_datatree WHERE user_uid='$USER'"`
HDA_B=0
COUNT=0
mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT datatree_id from $DB.horde_datatree WHERE user_uid='$USER'" > /tmp/01
while read I
do
COUNT=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_datatree_attributes WHERE datatree_id='$I'"`
HDA_B=`expr $HDA_B + $COUNT`
done </tmp/01
#
# Delete records (Comments out these lines for testing)
#
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.horde_prefs WHERE pref_uid='$USER'"
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.kronolith_events WHERE calendar_id='$USER'"
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.mnemo_memos WHERE memo_owner='$USER'"
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.turba_objects WHERE owner_id='$USER'"
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.nag_tasks WHERE task_owner='$USER'"
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.horde_datatree WHERE user_uid='$USER'"
while read I
do
mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.horde_datatree_attributes WHERE datatree_id='$I'"
done</tmp/01
#
# Count records after delete (Should always give zero)
#
HP_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_prefs WHERE pref_uid='$USER'"`
KE_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.kronolith_events WHERE calendar_id='$USER'"`
MM_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.mnemo_memos WHERE memo_owner='$USER'"`
TO_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.turba_objects WHERE owner_id='$USER'"`
NT_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.nag_tasks WHERE task_owner='$USER'"`
HD_A=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_datatree WHERE user_uid='$USER'"`
HDA_A=0
COUNT=0
mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT datatree_id from $DB.horde_datatree WHERE user_uid='$USER'" > /tmp/01
while read I
do
COUNT=`mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT count(*) from $DB.horde_datatree_attributes WHERE datatree_id='$I'"`
HDA_A=`expr $HDA_A + $COUNT`
done</tmp/01
#
# Display tables record counts after delete operation
#
echo "Before and after records count for user $USER'"
echo "==========================================================================="
echo "horde_prefs: $HP_B / $HP_A"
echo "kronolith_events: $KE_B / $KE_A"
echo "mnemo_memos: $MM_B / $MM_A"
echo "turba_objects: $TO_B / $TO_A"
echo "nag_tasks: $NT_B / $NT_A"
echo "horde_datatree: $HD_B / $HD_A"
echo "horde_datatree_attributes: $HDA_B / $HDA_A"
exit 0
</code>