server/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   noProblems boolean
   8 );
   9
  10 create table Users (
  11   id integer primary key,
  12   name varchar unique,
  13   email varchar unique,
  14   loginToken varchar,
  15   loginTime datetime,
  16   sessionToken varchar,
  17   created datetime,
  18   notify boolean,
  19   newsRead datetime
  20 );
  21
  22 create table Problems (
  23   id integer primary key,
  24   added datetime,
  25   fen varchar,
  26   uid integer,
  27   vid integer,
  28   instruction text,
  29   solution text,
  30   foreign key (uid) references Users(id),
  31   foreign key (vid) references Variants(id)
  32 );
  33
  34 create table News (
  35   id integer primary key,
  36   uid integer,
  37   added datetime,
  38   content text,
  39   foreign key (uid) references Users(id)
  40 );
  41
  42 create table Challenges (
  43   id integer primary key,
  44   added datetime,
  45   uid integer,
  46   target integer,
  47   vid integer,
  48   randomness integer,
  49   fen varchar,
  50   cadence varchar,
  51   foreign key (uid) references Users(id),
  52   foreign key (vid) references Variants(id)
  53 );
  54
  55 create table Games (
  56   id integer primary key,
  57   vid integer,
  58   fenStart varchar, --initial state
  59   fen varchar, --current state
  60   white integer,
  61   black integer,
  62   score varchar default '*',
  63   scoreMsg varchar,
  64   cadence varchar,
  65   randomness integer, --for rematch
  66   created datetime,
  67   drawOffer character default '',
  68   rematchOffer character default '',
  69   deletedByWhite boolean,
  70   deletedByBlack boolean,
  71   chatReadWhite datetime,
  72   chatReadBlack datetime,
  73   foreign key (vid) references Variants(id),
  74   foreign key (white) references Users(id),
  75   foreign key (black) references Users(id)
  76 );
  77
  78 create table Chats (
  79   gid integer,
  80   name varchar,
  81   msg varchar,
  82   added datetime
  83 );
  84
  85 create table Moves (
  86   gid integer,
  87   squares varchar, --description, appear/vanish/from/to
  88   played datetime, --when was this move played?
  89   idx integer, --index of the move in the game
  90   foreign key (gid) references Games(id)
  91 );
  92
  93 create index scoreIdx on Games(score);
  94
  95 pragma foreign_keys = on;