Removed NOT NULL from score in the SQL schema.

[ccbs] / sql / ccbs.sql

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index 44dcdf98a6617e01ac5f35a284fce98e218a765b..374af67217eac9e62c9f1e9142320d6a52b07977 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -69,11 +69,19 @@ 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,
 CREATE TABLE tournamentrankings (
        tournament INTEGER NOT NULL REFERENCES tournaments,
        ranking INTEGER NOT NULL,
        player INTEGER NOT NULL REFERENCES players,

-       points INTEGER
+       points INTEGER,

 
        UNIQUE (tournament, player),
        PRIMARY KEY (tournament, ranking)
 
        UNIQUE (tournament, player),
        PRIMARY KEY (tournament, ranking)


@@ -84,6 +92,7 @@ CREATE TABLE rounds (
        round INTEGER NOT NULL,
        randomsongs INTEGER NOT NULL,
        chosensongs INTEGER NOT NULL,
        round INTEGER NOT NULL,
        randomsongs INTEGER NOT NULL,
        chosensongs INTEGER NOT NULL,

+       numqualifying INTEGER,

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


@@ -127,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),