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;"