X-Git-Url: https://git.auder.net/?a=blobdiff_plain;f=server%2Fdb%2Fcreate.sql;h=5aba8ff0e66a570641362c9e4341fcd150869026;hb=d9a7a1e40254bda6e545514596a7363048c084f9;hp=9a8f785cc88fb824c8df75c0931fed59d455605b;hpb=83494c7fbd83fafa28c5a434a1c83f56c17e3d04;p=vchess.git diff --git a/server/db/create.sql b/server/db/create.sql index 9a8f785c..5aba8ff0 100644 --- a/server/db/create.sql +++ b/server/db/create.sql @@ -14,11 +14,29 @@ create table Users ( loginTime datetime, sessionToken varchar, created datetime, - notify boolean + notify boolean, + newsRead datetime ); --- 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,24 +44,37 @@ 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 + score varchar default '*', + scoreMsg varchar, + cadence varchar, + randomness integer, --for rematch + created datetime, + drawOffer character default '', + rematchOffer character default '', + deletedByWhite boolean, + deletedByBlack boolean, foreign key (vid) references Variants(id) ); +create table Chats ( + gid integer, + name varchar, + msg varchar, + added datetime +); + -- Store informations about players in a corr game create table Players ( gid integer, @@ -56,7 +87,6 @@ create table Players ( create table Moves ( gid integer, squares varchar, --description, appear/vanish/from/to - message varchar, played datetime, --when was this move played? idx integer, --index of the move in the game foreign key (gid) references Games(id)