Sync from --pgweb.

[ccbs] / parse / store-ccbs-tournament.pl

diff --git a/parse/store-ccbs-tournament.pl b/parse/store-ccbs-tournament.pl
new file mode 100644 (file)
index 0000000..77647e8
--- /dev/null
+++ b/parse/store-ccbs-tournament.pl
@@ -0,0 +1,124 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use Date::Manip;
+require "../html/ccbs.pm";
+
+$SIG{__DIE__} = sub {
+       print STDERR shift;
+       exit;
+};
+
+#
+# Not really a parser, but lets us export entire tournaments from one CCBS
+# database to another in a simple way without having to worry about the
+# numerical details.
+#
+
+my $tno = shift;
+die "Missing tournament number (give on command line)." if (!defined($tno));
+
+print "begin;\n";
+
+my $dbh = ccbs::db_connect();
+
+# The tournament itself
+my $tournament = $dbh->selectrow_hashref('SELECT * FROM tournaments NATURAL JOIN countries NATURAL JOIN seasons NATURAL JOIN machines NATURAL JOIN scoringsystems WHERE tournament=?', undef, $tno);
+
+print "INSERT INTO tournaments \n";
+print "  (season, tournamentname, country, location, \"date\", machine, scoringsystem) VALUES (\n";
+print "    (SELECT season FROM seasons WHERE seasonname='$tournament->{'seasonname'}' ),\n";
+print "    '$tournament->{'tournamentname'}',\n";
+print "    (SELECT country FROM countries WHERE countryname='$tournament->{'countryname'}'),\n";
+print "    '$tournament->{'location'}',\n";
+print "    '$tournament->{'date'}',\n";
+print "    (SELECT machine FROM machines WHERE machinename='$tournament->{'machinename'}'),\n";
+print "    (SELECT scoringsystem FROM scoringsystems WHERE scoringsystemname='$tournament->{'scoringsystemname'}')\n";
+print ");\n";
+
+# useful
+my $tsql = "(SELECT tournament FROM tournaments WHERE tournamentname='$tournament->{'tournamentname'}' AND season=( SELECT season FROM seasons WHERE seasonname='$tournament->{'seasonname'}' ))";
+
+# Rankings, and players (FIXME: do clubs as well)
+my $rankings = ccbs::db_fetch_all($dbh, 'SELECT * FROM tournamentrankings NATURAL JOIN players NATURAL JOIN countries WHERE tournament=?', $tno);
+for my $rank (@$rankings) {
+       printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick, ( SELECT country FROM countries WHERE countryname='%s' ) AS country WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $rank->{'nick'}, $rank->{'countryname'}, $rank->{'nick'};
+
+       my $p = $rank->{'points'};
+       $p = 'NULL' if (!defined($p));
+
+       print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n";
+       print "   $tsql,";
+       print "   $rank->{'ranking'},\n";
+       print "   (SELECT player FROM players WHERE nick='$rank->{'nick'}'),\n";
+       print "   $p);\n";
+}
+
+# Skip tournamentparticipation, it is not needed except during the tournament.
+
+# Rounds
+my $rounds = ccbs::db_fetch_all($dbh, 'SELECT * FROM rounds WHERE tournament=?', $tno);
+for my $round (@$rounds) {
+       print "INSERT INTO rounds ( tournament, round, randomsongs, chosensongs, numqualifying ) VALUES (\n";
+       print "   $tsql,\n";
+       print "   $round->{'round'},\n";
+       print "   $round->{'randomsongs'},\n";
+       print "   $round->{'chosensongs'},\n";
+       print "   $round->{'numqualifying'});\n";
+}
+
+# Groups
+my $groups = ccbs::db_fetch_all($dbh, 'SELECT * FROM groups WHERE tournament=?', $tno);
+for my $group (@$groups) {
+       print "INSERT INTO groups ( tournament, round, parallel ) VALUES (\n";
+       print "   $tsql,\n";
+       print "   $group->{'round'},\n";
+       print "   $group->{'parallel'});\n";
+}
+
+# Round random songs
+my $rrs = ccbs::db_fetch_all($dbh, 'SELECT * FROM roundrandomsongs NATURAL JOIN songs WHERE tournament=?', $tno);
+for my $rr (@$rrs) {
+       (my $title = $rr->{'title'}) =~ s/'/\\'/g;
+
+       print "INSERT INTO roundrandomsongs ( tournament, round, parallel, song ) VALUES (\n";
+       print "   $tsql,\n";
+       print "   $rr->{'round'},\n";
+       print "   $rr->{'parallel'},\n";
+       print "   ( SELECT song FROM songs WHERE lower(title)=lower('$title') ));\n";
+}
+
+# Round participation
+my $rps = ccbs::db_fetch_all($dbh, 'SELECT * FROM roundparticipation NATURAL JOIN players WHERE tournament=?', $tno);
+for my $rp (@$rps) {
+       print "INSERT INTO roundparticipation ( tournament, round, parallel, player, position ) VALUES (\n";
+       print "   $tsql,\n";
+       print "   $rp->{'round'},\n";
+       print "   $rp->{'parallel'},\n";
+       print "   ( SELECT player FROM players WHERE nick='$rp->{'nick'}' ),\n";
+       print "   $rp->{'position'});\n";
+}
+
+# And finally, scores
+my $scores = ccbs::db_fetch_all($dbh, 'SELECT * FROM scores NATURAL JOIN songs NATURAL JOIN players WHERE tournament=?', $tno);
+for my $score (@$scores) {
+       my $chosen = $score->{'chosen'} ? 't' : 'f';
+       (my $title = $score->{'title'}) =~ s/'/\\'/g;
+
+       print "INSERT INTO scores ( tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score ) VALUES (\n";
+       print "   $tsql,\n";
+       print "   $score->{'round'},\n";
+       print "   $score->{'parallel'},\n";
+       print "   ( SELECT player FROM players WHERE nick='$score->{'nick'}' ),\n";
+       print "   $score->{'songnumber'},\n";
+       print "   ( SELECT song FROM songs WHERE lower(title)=lower('$title') ),\n";
+       print "   '$score->{'playmode'}',\n";
+       print "   '$score->{'difficulty'}',\n";
+       print "   '$chosen',\n";
+       print "   $score->{'score'});\n";
+}
+
+print "commit;\n";
+
+$dbh->disconnect;
+