Removed NOT NULL from score in the SQL schema.

[ccbs] / sql / ccbs.sql

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index e0b62840e2a78a63994a78038fc91d0b9aec9812..374af67217eac9e62c9f1e9142320d6a52b07977 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -69,11 +69,30 @@ CREATE TABLE tournaments (
        UNIQUE ( season, tournamentname )
 );
 
        UNIQUE ( season, tournamentname )
 );
 

+CREATE TABLE tournamentparticipation (
+       tournament INTEGER NOT NULL REFERENCES tournaments,
+       player INTEGER NOT NULL REFERENCES players,
+       paid BOOLEAN NOT NULL,
+
+       PRIMARY KEY ( tournament, player )
+);
+
+CREATE TABLE tournamentrankings (
+       tournament INTEGER NOT NULL REFERENCES tournaments,
+       ranking INTEGER NOT NULL,
+       player INTEGER NOT NULL REFERENCES players,
+       points INTEGER,
+
+       UNIQUE (tournament, player),
+       PRIMARY KEY (tournament, ranking)
+);
+

 CREATE TABLE rounds (
        tournament INTEGER NOT NULL REFERENCES tournaments,
        round INTEGER NOT NULL,
        randomsongs INTEGER NOT NULL,
        chosensongs INTEGER NOT NULL,
 CREATE TABLE rounds (
        tournament INTEGER NOT NULL REFERENCES tournaments,
        round INTEGER NOT NULL,
        randomsongs INTEGER NOT NULL,
        chosensongs INTEGER NOT NULL,

+       numqualifying INTEGER,

 
        PRIMARY KEY (tournament, round)
 );
 
        PRIMARY KEY (tournament, round)
 );


@@ -117,12 +136,12 @@ CREATE TABLE scores (
        player INTEGER NOT NULL REFERENCES players,
        songnumber INTEGER NOT NULL,
        
        player INTEGER NOT NULL REFERENCES players,
        songnumber INTEGER NOT NULL,
        

-       song INTEGER NOT NULL REFERENCES songs,
+       song INTEGER REFERENCES songs,

        playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
        difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
        
        playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
        difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
        

-       chosen BOOLEAN NOT NULL,
-       score INTEGER NOT NULL CHECK (score >= 0 AND score <= 10000),
+       chosen BOOLEAN,
+       score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),

        
        FOREIGN KEY (song) REFERENCES songs (song),
        FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
        
        FOREIGN KEY (song) REFERENCES songs (song),
        FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),