Subversion Repositories ALCASAR

Compare Revisions

No changes between revisions

Ignore whitespace Rev 3201 → Rev 3202

/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