[commits] [Wiki] changed: DatabaseCleanup
Wiki Guest
wikiguest at horde.org
Thu Aug 30 19:14:17 UTC 2012
guest [195.98.226.13] Thu, 30 Aug 2012 19:14:17 +0000
Modified page: http://wiki.horde.org/DatabaseCleanup
New Revision: 8
Change log: Added modified hordel for horde 4
@@ -171,5 +171,268 @@
echo "horde_datatree: $HD_B / $HD_A"
echo "horde_datatree_attributes: $HDA_B / $HDA_A"
exit 0
+</code>
+
+
+---- modified hordel
+
+<code>
+#!/bin/bash
+#
+# Cleanup of horde databases after user deletion from ldap
+#
+# Origional From Beaudoin <jacques-beaudoin at cspi.qc.ca>
+# made it a little more flexible Bill Graham <grahamcw at 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
+
</code>
More information about the commits
mailing list