70.sql (2397B)
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 37 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) 38 SELECT id, uuid, message, raw_message, room_id, user_id, to_user_id, system, created_at, is_hellbanned, moderators, group_id FROM chat_messages; 39 40 DROP INDEX chat_messages_room_id_idx; 41 DROP INDEX chat_messages_user_id_idx; 42 DROP INDEX chat_messages_group_id_idx; 43 DROP INDEX chat_messages_is_hellbanned_idx; 44 DROP INDEX chat_messages_moderators_idx; 45 DROP INDEX chat_messages_to_user_id_idx; 46 DROP INDEX chat_messages_created_at_idx; 47 DROP TABLE chat_messages; 48 49 ALTER TABLE chat_messages_tmp RENAME TO chat_messages; 50 51 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 52 CREATE INDEX chat_messages_user_id_idx ON chat_messages (user_id); 53 CREATE INDEX chat_messages_to_user_id_idx ON chat_messages (to_user_id); 54 CREATE INDEX chat_messages_created_at_idx ON chat_messages (created_at); 55 CREATE INDEX chat_messages_group_id_idx ON chat_messages (group_id); 56 CREATE INDEX chat_messages_is_hellbanned_idx ON chat_messages (is_hellbanned); 57 CREATE INDEX chat_messages_moderators_idx ON chat_messages (moderators); 58 59 -- +migrate Down