SQL Tic-Tac-Toe attempt

Question

In an attempt to practice conditional statements in SQL I decided to design a tic-tac-toe game with MySQL. Let me know if you think any of the steps could be done better.

Step 1: Create the game board:

USE test;
DROP TABLE IF EXISTS TicTacToe;
CREATE TABLE TicTacToe
    (
    ID INT NOT NULL,
    A VARCHAR(1) NULL,
    B VARCHAR(1) NULL,
    C VARCHAR(1) NULL
    );
INSERT INTO TicTacToe(ID,A,B,C)
VALUES
    (1,NULL,NULL,NULL),
    (2,NULL,NULL,NULL),
    (3,NULL,NULL,NULL);
SELECT * FROM TicTacToe;

Result:

ID   A       B       C
1    NULL    NULL    NULL
2    NULL    NULL    NULL
3    NULL    NULL    NULL

Step 2: I was going to use a global variable to determine player turn, however unfortunately I found out MySQL does not support that. So instead, I will use a table.

USE test;
DROP TABLE IF EXISTS ttt_PlayerTurn;
CREATE TABLE ttt_PlayerTurn (turn VARCHAR(1) NOT NULL);
INSERT INTO ttt_PlayerTurn (turn) VALUES ('X');
-- 1 row(s) affected

Step 3: Create a stored procedure to check if game is over or ongoing. Note: If anyone can think of a more elegant/efficient way, let me know.

USE test;
DELIMITER |
DROP PROCEDURE IF EXISTS ttt_CheckVictory|
CREATE PROCEDURE ttt_CheckVictory ()
BEGIN
SET
    @A1 = (SELECT A FROM TicTacToe WHERE ID = 1),
    @A2 = (SELECT A FROM TicTacToe WHERE ID = 2),
    @A3 = (SELECT A FROM TicTacToe WHERE ID = 3),
    @B1 = (SELECT B FROM TicTacToe WHERE ID = 1),
    @B2 = (SELECT B FROM TicTacToe WHERE ID = 2),
    @B3 = (SELECT B FROM TicTacToe WHERE ID = 3),
    @C1 = (SELECT C FROM TicTacToe WHERE ID = 1),
    @C2 = (SELECT C FROM TicTacToe WHERE ID = 2),
    @C3 = (SELECT C FROM TicTacToe WHERE ID = 3);

    CASE 
    -- Horizontal wins
        -- Horizontal win on row 1
        WHEN 
            @A1 = @B1 AND @B1 = @C1
        THEN     (SELECT *, CONCAT('Player ', @A1, ' is victorious!') AS 'Result' FROM TicTacToe);
        -- Horizontal win on row 2
        WHEN 
            @A2 = @B2 AND @B2 = @C2
        THEN     (SELECT *, CONCAT('Player ', @A2, ' is victorious!') AS 'Result' FROM TicTacToe);
        -- Horizontal win on row 3
        WHEN 
            @A3 = @B3 AND @B3 = @C3
        THEN     (SELECT *, CONCAT('Player ', @A3, ' is victorious!') AS 'Result' FROM TicTacToe);
    -- Vertical wins
        -- Vertical win on column A
        WHEN 
            @A1 = @A2 AND @A2 = @A3
        THEN     (SELECT *, CONCAT('Player ', @A1, ' is victorious!') AS 'Result' FROM TicTacToe);
        -- Vertical win on column B
        WHEN 
            @B1 = @B2 AND @B2 = @B3
        THEN     (SELECT *, CONCAT('Player ', @B1, ' is victorious!') AS 'Result' FROM TicTacToe);
        -- Vertical win on column C
        WHEN 
            @C1 = @C2 AND @C2 = @C3
        THEN     (SELECT *, CONCAT('Player ', @C1, ' is victorious!') AS 'Result' FROM TicTacToe);
    -- Diagonal wins
        -- Diagonal win from A1
        WHEN 
            @A1 = @B2 AND @B2 = @C3
        THEN     (SELECT *, CONCAT('Player ', @A1, ' is victorious!') AS 'Result' FROM TicTacToe);
        -- Diagonal win from A3
        WHEN 
            @A3 = @B2 AND @B2 = @C1
        THEN     (SELECT *, CONCAT('Player ', @A3, ' is victorious!') AS 'Result' FROM TicTacToe);
    -- Game continues
        ELSE (SELECT *, 'Game is still ongoing'  AS 'Result' FROM TicTacToe);
    END CASE;
END|
DELIMITER ;

Step 4: Create a procedure that checks whose turn it is, registers player's selection and checks for outcome.

