Changeset 29 in mp3-find for trunk/lib/MP3/Find/DB.pm

Timestamp:

05/21/06 05:17:23 (18 years ago)

Author:

peter

Message:

• factored out 'get_mp3_metadata' function from Filesystem.pm to Util.pm
• added 'update' function to DB.pm that combines 'update_db' and 'update_file' (for updating just specific files); idea courtesy of Matt Dietrich
• modified mp3db to let you mix and match files and directories on the command line; now also uses the 'update' function in DB.pm

File:

• trunk/lib/MP3/Find/DB.pm (modified) (8 diffs)

trunk/lib/MP3/Find/DB.pm

@@ -9 +9 @@
 use DBI;
 use SQL::Abstract;
+
+use MP3::Find::Util qw(get_mp3_metadata);
 
 my $sql = SQL::Abstract->new;
@@ -48 +50 @@
 };
 
+# TODO: use DSNs instead of SQLite db names
 sub search {
     my $self = shift;
@@ -84 +87 @@
 }
 
+# TODO: convert to using DSNs instead of hardcoded SQLite connections
+# TODO: extended table for ID3v2 data
 sub create_db {
     my $self = shift;
@@ -90 +95 @@
     $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')');
 }
+
+# this is update_db and update_files (from Matt Dietrich) rolled into one
+=head2 update
+
+    my $count = $finder->update({
+        dsn   => 'dbi:SQLite:dbname=mp3.db',
+        files => \@filenames,
+        dirs  => [qw(music downloads/mp3)],
+    });
+
+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).
+
+=cut
+
+sub update {
+    my $self = shift;
+    my $args = shift;
+
+    my $dsn   = $args->{dsn} or croak "Need a DSN to connect to";
+    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 $dbh = DBI->connect($dsn, '', '', {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 = ?'
+    );
+    
+    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 specific code:
+    # 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) {
+        $_->{RaiseError} = 0;  # don't die on error
+        $_->{PrintError} = 0;  # ...and don't even say anything
+        $_->{Active} = 1;
+        $_->finish;
+    }
+    
+    return $count;
+}
+
+
 
 sub update_db {
@@ -129 +234 @@
         warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1;
         
-        #TODO: maybe print status updates somewhere else?
         if (@$records == 0) {
             $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS);
@@ -154 +258 @@
 }
 
+# TODO: use DSNs instead of SQLite db names
 sub sync_db {
     my $self = shift;
@@ -179 +284 @@
 }
 
+# TODO: use DSNs instead of SQLite db names (this might get funky)
 sub destroy_db {
     my $self = shift;
@@ -342 +448 @@
 Peter Eichman <peichman@cpan.org>
 
+=head1 THANKS
+
+Thanks to Matt Dietrich for suggesting having an option to just 
+update specific files instead of doing a (longer) full search.
+
 =head1 COPYRIGHT AND LICENSE