PRI/database_script.sql at master · AssuncaoAndre/PRI · GitHub

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
DROP TABLE IF EXISTS games;
DROP TABLE IF EXISTS players;
DROP TABLE IF EXISTS openings;

create table players (

    player_id INTEGER PRIMARY KEY AUTOINCREMENT,
    irl_name VARCHAR(32),
    online_name VARCHAR(32),
    title VARCHAR(16) NOT NULL,
    bio text

);

create table openings (
    opening_id INTEGER PRIMARY KEY AUTOINCREMENT,
    op_name text NOT NULL,
    code varchar(3) NOT NULL,
    pgn_moves text NOT NULL,
    op_description text,
    is_mainline INTEGER DEFAULT 0

);

create table games (

    game_id INTEGER PRIMARY KEY AUTOINCREMENT,
    white VARCHAR(32) NOT NULL DEFAULT 'Unknown',
    black VARCHAR(32) NOT NULL DEFAULT 'Unknown',
    result INTEGER NOT NULL,
    white_elo INTEGER NOT NULL,
    black_elo INTEGER NOT NULL,
    opening VARCHAR(255) NOT NULL DEFAULT 'Undetermined',
    eco VARCHAR(8)

);