Changeset 10 in mp3-find for trunk


Ignore:
Timestamp:
02/02/06 01:51:00 (19 years ago)
Author:
peter
Message:
  • added test suite for the DB backend
  • doc corrections and updates to Find.pm and Base.pm
  • moved create and update database functions from mp3db to DB.pm
  • added "destroy_db" function to DB.pm
  • documented mp3db
  • set version to 0.02
Location:
trunk
Files:
1 added
6 edited

Legend:

Unmodified
Added
Removed

  • trunk/Changes

    r6 r10  
    44    - first CPAN release 
    55 
    6 0.02 
    7     - doc updates (thanks to Michael Slass for the suggestion) 
    8  
     60.02  1 Feb 2006 
     7    - doc updates to MP3::Find (thanks to Mike Slass for the suggestion) 
     8    - added a test suite for the DB backend 
     9    - DB management functions are now in the DB backend 
     10    - rewrote (and documented) mp3db to use the new DB backend functions 

  • trunk/MANIFEST

    r8 r10  
    55t/01.t 
    66t/02-filesystem.t 
     7t/03-db.t 
    78t/mp3s/not-an-mp3 
    89lib/MP3/Find.pm 

  • trunk/bin/mp3db

    r1 r10  
    33 
    44use lib '/home/peter/projects/mp3-find/lib'; 
    5 use DBI; 
    6 use MP3::Find; 
     5use MP3::Find::DB; 
    76 
    87use File::Spec::Functions qw(catfile); 
    98use Getopt::Long; 
    109GetOptions( 
    11     'create' => \my $CREATE, 
     10    'create'   => \my $CREATE, 
    1211    'file|f=s' => \my $DB_FILE, 
    1312); 
    … …  
    1514$DB_FILE ||= catfile($ENV{HOME}, 'mp3.db'); 
    1615 
    17 #TODO: hints on numeric columns 
    18 my @COLUMNS = ( 
    19     [ mtime => 'INTEGER' ], 
    20     [ FILENAME => 'TEXT' ],  
    21     [ TITLE => 'TEXT' ],  
    22     [ ARTIST => 'TEXT' ],  
    23     [ ALBUM => 'TEXT' ], 
    24     [ YEAR => 'INTEGER' ],  
    25     [ COMMENT => 'TEXT' ],  
    26     [ GENRE => 'TEXT' ],  
    27     [ TRACKNUM => 'INTEGER' ],  
    28     [ VERSION => 'NUMERIC' ], 
    29     [ LAYER => 'INTEGER' ],  
    30     [ STEREO => 'TEXT' ], 
    31     [ VBR => 'TEXT' ], 
    32     [ BITRATE => 'INTEGER' ],  
    33     [ FREQUENCY => 'INTEGER' ],  
    34     [ SIZE => 'INTEGER' ],  
    35     [ OFFSET => 'INTEGER' ],  
    36     [ SECS => 'INTEGER' ],  
    37     [ MM => 'INTEGER' ], 
    38     [ SS => 'INTEGER' ], 
    39     [ MS => 'INTEGER' ],  
    40     [ TIME => 'TEXT' ], 
    41     [ COPYRIGHT => 'TEXT' ],  
    42     [ PADDING => 'INTEGER' ],  
    43     [ MODE => 'INTEGER' ], 
    44     [ FRAMES => 'INTEGER' ],  
    45     [ FRAME_LENGTH => 'INTEGER' ],  
    46     [ VBR_SCALE => 'INTEGER' ], 
    47 ); 
     16my @DIRS = @ARGV; 
    4817 
    49 my @DIRS = @ARGV; 
    50 push @DIRS, $ENV{HOME} unless @DIRS; 
     18my $f = MP3::Find::DB->new; 
     19$f->create_db($DB_FILE) if $CREATE; 
     20$f->update_db($DB_FILE, \@DIRS) if @DIRS; 
    5121 
     22=head1 NAME 
    5223 
    53 my $dbh = DBI->connect("dbi:SQLite:dbname=$DB_FILE",'','', { RaiseError => 1 }); 
     24mp3db - Frontend for creating and updating a database for MP3::Find::DB 
    5425 
    55 create_table($dbh) if $CREATE; 
    56 read_mp3s(\@DIRS); 
     26=head1 SYNOPSIS 
    5727 
    58 sub read_mp3s { 
    59     my $dirs = shift; 
     28    # create the database file 
     29    $ mp3db --create --file my_mp3.db 
     30     
     31    # add info 
     32    $ mp3db --file my_mp3.db ~/mp3 
     33     
     34    # update, and add results from another directory 
     35    $ mp3db --file my_mp3.db ~/mp3 ~/cds 
    6036 
    61     my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
    62     my $insert_sth = $dbh->prepare( 
    63         'INSERT INTO mp3 (' .  
    64             join(',', map { $$_[0] } @COLUMNS) . 
    65         ') VALUES (' . 
    66             join(',', map { '?' } @COLUMNS) . 
    67         ')' 
    68     ); 
    69     my $update_sth = $dbh->prepare( 
    70         'UPDATE mp3 SET ' .  
    71             join(',', map { "$$_[0] = ?" } @COLUMNS) .  
    72         ' WHERE FILENAME = ?' 
    73     ); 
    74      
    75     for my $mp3 (find_mp3s(dir => $dirs, no_format => 1)) { 
    76         # see if the file has been modified since it was first put into the db 
    77         $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
    78         $mtime_sth->execute($mp3->{FILENAME}); 
    79         my $records = $mtime_sth->fetchall_arrayref; 
    80          
    81         warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
    82          
    83         if (@$records == 0) { 
    84             $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
    85             print "A $$mp3{FILENAME}\n"; 
    86         } elsif ($mp3->{mtime} > $$records[0][0]) { 
    87             # the mp3 file is newer than its record 
    88             $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
    89             print "U $$mp3{FILENAME}\n"; 
    90         } 
    91     } 
    92      
    93     # as a workaround for the 'closing dbh with active staement handles warning 
    94     # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
    95     # NOT WORKING!!! 
    96     foreach ($mtime_sth, $insert_sth, $update_sth) { 
    97         $_->{Active} = 1; 
    98         $_->finish; 
    99     } 
    100 } 
     37=head1 DESCRIPTION 
    10138 
    102 #TODO: hints on numeric vs. string columns, for proper sorting 
    103 sub create_table { 
    104     my $dbh = shift; 
    105     $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); 
    106 } 
     39    mp3db [options] [directory] [directories...] 
    10740 
    108 =begin 
     41Creates and/or updates a database of ID3 data from the mp3s found 
     42in the given directories. 
    10943 
    110     CREATE TABLE mp3 ( 
    111         mtime, 
    112          
    113         FILENAME, 
    114          
    115         TITLE, 
    116         ARTIST, 
    117         ALBUM, 
    118         YEAR, 
    119         COMMENT, 
    120         GENRE, 
    121         TRACKNUM, 
    122          
    123         VERSION,         -- MPEG audio version (1, 2, 2.5) 
    124         LAYER,           -- MPEG layer description (1, 2, 3) 
    125         STEREO,          -- boolean for audio is in stereo 
    126      
    127         VBR,             -- boolean for variable bitrate 
    128         BITRATE,         -- bitrate in kbps (average for VBR files) 
    129         FREQUENCY,       -- frequency in kHz 
    130         SIZE,            -- bytes in audio stream 
    131         OFFSET,          -- bytes offset that stream begins 
    132      
    133         SECS,            -- total seconds 
    134         MM,              -- minutes 
    135         SS,              -- leftover seconds 
    136         MS,              -- leftover milliseconds 
    137         TIME,            -- time in MM:SS 
    138      
    139         COPYRIGHT,       -- boolean for audio is copyrighted 
    140         PADDING,         -- boolean for MP3 frames are padded 
    141         MODE,            -- channel mode (0 = stereo, 1 = joint stereo, 
    142                          -- 2 = dual channel, 3 = single channel) 
    143         FRAMES,          -- approximate number of frames 
    144         FRAME_LENGTH,    -- approximate length of a frame 
    145         VBR_SCALE        -- VBR scale from VBR header 
    146     ); 
     44=head2 Options 
     45 
     46=over 
     47 
     48=item C<--create>, C<-c> 
     49 
     50Create the database file named by the C<--file> option. 
     51 
     52=item C<--file>, C<-f> 
     53 
     54The name of the database file to work with. Defaults to F<~/mp3.db>. 
     55 
     56=back 
     57 
     58=head1 AUTHOR 
     59 
     60Peter Eichman <peichman@cpan.org> 
     61 
     62=head1 COPYRIGHT AND LICENSE 
     63 
     64Copyright (c) 2006 by Peter Eichman. All rights reserved. 
     65 
     66This program is free software; you can redistribute it and/or 
     67modify it under the same terms as Perl itself. 
    14768 
    14869=cut 

  • trunk/lib/MP3/Find.pm

    r6 r10  
    99use Carp; 
    1010 
    11 $VERSION = '0.01'; 
     11$VERSION = '0.02'; 
    1212 
    1313@EXPORT = qw(find_mp3s); 
    … …  
    6969is currently not as stable as the Filesystem backend. 
    7070 
     71B<Note the second>: This whole project is still in the alpha stage, so 
     72I can make no guarentees that there won't be significant interface changes 
     73in the next few versions or so. Also, comments about what about the API 
     74rocks (or sucks!) are appreciated. 
     75 
    7176=head1 REQUIRES 
    7277 

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

    r7 r10  
    9393=head1 NAME 
    9494 
    95 MP3::Find::Base - Base class for MP3::Find finders 
     95MP3::Find::Base - Base class for MP3::Find backends 
    9696 
    9797=head1 SYNOPSIS 

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

    r3 r10  
    55 
    66use base qw(MP3::Find::Base); 
     7use Carp; 
    78 
    89use DBI; 
    … …  
    1112my $sql = SQL::Abstract->new; 
    1213 
     14my @COLUMNS = ( 
     15    [ mtime        => 'INTEGER' ],  # the filesystem mtime, so we can do incremental updates 
     16    [ FILENAME     => 'TEXT' ],  
     17    [ TITLE        => 'TEXT' ],  
     18    [ ARTIST       => 'TEXT' ],  
     19    [ ALBUM        => 'TEXT' ], 
     20    [ YEAR         => 'INTEGER' ],  
     21    [ COMMENT      => 'TEXT' ],  
     22    [ GENRE        => 'TEXT' ],  
     23    [ TRACKNUM     => 'INTEGER' ],  
     24    [ VERSION      => 'NUMERIC' ], 
     25    [ LAYER        => 'INTEGER' ],  
     26    [ STEREO       => 'TEXT' ], 
     27    [ VBR          => 'TEXT' ], 
     28    [ BITRATE      => 'INTEGER' ],  
     29    [ FREQUENCY    => 'INTEGER' ],  
     30    [ SIZE         => 'INTEGER' ],  
     31    [ OFFSET       => 'INTEGER' ],  
     32    [ SECS         => 'INTEGER' ],  
     33    [ MM           => 'INTEGER' ], 
     34    [ SS           => 'INTEGER' ], 
     35    [ MS           => 'INTEGER' ],  
     36    [ TIME         => 'TEXT' ], 
     37    [ COPYRIGHT    => 'TEXT' ],  
     38    [ PADDING      => 'INTEGER' ],  
     39    [ MODE         => 'INTEGER' ], 
     40    [ FRAMES       => 'INTEGER' ],  
     41    [ FRAME_LENGTH => 'INTEGER' ],  
     42    [ VBR_SCALE    => 'INTEGER' ], 
     43); 
     44 
     45 
    1346sub search { 
    1447    my $self = shift; 
    1548    my ($query, $dirs, $sort, $options) = @_; 
    1649     
    17     my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', ''); 
     50    croak 'Need a database name to search (set "db_file" in the call to find_mp3s)' unless $$options{db_file}; 
     51     
     52    my $dbh = DBI->connect("dbi:SQLite:dbname=$$options{db_file}", '', '', {RaiseError => 1}); 
    1853     
    1954    # use the 'LIKE' operator to ignore case 
    … …  
    4378     
    4479    return @results; 
     80} 
     81 
     82sub create_db { 
     83    my $self = shift; 
     84    my $db_file = shift or croak "Need a name for the database I'm about to create"; 
     85    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
     86    $dbh->do('CREATE TABLE mp3 (' . join(',', map { "$$_[0] $$_[1]" } @COLUMNS) . ')'); 
     87} 
     88 
     89sub update_db { 
     90    my $self = shift; 
     91    my $db_file = shift or croak "Need the name of the databse to update"; 
     92    my $dirs = shift; 
     93     
     94    my @dirs = ref $dirs eq 'ARRAY' ? @$dirs : ($dirs); 
     95     
     96    my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '', {RaiseError => 1}); 
     97    my $mtime_sth = $dbh->prepare('SELECT mtime FROM mp3 WHERE FILENAME = ?'); 
     98    my $insert_sth = $dbh->prepare( 
     99        'INSERT INTO mp3 (' .  
     100            join(',', map { $$_[0] } @COLUMNS) . 
     101        ') VALUES (' . 
     102            join(',', map { '?' } @COLUMNS) . 
     103        ')' 
     104    ); 
     105    my $update_sth = $dbh->prepare( 
     106        'UPDATE mp3 SET ' .  
     107            join(',', map { "$$_[0] = ?" } @COLUMNS) .  
     108        ' WHERE FILENAME = ?' 
     109    ); 
     110     
     111    # the number of records added or updated 
     112    my $count = 0; 
     113     
     114    # look for mp3s using the filesystem backend 
     115    require MP3::Find::Filesystem; 
     116    my $finder = MP3::Find::Filesystem->new; 
     117    for my $mp3 ($finder->find_mp3s(dir => \@dirs, no_format => 1)) { 
     118        # see if the file has been modified since it was first put into the db 
     119        $mp3->{mtime} = (stat($mp3->{FILENAME}))[9]; 
     120        $mtime_sth->execute($mp3->{FILENAME}); 
     121        my $records = $mtime_sth->fetchall_arrayref; 
     122         
     123        warn "Multiple records for $$mp3{FILENAME}\n" if @$records > 1; 
     124         
     125        if (@$records == 0) { 
     126            $insert_sth->execute(map { $mp3->{$$_[0]} } @COLUMNS); 
     127            print STDERR "A $$mp3{FILENAME}\n"; 
     128            $count++; 
     129        } elsif ($mp3->{mtime} > $$records[0][0]) { 
     130            # the mp3 file is newer than its record 
     131            $update_sth->execute((map { $mp3->{$$_[0]} } @COLUMNS), $mp3->{FILENAME}); 
     132            print STDERR "U $$mp3{FILENAME}\n"; 
     133            $count++; 
     134        } 
     135    } 
     136     
     137    # as a workaround for the 'closing dbh with active staement handles warning 
     138    # (see http://rt.cpan.org/Ticket/Display.html?id=9643#txn-120724) 
     139    foreach ($mtime_sth, $insert_sth, $update_sth) { 
     140        $_->{Active} = 1; 
     141        $_->finish; 
     142    } 
     143     
     144    return $count; 
     145} 
     146 
     147sub destroy_db { 
     148    my $self = shift; 
     149    my $db_file = shift or croak "Need the name of a database to destory"; 
     150    unlink $db_file; 
    45151} 
    46152 
    … …  
    64170        }, 
    65171        ignore_case => 1, 
    66     ); 
     172        db_file => 'mp3.db', 
     173    ); 
     174     
     175    # you can do things besides just searching the database 
     176     
     177    # create another database 
     178    $finder->create_db('my_mp3s.db'); 
     179     
     180    # update the database from the filesystem 
     181    $finder->update_db('my_mp3s.db', ['/home/peter/mp3', '/home/peter/cds']); 
     182     
     183    # and then blow it away 
     184    $finder->destroy_db('my_mp3s.db'); 
    67185 
    68186=head1 REQUIRES 
    … …  
    121239=back 
    122240 
     241=head1 METHODS 
     242 
     243=head2 create_db 
     244 
     245    $finder->create_db($db_filename); 
     246 
     247Creates a SQLite database in the file named c<$db_filename>. 
     248 
     249=head2 update_db 
     250 
     251    my $count = $finder->update_db($db_filename, \@dirs); 
     252 
     253Searches for all mp3 files in the directories named by C<@dirs> 
     254using L<MP3::Find::Filesystem>, and adds or updates the ID3 info 
     255from those files to the database. If a file already has a record 
     256in the database, then it will only be updated if it has been modified 
     257sinc ethe last time C<update_db> was run. 
     258 
     259=head2 destroy_db 
     260 
     261    $finder->destroy_db($db_filename); 
     262 
     263Permanantly removes the database. 
     264 
    123265=head1 TODO 
    124266 
    125 Move the database/table creation code from F<mp3db> into this 
    126 module. 
    127  
    128267Database maintanence routines (e.g. clear out old entries) 
    129268 
     269Allow the passing of a DSN or an already created C<$dbh> instead 
     270of a SQLite database filename. 
     271 
    130272=head1 SEE ALSO 
    131273 
    132 L<MP3::Find>, L<MP3::Find::DB> 
     274L<MP3::Find>, L<MP3::Find::Filesystem>, L<mp3db> 
    133275 
    134276=head1 AUTHOR 
Note: See TracChangeset for help on using the changeset viewer.