WebSVN – ALCASAR – Path Comparison – / – /scripts/alcasar-db-migrations/ Rev 3201 and /scripts/alcasar-db-migrations/ Rev 3202

Ignore whitespace Rev 3201 → Rev 3202

/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 → 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 → 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 → 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 → 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

Subversion Repositories ALCASAR

Compare Revisions

Ignore whitespace Rev 3201 → Rev 3202