Subversion Repositories ALCASAR

Rev

Rev 2664 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log

Rev 2664 Rev 2667
Line 1... Line 1...
1
#!/bin/bash
1
#!/bin/bash
2
#
2
#
3
# $Id: alcasar-migration-3.2.0_dbStructure.sh 2664 2018-11-19 17:35:12Z tom.houdayer $
3
# $Id: alcasar-migration-3.2.0_dbStructure.sh 2667 2018-12-06 21:34:28Z tom.houdayer $
4
#
4
#
5
# alcasar-migration-3.2.0_dbStructure.sh
5
# alcasar-migration-3.2.0_dbStructure.sh
6
# by Tom HOUDAYER
6
# by Tom HOUDAYER
7
#
7
#
8
# This script is distributed under the Gnu General Public License (GPL)
8
# This script is distributed under the Gnu General Public License (GPL)
Line 10... Line 10...
10
# Migrate database structure to ALCASAR 3.2.0
10
# Migrate database structure to ALCASAR 3.2.0
11
# Changes:
11
# Changes:
12
# - Set database engine of radius tables to InnoDB
12
# - Set database engine of radius tables to InnoDB
13
# - Set column names in lowercase in radius tables
13
# - Set column names in lowercase in radius tables
14
# - Set index names in lowercase in radius tables
14
# - Set index names in lowercase in radius tables
15
# - Set RADIUS attribute length to 64 caracters
15
# - Set RADIUS attribute length to 64 characters
16
 
16
 
17
PASSWD_FILE="/root/ALCASAR-passwords.txt"
17
PASSWD_FILE="/root/ALCASAR-passwords.txt"
18
DB_PASS=$(cat $PASSWD_FILE | grep ^db_root= | cut -d'=' -f2-)
18
DB_PASS=$(cat $PASSWD_FILE | grep ^db_root= | cut -d'=' -f2-)
19
 
19
 
20
DRY_RUN=false
20
DRY_RUN=false
Line 52... Line 52...
52
		db_query "ALTER TABLE $tableName CHANGE $columnName $columnNameLower $columnType;"
52
		db_query "ALTER TABLE $tableName CHANGE $columnName $columnNameLower $columnType;"
53
	done <<< "$db_res"
53
	done <<< "$db_res"
54
fi
54
fi
55
 
55
 
56
# Set index names in lowercase in radius tables
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;")
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
58
if [ -n "$db_res" ]; then
59
	while read -r line; do
59
	while read -r line; do
60
		indexName=$(echo "$line"    | cut -f1)
60
		indexName=$(echo "$line"    | cut -f1)
61
		tableName=$(echo "$line"    | cut -f2)
61
		tableName=$(echo "$line"    | cut -f2)
62
		indexColumns=$(echo "$line" | cut -f3)
62
		indexColumns=$(echo "$line" | cut -f3)
63
		indexNameLower=${indexName,,}
63
		indexNameLower=${indexName,,}
64
		db_query "ALTER TABLE $tableName DROP INDEX $indexName, ADD INDEX $indexNameLower ($indexColumns);"
64
		db_query "ALTER TABLE $tableName DROP INDEX $indexName, ADD INDEX $indexNameLower ($indexColumns);"
65
	done <<< "$db_res"
65
	done <<< "$db_res"
66
fi
66
fi
67
 
67
 
68
# Set RADIUS attribute length to 64 caracters
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 '';"
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 '';"
70
db_query "ALTER TABLE radcheck      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
71
db_query "ALTER TABLE radcheck      MODIFY COLUMN attribute    varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '';"
-
 
72
db_query "ALTER TABLE radreply      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 '';"
73
db_query "ALTER TABLE radgroupcheck 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 '';"
74
db_query "ALTER TABLE radgroupreply 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 '';"
75
 
74
 
76
# Fix a potential bug
75
# Fix a potential bug