How to check if song is available using SQL

popper · Post by **popper** » Mon Feb 05, 2007 10:20 am

As has been stated several times, it would be nice if Radio FreeMonkey was able to handle absent songs, e.g. songs that are on not accessible network shares or on CDs.

Can someone tell me please on how to check this in the database? do i really have to use the AutoPlaylist "Accessible Songs" for this? How does this AutoPlaylist do that (there must be some SQL behind it)?

I think it would be best to have another WHERE clause in the initial SELECT statement so that only the accessible tracks are used:

Code: Select all

   SELECT_Clause = " SELECT Songs.Id, IIF(" & weightedRatingFormula & ">10,10," & weightedRatingFormula & ") "
   FROM_Clause = " FROM Songs "
   WHERE_Clause = " WHERE " & weightedPlayCountFormula &" < " & ratingFormula & " AND DateDiff('d',Songs.LastTimePlayed, Now) > " & MinDaysRepeat

So there should be some more stuff in it like "AND SongAccessible = TRUE" or something like that.

thanks
popper

trixmoto · Post by **trixmoto** » Mon Feb 05, 2007 10:58 am

The database doesn't know if the track is accessible or not. You have to use the file system to check if the filepath in the database points to an actual file or not.

Steegy · Post by **Steegy** » Mon Feb 05, 2007 4:14 pm

Hey.

To reliably know if a volume is active/present (e.g. the External Harddrive is plugged in and turned on), you'll have to use WMI through scripting (with the volume's serial number that is in the Medias database table).
There are at least 2 scripts that do WMI access like this (here's one).
Once you know which IDMedia's are online, you can use sql queries like "SELECT Songs.Title FROM Songs WHERE Songs.IDMedia IN (?, ??, ???)".

You can also check if a file can be accessed or not, but that's less "clean" and you still don't know if it's just the file that's missing, or if the whole volume is offline.

Cheers
Steegy

popper · Post by **popper** » Mon Feb 12, 2007 5:23 am

Thanks for your replies. After reading them, it became clear to me that of course, the database cannot know about these things - it's not its job to know this. I could have thought of this in the first place!

As my music collection still fits on on hard disk, I refrain from implementing this for now - if it had just been an additional SQL statement, I would have done it quickly. But I haven't got the time to work on this in more depth.