Here's my situation: I have a gazillion music files spread over
10 computers, thumbdrives, external drives, SD cards, etc. To make things
worse, the directories look like this
01 Runnin' With The Devil.mp3
02 Eruption.mp3
03 You Really Got Me.mp3
etc
I've long since forgotten what album I ripped them from, who the artist is, etc
I know there are mp3 tags somewhere, but depending on how the music was ripped and which OS did the ripping, there isn't a convenient way to automate getting all that stuff and putting it in a data base (that I know of). I know about packages like exfalso and quodlibet but I don't want to just view the data, I want ACCESS to it. I like MySql and I like Bash and I like PHP so I decided to make some tools to automate organizing my collection.
I started with looking at a music file to see what was there in plain
text so I did
strings "05 I'm The One.mp3" | head
and got back
HTT2 I'm The One Van Halen Edward Van Halen, Alex Van Halen, Michael Anthony and David Lee Roth Van Halen 5/11 1978 (17) iTunes v6.0.5.20 engiTunNORM
Wowza.. cool. It looks like everything I want is there. So my next
step was to do
for a in *; do strings "$a" | head -7 >> ../stuff; echo $a >> ../stuff; done
Where did the 7 come from? Trial and error. I didn't see anything below the year that was useful.
Now I have a file in the parent directory named stuff that contains all this meta data plus the filename of all the songs in that directory. My next step is to build a MySql table structure to hold this data.
First, I have a file in my home directory named .my.cnf that looks like this:
[client]
host=localhost
user=me
password=secret
[mysql]
database=xxx
prompt=\d>\_
;silent
;table
With the .my.cnf in place, I just need to type mysql to connect to the server and open the database
Next, I build a file I named sqlinit containing the commands to build the table structure:
DROP TABLE IF EXISTS `music`; CREATE TABLE `music` ( `Title` char(40) default NULL, `Artist` char(40) default NULL, `Album` char(80) default NULL, `Sequence` char(5) default NULL, `Year` char(4) default NULL, `Filename` char(40) default NULL);
And then I build the table with
mysql < sqlinit
Now that the table is built, it's time to write some php code to read the file named stuff and put the data into the MySql table.
Here is what I came up with:
#!/usr/bin/php -q
<?php
$sql = mysql_connect (localhost,me,secret) or die(mysql_error());
mysql_select_db('xxx') or die(mysql_error());
$fh = fopen('stuff','r') or die($php_errormsg);
while (true) {
$junk = fgets($fh);
if (feof($fh)) break;
$title = substr(mysql_real_escape_string(fgets($fh)),0,-2);
$artist = substr(mysql_real_escape_string(fgets($fh)),0,-2);
$album = substr(mysql_real_escape_string(fgets($fh)),0,-2);
$sequence = mysql_real_escape_string(rtrim(fgets($fh),"\n"));
$year = mysql_real_escape_string(rtrim(fgets($fh),"\n"));
$filename = mysql_real_escape_string(rtrim(fgets($fh),"\n"));
$query = "INSERT INTO music VALUES ('$title','$artist','$album','$sequence','$year','$filename')";
mysql_query($query) or die (mysql_error());
}
fclose($fh) or die($php_errormsg);
mysql_close($sql) or die(mysql_error());
?>
I make this script executable (chmod +x) and run it, and now I have a MySql database of songs
|
This site best viewed with a browser |
| Warning: This is a Debian centric site | |
| Many thanks to Debra and Ian Murdock for making Debian possible | |
| First created Dec 14, 2008 ~ Last revised April 22, 2009 |
