Subversion Repositories ALCASAR

#!/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'
#     - 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"

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)
);"