-
-
Save Appla/a30a2528614da76392c39f50a044ee7a to your computer and use it in GitHub Desktop.
MySQL to Sqlite converter
This file contains hidden or 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
| #!/bin/sh | |
| # Awk is choosen because it's fast and portable. You can use gawk, traditional awk or even the lightning fast mawk. | |
| # The mysqldump file is traversed only once. | |
| # 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. | |
| # Usage: $ ./mysql2sqlite | sqlite3 database.sqlite | |
| mysqldump --compatible=ansi --skip-extended-insert --compact -u **** -p****** datbase_name | \ | |
| awk -F ",$" ' | |
| BEGIN{ FS=",$" } | |
| # Skip comments | |
| /^\/\*/ { next } | |
| # Print all `INSERT` lines. The single quotes are protected by another single quote. | |
| /INSERT/ { gsub( /\\\047/, "\047\047" ); print; 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" ) } | |
| # Print all fields definition lines except the `KEY` lines. | |
| /^ / && !/^( KEY|\);)/ { | |
| gsub( /AUTO_INCREMENT/, "" ) | |
| gsub( /CHARACTER SET [^ ]+ /, "" ) | |
| gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP/, "" ) | |
| gsub( /COLLATE [^ ]+ /, "" ) | |
| gsub(/enum[^)]+\)/, "text ") | |
| gsub(/set\([^)]+\)/, "text ") | |
| gsub(/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] | |
| } | |
| ' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment