Add 'display' DB field for nicer variants display. Remove join on Variants table...

[vchess.git] / server / db / create.sql

diff --git a/server/db/create.sql b/server/db/create.sql
index 7c361b4..e26b03b 100644 (file)
--- a/server/db/create.sql
+++ b/server/db/create.sql
@@ -3,6 +3,8 @@
 create table Variants (
   id integer primary key,
   name varchar unique,
 create table Variants (
   id integer primary key,
   name varchar unique,

+  display varchar,
+  groupe integer,

   description text,
   noProblems boolean
 );
   description text,
   noProblems boolean
 );


@@ -16,7 +18,7 @@ create table Users (
   sessionToken varchar,
   created datetime,
   notify boolean,
   sessionToken varchar,
   created datetime,
   notify boolean,

-  newsRead datetime
+  bio text default ''

 );
 
 create table Problems (
 );
 
 create table Problems (


@@ -31,14 +33,6 @@ create table Problems (
   foreign key (vid) references Variants(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,
   added datetime,
 create table Challenges (
   id integer primary key,
   added datetime,


@@ -52,6 +46,12 @@ create table Challenges (
   foreign key (vid) references Variants(id)
 );
 
   foreign key (vid) references Variants(id)
 );
 

+create table GameStat (
+  vid integer,
+  total integer default 0,
+  foreign key (vid) references Variants(id)
+);
+

 create table Games (
   id integer primary key,
   vid integer,
 create table Games (
   id integer primary key,
   vid integer,


@@ -68,6 +68,8 @@ create table Games (
   rematchOffer character default '',
   deletedByWhite boolean,
   deletedByBlack boolean,
   rematchOffer character default '',
   deletedByWhite boolean,
   deletedByBlack boolean,

+  chatReadWhite datetime,
+  chatReadBlack datetime,

   foreign key (vid) references Variants(id),
   foreign key (white) references Users(id),
   foreign key (black) references Users(id)
   foreign key (vid) references Variants(id),
   foreign key (white) references Users(id),
   foreign key (black) references Users(id)