X-Git-Url: https://git.auder.net/?a=blobdiff_plain;f=server%2Fdb%2Fcreate.sql;h=c9f523241b57d7542a635576ae266992d7d2b8fb;hb=aae89b49a846b2c101d74db7dff9151392d6db34;hp=5357b843fe7b7f0f39f289552edd748918be2dd3;hpb=c547fbfbf65659b98f4f1bfba4718c685ec281f8;p=vchess.git diff --git a/server/db/create.sql b/server/db/create.sql index 5357b843..c9f52324 100644 --- a/server/db/create.sql +++ b/server/db/create.sql @@ -17,8 +17,25 @@ create table Users ( notify boolean ); --- All the following tables are for correspondance play only --- (Live games are stored in browser) +create table Problems ( + id integer primary key, + added datetime, + fen varchar, + uid integer, + vid integer, + instruction text, + solution text, + foreign key (uid) references Users(id), + foreign key (vid) references Variants(id) +); + +create table News ( + id integer primary key, + uid integer, + added datetime, + content text, + foreign key (uid) references Users(id) +); create table Challenges ( id integer primary key, @@ -26,22 +43,26 @@ create table Challenges ( uid integer, target integer, vid integer, + randomness integer, fen varchar, - timeControl varchar, + cadence varchar, foreign key (uid) references Users(id), foreign key (vid) references Variants(id) ); --- NOTE: no need for a "created" field, it's deduced from first move playing time create table Games ( id integer primary key, vid integer, fenStart varchar, --initial state fen varchar, --current state - score varchar, - timeControl varchar, - created datetime, --used only for DB cleaning - drawOffer boolean, + score varchar default '*', + scoreMsg varchar, + cadence varchar, + created datetime, + drawOffer character default '', + rematchOffer character default '', + deletedByWhite boolean, + deletedByBlack boolean, foreign key (vid) references Variants(id) ); @@ -49,7 +70,7 @@ create table Chats ( gid integer, name varchar, msg varchar, - added datetime, + added datetime ); -- Store informations about players in a corr game