Index: trunk/Changes =================================================================== --- trunk/Changes (revision 9) +++ trunk/Changes (revision 10) @@ -4,5 +4,7 @@ - first CPAN release -0.02 - - doc updates (thanks to Michael Slass for the suggestion) - +0.02 1 Feb 2006 + - doc updates to MP3::Find (thanks to Mike Slass for the suggestion) + - added a test suite for the DB backend + - DB management functions are now in the DB backend + - rewrote (and documented) mp3db to use the new DB backend functions Index: trunk/MANIFEST =================================================================== --- trunk/MANIFEST (revision 9) +++ trunk/MANIFEST (revision 10) @@ -5,4 +5,5 @@ t/01.t t/02-filesystem.t +t/03-db.t t/mp3s/not-an-mp3 lib/MP3/Find.pm Index: trunk/bin/mp3db =================================================================== --- trunk/bin/mp3db (revision 9) +++ trunk/bin/mp3db (revision 10) @@ -3,11 +3,10 @@ use lib '/home/peter/projects/mp3-find/lib'; -use DBI; -use MP3::Find; +use MP3::Find::DB; use File::Spec::Functions qw(catfile); use Getopt::Long; GetOptions( - 'create' => \my $CREATE, + 'create' => \my $CREATE, 'file|f=s' => \my $DB_FILE, ); @@ -15,134 +14,56 @@ $DB_FILE ||= catfile($ENV{HOME}, 'mp3.db'); -#TODO: hints on numeric columns -my @COLUMNS = ( - [ 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 @DIRS = @ARGV; -my @DIRS = @ARGV; -push @DIRS, $ENV{HOME} unless @DIRS; +my $f = MP3::Find::DB->new; +$f->create_db($DB_FILE) if $CREATE; +$f->update_db($DB_FILE, \@DIRS) if @DIRS; +=head1 NAME -my $dbh = DBI->connect("dbi:SQLite:dbname=$DB_FILE",'','', { RaiseError => 1 }); +mp3db - Frontend for creating and updating a database for MP3::Find::DB -create_table($dbh) if $CREATE; -read_mp3s(\@DIRS); +=head1 SYNOPSIS -sub read_mp3s { - my $dirs = shift; + # create the database file + $ mp3db --create --file my_mp3.db + + # add info + $ mp3db --file my_mp3.db ~/mp3 + + # update, and add results from another directory + $ mp3db --file my_mp3.db ~/mp3 ~/cds - 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 = ?' - ); - - for my $mp3 (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 "A $$mp3{FILENAME}\n"; - } elsif ($mp3->{mtime} > $$records[0][0]) { - # the mp3 file is newer than its record - $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); - print "U $$mp3{FILENAME}\n"; - } - } - - # as a workaround for the 'closing dbh with active staement handles warning - # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) - # NOT WORKING!!! - foreach ($mtime_sth, $insert_sth, $update_sth) { - $_->{Active} = 1; - $_->finish; - } -} +=head1 DESCRIPTION -#TODO: hints on numeric vs. string columns, for proper sorting -sub create_table { - my $dbh = shift; - $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); -} + mp3db [options] [directory] [directories...] -=begin +Creates and/or updates a database of ID3 data from the mp3s found +in the given directories. - CREATE TABLE mp3 ( - mtime, - - FILENAME, - - TITLE, - ARTIST, - ALBUM, - YEAR, - COMMENT, - GENRE, - TRACKNUM, - - VERSION, -- MPEG audio version (1, 2, 2.5) - LAYER, -- MPEG layer description (1, 2, 3) - STEREO, -- boolean for audio is in stereo - - VBR, -- boolean for variable bitrate - BITRATE, -- bitrate in kbps (average for VBR files) - FREQUENCY, -- frequency in kHz - SIZE, -- bytes in audio stream - OFFSET, -- bytes offset that stream begins - - SECS, -- total seconds - MM, -- minutes - SS, -- leftover seconds - MS, -- leftover milliseconds - TIME, -- time in MM:SS - - COPYRIGHT, -- boolean for audio is copyrighted - PADDING, -- boolean for MP3 frames are padded - MODE, -- channel mode (0 = stereo, 1 = joint stereo, - -- 2 = dual channel, 3 = single channel) - FRAMES, -- approximate number of frames - FRAME_LENGTH, -- approximate length of a frame - VBR_SCALE -- VBR scale from VBR header - ); +=head2 Options + +=over + +=item C<--create>, C<-c> + +Create the database file named by the C<--file> option. + +=item C<--file>, C<-f> + +The name of the database file to work with. Defaults to F<~/mp3.db>. + +=back + +=head1 AUTHOR + +Peter Eichman + +=head1 COPYRIGHT AND LICENSE + +Copyright (c) 2006 by Peter Eichman. All rights reserved. + +This program is free software; you can redistribute it and/or +modify it under the same terms as Perl itself. =cut Index: trunk/lib/MP3/Find.pm =================================================================== --- trunk/lib/MP3/Find.pm (revision 9) +++ 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 9) +++ 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 9) +++ 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 Index: trunk/t/03-db.t =================================================================== --- trunk/t/03-db.t (revision 10) +++ trunk/t/03-db.t (revision 10) @@ -0,0 +1,49 @@ +#!/usr/bin/perl -w +use strict; + +use Test::More; + +BEGIN { + eval { require DBI }; + plan skip_all => 'DBI required to use MP3::Find::DB backend' if $@; + eval { require DBD::SQLite }; + plan skip_all => 'DBD::SQLite required to use MP3::Find::DB backend' if $@; + eval { require SQL::Abstract }; + plan skip_all => 'SQL::Abstract required to use MP3::Find::DB backend' if $@; + + use_ok('MP3::Find::DB') +}; + +plan tests => 7; + +my $SEARCH_DIR = 't/mp3s'; +my $DB_FILE = 't/mp3.db'; +my $MP3_COUNT = 0; + +# exercise the object + +my $finder = MP3::Find::DB->new; +isa_ok($finder, 'MP3::Find::DB'); + +eval { $finder->create_db() }; +ok($@, 'create_db dies when not given a database name'); +eval { $finder->update_db() }; +ok($@, 'update_db dies when not given a database name'); +eval { $finder->destroy_db() }; +ok($@, 'destroy_db dies when not given a database name'); + + +# create a test db +unlink $DB_FILE; +$finder->create_db($DB_FILE); +ok(-e $DB_FILE, 'db file is there'); + +my $count = $finder->update_db($DB_FILE, $SEARCH_DIR); +is($count, $MP3_COUNT, 'added all the mp3s to the db'); + +# remove the db +$finder->destroy_db($DB_FILE); +ok(!-e $DB_FILE, 'db file is gone'); + +#TODO: get some test mp3s +#TODO: write a set of common set of test querys and counts for all the backends