MP3::Find::DB - SQLite database backend to MP3::Find

    use MP3::Find::DB;
    my $finder = MP3::Find::DB->new;

    my @mp3s = $finder->find_mp3s(
        dir => '/home/peter/music',
        query => {
            artist => 'ilyaimy',
            album  => 'myxomatosis',
        },
        ignore_case => 1,
        db_file => 'mp3.db',
    );

    # you can do things besides just searching the database

    # create another database
    $finder->create({ db_file => 'my_mp3s.db' });

    # update the database by searching the filesystem
    $finder->update({
	db_file => 'my_mp3s.db',
	dirs => ['/home/peter/mp3', '/home/peter/cds'],
    });

    # or just update specific mp3s
    $finder->update({
	db_file => 'my_mp3s.db',
	files => \@filenames,
    });

    # and then blow it away
    $finder->destroy_db('my_mp3s.db');

    CREATE TABLE mp3 (
        mtime         INTEGER,
        FILENAME      TEXT, 
        TITLE         TEXT, 
        ARTIST        TEXT, 
        ALBUM         TEXT,
        YEAR          INTEGER, 
        COMMENT       TEXT, 
        GENRE         TEXT, 
        TRACKNUM      INTEGER, 
        VERSION       NUMERIC,
        LAYER         INTEGER, 
        STEREO        TEXT,
        VBR           TEXT,
        BITRATE       INTEGER, 
        FREQUENCY     INTEGER, 
        SIZE          INTEGER, 
        OFFSET        INTEGER, 
        SECS          INTEGER, 
        MM            INTEGER,
        SS            INTEGER,
        MS            INTEGER, 
        TIME          TEXT,
        COPYRIGHT     TEXT, 
        PADDING       INTEGER, 
        MODE          INTEGER,
        FRAMES        INTEGER, 
        FRAME_LENGTH  INTEGER, 
        VBR_SCALE     INTEGER
    );

    my $finder = MP3::Find::DB->new(
        status_callback => \&callback,
    );

    sub default_callback {
        my ($status_code, $filename) = @_;
        print STDERR "$status_code $filename\n";
    }

    status_callback => sub {}

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

    $finder->create_db($db_filename);

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

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

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

    my $count = $finder->sync_db($db_filename);

    $finder->destroy({ db_file => $filename });

    $finder->destroy_db($db_filename);

package MP3::Find::DB;

use strict;
use warnings;

use base qw(MP3::Find::Base);
use Carp;

use DBI;
use SQL::Abstract;

use MP3::Find::Util qw(get_mp3_metadata);

my $sql = SQL::Abstract->new;

my @COLUMNS = (
    [ mtime        => 'INTEGER' ],  # filesystem mtime, so we can do incremental updates
    [ FILENAME     => 'TEXT' ], 
    [ TITLE        => 'TEXT' ], 
    [ ARTIST       => 'TEXT' ], 
    [ ALBUM        => 'TEXT' ],
    [ YEAR         => 'INTEGER' ], 
    [ COMMENT      => 'TEXT' ], 
    [ GENRE        => 'TEXT' ], 
    [ TRACKNUM     => 'INTEGER' ], 
    [ VERSION      => 'NUMERIC' ],
    [ LAYER        => 'INTEGER' ], 
    [ STEREO       => 'TEXT' ],
    [ VBR          => 'TEXT' ],
    [ BITRATE      => 'INTEGER' ], 
    [ FREQUENCY    => 'INTEGER' ], 
    [ SIZE         => 'INTEGER' ], 
    [ OFFSET       => 'INTEGER' ], 
    [ SECS         => 'INTEGER' ], 
    [ MM           => 'INTEGER' ],
    [ SS           => 'INTEGER' ],
    [ MS           => 'INTEGER' ], 
    [ TIME         => 'TEXT' ],
    [ COPYRIGHT    => 'TEXT' ], 
    [ PADDING      => 'INTEGER' ], 
    [ MODE         => 'INTEGER' ],
    [ FRAMES       => 'INTEGER' ], 
    [ FRAME_LENGTH => 'INTEGER' ], 
    [ VBR_SCALE    => 'INTEGER' ],
);

