From: Steinar H. Gunderson Date: Wed, 30 Mar 2005 01:22:49 +0000 (+0000) Subject: Make the DDR Europe parsing scripts distinguish tournaments based on their season... X-Git-Url: https://git.sesse.net/?p=ccbs;a=commitdiff_plain;h=4357baa5539a086b35a26c70f5e27844e739e14f Make the DDR Europe parsing scripts distinguish tournaments based on their season as well, not just the name. --- diff --git a/parse/parse-ddreurope-tournament.pl b/parse/parse-ddreurope-tournament.pl index 0771cc8..3f75c41 100644 --- a/parse/parse-ddreurope-tournament.pl +++ b/parse/parse-ddreurope-tournament.pl @@ -1,4 +1,4 @@ -#! /usr/bin/perl +h#! /usr/bin/perl use strict; use warnings; use Date::Manip; @@ -27,7 +27,7 @@ while (<>) { print "INSERT INTO tournaments \n"; print " (season, tournamentname, country, location, \"date\", machine, scoringsystem) VALUES (\n"; - print " (SELECT season FROM seasons WHERE seasonname='$season'),\n"; + print " (SELECT season FROM seasons WHERE seasonname='$season' AND season=( SELECT season FROM seasons WHERE seasonname='$season' ),\n"; print " '$name',\n"; print " (SELECT country FROM countries WHERE countryname='$country'),\n"; print " '$location',\n"; @@ -42,7 +42,7 @@ while (<>) { $player = $1; printf "INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round, $group,\n"; print " (SELECT player FROM players WHERE nick='$player'),\n"; print " $position\n"; @@ -57,7 +57,7 @@ while (<>) { # random songs while (s/ (\d+) <\/td>//x) { printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, chosen, score) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round, $group,\n"; print " (SELECT player FROM players WHERE nick='$player'),\n"; print " $i,\n"; @@ -74,7 +74,7 @@ while (<>) { $song =~ s/'/\\'/g; printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, chosen, score) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round, $group,\n"; print " (SELECT player FROM players WHERE nick='$player'),\n"; print " $i,\n"; @@ -91,7 +91,7 @@ while (<>) { print "INSERT INTO rounds (tournament, round, randomsongs, chosensongs) \n"; print " VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round,\n"; print " 0, 0);\n"; # Don't worry, we'll fix it later :-P } @@ -105,7 +105,7 @@ while (<>) { $position = 1; print "INSERT INTO groups (tournament, round, parallel) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round, $group);\n"; # Find the random songs, if any @@ -118,7 +118,7 @@ while (<>) { push @rsongs, $song; print "INSERT INTO roundrandomsongs (tournament, round, parallel, song) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $round, $group,\n"; print " (SELECT song FROM songs WHERE lower(title)=lower('$song'))\n"; print ");\n"; @@ -126,14 +126,14 @@ while (<>) { # Correct the random songs in the table printf "UPDATE rounds SET randomsongs=%u WHERE \n", scalar @rsongs; - print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name') AND \n"; + print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n"; print " round=$round;\n"; } # Header for chosen song (evil) if (/ <\/th>/x) { printf "UPDATE rounds SET chosensongs=1 WHERE \n"; - print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name') AND \n"; + print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n"; print " round=$round;\n"; } @@ -149,7 +149,7 @@ while (<>) { printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $nick, $nick; print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " 1,\n"; print " (SELECT player FROM players WHERE nick='$nick'),\n"; print " $points);\n"; @@ -168,7 +168,7 @@ while (<>) { printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $nick, $nick; print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n"; print " $ranking,\n"; print " (SELECT player FROM players WHERE nick='$nick'),\n"; print " $points);\n";