SQL-Viewer 2.4 [Script] 2009-10-25

[nynaevelan]

Hi Bex:

Is it possible to have an sql statement that would select tracks with a last played date in a certain month? I want to try to create some custom nodes for the months of the year.

Nyn

[Bex]

Sure!

Code: Select all

SELECT DATETIME(LastTimePlayed+2415018.5) LastPlayed, ID, SongTitle, Artist, Album
FROM Songs WHERE strftime('%Y-%m',LastTimePlayed+2415018.5) Between 'YYYY-MM' and 'YYYY-MM'

[nynaevelan]

You are an SQL genius, thank you AGAIN.

Nyn

[Bex]

No problem!

[nynaevelan]

I think I have just found the solution to my autoplaylist deficit, now off to blow up my db with complex queries...

Nyn

[jjbuchan]

First of all great script!

However, I've screwed something up by not knowing enough about how MM works and would be very grateful for some assistance.

All I was trying to do was change the path of my files that were still set as \\DESKTOP\... to C:\Documents and Settings. I thought this'd be as simple as using the query:

Code: Select all

UPDATE Songs SET SongPath = replace(SongPath, "\\DESKTOP", "C:\Documents and Settings\Owner") WHERE SongPath LIKE "\\DESKTOP%"

however after running that the songs wouldn't play and although the path would show up correctly to start with, if I then tried to play one it would change to "[Network]\Documents and Settings..." I then read something about the IDMedia so thought I'd just change all my songs to the same ID that the other working tracks were set as. This made no difference and I eventually ended up changing the IDMedia of all tracks and now none of them work. No matter what number I set it at none of them can be found and the pathname changes to [Network]...

Hope that makes sense, and any help would be much appreciated as I now wont have my usual alarm for the morning.

Thanks

[nynaevelan]

I could be wrong since I am almost completely SQL illiterate but all the queries I have been given by the experts are with single quotes not double quotes. Can you try that? BTW, I sure hope you made a backup of your db before you started all this.

Nyn

[jjbuchan]

Looks like it was all just a matter of restarting MM after I'd changed the IDMedia field. I really should've tried that first - the turning it off and on again technique never fails.

I'm gradually getting more songs to play now (It keeps freezing if I try and alter them all at once).

Thanks for the reply though nynaevelan.

[nynaevelan]

Bex:

When running a query is it possible to limit the output to only a sample of rows?

Nyn

[Bex]

Sure!
Just set the "Display only X Rows" to a low value.

[nynaevelan]

I tried that but it still queries for all the tracks. I would like to test it to make sure I am getting what I want before running it full out and I am finding that some of the queries are locking up my MM.

Nyn

[Bex]

I see. The best way to "test" an SQL is to run it on a single track (or a small subset of tracks). This is done in the WHERE clause, for example like this:

Code: Select all

SELECT ID, SongTitle, Artist, Album FROM Songs WHERE ID=Some_ID_you_know_exist
--or
SELECT ID, SongTitle, Artist, Album FROM Songs WHERE ID BETWEEN Start_Range AND End_Range

[nynaevelan]

So to do that I select the set of id's and then add the query I want to test after an AND?

Nyn

[Bex]

The other way around. Write your query and then add:

Code: Select all

AND ID BETWEEN Start_Range AND End_Range

In the WHERE clause.

[Owyn]

Bex:

I tried to use this script to do a quick cleanup, specifically "DELETE * FROM PodcastEpisodes".

The script did not like the syntax.

What am I doing wrong?

Update:
Nevermind.
"DELETE FROM PodcastEpisodes WHERE 1=1" worked.