mremi/database-encoder

A PHP5 library to fix database encoding

Database encoder library

This library allows you to fix database encoding., _(*1)

Who has never seen some accented characters like "Activé" instead of "Activé"?, _(*2)

Basic Docs, _(*3)

• Installation
• Encode tables
• Encode database
• Contribution

, _(*4)

Installation

Only 1 step:, _(*5)

Download DatabaseEncoder using composer

Add DatabaseEncoder in your composer.json:, _(*6)

{
    "require": {
        "mremi/database-encoder": "dev-master"
    }
}

Now tell composer to download the library by running the command:, _(*7)

``` bash $ php composer.phar update mremi/database-encoder, <sub>(*8)</sub>

Composer will install the library to your project's `vendor/mremi` directory.

**Feel free to dump your database before use this library.**

This library supports *dry run* mode: look at your logs, you will see SQL
queries which will be executed.

<a name="encode-tables"></a>

## Encode tables

```php
<?php

use Mremi\DatabaseEncoder\EncoderHandler;
use Mremi\DatabaseEncoder\MySql\MySqlEncoder;

use Monolog\Logger;

$conn    = new \PDO('mysql:host=localhost;dbname=db_name;charset=utf8;', 'db_user', 'db_password');
$logger  = new Logger('app');
$encoder = new MySqlEncoder($conn, $logger);
$handler = new EncoderHandler($encoder, $logger);

$handler->encodeTables(array(
   'table1' => array('column1_1', 'column1_2'),
   'table2' => array('column2_1', 'column2_2', 'column2_3'),
));

You can also use the command provided by this library, look at the help message:, <sub>(*9)</sub>

$ bin/encoder encode-tables --help

Some arguments are mandatory:, <sub>(*10)</sub>

$ bin/encoder encode-tables "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password '{"table1":["column1_1","column1_2"],"table2":["column2_1","column2_2","column2_3"]}'

Some options are available:, <sub>(*11)</sub>

$ bin/encoder encode-tables "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password '{"table1":["column1_1","column1_2"],"table2":["column2_1","column2_2","column2_3"]}' --options='{"1000":1}' --encoding=utf8 --dry-run

You can increase the log verbosity. The following example allows you to see the SQL queries without execute them:, <sub>(*12)</sub>

$ bin/encoder encode-tables "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password '{"table1":["column1_1","column1_2"],"table2":["column2_1","column2_2","column2_3"]}' --dry-run -vvv

[notice] Starting encoding (5 queries)...
[debug] Executed in 0 ms: UPDATE `table1` SET `column1_1` = CONVERT(CAST(CONVERT(`column1_1` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table1` SET `column1_2` = CONVERT(CAST(CONVERT(`column1_2` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_1` = CONVERT(CAST(CONVERT(`column2_1` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_2` = CONVERT(CAST(CONVERT(`column2_2` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_3` = CONVERT(CAST(CONVERT(`column2_3` USING latin1) AS BINARY) USING utf8) []
[notice] Done!

, <sub>(*13)</sub>

Encode database

<?php

use Mremi\DatabaseEncoder\EncoderHandler;
use Mremi\DatabaseEncoder\MySql\MySqlEncoder;

use Monolog\Logger;

$conn    = new \PDO('mysql:host=localhost;dbname=db_name;charset=utf8;', 'db_user', 'db_password');
$logger  = new Logger('app');
$encoder = new MySqlEncoder($conn, $logger);
$handler = new EncoderHandler($encoder, $logger);

$handler->encodeDatabase();

You can also use the command provided by this library, look at the help message:, <sub>(*14)</sub>

$ bin/encoder encode-database --help

Some arguments are mandatory:, <sub>(*15)</sub>

$ bin/encoder encode-database "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password

Some options are available:, <sub>(*16)</sub>

$ bin/encoder encode-database "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password --options='{"1000":1}' --encoding=utf8 --dry-run

You can increase the log verbosity. The following example allows you to see the SQL queries without execute them:, <sub>(*17)</sub>

$ bin/encoder encode-database "mysql:host=localhost;dbname=db_name;charset=utf8;" db_user db_password --dry-run -vvv

[notice] Retrieving string columns...
[debug] Executed in 0 ms: SELECT DATABASE() []
[debug] Executed in 0 ms: SELECT `TABLE_NAME` AS table_name, `COLUMN_NAME` AS column_name
            FROM `information_schema`.`COLUMNS`
            WHERE
                `TABLE_SCHEMA` = :table_schema
                AND `CHARACTER_SET_NAME` = :character_set_name
            ORDER BY `TABLE_NAME` ["db_name","utf8"]
[notice] Starting encoding (9 queries)...
[debug] Executed in 0 ms: UPDATE `table1` SET `column1_1` = CONVERT(CAST(CONVERT(`column1_1` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table1` SET `column1_2` = CONVERT(CAST(CONVERT(`column1_2` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_1` = CONVERT(CAST(CONVERT(`column2_1` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_2` = CONVERT(CAST(CONVERT(`column2_2` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table2` SET `column2_3` = CONVERT(CAST(CONVERT(`column2_3` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table3` SET `column3_1` = CONVERT(CAST(CONVERT(`column3_1` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table3` SET `column3_2` = CONVERT(CAST(CONVERT(`column3_2` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table3` SET `column3_3` = CONVERT(CAST(CONVERT(`column3_3` USING latin1) AS BINARY) USING utf8) []
[debug] Executed in 0 ms: UPDATE `table3` SET `column3_4` = CONVERT(CAST(CONVERT(`column3_4` USING latin1) AS BINARY) USING utf8) []
[notice] Done!

, <sub>(*18)</sub>

Contribution

Any question or feedback? Open an issue and I will try to reply quickly., <sub>(*19)</sub>

A feature is missing here? Feel free to create a pull request to solve it!, <sub>(*20)</sub>

I hope this has been useful and has helped you. If so, share it and recommend it! :), <sub>(*21)</sub>

@mremitsme, <sub>(*22)</sub>