Skip to content

Instantly share code, notes, and snippets.

@vidalon
Created June 20, 2012 17:49
Show Gist options
  • Save vidalon/2961155 to your computer and use it in GitHub Desktop.
Save vidalon/2961155 to your computer and use it in GitHub Desktop.
Bash script to migrate from a SQLite database to a MySQL database
#!/bin/bash
SQLITE_FILE=
SQLITE_FILE_TMP=
MYSQL_USER=
MYSQL_DATABASE=
while getopts ":s:d:u:" opt; do
case $opt in
s)
SQLITE_FILE="$OPTARG" ;;
d)
MYSQL_DATABASE="$OPTARG" ;;
u)
MYSQL_USER="$OPTARG" ;;
\?)
echo "Invalid option: -$OPTARG" >&2
;;
esac
done
# Clone the SQLite file to a temporary one
SQLITE_FILE_TMP="$SQLITE_FILE.tmp"
cp $SQLITE_FILE $SQLITE_FILE_TMP
# First, replace " (double-quotes) with ` (grave accent)
echo "Transforming the SQLite dump into a MySQL compatible one..."
perl -p -i -e 'next unless /CREATE/; s/"/`/g' $SQLITE_FILE_TMP
perl -p -i -e 's/INSERT INTO "([^"]+)"/INSERT INTO `$1`/g' $SQLITE_FILE_TMP
# Remove "BEGIN TRANSACTION;" "COMMIT;", and lines related to "sqlite_sequence" and "PRAGMA foreign_keys=OFF"
perl -i -n -e 'print unless /BEGIN TRANSACTION;/' $SQLITE_FILE_TMP
perl -i -n -e 'print unless /COMMIT;/' $SQLITE_FILE_TMP
perl -i -n -e 'print unless /sqlite_sequence/' $SQLITE_FILE_TMP
perl -i -n -e 'print unless /PRAGMA foreign_keys=OFF/' $SQLITE_FILE_TMP
# Replace "autoincrement" with "auto_increment"
perl -p -i -e 's/autoincrement/auto_increment/gi' $SQLITE_FILE_TMP
# Replace booleand fields default values
perl -p -i -e "s/'t'/'1'/gi" $SQLITE_FILE_TMP
perl -p -i -e "s/'f'/'0'/gi" $SQLITE_FILE_TMP
# Restore to the target MySQL databse
echo "Restoring $MYSQL_DATABASE with modified dump..."
mysql -u $MYSQL_USER $MYSQL_DATABASE < $SQLITE_FILE_TMP
# Cleaning up...
rm $SQLITE_FILE_TMP
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment