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
<?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 linen");
}
// 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 capabilityn";
exit;
}
echo "$valides valid users foundn";
$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 foundn";
echo "Exit without removing data, change the script pleasen";
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 $valuen";
Auth::removeUserData($value);
$supp++;
}
}
echo "suppressed $suppn";
?>
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