1.sql (3366B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS users ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 username VARCHAR(50) NOT NULL UNIQUE, 5 password VARCHAR(255) NOT NULL, 6 two_factor_secret BLOB NULL, 7 two_factor_recovery BLOB NULL, 8 gpg_public_key TEXT NULL, 9 token VARCHAR(255) unique, 10 role VARCHAR(30) default 'member' not null, 11 lang VARCHAR(10) default '' not null, 12 chat_color VARCHAR(20) default '#000000' not null, 13 api_key VARCHAR(50) NOT NULL DEFAULT '', 14 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 15 deleted_at DATETIME NULL, 16 updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, 17 is_admin TINYINT(1) NOT NULL DEFAULT 0, 18 is_hellbanned TINYINT(1) NOT NULL DEFAULT 0, 19 verified TINYINT(1) NOT NULL DEFAULT 0); 20 21 CREATE TABLE IF NOT EXISTS sessions ( 22 id INTEGER NOT NULL PRIMARY KEY, 23 token VARCHAR(255) NOT NULL, 24 expires_at DATETIME NOT NULL, 25 deleted_at DATETIME NULL, 26 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 27 user_id INTEGER NOT NULL, 28 client_ip VARCHAR(45) NULL, 29 user_agent VARCHAR(255) NULL, 30 CONSTRAINT sessions_user_id_fk 31 FOREIGN KEY (user_id) 32 REFERENCES users (id) 33 ON DELETE CASCADE 34 ON UPDATE CASCADE); 35 CREATE INDEX sessions_user_id_idx ON sessions (user_id); 36 CREATE INDEX sessions_token_idx ON sessions (token); 37 38 CREATE TABLE IF NOT EXISTS invitations ( 39 id INTEGER NOT NULL PRIMARY KEY, 40 token VARCHAR(255) NOT NULL, 41 owner_user_id INTEGER NOT NULL, 42 invitee_user_id INTEGER NOT NULL, 43 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 44 updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, 45 CONSTRAINT invitations_owner_user_id_fk 46 FOREIGN KEY (owner_user_id) 47 REFERENCES users (id) 48 ON DELETE CASCADE 49 ON UPDATE CASCADE, 50 CONSTRAINT invitations_invitee_user_id_fk 51 FOREIGN KEY (invitee_user_id) 52 REFERENCES users (id) 53 ON DELETE CASCADE 54 ON UPDATE CASCADE); 55 CREATE INDEX invitations_owner_user_id_idx ON invitations (owner_user_id); 56 CREATE INDEX invitations_invitee_user_id_idx ON invitations (invitee_user_id); 57 CREATE INDEX invitations_token_idx ON invitations (token); 58 59 CREATE TABLE IF NOT EXISTS chat_rooms ( 60 id INTEGER NOT NULL PRIMARY KEY, 61 name VARCHAR(255) NOT NULL, 62 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP); 63 64 INSERT INTO chat_rooms (id, name) VALUES (1, 'general'); 65 INSERT INTO chat_rooms (id, name) VALUES (2, 'suggestions'); 66 INSERT INTO chat_rooms (id, name) VALUES (3, 'announcements'); 67 INSERT INTO chat_rooms (id, name) VALUES (4, 'moderators'); 68 INSERT INTO chat_rooms (id, name) VALUES (5, 'werewolf'); 69 70 CREATE TABLE IF NOT EXISTS chat_messages ( 71 id INTEGER NOT NULL PRIMARY KEY, 72 message VARCHAR(255) NOT NULL, 73 room_id INTEGER NOT NULL, 74 user_id INTEGER NOT NULL, 75 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 76 CONSTRAINT chat_messages_room_id_fk 77 FOREIGN KEY (room_id) 78 REFERENCES chat_rooms (id) 79 ON DELETE CASCADE 80 ON UPDATE CASCADE, 81 CONSTRAINT chat_messages_user_id_fk 82 FOREIGN KEY (user_id) 83 REFERENCES users (id) 84 ON DELETE CASCADE 85 ON UPDATE CASCADE); 86 CREATE INDEX chat_messages_room_id_idx ON chat_messages (room_id); 87 88 -- +migrate Down