89.sql (1630B)
1 -- +migrate Up 2 3 CREATE TABLE IF NOT EXISTS sessions_tmp ( 4 token VARCHAR(255) PRIMARY KEY NOT NULL, 5 expires_at DATETIME NOT NULL, 6 deleted_at DATETIME NULL, 7 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 8 user_id INTEGER NOT NULL, 9 client_ip VARCHAR(45) NULL, 10 user_agent VARCHAR(255) NULL, 11 CONSTRAINT sessions_user_id_fk 12 FOREIGN KEY (user_id) 13 REFERENCES users (id) 14 ON DELETE CASCADE 15 ON UPDATE CASCADE); 16 17 INSERT INTO sessions_tmp (token, expires_at, deleted_at, created_at, user_id, client_ip, user_agent) 18 SELECT token, expires_at, deleted_at, created_at, user_id, client_ip, user_agent FROM sessions; 19 20 DROP INDEX sessions_user_id_idx; 21 DROP INDEX sessions_token_idx; 22 DROP TABLE sessions; 23 24 ALTER TABLE sessions_tmp RENAME TO sessions; 25 26 CREATE INDEX sessions_user_id_idx ON sessions (user_id); 27 28 CREATE TABLE IF NOT EXISTS session_notifications ( 29 id INTEGER NOT NULL PRIMARY KEY, 30 session_token VARCHAR(255) NOT NULL, 31 message TEXT NOT NULL, 32 is_read TINYINT(1) NOT NULL DEFAULT 0, 33 read_at DATETIME NULL, 34 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 35 CONSTRAINT session_notifications_session_token_fk 36 FOREIGN KEY (session_token) 37 REFERENCES sessions (token) 38 ON DELETE CASCADE 39 ON UPDATE CASCADE); 40 CREATE INDEX session_notifications_session_token_idx ON session_notifications (session_token); 41 CREATE INDEX session_notifications_is_read_idx ON session_notifications (is_read); 42 CREATE INDEX session_notifications_read_at_idx ON session_notifications (read_at); 43 44 -- +migrate Down