January 12th 2009
Another script in PHP using API removeUserData from dom.lalot à gmail.com
Sorry for my poor coding, I'm a Perl programmer not a PHP one. The purpose of this script is to remove data from non existent users. Your auth driver should be able to listusers. The prefs are analyzed to get all current entries by checking uid. Then we compare to the list of all current users.
You have to change the script to get it working. There an exit before doing the bad job.
Save you data if you have some fears.
#!/usr/bin/env php
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 1');
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";
echo "Exit without removing data, change the script please\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";
?>
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
#!/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