Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /**
- * Migrates MySQL database table columns that have been stored in a mixed
- * environment - e.g. database running on `latin1` but using `SET NAMES utf8`
- * during the connection when writing data to the DMBS.
- *
- * Expected source character set: latin1
- * Defined target character set: utf8
- * Defined target collation: utf8_general_ci
- *
- * @author Oliver Hader <oliver.hader@typo3.org>
- * @license GPLv2 (or any later version)
- * @modified 2018-06-05
- */
- if ($argc < 4) {
- echo implode(PHP_EOL, [
- '+ Usage',
- '++++++++',
- sprintf('%s database username password [hostname]' . PHP_EOL, $argv[0])
- ]);
- exit(1);
- }
- $arguments = $argv;
- array_shift($arguments);
- $database = array_shift($arguments);
- $username = array_shift($arguments);
- $password = array_shift($arguments);
- $hostname = array_shift($arguments);
- if (empty($hostname)) {
- $hostname = '127.0.0.1';
- }
- $dsn = sprintf('mysql:dbname=%s;host=%s', $database, $hostname);
- $pdo = new \PDO($dsn, $username, $password);
- $statement = $pdo->query(
- 'SELECT TABLES.TABLE_NAME, TABLES.TABLE_COLLATION, CCSA.CHARACTER_SET_NAME '
- . 'FROM information_schema.TABLES TABLES, '
- . 'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA '
- . 'WHERE CCSA.COLLATION_NAME = TABLES.TABLE_COLLATION AND TABLES.TABLE_SCHEMA=' . $pdo->quote($database)
- );
- $changed = 0;
- $tables = $statement->fetchAll(\PDO::FETCH_ASSOC);
- foreach ($tables as $table) {
- $tableName = $table['TABLE_NAME'];
- $statement = $pdo->query(
- 'SELECT COLUMN_NAME, COLUMN_DEFAULT, COLUMN_TYPE, IS_NULLABLE, '
- . 'DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS '
- . 'WHERE TABLE_NAME=' . $pdo->quote($tableName) . ' AND TABLE_SCHEMA=' . $pdo->quote($database)
- );
- $columns = array_filter(
- $statement->fetchAll(\PDO::FETCH_ASSOC),
- function (array $column) {
- return $column['CHARACTER_SET_NAME'] === 'latin1'
- || strpos($column['COLLATION_NAME'], 'latin1_') === 0;
- }
- );
- foreach ($columns as $column) {
- $columnName = $column['COLUMN_NAME'];
- echo sprintf('+ %s.%s... ', $tableName, $columnName);
- $parts = [
- 'ALTER TABLE',
- $tableName,
- 'CHANGE',
- $columnName,
- $columnName,
- 'BLOB',
- ];
- $pdo->query(implode(' ', $parts));
- $parts = [
- 'ALTER TABLE',
- $tableName,
- 'CHANGE',
- $columnName,
- $columnName,
- $column['COLUMN_TYPE'],
- 'CHARACTER SET utf8',
- 'COLLATE utf8_general_ci',
- ($column['IS_NULLABLE'] ? '' : 'NOT ') . 'NULL',
- ];
- if ($column['COLUMN_DEFAULT'] !== null) {
- $parts[] = 'DEFAULT ' . $pdo->quote($column['COLUMN_DEFAULT']);
- }
- $pdo->query(implode(' ', $parts));
- $changed++;
- echo 'done' . PHP_EOL;
- }
- if ($table['CHARACTER_SET_NAME'] === 'latin1'
- || strpos($table['TABLE_COLLATION'], 'latin1_') === 0
- ) {
- echo sprintf('* %s... ', $tableName);
- $pdo->query(implode(' ', [
- 'ALTER TABLE',
- $tableName,
- 'CHARACTER SET utf8',
- 'COLLATE utf8_general_ci',
- ]));
- echo 'done' . PHP_EOL;
- }
- }
- if ($changed === 0) {
- echo 'All fine. Not changes required...' . PHP_EOL;
- }
Add Comment
Please, Sign In to add comment