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 |