[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