NAME

Games::Go::AGA::TDListDB - an SQL object for holding AGA TDList data

VERSION

version 0.048

SYNOPSIS

  use Games::Go::AGA::TDListDB;

DESCRIPTION

Games::Go::AGA::TDListDB builds a database (SQLite by default) of information from the TDList file provided by the American Go Association.

An update method is available that can reach out to the AGA website and grab the latest TDList information.

Accessors

All of the options listed under the new method (below) may also be used as accessors.

Methods

$tdlist = Games::Go::AGA::TDListDB->new( [ %options ] );

Creates a new TdListDB object. The following options may be supplied (and may also be accessed via functions of the same name):

db => $db

If $db (a perl DBI object) is supplied, it is used as the database object handle, otherwise an SQLite DBI handle is created and used.

The return value is the DBI object you want to use for regular database operations, such as inserting, updating, etc. However, see also the predefined statement handles (tdlist->sth below), the statement you need may already be there.

dbdname => 'path/to/filename'

This is the SQLite database filename used when no db object is supplied to new. If the file does not exist, it is created and populated. The default filename is 'tdlistdb.sqlite'.

max_update_errors => integer

An update_from_file or update_from_AGA counts errors until this number is reached, at which point the update gives up and throws an exception. The default value is 10.

url => 'http://where.to.find.tdlist'

The URL to retrieve TDList from. The default is 'http://www.usgo.org/ratings/TDListN.txt'.

raw_filename => 'TDList.txt'

When fetching from the AGA, the TDList data is dumped into this filename. If this file already exists, and is not older than the data at the AGA, the fetch is skipped (since the data should be the same - see perldoc LWP::UserAgent mirror).

background => true or false

When true, calls to update_from_AGA will be run in the background in a forked process. When false, update_from_AGA blocks until complete (which could be several minutes).

NOTE: if background is true, you should arrange to call the reap method periodically, or make other arrangements to remove zombies.

table_name => 'DB_table_name'

The name of the database table. An additional table (retrievable with the table_name_meta read-only accessor) is also created to hold the table's update_time and next_tmp_id.

When returning the table name, the value is always metaquoted.

The default table name is 'tdlistn'.

extra_columns => [ {column_name => column_TYPE}, ... ]

If you need extra columns in the database, add the names/column types here. They are used only in the creation of the table schema if the database doesn't already exist. The default columns are:

    {last_name  => 'VARCHAR(256)'        },
    {first_name => 'VARCHAR(256)'        },
    {id         => 'INTEGER PRIMARY KEY' },
    {membership => 'VARCHAR(256)'        },
    {rank       => 'VARCHAR(256)'        },
    {date       => 'VARCHAR(256)'        },
    {club       => 'VARCHAR(256)'        },
    {state      => 'VARCHAR(256)'        },
    {extra      => 'VARCHAR(256)'        },

which are the columns found in TDList.txt from the AGA. When defining extra columns, take care to set a proper SQL column type and not to overlap these existing names.

To fill in these extra columns, you might want to use:

extra_columns_callback => sub { ...; return @column_values; }

This callback is called for each record added (or updated) to the database during update_from_AGA or update_from_file. It is called with the object pointer, and a ref to an array containing the values of the default columns as listed above. It should return an array of the values for the extra columns in the same order as given in extra_columns. Alternatively, it can directly append those values onto the passed in array ref.

    extra_columns          => [ 'rank_range' ], # name(s) for the extra column(s)
    extra_columns_callback => sub {
        my ($self, $columns) = @_;
        # add extra column indicating Dan or kyu
        return '' if not $columns->[$self->column_idx('rank')];
        return 'Dan' if Rank_to_Rating( $columns->[$self->column_idx('rank')] ) > 0;
        return 'Kyu';
    }

This function should always return exactly the number of extra columns defined in the extra_columns option - the returned value may be the empty string ('').

