6.0.0-git
2024-03-19
Last Modified 2013-09-16 by Guest

Syntax: horde_cleanup.php [-vf] <user>
<user> can be regex

#!/usr/bin/env php
<?php

function verbose($msg, $type = 'cli.message')
{
  if (!$GLOBALS['verbose'])
    return false;
  $prefix = (!empty($GLOBALS['vprefix'])) ? $GLOBALS['vprefix'] . ' ' : '';
  return $GLOBALS['cli']->message($prefix . $msg, $type);
}

# initialize horde
require_once 'PEAR/Config.php';
$baseDir = PEAR_Config::singleton()->get('horde_dir', null, 'pear.horde.org');

# initialize kronolith instead of horde as we need the tagger instance
require_once $baseDir . '/kronolith/lib/Application.php';
Horde_Registry::appInit('kronolith', array(
  'cli' => true,
  'authentication' => 'none',
  'user_admin' => true
));

require_once $baseDir . '/content/lib/Application.php';
Horde_Registry::appInit('content', array(
  'cli' => true,
  'user_admin' => true
));
require CONTENT_BASE . '/lib/Tags/Tag.php';
require CONTENT_BASE . '/lib/Tags/TagMapper.php';
require CONTENT_BASE . '/lib/Objects/ObjectMapper.php';

# parse commandline options
$c = new Console_Getopt();
$argv = $c->readPHPArgv();
array_shift($argv);
if (empty($argv) || empty($argv[0]))
{
  $cli->writeln(
    $cli->bold('Syntax: ') .
    $cli->blue($_SERVER['argv'][0]) .
    $cli->green(' [-vf]') .
    $cli->red(' <user>')
  );
  exit(1);
}
$options = $c->getopt2($argv, 'fv');
if (PEAR::isError($options))
  $cli->fatal($options->getMessage());
$user      = $options[1][0];
$regexp    = '^'.$user.'$';
$doremove  = false;
$GLOBALS['verbose'] = false;
foreach ($options[0] as $val)
{
  switch($val[0])
  {
    case 'f':
      $doremove = true;
      break;
    case 'v':
      $GLOBALS['verbose'] = true;
      break;
  }
}

if (!$doremove)
{
  $cli->message('DO_NOT_REMOVE-flag is set!', 'cli.warning');
  if (!$GLOBALS['verbose'])
  {
    $cli->message('Enabling verbose mode...');
    $GLOBALS['verbose'] = true;
  }
}

# connect to database
try
{
  $db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_Db')->create('horde', 'removeUser');
}
catch(Horde_Db_Exception $e)
{
  $cli->fatal('Unable to connect to the database: ' . $e);
}

# fetch users from horde_prefs
try
{
  $result = $db->selectValues(
    'SELECT ' . $db->distinct('pref_uid') . ' ' .
    'FROM horde_prefs');
  $users = array();
  foreach($result as $u)
  {
    if (preg_match('/'.$regexp.'/', $u))
      $users[] = $u;
  }
}
catch (Horde_Db_Exception $e)
{
  $cli->fatal('Unable to select from table \'horde_prefs\': ' . $e);
}

if (empty($users))
{
  verbose('No users with syntax "' . $regexp . '" found (no prefs exist)', 'cli.error');
  exit(0);
}

# fetch and check active sessions
try
{
  $sessionHandler = $GLOBALS['injector']->createInstance('Horde_SessionHandler');
  $sessions = $sessionHandler->getSessionsInfo();
  $loggedin = array();
  foreach ($sessions as $sid => $data)
  {
    if (preg_match('/'.$regexp.'/', $data['userid']))
    {
      # we're unable to delete sessions from phps builtin sessionhandler
      #$session->sessionHandler->destroy($sid);
      $loggedin[$data['userid']] = true;
    }
  }
}
catch(Horde_SessionHandler_Exception $e)
{
  $cli->fatal('Session counting is not supported with the current session handler.');
}
if (!empty($loggedin))
  $cli->fatal('The following users cannot be removed as they are currently logged in: ' . join(' ', array_keys($loggedin)));

# list users which will be removed
verbose('I\'ll delete the following users:');
foreach($users as $u)
  verbose('  ' . $u);

# get tagger
$tagger = $GLOBALS['injector']->getInstance('Content_Tagger');

# finally remove the data
# NOTE: we're unable to remove every object references especially in rampage
# thus we take care of that in a separate cron: cleanup_rampage.php
if ($doremove)
{
  foreach($users as $user)
  {
    $cli->message(str_repeat('-', 30), 'cli.success');
    $cli->message('Processing ' . $user . '...', 'cli.success');

    # remove tags
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ Content ]');
      $tUserId = current($GLOBALS['injector']->getInstance('Content_Users_Manager')->ensureUsers($user));
      $tTags = $tagger->getTags(array('userId' => $tUserId));
      foreach($tTags as $tTagId => $tTag)
      {
        $tObjects = $tagger->getObjects(array('tagId' => $tTagId, 'userId' => $tUserId));
        foreach($tObjects as $tObjectId => $tObject)
        {
          verbose(sprintf('Removing tag "%s/%d" from object "%s/#%d"...', $tTag, $tTagId, $tObject, $tObjectId), 'cli.none');
          $tagger->untag($tUserId, $tObjectId, $tTagId);

          # housekeeping: check if objects are used anymore
          verbose(sprintf('Removing object "%s/#%d"...', $tObject, $tObjectId), 'cli.none');
          $mapper = new Content_ObjectMapper($injector->getInstance('Horde_Db_Adapter'));
          $mapper->delete($tObjectId);
        }

        # housekeeping: check if tags are used anymore
        $tObjects = $tagger->getObjects(array('tagId' => $tTagId));
        if (empty($tObjects))
        {
          verbose(sprintf('Removing tag "%s/#%d" as it isn\'t used any more...', $tTag, $tTagId), 'cli.none');
          $mapper = new Content_TagMapper($injector->getInstance('Horde_Db_Adapter'));
          $mapper->delete($tTagId);
        }
      }
    }
    catch(Horde_Db_Exception $e)
    {
      # fatal/exit is safe here as we don't care much
      # about a consistent content database
      $cli->fatal('Error while removing tags: ' . $e);
    }

    # remove SyncML anchors and maps
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ SyncML  ]');
      $syncml = Horde_SyncMl_Backend::factory('Horde');
      $devices = $syncml->getUserAnchors($user);
      if (!empty($devices))
      {
        verbose('Removing session...', 'cli.none');
        $syncml->removeAnchor($user);
        $syncml->removeMaps($user);
      }
    }
    catch(Horde_Exception $e)
    {
      # fatal/exit is safe here as we don't care much
      # about a consistent syncml database
      $cli->fatal('Error while removing SyncML session: ' . $e);
    }

    # remove ActiveSync devices
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ ActSync ]');
      $activesync = $GLOBALS['injector']->getInstance('Horde_ActiveSyncState');
      $activesync->setLogger($GLOBALS['injector']->getInstance('Horde_Log_Logger'));
      $devices = $activesync->listDevices($user);
      if (!empty($devices))
        verbose('Removing devices...', 'cli.none');
      foreach ($devices as $device)
        $activesync->removeState(null, $device['device_id'], $user);
    }
    catch(Horde_ActiveSync_Exception $e)
    {
      # fatal/exit is safe here as we don't care much
      # about a consistent activesync database
      $cli->fatal('Error while removing ActiveSync devices: ' . $e);
    }

    # remove history
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ History ]');
      verbose('Removing data...', 'cli.none');
      $db->delete('DELETE FROM horde_histories WHERE history_who = ?', array($user));
    }
    catch(Horde_Db_Exception $e)
    {
      # fatal/exit is safe here as we don't care much
      # about a consistent history database
      $cli->fatal('Error while removing history: ' . $e);
    }

    # remove user data
    # NOTE: this will leave/add an delete-entry in horde_histories
    # belonging to/referencing the admin user
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ Data ]');
      verbose('Removing user data...', 'cli.none');
      $GLOBALS['registry']->removeUserData($user);
    }
    catch(Horde_Exception $e)
    {
      $cli->message('  Error while removing ' . $user . ': ' . $e, 'cli.error');
      continue;
    }

    # remove history
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ History ]');
      verbose('Removing data...', 'cli.none');
      $db->delete('DELETE FROM horde_histories WHERE history_who = ?', array($user));
    }
    catch(Horde_Db_Exception $e)
    {
      # fatal/exit is safe here as we don't care much
      # about a consistent history database
      $cli->fatal('Error while removing history: ' . $e);
    }

    # clear horde prefs cache
    # otherwise the shutdown function will store the default value of all dirty
    # cache entries (= everything removed during removeUserData($user)) again
    try
    {
      $GLOBALS['vprefix'] = $cli->yellow('[ Prefs ]');
      verbose('Clear prefs cache...', 'cli.none');
      $prefs_ob = $GLOBALS['injector']
        ->getInstance('Horde_Core_Factory_Prefs')
        ->create('horde', array(
          'user' => $user
        ));
    }
    catch(Horde_Exception $e)
    {
      $cli->message('  Error while clearing prefs cache: ' . $e, 'cli.error');
      continue;
    }

    # finally remove the user from rampage
    try
    {
      # this should be the last as we might recreate the user during deletion
      $GLOBALS['vprefix'] = $cli->yellow('[ Content ]');
      verbose(sprintf('Removing user "%s/#%d"...', $user, $tUserId), 'cli.none');
      $usermanager = $GLOBALS['injector']->getInstance('Content_Users_Manager');
      $meth = new ReflectionMethod(get_class($usermanager), '_t');
      $meth->setAccessible(true);
      $db->delete('DELETE FROM ' . $meth->invoke($usermanager, 'users') . ' WHERE user_id = ?', array($tUserId));
    }
    catch(Horde_Db_Exception $e)
    {
      $cli->message('Error while removing user: ' . $e, 'cli.error');
      continue;
    }

    $cli->message('  ' . $user . ' removed', 'cli.success');
  }
}

