25.sql (1912B)
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 to_user_id INTEGER NULL, 10 system TINYINT(1) NOT NULL DEFAULT 0, 11 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 12 CONSTRAINT chat_messages_room_id_fk 13 FOREIGN KEY (room_id) 14 REFERENCES chat_rooms (id) 15 ON DELETE CASCADE 16 ON UPDATE CASCADE, 17 CONSTRAINT chat_messages_to_user_id_fk 18 FOREIGN KEY (to_user_id) 19 REFERENCES users (id) 20 ON DELETE CASCADE 21 ON UPDATE CASCADE, 22 CONSTRAINT chat_messages_user_id_fk 23 FOREIGN KEY (user_id) 24 REFERENCES users (id) 25 ON DELETE CASCADE 26 ON UPDATE CASCADE); 27 28 INSERT INTO chat_messages_tmp (id, uuid, message, raw_message, room_id, user_id, to_user_id, created_at) 29 SELECT id, lower( 30 hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || '4' || 31 substr(hex( randomblob(2)), 2) || '-' || 32 substr('AB89', 1 + (abs(random()) % 4) , 1) || 33 substr(hex(randomblob(2)), 2) || '-' || 34 hex(randomblob(6)) 35 ), message, '', room_id, user_id, to_user_id, created_at FROM chat_messages; 36 37 DROP INDEX chat_messages_room_id_idx; 38 DROP INDEX chat_messages_user_id_idx; 39 DROP INDEX chat_messages_to_user_id_idx; 40 DROP INDEX chat_messages_created_at_idx; 41 DROP TABLE chat_messages; 42 43 ALTER TABLE chat_messages_tmp RENAME TO chat_messages; 44 45 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 46 CREATE INDEX chat_messages_user_id_idx ON chat_messages (user_id); 47 CREATE INDEX chat_messages_to_user_id_idx ON chat_messages (to_user_id); 48 CREATE INDEX chat_messages_created_at_idx ON chat_messages (created_at); 49 50 -- +migrate Down