VERS=-v1
PREF=inc101

DBNAME=mssql
#MYSQLOPT="-u mssql"
MYSQLOPT="-u mssql -S /tmp/musql.sock"



#ln -s .. $PREF
#mkdir $PREF


# the UTF16 version is generated on windows
# -- restore backup in sql studio express
# -- use sql publishing wizard to dump it all out


#echo utf16 to utf8 conversion
#iconv -f utf16 -t utf8 ${PREF}-mssql-utf16.sql -o ${PREF}-mssql-utf8.sql
#ln ${PREF}-mssql-utf8.sql $PREF/
#bzip2 -9f $PREF/${PREF}-mssql-utf8.sql &

echo mssql to mysql conversion
perl dboimporter.pl -d "" -D < ${PREF}-mssql-utf8.sql > TMP-${PREF}-mysql$VERS.sql 
mysqladmin $MYSQLOPT -f drop $DBNAME
mysqladmin $MYSQLOPT -f create $DBNAME
echo mysql loading
mysql $MYSQLOPT $DBNAME < TMP-${PREF}-mysql$VERS.sql
# checkpoint
# mysqldump $MYSQLOPT $DBNAME > CHK-${PREF}-mysql5$VERS.sql
# mysql $MYSQLOPT $DBNAME < CHK-${PREF}-mysql5$VERS.sql
## POLISHING ##
#
# DB2NAME=msnew
# mysqladmin $MYSQLOPT -f drop $DB2NAME
# mysqladmin $MYSQLOPT -f create $DB2NAME
# sed -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' CHK-${PREF}-mysql5$VERS.sql | mysql $MYSQLOPT $DB2NAME
#
# select coalesce(n.table_name,o.table_name),o.table_rows,n.table_rows from (select table_name,table_rows from information_schema.tables where table_schema='mssql') n right join (select table_name,table_rows from information_schema.tables where table_schema='dbo') o on n.table_name=o.table_name where o.table_rows is null or n.table_rows is null or o.table_rows != n.table_rows or n.table_rows = 0 or o.table_rows = 0;
#
##
echo applying fixups-and-fk
mysql $MYSQLOPT $DBNAME < update-fixups-and-fk.sql

echo mysql structure dump
echo "show tables;" | mysql $MYSQLOPT $DBNAME | (read;cat) > TMP-${PREF}-tables$VERS
mysqldump -d $MYSQLOPT $DBNAME > ${PREF}-mysql5-tables$VERS.sql
ln ${PREF}-mysql5-tables$VERS.sql $PREF/

echo mysql singlefile dump
mysqldump --skip-extended-insert --no-autocommit $MYSQLOPT $DBNAME > ${PREF}-mysql5$VERS.sql
ln ${PREF}-mysql5$VERS.sql $PREF/
bzip2 -9f $PREF/${PREF}-mysql5$VERS.sql &

echo mysql multifile dump
mkdir ${PREF}-mysql5-sql$VERS
for a in `cat TMP-${PREF}-tables$VERS`; do
	mysqldump --skip-extended-insert --no-autocommit $MYSQLOPT $DBNAME $a > ${PREF}-mysql5-sql$VERS/${PREF}-$a-mysql5$VERS.sql
