Android SQLite dump comparator

droid_sqlite_dump_compare

Debugging the SQLite database in Android can be a pain. A common technique is to dump the .sqlite database and inspect it using some program. 
Sometimes we just want to know what has changed in the database within a time period, we can do this by dumping the database twice and comparing the two dumps. 

This function speeds up the process of viewing differences between database dumps.

To use this function create an empty directory and navigate to it. This directory will be where we store dumps and perform comparisons. 
Make sure you create a separate directory for each application you’re inspecting, or you may end up comparing dumps from different applications.

Usage: sqlitecompare <path_to_sqlite_file_on_device> e.g. sqlitecompare /data/data/com.2bard.foo/databases/userdb

~~This currently only works if you have one device attached via ADB~~
~~This was only tested on OSX 10.9.2/zsh 5.0.2 

sqlitecompare(){
	db_filepath=$1
	num_of_files=`ls *.sql.txt | wc -l | tr -d ' '`
	timestamp=`date +%s`

	case $num_of_files in

		0)	print “No DB dumps exist, pulling new dump…”
			  pull_db $db_filepath $timestamp
			  print “…dump collected” 
			  ;;

		1)	pull_and_compare $db_filepath $timestamp 
			  ;;

		2)	delete_oldest_file
			  pull_and_compare $db_filepath $timestamp 
			  ;;

		*)	echo “Clean this mess up”
			  ;;

	esac
}

#pull the database from the attached device then compare with the existing dump in the current directory
# $1 = filepath
# $2 = timestamp (for dump filename)
pull_and_compare(){
	filepath=$1
	timestamp=$2
	pull_db $filepath $timestamp
	compare_dumps
}

#delete the oldest .sql.txt file in the current directory
delete_oldest_file(){
	print There are two dumps in the current directory, deleting the oldest…
	rm -rf `ls -tr1 *.sql.txt | head -1`
	print …done
}

#pull the database from the attached device
# $1 = filepath
# $2 = timestamp (for dump filename)
pull_db(){
	filepath=$1
	timestamp=$2

	adb pull $filepath $timestamp
	sqlite3 $timestamp .dump > $timestamp.sql.txt
	rm -rf $timestamp
}

##compares two sql.txt files in the current directory using diff
compare_dumps(){
	file_a=`ls *.sql.txt | sort -n | head -1`
	file_b=`ls *.sql.txt | sort -n | head -2 | tail -1`
	
	print comparing $file_a with $file_b

	if [[ -e result.diff ]]; then
		rm -rf result.diff
	fi

	diff -u $file_a $file_b > result.diff
	cat result.diff
	rm -rf result.diff
}