Changeset 30 in mp3-find for trunk


Ignore:
Timestamp:
05/21/06 17:32:39 (18 years ago)
Author:
peter
Message:
  • upped version number in Find.pm
  • rearranged docs in DB.pm
Location:
trunk/lib/MP3
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/lib/MP3/Find.pm

    r25 r30  
    99use Carp; 
    1010 
    11 $VERSION = '0.05'; 
     11$VERSION = '0.06'; 
    1212 
    1313@EXPORT = qw(find_mp3s); 

  • trunk/lib/MP3/Find/DB.pm

    r29 r30  
    5050}; 
    5151 
    52 # TODO: use DSNs instead of SQLite db names 
    53 sub search { 
    54     my $self = shift; 
    55     my ($query, $dirs, $sort, $options) = @_; 
    56      
    57     croak 'Need a database name to search (set "db_file" in the call to find_mp3s)' unless $$options{db_file}; 
    58      
    59     my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', '', {RaiseError => 1}); 
    60      
    61     # use the 'LIKE' operator to ignore case 
    62     my $op = $$options{ignore_case} ? 'LIKE' : '='; 
    63      
    64     # add the SQL '%' wildcard to match substrings 
    65     unless ($$options{exact_match}) { 
    66         for my $value (values %$query) { 
    67             $value = [ map { "%$_%" } @$value ]; 
    68         } 
    69     } 
    70  
    71     my ($where, @bind) = $sql->where( 
    72         { map { $_ => { $op => $query->{$_} } } keys %$query }, 
    73         ( @$sort ? [ map { uc } @$sort ] : () ), 
    74     ); 
    75      
    76     my $select = "SELECT * FROM mp3 $where"; 
    77      
    78     my $sth = $dbh->prepare($select); 
    79     $sth->execute(@bind); 
    80      
    81     my @results; 
    82     while (my $row = $sth->fetchrow_hashref) { 
    83         push @results, $row; 
    84     } 
    85      
    86     return @results; 
    87 } 
    88  
    89 # TODO: convert to using DSNs instead of hardcoded SQLite connections 
    90 # TODO: extended table for ID3v2 data 
    91 sub create_db { 
    92     my $self = shift; 
    93     my $db_file = shift or croak "Need a name for the database I'm about to create"; 
    94     my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
    95     $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); 
    96 } 
    97  
    98 # this is update_db and update_files (from Matt Dietrich) rolled into one 
    99 =head2 update 
    100  
    101     my $count = $finder->update({ 
    102         dsn   => 'dbi:SQLite:dbname=mp3.db', 
    103         files => \@filenames, 
    104         dirs  => [qw(music downloads/mp3)], 
    105     }); 
    106  
    107 Compares the files in the C<files> list plus any MP3s found by searching 
    108 in C<dirs> to their records in the database pointed to by C<dsn>. If the 
    109 files found have been updated since they have been recorded in the database 
    110 (or if they are not in the database), they are updated (or added). 
    111  
    112 =cut 
    113  
    114 sub update { 
    115     my $self = shift; 
    116     my $args = shift; 
    117  
    118     my $dsn   = $args->{dsn} or croak "Need a DSN to connect to"; 
    119     my @dirs  = $args->{dirs} 
    120                     ? ref $args->{dirs} eq 'ARRAY' 
    121                         ? @{ $args->{dirs} } 
    122                         : ($args->{dirs}) 
    123                     : (); 
    124  
    125     my @files  = $args->{files} 
    126                     ? ref $args->{files} eq 'ARRAY'  
    127                         ? @{ $args->{files} } 
    128                         : ($args->{files}) 
    129                     : (); 
    130      
    131     my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
    132  
    133     my $dbh = DBI->connect($dsn, '', '', {RaiseError => 1}); 
    134     my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
    135     my $insert_sth = $dbh->prepare( 
    136         'INSERT INTO mp3 (' .  
    137             join(',', map { $$_[0] } @COLUMNS) . 
    138         ') VALUES (' . 
    139             join(',', map { '?' } @COLUMNS) . 
    140         ')' 
    141     ); 
    142     my $update_sth = $dbh->prepare( 
    143         'UPDATE mp3 SET ' .  
    144             join(',', map { "$$_[0] = ?" } @COLUMNS) .  
    145         ' WHERE FILENAME = ?' 
    146     ); 
    147      
    148     my $count = 0;  # the number of records added or updated 
    149     my @mp3s;       # metadata for mp3s found 
    150  
    151     # look for mp3s using the filesystem backend if we have dirs to search in 
    152     if (@dirs) { 
    153         require MP3::Find::Filesystem; 
    154         my $finder = MP3::Find::Filesystem->new; 
    155         unshift @mp3s, $finder->find_mp3s(dir => \@dirs, no_format => 1); 
    156     } 
    157  
    158     # get the metadata on specific files 
    159     unshift @mp3s, map { get_mp3_metadata({ filename => $_ }) } @files; 
    160  
    161     # check each file against its record in the database 
    162     for my $mp3 (@mp3s) {        
    163         # see if the file has been modified since it was first put into the db 
    164         $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
    165         $mtime_sth->execute($mp3->{FILENAME}); 
    166         my $records = $mtime_sth->fetchall_arrayref; 
    167          
    168         warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
    169          
    170         if (@$records == 0) { 
    171             # we are adding a record 
    172             $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
    173             $status_callback->(A => $$mp3{FILENAME}); 
    174             $count++; 
    175         } elsif ($mp3->{mtime} > $$records[0][0]) { 
    176             # the mp3 file is newer than its record 
    177             $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
    178             $status_callback->(U => $$mp3{FILENAME}); 
    179             $count++; 
    180         } 
    181     } 
    182      
    183     # SQLite specific code: 
    184     # as a workaround for the 'closing dbh with active staement handles warning 
    185     # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
    186     foreach ($mtime_sth, $insert_sth, $update_sth) { 
    187         $_->{RaiseError} = 0;  # don't die on error 
    188         $_->{PrintError} = 0;  # ...and don't even say anything 
    189         $_->{Active} = 1; 
    190         $_->finish; 
    191     } 
    192      
    193     return $count; 
    194 } 
    195  
    196  
    197  
    198 sub update_db { 
    199     my $self = shift; 
    200     my $db_file = shift or croak "Need the name of the database to update"; 
    201     my $dirs = shift; 
    202      
    203     my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
    204      
    205     my @dirs = ref $dirs eq 'ARRAY' ? @$dirs : ($dirs); 
    206      
    207     my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
    208     my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
    209     my $insert_sth = $dbh->prepare( 
    210         'INSERT INTO mp3 (' .  
    211             join(',', map { $$_[0] } @COLUMNS) . 
    212         ') VALUES (' . 
    213             join(',', map { '?' } @COLUMNS) . 
    214         ')' 
    215     ); 
    216     my $update_sth = $dbh->prepare( 
    217         'UPDATE mp3 SET ' .  
    218             join(',', map { "$$_[0] = ?" } @COLUMNS) .  
    219         ' WHERE FILENAME = ?' 
    220     ); 
    221      
    222     # the number of records added or updated 
    223     my $count = 0; 
    224      
    225     # look for mp3s using the filesystem backend 
    226     require MP3::Find::Filesystem; 
    227     my $finder = MP3::Find::Filesystem->new; 
    228     for my $mp3 ($finder->find_mp3s(dir => \@dirs, no_format => 1)) { 
    229         # see if the file has been modified since it was first put into the db 
    230         $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
    231         $mtime_sth->execute($mp3->{FILENAME}); 
    232         my $records = $mtime_sth->fetchall_arrayref; 
    233          
    234         warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
    235          
    236         if (@$records == 0) { 
    237             $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
    238             $status_callback->(A => $$mp3{FILENAME}); 
    239             $count++; 
    240         } elsif ($mp3->{mtime} > $$records[0][0]) { 
    241             # the mp3 file is newer than its record 
    242             $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
    243             $status_callback->(U => $$mp3{FILENAME}); 
    244             $count++; 
    245         } 
    246     } 
    247      
    248     # as a workaround for the 'closing dbh with active staement handles warning 
    249     # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
    250     foreach ($mtime_sth, $insert_sth, $update_sth) { 
    251         $_->{RaiseError} = 0;  # don't die on error 
    252         $_->{PrintError} = 0;  # ...and don't even say anything 
    253         $_->{Active} = 1; 
    254         $_->finish; 
    255     } 
    256      
    257     return $count; 
    258 } 
    259  
    260 # TODO: use DSNs instead of SQLite db names 
    261 sub sync_db { 
    262     my $self = shift; 
    263     my $db_file = shift or croak "Need the name of the databse to sync"; 
    264  
    265     my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
    266  
    267     my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
    268     my $select_sth = $dbh->prepare('SELECT FILENAME FROM mp3'); 
    269     my $delete_sth = $dbh->prepare('DELETE FROM mp3 WHERE FILENAME = ?'); 
    270      
    271     # the number of records removed 
    272     my $count = 0; 
    273      
    274     $select_sth->execute; 
    275     while (my ($filename) = $select_sth->fetchrow_array) { 
    276         unless (-e $filename) { 
    277             $delete_sth->execute($filename); 
    278             $status_callback->(D => $filename); 
    279             $count++; 
    280         } 
    281     } 
    282      
    283     return $count;     
    284 } 
    285  
    286 # TODO: use DSNs instead of SQLite db names (this might get funky) 
    287 sub destroy_db { 
    288     my $self = shift; 
    289     my $db_file = shift or croak "Need the name of a database to destory"; 
    290     unlink $db_file; 
    291 } 
    292  
    293 # module return 
    294 1; 
    295  
    29652=head1 NAME 
    29753 
    … …  
    408164Creates a SQLite database in the file named c<$db_filename>. 
    409165 
     166=cut 
     167 
     168# TODO: convert to using DSNs instead of hardcoded SQLite connections 
     169# TODO: extended table for ID3v2 data 
     170sub create_db { 
     171    my $self = shift; 
     172    my $db_file = shift or croak "Need a name for the database I'm about to create"; 
     173    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
     174    $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); 
     175} 
     176 
     177=head2 update 
     178 
     179    my $count = $finder->update({ 
     180        dsn   => 'dbi:SQLite:dbname=mp3.db', 
     181        files => \@filenames, 
     182        dirs  => \@dirs, 
     183    }); 
     184 
     185Compares the files in the C<files> list plus any MP3s found by searching 
     186in C<dirs> to their records in the database pointed to by C<dsn>. If the 
     187files found have been updated since they have been recorded in the database 
     188(or if they are not in the database), they are updated (or added). 
     189 
     190=cut 
     191 
     192# this is update_db and update_files (from Matt Dietrich) rolled into one 
     193sub update { 
     194    my $self = shift; 
     195    my $args = shift; 
     196 
     197    my $dsn   = $args->{dsn} or croak "Need a DSN to connect to"; 
     198    my @dirs  = $args->{dirs} 
     199                    ? ref $args->{dirs} eq 'ARRAY' 
     200                        ? @{ $args->{dirs} } 
     201                        : ($args->{dirs}) 
     202                    : (); 
     203 
     204    my @files  = $args->{files} 
     205                    ? ref $args->{files} eq 'ARRAY'  
     206                        ? @{ $args->{files} } 
     207                        : ($args->{files}) 
     208                    : (); 
     209     
     210    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
     211 
     212    my $dbh = DBI->connect($dsn, '', '', {RaiseError => 1}); 
     213    my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
     214    my $insert_sth = $dbh->prepare( 
     215        'INSERT INTO mp3 (' .  
     216            join(',', map { $$_[0] } @COLUMNS) . 
     217        ') VALUES (' . 
     218            join(',', map { '?' } @COLUMNS) . 
     219        ')' 
     220    ); 
     221    my $update_sth = $dbh->prepare( 
     222        'UPDATE mp3 SET ' .  
     223            join(',', map { "$$_[0] = ?" } @COLUMNS) .  
     224        ' WHERE FILENAME = ?' 
     225    ); 
     226     
     227    my $count = 0;  # the number of records added or updated 
     228    my @mp3s;       # metadata for mp3s found 
     229 
     230    # look for mp3s using the filesystem backend if we have dirs to search in 
     231    if (@dirs) { 
     232        require MP3::Find::Filesystem; 
     233        my $finder = MP3::Find::Filesystem->new; 
     234        unshift @mp3s, $finder->find_mp3s(dir => \@dirs, no_format => 1); 
     235    } 
     236 
     237    # get the metadata on specific files 
     238    unshift @mp3s, map { get_mp3_metadata({ filename => $_ }) } @files; 
     239 
     240    # check each file against its record in the database 
     241    for my $mp3 (@mp3s) {        
     242        # see if the file has been modified since it was first put into the db 
     243        $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
     244        $mtime_sth->execute($mp3->{FILENAME}); 
     245        my $records = $mtime_sth->fetchall_arrayref; 
     246         
     247        warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
     248         
     249        if (@$records == 0) { 
     250            # we are adding a record 
     251            $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
     252            $status_callback->(A => $$mp3{FILENAME}); 
     253            $count++; 
     254        } elsif ($mp3->{mtime} > $$records[0][0]) { 
     255            # the mp3 file is newer than its record 
     256            $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
     257            $status_callback->(U => $$mp3{FILENAME}); 
     258            $count++; 
     259        } 
     260    } 
     261     
     262    # SQLite specific code: 
     263    # as a workaround for the 'closing dbh with active staement handles warning 
     264    # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
     265    foreach ($mtime_sth, $insert_sth, $update_sth) { 
     266        $_->{RaiseError} = 0;  # don't die on error 
     267        $_->{PrintError} = 0;  # ...and don't even say anything 
     268        $_->{Active} = 1; 
     269        $_->finish; 
     270    } 
     271     
     272    return $count; 
     273} 
     274 
    410275=head2 update_db 
    411276 
    … …  
    416281from those files to the database. If a file already has a record 
    417282in the database, then it will only be updated if it has been modified 
    418 sinc ethe last time C<update_db> was run. 
     283since the last time C<update_db> was run. 
     284 
     285=cut 
     286 
     287sub update_db { 
     288    my $self = shift; 
     289    my $db_file = shift or croak "Need the name of the database to update"; 
     290    my $dirs = shift; 
     291     
     292    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
     293     
     294    my @dirs = ref $dirs eq 'ARRAY' ? @$dirs : ($dirs); 
     295     
     296    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
     297    my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
     298    my $insert_sth = $dbh->prepare( 
     299        'INSERT INTO mp3 (' .  
     300            join(',', map { $$_[0] } @COLUMNS) . 
     301        ') VALUES (' . 
     302            join(',', map { '?' } @COLUMNS) . 
     303        ')' 
     304    ); 
     305    my $update_sth = $dbh->prepare( 
     306        'UPDATE mp3 SET ' .  
     307            join(',', map { "$$_[0] = ?" } @COLUMNS) .  
     308        ' WHERE FILENAME = ?' 
     309    ); 
     310     
     311    # the number of records added or updated 
     312    my $count = 0; 
     313     
     314    # look for mp3s using the filesystem backend 
     315    require MP3::Find::Filesystem; 
     316    my $finder = MP3::Find::Filesystem->new; 
     317    for my $mp3 ($finder->find_mp3s(dir => \@dirs, no_format => 1)) { 
     318        # see if the file has been modified since it was first put into the db 
     319        $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
     320        $mtime_sth->execute($mp3->{FILENAME}); 
     321        my $records = $mtime_sth->fetchall_arrayref; 
     322         
     323        warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
     324         
     325        if (@$records == 0) { 
     326            $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
     327            $status_callback->(A => $$mp3{FILENAME}); 
     328            $count++; 
     329        } elsif ($mp3->{mtime} > $$records[0][0]) { 
     330            # the mp3 file is newer than its record 
     331            $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
     332            $status_callback->(U => $$mp3{FILENAME}); 
     333            $count++; 
     334        } 
     335    } 
     336     
     337    # as a workaround for the 'closing dbh with active staement handles warning 
     338    # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
     339    foreach ($mtime_sth, $insert_sth, $update_sth) { 
     340        $_->{RaiseError} = 0;  # don't die on error 
     341        $_->{PrintError} = 0;  # ...and don't even say anything 
     342        $_->{Active} = 1; 
     343        $_->finish; 
     344    } 
     345     
     346    return $count; 
     347} 
    419348 
    420349=head2 sync_db 
    … …  
    426355removed. 
    427356 
     357=cut 
     358 
     359# TODO: use DSNs instead of SQLite db names 
     360sub sync_db { 
     361    my $self = shift; 
     362    my $db_file = shift or croak "Need the name of the databse to sync"; 
     363 
     364    my $status_callback = $self->{status_callback} || $DEFAULT_STATUS_CALLBACK; 
     365 
     366    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
     367    my $select_sth = $dbh->prepare('SELECT FILENAME FROM mp3'); 
     368    my $delete_sth = $dbh->prepare('DELETE FROM mp3 WHERE FILENAME = ?'); 
     369     
     370    # the number of records removed 
     371    my $count = 0; 
     372     
     373    $select_sth->execute; 
     374    while (my ($filename) = $select_sth->fetchrow_array) { 
     375        unless (-e $filename) { 
     376            $delete_sth->execute($filename); 
     377            $status_callback->(D => $filename); 
     378            $count++; 
     379        } 
     380    } 
     381     
     382    return $count;     
     383} 
     384 
    428385=head2 destroy_db 
    429386 
    … …  
    431388 
    432389Permanantly removes the database. 
     390 
     391=cut 
     392 
     393# TODO: use DSNs instead of SQLite db names (this might get funky) 
     394sub destroy_db { 
     395    my $self = shift; 
     396    my $db_file = shift or croak "Need the name of a database to destory"; 
     397    unlink $db_file; 
     398} 
     399 
     400 
     401# TODO: use DSNs instead of SQLite db names 
     402sub search { 
     403    my $self = shift; 
     404    my ($query, $dirs, $sort, $options) = @_; 
     405     
     406    croak 'Need a database name to search (set "db_file" in the call to find_mp3s)' unless $$options{db_file}; 
     407     
     408    my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', '', {RaiseError => 1}); 
     409     
     410    # use the 'LIKE' operator to ignore case 
     411    my $op = $$options{ignore_case} ? 'LIKE' : '='; 
     412     
     413    # add the SQL '%' wildcard to match substrings 
     414    unless ($$options{exact_match}) { 
     415        for my $value (values %$query) { 
     416            $value = [ map { "%$_%" } @$value ]; 
     417        } 
     418    } 
     419 
     420    my ($where, @bind) = $sql->where( 
     421        { map { $_ => { $op => $query->{$_} } } keys %$query }, 
     422        ( @$sort ? [ map { uc } @$sort ] : () ), 
     423    ); 
     424     
     425    my $select = "SELECT * FROM mp3 $where"; 
     426     
     427    my $sth = $dbh->prepare($select); 
     428    $sth->execute(@bind); 
     429     
     430    my @results; 
     431    while (my $row = $sth->fetchrow_hashref) { 
     432        push @results, $row; 
     433    } 
     434     
     435    return @results; 
     436} 
     437 
     438# module return 
     4391; 
    433440 
    434441=head1 TODO 
Note: See TracChangeset for help on using the changeset viewer.