Skip to content

Instantly share code, notes, and snippets.

@koi-chan
Created June 25, 2019 13:29
Show Gist options
  • Save koi-chan/eb939053ac280f7fa4f492c3aa0521a2 to your computer and use it in GitHub Desktop.
Save koi-chan/eb939053ac280f7fa4f492c3aa0521a2 to your computer and use it in GitHub Desktop.
conversation_message-show_create_table.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
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
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
@koi-chan
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment