/scripts/alcasar-db-migrations/alcasar-migration-3.2.0_dbStructure.sh |
---|
File deleted |
Property changes: |
Deleted: svn:eol-style |
-LF |
\ No newline at end of property |
Deleted: svn:executable |
-* |
\ No newline at end of property |
Deleted: svn:keywords |
-Id |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration-3.3.0_dbRadiusAttrs.sh |
---|
File deleted |
Property changes: |
Deleted: svn:eol-style |
-LF |
\ No newline at end of property |
Deleted: svn:executable |
-* |
\ No newline at end of property |
Deleted: svn:keywords |
-Id |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration-3.3.1_dbRadiusAttrs.sh |
---|
File deleted |
Property changes: |
Deleted: svn:eol-style |
-LF |
\ No newline at end of property |
Deleted: svn:executable |
-* |
\ No newline at end of property |
Deleted: svn:keywords |
-Id |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration_db-3.2.0.sh |
---|
0,0 → 1,81 |
#!/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;" |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |
Added: svn:executable |
+* |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration_db-3.3.0.sh |
---|
0,0 → 1,103 |
#!/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 |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |
Added: svn:executable |
+* |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration_db-3.3.1.sh |
---|
0,0 → 1,62 |
#!/bin/bash |
# |
# $Id: alcasar-migration-3.3.1_dbRadiusAttrs.sh 2664 2018-11-19 17:35:12Z tom.houdayer $ |
# |
# alcasar-migration_db-3.3.1.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 |
+native |
\ No newline at end of property |
Added: svn:executable |
+* |
\ No newline at end of property |
/scripts/alcasar-db-migrations/alcasar-migration_db-3.7.0.sh |
---|
0,0 → 1,79 |
#!/bin/bash |
# |
# alcasar-migration_db-3.7.0.sh |
# by Richard REY (Rexy) |
# |
# This script is distributed under the Gnu General Public License (GPL) |
# |
# Migrate database structure to ALCASAR 3.7.0 |
# Changes: |
# - Update radacct table |
# - remove 'groupname', 'acctstartdelay', 'acctstopdelay' & 'xascendsessionsvrkey' |
# - add 'acctupdatetime', 'acctinterval', 'framedipv6address', 'framedipv6prefix', 'framedinterfaceid', 'delegatedipv6prefix' & 'class' |
# - change 'acctsessionid', 'acctuniqueid', 'nasportid', 'connectinfo start' & 'connectinfo stop' |
# - KEY add: 'framedipv6address', 'framedipv6prefix', 'framedinterfaceid', 'delegatedipv6prefix', acctinterval & class |
# - Update radpostauth table |
# - add 'class' |
# - KEY add: 'class' & 'username' |
# - add nas table |
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 |
} |
# Update radacct table attributes |
db_query "ALTER TABLE radacct DROP COLUMN IF EXISTS groupname, DROP COLUMN IF EXISTS acctstartdelay, DROP COLUMN IF EXISTS acctstopdelay, DROP COLUMN IF EXISTS xascendsessionsvrkey;" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS acctupdatetime datetime DEFAULT NULL;" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS acctinterval int(12) DEFAULT NULL;" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS framedipv6address varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS framedipv6prefix varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS framedinterfaceid varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS delegatedipv6prefix varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct ADD COLUMN IF NOT EXISTS class varchar(64) DEFAULT NULL;" |
db_query "ALTER TABLE radacct MODIFY COLUMN acctsessionid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct MODIFY COLUMN acctuniqueid varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct MODIFY COLUMN nasportid varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '';" |
db_query "ALTER TABLE radacct MODIFY COLUMN connectinfo_start varchar(128) COLLATE utf8_bin DEFAULT NULL;" |
db_query "ALTER TABLE radacct MODIFY COLUMN connectinfo_stop varchar(128) COLLATE utf8_bin DEFAULT NULL;" |
db_query "ALTER TABLE radacct ADD INDEX (framedipv6address);" |
db_query "ALTER TABLE radacct ADD INDEX (framedipv6prefix);" |
db_query "ALTER TABLE radacct ADD INDEX (framedinterfaceid);" |
db_query "ALTER TABLE radacct ADD INDEX (delegatedipv6prefix);" |
db_query "ALTER TABLE radacct ADD INDEX (acctinterval);" |
db_query "ALTER TABLE radacct ADD INDEX (class);" |
# update radpostauth table |
db_query "ALTER TABLE radpostauth ADD COLUMN IF NOT EXISTS class varchar(64) DEFAULT NULL;" |
db_query "ALTER TABLE radpostauth ADD INDEX (class);" |
# add nas table |
db_query "CREATE TABLE IF NOT EXISTS nas ( |
id int(10) NOT NULL AUTO_INCREMENT, |
nasname varchar(128) NOT NULL, |
shortname varchar(32), |
type varchar(30) DEFAULT 'other', |
ports int(5), |
secret varchar(60) DEFAULT 'secret' NOT NULL, |
server varchar(64), |
community varchar(50), |
description varchar(200) DEFAULT 'RADIUS Client', |
PRIMARY KEY (id), |
KEY nasname (nasname) |
);" |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |
Added: svn:executable |
+* |
\ No newline at end of property |