-
-
Save nshenry03/8960534 to your computer and use it in GitHub Desktop.
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
#!/bin/bash - | |
#=============================================================================== | |
# | |
# FILE: mysql2sqlite.sh | |
# | |
# USAGE: ./mysql2sqlite.sh [options] [db_name [tbl_name ...]] | \ | |
# sqlite3 database.db | |
# | |
# EXAMPLE: ./mysql2sqlite.sh --no-data -u root -pMySecretPassWord \ | |
# myDbase | sqlite3 database.db | |
# | |
# DESCRIPTION: 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. | |
# | |
# OPTIONS: --- | |
# REQUIREMENTS: mysqldump, sqlite3 | |
# BUGS: --- | |
# | |
# NOTES: Awk was chosen 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. | |
# | |
# Thanks to and @artemyk and @gkuenning for their nice tweaks. | |
# | |
# AUTHOR: Jean-Luc Lacroix [@esperlu](github.com/esperlu) | |
# ORGANIZATION: --- | |
# CREATED: 04/26/2011 10:46:05 PM MST | |
# REVISION: 0.11.2 | |
# | |
# LICENSE: Licensed under the Apache License, Version 2.0 (the "License"); | |
# you may not use this file except in compliance with the License. | |
# You may obtain a copy of the License at | |
# | |
# http://www.apache.org/licenses/LICENSE-2.0 | |
# | |
# Unless required by applicable law or agreed to in writing, software | |
# distributed under the License is distributed on an "AS IS" BASIS, | |
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
# See the License for the specific language governing permissions and | |
# limitations under the License. | |
#=============================================================================== | |
mysqldump --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" ) | |
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;" | |
} | |
' | |
exit 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment