Begin changing the country stuff.

- All players must now have a country.
- There are clubs.
- Countries have three-letter IOC country codes.

The changes are not yet reflected in the data.

diff --git a/sql/basic-data.sql b/sql/basic-data.sql
index 614d1f65733dfd0da7ed2b8809156bdeef5d00a6..c66825c72f52f474a163b870e248953775a38c69 100644 (file)
--- a/sql/basic-data.sql
+++ b/sql/basic-data.sql
@@ -2,8 +2,7 @@
 INSERT INTO machines (machinename) VALUES ('Dancing Stage EuroMix 2');
 INSERT INTO machines (machinename) VALUES ('DDR Extreme');
 INSERT INTO scoringsystems (scoringsystemname) VALUES ('10K Machine score');
-INSERT INTO countries (countryname) VALUES ('Europe');
-INSERT INTO countries (countryname) VALUES ('Norway');
+INSERT INTO countries (countryname) VALUES ('Europe', 'EUR');  -- special exception
 INSERT INTO seasons (seasonname) VALUES ('European Cup');
 INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2003');
 INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2004');

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index 839dd86879d6c9a2ff352caf2ea65d3b12a32879..2b1a68605721d7ce334075fbb4873eff8a83221d 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -10,8 +10,19 @@ CREATE TABLE machines (
 CREATE TABLE countries (
        country SERIAL PRIMARY KEY,
        countryname VARCHAR NOT NULL,
+       countrycode CHAR(3) NOT NULL,   -- IOC country code
 
-       UNIQUE ( countryname )
+       UNIQUE ( countryname ),
+       UNIQUE ( countrycode )
+);
+
+CREATE TABLE clubs (
+       club SERIAL PRIMARY KEY,
+       clubname VARCHAR NOT NULL,
+       clubcode CHAR(3) NOT NULL,
+
+       UNIQUE ( clubname ),
+       UNIQUE ( clubcode )
 );
 
 CREATE TABLE songs (
@@ -60,6 +71,8 @@ CREATE INDEX songratings_feetrating ON songratings ( feetrating );
 CREATE TABLE players (
        player SERIAL PRIMARY KEY,
        nick VARCHAR NOT NULL,
+       country INTEGER NOT NULL REFERENCES countries,
+       club INTEGER REFERENCES clubs,
 
        UNIQUE ( nick )
 );