Diff for DatabaseCleanup between 2 and 3

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

<code>

#!/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$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

</code>