Last active
August 29, 2015 14:14
-
-
Save davidlj95/b78947bc3074e206c659 to your computer and use it in GitHub Desktop.
MySQL2SQLite3.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #=============================================================================# | |
| # __ __ _____ ____ _ ___ _____ ____ _ _ _ # | |
| # | \/ | / ____|/ __ \| | |__ \ / ____|/ __ \| | (_) | # | |
| # | \ / |_ _| (___ | | | | | ) | | (___ | | | | | _| |_ ___ # | |
| # | |\/| | | | |\___ \| | | | | / / \___ \| | | | | | | __/ _ \ # | |
| # | | | | |_| |____) | |__| | |____ / /_ ____) | |__| | |____| | || __/ # | |
| # |_| |_|\__, |_____/ \___\_\______| |____| |_____/ \___\_\______|_|\__\___| # | |
| # __/ | # | |
| # |___/ # | |
| # # | |
| #-----------------------------------------------------------------------------# | |
| # USAGE # | |
| #-----------------------------------------------------------------------------# | |
| # See below HELP function or use -h option to see some usage help # | |
| # # | |
| #-----------------------------------------------------------------------------# | |
| # CREDITS # | |
| #-----------------------------------------------------------------------------# | |
| # # | |
| #.................................. AUTHORS ..................................# | |
| # @davidlj95 mySQL2SQLite3.sh # | |
| # https://gist.github.com/davidlj95/b78947bc3074e206c659 # | |
| # # | |
| #.................................. BASED ON .................................# | |
| # @esperlu mysql2sqlite.sh # | |
| # https://gist.github.com/esperlu/943776 # | |
| # # | |
| #=============================================================================# | |
| #=============================================================================# | |
| # START BASH # | |
| #=============================================================================# | |
| #!/bin/bash | |
| #=============================================================================# | |
| # GLOBAL VARIABLES # | |
| #=============================================================================# | |
| ## OPTIONS ## | |
| declare -a g_Options=(0) | |
| #=============================================================================# | |
| # HELP # | |
| # # | |
| # Shows a help message to show users how to use the script # | |
| #=============================================================================# | |
| function help { | |
| ## TITLE ## | |
| echo -e \ | |
| "==========================================================================\n"\ | |
| " MYSQL TO SQLITE SCRIPT \n"\ | |
| "==========================================================================\n"\ | |
| "\n"\ | |
| " DESCRIPTION:\n"\ | |
| "--------------------------------------------------------------------------\n"\ | |
| " Given a database name, user and password, converts the MySQL dump of\n"\ | |
| " that database into a SQLite3 compatible file\n"\ | |
| " The difference between the base version made by @esperlu in GitHub is\n"\ | |
| " that takes care about encoding to use accents (using UTF8 encoding)\n\n"\ | |
| " USAGE:\n"\ | |
| "--------------------------------------------------------------------------\n"\ | |
| " The syntax to use the script is the following\n\n"\ | |
| " $0 -u \"DB_Username\" -p \"DB_Pass\" -d \"DB_Name\" file [-e] [-h]\n\n"\ | |
| "\t OPTIONS:\n"\ | |
| "\t\t -u Username\n"\ | |
| "\t\t Username to setup a connection with the database\n"\ | |
| "\t\t -p Password\n"\ | |
| "\t\t Password of the previous username\n"\ | |
| "\t\t -d Database\n"\ | |
| "\t\t Database to convert\n"\ | |
| "\t\t -e \"Extra arguments\"\n"\ | |
| "\t\t Extra arguments to pass to mysqldump\n"\ | |
| "\t\t -h\n"\ | |
| "\t\t Show help and exit\n"\ | |
| " EXAMPLE:\n"\ | |
| "--------------------------------------------------------------------------\n"\ | |
| " Convert exampleDB with user/pass admin/admin into file myDB.sqlite\n"\ | |
| " $0 -u \"admin\" -p \"admin\" -d \"exampleDB\" myDB.sqlite\n\n"\ | |
| " AUTHORS:\n"\ | |
| "--------------------------------------------------------------------------\n"\ | |
| " @davidlj95 based on @esperlu from GitHub" | |
| } | |
| #=============================================================================# | |
| # PARSE OPTIONS # | |
| # # | |
| # Given the options of the script, tries to get all options and its values # | |
| # and saves them into the global options variables # | |
| # # | |
| # If missing mandatory options, exits # | |
| # # | |
| # @param array - array of options ($@) # | |
| #=============================================================================# | |
| function parseOptions { | |
| #-------------------------------------------------------------------------# | |
| # Variables # | |
| #-------------------------------------------------------------------------# | |
| local user="" | |
| local pass="" | |
| local database="" | |
| local file="" | |
| local extra="" | |
| #-------------------------------------------------------------------------# | |
| # Parse options # | |
| #-------------------------------------------------------------------------# | |
| while getopts :u:p:d:e:h option | |
| do | |
| case $option in | |
| # HELP | |
| h) help && exit;; | |
| # USER | |
| u) user=$OPTARG;; | |
| # PASS | |
| p) pass=$OPTARG;; | |
| # DATABASE | |
| d) database=$OPTARG;; | |
| # EXTRA | |
| e) extra=$OPTARG;; | |
| # NON-IDENTIFIED | |
| *) | |
| >&2 echo "(Error) One or more options do not exist ($OPTIND)" | |
| >&2 echo " Use -h to see help" | |
| exit;; | |
| esac | |
| done | |
| #-------------------------------------------------------------------------# | |
| # Parse arguments # | |
| #-------------------------------------------------------------------------# | |
| # Update pointer | |
| shift $(($OPTIND -1)) | |
| #File | |
| if [ "$1" == "" ] | |
| then | |
| >&2 echo "(Error) No output file specified" | |
| >&2 echo " Use -h to see help using the script" | |
| exit | |
| else | |
| file="$1" | |
| fi | |
| #-------------------------------------------------------------------------# | |
| # Save options # | |
| #-------------------------------------------------------------------------# | |
| g_Options[0]=$user | |
| g_Options[1]=$pass | |
| g_Options[2]=$database | |
| g_Options[3]=$file | |
| g_Options[4]=$extra | |
| } | |
| #=============================================================================# | |
| # GETTERS # | |
| # # | |
| # Helpers to get from the global options variable in an easy way # | |
| # They echo the queried option # | |
| #=============================================================================# | |
| function getOption { echo "${g_Options[$1]}" ; } | |
| function getUser { getOption 0 ; } | |
| function getPass { getOption 1 ; } | |
| function getDatabase { getOption 2 ; } | |
| function getOutputFile { getOption 3 ; } | |
| function getExtra { getOption 4 ; } | |
| function getTemp { echo ".mysql2sqlite.tmp" ; } | |
| #=============================================================================# | |
| # GENERATE MYSQLDUMP # | |
| # # | |
| # Generates a dump from the MySQL database to then convert it to SQLite # | |
| #=============================================================================# | |
| function generate_mysqldump { | |
| #-------------------------------------------------------------------------# | |
| # Variables # | |
| #-------------------------------------------------------------------------# | |
| local opt_User=$(getUser) | |
| local opt_Pass=$(getPass) | |
| local opt_DB=$(getDatabase) | |
| local opt_File=$(getOutputFile) | |
| local opt_Extra=$(getExtra) | |
| #-------------------------------------------------------------------------# | |
| # Options # | |
| #-------------------------------------------------------------------------# | |
| ## USER ## | |
| if ! [ "$opt_User" == "" ] | |
| then | |
| opt_User="--user=$opt_User" | |
| fi | |
| ## PASS ## | |
| if ! [ "$opt_Pass" == "" ] | |
| then | |
| opt_Pass="--password=$opt_Pass" | |
| fi | |
| ## FILE ## | |
| opt_File="--result-file=$opt_File" | |
| #-------------------------------------------------------------------------# | |
| # MySQLDump # | |
| #-------------------------------------------------------------------------# | |
| mysqldump --compatible=ansi --skip-extended-insert --compact\ | |
| $opt_File $opt_User $opt_Pass $opt_DB $opt_Extra | |
| } | |
| #=============================================================================# | |
| # MYSQL2SQLITE # | |
| # # | |
| # Converts a MySQL dump to SQLite compatible database file # | |
| # They echo the queried option # | |
| #=============================================================================# | |
| function mysql2sqlite { | |
| #-------------------------------------------------------------------------# | |
| # Variables # | |
| #-------------------------------------------------------------------------# | |
| (awk ' | |
| BEGIN { | |
| FS=",$" | |
| print "PRAGMA synchronous = OFF;" | |
| print "PRAGMA journal_mode = MEMORY;" | |
| print "BEGIN TRANSACTION;" | |
| } | |
| # CREATE TRIGGER statements have funny commenting | |
| # Remember we are in trigger. | |
| /^\/\*.*CREATE.*TRIGGER/ { | |
| gsub( /^.*TRIGGER/, "CREATE TRIGGER" ) | |
| inTrigger = 1 | |
| next | |
| } | |
| # The end of CREATE TRIGGER has a stray comment terminator | |
| /END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next } | |
| # The rest of triggers just get passed through | |
| inTrigger != 0 { print; next } | |
| # Skip other comments | |
| /^\/\*/ { next } | |
| # Print all `INSERT` lines | |
| # The single quotes are protected by another single quote. | |
| /INSERT/ { | |
| gsub( /\\\047/, "\047\047" ) | |
| gsub(/\\n/, "\n") | |
| gsub(/\\r/, "\r") | |
| gsub(/\\"/, "\"") | |
| gsub(/\\\\/, "\\") | |
| gsub(/\\\032/, "\032") | |
| next | |
| } | |
| # Print the `CREATE` line as is and capture the table name. | |
| /^CREATE/ { | |
| if (match($0,/\"[^\"]+/)) tableName = substr($0,RSTART+1,RLENGTH-1) | |
| } | |
| # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY` | |
| /^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) } | |
| # Get rid of field lengths in KEY lines | |
| / KEY/ { gsub(/\([0-9]+\)/, "") } | |
| # Print all fields definition lines except the `KEY` lines. | |
| /^ / && !/^( KEY|\);)/ { | |
| gsub( /AUTO_INCREMENT|auto_increment/, "" ) | |
| gsub( /(CHARACTER SET|character set) [^ ]+ /, "" ) | |
| gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" ) | |
| gsub( /(COLLATE|collate) [^ ]+ /, "" ) | |
| gsub(/(ENUM|enum)[^)]+\)/, "text ") | |
| gsub(/(SET|set)\([^)]+\)/, "text ") | |
| gsub(/UNSIGNED|unsigned/, "") | |
| if (prev) print prev "," | |
| prev = $1 | |
| } | |
| # `KEY` lines are extracted from the `CREATE` block and stored in | |
| # array for later print | |
| # in a separate `CREATE KEY` command. The index name is prefixed by the | |
| # table name to avoid a sqlite error for duplicate index name. | |
| /^( KEY|\);)/ { | |
| if (prev) print prev | |
| prev="" | |
| if ($0 == ");"){ | |
| } else { | |
| if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) | |
| if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) | |
| key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n" | |
| } | |
| } | |
| # Print all `KEY` creation lines. | |
| END { | |
| for (table in key) printf key[table] | |
| print "END TRANSACTION;" | |
| } | |
| ' < $(getOutputFile)) > $(getTemp) | |
| #-------------------------------------------------------------------------# | |
| # Generate SQLite3 # | |
| #-------------------------------------------------------------------------# | |
| # Remove MySQL dump | |
| rm $(getOutputFile) | |
| # Generate database | |
| cat $(getTemp) | sqlite3 $(getOutputFile) | |
| # Remove temp file | |
| rm $(getTemp) | |
| } | |
| #=============================================================================# | |
| # COMMANDS # | |
| #=============================================================================# | |
| # 0. PARSE OPTIONS | |
| parseOptions $@ | |
| # 1. GENERATE MYSQLDUMP | |
| generate_mysqldump | |
| # 2. CONVERT | |
| mysql2sqlite |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
mySQL2SQLite3 Script
The aim of this shell script is to convert a MySQL database into a SQLite3 file containing a copy of that database, taking care of the encoding UTF8
The script is based on @esperlu mysql2sqlite.sh script and just improves the way to use it, a little bit of help and takes care about encoding. The main script is the same.
Usage
The usage is a little bit different of @esperlu original script, but not so much.
Make it executable
First of all, make the script executable:
chmod +x mySQL2SQLite3.shArguments
The following arguments are available:
man mysqldump)Example
Convert exampleDB with user/pass admin/admin into file myDB.sqlite, with just structure
./mySQL2SQLite3.sh -u "admin" -p "admin" -d "exampleDB" -e "--no-data" myDB.sqliteCredits
The script is based in @esperlu mysql2sqlite.sh shell script. All the AWK code is from there