done
cp -al ${PREF}-mysql5-sql$VERS $PREF/
bzip2 -9f $PREF/${PREF}-mysql5-sql$VERS/* &
#tar cf ${PREF}-mysql5-sql$VERS.tar ${PREF}-mysql5-sql$VERS
#ln ${PREF}-mysql5-sql$VERS.tar $PREF/
#bzip2 -9f $PREF/${PREF}-mysql5-sql$VERS.tar &

echo mysql ansi dump
mysqldump --skip-extended-insert --compact --compatible=ansi --default-character-set=binary $MYSQLOPT $DBNAME > ${PREF}-mysql5-ansi$VERS.sql
#ln ${PREF}-mysql5-ansi$VERS.sql $PREF/
#bzip2 -9f $PREF/${PREF}-mysql5-ansi$VERS.sql  &

echo mysql to sqlite conversion
# ln ../../mysql2* .
perl mysql2sqlite.pl < ${PREF}-mysql5-ansi$VERS.sql > TMP-${PREF}-sqlite$VERS.sql
echo sqlite loading
sqlite3 ${PREF}-sqlite3$VERS.db < TMP-${PREF}-sqlite$VERS.sql
ln ${PREF}-sqlite3$VERS.db $PREF/
bzip2 -9f $PREF/${PREF}-sqlite3$VERS.db &

mysqldump --skip-extended-insert --compact --compatible=ansi --default-character-set=binary $MYSQLOPT $DBNAME `cat eam-tables` > TMP-${PREF}-mysql5-ansi-eam$VERS.sql
perl mysql2sqlite.pl < TMP-${PREF}-mysql5-ansi-eam$VERS.sql > TMP-${PREF}-sqlite-eam$VERS.sql
sqlite3 ${PREF}-sqlite3-eam$VERS.db < TMP-${PREF}-sqlite-eam$VERS.sql
ln ${PREF}-sqlite3-eam$VERS.db $PREF/
bzip2 -9f $PREF/${PREF}-sqlite3-eam$VERS.db  &



echo mysql to postgres conversion
cat /dev/null > TMP-${PREF}-pgsql-IDX$VERS.sql
cat /dev/null > TMP-${PREF}-pgsql$VERS.sql
perl mysql2pgsql.pl --noquotes --nounsignedchecks --nodrop --preserve_case \
	--sepfile TMP-${PREF}-pgsql-IDX$VERS.sql ${PREF}-mysql5$VERS.sql TMP-${PREF}-pgsql$VERS.sql
sed -e 's/`//g' update-fixups-and-fk.sql | grep FOREIGN > TMP-${PREF}-pgsql-FK$VERS.sql


cat TMP-${PREF}-pgsql$VERS.sql \
	TMP-${PREF}-pgsql-IDX$VERS.sql \
	TMP-${PREF}-pgsql-FK$VERS.sql > ${PREF}-pgsql$VERS-unquoted-compatible.sql
ln ${PREF}-pgsql$VERS-unquoted-compatible.sql $PREF/
bzip2 -9f $PREF/${PREF}-pgsql$VERS-unquoted-compatible.sql &

echo postgres loading
dropdb $DBNAME
createdb $DBNAME
psql -q -f TMP-${PREF}-pgsql$VERS.sql $DBNAME
psql -q -f TMP-${PREF}-pgsql-IDX$VERS.sql $DBNAME
psql -q -f TMP-${PREF}-pgsql-FK$VERS.sql $DBNAME

echo postgres dumping
pg_dump -x -O $DBNAME > ${PREF}-pgsql$VERS.sql
ln ${PREF}-pgsql$VERS.sql $PREF/
bzip2 -9f $PREF/${PREF}-pgsql$VERS.sql  &



echo mysql xml multifile dump
mkdir ${PREF}-mysql5-xml$VERS
for a in `cat TMP-${PREF}-tables$VERS`; do
	mysqldump -X $MYSQLOPT $DBNAME $a > ${PREF}-mysql5-xml$VERS/${PREF}-$a-mysql5$VERS.xml
done
cp -al ${PREF}-mysql5-xml$VERS $PREF/
bzip2 -9f $PREF/${PREF}-mysql5-xml$VERS/* &

#tar cf ${PREF}-mysql5-xml$VERS.tar ${PREF}-mysql5-xml$VERS
#ln ${PREF}-mysql5-xml$VERS.tar $PREF/

wait


exit 0


echo mysql xml singlefile dump
mysqldump -X $MYSQLOPT $DBNAME > ${PREF}-mysql5$VERS.xml
ln ${PREF}-mysql5$VERS.xml $PREF/


mkdir meep$VERS
for a in `cat meep-tables`; do 
	mysql -B $MYSQLOPT $DBNAME -e "SELECT * FROM $a" | \
	perl -F"\t" -lane 'print join "|", map {s/"/""/g; s/^NULL$/0/g; die "bad val: $_" if /\|/; /^-?[\d.]+(?:e(?:\+|-)\d+)?$/ ? $_ : qq("$_")} @F ' > meep$VERS/${PREF}-meep$VERS-$a.csv
done
tar cjpf meep$VERS.tar.bz2 meep$VERS



mkdir ${PREF}-excel$VERS
for a in `cat meep-tables`; do 
	mysql -B $MYSQLOPT $DBNAME -e "SELECT * FROM $a" | \
	perl -F"\t" -lane 'print join ",", map {s/"/""/g; s/^NULL$/0/g; /^-?[\d.]+(?:e(?:\+|-)\d+)?$/ ? $_ : qq("$_")} @F ' > ${PREF}-excel$VERS/${PREF}-excel$VERS-$a.csv
done
tar cjpf ${PREF}-excel$VERS.tar.bz2 ${PREF}-excel$VERS