Subversion Repositories ALCASAR

Compare Revisions

No changes between revisions

Ignore whitespace Rev 2663 → Rev 2664

/scripts/alcasar-migrations/alcasar-migration-3.2.0_dbStructure.sh
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
/scripts/alcasar-migrations/alcasar-migration-3.3.0_dbRadiusAttrs.sh
0,0 → 1,103
#!/bin/bash
#
# $Id$
#
# alcasar-migration-3.3.0_dbRadiusAttrs.sh
# by Tom HOUDAYER
#
# This script is distributed under the Gnu General Public License (GPL)
#
# Migrate user database to ALCASAR 3.3.0
# Changes:
# - Explode "Filter-Id" RADIUS attribute into "Alcasar-Filter", "Alcasar-Protocols-Filter", "Alcasar-Status-Page-Must-Stay-Open" and "Alcasar-Imputability-Warning"
# - Rename "ChilliSpot-*" RADIUS attribute to "CoovaChilli-*"
# - Rename "Max-All-Session" RADIUS attribute to "Alcasar-Expire-After"
 
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
}
 
for step in $(seq 1 2); do
if [ $step -eq 1 ]; then
tableNameCheck='radcheck'
tableNameReply='radreply'
loginName='username'
else
tableNameCheck='radgroupcheck'
tableNameReply='radgroupreply'
loginName='groupname'
fi
 
# Explode "Filter-Id" RADIUS attribute into "Alcasar-Filter", "Alcasar-Protocols-Filter", "Alcasar-Status-Page-Must-Stay-Open" and "Alcasar-Imputability-Warning"
db_res=$(db_query "SELECT $loginName, value FROM $tableNameReply WHERE attribute = 'Filter-Id';")
if [ -n "$db_res" ]; then
echo "$(echo "$db_res" | wc -l) \"Filter-Id\" found in table \"$tableNameReply\"."
while read -r line; do
login=$(echo "$line" | cut -f1)
filterId=$(echo "$line" | cut -f2)
echo " $login ($filterId)..."
 
if [ ${filterId:5:1} == '1' ]; then # Filter: HAVP_WL
filter='4'
elif [ ${filterId:6:1} == '1' ]; then # Filter: HAVP_BL
filter='3'
elif [ ${filterId:7:1} == '1' ]; then # Filter: HAVP
filter='2'
else # Filter: NOT_FILTERED
filter=''
fi
[ ! -z "$filter" ] && db_query "INSERT INTO $tableNameReply ($loginName, attribute, value, op) VALUES ('$login','Alcasar-Filter','$filter', '=');"
 
if [ ${filterId:2:1} == '1' ]; then # FilterProto: PROFILE 3 (Custom)
filterProto='4';
elif [ ${filterId:1:1} == '1' ]; then # FilterProto: PROFILE 2 (WEB + Mail + Remote access)
filterProto='3';
elif [ ${filterId:0:1} == '1' ]; then # FilterProto: PROFILE 1 (WEB)
filterProto='2';
else # FilterProto: PROFILE 0 (Not filtered)
filterProto='';
fi
[ ! -z "$filterProto" ] && db_query "INSERT INTO $tableNameReply ($loginName, attribute, value, op) VALUES ('$login','Alcasar-Protocols-Filter','$filterProto', '=');"
 
if [ ${filterId:4:1} == '1' ]; then # status_open_required
statusOpenRequired='2';
else
statusOpenRequired='';
fi
[ ! -z "$statusOpenRequired" ] && db_query "INSERT INTO $tableNameReply ($loginName, attribute, value, op) VALUES ('$login','Alcasar-Status-Page-Must-Stay-Open','$statusOpenRequired', '=');"
 
if [ ${filterId:3:1} == '1' ]; then # imputability warning
imputabilityWarning='1';
else
imputabilityWarning='';
fi
[ ! -z "$imputabilityWarning" ] && db_query "INSERT INTO $tableNameReply ($loginName, attribute, value, op) VALUES ('$login','Alcasar-Imputability-Warning','$imputabilityWarning', '=');"
 
db_query "DELETE FROM $tableNameReply WHERE attribute = 'Filter-Id' AND $loginName = '$login';"
done <<< "$db_res"
fi
 
# Rename "ChilliSpot-*" RADIUS attribute to "CoovaChilli-*"
db_query "UPDATE $tableNameReply SET attribute = 'CoovaChilli-Max-Input-Octets' WHERE attribute = 'ChilliSpot-Max-Input-Octets';"
db_query "UPDATE $tableNameReply SET attribute = 'CoovaChilli-Max-Output-Octets' WHERE attribute = 'ChilliSpot-Max-Output-Octets';"
db_query "UPDATE $tableNameReply SET attribute = 'CoovaChilli-Max-Total-Octets' WHERE attribute = 'ChilliSpot-Max-Total-Octets';"
db_query "UPDATE $tableNameReply SET attribute = 'CoovaChilli-Bandwidth-Max-Up' WHERE attribute = 'ChilliSpot-Bandwidth-Max-Up';"
db_query "UPDATE $tableNameReply SET attribute = 'CoovaChilli-Bandwidth-Max-Down' WHERE attribute = 'ChilliSpot-Bandwidth-Max-Down';"
 
# Rename "Max-All-Session" RADIUS attribute to "Alcasar-Expire-After"
db_query "UPDATE $tableNameCheck SET attribute = 'Alcasar-Expire-After' WHERE attribute = 'Max-All-Session';"
done
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
/scripts/alcasar-migrations/alcasar-migration-3.3.1_dbRadiusAttrs.sh
0,0 → 1,62
#!/bin/bash
#
# $Id$
#
# alcasar-migration-3.3.1_dbRadiusAttrs.sh
# by Tom HOUDAYER
#
# This script is distributed under the Gnu General Public License (GPL)
#
# Migrate user database to ALCASAR 3.3.1
# Changes:
# - Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck
# - Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes
 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
DB_PASS=$(grep ^db_root= $PASSWD_FILE | 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" -Bs
[ $? -ne 0 ] && echo "[SQL] ERROR (\"$1\")"
fi
}
 
for step in $(seq 1 2); do
if [ $step -eq 1 ]; then
tableNameCheck='radcheck'
tableNameReply='radreply'
loginName='username'
else
tableNameCheck='radgroupcheck'
tableNameReply='radgroupreply'
loginName='groupname'
fi
 
# Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck
db_res=$(db_query "SELECT $loginName, value FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';")
if [ -n "$db_res" ]; then
echo "$(echo "$db_res" | wc -l) \"CoovaChilli-Max-Total-Octets\" found in table \"$tableNameReply\"."
while read -r line; do
login=$(echo "$line" | cut -f1)
value=$(echo "$line" | cut -f2)
echo " $login..."
 
db_query "INSERT INTO $tableNameCheck ($loginName, attribute, value, op) VALUES ('$login','CoovaChilli-Max-Total-Octets','$value', ':=');"
 
done <<< "$db_res"
db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';"
fi
 
# Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes
db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Input-Octets';"
db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Output-Octets';"
done
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