Changeset 10 in mp3-find for trunk/bin/mp3db


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
File:
1 edited

Legend:

Unmodified
Added
Removed

  • 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 
Note: See TracChangeset for help on using the changeset viewer.