view release on metacpan or  search on metacpan

lib/MP3/Find/DB.pm  view on Meta::CPAN

=head2 create

    $finder->create({
	dsn => 'dbi:SQLite:dbname=mp3.db',
	dbh => $dbh,
	db_file => 'mp3.db',
    });

Creates a new table for storing mp3 info in the database. You can provide
either a DSN (plus username and password, if needed), an already created
database handle, or just the name of an SQLite database file.

=cut

sub create {
    my $self = shift;
    my $args = shift;

    my $dbh = _get_dbh($args) or croak "Please provide a DBI database handle, DSN, or SQLite database filename";
    
    my $create = 'CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')';
    $dbh->do($create);
}

=head2 create_db (DEPRECATED)

    $finder->create_db($db_filename);

Creates a SQLite database in the file named C<$db_filename>.

=cut

# TODO: extended table for ID3v2 data
sub create_db {
    my $self = shift;
    my $db_file = shift or croak "Need a name for the database I'm about to create";
    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1});
    my $create = 'CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')';
    $dbh->do($create);
}

=head2 update

    my $count = $finder->update({
	dsn   => 'dbi:SQLite:dbname=mp3.db',
	files => \@filenames,
	dirs  => \@dirs,
    });

Compares the files in the C<files> list plus any MP3s found by searching
in C<dirs> to their records in the database pointed to by C<dsn>. If the
files found have been updated since they have been recorded in the database
(or if they are not in the database), they are updated (or added).

Instead of a C<dsn>, you can also provide either an already created
database handle as C<dbh> or the filename of an SQLite database as C<db_file>.

=cut

# this is update_db and update_files (from Matt Dietrich) rolled into one
sub update {
    my $self = shift;
    my $args = shift;

    my $dbh = _get_dbh($args) or croak "Please provide a DBI database handle, DSN, or SQLite database filename";

    my @dirs  = $args->{dirs}
		    ? ref $args->{dirs} eq 'ARRAY'
			? @{ $args->{dirs} }
			: ($args->{dirs})
		    : ();

    my @files  = $args->{files}
		    ? ref $args->{files} eq 'ARRAY' 
			? @{ $args->{files} }
			: ($args->{files})
		    : ();
    
    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK;

    my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?');
    my $insert_sth = $dbh->prepare(
        'INSERT INTO mp3 (' . 
            join(',', map { $$_[0] } @COLUMNS) .
        ') VALUES (' .
            join(',', map { '?' } @COLUMNS) .
        ')'
    );
    my $update_sth = $dbh->prepare(
        'UPDATE mp3 SET ' . 
            join(',', map { "$$_[0] = ?" } @COLUMNS) . 
        ' WHERE FILENAME = ?'
    );
    
    my $count = 0;  # the number of records added or updated
    my @mp3s;       # metadata for mp3s found

    # look for mp3s using the filesystem backend if we have dirs to search in
    if (@dirs) {
	require MP3::Find::Filesystem;
	my $finder = MP3::Find::Filesystem->new;
	unshift @mp3s, $finder->find_mp3s(dir => \@dirs, no_format => 1);
    }

    # get the metadata on specific files
    unshift @mp3s, map { get_mp3_metadata({ filename => $_ }) } @files;

    # check each file against its record in the database
    for my $mp3 (@mp3s) {	
        # see if the file has been modified since it was first put into the db
        $mp3->{mtime} = (stat($mp3->{FILENAME}))[9];
        $mtime_sth->execute($mp3->{FILENAME});
        my $records = $mtime_sth->fetchall_arrayref;
        
        warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1;
        
        if (@$records == 0) {
	    # we are adding a record
            $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS);
            $status_callback->(A => $$mp3{FILENAME});
            $count++;
        } elsif ($mp3->{mtime} > $$records[0][0]) {
            # the mp3 file is newer than its record
            $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME});
            $status_callback->(U => $$mp3{FILENAME});
            $count++;
        }
    }
    
    # SQLite buggy driver
    _sqlite_workaround($mtime_sth, $insert_sth, $update_sth);
     
    return $count;
}

=head2 update_db (DEPRECATED)

    my $count = $finder->update_db($db_filename, \@dirs);

Searches for all mp3 files in the directories named by C<@dirs>
using L<MP3::Find::Filesystem>, and adds or updates the ID3 info
from those files to the database. If a file already has a record
in the database, then it will only be updated if it has been modified
since the last time C<update_db> was run.

=cut

sub update_db {
    my $self = shift;
    my $db_file = shift or croak "Need the name of the database to update";
    my $dirs = shift;
    
    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK;
    
    my @dirs = ref $dirs eq 'ARRAY' ? @$dirs : ($dirs);
    
    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1});
    my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?');
    my $insert_sth = $dbh->prepare(
        'INSERT INTO mp3 (' . 
            join(',', map { $$_[0] } @COLUMNS) .
        ') VALUES (' .
            join(',', map { '?' } @COLUMNS) .
        ')'
    );
    my $update_sth = $dbh->prepare(
        'UPDATE mp3 SET ' . 
            join(',', map { "$$_[0] = ?" } @COLUMNS) . 
        ' WHERE FILENAME = ?'
    );
    
    # the number of records added or updated
    my $count = 0;
    
    # look for mp3s using the filesystem backend
    require MP3::Find::Filesystem;
    my $finder = MP3::Find::Filesystem->new;
    for my $mp3 ($finder->find_mp3s(dir => \@dirs, no_format => 1)) {
        # see if the file has been modified since it was first put into the db
        $mp3->{mtime} = (stat($mp3->{FILENAME}))[9];
        $mtime_sth->execute($mp3->{FILENAME});
        my $records = $mtime_sth->fetchall_arrayref;
        
        warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1;
        
        if (@$records == 0) {
            $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS);
            $status_callback->(A => $$mp3{FILENAME});
            $count++;
        } elsif ($mp3->{mtime} > $$records[0][0]) {
            # the mp3 file is newer than its record
            $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME});
            $status_callback->(U => $$mp3{FILENAME});
            $count++;
        }
    }
    
    # SQLite buggy driver
    _sqlite_workaround($mtime_sth, $insert_sth, $update_sth);
    
    return $count;
}

=head2 sync

    my $count = $finder->sync({ dsn => $DSN });

Removes entries from the database that refer to files that no longer