my $DEFAULT_STATUS_CALLBACK = sub {
    my ($action_code, $filename) = @_;
    print STDERR "$action_code $filename\n";
};

# get a database handle from named arguments
sub _get_dbh {
    my $args = shift;

    # we got an explicit $dbh object
    return $args->{dbh} if defined $args->{dbh};

    # or a custom DSN
    if (defined $args->{dsn}) {
    	my $dbh = DBI->connect(
	    $args->{dsn}, 
	    $args->{username}, 
	    $args->{password}, 
	    { RaiseError => 1 },
	);
	return $dbh;
    }
    
    # default to a SQLite database
    if (defined $args->{db_file}) {
	my $dbh = DBI->connect(
	    "dbi:SQLite:dbname=$$args{db_file}",
	    '',
	    '',
	    { RaiseError => 1 },
	);
	return $dbh;
    }

    return;
}

sub _sqlite_workaround {
    # as a workaround for the 'closing dbh with active staement handles warning
    # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724)
    foreach (@_) {
        $_->{RaiseError} = 0;  # don't die on error
        $_->{PrintError} = 0;  # ...and don't even say anything
        $_->{Active} = 1;
        $_->finish;
    }
}
 

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);
}

# 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);
}

# 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;
}

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;
}

sub sync {
    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 $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK;

    my $select_sth = $dbh->prepare('SELECT FILENAME FROM mp3');
    my $delete_sth = $dbh->prepare('DELETE FROM mp3 WHERE FILENAME = ?');
    
    # the number of records removed
    my $count = 0;
    
    $select_sth->execute;
    while (my ($filename) = $select_sth->fetchrow_array) {
        unless (-e $filename) {
            $delete_sth->execute($filename);
            $status_callback->(D => $filename);
            $count++;
        }
    }
    
    # SQLite buggy driver
    _sqlite_workaround($select_sth, $delete_sth);
    
    return $count;    
}

sub sync_db {
    my $self = shift;
    my $db_file = shift or croak "Need the name of the databse to sync";

    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK;

    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1});
    my $select_sth = $dbh->prepare('SELECT FILENAME FROM mp3');
    my $delete_sth = $dbh->prepare('DELETE FROM mp3 WHERE FILENAME = ?');
    
    # the number of records removed
    my $count = 0;
    
    $select_sth->execute;
    while (my ($filename) = $select_sth->fetchrow_array) {
        unless (-e $filename) {
            $delete_sth->execute($filename);
            $status_callback->(D => $filename);
            $count++;
        }
    }
    
    return $count;    
}

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

    # XXX: this method only knows how to deal with SQLite files;
    # is there a way to DROP a database given a $dbh?

    my $db_file = $args->{db_file} or croak "Need a db_file argument";

    # actually delete the thing
    unlink $db_file;
}

sub destroy_db {
    my $self = shift;
    my $db_file = shift or croak "Need the name of a database to destroy";
    unlink $db_file;
}


sub search {
    my $self = shift;
    my ($query, $dirs, $sort, $options) = @_;
    
    croak 'Need a database name to search (set "db_file" in the call to find_mp3s)' unless $$options{db_file};
    
    my $dbh = _get_dbh($options);
    
    # use the 'LIKE' operator to ignore case
    my $op = $$options{ignore_case} ? 'LIKE' : '=';
    
    # add the SQL '%' wildcard to match substrings
    unless ($$options{exact_match}) {
        for my $value (values %$query) {
            $value = [ map { "%$_%" } @$value ];
        }
    }

    my ($where, @bind) = $sql->where(
        { map { $_ => { $op => $query->{$_} } } keys %$query },
        ( @$sort ? [ map { uc } @$sort ] : () ),
    );
    
    my $select = "SELECT * FROM mp3 $where";
    
    my $sth = $dbh->prepare($select);
    $sth->execute(@bind);
    
    my @results;
    while (my $row = $sth->fetchrow_hashref) {
        push @results, $row;
    }
    
    return @results;
}

# module return
1;