Subversion Repositories ALCASAR

#!/bin/bash
#
# $Id: alcasar-migration-3.3.0_dbRadiusAttrs.sh 2664 2018-11-19 17:35:12Z tom.houdayer $
#
# alcasar-migration_db-3.3.0.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