# $RCSfile$; $Date: 2005-10-23 01:02:30 -0400 (Sun, 23 Oct 2005) $; $Revision: 964 $; $Name$
package FeedfetcherDeluxe::XtossUtil;
use XML::LibXML;
use Core::XmlUtil;
use FeedFetcherDeluxe::Entities::Publisher;
use FeedFetcherDeluxe::Entities::Team;
use FeedFetcherDeluxe::Entities::Affiliation;
use FeedFetcherDeluxe::Entities::Person;
my $xtoss_table_prefix = '';
sub new {
my ($class, $dbh, $log) = @_;
my $self = {};
bless $self, $class;
$self->_initialize($dbh, $log);
$self->{'xtoss_affiliation_exists'} = $dbh->prepare(q{
SELECT id
FROM affiliations
WHERE affiliation_key=?
AND affiliation_type=?
AND publisher_id=?
});
$self->{'xtoss_affiliation_insert'} = $dbh->prepare_cached(q{
INSERT INTO affiliations (affiliation_key, affiliation_type, publisher_id)
VALUES (?, ?, ?)
});
$self->{'xtoss_publisher_exists'} = $dbh->prepare(q{
SELECT id
FROM } . $xtoss_table_prefix . q{publishers WHERE publisher_key=?
});
$self->{'xtoss_league_exists'} = $dbh->prepare(q{
SELECT id
FROM affiliations
WHERE affiliation_type='league'
AND affiliation_key=?
AND publisher_id=?
});
$self->{'xtoss_position_select'} = $dbh->prepare(q{
SELECT id
FROM positions
WHERE position_key=?
AND event_id=?
AND league_id=?
});
$self->{'xtoss_event_exists'} = $dbh->prepare(q{
SELECT id
FROM } . $xtoss_table_prefix . q{events
WHERE event_key=?
AND publisher_id=?
});
$self->{'xtoss_season_select'} = $dbh->prepare(q{
SELECT id
FROM seasons
WHERE season_key=?
AND publisher_id=?
AND league_id=?
});
$self->{'xtoss_sub_season_select'} = $dbh->prepare(q{
SELECT id
FROM sub_seasons
WHERE sub_season_key=?
AND season_id=?
});
$self->{'xtoss_teams_select'} = $dbh->prepare(q{
SELECT id
FROM } . $xtoss_table_prefix . q{teams
WHERE team_key=? AND publisher_id=?
});
$self->{'xtoss_affiliations_select'} = $dbh->prepare(q{
SELECT id
FROM } . $xtoss_table_prefix . q{affiliations
WHERE affiliation_key=? AND publisher_id=?
});
$self->{'xtoss_persons_select'} = $dbh->prepare(q{
SELECT id
FROM } . $xtoss_table_prefix . q{persons
WHERE person_key=? AND publisher_id=?
});
$self->{'keys-xtoss'};
$self->{'xtoss-keys'};
$self->{'keys-idpointers'};
return $self;
}
sub _initialize {
my ($self, $dbh, $log) = @_;
$self->{dbh} = $dbh;
$self->{log} = $log;
$self->{team_entity} = FeedFetcherDeluxe::Entities::Team->new($self->{dbh}, $self->{log});
$self->{affiliation_entity} = FeedFetcherDeluxe::Entities::Affiliation->new($self->{dbh}, $self->{log});
$self->{publisher_entity} = FeedFetcherDeluxe::Entities::Publisher->new($self->{dbh}, $self->{log});
$self->_set_keys_idpointers();
$self->_set_xtoss_translation();
# $self->get_xtoss_column_info('event-id', 'event-key-test', '1');
}
# Find a publisher id or return undef if not found
sub _find_publisher {
my ($self, $key, $publisher_key, $dummy) = @_;
my $publisher_id;
# print "!!!!! publisher key: $publisher_key\n";
$self->{'xtoss_publisher_exists'}->execute($publisher_key);
my @arr = $self->{'xtoss_publisher_exists'}->fetchrow_array();
$self->{'xtoss_publisher_exists'}->finish;
if (@arr->[0]) {
$publisher_id = @arr->[0];
}
return $publisher_id;
}
# Find a publisher id or return undef if not found
sub _find_league {
my ($self, $key, $league_key, $publisher_id) = @_;
my $league_id;
$self->{'xtoss_league_exists'}->execute($league_key, $publisher_id);
my @arr = $self->{'xtoss_league_exists'}->fetchrow_array();
$self->{'xtoss_league_exists'}->finish;
if (@arr->[0]) {
$league_id = @arr->[0];
}
return $league_id;
}
# Find an event id or return undef if not found
sub _find_event {
my ($self, $key, $event_key, $publisher_id) = @_;
my $event_id;
$self->{'xtoss_event_exists'}->execute($event_key, $publisher_id);
my @arr = $self->{'xtoss_event_exists'}->fetchrow_array();
$self->{'xtoss_event_exists'}->finish;
if (@arr->[0]) {
$event_id = @arr->[0];
}
return $event_id;
}
# Find a team id or return undef if not found
sub _find_team {
my ($self, $key, $team_key, $publisher_id) = @_;
my $team_id;
# Look for the team by its key
$self->{'xtoss_teams_select'}->execute($team_key, $publisher_id);
my @arr = $self->{'xtoss_teams_select'}->fetchrow_array();
$self->{'xtoss_teams_select'}->finish;
# Use the existing team if available, otherwise, create a new one
if (@arr->[0]) {
$team_id = @arr->[0];
}
return $team_id;
}
# Find a team id or return undef if not found
sub _find_team_key {
my ($self, $key, $team_id, $publisher_id) = @_;
my $team_key = $self->{team_entity}->retrieve_key_by_id($team_id);
return $team_key;
}
# Find an affiliation id or return undef if not found
sub _find_affiliation {
my ($self, $key, $affiliation_key, $publisher_id) = @_;
my $affiliation_id;
# Look for the affiliation by its key
$self->{'xtoss_affiliations_select'}->execute($affiliation_key, $publisher_id);
my @arr = $self->{'xtoss_affiliations_select'}->fetchrow_array();
$self->{'xtoss_affiliations_select'}->finish;
# Use the existing affiliation if available, otherwise, create a new one
if (@arr->[0]) {
$affiliation_id = @arr->[0];
}
return $affiliation_id;
}
# AlanLD
# Find an affiliation id or return undef if not found
sub _find_affiliation_key {
my ($self, $key, $affiliation_id, $publisher_id) = @_;
my $team_key = $self->{affiliation_entity}->retrieve_key_by_id($affiliation_id);
return $affiliation_key;
}
# Find a team id or return undef if not found
sub _find_person_key {
my ($self, $key, $team_id, $publisher_id) = @_;
my $person_key = $self->{person_entity}->retrieve_key_by_id($team_id);
return $person_key;
}
# Find a person id or return undef if not found
sub _find_person {
my ($self, $key, $person_key, $publisher_id, , $xtoss_table, $node) = @_;
my $person_id;
# Track down the actual key if what we got was an idref
if (defined $node) {
my $player_metadata = solonode_optional($node, "//player[\@id='$person_key']/player-metadata");
if ($player_metadata) {
$person_key = required_attribute($player_metadata, 'player-key');
}
}
$self->{'xtoss_persons_select'}->execute($person_key, $publisher_id);
my @arr = $self->{'xtoss_persons_select'}->fetchrow_array();
$self->{'xtoss_persons_select'}->finish;
if (@arr->[0]) {
$person_id = @arr->[0];
}
return $person_id;
}
# Transform 'true' or 'false' to 1/0 respectively
sub _set_truefalse_to_integer {
my ($self, $key, $value, $publisher_id, $xtoss_table, $node) = @_;
my $ret_val = 0;
$value = lc($value);
if ($value eq 'true') {
$ret_val = 1;
}
return $ret_val;
}
sub _find_person_position {
my ($self, $key, $value, $publisher_id, $xtoss_table, $node) = @_;
my $position_id;
return $position_id if ! $value;
my $affiliation_id = 0;
my $sport_node = solonode_optional($node, "//sports-content-code[\@code-type='sport']");
my $sport_code = required_attribute($sport_node, 'code-key');
# $self->{log}->log("[DBG] *** value: $value");
# Find affiliation id
my $select_sql = q{
SELECT id
FROM affiliations
WHERE affiliation_type='sport'
AND affiliation_key=?
AND publisher_id=?
};
my $sth = $self->{dbh}->prepare($select_sql);
$sth->execute($sport_code, $publisher_id);
my $arr = $sth->fetchrow_arrayref();
$sth->finish;
if ($arr->[0]) {
$affiliation_id = $arr->[0];
# $self->{log}->log("[DBG] *** affiliation id: $affiliation_id");
}
$select_sql = q{
SELECT id
FROM positions
WHERE affiliation_id=?
AND abbreviation=?
};
$sth = $self->{dbh}->prepare($select_sql);
$sth->execute($affiliation_id, $value);
my $arr = $sth->fetchrow_arrayref();
$sth->finish;
if ($arr->[0]) {
$position_id =$arr->[0];
# $self->{log}->log("[DBG] *** position id: $position_id");
}
else {
my $insert_sql = q{
INSERT INTO positions (affiliation_id, abbreviation) VALUES (?, ?)
};
$sth = $self->{dbh}->prepare($insert_sql);
$sth->execute($affiliation_id, $value);
$sth->finish;
$sth = $self->{dbh}->prepare($select_sql);
$sth->execute($affiliation_id, $value);
my $arr = $sth->fetchrow_arrayref();
$sth->finish;
$position_id =$arr->[0];
}
# $self->{log}->log("[DBG] ***** position id: $position_id");
return $position_id;
}
# For those times when what one wants is a key translation without value transform
sub _do_nothing {
my ($self, $key, $value, $publisher_id) = @_;
return $value;
}
sub _evaluate_state {
my ($self, $key, $value, $publisher_id) = @_;
$value = lc($value);
my $ret_val = 0;
if ($value eq 'complete') {
$ret_val = 1;
}
return $ret_val;
}
sub _evaluate_stats_coverage_type {
my ($self, $key, $value, $publisher_id) = @_;
# print "type: $value\n";
$value = lc($value);
$value =~ s/-/_/g;
my $ret_val =$value;
if ($value =~ /event/) {
$ret_val ='event';
}
return $ret_val;
}
# XML name to xtoss table name translation map
sub _set_xtoss_translation {
my ($self) = @_;
$self->{'keys-xtoss'} = {
'stats-core' => 'core-stats',
'stats-baseball-offensive' => 'baseball-offensive-stats',
'stats-baseball-defensive' => 'baseball-defensive-stats',
'stats-baseball-pitching' => 'baseball-pitching-stats',
'stats-american-football-offensive' => 'american-football-offensive-stats',
'stats-american-football-defensive' => 'american-football-defensive-stats',
'stats-american-football-passing' => 'american-football-passing-stats',
'stats-american-football-rushing' => 'american-football-rushing-stats',
'stats-american-football-down-progress' => 'american-football-down-progress-stats',
'stats-american-football-sacks-against' => 'american-football-sacks-against-stats',
'stats-american-football-scoring' => 'american-football-scoring-stats',
'stats-american-football-special-teams' => 'american-football-special-teams-stats',
'stats-american-football-fumbles' => 'american-football-fumbles-stats',
'stats-american-football-penalties' => 'american-football-penalties-stats',
'team-stats-american-football' => 'team-american-football-stats',
'event-metadata-baseball' => 'baseball-event-states',
'action-baseball-play' => 'baseball-action-plays',
'action-baseball-substitution' => 'baseball-action-substitutions',
'action-baseball-score' => 'baseball-action-plays',
'event-metadata-american-football' => 'american-football-event-states',
'action-american-football-play-participant' => 'american-football-action-participants',
'action-american-football-play' => 'american-football-action-plays',
'action-american-football-score' => 'american-football-action-plays',
'event-metadata-ice-hockey' => 'ice-hockey-event-states',
'stats-ice-hockey-offensive' => 'ice-hockey-offensive-stats',
'stats-ice-hockey-defensive' => 'ice-hockey-defensive-stats',
'player-stats-ice-hockey' => 'ice-hockey-player-stats',
'event-metadata-basketball' => 'basketball-event-states',
'event-metadata-basketball' => 'basketball-event-states',
'stats-basketball-offensive' => 'basketball-offensive-stats',
'stats-basketball-defensive' => 'basketball-defensive-stats',
'stats-basketball-rebounding' => 'basketball-rebounding-stats',
'team-stats-basketball' => 'basketball-team-stats',
'event-metadata-soccer' => 'soccer-event-states',
'stats-soccer-offensive' => 'soccer-offensive-stats',
'stats-soccer-defensive' => 'soccer-defensive-stats',
'stats-soccer-foul' => 'soccer-foul-stats',
'event-metadata-motor-racing' => 'motor-racing-event-states',
'stats-motor-racing-race' => 'motor-racing-race-stats',
'stats-motor-racing-qualifying' => 'motor-racing-qualifying-stats',
'team-in-possession-idref' => 'team-in-possession-id',
'runner-on-first-idref' => 'person-id',
'runner-on-second-idref' => 'person-id',
'runner-on-third-idref' => 'person-id',
'pitcher-idref' => 'person-id',
'batter-idref' => 'person-id',
#'events'=>'event',
'team'=>'teams',
'person'=>'persons',
'player'=>'players'
};
my %reversed = reverse %{$self->{'keys-xtoss'}};
$self->{'xtoss-keys'} = \% reversed;
# print "value test: ".$self->{'xtoss-keys'}{'baseball-event-states'};
}
sub _set_keys_idpointers {
my ($self) = @_;
# Matches between sportsml names (keys) and xtoss column names and the subs that will find ids
# based on keys for those entities. Some, like 'person-id' are in the list to allow
# subs like get_person_id to work.
$self->{'keys-idpointers'} = {
'publisher-by-key' => {'column'=>'publisher_id', 'sub'=>\&_find_publisher},
'league-by-key' => {'column'=>'league_id', 'sub'=>\&_find_league},
'event-key' => {'column'=>'event_id', 'sub'=>\&_find_event},
'person-key' => {'column'=>'person_id', 'sub'=>\&_find_person},
'season-key' => {'column'=>'season_id', 'sub'=>\&_find_season},
'team-key' => {'column'=>'team_id', 'sub'=>\&_find_team},
'affiliation-key' => {'column'=>'affiliation_id', 'sub'=>\&_find_affiliation},
'team-in-possession-id' => {'column'=>'team_key', 'sub'=>\&_find_team_key},
'person-id' => {'column'=>'person_key', 'sub'=>\&_find_person_key},
# A quirk, should be team-in-possession-id in that sportsml but it has been found not to
'team-idref:american-football-event-states' => {'column'=>'team_in_possession_id', 'sub'=>\&_find_team},
'team-in-possession-idref' => {'column'=>'team_in_possession_id', 'sub'=>\&_find_team},
'player-idref' => {'column'=>'player_id', 'sub'=>\&_find_person},
'batter-idref' => {'column'=>'batter_id', 'sub'=>\&_find_person},
'pitcher-idref' => {'column'=>'pitcher_id', 'sub'=>\&_find_person},
'runner-on-first-idref' => {'column'=>'runner_on_first_id', 'sub'=>\&_find_person},
'runner-on-second-idref' => {'column'=>'runner_on_second_id', 'sub'=>\&_find_person},
'runner-on-third-idref' => {'column'=>'runner_on_third_id', 'sub'=>\&_find_person},
'runner-on-first' => {'column'=>'runner_on_first', 'sub'=>\&_set_truefalse_to_integer},
'runner-on-second' => {'column'=>'runner_on_second', 'sub'=>\&_set_truefalse_to_integer},
'runner-on-third' => {'column'=>'runner_on_third', 'sub'=>\&_set_truefalse_to_integer},
'play-sequence-number' => {'column'=>'sequence_number', 'sub'=>\&_do_nothing},
'current-state' => {'column'=>'current_state', 'sub'=>\&_evaluate_state},
'stats-coverage-type:stats' => {'column'=>'stats_coverage_type', 'sub'=>\&_evaluate_stats_coverage_type},
'play-scorekeepers-notation' => {'column'=>'notation', 'sub'=>\&_do_nothing},
# 'player-type' => {'column'=>'person_type', 'sub'=>\&_do_nothing},
'person-original-idref' => {'column'=>'person_original_id', 'sub'=>\&_find_person},
'person-replacing-idref' => {'column'=>'person_replacing_id', 'sub'=>\&_find_person},
'person-original-position' => {'column'=>'person_original_position_id', 'sub'=>\&_find_person_position},
'person-replacing-position' => {'column'=>'person_replacing_position_id', 'sub'=>\&_find_person_position},
'score-type' => {'column'=>'score_attempt_type', 'sub'=>\&_do_nothing},
};
# The following three items are boolean in the sportsml and integer (person ids I believe) in xtoss
#'runner-on-first' => {'column'=>'runner_on_first_id', 'sub'=>\&_find_person},
#'runner-on-second' => {'column'=>'runner_on_second_id', 'sub'=>\&_find_person},
#'runner-on-third' => {'column'=>'runner_on_third_id', 'sub'=>\&_find_person}
}
sub _set_idpointers_keys {
my ($self) = @_;
# Matches between sportsml names (keys) and xtoss column names and the subs that will find ids
# based on keys for those entities. Some, like 'person-id' are in the list to allow
# subs like get_person_id to work.
$self->{'keys-idpointers'} = {
'team-in-possession-idref' => {'column'=>'team_in_possession_idref', 'sub'=>\&_find_team}
};
# The following three items are boolean in the sportsml and integer (person ids I believe) in xtoss
#'runner-on-first' => {'column'=>'runner_on_first_id', 'sub'=>\&_find_person},
#'runner-on-second' => {'column'=>'runner_on_second_id', 'sub'=>\&_find_person},
#'runner-on-third' => {'column'=>'runner_on_third_id', 'sub'=>\&_find_person}
}
sub _get_key_translation {
my ($self, $key) = @_;
my $ref;
$ref = $self->{'keys-idpointers'}{$key};
my $sub_ref;
my $column;
my $sub_ref;
my %h = %$ref;
# If the a a hash entry for the key was found
if (%h) {
$column = $h{'column'};
$sub_ref = $h{'sub'};
# Return the xtoss column name and a pointer to the sub to
# translate it
# print "column: $column\n";
return ($column, $sub_ref);
}
# Return the original key and an undefined pointer to
# a sub if nothing matched
return ($key, undef);
}
# Gets a key and publisher id (integer) and returns the
# xtoss column name and integer id, if found
sub get_xtoss_column_info {
my ($self, $key, $key_value, $publisher_id, $xtoss_table, $node) = @_;
my $coumn;
my $sub_ref;
$key =~ s/_/-/g;
# If qualified with an xtoss table name, look for a key qualified that way as well
if ($xtoss_table) {
$xtoss_table =~ s/_/-/g;
$key_with_namespace = $key.":".$xtoss_table;
($column, $sub_ref) = $self->_get_key_translation($key_with_namespace);
}
# Fall through to just the key even if a namespace argument was passed as long
# as nothing was found yet
if (! $sub_ref) {
# print "!!!!! no sub ref\n";
# Translate underscores to dashes to ensure match if there should be one
($column, $sub_ref) = $self->_get_key_translation($key);
}
my $ret_val = $key_value;
if ($sub_ref) {
# print "!!!!! sub ref\n";
# Call the sub found and get the value
$ret_val = $self->$sub_ref($key, $key_value, $publisher_id, $xtoss_table, $node);
# print "!!!!! ret_val: $ret_val column: $column\n";
}
# Return the xtoss column name (or the original key if no match was found)
# and an undefined return value if nothing was found
return ($column, $ret_val);
}
sub get_affiliation_id {
my ($self, $key, $type, $publisher_id) = @_;
$self->{'xtoss_affiliation_exists'}->execute($key, $type, $publisher_id);
my @arr = $self->{'xtoss_affiliation_exists'}->fetchrow_array();
$self->{'xtoss_affiliation_exists'}->finish;
if (@arr->[0]) {
$affiliation_id = @arr->[0];
}
else {
$self->{'xtoss_affiliation_insert'}->execute($key, $type, $publisher_id);
$self->{'xtoss_affiliation_exists'}->execute($key, $type, $publisher_id);
my @arr = $self->{'xtoss_affiliation_exists'}->fetchrow_array();
$self->{'xtoss_affiliation_exists'}->finish;
$affiliation_id = @arr->[0];
}
return $affiliation_id;
}
# A convenience sub
# Other id fetching subs require a publisher id argument, but obviously, getting a publisher id
# doesn't. The calling of subs by key always passes an id and publisher id, requiring a dummy
# valueu to be passed when getting the publisher, which is icky, so it's papered over here.
sub get_publisher_id {
my ($self, $publisher_key) = @_;
my ($column, $publisher_id) = $self->get_xtoss_column_info('publisher-by-key', $publisher_key, '');
return $publisher_id;
}
# A convenience sub to find a league id by its key
# Note that the query that is actually executed looks at the affiliations table, not a 'league' table
sub get_league_id {
my ($self, $league_key, $publisher_id) = @_;
my ($column, $league_id) = $self->get_xtoss_column_info('league-by-key', $league_key, $publisher_id);
return $publisher_id;
}
# Find an event id by key
sub get_event_id {
my ($self, $event_key, $publisher_id) = @_;
my ($column, $event_id) = $self->get_xtoss_column_info('event_key', $event_key, $publisher_id);
return $event_id;
}
# Find a person id by key
sub get_person_id {
my ($self, $person_key, $publisher_id) = @_;
my ($column, $person_id) = $self->get_xtoss_column_info('person_key', $person_key, $publisher_id);
return $person_id;
}
sub get_season_id {
my ($self, $season_key, $publisher_id, $league_id) = @_;
my $season_id;
# print "season_id: $season_key publisher_id: $publisher_id league_id: $league_id\n";
$self->{'xtoss_season_select'}->execute($season_key, $publisher_id, $league_id);
my @arr = $self->{'xtoss_season_select'}->fetchrow_array();
$self->{'xtoss_season_select'}->finish;
if (@arr->[0]) {
$season_id = @arr->[0];
}
return $season_id;
}
sub get_sub_season_id {
my ($self, $sub_season_key, $season_id) = @_;
my $sub_season_id;
$self->{'xtoss_sub_season_select'}->execute($sub_season_key, $season_id);
my @arr = $self->{'xtoss_sub_season_select'}->fetchrow_array();
$self->{'xtoss_sub_season_select'}->finish;
if (@arr->[0]) {
$sub_season_id = @arr->[0];
}
return $sub_season_id;
}
# find a team by key
sub get_team_id {
my ($self, $team_key, $publisher_id) = @_;
my ($column, $team_id) = $self->get_xtoss_column_info('team_key', $team_key, $publisher_id);
return $team_id;
}
# Get an xtoss table name from a sportsml tag name or attribute (just a hash lookup)
sub get_xtoss_name {
my ($self, $sportsml_name) = @_;
$sportsml_name =~ s/_/-/g;
my $value = $self->{'keys-xtoss'}{$sportsml_name};
# If the a a hash entry for the key was found
if ($self->{'keys-xtoss'}{$sportsml_name}) {
$value =~ s/-/_/g;
return scalar $value;
}
$sportsml_name =~ s/-/_/g;
return $sportsml_name;
}
# Get a sportsml name from an xtoss table name (just a hash lookup)
sub get_sportsml_name {
my ($self, $table_name) = @_;
$table_name =~ s/_/-/g;
my $value = $self->{'xtoss-keys'}{$table_name};
# print keys(%{$self->{'xtoss-keys'}});
if ($self->{'xtoss-keys'}{$table_name}) {
$value =~ s/_/-/g;
return scalar $value;
}
$table_name =~ s/-/_/g;
# my $ref = $self->{'xtoss-keys'}{$table_name};
# my %value = %$ref;
# If the a a hash entry for the key was found
# if (%value) {
# return $value;
# }
return $table_name;
}
1;