[SOLVED] bash: compare directory to database?

DJCharlie · 05-23-2011, 11:59 AM

So basically, I have a (rather large) database of mp3 track information. I also have several directories of the actual mp3s. I'm trying to write a simple bash script that will check the contents of the directories against what's in the database, and upon finding a file that isn't in the database, do something.

Here's what I have so far:

Code:

#!/bin/bash

odate=$(date +%Y-%m-%d_%T)
sqluser="myuser"
sqlpass="mypass"
sqldb="mydatabase"
sqltbl="music"

echo "Odd Tracks:" > /tmp/odd$odate.eml
echo "  " >> /tmp/odd$odate.eml

cd "/mnt/music/B/Beatles, The"

while read line; do
  artist=${line% - *}
  title=${line#* - }
  title=${title%.mp3*}
  apath="/mnt/music/B/Beatles, The/"
  opath="$apath$artist - $title.mp3"

  echo "SELECT * FROM $sqltbl WHERE path = \"$opath\";" | mysql -u$sqluser -p$sqlpass -htranscoder1 $sqldb

done < <(ls -1 *.mp3)
exit

The trouble I'm having (in the sample directory of "/mnt/music/B/Beatles, The/" the file "Beatles, The - Taxman.mp3" isn't in the database.) is getting it to actually trigger an error when an entry isn't in the database.

Any ideas on getting this to work?

Bonus points: Once triggering on a non-entered file is working, any way to get it to traverse the entire /mnt/music hierarchy?

Thanks!

MTK358 · 05-23-2011, 01:21 PM

Where are you checking whether the file is in the database?

Also, don't parse ls output. Instead of this:

Code:

while read line; do
    # stuff
done < <(ls -1 *.mp3)

use this:

Code:

for line in *.mp3; do
    # stuff
done

DJCharlie · 05-23-2011, 01:37 PM

Quote:

Originally Posted by MTK358

Where are you checking whether the file is in the database?

On this line:

Code:

echo "SELECT * FROM $sqltbl WHERE path = \"$opath\";" | mysql -u$sqluser -p$sqlpass -htranscoder1 $sqldb

The trouble is, MySQL isn't returning an error when the file in question isn't listed.

MTK358 · 05-23-2011, 01:44 PM

Quote:

Originally Posted by DJCharlie

The trouble is, MySQL isn't returning an error when the file in question isn't listed.

What is the mysql command's output based on whether the file exists of not?

DJCharlie · 05-23-2011, 02:25 PM

Quote:

Originally Posted by MTK358

What is the mysql command's output based on whether the file exists of not?

Code:

tracknum	artist	title	genre	path	dir	tlen	idate
4312	Beatles, The	12 Bar Original		/mnt/music/B/Beatles, The/Beatles, The - 12 Bar Original.mp3	B	2:55	2011-03-14_13:40:34

Repeated for each song that's in the directory AND the database. When it hits the song that isn't in the database, it outputs nothing.

MTK358 · 05-23-2011, 02:43 PM

Quote:

Originally Posted by DJCharlie

When it hits the song that isn't in the database, it outputs nothing.

That's all I need to know.

Code:

if [ -z "$(echo "SELECT * FROM $sqltbl WHERE path = \"$opath\";" | mysql -u$sqluser -p$sqlpass -htranscoder1 $sqldb)" ]
then
    # do something if the file isn't in the db
fi

The "-z" option the the "test" command ("[" is an alias for "test" that requires the last argument to be "]") checks if a string is empty.

DJCharlie · 05-23-2011, 03:12 PM

Quote:

Originally Posted by MTK358

That's all I need to know.

That fixed it, thanks!

Now for the bonus...

Instead of setting the path to start in (in this case, /mnt/music/B/Beatles, The), any way to set it to start at /mnt/music and just work its way through all subdirectories?

MTK358 · 05-23-2011, 03:15 PM

Quote:

Originally Posted by DJCharlie

Instead of setting the path to start in (in this case, /mnt/music/B/Beatles, The), any way to set it to start at /mnt/music and just work its way through all subdirectories?

Sure:

Code:

find . -type d -print | while read directory
do
    # do stuff. the variable "$directory" will contain the current dir.
done

If you want to search a predefined directory instead of the current directory, replace the "." after "find" with the path to it.

EDIT: Since your filenames seem to contain spaces, wrap all varaible substitutions (i.e. "${var}") in double quotes. That will preserve whitespace and keep it from being interpreted as an argument separator.

DJCharlie · 05-23-2011, 03:47 PM

Quote:

Originally Posted by MTK358

Sure:

Code:

find . -type d -print | while read directory
do
    # do stuff. the variable "$directory" will contain the current dir.
done

If you want to search a predefined directory instead of the current directory, replace the "." after "find" with the path to it.

EDIT: Since your filenames seem to contain spaces, wrap all varaible substitutions (i.e. "${var}") in double quotes. That will preserve whitespace and keep it from being interpreted as an argument separator.

