67.sql (1369B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS chat_room_groups ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 room_id INTEGER NOT NULL, 5 name VARCHAR(50) NOT NULL, 6 color VARCHAR(20) NOT NULL, 7 locked TINYINT(1) NOT NULL DEFAULT 0, 8 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 9 UNIQUE(room_id, name), 10 CONSTRAINT chat_room_groups_room_id_fk 11 FOREIGN KEY (room_id) 12 REFERENCES chat_rooms (id) 13 ON DELETE CASCADE 14 ON UPDATE CASCADE); 15 16 CREATE TABLE IF NOT EXISTS chat_room_user_groups ( 17 group_id INTEGER NOT NULL, 18 room_id INTEGER NOT NULL, 19 user_id INTEGER NOT NULL, 20 PRIMARY KEY (group_id, user_id, room_id), 21 CONSTRAINT chat_room_user_groups_group_id_fk 22 FOREIGN KEY (group_id) 23 REFERENCES chat_room_groups (id) 24 ON DELETE CASCADE 25 ON UPDATE CASCADE, 26 CONSTRAINT chat_room_user_groups_user_id_fk 27 FOREIGN KEY (user_id) 28 REFERENCES users (id) 29 ON DELETE CASCADE 30 ON UPDATE CASCADE, 31 CONSTRAINT chat_room_user_groups_room_id_fk 32 FOREIGN KEY (room_id) 33 REFERENCES chat_rooms (id) 34 ON DELETE CASCADE 35 ON UPDATE CASCADE); 36 37 ALTER TABLE chat_messages ADD COLUMN group_id INTEGER NULL; 38 CREATE INDEX chat_messages_group_id_idx ON chat_messages (group_id); 39 40 -- +migrate Down