Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
Oh wise PerlMonks,

I am working on a script to parse my mp3 files and write the id3 tag info to a database, mainly just for the exercise of doing it. My code is below. It works, but I'm pretty sure the way I am doing things is less efficient than it could be.

I'd love any suggestions on how to make this code run more quickly and efficiently.

BTW - if it matters, I am using ActivePerl on a Vista x64 box.

#!/usr/bin/perl use strict; use warnings; use MP3::Tag; use File::Find; use DBI; #set up sqlite connection my $db = DBI->connect("dbi:SQLite:music.sqlite","",""); #set up variables my $count = 0; my @songinfo = my $mp3 = my @dirs = ("/users/mine/music"); # Get rid of the previous version of the table $db->do("DROP TABLE IF EXISTS Songs") or DIE("Couldn't drop old table" +); # Recreate Table $db->do("CREATE TABLE songs (id INTEGER PRIMARY KEY AUTOINCREMENT, Tra +ck INTEGER, Title TEXT, Artist TEXT, Album TEXT, Year INTEGER, Genre +TEXT, Path TEXT)") or DIE("Couldn't create new table"); #set up SQL statement my $sth = $db->prepare('INSERT INTO Songs (Track, Title, Artist, Album +, Year, Genre, Path) VALUES (?, ?, ?, ?, ?, ?, ?)'); # get total number of mp3's my @songtotal = `dir c:\\users\\mine\\music\\*.mp3 /s`; my @mp3total = split(/ /, trim($songtotal[(scalar @songtotal)-2])); # look for files in each directory find(\&displayMP3Info, @dirs); # this function is called every time a file is found sub displayMP3Info { # if the file has an MP3 extension if (/\.mp3$/i) { # increment counter $count++; # create new MP3-Tag object $mp3 = MP3::Tag->new($_); # get tag information $mp3->get_tags(); # check to see if an ID3v1 tag exists if (exists $mp3->{ID3v1}) { #if it's an ID3V1 Tag stick the parts of it into an array +called songinfo @songinfo = ($mp3->{ID3v1}->track, $mp3->{ID3v1}->title, $ +mp3->{ID3v1}->artist, $mp3->{ID3v1}->album, $mp3->{ID3v1}->year, $mp3 +->{ID3v1}->genre, $File::Find::name); # put it in the database $sth->execute($songinfo[0], $songinfo[1], $songinfo[2], $s +onginfo[3], $songinfo[4], $songinfo[5], $songinfo[6]); # print "\n$songinfo[0], $songinfo[1], $songinfo[2], $song +info[3], $songinfo[4], $songinfo[5], $songinfo[6]\n"; # update the total on the screen printf STDERR ("\r%02d of $mp3total[0] files.", $count); } # clean up $mp3->close(); } } print "Done.\n"; # trim function to get rid of leading and trailing spaces sub trim{ my $string = shift; $string =~ s/^\s+|\s+$//g; return $string; }


In reply to Improving SQL speed by Mad_Mac

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2024-03-29 02:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found