4.sql (1393B)
1 -- +migrate Up 2 3 CREATE TABLE IF NOT EXISTS chat_messages_tmp ( 4 id INTEGER NOT NULL PRIMARY KEY, 5 message VARCHAR(255) NOT NULL, 6 room_id INTEGER NOT NULL, 7 user_id INTEGER NOT NULL, 8 to_user_id INTEGER NULL, 9 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 10 CONSTRAINT chat_messages_room_id_fk 11 FOREIGN KEY (room_id) 12 REFERENCES chat_rooms (id) 13 ON DELETE CASCADE 14 ON UPDATE CASCADE, 15 CONSTRAINT chat_messages_to_user_id_fk 16 FOREIGN KEY (room_id) 17 REFERENCES users (id) 18 ON DELETE CASCADE 19 ON UPDATE CASCADE, 20 CONSTRAINT chat_messages_user_id_fk 21 FOREIGN KEY (user_id) 22 REFERENCES users (id) 23 ON DELETE CASCADE 24 ON UPDATE CASCADE); 25 26 INSERT INTO chat_messages_tmp (id, message, room_id, user_id, created_at) 27 SELECT id, message, room_id, user_id, created_at FROM chat_messages; 28 29 DROP INDEX chat_messages_room_id_idx; 30 DROP INDEX chat_messages_created_at_idx; 31 DROP TABLE chat_messages; 32 33 ALTER TABLE chat_messages_tmp RENAME TO chat_messages; 34 35 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 36 CREATE INDEX chat_messages_user_id_idx ON chat_messages (user_id); 37 CREATE INDEX chat_messages_to_user_id_idx ON chat_messages (to_user_id); 38 CREATE INDEX chat_messages_created_at_idx ON chat_messages (created_at); 39 40 -- +migrate Down