Subversion Repositories ALCASAR

Rev

Details | Last modification | View Log

Rev Author Line No. Line
3202 rexy 1
#!/bin/bash
2
#
3
# $Id: alcasar-migration-3.2.0_dbStructure.sh 2798 2020-04-07 09:01:01Z rexy $
4
#
5
# alcasar-migration_db-3.2.0.sh
6
# by Tom HOUDAYER & Richard REY (Rexy)
7
#
8
# This script is distributed under the Gnu General Public License (GPL)
9
#
10
# Migrate database structure to ALCASAR 3.2.0
11
# Changes:
12
# - Set database engine of radius tables to InnoDB
13
# - Set column names in lowercase in radius tables
14
# - Set index names in lowercase in radius tables
15
# - Set RADIUS attribute length to 64 characters
16
 
17
PASSWD_FILE="/root/ALCASAR-passwords.txt"
18
DB_PASS=$(cat $PASSWD_FILE | grep ^db_root= | cut -d'=' -f2-)
19
 
20
DRY_RUN=false
21
 
22
if [ $# -eq 1 ] && [ "$1" == "--simulation" ]; then
23
	DRY_RUN=true
24
fi
25
 
26
db_query () {
27
	if $DRY_RUN && [[ ! "$1" =~ ^'SELECT ' ]]; then
28
		echo "[SQL] request: \"$1\""
29
	else
30
		mysql -u root -p"$DB_PASS" -D radius -e "$1" -Ns
31
		[ $? -ne 0 ] && echo "[SQL] ERROR (\"$1\")"
32
	fi
33
}
34
 
35
 
36
# Set database engine of radius tables to InnoDB
37
db_res=$(db_query "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'radius' AND ENGINE != 'InnoDB';")
38
if [ -n "$db_res" ]; then
39
	while read -r tableName; do
40
		db_query "ALTER TABLE $tableName ENGINE = InnoDB;"
41
	done <<< "$db_res"
42
fi
43
 
44
# Set column names in lowercase in radius tables
45
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]';")
46
if [ -n "$db_res" ]; then
47
	while read -r line; do
48
		columnName=$(echo "$line" | cut -f1)
49
		tableName=$(echo "$line"  | cut -f2)
50
		columnType=$(echo "$line" | cut -f3)
51
		columnNameLower=${columnName,,}
52
		db_query "ALTER TABLE $tableName CHANGE $columnName $columnNameLower $columnType;"
53
	done <<< "$db_res"
54
fi
55
 
56
# Set index names in lowercase in radius tables
57
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;")
58
if [ -n "$db_res" ]; then
59
	while read -r line; do
60
		indexName=$(echo "$line"    | cut -f1)
61
		tableName=$(echo "$line"    | cut -f2)
62
		indexColumns=$(echo "$line" | cut -f3)
63
		indexNameLower=${indexName,,}
64
		db_query "ALTER TABLE $tableName DROP INDEX $indexName, ADD INDEX $indexNameLower ($indexColumns);"
65
	done <<< "$db_res"
66
fi
67
 
68
# Set RADIUS attribute length to 64 characters
69
db_query "ALTER TABLE radacct       MODIFY COLUMN acctuniqueid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
70
db_query "ALTER TABLE radcheck      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
71
db_query "ALTER TABLE radreply      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
72
db_query "ALTER TABLE radgroupcheck MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
73
db_query "ALTER TABLE radgroupreply MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
74
 
75
# Fix potential bugs
76
db_query "UPDATE radreply      SET attribute = 'Alcasar-Status-Page-Must-Stay-Open' WHERE attribute = 'Alcasar-Status-Page-Must-Stay-Op';"
77
db_query "UPDATE radgroupreply SET attribute = 'Alcasar-Status-Page-Must-Stay-Open' WHERE attribute = 'Alcasar-Status-Page-Must-Stay-Op';"
78
db_query "ALTER TABLE mtotacct DROP COLUMN mtotacctid;"
79
db_query "ALTER TABLE mtotacct ADD COLUMN mtotacctid bigint(21) AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;"
80
db_query "ALTER TABLE totacct DROP COLUMN totacctid;"
81
db_query "ALTER TABLE totacct ADD COLUMN totacctid bigint(21) AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;"