$tdlistdb->print_cb( [ \&callback ]

Set/get the print callback. Whenever something is to be printed, the callback function is called with the print arguments. callback defaults to the standard perl print function, and new callback functions should be written to take arguments the same way print does.

$tdlistdb->my_print( @args )

Calls the my_print callback with @args.

$tdlistdb->column_idx( [ 'name' ] )

When 'name' is defined, it is lower-cased, and the column index for 'name' (or undef if 'name' isn't one of the default column names) is returned.

When 'name' is not provided, returns an array (or ref to array in scalar context) of the default column names, in order.

These are the default columns by name:

last_name

first_name

id

membership

rank

date

club

state

extra

$tdlistdb->update_from_AGA( [ $force ] )

Reach out to the American Go Association (AGA) ratings web page and grab the most recent TDList information. Update the database. May throw an exception if the update fails for any of a number of reasons.

$tdlistdb->update_from_file( $file )

Updates the database from a file in TDList format. Called by update_from_AGA. $file may be a file handle, or if it's a string, it is the name of the file to open. May throw an exception on various file or formatting errors.

$sql = $tdlistdb->sql_columns( [ $joiner ])

Returns SQL suitable for the list of column names, separated by commas (or something else if you set a $joiner). See INSERT and SELECT queries.

$sql = $tdlistdb->sql_column_types( [ $joiner ])

Returns SQL suitable for the list of column names followed by the column type, separated by commas (or something else if you set a $joiner). See CREATE TABLE queries.

$sql = $tdlistdb->sql_update_qs( [ $joiner ])

Returns SQL suitable for the list of question-mark ('?') placeholders for each column, separated by commas (or something else if you set a $joiner). See UPDATE queries.

$sql = $tdlistdb->sql_insert_qs( [ $joiner ])

Returns SQL suitable for the list of "column = ?" placeholders, separated by commas (or something else if you set a $joiner). See INSERT queries.

$id = $tdlistdb->next_tmp_id( [ $used ] )

Returns the next available (unused) TMPnnnn temporary ID. Setting $used to a true value indicates that this ID is being allocated.

$id = $tdlistdb->update_time( [ $seconds ] )

Get or set the time (in seconds) the database was last updated (via update_from_AGA or update_from_file).

@player_fields = $tdlistdb->select_id( 'id_string' )

Returns the array (or ref to array in scalar context) of the player with ID equal to 'id_string', or an empty array of 'id_string' is not found.

$sth = $tdlistdb->sth('handle_name', [ $DBI::sth ] )

Games::Go::AGA::TDListDB maintains a small library of prepared DBI statement handles, available by name. You may add to the list, but take care not to overwrite existing names if you want this module to continue working correctly. The predefined handles are:

$tdlistdb->sth('select_by_name')->execute('last name', 'first name')

Find a player by last name and first name. Note that the ID is the 'PRIMARY KEY' for the database, and that last and first names may not be unique.

$tdlistdb->sth('insert_player')->execute(@new_column_values)

Add a new player to the database. @new_column_values are values for all the columns, both built-in and extra_columns.

If the id column is non-true, this player is assigned an ID from next_tmp_id.

Returns the @new_column_values array (or a reference to it in scalar context), with the new ID if it was set.

$tdlistdb->sth('update_id')->execute(@new_column_values, 'ID')

Update a player already in the database. @new_column_values are values for all the columns, both built-in and extra_columns, and ID is the player's unique ID. Note that a new ID is also in the @new_column_values. These should differ only under exceptional circumstances, such as if a TMP player gets a real AGA ID.

$tdlistdb->sth('select_id')->execute('ID');

Find a player by ID. Note that the ID is the 'PRIMARY KEY' for the database, so this query will return only one record.

$tdlistdb->sth('select_time')->execute();

Get the current database update time (but use update_time() instead).

$tdlistdb->sth('update_time')->execute($new);

Set the current database update time (but use update_time($new) instead).

$tdlistdb->sth('select_next_tmp')->execute;

Get the numeric part of the next TMP ID (but use next_tmp_id() instead).

$tdlistdb->sth('update_next_tmp')->execute(integer);

Set the numeric part of the next TMP ID (but use next_tmp_id('use') instead).

reap

Reaps zombies created when update_from_AGA is called with background true (or any other zombies, for that matter). This is a non-blocking call, so it is safe to call periodically.

SEE ALSO

Games::Go::AGA::Parse

Parsers for AGA format files.

Games::Go::Wgtd

Online go tournament system.

AUTHOR

Reid Augustin <reid@hellosix.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2013 by Reid Augustin.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.