0,0 → 1,78 |
#!/bin/bash |
# |
# $Id$ |
# |
# alcasar-migration-3.2.0_dbStructure.sh |
# by Tom HOUDAYER |
# |
# 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 caracters |
|
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 caracters |
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 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 a potential bug |
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';" |
Property changes: |
Added: svn:eol-style |
+LF |
\ No newline at end of property |
Added: svn:executable |
+* |
\ No newline at end of property |
Added: svn:keywords |
+Id |
\ No newline at end of property |