21 lines
709 B
SQL
21 lines
709 B
SQL
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
room_code VARCHAR(10) UNIQUE NOT NULL,
|
|
player1_id VARCHAR(50) NOT NULL,
|
|
player2_id VARCHAR(50) DEFAULT NULL,
|
|
status ENUM('waiting', 'playing', 'finished') DEFAULT 'waiting',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS game_states (
|
|
room_id INT NOT NULL,
|
|
player_id VARCHAR(50) NOT NULL,
|
|
board TEXT,
|
|
score INT DEFAULT 0,
|
|
next_piece TEXT,
|
|
is_game_over TINYINT(1) DEFAULT 0,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (room_id, player_id),
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|