1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

/// Query to run to set up the database.
pub const INITIALISE_DATABASE: &str = r#####"





CREATE TABLE IF NOT EXISTS sessions (
    id              INTEGER PRIMARY KEY ASC,                -- Unique session ID
    expiry          DATETIME NOT NULL,                      -- Expiry datetime in RFC3339 format
    is_admin        BOOLEAN NOT NULL DEFAULT 0,             -- Whether the user has authenticated as administrator
    user_id         INTEGER REFERENCES users (id),          -- ID of user session is logged in as

    sudoku_board_id INTEGER REFERENCES sudoku_boards (id),  -- ID of board currently being solved
    board_skeleton  TEXT,                                   -- The board skeleton sent to the user
    solve_start     DATETIME,                               -- Time the solving started

    CHECK ((board_skeleton IS NULL) OR (LENGTH(board_skeleton) == 9 * 9))
);


CREATE TABLE IF NOT EXISTS sudoku_boards (
    id            INTEGER PRIMARY KEY ASC,                                               -- Unique board ID
    full_board    TEXT NOT NULL UNIQUE,                                                  -- The full "solved" board repr
    difficulty    INTEGER NOT NULL,                                                      -- Board "difficulty", between one and three
    creation_time DATETIME NOT NULL,                                                     -- Time the board was generated

    CHECK (((difficulty >= 1) AND (difficulty <= 3)) AND (LENGTH(full_board) == 9 * 9))
);







CREATE TABLE IF NOT EXISTS sudoku_solutions (
    id                     INTEGER PRIMARY KEY ASC,                                                     -- Unique solution ID
    display_name           TEXT NOT NULL,                                                               -- Solver's display name
    board_id               INTEGER NOT NULL REFERENCES sudoku_boards (id),                              -- The solved board ID
    skeleton               TEXT NOT NULL,                                                               -- The solved board skeleton
    difficulty             INTEGER NOT NULL,                                                            -- Board "difficulty", between one and three
    solution_duration_secs INTEGER NOT NULL,                                                            -- Time in seconds taken to achieve the solution
    score                  INTEGER NOT NULL,                                                            -- Score achieved for the solve
    solution_time          DATETIME NOT NULL,                                                           -- Time the solution occured at

    CHECK (((difficulty >= 1) AND (difficulty <= 3)) AND (solution_duration_secs > 0) AND (score > 0))
);



CREATE TABLE IF NOT EXISTS users (
    id                 INTEGER PRIMARY KEY ASC,     -- Unique user ID
    username           TEXT NOT NULL UNIQUE,        -- User's name or "login" or whatever you want to call it
    password           TEXT NOT NULL,               -- Doubly scrypted password text, see above.
    email              TEXT NOT NULL UNIQUE,        -- User's contact e-mail
    created_at         DATETIME NOT NULL,           -- Time user was created
    is_admin           BOOLEAN NOT NULL DEFAULT 0,  -- Whether the user has administrative privileges
    points_total       INTEGER NOT NULL DEFAULT 0,  -- Sum total of the user's points, calculated according to sudoku.md#scoring-formula, non-negative
    games_total        INTEGER NOT NULL DEFAULT 0,  -- Total amount of games played, non-negative
    games_total_easy   INTEGER NOT NULL DEFAULT 0,  -- Amount of easy games played, non-negative
    games_total_medium INTEGER NOT NULL DEFAULT 0,  -- Amount of medium games played, non-negative
    games_total_hard   INTEGER NOT NULL DEFAULT 0,  -- Amount of hard games played, non-negative

    CHECK ((points_total >= 0) AND (games_total >= 0) AND (games_total_easy >= 0) AND (games_total_medium >= 0) AND (games_total_hard >= 0))
);




"#####;