78.sql (2575B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS chat_messages_tmp ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 uuid VARCHAR(100) UNIQUE NOT NULL, 5 message TEXT NOT NULL, 6 raw_message TEXT NOT NULL, 7 room_id INTEGER NOT NULL, 8 user_id INTEGER NOT NULL, 9 upload_id INTEGER NULL, 10 to_user_id INTEGER NULL, 11 system TINYINT(1) NOT NULL DEFAULT 0, 12 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 13 is_hellbanned TINYINT(1) NOT NULL DEFAULT 0, 14 moderators TINYINT(1) NOT NULL DEFAULT 0, 15 group_id INTEGER NULL, 16 CONSTRAINT chat_messages_room_id_fk 17 FOREIGN KEY (room_id) 18 REFERENCES chat_rooms (id) 19 ON DELETE CASCADE 20 ON UPDATE CASCADE, 21 CONSTRAINT chat_messages_to_user_id_fk 22 FOREIGN KEY (to_user_id) 23 REFERENCES users (id) 24 ON DELETE CASCADE 25 ON UPDATE CASCADE, 26 CONSTRAINT chat_messages_user_id_fk 27 FOREIGN KEY (user_id) 28 REFERENCES users (id) 29 ON DELETE CASCADE 30 ON UPDATE CASCADE, 31 CONSTRAINT chat_messages_upload_id_fk 32 FOREIGN KEY (upload_id) 33 REFERENCES uploads (id) 34 ON DELETE SET NULL 35 ON UPDATE CASCADE, 36 CONSTRAINT chat_messages_group_id_fk 37 FOREIGN KEY (group_id) 38 REFERENCES chat_room_groups (id) 39 ON DELETE CASCADE 40 ON UPDATE CASCADE); 41 42 INSERT INTO chat_messages_tmp (id, uuid, message, raw_message, room_id, user_id, to_user_id, system, created_at, is_hellbanned, moderators, group_id) 43 SELECT id, uuid, message, raw_message, room_id, user_id, to_user_id, system, created_at, is_hellbanned, moderators, group_id FROM chat_messages; 44 45 DROP INDEX chat_messages_room_id_idx; 46 DROP INDEX chat_messages_user_id_idx; 47 DROP INDEX chat_messages_group_id_idx; 48 DROP INDEX chat_messages_is_hellbanned_idx; 49 DROP INDEX chat_messages_moderators_idx; 50 DROP INDEX chat_messages_to_user_id_idx; 51 DROP INDEX chat_messages_created_at_idx; 52 DROP TABLE chat_messages; 53 54 ALTER TABLE chat_messages_tmp RENAME TO chat_messages; 55 56 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 57 CREATE INDEX chat_messages_user_id_idx ON chat_messages (user_id); 58 CREATE INDEX chat_messages_to_user_id_idx ON chat_messages (to_user_id); 59 CREATE INDEX chat_messages_created_at_idx ON chat_messages (created_at); 60 CREATE INDEX chat_messages_group_id_idx ON chat_messages (group_id); 61 CREATE INDEX chat_messages_is_hellbanned_idx ON chat_messages (is_hellbanned); 62 CREATE INDEX chat_messages_moderators_idx ON chat_messages (moderators); 63 64 -- +migrate Down