62.sql (1880B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS chat_inbox_messages_tmp( 3 id INTEGER NOT NULL PRIMARY KEY, 4 message TEXT NOT NULL, 5 room_id INTEGER NOT NULL, 6 user_id INTEGER NOT NULL, 7 to_user_id INTEGER NOT NULL, 8 is_read TINYINT(1) NOT NULL DEFAULT 0, 9 is_pm TINYINT(1) NOT NULL DEFAULT 0, 10 chat_message_id INTEGER NULL, 11 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 12 CONSTRAINT chat_inbox_messages_chat_message_id_fk 13 FOREIGN KEY (chat_message_id) 14 REFERENCES chat_messages (id) 15 ON DELETE SET NULL 16 ON UPDATE CASCADE, 17 CONSTRAINT chat_inbox_messages_room_id_fk 18 FOREIGN KEY (room_id) 19 REFERENCES chat_rooms (id) 20 ON DELETE CASCADE 21 ON UPDATE CASCADE, 22 CONSTRAINT chat_inbox_messages_user_id_fk 23 FOREIGN KEY (user_id) 24 REFERENCES users (id) 25 ON DELETE CASCADE 26 ON UPDATE CASCADE, 27 CONSTRAINT chat_inbox_messages_to_user_id_fk 28 FOREIGN KEY (to_user_id) 29 REFERENCES users (id) 30 ON DELETE CASCADE 31 ON UPDATE CASCADE); 32 33 INSERT INTO chat_inbox_messages_tmp (id, message, room_id, user_id, to_user_id, is_read, is_pm, created_at) 34 SELECT id, message, room_id, user_id, to_user_id, is_read, is_pm, created_at FROM chat_inbox_messages; 35 36 DROP INDEX chat_inbox_messages_to_user_id_idx; 37 DROP INDEX chat_inbox_messages_is_read_idx; 38 DROP TABLE chat_inbox_messages; 39 40 ALTER TABLE chat_inbox_messages_tmp RENAME TO chat_inbox_messages; 41 42 CREATE INDEX chat_inbox_messages_to_user_id_idx ON chat_inbox_messages (to_user_id); 43 CREATE INDEX chat_inbox_messages_is_read_idx ON chat_inbox_messages (is_read); 44 CREATE INDEX chat_inbox_messages_is_pm_idx ON chat_inbox_messages (is_pm); 45 CREATE INDEX chat_inbox_messages_chat_message_id_idx ON chat_inbox_messages (chat_message_id); 46 47 -- +migrate Down