{"id":993,"date":"2012-06-06T14:54:22","date_gmt":"2012-06-06T12:54:22","guid":{"rendered":"http:\/\/www.dotmana.com\/weblog\/?p=993"},"modified":"2012-06-06T21:06:38","modified_gmt":"2012-06-06T19:06:38","slug":"conversion-mysql-sqlite","status":"publish","type":"post","link":"http:\/\/www.dotmana.com\/weblog\/2012\/06\/conversion-mysql-sqlite\/","title":{"rendered":"Conversion Mysql -> Sqlite"},"content":{"rendered":"

Apr\u00e8s quelques minutes<\/del> heures de batailles, enfin un script qui fonctionne pour convertir votre sch\u00e9ma + vos data Mysql vers Sqlite. Merci \u00e0 nfuria sur SOF<\/a><\/p>\n

Je pensais la conversion simple via une option directe dans mysqldump… Pas du tout, c’est le parcours du combattant pour passer du format Mysql \u00e0 Sqlite. Ce script est le meilleur : il fonctionne d’un coup (j’en ai test\u00e9 5 autres avec diff\u00e9rents niveau de succ\u00e8s). L’astuce du script est que plut\u00f4t que vouloir retravailler le dump mysql \u00e0 la main en virant les caract\u00e8res non accept\u00e9s par sqlite ou en supprimant certaines instructions SQL, il se base sur le module (fiable) SQL::Translator de Perl.<\/p>\n

N\u00e9cessite donc d’avoir le paquet “sqlfairy” install\u00e9.<\/p>\n

\n
\r\n#!\/bin\/sh\r\n#===============================================================================\r\n#         USAGE: .\/mysql2sqlite.sh  \r\n#   DESCRIPTION: Converts MySQL databases to SQLite\r\n#                Triggers are not converted\r\n#  REQUIREMENTS: mysqldump, Perl and module SQL::Translator, SQLite \r\n#===============================================================================\r\nif [ \"$#\" = 2 ]; then\r\n    USER=\"$2\"    \r\nelse\r\n    echo \"Usage: $0  \"\r\n    exit\r\nfi\r\nif [ -s $1.db ]; then\r\n    read -p \"File <$1.db> exists. Overwrite? [y|n] \" ANS\r\n    if [ \"$ANS\" = \"y\" ] || [ \"$ANS\" = \"Y\" ] ; then\r\n        rm $1.db \r\n    else \r\n        echo \"*** Aborting...\"\r\n        exit\r\n    fi\r\nfi   \r\n# extracts the necessary structure for SQLite:\r\nmysqldump --skip-triggers --skip-add-locks --routines --no-data --compatible=ansi \\\r\n    --compact -u $USER --password $1 > \/tmp\/$1_$$_str.sql\r\n# verify\r\nif [ ! -s \/tmp\/$1_$$_str.sql ]; then\r\n    echo \"*** There are some problem with the dump. Exiting.\"\r\n    exit\r\nfi\r\n# translates MySQL syntax structure to SQLite using the script \"sqlt\" of the\r\n# perl module SQL::Translator (that corrects the foreign keys, indexes, etc.)\r\nsqlt -f MySQL -t SQLite --show-warnings \/tmp\/$1_$$_str.sql \\\r\n    1> \/tmp\/$1_$$.sqlite 2> \/tmp\/$1_$$_sqlt.log\r\n# verify\r\nif [ ! -s \/tmp\/$1_$$.sqlite ]; then\r\n    echo \"*** There are some problem with the sql translation. Exiting.\"\r\n    exit\r\nfi\r\n# adds statements to allow to load tables with foreign keys:\r\necho \"PRAGMA foreign_keys=OFF;\" >> \/tmp\/$1_$$.sqlite\r\necho \"BEGIN TRANSACTION;\" >> \/tmp\/$1_$$.sqlite\r\n# extracts the data (simple inserts) without locks\/disable keys, \r\n# to be read in versions of SQLite that do not support multiples inserts:\r\nmysqldump --skip-triggers --no-create-db --no-create-info --skip-add-locks \\\r\n    --skip-extended-insert  --compact -u $USER \\\r\n    --password $1 >> \/tmp\/$1_$$.sqlite\r\n# adds statements to finish the transaction:\r\necho \"COMMIT;\" >> \/tmp\/$1_$$.sqlite\r\necho \"PRAGMA foreign_keys=ON;\" >> \/tmp\/$1_$$.sqlite\r\n# correct single quotes in inserts\r\nperl -pi -e ' if (\/^INSERT INTO\/) { s\/\\\\'\\''\/'\\'\\''\/g; } ' \/tmp\/$1_$$.sqlite\r\n# load the sql file and generate the SQLite db with the same name \r\n# of the MySQL database\r\nsqlite3 $1.db < \/tmp\/$1_$$.sqlite 2> \/tmp\/$1_$$sqlite.errlog\r\n# verify\r\nERRORS=`cat \/tmp\/$1_$$sqlite.errlog | wc -l`\r\nif [ $ERRORS = 0 ]; then\r\n    echo \"* Conversion complete. Verify the file < $1.db >\"\r\n    rm \/tmp\/$1_$$*\r\nelse\r\n    echo \"*** There are some problem. Verify the files < \/tmp\/$1_$$* >\"\r\nfi\r\n<\/code><\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
Apr\u00e8s quelques minutes heures de batailles, enfin un script qui fonctionne pour convertir votre sch\u00e9ma + vos data Mysql vers Sqlite. Merci \u00e0 nfuria sur SOF Je pensais la conversion simple via une option directe dans mysqldump… Pas du tout, … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-993","post","type-post","status-publish","format-standard","hentry","category-dev"],"_links":{"self":[{"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/posts\/993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/comments?post=993"}],"version-history":[{"count":8,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/posts\/993\/revisions"}],"predecessor-version":[{"id":998,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/posts\/993\/revisions\/998"}],"wp:attachment":[{"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/media?parent=993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/categories?post=993"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.dotmana.com\/weblog\/wp-json\/wp\/v2\/tags?post=993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}