view release on metacpan or  search on metacpan

lib/Games/Go/AGA/TDListDB.pm  view on Meta::CPAN

    }

    return $self->{db};
}

# library of statement handles
sub sth {
    my ($self, $name, $new) = @_;

    if (not $name) {
        $self->my_print("Statement handle name is required\n");
        die;
    }
    if (@_ > 2) {
        $self->{sth}{$name} = $new;
    }

    my $sth = $self->{sth}{$name};
    if (not $sth) {
        $self->my_print("No statement handle called '$name'\n");
        die;
    }

    return $sth;
}

sub _db_schema {
    my ($self) = @_;

    $self->db->do(
        join('',
            'CREATE TABLE IF NOT EXISTS ',
            $self->table_name,
            ' (',
                $self->sql_column_types,
            ' )',
        ),
    );

    $self->db->do(join '',
        'CREATE TABLE IF NOT EXISTS ',
        $self->table_name_meta,
        ' (',
            'key         INTEGER PRIMARY KEY, ',
            'update_time VARCHAR(12), ',
            'next_tmp_id VARCHAR(12)',
        ' )',
    );

    $self->db->do(join '',
        'INSERT OR IGNORE INTO ',
        $self->table_name_meta,
        ' (',
            'key, ',
            'update_time, ',
            'next_tmp_id',
        ' ) VALUES ( 1, 0, 1 )',
    );
}

sub update_time {
    my ($self, $new) = @_;

    if (@_ > 1) {
        $self->sth('update_time')->execute($new);
    }
    $self->sth('select_time')->execute();
    my $time = $self->sth('select_time')->fetchall_arrayref();
    $time = $time->[0][0];
    return $time || 0;
}

sub select_id {
    my ($self, $id) = @_;

    $self->sth('select_id')->execute($id);
    # ID is primary index, so can only be one - fetch into first array
    # element:
    my ($player) = $self->sth('select_id')->fetchall_arrayref;
    $player->[$self->column_idx('rank')] += 0 if (is_Rating($player->[$self->column_idx('rank')]));   # numify ratings
    return wantarray
        ? @{$player->[0]}
        : $player->[0];
}

sub insert_player {
    my ($self, @new) = @_;

    $new[$self->column_idx('id')] = $self->next_tmp_id(1) if (not $new[$self->column_idx('id')]);
    $self->sth('insert_player')->execute(@new);
    return wantarray
        ? @new
        : \@new;
}

sub next_tmp_id {
    my ($self, $used) = @_;

    $self->sth('select_next_tmp')->execute;
    my $next_tmp = $self->sth('select_next_tmp')->fetchall_arrayref;
    $next_tmp = $next_tmp->[0][0];
    $next_tmp ||= 1;
    while ($self->select_id("TMP$next_tmp")) {
        $next_tmp++
    }

    if ($used) {    # is the caller planning on allocating this one?
        $self->sth('update_next_tmp')->execute($next_tmp + 1);
    }
    return "TMP$next_tmp";
}

# reap any child zombies from earlier update_from_AGA calls
sub reap {
    my $kid;
    my $reaped = 0;
    do {
        $kid = waitpid(-1, WNOHANG);
        $reaped++ if ($kid > 0);
    } while $kid > 0;
    return $reaped;
}

sub update_from_AGA {
    my ($self) = @_;

    my $pid;
    if ($self->background) {
        $pid = fork;
        die "fork failed: $!\n" if not defined $pid;
    }
    if ($pid) {
        # parent process
        return;
    }

    if (not $self->{ua}) {
        $self->{ua} = LWP::UserAgent->new;
    }

    my $fname = $self->raw_filename;
    my $url = $self->url;
    $self->my_print("Starting $fname fetch from $url at ", scalar localtime, "\n") if ($self->verbose);
    $self->{ua}->mirror($url, $fname);
    $self->my_print("... fetch done at ", scalar localtime, "\n") if ($self->verbose);
    $self->update_from_file($fname);

    exit if (defined $pid); # exit if this is a spawned child ($pid == 0)
}

sub update_from_file {
    my ($self, $fh) = @_;

    if (not ref $fh) {
        my $fname = $fh;
        $fh = undef;
        if (not open($fh, '<', $fname)) {
            $self->my_print("Error opening $fname for reading: $!\n");
            die;
        }
    }
    $self->fh($fh);

    my $parser = Games::Go::AGA::Parse::TDList->new();
    my $verbose = $self->verbose;
    $self->my_print("Starting database update at ", scalar localtime, "\n") if ($verbose);
    $self->db->do('BEGIN');
    my $error_count = 0;
    my $ii = 0;
    my $ID = $self->column_idx('id');
    while (1) {
        $ii++;
        my $line = $self->next_line;
        last if (not defined $line);
        next if (not $line);

        if ($verbose) {
            $self->my_print('.')  if ($ii % 1000 == 0);
            $self->my_print("\n") if ($ii % 40000 == 0);
        }
        try {   # in case a line crashes, print error but continue
#$self->my_print("parse $line") if ($verbose);
            $parser->parse($line);
            my $update = $parser->as_array;
            if ($update->[$self->column_idx('last_name')] or $update->[$self->column_idx('first_name')]) {
                push @{$update}, $self->extra_columns_callback->($self, $update);
                if ($update->[$ID]) {
                    if ($update->[$ID] =~ m/tmp/i) {
                        die "TMP IDs not allowed in TDList input";
                    }
                }
                else {
                    $self->sth('select_by_name')->execute($update->[$self->column_idx('last_name')], $update->[$self->column_idx('first_name')]);
                    my $players = $self->sth('select_by_name')->fetchall_arrayref;
                    for my $player (@{$players}) {
                        if ($player->[$ID] =~ m/tmp/i) {
                            $update->[$ID] = $player->[$ID];  # already in DB (hope it's the same guy!)
                        }
                    }
                    if (not $update->[$ID]) {
                        $update->[$ID] = $self->next_tmp_id(1);
                    }
                }
                if ($self->select_id($update->[$ID])) {
                    # ID is already in database, do an update
                    $self->sth('update_id')->execute(
                        @{$update},     # new values for all columns
                        $update->[$ID],  # player ID (for WHERE condition)
                    );
                }
                else {