Database: Use utf8mb4_unicode_ci

3 files changed, 78 insertions, 2 deletions

diff --git a/db/migrations/2019_07_21_000000_fix_old_character_set.php b/db/migrations/2019_07_21_000000_fix_old_character_set.php
new file mode 100644
index 00000000..f72e26ae
--- /dev/null
+++ b/db/migrations/2019_07_21_000000_fix_old_character_set.php
@@ -0,0 +1,75 @@
+<?php
+
+namespace Engelsystem\Migrations;
+
+use Engelsystem\Database\Migration\Migration;
+use stdClass;
+
+class FixOldCharacterSet extends Migration
+{
+    /**
+     * Run the migration
+     */
+    public function up()
+    {
+        $connection = $this->schema->getConnection();
+        $targetCharset = $connection->getConfig('charset');
+        $targetCollation = $connection->getConfig('collation');
+
+        if (!$targetCharset || !$targetCharset) {
+            return;
+        }
+
+        $connection
+            ->unprepared(sprintf(
+                'ALTER DATABASE %s CHARACTER SET %s COLLATE %s',
+                $connection->getDatabaseName(),
+                $targetCharset,
+                $targetCollation
+            ));
+
+        /** @var stdClass[] $databases */
+        $tables = $this->getTablesToChange($targetCollation);
+        foreach ($tables as $table) {
+            $connection
+                ->unprepared(sprintf(
+                    'ALTER TABLE %s CONVERT TO CHARACTER SET %s COLLATE %s',
+                    $table->table,
+                    $targetCharset,
+                    $targetCollation
+                ));
+        }
+    }
+
+    /**
+     * Reverse the migration
+     */
+    public function down()
+    {
+        // Nothing to do
+    }
+
+    /**
+     * @param $target
+     * @return array
+     */
+    protected function getTablesToChange($target)
+    {
+        $connection = $this->schema->getConnection();
+
+        return $connection
+            ->select(
+                '
+                    SELECT
+                        `TABLE_NAME` AS "table"
+                    FROM information_schema.TABLES
+                    WHERE TABLE_SCHEMA = ?
+                    AND TABLE_COLLATION != ?
+                ',
+                [
+                    $connection->getDatabaseName(),
+                    $target
+                ]
+            );
+    }
+}
diff --git a/src/Database/DatabaseServiceProvider.php b/src/Database/DatabaseServiceProvider.php
index b3c33588..13b10552 100644
--- a/src/Database/DatabaseServiceProvider.php
+++ b/src/Database/DatabaseServiceProvider.php
@@ -22,8 +22,8 @@ class DatabaseServiceProvider extends ServiceProvider
             'database'  => '',
             'username'  => '',
             'password'  => '',
-            'charset'   => 'utf8',
-            'collation' => 'utf8_unicode_ci',
+            'charset'   => 'utf8mb4',
+            'collation' => 'utf8mb4_unicode_ci',
             'prefix'    => '',
         ], $dbConfig));
 
diff --git a/tests/Unit/HasDatabase.php b/tests/Unit/HasDatabase.php
index 7a58bb2b..7fd42f96 100644
--- a/tests/Unit/HasDatabase.php
+++ b/tests/Unit/HasDatabase.php
@@ -45,6 +45,7 @@ trait HasDatabase
                 ['migration' => '2018_01_01_000004_cleanup_group_privileges'],
                 ['migration' => '2018_01_01_000005_add_angel_supporter_permissions'],
                 ['migration' => '2018_12_27_000000_fix_missing_arrival_dates'],
+                ['migration' => '2019_07_21_000000_fix_old_character_set'],
             ]);
 
         $migration->run(__DIR__ . '/../../db/migrations');