[17/6/1] 改行コード(char(10)/char(13))を含むデータに対応できるよう修正
WebアプリのバックエンドをSQLiteからMySQLに移行するための備忘録です。
SQLiteでダンプしたものを何も考えずにMySQLに突っ込むとFOREIGN KEYなどの制約が抜けてしまう状況だったので、migrationはアプリ付属の初期化スクリプトを使い、データだけをSQLiteから移行するという方針です。
動作環境
sh: FreeBSD 11の/bin/sh
sqlite3: 3.18.0
MySQL: 5.6
データ抽出スクリプト
SQLiteのDBからMySQL投入用のSQLを生成するスクリプトです。こちらはstack overflowから拝借してきたものを少し修正しました。
元のスクリプトの
- '\nINSERT INTO table'という文字列を含むデータがあると上手くいかない
という制約は残っていますが、
- カラム名に特殊な文字があるとダメ
という制約の方は対策してあります。
sqlite2mysql.sh
#!/bin/sh
SQLITE=sqlite3
if [ -z "$1" ] ; then
echo usage: $0 sqlite3.db
exit
fi
DB="$1"
TABLES=$("$SQLITE" "$DB" .tables)
#echo "-- $TABLES"
echo 'BEGIN;'
echo 'SET FOREIGN_KEY_CHECKS = 0;'
for TABLE in $TABLES ; do
echo
echo "-- $TABLE:";
echo "TRUNCATE TABLE $TABLE;"
COLS=$("$SQLITE" "$DB" "pragma table_info($TABLE)" |
cut -d'|' -f2 )
COLS_CS=$(echo $COLS | sed 's/ /","/g')
COLS_CS2=$(echo $COLS | sed 's/ /`,`/g')
echo -e ".mode insert\nselect \"$COLS_CS\" from $TABLE;\n" |
"$SQLITE" "$DB" |
sed "s/^INSERT INTO \"table\"/INSERT INTO $TABLE (\`$COLS_CS2\`)/" |
sed "s/'||//g" | sed "s/||'//g" | sed 's/char(13)|\{0,2\}/\\r/g' | sed 's/char(10)|\{0,2\}/\\n/g'
done
echo 'SET FOREIGN_KEY_CHECKS = 1;'
echo 'COMMIT;';
データ抽出
例として、Roundcubeのデータの移行手順を記述します 1。
データ抽出
% sh sqlite2mysql.sh roundcube.db > rc_data.sql
で、rc_data.sqlの内容は以下のようになります。
rc_data.sqlBEGIN; SET FOREIGN_KEY_CHECKS = 0; -- cache: TRUNCATE TABLE cache; -- contactgroupmembers: TRUNCATE TABLE contactgroupmembers; INSERT INTO contactgroupmembers (`contactgroup_id`,`contact_id`,`created`) VALUES(1,10,'2017-05-24 07:00:00'); : INSERT INTO identities (`identity_id`,`user_id`,`changed`,`del`,`standard`,`name`,`organization`,`email`,`reply-to`,`bcc`,`signature`,`html_signature`) VALUES(...); SET FOREIGN_KEY_CHECKS = 1; COMMIT;
データ投入
出来上がったrc_data.sqlをMySQLに投入します。[DB名]のデータベースの初期化は済ませてあるものとします。
データ投入
% mysql -u [DBユーザ] -p [DB名] < rc_data.sql
途中でエラーが出たらロールバックするので、1行ずつコツコツと修正します。
各テーブルのINSERT直前にTRUNCATEしているので、やり直しもできます。
以上。
-
これが目的だったんですが(^^; ↩