Scripts for migrating CoreProtect database.db data to MySQL server - https://ralph.sh/coreprotect-sqlite-de20a

co_import.sh

#!/bin/bash

# co_import.sh -- Import migrated CoreProtect data into a MySQL server.
# Copyright (C) 2019 Ralph Drake

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <https://www.gnu.org/licenses/>.

set -e

if ! command -v pv >/dev/null 2>&1; then
    echo "Please install pv with \`apt-get install pv\`"
    exit 1
fi

read -p "Enter database name: " MYSQL_DB
read -p "Enter MySQL username: " MYSQL_USER
read -p "Enter MySQL password: " MYSQL_PASSWORD

for FILE in migrate/*.sql; do
    LINECOUNT=`wc -l $FILE | cut -f1 -d' '`
    echo "Processing $LINECOUNT statements from $FILE"

    # -l makes pv count lines processed rather than bytes
    # -s $LINECOUNT feeds pv the total number of lines for accurate output
    pv -l -s $LINECOUNT $FILE | mysql -u "$MYSQL_USER" "-p$MYSQL_PASS" "$MYSQL_DB"
done

# TODO: I could add the option to run CHECK TABLE <table> over the new data
#       If you want to manually check, log in to your database and run
#       CHECK TABLE <table> for the tables you want to check.
# NB: CHECK TABLE <table> will probably time out in PHPMyAdmin.

echo "Finished importing migrated data."

co_migrate.sh

#!/bin/bash

# co_migrate.sh -- migrate data from CoreProtect database.db to SQL server
# Copyright (C) 2019 Ralph Drake

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <https://www.gnu.org/licenses/>.

set -e

DATABASE_FILE=$* # database.db

mkdir -p migrate

if [ -z "$DATABASE_FILE" ]; then
    echo "Usage: $0 database.db" && exit
fi
if [ ! -f "$DATABASE_FILE" ]; then
    echo "File \`$DATABASE_FILE' not found!" && exit
fi

# CoreProtect table names
CO_TABLES=(
    co_art_map
    co_container
    co_session
    co_version
    co_block
    co_database_lock
    co_sign
    co_world
    co_blockdata_map
    co_entity
    co_skull
    co_chat
    co_entity_map
    co_user
    co_command
    co_material_map
    co_username_log
    co_item
)

# Export SQLite database using insert mode
echo "Exporting data to migration folder..."
for table in "${CO_TABLES[@]}"; do
    echo "  \"SELECT * FROM $table;\" > \"migrate/$table.sql\""

    # Set headers for database encoding
    echo "SET NAMES utf8;" > migrate/$table.sql
    echo "SET character_set_client = utf8;" >> migrate/$table.sql
    echo "SET character_set_server = utf8;" >> migrate/$table.sql

    echo "TRUNCATE TABLE $table;" >> migrate/$table.sql

    # We can safely disable checks since our data is already sanitary
    # https://dba.stackexchange.com/a/98815
    echo "SET autocommit = 0;" >> migrate/$table.sql
    echo "SET unique_checks = 0;" >> migrate/$table.sql
    echo "SET foreign_key_checks = 0;" >> migrate/$table.sql

    sqlite3 -header "$DATABASE_FILE" -cmd ".mode insert $table" \
        "SELECT * FROM $table;" >> migrate/$table.sql

    # Commit imported data since autocommit == 0
    echo "COMMIT;" >> migrate/$table.sql

    # Reset the settings we changed for an import speed boost
    echo "SET autocommit = 1;" >> migrate/$table.sql
    echo "SET unique_checks = 1;" >> migrate/$table.sql
    echo "SET foreign_key_checks = 1;" >> migrate/$table.sql
done
echo -e "Done.\n"

echo "Fixing rowid column name..."
sed -i "s/co_entity(id,/co_entity(rowid,/g" migrate/co_entity.sql
sed -i "s/co_skull(id,/co_skull(rowid,/g" migrate/co_skull.sql
sed -i "s/co_user(id,/co_user(rowid,/g" migrate/co_user.sql
sed -i "s/co_username_log(id,/co_username_log(rowid,/g" migrate/co_username_log.sql
echo -e "Done.\n"

# Perform migration actions on our separate SQL files.
for table in "${CO_TABLES[@]}"; do
    echo "Migrating migrate/$table.sql"

    echo "... Finxing \"action\" quotation issue..."
    sed -i 's/"action"/action/g' migrate/$table.sql

    echo "... Fixing spacing..."
    sed -i "s/INSERT INTO $table(/INSERT INTO $table (/g" migrate/$table.sql
    sed -i "s/VALUES(/VALUES (/g" migrate/$table.sql

    echo "... Escaping special characters..."
    sed -i 's/\\/\\\\/g' migrate/$table.sql
done

echo -e "Finished migrating all tables.\n"

echo "You can now import data from migrage/*.sql into your MySQL database."
echo "Add your MySQL details to config.yml and restart your server beforehand."
echo "After doing so, see co_import.sh for import details."