view release on metacpan or  search on metacpan

lib/TV/Mediathek.pm  view on Meta::CPAN

    }
    $self->log->debug( "Media XML file is " . Format::Human::Bytes::base10( $self->file_util->size( $self->cache_files->{media} ) ) );

    $self->log->debug( "Deleting media tables in db" );
    $self->dbh->do( 'DELETE FROM channels' );
    $self->dbh->do( 'DELETE FROM themes' );
    $self->dbh->do( 'DELETE FROM map_media' );
    $self->dbh->do( 'DELETE FROM media' );

    my $t = XML::Twig->new( twig_handlers => { Filme => \&_media_to_db, }, );

    # Prepare the statement handlers
    my $sths = {};
    my $sql =
        'INSERT OR IGNORE INTO media '
      . '( nr, filename, title, date, url, url_auth, url_hd, url_org, url_rtmp, url_theme ) '
      . 'VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )';
    $sths->{ins_media} = $self->dbh->prepare( $sql );

    $sql = 'INSERT OR IGNORE INTO channels ( channel ) VALUES( ? )';
    $sths->{ins_channel} = $self->dbh->prepare( $sql );

    $sql = 'INSERT OR IGNORE INTO themes ( channel_id, theme ) VALUES( ?, ? )';
    $sths->{ins_theme} = $self->dbh->prepare( $sql );

    $sql = 'INSERT OR IGNORE INTO map_media ( media_id, theme_id ) VALUES( ?, ? )';
    $sths->{ins_map_media} = $self->dbh->prepare( $sql );

    $sql = 'SELECT id AS channel_id FROM channels WHERE channel=?';
    $sths->{sel_channel_id} = $self->dbh->prepare( $sql );

    $sql = 'SELECT id AS theme_id FROM themes WHERE channel_id=? AND theme=?';
    $sths->{sel_theme_id} = $self->dbh->prepare( $sql );

    $sql = 'SELECT id AS media_id FROM media WHERE url=?';
    $sths->{sel_media_id} = $self->dbh->prepare( $sql );

    $t->{mediathek_sths}          = $sths;
    $t->{mediathek_logger}        = $self->log;
    $t->{mediathek_count_inserts} = 0;

    $self->log->debug( sprintf "Parsing media XML: %s", $self->cache_files->{media} );
    $t->parsefile( $self->cache_files->{media} );
    $self->log->debug( "Finished parsing media XML" );
    $t->purge;

    # Clean up all of the handlers
    foreach ( keys( %$sths ) ) {
        $sths->{$_}->finish;
    }

    $t->{mediathek_sths}          = undef;
    $t->{mediathek_logger}        = undef;
    $t->{mediathek_count_inserts} = undef;

    $self->log->debug( __PACKAGE__ . "->refresh_media end" );
}

# Local XML::Twig twig handler method for importing media to the database.
# Expects to receive a twig with the required statement handlers initialised.
# <Filme><Nr>0000</Nr><Sender>3Sat</Sender><Thema>3sat.full</Thema><Titel>Mediathek-Beiträge</Titel><Datum>04.09.2011</Datum><Zeit>19:23:11</Zeit><Url>http://wstreaming.zdf.de/3sat/veryhigh/110103_jazzbaltica2010ceu_musik.asx</Url><UrlOrg>http://wst...
sub _media_to_db {
    my ( $t, $section ) = @_;

    my %values;
    ###FIXME - get all children, not just by name
    foreach my $key ( qw/Datei Nr Sender Thema Titel Datum Url UrlOrg UrlAuth UrlHD UrlRTMP UrlThema/ ) {
        my $element = $section->first_child( $key );
        if ( $element ) {
            $values{$key} = $element->text();
        }
    }

    foreach ( qw/Url Sender Thema Titel/ ) {
        if ( !$values{$_} ) {
            warn( "$_ not defined for entry $values{Nr}.  Skipping.\n" );
            return undef;
        }
    }

    my ( $row, $sql );
    my $sths = $t->{mediathek_sths};
    $sths->{ins_channel}->execute( $values{Sender} );

    $sths->{sel_channel_id}->execute( $values{Sender} );
    $row = $sths->{sel_channel_id}->fetchrow_hashref();
    if ( !$row ) {
        die( "Could not find channel_id for $values{Sender} at entry number $values{Nr}" );
    }
    my $channel_id = $row->{channel_id};

    $sths->{ins_theme}->execute( $channel_id, $values{Thema} );
    $sths->{sel_theme_id}->execute( $channel_id, $values{Thema} );
    $row = $sths->{sel_theme_id}->fetchrow_hashref();
    if ( !$row ) {
        die(    "Could not find themeid for Theme \"$values{Thema}\" and "
              . "Channel \"$values{Sender}\" (channel_id $channel_id) at entry number $values{Nr}" );
    }
    my $theme_id = $row->{theme_id};

    local $Class::Date::DATE_FORMAT = "%Y-%m-%d";
    my $date;
    if ( $values{Datum} ) {
        my ( $day, $month, $year ) = split( /\./, $values{Datum} );
        $date = Class::Date->new( [ $year, $month, $day ] );
    } else {

        #using current time as default
        $date = date( time );
    }

    # Add the media data
    #( filename, title, datum, url, url_auth, url_hd, url_org, url_rtmp, url_theme )
    $sths->{ins_media}->execute(
        $values{Nr},      $values{Datei}, $values{Titel},  $date,            $values{Url},
        $values{UrlAuth}, $values{UrlHD}, $values{UrlOrg}, $values{UrlRTMP}, $values{UrlThema}
    );
    $sths->{sel_media_id}->execute( $values{Url} );
    $row = $sths->{sel_media_id}->fetchrow_hashref();
    if ( !$row ) {
        die( "Could not find media with url $values{Url}" );