USE test;
DELIMITER |
DROP PROCEDURE IF EXISTS ttt_PlayerMove|
CREATE PROCEDURE ttt_PlayerMove(p_move VARCHAR(1), p_column VARCHAR(1), p_row INT)
BEGIN
    -- Check for valid player input
    IF     p_move NOT IN ('X', 'O')
        THEN (SELECT 'Move must be X or O');
    END IF;
    -- Check for valid column
    IF     p_column NOT IN ('A', 'B', 'C')
        THEN (SELECT 'Column must be A, B or C');
    END IF;
    -- Check for valid row
    IF     p_row NOT IN (1,2,3)
        THEN (SELECT 'Row must be 1, 2 or 3');
    END IF;
    -- Check for player turn and update player turn
    IF p_move = (SELECT turn FROM ttt_PlayerTurn)
        THEN (SELECT 
                CONCAT('This turn belongs to player ', (SELECT turn FROM ttt_PlayerTurn), '!')
        );
        ELSE
            UPDATE TicTacToe
            SET p_column = p_move
            WHERE ID = p_row;
            UPDATE ttt_PlayerTurn
            SET turn = 
                CASE
                WHEN turn = 'X' THEN 'O'
                WHEN turn = 'O' THEN 'X'
                END;
    END IF;
    -- Check if victory is achieved
    CALL ttt_Check_Victory();
END|
DELIMITER ;

Step 5: Create a procedure to reset the board:

DELIMITER |
DROP PROCEDURE IF EXISTS ttt_ResetBoard|
CREATE PROCEDURE ttt_ResetBoard()
BEGIN
UPDATE TicTacToe SET A=NULL,B=NULL,C=NULL WHERE ID IN (1,2,3);
UPDATE ttt_PlayerTurn SET turn = 'X';
END|
DELIMITER ; 

Step 6: Implementation. This is very easy to implement using basically any application code by simply calling on the procedures:

Player move: CALL ttt_PlayerMove(X,A,1);, CALL ttt_PlayerMove(O,C,2), etc.
Check victory: CALL ttt_CheckVictory();
Reset board: CALL ttt_ResetBoard();

Answer 1

There is one big thing that I don't agree with, and that's your SQL schema. In my opinion, it is not normalized.

Each row should only contain one kind of information. One row in your TicTacToe table however, contains three pieces information: The tile A, the tile B and the tile C.

You have a major scalability issue here, that your table can only contain the information for one game. You're also not able to change the size of the board without changing the entire schema, or support TicTacToe Ultimate (even though supporting TTT Ultimate would require some more work, but I believe it would be possible if you structure things correctly).

I would recommend a more normalized approach to this. So let's see what tables we could use:

• Board (with tiles), obviously, where each row contains a tile in the board
• Game, storing information about the current player and having a Board field to connect the game to a board id. If you want flexible size of the boards, you could add sizeX and sizeY fields here.
• WinConditions, to store the data about what rows/columns/diagonals are considered as winners. This will remove the win conditions from your code and add them as data. Below, I have only one board and have linked the WinConditions to a specific board. Ideally, you could create a many-to-many relationship between WinConditions and Game, so that all ordinary Tic-Tac-Toe games can share the same set of WinConditions.

Once you have this setup properly, you'll be able to support much more than just TicTacToe. The game Connect Four actually shares a lot of logic with TicTacToe, the only difference is the size and the existence of "gravity".

Here's some SQL to show an example:

CREATE TABLE Board
    (
      ID INT,
    X INT NOT NULL,
    Y INT NOT NULL,
    VALUE VARCHAR(1) NULL,
    CONSTRAINT position PRIMARY KEY (ID, X, Y)
    );

CREATE TABLE Game
    (
    TURN INT,
      BOARD INT
    );

CREATE TABLE WinConditions (
    Board INT,
  ConditionID INT,
  TileX INT,
  TileY INT
);

Setup some win-conditions:

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 0, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 1, 1);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 2, 2);

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 0, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 1, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 2, 0);

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 1);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 2);

Insert some data into the board, causing it to look like

XOO
OX_
X_X


INSERT INTO Board (id, x, y, value) VALUES (1, 1, 1, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 1, 0, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 2, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 2, 0, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 0, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 1, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 2, 2, 'X');

Query to check for wins:

SELECT MAX(value), MIN(value), COUNT(value) FROM WinConditions `wc`
JOIN Board `b` ON (wc.tilex = b.x) AND (wc.tiley = b.y) AND (wc.board = b.id)
WHERE wc.board = 1
GROUP BY conditionID;

Output from query:

MAX(VALUE)  MIN(VALUE)  COUNT(VALUE)
X           X           3
X           O           3
X           O           2

By looking at this query, we can see that one condition has led to a win for X, because he's the only player to have played there as MAX(VALUE) == MIN(VALUE) and that COUNT(VALUE) == 3 (where three is the number of tiles in the win condition)

Unfortunately, I don't know SQL syntax good enough to expand this into SQL procedures and stuff, I will leave that up to you to do if you wish :)