34.sql (1322B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS forum_messages_tmp ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 uuid VARCHAR(100) UNIQUE NOT NULL, 5 message TEXT NOT NULL, 6 user_id INTEGER NOT NULL, 7 thread_id INTEGER NOT NULL, 8 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 9 CONSTRAINT forum_messages_user_id_fk 10 FOREIGN KEY (user_id) 11 REFERENCES users (id) 12 ON DELETE CASCADE 13 ON UPDATE CASCADE, 14 CONSTRAINT forum_messages_thread_id_fk 15 FOREIGN KEY (thread_id) 16 REFERENCES forum_threads (id) 17 ON DELETE CASCADE 18 ON UPDATE CASCADE); 19 20 INSERT INTO forum_messages_tmp (id, uuid, message, user_id, thread_id, created_at) 21 SELECT id, lower( 22 hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || '4' || 23 substr(hex( randomblob(2)), 2) || '-' || 24 substr('AB89', 1 + (abs(random()) % 4) , 1) || 25 substr(hex(randomblob(2)), 2) || '-' || 26 hex(randomblob(6)) 27 ), message, user_id, thread_id, created_at FROM forum_messages; 28 29 DROP TABLE forum_messages; 30 31 ALTER TABLE forum_messages_tmp RENAME TO forum_messages; 32 33 CREATE INDEX forum_messages_user_id_idx ON forum_messages (user_id); 34 CREATE INDEX forum_messages_thread_id_idx ON forum_messages (thread_id); 35 -- +migrate Down