Skip to content

Instantly share code, notes, and snippets.

@cabbiepete

cabbiepete/mysqlsandbox2sqlite.sh

Created May 4, 2016 23:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cabbiepete/fdbf7985c7123213a4300df7e609a6a7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cabbiepete/fdbf7985c7123213a4300df7e609a6a7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. cabbiepete created this gist May 4, 2016.
    106 changes: 106 additions & 0 deletions mysqlsandbox2sqlite.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,106 @@
    #!/bin/sh

    # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
    # CREATE block and create them in separate commands _after_ all the INSERTs.

    # Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
    # The mysqldump file is traversed only once.

    # Usage: $ ./mysql2sqlite path-to-sandbox mysqldump-opts db-name | sqlite3 database.sqlite
    # Example: $ ./mysql2sqlite ~/sandboxes/msb_5_6_28 --no-data myDbase | sqlite3 database.sqlite

    # Thanks to and @artemyk and @gkuenning for their nice tweaks.

    # NB: If you are using mysql sandbox change mysqldump below to the equilvalent
    # e.g. /Users/cabbiepete/sandboxes/msb_5_6_28/my sqldump

    sandbox=$1
    shift 1
    $sandbox/my sqldump --compatible=ansi --skip-extended-insert --compact "$@" | \

    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" )
    print
    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")
    print
    next
    }
    # Print the `CREATE` line as is and capture the table name.
    /^CREATE/ {
    print
    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( /(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 == ");"){
    print
    } 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;"
    }
    '
    exit 0