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