#!/bin/bash
if [ $# -lt 2 ]; then
    echo "my_sync version 1.00"
    echo
    echo "Show difference between two MySQL databases including:"
    echo " * Table exist only in one database"
    echo " * Table format differs"
    echo " * Table row differs (only for choosen tables with ability to"
    echo "   execute individual SQL per table)"
    echo "Usage: $0 'connect1' 'connect2' ['sql1' ... 'sqlN']"
    echo "  'connectX' is connect parameters for mysql like this:"
    echo "     --user=USER --password=PASS --host=HOST DATABASE"
    echo "  'sqlX' can be sql query OR table name."
    echo "     For table name: 'SELECT * FROM sqlX' will be executed."
    echo "Examples:"
    echo "  my_sync '-u test test' '-u test -h server2 test'"
    echo "  my_sync '-u me -pmypass mydb' \\"
    echo "          '-u me -pmypass -h server2 mydb' \\"
    echo "          mytable1 \\"
    echo "          'SELECT field1, field2 FROM mytable2' \\"
    echo "          mytable3"
    echo "BUGS:"
    echo " * too slow :-("
    exit
fi
on_exit () { 
	rm -rf /tmp/my_sync.*.$$ 
	exit
}
trap 'on_exit' 0 2

connect1=$1
connect2=$2
tables1="/tmp/my_sync.tables1.$$"
tables2="/tmp/my_sync.tables2.$$"
tables_diff="/tmp/my_sync.tables_diff.$$"
table1="/tmp/my_sync.table1.$$"
table2="/tmp/my_sync.table2.$$"
table_diff="/tmp/my_sync.table_diff.$$"
echo "Checking database"
echo "show tables;" | mysql -N $connect1 | sort >$tables1
echo "show tables;" | mysql -N $connect2 | sort >$tables2
diff -u0 $tables2 $tables1 | grep '^[+-][^+-]' >$tables_diff
cat $tables_diff
for t in `cat $tables1 $tables2 | sort | uniq -d`; do
    echo "Checking table format: $t"
    mysqldump -d $connect1 $t | grep '^  ' | sort >$table1
    mysqldump -d $connect2 $t | grep '^  ' | sort >$table2
    perl -i -pe '
	s/ default NULL//;
	s/KEY (\w+)\(/KEY $1 (/;
	s/UNIQUE KEY/UNIQUE/;
	s/timestamp\(14\) NOT NULL/timestamp(14)/;
	s/ NOT NULL default (.*?),/ DEFAULT $1 NOT NULL,/;
	' $table1 $table2 # alter format 3.23 ==> 3.22
    diff -u0 $table2 $table1 | grep '^[+-][^+-]' >$table_diff
    cat $table_diff
done
shift 2
for sql in "$@"; do
    sql=`echo $sql | perl -pe 's/^(\w+)$/SELECT * FROM $1/'`
    echo "Checking table content: $sql"
    echo "$sql;" | mysql -N $connect1 | sort >$table1
    echo "$sql;" | mysql -N $connect2 | sort >$table2
    diff -u0 $table2 $table1 | grep '^[+-][^+-]' >$table_diff
    cat $table_diff
done