LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 05-23-2011, 12:59 PM   #1
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Rep: Reputation: 4
Question bash: compare directory to database?


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!
 
Old 05-23-2011, 02:21 PM   #2
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
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

Last edited by MTK358; 05-23-2011 at 02:23 PM.
 
Old 05-23-2011, 02:37 PM   #3
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Original Poster
Rep: Reputation: 4
Quote:
Originally Posted by MTK358 View Post
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.
 
Old 05-23-2011, 02:44 PM   #4
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
Quote:
Originally Posted by DJCharlie View Post
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?

Last edited by MTK358; 05-23-2011 at 02:45 PM.
 
Old 05-23-2011, 03:25 PM   #5
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Original Poster
Rep: Reputation: 4
Quote:
Originally Posted by MTK358 View Post
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.
 
Old 05-23-2011, 03:43 PM   #6
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
Quote:
Originally Posted by DJCharlie View Post
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.

Last edited by MTK358; 05-23-2011 at 03:45 PM.
 
Old 05-23-2011, 04:12 PM   #7
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Original Poster
Rep: Reputation: 4
Quote:
Originally Posted by MTK358 View Post
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?
 
Old 05-23-2011, 04:15 PM   #8
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
Quote:
Originally Posted by DJCharlie View Post
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.

Last edited by MTK358; 05-23-2011 at 04:19 PM.
 
Old 05-23-2011, 04:47 PM   #9
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Original Poster
Rep: Reputation: 4
Quote:
Originally Posted by MTK358 View Post
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.
 
Old 05-23-2011, 04:59 PM   #10
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
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
 
Old 05-23-2011, 06:33 PM   #11
DJCharlie
Member
 
Registered: Sep 2010
Posts: 37

Original Poster
Rep: Reputation: 4
Quote:
Originally Posted by MTK358 View Post
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!
 
Old 05-24-2011, 03:58 AM   #12
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,692

Rep: Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987
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?
 
Old 05-24-2011, 07:22 AM   #13
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
Quote:
Originally Posted by grail View Post
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.
 
Old 05-24-2011, 08:47 AM   #14
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,692

Rep: Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987
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
 
  


Reply

Tags
bash, mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare oracle database table contains using scripts RB_ramesh Linux - Newbie 4 12-29-2010 04:09 PM
How to compare records in two tables in seperate My Sql database using perl script chandanperl Programming 1 08-22-2008 10:33 AM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 10:45 AM
directory compare tmoorman Linux - Newbie 2 09-26-2003 02:45 PM
Best way to compare 2 directory heirarchies? Electrode Linux - General 1 11-14-2002 11:36 PM


All times are GMT -5. The time now is 05:12 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration