[cvs] [Wiki] created: DatabaseCleanup

[Wiki Guest]

guest [64.179.170.108]  Mon, 26 Jun 2006 18:13:30 -0700

Created page: http://wiki.horde.org/DatabaseCleanup

Here is a  database cleanup script that was first  posted on the Horde list by Jacques Beaudoin <jacques-beaudoin at 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 at cspi.qc.ca>
# made it a little more flexible  Bill Graham <grahamcw at 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