Hi,
i would like to share a code to transform and compress a database of MySQL to SQLite. I use it in Cron (automated task) to create a clone of my main database. It's hard to find a real good script bash code to do it.
Of course, change your MySQL option and "your_sqlite_db_name" in the correct name. Maybe, you must changing some Regex to a better adaptation.
This is a bash code, simply past in a new file named "mysql2sqlite.sh" and lunch it with the command "./mysql2sqlite.sh". The function's time is 3 seconds for a MySQL database with 10Mb undex 2x1.6 Ghz with 512 Mb of memory.
You can use HttpUtils2Service to download it, decompress it with WrapInputStream and write it on device (code B4A at the end of this thread).
Bash for linux
B4A HttpJob extract
i would like to share a code to transform and compress a database of MySQL to SQLite. I use it in Cron (automated task) to create a clone of my main database. It's hard to find a real good script bash code to do it.
Of course, change your MySQL option and "your_sqlite_db_name" in the correct name. Maybe, you must changing some Regex to a better adaptation.
This is a bash code, simply past in a new file named "mysql2sqlite.sh" and lunch it with the command "./mysql2sqlite.sh". The function's time is 3 seconds for a MySQL database with 10Mb undex 2x1.6 Ghz with 512 Mb of memory.
You can use HttpUtils2Service to download it, decompress it with WrapInputStream and write it on device (code B4A at the end of this thread).
Bash for linux
B4X:
#!/bin/sh
echo 'Dump started'
rm -f your_sqlite_db_name.db
rm -f your_sqlite_db_name.db.gz
echo 'Dump MySQL'
echo 'Transfert type :'
mysqldump -u [user] -p[password] --ignore-table=[option: 1st table ignored] --ignore-table=[option: 2nd table ignored] --skip-extended-insert --compatible=ansi --skip-opt --skip-comments --skip-triggers --quick [name of database] | \
awk '
BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
inTrigger != 0 { print; next }
/^\/\*/ { next }
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}
/^CREATE/ {
print
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
/ KEY/ { gsub(/\([0-9]+\)/, "") }
/^ / && !/^( 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( /[ ]COMMENT(.*)/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\)/, "text ")
gsub(/(SET|set)\([^)]+\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
gsub(/" [^ ]*(INT|int)[^ ]*/, "\" integer")
gsub(/" [^ ]*(DOUBLE|double)[^ ]*/, "\" NUMERIC")
if (prev) print prev ","
prev = $1
}
/^( 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"
}
}
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
' | sqlite3 your_sqlite_db_name.db
echo 'GZip in progress...'
gzip -c your_sqlite_db_name.db > your_sqlite_db_name.db.gz
echo 'Clean in progress'
rm -f your_sqlite_db_name.db
echo 'Dump terminated !'
exit 0B4A HttpJob extract
B4X:
If Job.JobName = "install" Then
Dim In As InputStream
Dim Out As OutputStream
Dim CS As CompressedStreams
ProgressDialogShow("Installation progress." & CRLF & "Please wait and make a coffee ...")
In = CS.WrapInputStream(Job.GetInputStream, "gzip")
Out = File.OpenOutput(File.DirInternal, DatabaseName, False)
File.Copy2(In, Out)
Out.Close
ProgressDialogHide
SQL.Initialize(File.DirInternal, DatabaseName, False)
End If
Last edited:
