Platform 1.0 to 2.0 SQL Changes

# New Columns
ALTER TABLE `menu_types` ADD COLUMN `asset_id` INT(11) NOT NULL AFTER `id`;
ALTER TABLE `languages` ADD COLUMN `asset_id` INT(11) NOT NULL AFTER `lang_id`;

# Changes in columns
-- menu
DROP INDEX `idx_client_id_parent_id_alias_language` ON `menu`;
CREATE INDEX `idx_client_id_parent_id_alias_language` ON `menu` (`client_id`,`parent_id`,`alias`(100),`language`);

DROP INDEX `idx_alias` ON `menu`;
CREATE INDEX `idx_alias` ON `menu` (`alias`(100));

DROP INDEX `idx_path` ON `menu`;
CREATE INDEX `idx_path` ON `menu` (`alias`(100));

ALTER TABLE `menu` MODIFY COLUMN `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'The SEF alias of the menu item.';

-- users
DROP INDEX `idx_name` ON `users`;
CREATE INDEX `idx_name` ON `users` (`name`(100));

ALTER TABLE `users` MODIFY COLUMN `name` varchar(400) NOT NULL DEFAULT '';

-- users_keys
ALTER TABLE `users_keys` MODIFY COLUMN `user_id` varchar(150) NOT NULL;
ALTER TABLE `users_keys` MODIFY COLUMN `series` varchar(191) NOT NULL;

-- users_sessions
ALTER TABLE `users_sessions` MODIFY COLUMN `session_id` varchar(191) NOT NULL DEFAULT '';

# Changes in character encoding
ALTER TABLE `assets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `extensions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `languages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `languages_associations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `menu` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `menu_types` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `modules` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `modules_menu` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `templates` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users_groups_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users_keys` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users_profiles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users_sessions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;