Subversion Repositories ALCASAR

#!/bin/bash
#
# $Id: alcasar-migration-3.2.0_dbStructure.sh 2798 2020-04-07 09:01:01Z rexy $
#
# alcasar-migration_db-3.2.0.sh
# by Tom HOUDAYER & Richard REY (Rexy)
#
# This script is distributed under the Gnu General Public License (GPL)
#
# Migrate database structure to ALCASAR 3.2.0
# Changes:
# - Set database engine of radius tables to InnoDB
# - Set column names in lowercase in radius tables
# - Set index names in lowercase in radius tables
# - Set RADIUS attribute length to 64 characters

PASSWD_FILE="/root/ALCASAR-passwords.txt"
DB_PASS=$(cat $PASSWD_FILE | grep ^db_root= | cut -d'=' -f2-)

DRY_RUN=false

if [ $# -eq 1 ] && [ "$1" == "--simulation" ]; then
        DRY_RUN=true
fi

db_query () {
        if $DRY_RUN && [[ ! "$1" =~ ^'SELECT ' ]]; then
                echo "[SQL] request: \"$1\""
        else
                mysql -u root -p"$DB_PASS" -D radius -e "$1" -Ns
                [ $? -ne 0 ] && echo "[SQL] ERROR (\"$1\")"
        fi
}


# Set database engine of radius tables to InnoDB
db_res=$(db_query "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'radius' AND ENGINE != 'InnoDB';")
if [ -n "$db_res" ]; then
        while read -r tableName; do
                db_query "ALTER TABLE $tableName ENGINE = InnoDB;"
        done <<< "$db_res"
fi

# Set column names in lowercase in radius tables
db_res=$(db_query "SELECT COLUMN_NAME, TABLE_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'radius' AND TABLE_NAME IN ('mtotacct', 'totacct', 'userinfo') AND BINARY COLUMN_NAME REGEXP BINARY '[A-Z]';")
if [ -n "$db_res" ]; then
        while read -r line; do
                columnName=$(echo "$line" | cut -f1)
                tableName=$(echo "$line"  | cut -f2)
                columnType=$(echo "$line" | cut -f3)
                columnNameLower=${columnName,,}
                db_query "ALTER TABLE $tableName CHANGE $columnName $columnNameLower $columnType;"
        done <<< "$db_res"
fi

# Set index names in lowercase in radius tables
db_res=$(db_query "SELECT INDEX_NAME, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM ( SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'radius' AND TABLE_NAME IN ('mtotacct', 'totacct', 'userinfo') AND INDEX_NAME != 'PRIMARY' AND BINARY INDEX_NAME REGEXP BINARY '[A-Z]' ORDER BY SEQ_IN_INDEX ) AS indexes GROUP BY TABLE_NAME, INDEX_NAME;")
if [ -n "$db_res" ]; then
        while read -r line; do
                indexName=$(echo "$line"    | cut -f1)
                tableName=$(echo "$line"    | cut -f2)
                indexColumns=$(echo "$line" | cut -f3)
                indexNameLower=${indexName,,}
                db_query "ALTER TABLE $tableName DROP INDEX $indexName, ADD INDEX $indexNameLower ($indexColumns);"
        done <<< "$db_res"
fi

# Set RADIUS attribute length to 64 characters
db_query "ALTER TABLE radacct       MODIFY COLUMN acctuniqueid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
db_query "ALTER TABLE radcheck      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
db_query "ALTER TABLE radreply      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
db_query "ALTER TABLE radgroupcheck MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
db_query "ALTER TABLE radgroupreply MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"

# Fix potential bugs
db_query "UPDATE radreply      SET attribute = 'Alcasar-Status-Page-Must-Stay-Open' WHERE attribute = 'Alcasar-Status-Page-Must-Stay-Op';"
db_query "UPDATE radgroupreply SET attribute = 'Alcasar-Status-Page-Must-Stay-Open' WHERE attribute = 'Alcasar-Status-Page-Must-Stay-Op';"
db_query "ALTER TABLE mtotacct DROP COLUMN mtotacctid;"
db_query "ALTER TABLE mtotacct ADD COLUMN mtotacctid bigint(21) AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;"
db_query "ALTER TABLE totacct DROP COLUMN totacctid;"
db_query "ALTER TABLE totacct ADD COLUMN totacctid bigint(21) AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;"