MySQL: Use utf8mb4 charset and utf8mb4_unicode_ci collation (#6535, #7113)

Fixes support for utf8 emoji in html signature.

10 files changed, 37 insertions, 19 deletions

diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 6f910ead2..72857dc36 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -422,6 +422,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
     WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)

 GO

 

-INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2020020100')

+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2020020101')

 GO

 
\ No newline at end of file
diff --git a/SQL/mssql/2020020101.sql b/SQL/mssql/2020020101.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/mssql/2020020101.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 8265f70f8..8981e0c3b 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -12,7 +12,7 @@ CREATE TABLE `session` (
  `vars` mediumtext NOT NULL,
  PRIMARY KEY(`sess_id`),
  INDEX `changed_index` (`changed`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `users`
@@ -29,7 +29,7 @@ CREATE TABLE `users` (
  `preferences` longtext,
  PRIMARY KEY(`user_id`),
  UNIQUE `username` (`username`, `mail_host`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `cache`
@@ -43,7 +43,7 @@ CREATE TABLE `cache` (
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `expires_index` (`expires`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `cache_shared`
@@ -54,7 +54,7 @@ CREATE TABLE `cache_shared` (
  `data` longtext NOT NULL,
  PRIMARY KEY (`cache_key`),
  INDEX `expires_index` (`expires`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `cache_index`
@@ -69,7 +69,7 @@ CREATE TABLE `cache_index` (
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `expires_index` (`expires`),
  PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `cache_thread`
@@ -83,7 +83,7 @@ CREATE TABLE `cache_thread` (
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `expires_index` (`expires`),
  PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `cache_messages`
@@ -99,7 +99,7 @@ CREATE TABLE `cache_messages` (
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `expires_index` (`expires`),
  PRIMARY KEY (`user_id`, `mailbox`, `uid`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `contacts`
@@ -119,7 +119,7 @@ CREATE TABLE `contacts` (
  CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `user_contacts_index` (`user_id`,`del`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 -- Table structure for table `contactgroups`
 
@@ -133,7 +133,7 @@ CREATE TABLE `contactgroups` (
   CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
     REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   INDEX `contactgroups_user_index` (`user_id`,`del`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 CREATE TABLE `contactgroupmembers` (
   `contactgroup_id` int(10) UNSIGNED NOT NULL,
@@ -168,7 +168,7 @@ CREATE TABLE `identities` (
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `user_identities_index` (`user_id`, `del`),
  INDEX `email_identities_index` (`email`, `del`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `dictionary`
@@ -181,7 +181,7 @@ CREATE TABLE `dictionary` (
   CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
     REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   UNIQUE `uniqueness` (`user_id`, `language`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 -- Table structure for table `searches`
@@ -196,7 +196,7 @@ CREATE TABLE `searches` (
  CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE `uniqueness` (`user_id`, `type`, `name`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 -- Table structure for table `filestore`
 
@@ -211,7 +211,7 @@ CREATE TABLE `filestore` (
  CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE `uniqueness` (`user_id`, `context`, `filename`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 -- Table structure for table `system`
 
@@ -219,8 +219,8 @@ CREATE TABLE `system` (
  `name` varchar(64) NOT NULL,
  `value` mediumtext,
  PRIMARY KEY(`name`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
 
-INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2020020100');
+INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2020020101');
diff --git a/SQL/mysql/2020020101.sql b/SQL/mysql/2020020101.sql
new file mode 100644
index 000000000..ddab0878d
--- /dev/null
+++ b/SQL/mysql/2020020101.sql
@@ -0,0 +1,14 @@
+ALTER TABLE `session` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `cache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `cache_shared` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `cache_index` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `cache_thread` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `cache_messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `contacts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `contactgroups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `identities` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `dictionary` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `searches` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `filestore` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `system` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
index f89a21bb9..711e71904 100644
--- a/SQL/oracle.initial.sql
+++ b/SQL/oracle.initial.sql
@@ -238,4 +238,4 @@ CREATE TABLE "system" (
     "value" long
 );
 
-INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2020020100');
+INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2020020101');
diff --git a/SQL/oracle/2020020101.sql b/SQL/oracle/2020020101.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/oracle/2020020101.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index c1db6dd33..02be65d98 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -314,4 +314,4 @@ CREATE TABLE "system" (
     value text
 );
 
-INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2020020100');
+INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2020020101');
diff --git a/SQL/postgres/2020020101.sql b/SQL/postgres/2020020101.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/postgres/2020020101.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index e82254cb5..42ab425f1 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -215,4 +215,4 @@ CREATE TABLE system (
   value text NOT NULL
 );
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2020020100');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2020020101');
diff --git a/SQL/sqlite/2020020101.sql b/SQL/sqlite/2020020101.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/sqlite/2020020101.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file