$db->disconnect();

?>

cleanup_rampage.php cron

#!/usr/bin/env php
<?php

require_once 'PEAR/Config.php';
require_once PEAR_Config::singleton()
  ->get('horde_dir', null, 'pear.horde.org') . '/lib/Application.php';
Horde_Registry::appInit('horde', array('cli' => true, 'user_admin' => true));

try
{
  $db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_Db')->create('horde', 'removeUser');
}
catch(Horde_Db_Exception $e)
{
  $cli->fatal('Unable to connect to the database: ' . $e);
}

try
{
  # delete objects that don't have any tags
  # this will delete A LOT of objects that still exists
  # but we've no other way to remove orphans and existing
  # objects will be created again anyway
  $db->execute(
    'DELETE FROM ro USING rampage_objects ro ' .
    'LEFT OUTER JOIN rampage_tagged rtd ' .
    '  ON (ro.object_id = rtd.object_id) ' .
    'WHERE rtd.object_id IS NULL'
  );
}
catch (Horde_Db_Exception $e)
{
  $cli->fatal('Unable to execute query: ' . $e);
}

$db->disconnect();

?>


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


---- modified hordel

#!/bin/bash
#
# Cleanup of horde databases after user deletion from ldap
#
# Origional From Beaudoin <jacques-beaudoin@cspi.qc.ca>
# made it a little more flexible  Bill Graham <grahamcw@hurleybulldogs.com>
# H4 patch by Greg Pascal <ngombe at gmail dot com>
#

if [ $# = 2 ]
 then

    USER="$2"

    # # # # # # # # #
    # Global config #
    # # # # # # # # #

    # horde database
    DB="horde_4"
    # horde mysql userid
    _USER="mysql_user"
    # horde mysql passwd
    _PWD="mysql_user_passwd"
    # horde mysql host
    _HOST="localhost"
    # horde mysql port 
    _PORT="3306"

    # # # # # # # # #
    # Count records #
    # # # # # # # # #

    ## imp
    IM_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.imp_sentmail WHERE sentmail_who='$USER'"`

    ## ingo
    IF_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_forwards WHERE forward_owner='$USER'"`
    IL_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_lists WHERE list_owner='$USER'"`
    IR_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_rules WHERE rule_owner='$USER'"`
    IS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_shares WHERE share_owner='$USER'"`
    II_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_shares_users WHERE user_uid='$USER'"`
    IN_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_sharesng WHERE share_owner='$USER'"`
    IU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_sharesng_users WHERE user_uid='$USER'"`
    IP_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_spam WHERE spam_owner='$USER'"`
    IV_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.ingo_vacations WHERE vacation_owner='$USER'"`

    ## kronolith
    KE_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_events WHERE calendar_id='$USER'"`
    KS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_shares WHERE share_owner='$USER'"`
    KI_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_shares_users WHERE user_uid='$USER'"`
    KN_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_sharesng WHERE share_owner='$USER'"`
    KU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_sharesng_users WHERE user_uid='$USER'"`
    KT_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.kronolith_storage WHERE vfb_owner='$USER'"`

    ## mnemo
    MM_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.mnemo_memos WHERE memo_owner='$USER'"`
    MS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.mnemo_shares WHERE share_owner='$USER'"`
    MI_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.mnemo_shares_users WHERE user_uid='$USER'"`
    MN_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.mnemo_sharesng WHERE share_owner='$USER'"`
    MU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.mnemo_sharesng_users WHERE user_uid='$USER'"`

    ## nag
    NT_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.nag_tasks WHERE task_owner='$USER'"`
    NS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.nag_shares WHERE share_owner='$USER'"`
    NI_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.nag_shares_users WHERE user_uid='$USER'"`
    NN_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.nag_sharesng WHERE share_owner='$USER'"`
    NU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.nag_sharesng_users WHERE user_uid='$USER'"`

    ## turba
    TO_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.turba_objects WHERE owner_id='$USER'"`
    TS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.turba_shares WHERE share_owner='$USER'"`
    TI_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.turba_shares_users WHERE user_uid='$USER'"`
    TN_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.turba_sharesng WHERE share_owner='$USER'"`
    TU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.turba_sharesng_users WHERE user_uid='$USER'"`

    ## rampage
    RU_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.rampage_users WHERE user_name='$USER'"`
    USER_ID=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT user_id from $DB.rampage_users WHERE user_name='$USER'"`
    RT_B=0
    RO_B=0
    COUNT=0
    if [ $USER_ID ]
    then
        RT_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(tag_id) from $DB.rampage_tagged WHERE user_id='$USER_ID'"`
        RO_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(DISTINCT object_id) from $DB.rampage_tagged WHERE user_id='$USER_ID'"`
    fi

    ## Horde
    HA_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_alarms WHERE alarm_uid='$USER'"`
    HM_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_muvfs WHERE vfs_owner='$USER'"`
    HV_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_vfs WHERE vfs_owner='$USER'"`
    HY_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_syncml_anchors WHERE syncml_uid='$USER'"`
    HZ_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_syncml_map WHERE syncml_uid='$USER'"`
    HG_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_groups_members WHERE user_uid='$USER'"`
    HP_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_prefs WHERE pref_uid='$USER'"`
    HS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_histories WHERE history_who='$USER'"`
    HL_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_locks WHERE lock_owner='$USER'"`
    AM_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_activesync_map WHERE sync_user='$USER'"`
    AS_B=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_activesync_device_users WHERE device_user='$USER'"`

    ASD_B=0
    ASS_B=0
    COUNT=0
    mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT device_id from $DB.horde_activesync_device_users WHERE device_user='$USER'" > /tmp/01
    while read I
    do
      COUNT=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_activesync_device WHERE device_id='$I'"`
      ASD_B=`expr $ASD_B + $COUNT`
      COUNT=`mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT count(*) from $DB.horde_activesync_state WHERE sync_devid='$I'"`
      ASS_B=`expr $ASS_B + $COUNT`
    done</tmp/01

    # # # # # # # # # # # # #
    # Display records count #
    # # # # # # # # # # # # #

    echo "====================================================="
    echo " Records informations for user '$USER'"
    echo "====================================================="

    echo "horde_prefs: $HP_B"
    echo "horde_groups_members: $HG_B"
    echo "horde_histories: $HS_B"
    echo "horde_locks: $HL_B"
    echo "horde_alarms: $HA_B"
    echo "horde_vfs: $HV_B"
    echo "horde_muvfs: $HM_B"
    echo "horde_syncml_anchors: $HY_B"
    echo "horde_syncml_map: $HZ_B"
    echo "horde_activesync_device_users: $AS_B"
    echo "horde_activesync_device: $ASD_B"
    echo "horde_activesync_map: $AM_B"
    echo "horde_activesync_state: $ASS_B"

    echo "rampage_users: $RU_B"
    echo "rampage_tagged: $RT_B"
    echo "rampage_object: $RO_B"

    echo "imp_sentmail: $IM_B"

    echo "ingo_forwards: $IF_B"
    echo "ingo_spam: $IP_B"
    echo "ingo_vacations: $IV_B"
    echo "ingo_lists: $IL_B"
    echo "ingo_rules: $IR_B"
    echo "ingo_shares: $IS_B"
    echo "ingo_shares_users: $II_B"
    echo "ingo_sharesng: $IN_B"
    echo "ingo_sharesng_users: $IU_B"

    echo "kronolith_events: $KE_B"
    echo "kronolith_shares: $KS_B"
    echo "kronolith_shares_user: $KI_B"
    echo "kronolith_sharesng: $KN_B"
    echo "kronolith_sharesng_users: $KU_B"
    echo "kronolith_storage: $KT_B"

    echo "mnemo_memos: $MM_B"
    echo "mnemo_shares: $MS_B"
    echo "mnemo_shares_users: $MI_B"
    echo "mnemo_sharesng: $MN_B"
    echo "mnemo_sharesng_users: $MU_B"

    echo "nag_tasks: $NT_B"
    echo "nag_shares: $NS_B"
    echo "nag_shares_users: $NI_B"
    echo "nag_sharesng: $NN_B"
    echo "nag_sharesng_users: $NU_B"

    echo "turba_objects: $TO_B"
    echo "turba_shares: $TS_B"
    echo "turba_shares_users: $TI_B"
    echo "turba_sharesng: $TN_B"
    echo "turba_sharesng_users: $TU_B"

    echo "====================================================="

    if [ $1 = "remove" ]
     then

        # # # # # # # # # #
        #  Delete records #
        # # # # # # # # # #

        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.imp_sentmail WHERE sentmail_who='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_forwards WHERE forward_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_lists WHERE list_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_rules WHERE rule_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_shares WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_shares_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_sharesng WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_sharesng_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_spam WHERE spam_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.ingo_vacations WHERE vacation_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_events WHERE calendar_id='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_shares WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_shares_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_sharesng WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_sharesng_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.kronolith_storage WHERE vfb_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.mnemo_memos WHERE memo_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.mnemo_shares WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.mnemo_shares_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.mnemo_sharesng WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.mnemo_sharesng_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.nag_tasks WHERE task_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.nag_shares WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.nag_shares_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.nag_sharesng WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.nag_sharesng_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.turba_objects WHERE owner_id='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.turba_shares WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.turba_shares_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.turba_sharesng WHERE share_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.turba_sharesng_users WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_alarms WHERE alarm_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_muvfs WHERE vfs_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_vfs WHERE vfs_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_syncml_anchors WHERE syncml_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_syncml_map WHERE syncml_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_groups_members WHERE user_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_prefs WHERE pref_uid='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_histories WHERE history_who='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_locks WHERE lock_owner='$USER'"
        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_activesync_map WHERE sync_user='$USER'"

        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT device_id from $DB.horde_activesync_device_users WHERE device_user='$USER'" > /tmp/01
        while read I
        do
          mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_activesync_device WHERE device_id='$I'"
          mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_activesync_state WHERE sync_devid='$I'"
        done</tmp/01

        mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.horde_activesync_device_users WHERE device_user='$USER'"

        if [ $USER_ID ]
        then
            mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "SELECT DISTINCT object_id FROM $DB.rampage_tagged WHERE user_id='$USER_ID'" > /tmp/01
            while read I
            do
              mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.rampage_objects WHERE object_id='$I'"
            done</tmp/01
            mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.rampage_tagged WHERE user_id='$USER_ID'"
            mysql -s -u $_USER --password=$_PWD --host=$_HOST --port=$_PORT -e "DELETE from $DB.rampage_users WHERE user_name='$USER'"
        fi

        echo "Datas removed from $DB@$_HOST:$_PORT ...";

    fi

else
    echo "Syntaxe : $0 [test|remove] [uid]";
fi

exit 0