5.sql (1488B)
1 -- +migrate Up 2 3 CREATE TABLE IF NOT EXISTS chat_messages_tmp ( 4 id INTEGER NOT NULL PRIMARY KEY, 5 message TEXT 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, to_user_id, created_at) 27 SELECT id, message, room_id, user_id, to_user_id, created_at FROM chat_messages; 28 29 DROP INDEX chat_messages_room_id_idx; 30 DROP INDEX chat_messages_user_id_idx; 31 DROP INDEX chat_messages_to_user_id_idx; 32 DROP INDEX chat_messages_created_at_idx; 33 DROP TABLE chat_messages; 34 35 ALTER TABLE chat_messages_tmp RENAME TO chat_messages; 36 37 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 38 CREATE INDEX chat_messages_user_id_idx ON chat_messages (user_id); 39 CREATE INDEX chat_messages_to_user_id_idx ON chat_messages (to_user_id); 40 CREATE INDEX chat_messages_created_at_idx ON chat_messages (created_at); 41 42 -- +migrate Down