Migate from SQLite to MySQL - script for 6+ versions (

sqlite2mysql_data_6+.sh

#!/bin/sh
# =======================================================================================
# full credits to seafile-team AND Lonsarg https://forum.seafile.com/users/Lonsarg
# 
# basefile from seafile-team: https://manual.seafile.com/deploy/sqlite_to_mysql.sh
# became https://www.dropbox.com/s/2gizfzelgyf23fu/sqlite2mysql_data_6%2B.sh?dl=0
# =======================================================================================
#
# This shell script uses sqlite3 commands to export data only(no schema) from seafile sqlite3 installation
#
# Setup:
#
#  1. Move this file to the top directory of your Seafile
#     installation path (e.g. /data/haiwen).
#  2. Run: ./sqlite2mysql_data.sh
#  3. Three files(ccnet-db-data.sql, seafile-db-data.sql, seahub-db-data.sql) are created.
#  4. Loads these files to your existing MySQL databases
#     example: (mysql> source ccnet-db-data.sql)
#
 
#Function that generates INSERT statements for all data in given database
#Parameter1 = database_path
Sqlite2MysqlData () {
    echo "SET FOREIGN_KEY_CHECKS=0;"
    for TABLE in $(sqlite3 $1 .tables)
    do
        #delete any old data
        echo "DELETE FROM \`${TABLE}\`;"
        #get columns and insert ``, around every one
        COLS=$(sqlite3 $1 "PRAGMA table_info('${TABLE}');" | cut -d'|' -f2 | sed 's/^/`/'  | sed 's/$/`, /' )
        #remove comma from last one
        COLS_PURE=$(echo $COLS | sed 's/.$//')
        #generate insert statemets (via "echoMultipleCommands" support by sqlite3), does not include column names
        echo -e ".mode insert\nselect * from '${TABLE}';" | sqlite3 $1 |
        #replace 3rd word with table name + columns from above:  TableName(`col1`, `col2`, `col3`, ...)
        sed "s/[^ ]*[^ ]/\`${TABLE}\` (${COLS_PURE})/3"
    done
    echo "SET FOREIGN_KEY_CHECKS=1;"
}
 
CCNET_DB='ccnet-db-data.sql'
SEAFILE_DB='seafile-db-data.sql'
SEAHUB_DB='seahub-db-data.sql'
 
########## ccnet
 
seafile_path=$(pwd)
if [ -f "${seafile_path}/ccnet/ccnet.conf" ]; then
    USER_MGR_DB=${seafile_path}/ccnet/PeerMgr/usermgr.db
    GRP_MGR_DB=${seafile_path}/ccnet/GroupMgr/groupmgr.db
else
    echo "${seafile_path}/ccnet/ccnet.conf does not exists."
    read -p "Please provide your ccnet.conf path(e.g. /data/haiwen/ccnet/ccnet.conf): " ccnet_conf_path
    if [ -f ${ccnet_conf_path} ]; then
        USER_MGR_DB=$(dirname "${ccnet_conf_path}")/PeerMgr/usermgr.db
        GRP_MGR_DB=$(dirname "${ccnet_conf_path}")/GroupMgr/groupmgr.db
    else
        echo "${ccnet_conf_path} does not exists, quit."
        exit 1
    fi
fi
rm -rf ${CCNET_DB}
echo "Start export ccnet data from user"
Sqlite2MysqlData ${USER_MGR_DB} > ${CCNET_DB}
echo "Start export ccnet data from group"
Sqlite2MysqlData ${GRP_MGR_DB} >> ${CCNET_DB}
echo "Done export ccnet data"
 
########## seafile
 
if [ -f "${seafile_path}/seafile-data/seafile.db" ]; then
    SEAFILE_SQLITE_DB=${seafile_path}/seafile-data/seafile.db
else
    echo "${seafile_path}/seafile-data/seafile.db does not exists."
    read -p "Please provide your seafile.db path(e.g. /data/haiwen/seafile-data/seafile.db): " seafile_db_path
    if [ -f ${seafile_db_path} ];then
        SEAFILE_SQLITE_DB=${seafile_db_path}
    else
        echo "${seafile_db_path} does not exists, quit."
        exit 1
    fi
fi
 
rm -rf ${SEAFILE_DB}
 
echo "Start export seafile data"
Sqlite2MysqlData ${SEAFILE_SQLITE_DB} > ${SEAFILE_DB}
echo "Done export seafile data"
 
########## seahub
if [ -f "${seafile_path}/seahub.db" ]; then
    SEAHUB_SQLITE_DB=${seafile_path}/seahub.db 
else
    echo "${seafile_path}/seahub.db does not exists."
    read -p "Please prove your seahub.db path(e.g. /data/haiwen/seahub.db): " seahub_db_path
    if [ -f ${seahub_db_path} ]; then
        SEAHUB_SQLITE_DB=${seahub_db_path}
    else
        echo "${seahub_db_path} does not exists, quit."
        exit 1
    fi
fi
 
rm -rf ${SEAHUB_DB}
 
echo "Start export seahub data"
Sqlite2MysqlData ${SEAHUB_SQLITE_DB} > ${SEAHUB_DB}
echo "Done export seahub data"