conversation_message-show_create_table.sql

clean-migration.sql

CREATE TABLE `conversation_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  `nick` varchar(64) NOT NULL DEFAULT '',
  `message` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `irc_user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_conversation_messages_on_channel_id` (`channel_id`),
  KEY `index_conversation_messages_on_timestamp` (`timestamp`),
  KEY `index_conversation_messages_on_id_and_channel_id_and_timestamp` (`id`,`channel_id`,`timestamp`),
  KEY `index_conversation_messages_on_id_and_channel_id` (`id`,`channel_id`),
  KEY `index_conversation_messages_on_id_and_timestamp` (`id`,`timestamp`),
  KEY `index_conversation_messages_on_type` (`type`),
  KEY `index_conversation_messages_on_channel_id_and_timestamp` (`channel_id`,`timestamp`),
  KEY `index_conversation_messages_on_irc_user_id` (`irc_user_id`),
  FULLTEXT KEY `index_conversation_messages_on_message` (`message`),
  FULLTEXT KEY `index_conversation_messages_on_nick` (`nick`),
  FULLTEXT KEY `index_conversation_messages_on_nick_and_message` (`nick`,`message`),
  CONSTRAINT `channel_id` FOREIGN KEY (`channel_id`) REFERENCES `log_archiver_dev`.`channels` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

diff

12,19c12,19
<   KEY `index_conversation_messages_on_channel_id` (`channel_id`),
<   KEY `index_conversation_messages_on_timestamp` (`timestamp`),
<   KEY `index_conversation_messages_on_id_and_channel_id_and_timestamp` (`id`,`channel_id`,`timestamp`),
<   KEY `index_conversation_messages_on_id_and_channel_id` (`id`,`channel_id`),
<   KEY `index_conversation_messages_on_id_and_timestamp` (`id`,`timestamp`),
<   KEY `index_conversation_messages_on_type` (`type`),
<   KEY `index_conversation_messages_on_channel_id_and_timestamp` (`channel_id`,`timestamp`),
<   KEY `index_conversation_messages_on_irc_user_id` (`irc_user_id`),
---
>   KEY `index_conversation_messages_on_channel_id_and_timestamp` (`channel_id`,`timestamp`) USING BTREE,
>   KEY `index_conversation_messages_on_channel_id` (`channel_id`) USING BTREE,
>   KEY `index_conversation_messages_on_id_and_channel_id_and_timestamp` (`id`,`channel_id`,`timestamp`) USING BTREE,
>   KEY `index_conversation_messages_on_id_and_channel_id` (`id`,`channel_id`) USING BTREE,
>   KEY `index_conversation_messages_on_id_and_timestamp` (`id`,`timestamp`) USING BTREE,
>   KEY `index_conversation_messages_on_irc_user_id` (`irc_user_id`) USING BTREE,
>   KEY `index_conversation_messages_on_timestamp` (`timestamp`) USING BTREE,
>   KEY `index_conversation_messages_on_type` (`type`) USING BTREE,
21d20
<   FULLTEXT KEY `index_conversation_messages_on_nick` (`nick`),
23,24c22,23
<   CONSTRAINT `channel_id` FOREIGN KEY (`channel_id`) REFERENCES `log_archiver_dev`.`channels` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
< ) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
---
>   FULLTEXT KEY `index_conversation_messages_on_nick` (`nick`)
> ) ENGINE=Mroonga AUTO_INCREMENT=532601 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

irclog.kazagakure.sql

CREATE TABLE `conversation_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  `nick` varchar(64) NOT NULL DEFAULT '',
  `message` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `irc_user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_conversation_messages_on_channel_id_and_timestamp` (`channel_id`,`timestamp`) USING BTREE,
  KEY `index_conversation_messages_on_channel_id` (`channel_id`) USING BTREE,
  KEY `index_conversation_messages_on_id_and_channel_id_and_timestamp` (`id`,`channel_id`,`timestamp`) USING BTREE,
  KEY `index_conversation_messages_on_id_and_channel_id` (`id`,`channel_id`) USING BTREE,
  KEY `index_conversation_messages_on_id_and_timestamp` (`id`,`timestamp`) USING BTREE,
  KEY `index_conversation_messages_on_irc_user_id` (`irc_user_id`) USING BTREE,
  KEY `index_conversation_messages_on_timestamp` (`timestamp`) USING BTREE,
  KEY `index_conversation_messages_on_type` (`type`) USING BTREE,
  FULLTEXT KEY `index_conversation_messages_on_message` (`message`),
  FULLTEXT KEY `index_conversation_messages_on_nick_and_message` (`nick`,`message`),
  FULLTEXT KEY `index_conversation_messages_on_nick` (`nick`)
) ENGINE=Mroonga AUTO_INCREMENT=532601 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

どちらも、マイグレーションのバージョンは 20181010040315 です。
clean-migration.sql は、db/migrate/20161128183000_create_conversation_messages.rb の書き換えが行なわれているもので、
irclog.kazagakure.sql は、上記ファイルの書き換えを行なっておらず、手動で追加のSQLも実行していません。