Index: trunk/lib/MP3/Find.pm =================================================================== --- trunk/lib/MP3/Find.pm (revision 7) +++ trunk/lib/MP3/Find.pm (revision 10) @@ -9,5 +9,5 @@ use Carp; -$VERSION = '0.01'; +$VERSION = '0.02'; @EXPORT = qw(find_mp3s); @@ -69,4 +69,9 @@ is currently not as stable as the Filesystem backend. +B: This whole project is still in the alpha stage, so +I can make no guarentees that there won't be significant interface changes +in the next few versions or so. Also, comments about what about the API +rocks (or sucks!) are appreciated. + =head1 REQUIRES Index: trunk/lib/MP3/Find/Base.pm =================================================================== --- trunk/lib/MP3/Find/Base.pm (revision 7) +++ trunk/lib/MP3/Find/Base.pm (revision 10) @@ -93,5 +93,5 @@ =head1 NAME -MP3::Find::Base - Base class for MP3::Find finders +MP3::Find::Base - Base class for MP3::Find backends =head1 SYNOPSIS Index: trunk/lib/MP3/Find/DB.pm =================================================================== --- trunk/lib/MP3/Find/DB.pm (revision 7) +++ trunk/lib/MP3/Find/DB.pm (revision 10) @@ -5,4 +5,5 @@ use base qw(MP3::Find::Base); +use Carp; use DBI; @@ -11,9 +12,43 @@ my $sql = SQL::Abstract->new; +my @COLUMNS = ( + [ mtime => 'INTEGER' ], # the 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' ], +); + + sub search { my $self = shift; my ($query, $dirs, $sort, $options) = @_; - my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', ''); + croak 'Need a database name to search (set "db_file" in the call to find_mp3s)' unless $$options{db_file}; + + my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', '', {RaiseError => 1}); # use the 'LIKE' operator to ignore case @@ -43,4 +78,75 @@ return @results; +} + +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}); + $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); +} + +sub update_db { + my $self = shift; + my $db_file = shift or croak "Need the name of the databse to update"; + my $dirs = shift; + + 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); + print STDERR "A $$mp3{FILENAME}\n"; + $count++; + } elsif ($mp3->{mtime} > $$records[0][0]) { + # the mp3 file is newer than its record + $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); + print STDERR "U $$mp3{FILENAME}\n"; + $count++; + } + } + + # 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 ($mtime_sth, $insert_sth, $update_sth) { + $_->{Active} = 1; + $_->finish; + } + + return $count; +} + +sub destroy_db { + my $self = shift; + my $db_file = shift or croak "Need the name of a database to destory"; + unlink $db_file; } @@ -64,5 +170,17 @@ }, ignore_case => 1, - ); + db_file => 'mp3.db', + ); + + # you can do things besides just searching the database + + # create another database + $finder->create_db('my_mp3s.db'); + + # update the database from the filesystem + $finder->update_db('my_mp3s.db', ['/home/peter/mp3', '/home/peter/cds']); + + # and then blow it away + $finder->destroy_db('my_mp3s.db'); =head1 REQUIRES @@ -121,14 +239,38 @@ =back +=head1 METHODS + +=head2 create_db + + $finder->create_db($db_filename); + +Creates a SQLite database in the file named c<$db_filename>. + +=head2 update_db + + my $count = $finder->update_db($db_filename, \@dirs); + +Searches for all mp3 files in the directories named by C<@dirs> +using L, 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 +sinc ethe last time C was run. + +=head2 destroy_db + + $finder->destroy_db($db_filename); + +Permanantly removes the database. + =head1 TODO -Move the database/table creation code from F into this -module. - Database maintanence routines (e.g. clear out old entries) +Allow the passing of a DSN or an already created C<$dbh> instead +of a SQLite database filename. + =head1 SEE ALSO -L, L +L, L, L =head1 AUTHOR