From: Steinar H. Gunderson <sesse@samfundet.no>
Date: Wed, 30 Mar 2005 13:37:08 +0000 (+0000)
Subject: Begin changing the country stuff.
X-Git-Url: https://git.sesse.net/?p=ccbs;a=commitdiff_plain;h=eb9ee3742ba220deebc83771b03ffdc7f6a4d59c

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 614d1f6..c66825c 100644
--- 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 839dd86..2b1a686 100644
--- 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 )
 );