From d8c625c8562f527f4a1a7cec6c3e6904ca11b69d Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Thu, 12 Apr 2018 13:41:32 -0700 Subject: Use utf8mb4 encoding for MySQL instead of utf8 --- app/Schema/Mysql.php | 72 +++++++- app/Schema/Sql/mysql.sql | 284 +++++++++++++++---------------- app/ServiceProvider/DatabaseProvider.php | 2 +- 3 files changed, 214 insertions(+), 144 deletions(-) (limited to 'app') diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php index 6adcaec1..c5144663 100644 --- a/app/Schema/Mysql.php +++ b/app/Schema/Mysql.php @@ -8,7 +8,77 @@ use PDO; use Kanboard\Core\Security\Token; use Kanboard\Core\Security\Role; -const VERSION = 129; +const VERSION = 130; + +/* + +This migration convert table encoding to utf8mb4. +You should also convert the database encoding: + +ALTER DATABASE kanboard CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + +You might need to run: + +REPAIR TABLE table_name; +OPTIMIZE TABLE table_name; + +*/ +function version_130(PDO $pdo) +{ + $tables = [ + 'action_has_params', + 'actions', + 'column_has_move_restrictions', + 'column_has_restrictions', + 'columns', + 'comments', + 'currencies', + 'custom_filters', + 'group_has_users', + 'groups', + 'invites', + 'last_logins', + 'links', + 'password_reset', + 'plugin_schema_versions', + 'predefined_task_descriptions', + 'project_activities', + 'project_daily_column_stats', + 'project_daily_stats', + 'project_has_categories', + 'project_has_files', + 'project_has_groups', + 'project_has_metadata', + 'project_has_notification_types', + 'project_has_roles', + 'project_has_users', + 'project_role_has_restrictions', + 'projects', + 'remember_me', + 'sessions', + 'settings', + 'subtask_time_tracking', + 'subtasks', + 'swimlanes', + 'tags', + 'task_has_external_links', + 'task_has_files', + 'task_has_links', + 'task_has_metadata', + 'task_has_tags', + 'tasks', + 'transitions', + 'user_has_metadata', + 'user_has_notification_types', + 'user_has_notifications', + 'user_has_unread_notifications', + 'users', + ]; + + foreach ($tables as $table) { + $pdo->exec('ALTER TABLE `'.$table.'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'); + } +} function version_129(PDO $pdo) { diff --git a/app/Schema/Sql/mysql.sql b/app/Schema/Sql/mysql.sql index 3113b4e6..7f46c872 100644 --- a/app/Schema/Sql/mysql.sql +++ b/app/Schema/Sql/mysql.sql @@ -15,12 +15,12 @@ DROP TABLE IF EXISTS `action_has_params`; CREATE TABLE `action_has_params` ( `id` int(11) NOT NULL AUTO_INCREMENT, `action_id` int(11) NOT NULL, - `name` text NOT NULL, - `value` text NOT NULL, + `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `action_id` (`action_id`), CONSTRAINT `action_has_params_ibfk_1` FOREIGN KEY (`action_id`) REFERENCES `actions` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `actions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -28,12 +28,12 @@ DROP TABLE IF EXISTS `actions`; CREATE TABLE `actions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project_id` int(11) NOT NULL, - `event_name` text NOT NULL, - `action_name` text NOT NULL, + `event_name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `action_name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `project_id` (`project_id`), CONSTRAINT `actions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `column_has_move_restrictions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -54,7 +54,7 @@ CREATE TABLE `column_has_move_restrictions` ( CONSTRAINT `column_has_move_restrictions_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `project_has_roles` (`role_id`) ON DELETE CASCADE, CONSTRAINT `column_has_move_restrictions_ibfk_3` FOREIGN KEY (`src_column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, CONSTRAINT `column_has_move_restrictions_ibfk_4` FOREIGN KEY (`dst_column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `column_has_restrictions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -64,7 +64,7 @@ CREATE TABLE `column_has_restrictions` ( `project_id` int(11) NOT NULL, `role_id` int(11) NOT NULL, `column_id` int(11) NOT NULL, - `rule` varchar(255) NOT NULL, + `rule` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`restriction_id`), UNIQUE KEY `role_id` (`role_id`,`column_id`,`rule`), KEY `project_id` (`project_id`), @@ -72,24 +72,24 @@ CREATE TABLE `column_has_restrictions` ( CONSTRAINT `column_has_restrictions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `column_has_restrictions_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `project_has_roles` (`role_id`) ON DELETE CASCADE, CONSTRAINT `column_has_restrictions_ibfk_3` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `columns`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `columns` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `title` varchar(255) NOT NULL, + `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `position` int(11) NOT NULL, `project_id` int(11) NOT NULL, `task_limit` int(11) DEFAULT '0', - `description` text, + `description` mediumtext COLLATE utf8mb4_unicode_ci, `hide_in_dashboard` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `idx_title_project` (`title`,`project_id`), KEY `columns_project_idx` (`project_id`), CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `comments`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -99,34 +99,34 @@ CREATE TABLE `comments` ( `task_id` int(11) NOT NULL, `user_id` int(11) DEFAULT '0', `date_creation` bigint(20) DEFAULT NULL, - `comment` text, - `reference` varchar(255) DEFAULT '', + `comment` mediumtext COLLATE utf8mb4_unicode_ci, + `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `date_modification` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `comments_reference_idx` (`reference`), KEY `comments_task_idx` (`task_id`), CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `currencies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `currencies` ( - `currency` char(3) NOT NULL, + `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL, `rate` float DEFAULT '0', UNIQUE KEY `currency` (`currency`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `custom_filters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `custom_filters` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `filter` text NOT NULL, + `filter` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, - `name` text NOT NULL, + `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `is_shared` tinyint(1) DEFAULT '0', `append` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), @@ -134,7 +134,7 @@ CREATE TABLE `custom_filters` ( KEY `user_id` (`user_id`), CONSTRAINT `custom_filters_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `custom_filters_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `group_has_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -146,79 +146,79 @@ CREATE TABLE `group_has_users` ( KEY `user_id` (`user_id`), CONSTRAINT `group_has_users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE, CONSTRAINT `group_has_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `groups`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `groups` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `external_id` varchar(255) DEFAULT '', - `name` varchar(255) NOT NULL, + `external_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `invites`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `invites` ( - `email` varchar(255) NOT NULL, + `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, - `token` varchar(255) NOT NULL, + `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`email`,`token`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `last_logins`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `last_logins` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `auth_type` varchar(25) DEFAULT NULL, + `auth_type` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_id` int(11) DEFAULT NULL, - `ip` varchar(45) DEFAULT NULL, - `user_agent` varchar(255) DEFAULT NULL, + `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `date_creation` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `last_logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `links`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `links` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `label` varchar(255) NOT NULL, + `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `opposite_id` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `label` (`label`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `password_reset`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `password_reset` ( - `token` varchar(80) NOT NULL, + `token` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL, `user_id` int(11) NOT NULL, `date_expiration` int(11) NOT NULL, `date_creation` int(11) NOT NULL, - `ip` varchar(45) NOT NULL, - `user_agent` varchar(255) NOT NULL, + `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL, + `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `is_active` tinyint(1) NOT NULL, PRIMARY KEY (`token`), KEY `user_id` (`user_id`), CONSTRAINT `password_reset_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `plugin_schema_versions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `plugin_schema_versions` ( - `plugin` varchar(80) NOT NULL, + `plugin` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL, `version` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`plugin`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `predefined_task_descriptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -226,12 +226,12 @@ DROP TABLE IF EXISTS `predefined_task_descriptions`; CREATE TABLE `predefined_task_descriptions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project_id` int(11) NOT NULL, - `title` text NOT NULL, - `description` text NOT NULL, + `title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `description` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `project_id` (`project_id`), CONSTRAINT `predefined_task_descriptions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_activities`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -239,11 +239,11 @@ DROP TABLE IF EXISTS `project_activities`; CREATE TABLE `project_activities` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date_creation` bigint(20) DEFAULT NULL, - `event_name` text NOT NULL, + `event_name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `creator_id` int(11) DEFAULT NULL, `project_id` int(11) DEFAULT NULL, `task_id` int(11) DEFAULT NULL, - `data` text, + `data` mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), KEY `creator_id` (`creator_id`), KEY `project_id` (`project_id`), @@ -251,14 +251,14 @@ CREATE TABLE `project_activities` ( CONSTRAINT `project_activities_ibfk_1` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `project_activities_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `project_activities_ibfk_3` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_daily_column_stats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `project_daily_column_stats` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `day` char(10) NOT NULL, + `day` char(10) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, `column_id` int(11) NOT NULL, `total` int(11) NOT NULL DEFAULT '0', @@ -269,14 +269,14 @@ CREATE TABLE `project_daily_column_stats` ( KEY `project_id` (`project_id`), CONSTRAINT `project_daily_column_stats_ibfk_1` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, CONSTRAINT `project_daily_column_stats_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_daily_stats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `project_daily_stats` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `day` char(10) NOT NULL, + `day` char(10) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, `avg_lead_time` int(11) NOT NULL DEFAULT '0', `avg_cycle_time` int(11) NOT NULL DEFAULT '0', @@ -284,21 +284,21 @@ CREATE TABLE `project_daily_stats` ( UNIQUE KEY `project_daily_stats_idx` (`day`,`project_id`), KEY `project_id` (`project_id`), CONSTRAINT `project_daily_stats_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `project_has_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(255) NOT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, - `description` text, + `description` mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `idx_project_category` (`project_id`,`name`), KEY `categories_project_idx` (`project_id`), CONSTRAINT `project_has_categories_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_files`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -306,8 +306,8 @@ DROP TABLE IF EXISTS `project_has_files`; CREATE TABLE `project_has_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project_id` int(11) NOT NULL, - `name` text NOT NULL, - `path` text NOT NULL, + `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `path` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `is_image` tinyint(1) DEFAULT '0', `size` int(11) NOT NULL DEFAULT '0', `user_id` int(11) NOT NULL DEFAULT '0', @@ -315,7 +315,7 @@ CREATE TABLE `project_has_files` ( PRIMARY KEY (`id`), KEY `project_id` (`project_id`), CONSTRAINT `project_has_files_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_groups`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -323,25 +323,25 @@ DROP TABLE IF EXISTS `project_has_groups`; CREATE TABLE `project_has_groups` ( `group_id` int(11) NOT NULL, `project_id` int(11) NOT NULL, - `role` varchar(255) NOT NULL, + `role` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, UNIQUE KEY `group_id` (`group_id`,`project_id`), KEY `project_id` (`project_id`), CONSTRAINT `project_has_groups_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE, CONSTRAINT `project_has_groups_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_metadata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `project_has_metadata` ( `project_id` int(11) NOT NULL, - `name` varchar(50) NOT NULL, - `value` varchar(255) DEFAULT '', + `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `changed_by` int(11) NOT NULL DEFAULT '0', `changed_on` int(11) NOT NULL DEFAULT '0', UNIQUE KEY `project_id` (`project_id`,`name`), CONSTRAINT `project_has_metadata_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_notification_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -349,23 +349,23 @@ DROP TABLE IF EXISTS `project_has_notification_types`; CREATE TABLE `project_has_notification_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project_id` int(11) NOT NULL, - `notification_type` varchar(50) NOT NULL, + `notification_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `project_id` (`project_id`,`notification_type`), CONSTRAINT `project_has_notification_types_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `project_has_roles` ( `role_id` int(11) NOT NULL AUTO_INCREMENT, - `role` varchar(255) NOT NULL, + `role` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, PRIMARY KEY (`role_id`), UNIQUE KEY `project_id` (`project_id`,`role`), CONSTRAINT `project_has_roles_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_has_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -373,12 +373,12 @@ DROP TABLE IF EXISTS `project_has_users`; CREATE TABLE `project_has_users` ( `project_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, - `role` varchar(255) NOT NULL, + `role` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, UNIQUE KEY `idx_project_user` (`project_id`,`user_id`), KEY `user_id` (`user_id`), CONSTRAINT `project_has_users_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `project_has_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `project_role_has_restrictions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -387,37 +387,37 @@ CREATE TABLE `project_role_has_restrictions` ( `restriction_id` int(11) NOT NULL AUTO_INCREMENT, `project_id` int(11) NOT NULL, `role_id` int(11) NOT NULL, - `rule` varchar(255) NOT NULL, + `rule` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`restriction_id`), UNIQUE KEY `role_id` (`role_id`,`rule`), KEY `project_id` (`project_id`), CONSTRAINT `project_role_has_restrictions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `project_role_has_restrictions_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `project_has_roles` (`role_id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `projects`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `name` text NOT NULL, + `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `is_active` tinyint(4) DEFAULT '1', - `token` varchar(255) DEFAULT NULL, + `token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `last_modified` bigint(20) DEFAULT NULL, `is_public` tinyint(1) DEFAULT '0', `is_private` tinyint(1) DEFAULT '0', - `description` text, - `identifier` varchar(50) DEFAULT '', - `start_date` varchar(10) DEFAULT '', - `end_date` varchar(10) DEFAULT '', + `description` mediumtext COLLATE utf8mb4_unicode_ci, + `identifier` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '', + `start_date` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '', + `end_date` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '', `owner_id` int(11) DEFAULT '0', `priority_default` int(11) DEFAULT '0', `priority_start` int(11) DEFAULT '0', `priority_end` int(11) DEFAULT '3', - `email` text, - `predefined_email_subjects` text, + `email` mediumtext COLLATE utf8mb4_unicode_ci, + `predefined_email_subjects` mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `remember_me`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -425,16 +425,16 @@ DROP TABLE IF EXISTS `remember_me`; CREATE TABLE `remember_me` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, - `ip` varchar(45) DEFAULT NULL, - `user_agent` varchar(255) DEFAULT NULL, - `token` varchar(255) DEFAULT NULL, - `sequence` varchar(255) DEFAULT NULL, + `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `sequence` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `expiration` int(11) DEFAULT NULL, `date_creation` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `remember_me_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `schema_version`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -447,22 +447,22 @@ DROP TABLE IF EXISTS `sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sessions` ( - `id` varchar(255) NOT NULL, + `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `expire_at` int(11) NOT NULL, - `data` longtext, + `data` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `settings` ( - `option` varchar(100) NOT NULL, - `value` text, + `option` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `value` mediumtext COLLATE utf8mb4_unicode_ci, `changed_by` int(11) NOT NULL DEFAULT '0', `changed_on` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`option`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `subtask_time_tracking`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -479,14 +479,14 @@ CREATE TABLE `subtask_time_tracking` ( KEY `subtask_id` (`subtask_id`), CONSTRAINT `subtask_time_tracking_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `subtask_time_tracking_ibfk_2` FOREIGN KEY (`subtask_id`) REFERENCES `subtasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `subtasks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `subtasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `title` text NOT NULL, + `title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `status` int(11) DEFAULT '0', `time_estimated` float DEFAULT NULL, `time_spent` float DEFAULT NULL, @@ -496,44 +496,44 @@ CREATE TABLE `subtasks` ( PRIMARY KEY (`id`), KEY `subtasks_task_idx` (`task_id`), CONSTRAINT `subtasks_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `swimlanes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `swimlanes` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(255) NOT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `position` int(11) DEFAULT '1', `is_active` int(11) DEFAULT '1', `project_id` int(11) DEFAULT NULL, - `description` text, + `description` mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`project_id`), KEY `swimlanes_project_idx` (`project_id`), CONSTRAINT `swimlanes_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `tags`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tags` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(255) NOT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `project_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `project_id` (`project_id`,`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `task_has_external_links`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `task_has_external_links` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `link_type` varchar(100) NOT NULL, - `dependency` varchar(100) NOT NULL, - `title` text NOT NULL, - `url` text NOT NULL, + `link_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `dependency` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `url` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `date_creation` int(11) NOT NULL, `date_modification` int(11) NOT NULL, `task_id` int(11) NOT NULL, @@ -541,15 +541,15 @@ CREATE TABLE `task_has_external_links` ( PRIMARY KEY (`id`), KEY `task_id` (`task_id`), CONSTRAINT `task_has_external_links_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `task_has_files`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `task_has_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `name` text NOT NULL, - `path` text NOT NULL, + `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `path` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `is_image` tinyint(1) DEFAULT '0', `task_id` int(11) NOT NULL, `date` bigint(20) DEFAULT NULL, @@ -558,7 +558,7 @@ CREATE TABLE `task_has_files` ( PRIMARY KEY (`id`), KEY `files_task_idx` (`task_id`), CONSTRAINT `task_has_files_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `task_has_links`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -575,20 +575,20 @@ CREATE TABLE `task_has_links` ( CONSTRAINT `task_has_links_ibfk_1` FOREIGN KEY (`link_id`) REFERENCES `links` (`id`) ON DELETE CASCADE, CONSTRAINT `task_has_links_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE, CONSTRAINT `task_has_links_ibfk_3` FOREIGN KEY (`opposite_task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `task_has_metadata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `task_has_metadata` ( `task_id` int(11) NOT NULL, - `name` varchar(50) NOT NULL, - `value` varchar(255) DEFAULT '', + `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `changed_by` int(11) NOT NULL DEFAULT '0', `changed_on` int(11) NOT NULL DEFAULT '0', UNIQUE KEY `task_id` (`task_id`,`name`), CONSTRAINT `task_has_metadata_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `task_has_tags`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -600,19 +600,19 @@ CREATE TABLE `task_has_tags` ( KEY `task_id` (`task_id`), CONSTRAINT `task_has_tags_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE, CONSTRAINT `task_has_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `tasks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `title` text NOT NULL, - `description` text, + `title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `description` mediumtext COLLATE utf8mb4_unicode_ci, `date_creation` bigint(20) DEFAULT NULL, `date_completed` bigint(20) DEFAULT NULL, `date_due` bigint(20) DEFAULT NULL, - `color_id` varchar(50) DEFAULT NULL, + `color_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `project_id` int(11) NOT NULL, `column_id` int(11) NOT NULL, `owner_id` int(11) DEFAULT '0', @@ -622,7 +622,7 @@ CREATE TABLE `tasks` ( `category_id` int(11) DEFAULT '0', `creator_id` int(11) DEFAULT '0', `date_modification` int(11) DEFAULT '0', - `reference` varchar(255) DEFAULT '', + `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `date_started` bigint(20) DEFAULT NULL, `time_spent` float DEFAULT '0', `time_estimated` float DEFAULT '0', @@ -636,8 +636,8 @@ CREATE TABLE `tasks` ( `recurrence_parent` int(11) DEFAULT NULL, `recurrence_child` int(11) DEFAULT NULL, `priority` int(11) DEFAULT '0', - `external_provider` varchar(255) DEFAULT NULL, - `external_uri` varchar(255) DEFAULT NULL, + `external_provider` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `external_uri` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_task_active` (`is_active`), KEY `column_id` (`column_id`), @@ -647,7 +647,7 @@ CREATE TABLE `tasks` ( CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, CONSTRAINT `tasks_swimlane_ibfk_1` FOREIGN KEY (`swimlane_id`) REFERENCES `swimlanes` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `transitions`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -672,20 +672,20 @@ CREATE TABLE `transitions` ( CONSTRAINT `transitions_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `transitions_ibfk_4` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, CONSTRAINT `transitions_ibfk_5` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `user_has_metadata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_has_metadata` ( `user_id` int(11) NOT NULL, - `name` varchar(50) NOT NULL, - `value` varchar(255) DEFAULT '', + `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `changed_by` int(11) NOT NULL DEFAULT '0', `changed_on` int(11) NOT NULL DEFAULT '0', UNIQUE KEY `user_id` (`user_id`,`name`), CONSTRAINT `user_has_metadata_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `user_has_notification_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -693,11 +693,11 @@ DROP TABLE IF EXISTS `user_has_notification_types`; CREATE TABLE `user_has_notification_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, - `notification_type` varchar(50) DEFAULT NULL, + `notification_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_has_notification_types_user_idx` (`user_id`,`notification_type`), CONSTRAINT `user_has_notification_types_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `user_has_notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -709,7 +709,7 @@ CREATE TABLE `user_has_notifications` ( KEY `user_has_notifications_ibfk_2` (`project_id`), CONSTRAINT `user_has_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `user_has_notifications_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `user_has_unread_notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -718,44 +718,44 @@ CREATE TABLE `user_has_unread_notifications` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `date_creation` bigint(20) NOT NULL, - `event_name` text NOT NULL, - `event_data` text NOT NULL, + `event_name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `event_data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `user_has_unread_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, - `username` varchar(255) NOT NULL, - `password` varchar(255) DEFAULT NULL, + `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_ldap_user` tinyint(1) DEFAULT '0', - `name` varchar(255) DEFAULT NULL, - `email` varchar(255) DEFAULT NULL, - `google_id` varchar(30) DEFAULT NULL, - `github_id` varchar(30) DEFAULT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `google_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `github_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notifications_enabled` tinyint(1) DEFAULT '0', - `timezone` varchar(50) DEFAULT NULL, - `language` varchar(5) DEFAULT NULL, + `timezone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `language` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `disable_login_form` tinyint(1) DEFAULT '0', `twofactor_activated` tinyint(1) DEFAULT '0', - `twofactor_secret` char(16) DEFAULT NULL, - `token` varchar(255) DEFAULT '', + `twofactor_secret` char(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '', `notifications_filter` int(11) DEFAULT '4', `nb_failed_login` int(11) DEFAULT '0', `lock_expiration_date` bigint(20) DEFAULT NULL, `gitlab_id` int(11) DEFAULT NULL, - `role` varchar(25) NOT NULL DEFAULT 'app-user', + `role` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'app-user', `is_active` tinyint(1) DEFAULT '1', - `avatar_path` varchar(255) DEFAULT NULL, - `api_access_token` varchar(255) DEFAULT NULL, - `filter` text, + `avatar_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `api_access_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `filter` mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `users_username_idx` (`username`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -805,4 +805,4 @@ UNLOCK TABLES; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -INSERT INTO users (username, password, role) VALUES ('admin', '$2y$10$eu5txjAlmBRZYmAcWjHAx.BSCIYL6RMTIyrIWG4eqWFtf62DCJPWy', 'app-admin');INSERT INTO schema_version VALUES ('129'); +INSERT INTO users (username, password, role) VALUES ('admin', '$2y$10$wHAStAxKXclzpBY4hRwL0e/LMNpxB5o50qwtO6I4OtJjIfB4stZIO', 'app-admin');INSERT INTO schema_version VALUES ('130'); diff --git a/app/ServiceProvider/DatabaseProvider.php b/app/ServiceProvider/DatabaseProvider.php index 704e8079..40413b73 100644 --- a/app/ServiceProvider/DatabaseProvider.php +++ b/app/ServiceProvider/DatabaseProvider.php @@ -130,7 +130,7 @@ class DatabaseProvider implements ServiceProviderInterface 'username' => DB_USERNAME, 'password' => DB_PASSWORD, 'database' => DB_NAME, - 'charset' => 'utf8', + 'charset' => 'utf8mb4', 'port' => DB_PORT, 'ssl_key' => DB_SSL_KEY, 'ssl_ca' => DB_SSL_CA, -- cgit v1.2.3