db/create.sql

   1 -- Database should be in this folder, and named 'vchess.sqlite'
   2
   3 create table Variants (
   4     id integer primary key,
   5     name varchar unique,
   6     description text
   7 );
   8
   9 create table Users (
  10     id integer primary key,
  11     name varchar unique,
  12     email varchar unique,
  13     loginToken varchar,
  14     loginTime datetime,
  15     sessionToken varchar,
  16     notify boolean
  17 );
  18
  19 create table Problems (
  20     id integer primary key,
  21     added datetime,
  22     uid integer,
  23     vid integer,
  24     fen varchar,
  25     instructions text,
  26     solution text,
  27     foreign key (uid) references Users(id),
  28     foreign key (vid) references Variants(id)
  29 );
  30
  31 -- All the following tables are for correspondance play only
  32 -- (Live games are stored only in browsers)
  33
  34 create table Challenges (
  35     id integer primary key,
  36     added datetime,
  37     uid integer,
  38     vid integer,
  39     foreign key (uid) references Users(id),
  40     foreign key (vid) references Variants(id)
  41 );
  42
  43 -- Store informations about players who accept a challenge
  44 create table WillPlay (
  45     cid integer,
  46     uid integer,
  47     yes boolean,
  48     foreign key (cid) references Challenges(id),
  49     foreign key (uid) references Users(id)
  50 );
  51
  52 create table Games (
  53     id integer primary key,
  54     vid integer,
  55     fen varchar, --initial position
  56     score varchar,
  57     foreign key (vid) references Variants(id)
  58 );
  59
  60 -- Store informations about players in a corr game
  61 create table Players (
  62     uid integer,
  63     color character,
  64     gid integer,
  65     foreign key (uid) references Users(id),
  66     foreign key (gid) references Games(id)
  67 );
  68
  69 create table Moves (
  70     gid integer,
  71     move varchar,
  72     played datetime, --when was this move played?
  73     idx integer, --index of the move in the game
  74     color character, --required for e.g. Marseillais Chess
  75     foreign key (gid) references Games(id)
  76 );
  77
  78 pragma foreign_keys = on;