No joy.

For testing purposes I added:

Code:

echo "$artist - $title"

to track the progress. And all it's outputting is this:

Code:

*.mp3 - *

for each track/directory.

Double quotes seem to make no difference either way.

MTK358 · 05-23-2011, 03:59 PM

Does this do what you want?

Code:

#!/bin/bash

sqluser="myuser"
sqlpass="mypass"
sqldb="mydatabase"
sqltbl="music"

oddtracksfile="/tmp/odd$(date +%Y-%m-%d_%T).eml"

echo "Odd Tracks:" > "${oddtracksfile}"
echo "  " >> "${oddtracksfile}"


find /mnt/music -type d -print | while read dir
do
	for file in "${dir}/"*.mp3
	do
		artist="${file%% - *}"
		title="${file##* - }"
		title="${title%%.mp3*}"
		path="${dir}${artist} - ${title}.mp3"

		if [ -z "$(echo 'SELECT * FROM '"${sqltbl}"' WHERE path = "'"${path}"'";' | mysql -u"${sqluser}" -p"${sqlpass}" -htranscoder1 "${sqldb}")" ]
		then
			echo "${dir}/${file}" >> "${oddtracksfile}"
		fi
	done
done

DJCharlie · 05-23-2011, 05:33 PM

Quote:

Originally Posted by MTK358

Does this do what you want?

Beautifully, yes!

And I have to admit, I'm rather surprised. We just finished doing a full index of the station's music archive, and so far this script has found 1,162 tracks that weren't indexed initially.

Thanks again for the help!

grail · 05-24-2011, 02:58 AM

DJ - I know you closed the call and I was going to suggest an alternative to get rid of one of the loops, but I would have to ask a question first:

Your example file is - "Beatles, The - Taxman.mp3", but when you look at the code you seem to split this into artist and title (which doesn't seem to work correctly if I follow the new code) and then put it back together in the same order. May I ask what the purpose is here?

Starting with your example and code:

Code:

cd "/mnt/music/B/Beatles, The"

# Assume line now contains "Beatles, The - Taxman.mp3"
while read line; do
  artist=${line% - *}
# artist = "Beatles, The"
  title=${line#* - }
# title = Taxman.mp3
  title=${title%.mp3*}
# title = Taxman
  apath="/mnt/music/B/Beatles, The/"
  opath="$apath$artist - $title.mp3"
# opath = /mnt/music/B/Beatles, The/Beatles, The - Taxman.mp3

  echo "SELECT * FROM $sqltbl WHERE path = \"$opath\";" | mysql -u$sqluser -p$sqlpass -htranscoder1 $sqldb

done < <(ls -1 *.mp3)

So from this we can deduce that the construction of opath could have simply been:

Code:

opath="$apath$file"

The issue I have with the new code, which you have said works, is as follows:

Code:

find /mnt/music -type d -print | while read dir
do
# Assume find has placed us in - /mnt/music/B/Beatles, The/
	for file in "${dir}/"*.mp3
	do
        # Assume mp3 we are up to is our previous example
        # This would make file = /mnt/music/B/Beatles, The/Beatles, The - Taxman.mp3
		artist="${file%% - *}"
        # artist = /mnt/music/B/Beatles, The/Beatles, The (oops not artist name only)
		title="${file##* - }"
		title="${title%%.mp3*}"
        # title parts will work the same
		path="${dir}${artist} - ${title}.mp3"
        # path = /mnt/music/B/Beatles, The/mnt/music/B/Beatles, The/Beatles, The - Taxman.mp3 (clearly not correct)

So I am not sure how this is working for you?

MTK358 · 05-24-2011, 06:22 AM

Quote:

Originally Posted by grail

The issue I have with the new code, which you have said works, is as follows:

Code:

find /mnt/music -type d -print | while read dir
do
# Assume find has placed us in - /mnt/music/B/Beatles, The/
	for file in "${dir}/"*.mp3
	do
        # Assume mp3 we are up to is our previous example
        # This would make file = /mnt/music/B/Beatles, The/Beatles, The - Taxman.mp3
		artist="${file%% - *}"
        # artist = /mnt/music/B/Beatles, The/Beatles, The (oops not artist name only)
		title="${file##* - }"
		title="${title%%.mp3*}"
        # title parts will work the same
		path="${dir}${artist} - ${title}.mp3"
        # path = /mnt/music/B/Beatles, The/mnt/music/B/Beatles, The/Beatles, The - Taxman.mp3 (clearly not correct)

So I am not sure how this is working for you?

I didn't notice that.

The way to fix it would be to replace ""${dir}/"*.mp3" with "*.mp3" and put "cd "${dir}"" just before the for loop.

grail · 05-24-2011, 07:47 AM

Hi MTK ... wasn't so much picking on your script but more on the fact that the user said it worked. They may have changed it so it did already