screp: insert replay data directly into database

Automates either a complete insert of game and players through --dbname
option (requires replay file as argument to assertain mtime), or --dbid
to add replay data to an existing game entry (initial insert from pvpgn
report; only sets data unique to replay).

diff --git a/screp b/screp
index 4b896b58c8b20e98e030c11c0763adde85d45b43..8fb41f5b4e5c03749660151d8857665479c58cde 100755 (executable)
--- a/screp
+++ b/screp
@@ -3,15 +3,19 @@ use strict;
 use warnings;
 use Data::Dumper;
 
-our $VERSION = '1.00';
+our $VERSION = '1.01';
 
 my $SHOWWARN = 0;
 my $APMSVG = undef;
+my $DBNAME = undef;
+my $DBGAME = undef;
 
 use Getopt::Long qw(:config bundling auto_version auto_help);
 GetOptions(
        "verbose|v!" => \$SHOWWARN,
        "apm|a=s" => \$APMSVG,
+       "dbname|D=s" => \$DBNAME,
+       "dbid|d=s" => \$DBGAME,
 );
 
 use constant { APM_FIRSTFRAME => 80 / .042 };
@@ -520,6 +524,8 @@ for (@$map) {
 }
 
 for my $player (sort keys %stats) {
+       $stats{$player}{$_} = $player[$slot[$player]]{$_}
+               for keys %{ $player[$slot[$player]] };
        my $row = $stats{$player};
        $row->{last} ||= $map->[-1][0];
 #      printf("%-16s%6d actions (%3d micro,%4d macro);%4d APM\n",
@@ -562,6 +568,96 @@ for my $player (sort keys %stats) {
        ) if 0;
 }
 
+use Games::StarCraft::DB;
+my $Db = Games::StarCraft::DB->connect({RaiseError => 1})
+       or die "No database: $DBI::errstr\n";
+sub findaccount ($) {
+       my ($name) = @_;
+       my $query = $Db->query(q{
+               SELECT DISTINCT account FROM play
+               WHERE name = ? AND account IS NOT NULL
+       }, $name);
+       return $query->rows == 1 ? $query->list : undef;
+}
+
+if ($DBGAME) {{
+       print "\n";
+       my $game = $Db->query("SELECT * FROM game WHERE id=?", $DBGAME)->hash;
+       if (not $game) {
+               printf "Database game # %d not found\n", $DBGAME;
+               last;
+       }
+       if ($game->{map} ne $head->{map}) {
+               printf "Replay map (%s) does not match database map (%s)\n",
+                       $head->{map}, $game->{map};
+               last;
+       }
+
+       $Db->begin;
+       $Db->insert("game", {
+               frames => $head->{frames},
+#              map => $head->{map},
+#              start => time2str('%Y-%m-%d %X', $head->{time}),
+       #       endreplay => time2str('%Y-%m-%d %X', $repstats[9]), # mtime
+#              durationguess => \"endreplay - start",
+       });
+       $Db->update("play", {
+               name => $_->{name}, #TODO: --force
+               race => $_->{race}, #      --force
+               apm => $_->{gameactions} / ($_->{last} - APM_FIRSTFRAME) * 60 / .042,
+               team => $_->{team},
+               color => $_->{color},
+       }, {
+               game => $DBGAME,
+               slot => $_->{slot},
+       }) for values %stats;
+       $Db->commit;
+}}
+
+if ($DBNAME) {
+       print "\n";
+       my @repstats = stat $DBNAME or die "no rep: $!\n";
+       my ($name) = $DBNAME =~ m{.*/([^.]+)};
+
+       my %placetxt = (
+               bn => "bnet",
+               gr => "groningen",
+               md => "mdhq",
+       );
+       my ($placeid) = $name =~ /.*([a-z]{2})/;
+       my $place = defined $placetxt{$placeid} ? $placetxt{$placeid} : undef;
+
+       my $winslot;
+       if (@ARGV == 1 and $ARGV[0] =~ /^\d$/) {
+               $winslot = $ARGV[0];
+       }
+
+       $Db->begin;
+       $Db->insert("game", {
+               name => $name,
+               frames => $head->{frames},
+               map => $head->{map},
+               start => time2str('%Y-%m-%d %X', $head->{time}),
+               endreplay => time2str('%Y-%m-%d %X', $repstats[9]), # mtime
+#              durationguess => \"endreplay - start",
+               place => $place,
+       });
+       my $gameid = $Db->last_insert_id((undef)x4, {sequence => "game_id_seq"});
+       $Db->update("game", {durationguess => \"endreplay - start"}, {id => $gameid});
+       $Db->insert("play", {
+               game => $gameid,
+               slot => $_->{slot},
+               name => $_->{name},
+               race => $_->{race},
+               apm => $_->{gameactions} / ($_->{last} - APM_FIRSTFRAME) * 60 / .042,
+               team => $_->{team},
+               color => $_->{color},
+               account => findaccount($_->{name}),
+               result => defined $winslot ? $_->{slot} == $winslot ? 1 : -1 : 0,
+       }) for values %stats;
+       $Db->commit;
+}
+
 if ($APMSVG) {
        my @seq;  # player => time (s) => actions
        $seq[$_->[1]][$_->[0] * .042]++ for @$map;
@@ -631,6 +727,8 @@ screp [options] < [replay data]
  Options:
    --verbose
    --apm
+   --dbname
+   --dbid
 
 =head1 OPTIONS