Last Modified 2009-01-12 by Guest

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.

#!/usr/bin/env 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();


// 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__);




if ($valides==0){

   echo "Can't list users, your auth driver has no listusers capability\n";



echo "$valides valid users found\n";


foreach($validusers as $value){



// Looking at prefs to get most of old user data


$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');



$uid = array();

while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {


    $uid[$login] = $login;



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";





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



#  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""



# horde database


# horde mysql userid


# horde mysql passwd



#  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'"`



mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT datatree_id from $DB.horde_datatree WHERE user_uid='$USER'" > /tmp/01

while read I


  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


  mysql -s -u $SQLUSER --password=$SQLPWD -e "DELETE from $DB.horde_datatree_attributes WHERE datatree_id='$I'"



#  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'"`



mysql -s -u $SQLUSER --password=$SQLPWD -e "SELECT datatree_id from $DB.horde_datatree WHERE user_uid='$USER'" > /tmp/01

while read I


  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`



#  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