42 lines
2.0 KiB
SQL
42 lines
2.0 KiB
SQL
-- Multiplayer room support for the browser Tetris game.
|
|
CREATE TABLE IF NOT EXISTS tetris_rooms (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
room_code VARCHAR(8) NOT NULL,
|
|
status ENUM('waiting', 'active', 'closed') NOT NULL DEFAULT 'waiting',
|
|
host_player_id BIGINT UNSIGNED NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
expires_at DATETIME NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uniq_tetris_rooms_code (room_code),
|
|
KEY idx_tetris_rooms_status (status),
|
|
KEY idx_tetris_rooms_updated (updated_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS tetris_room_players (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
room_id BIGINT UNSIGNED NOT NULL,
|
|
player_token CHAR(48) NOT NULL,
|
|
player_slot TINYINT UNSIGNED NOT NULL,
|
|
display_name VARCHAR(48) NOT NULL DEFAULT 'Player',
|
|
connection_status ENUM('connected', 'disconnected') NOT NULL DEFAULT 'connected',
|
|
game_status ENUM('ready', 'playing', 'paused', 'game_over') NOT NULL DEFAULT 'ready',
|
|
score INT UNSIGNED NOT NULL DEFAULT 0,
|
|
lines_cleared INT UNSIGNED NOT NULL DEFAULT 0,
|
|
level INT UNSIGNED NOT NULL DEFAULT 1,
|
|
board_state_json JSON NULL,
|
|
piece_state_json JSON NULL,
|
|
meta_json JSON NULL,
|
|
joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uniq_tetris_room_players_token (player_token),
|
|
UNIQUE KEY uniq_tetris_room_players_slot (room_id, player_slot),
|
|
KEY idx_tetris_room_players_room (room_id),
|
|
KEY idx_tetris_room_players_seen (last_seen_at),
|
|
CONSTRAINT fk_tetris_room_players_room
|
|
FOREIGN KEY (room_id) REFERENCES tetris_rooms(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|