21.sql (1592B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS forum_threads ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 name VARCHAR(255) NOT NULL, 5 user_id INTEGER NOT NULL, 6 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 7 CONSTRAINT forum_threads_user_id_fk 8 FOREIGN KEY (user_id) 9 REFERENCES users (id) 10 ON DELETE CASCADE 11 ON UPDATE CASCADE); 12 13 CREATE TABLE IF NOT EXISTS forum_messages ( 14 id INTEGER NOT NULL PRIMARY KEY, 15 message TEXT NOT NULL, 16 user_id INTEGER NOT NULL, 17 thread_id INTEGER NOT NULL, 18 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 19 CONSTRAINT forum_messages_user_id_fk 20 FOREIGN KEY (user_id) 21 REFERENCES users (id) 22 ON DELETE CASCADE 23 ON UPDATE CASCADE, 24 CONSTRAINT forum_messages_thread_id_fk 25 FOREIGN KEY (thread_id) 26 REFERENCES forum_threads (id) 27 ON DELETE CASCADE 28 ON UPDATE CASCADE); 29 CREATE INDEX forum_messages_thread_id_idx ON forum_messages (thread_id); 30 31 CREATE TABLE IF NOT EXISTS forum_read_records ( 32 user_id INTEGER NOT NULL, 33 thread_id INTEGER NOT NULL, 34 read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 35 PRIMARY KEY (user_id, thread_id), 36 CONSTRAINT forum_read_user_id_fk 37 FOREIGN KEY (user_id) 38 REFERENCES users (id) 39 ON DELETE CASCADE 40 ON UPDATE CASCADE, 41 CONSTRAINT forum_read_thread_id_fk 42 FOREIGN KEY (thread_id) 43 REFERENCES forum_threads (id) 44 ON DELETE CASCADE 45 ON UPDATE CASCADE); 46 -- +migrate Down