WebSVN – ALCASAR – Diff – /scripts/alcasar-db-migrations/alcasar-migration-3.3.1_dbRadiusAttrs.sh


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

Rev 2664	Rev 2752
1	`#!/bin/bash`	1	`#!/bin/bash`
2	`#`	2	`#`
3	`# $Id: alcasar-migration-3.3.1_dbRadiusAttrs.sh 2664 2018-11-19 17:35:12Z tom.houdayer $`	3	`# $Id: alcasar-migration-3.3.1_dbRadiusAttrs.sh 2664 2018-11-19 17:35:12Z tom.houdayer $`
4	`#`	4	`#`
5	`# alcasar-migration-3.3.1_dbRadiusAttrs.sh`	5	`# alcasar-migration-3.3.1_dbRadiusAttrs.sh`
6	`# by Tom HOUDAYER`	6	`# by Tom HOUDAYER`
7	`#`	7	`#`
8	`# This script is distributed under the Gnu General Public License (GPL)`	8	`# This script is distributed under the Gnu General Public License (GPL)`
9	`#`	9	`#`
10	`# Migrate user database to ALCASAR 3.3.1`	10	`# Migrate user database to ALCASAR 3.3.1`
11	`# Changes:`	11	`# Changes:`
12	`# - Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck`	12	`# - Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck`
13	`# - Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes`	13	`# - Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes`
14		14
15	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`	15	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`
16	`DB_PASS=$(grep ^db_root= $PASSWD_FILE \| cut -d'=' -f2-)`	16	`DB_PASS=$(grep ^db_root= $PASSWD_FILE \| cut -d'=' -f2-)`
17		17
18	`DRY_RUN=false`	18	`DRY_RUN=false`
19		19
20	`if [ $# -eq 1 ] && [ "$1" == "--simulation" ]; then`	20	`if [ $# -eq 1 ] && [ "$1" == "--simulation" ]; then`
21	`DRY_RUN=true`	21	`DRY_RUN=true`
22	`fi`	22	`fi`
23		23
24	`db_query () {`	24	`db_query () {`
25	`if $DRY_RUN && [[ ! "$1" =~ ^'SELECT ' ]]; then`	25	`if $DRY_RUN && [[ ! "$1" =~ ^'SELECT ' ]]; then`
26	`echo "[SQL] request: \"$1\""`	26	`echo "[SQL] request: \"$1\""`
27	`else`	27	`else`
28	`mysql -u root -p"$DB_PASS" -D radius -e "$1" -Bs`	28	`mysql -u root -p"$DB_PASS" -D radius -e "$1" -Bs`
29	`[ $? -ne 0 ] && echo "[SQL] ERROR (\"$1\")"`	29	`[ $? -ne 0 ] && echo "[SQL] ERROR (\"$1\")"`
30	`fi`	30	`fi`
31	`}`	31	`}`
32		32
33	`for step in $(seq 1 2); do`	33	`for step in $(seq 1 2); do`
34	`if [ $step -eq 1 ]; then`	34	`if [ $step -eq 1 ]; then`
35	`tableNameCheck='radcheck'`	35	`tableNameCheck='radcheck'`
36	`tableNameReply='radreply'`	36	`tableNameReply='radreply'`
37	`loginName='username'`	37	`loginName='username'`
38	`else`	38	`else`
39	`tableNameCheck='radgroupcheck'`	39	`tableNameCheck='radgroupcheck'`
40	`tableNameReply='radgroupreply'`	40	`tableNameReply='radgroupreply'`
41	`loginName='groupname'`	41	`loginName='groupname'`
42	`fi`	42	`fi`
43		43
44	`# Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck`	44	`# Move "CoovaChilli-Max-Total-Octets" RADIUS attribute from radreply to radcheck`
45	`db_res=$(db_query "SELECT $loginName, value FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';")`	45	`db_res=$(db_query "SELECT $loginName, value FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';")`
46	`if [ -n "$db_res" ]; then`	46	`if [ -n "$db_res" ]; then`
47	`echo "$(echo "$db_res" \| wc -l) \"CoovaChilli-Max-Total-Octets\" found in table \"$tableNameReply\"."`	47	`echo "$(echo "$db_res" \| wc -l) \"CoovaChilli-Max-Total-Octets\" found in table \"$tableNameReply\"."`
48	`while read -r line; do`	48	`while read -r line; do`
49	`login=$(echo "$line" \| cut -f1)`	49	`login=$(echo "$line" \| cut -f1)`
50	`value=$(echo "$line" \| cut -f2)`	50	`value=$(echo "$line" \| cut -f2)`
51	`echo " $login..."`	51	`echo " $login..."`
52		52
53	`db_query "INSERT INTO $tableNameCheck ($loginName, attribute, value, op) VALUES ('$login','CoovaChilli-Max-Total-Octets','$value', ':=');"`	53	`db_query "INSERT INTO $tableNameCheck ($loginName, attribute, value, op) VALUES ('$login','CoovaChilli-Max-Total-Octets','$value', ':=');"`
54		54
55	`done <<< "$db_res"`	55	`done <<< "$db_res"`
56	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';"`	56	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Total-Octets';"`
57	`fi`	57	`fi`
58		58
59	`# Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes`	59	`# Delete "CoovaChilli-Max-Input-Octets" and "CoovaChilli-Max-Output-Octets" RADIUS attributes`
60	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Input-Octets';"`	60	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Input-Octets';"`
61	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Output-Octets';"`	61	`db_query "DELETE FROM $tableNameReply WHERE attribute = 'CoovaChilli-Max-Output-Octets';"`
62	`done`	62	`done`
63		63

Subversion Repositories ALCASAR

(root)/scripts/alcasar-db-migrations/alcasar-migration-3.3.1_dbRadiusAttrs.sh @ 2986 – Rev 2664 → 2752