Last active
December 15, 2016 14:01
-
-
Save owen2345/34dcb35338e7572c3c11d3cc5b9d4e3b to your computer and use it in GitHub Desktop.
Camaleon CMS migrate sqlite3 to mysql DB
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
#Take it line by line | |
pending_line = [] | |
query_end = ');' # take care with insert content's ending with ");" | |
ARGF.each do |line| | |
# fix for: mapping values are not allowed in this context at (content used by rails for serialized model attributes) | |
if line.start_with?('INSERT INTO') && !line.strip.end_with?(query_end) | |
pending_line = [line.gsub("\n", '')] | |
next | |
end | |
if pending_line.length > 0 && !line.strip.end_with?(query_end) | |
pending_line << line.gsub("\n", '') | |
next | |
end | |
if pending_line.length > 0 | |
line = (pending_line << line).join('\\n') | |
pending_line = [] | |
end | |
# end fix | |
# Remove lines not included in MySQL | |
if line.start_with? "PRAGMA" \ | |
or line.start_with? "COMMIT" \ | |
or line.start_with? "BEGIN TRANSACTION" \ | |
or line.start_with? "CREATE UNIQUE INDEX" \ | |
or line.start_with? "DELETE FROM sqlite_sequence" \ | |
or line.start_with? "INSERT INTO \"sqlite_sequence\"" | |
next | |
end | |
line = line.gsub(", 't'",", 1") | |
line = line.gsub(",'t'",", 1") | |
line = line.gsub(", 'f'",", 0") | |
line = line.gsub(",'f'",", 0") | |
if line.start_with?('CREATE TABLE') # create table rows | |
line = line.gsub('autoincrement','auto_increment') | |
line = line.gsub('AUTOINCREMENT','AUTO_INCREMENT') | |
line = line.gsub("DEFAULT 't'", "DEFAULT '1'") | |
line = line.gsub("DEFAULT 'f'", "DEFAULT '0'") | |
line = line.gsub(/ varchar(?!\()/, ' varchar(255)') | |
line = line.gsub('"',"`") | |
#Fix problems with sqlite3 numbers by making (almost) every INTEGER a mysql BIGINT type | |
line = line.gsub("INTEGER", "BIGINT") unless line.include? " id " or line.include? " ID " | |
#replace datetime('now') function from sqlite with mysql equivalent | |
#datetime is sometimes wrapped, so try to remove the extra () first | |
line = line.gsub("(datetime('now'))","NOW()") | |
#then replace the unwrapped function | |
line = line.gsub("datetime('now')","NOW()") | |
line = line.gsub("datetime default","TIMESTAMP DEFAULT") | |
line = line.gsub("DATETIME DEFAULT","TIMESTAMP DEFAULT") | |
# remove default value for text formats | |
line = line.gsub(/` text DEFAULT '(.*)'/, '` text') | |
end | |
if line.start_with?('INSERT INTO ') # insert rows | |
tmp = line.split(' VALUES') | |
tmp[0] = tmp.first.gsub('"',"`") | |
line = tmp.join(' VALUES') | |
end | |
if line.start_with? 'CREATE INDEX ' # create indexes fix | |
line = line.gsub('"',"`") | |
end | |
#Write line to standard out | |
$stdout.write line | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Check manual here:
http://camaleon.tuzitio.com/documentation/category/40756-uncategorized/sqlite3-to-mysql.html