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

1. 1. Origional From Beaudoin <jacques-beaudoin@cspi.qc.ca>
   2. made it a little more flexible Bill Graham <grahamcw@hurleybulldogs.com>

1. 1. hordedel
   2. cleanup of horde databases after user deletion.

1. 1. Exemple usage hordedel "useridname""

USER="$1"

1. horde database

DB="yourhordedatabase"

1. horde mysql userid

SQLUSER="yourhordeuserid"

1. horde mysql passwd

SQLPWD="yourdatabasepassword"

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

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

1. 1. 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.kronolit_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

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