migrate_from_symfony_3_to_5.sh

#!/bin/bash
BASE_DIR=$(realpath "$(dirname $0)/../..")
source $BASE_DIR/.env

BACKUP_DB="${MYSQL_DATABASE}_BACKUP_3_TO_5"
mysql_cmd="docker-compose exec mysql mysql -u root -p$MYSQL_ROOT_PASSWORD"

#
# Step 1 backup the db
#

$mysql_cmd -e "use $BACKUP_DB"
backup=$?

if [ "$backup" -ne 0 ]; then
    echo "Backup DB does not exists, saving the current DB"
    # The backup db does not exists
    echo "Creating DB $BACKUP_DB"
    $mysql_cmd -e "CREATE DATABASE $BACKUP_DB"
    echo "Saving $MYSQL_DATABASE to $BACKUP_DB"
    docker-compose exec mysql mysqldump -u root -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE |\
        grep -v Warning |
        docker exec -i $(docker-compose ps -q mysql) mysql -u root -p$MYSQL_ROOT_PASSWORD $BACKUP_DB
    ret=$?
    if [ $ret -ne 0 ]; then
        echo "Backup failed, aborting"
        exit $ret
    fi
    echo "Backup done"
else
    echo "A previous backup exists, skipping backup step"
fi

echo -e "Please check the integrity of the backup databse $BACKUP_DB then type 'yes' to continue. \nThe next step will erase your database if the backup is incomplete you WILL LOOSE ALL YOUR DATA\n"
read next
if [ "$next" != 'yes' ]; then
    echo "Aborting as you did not type yes"
    exit 1
fi

#
# Step 2 Reload db with initial data
#

docker-compose exec memorekall-member composer reload-db

#
# Step 3 empty the newly generated database and recopy data from the backup
#

# Columns renaming in the form pattern/replacement/sedopts
declare -A COLUMNS_REPLACEMENTS
COLUMNS_REPLACEMENTS[user]="roles,\(.*\)credential_expired/'[\"USER_ROLE\"]' as roles,\1credentials_expired as credential_expired/"
# Old capsule table does not have a password field
COLUMNS_REPLACEMENTS[capsule]='password/SUBSTRING(edition_link, -40) as password/'

TABLES="user reset_password_request capsule doctrine_migration_versions editeur_capsule"
echo "SET foreign_key_checks = 0;" > $$.sql
for table in $TABLES; do
    COLUMNS=$($mysql_cmd --batch --disable-column-names \
        -e "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$table' AND TABLE_SCHEMA='$MYSQL_DATABASE'" |\
        grep -v "Warning" | tr '\r\n' ', ' | sed 's/, $//'
    )
    if [ ! -z "${COLUMNS_REPLACEMENTS[$table]}" ]; then
        COLUMNS_OLD=$(echo $COLUMNS | sed "s/${COLUMNS_REPLACEMENTS[$table]}")
    else
        COLUMNS_OLD=$COLUMNS
    fi
    echo "DELETE FROM $MYSQL_DATABASE.$table;" >> $$.sql
    echo "INSERT INTO $MYSQL_DATABASE.$table ($COLUMNS) SELECT $COLUMNS_OLD FROM $BACKUP_DB.$table;" >> $$.sql
done
# Ensure that dt_maj is not NULL
echo "UPDATE $MYSQL_DATABASE.capsule SET dt_maj=dt_crea WHERE dt_maj IS NULL;" >> $$.sql
echo "SET foreign_key_checks = 1;" >> $$.sql
cat $$.sql | docker exec -i $(docker-compose ps -q mysql) mysql -u root -p$MYSQL_ROOT_PASSWORD
echo "All done"
rm $$.sql