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

[nynaevelan]

Bex:

Would it be possible to have the script export the results of queries into a report or a playlist, preferably a report??

Nyn

[Bex]

Yes, that would be possible. I'll see what I can do but I don't know when though...

[nynaevelan]

No rush...

Nyn

[Bex]

Script is updated
Ver 2.3 (2008-05-15) [MM3 only]

These are added to the DropDown which adds data to the SQL-Window from the selected song in MM:
- Added ParentFolderID
- Added FolderID
- Added SubFolderID(s)



Enjoy!
/Bex

[gege]

Hi, guys
I need some help from one of you SQL gurus out there

A couple of days ago I discovered my library has songs which I have listened to IN THE FUTURE!
Yes, there are exactly 465 songs whose values in Last Played column are between April 10, 2009 and May 05, 2009 (39913.3374074074 and 39939.2972106481 in SQL internal representation)!
I used SQL Viewer and discovered these wrong values are only in Songs.LastTimePlayed field. Played.PlayDate has correct values.

So, I ask: what's the correct SQL query I can use to update all songs with Songs.LastTimePlayed >= 39913.3374074074 with the REAL Last Play date existing in Played.PlayDate?
I mean, copying the greatest value of Played.PlayDate to Songs.LastTimePlayed only for songs with Songs.LastTimePlayed >= 39913.3374074074

That's it. Thanks in advance.

Gege.

[MDominik13]

Seems thats SQLite doesn't support UPDATE/FROM or UPDATE's with JOIN's.

Not very pretty but it works:
UPDATE Songs
SET LastTimePlayed = (SELECT MAX(PlayDate) FROM Played WHERE Played.IDSONG = Songs.Id)
WHERE Songs.ID IN (SELECT IDSONG FROM Played)
AND Songs.LastTimePlayed >= 39913.3374074074

I tested on my database and it worked but I would still back yours up before running it. Also, MediaMonkey didn't seem to like it when I tried to update a track that was currently selected in the window so you might want to try running it from a node where no tracks are visible in the main window.

Mike

[gege]

I tested on my database and it worked but I would still back yours up before running it. Also, MediaMonkey didn't seem to like it when I tried to update a track that was currently selected in the window so you might want to try running it from a node where no tracks are visible in the main window.

I'll sure back it up. Don't worry.
Thanks for the fast response. I'll try that ASAP, then I'll post a report...

cheers.

[Big_Berny]

Hi Bex,
I have a little feature request for your script:
If we get songs out of the SQL-query it would be cool if we could load them into a playlist. Like this we could have playlists based on SQL-queries which gives us much more possibilities than we have with AutoPlaylists.

What do you think?

[nynaevelan]

I like this idea, I hope it's possible.

Nyn

[trixmoto]

Well this script really returns the data in it's purest form, it doesn't return song objects. Whilst it might be possible, it doesn't really seem to fit with the purpose of the script for me.

[Big_Berny]

Well I don't know, trix. Personally use the script often for debugging and finding problems in my scripts which use SQL. And if I could load the songs into a playlist it would be even easier to find bugs.

The other thing is that I'd really like autoplaylists with custom SQL-queries. Unfortunately this is not possible in MM and I already thought of making such a script by myself. But then I saw that the GUI of this script would already be perfect for that because it wouldn't need a lot changes.

Maybe you're right and a separate script would be better. But a 'autoplaylist-script' would be problematic because autoplaylists are only for gold-users... So I thought of just implementing that option in this script. But let's see what Bex means!

[Bex]

This is already possible!

Create a playlist in MM for the purpose and then run this sql to get the newly created playlists internal ID:

Code: Select all

SELECT IDPlaylist, PlaylistName FROM Playlists WHERE PlaylistName = 'playlistname goes here'

Note the id for future use.

Construct your sql which finds the songs you want to put in the playlists.
When you then want to add the songs to your playlist you need to construct the SELECT-statement like this:

Code: Select all

SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla

The sql can be much more complex than above. The key is that the it must start with:

Code: Select all

SELECT ThePlaylistIdHere, Songs.ID FROM

(Songs.ID can be another field as long as it means the same thing. E.g. Played.IDSong or PlaylistSongs.IDSong)

To actually add the songs to the playlist execute this statement: (Press "Execute DDL/DML Statement")

Code: Select all

INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla

If you wish to remove all the songs from your playlist before you add new ones, either do it manually in MM or execute this statement:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPlaylist = ThePlaylistIdHere

I haven't tested it. The only problem I see is that the songorder field isn't populated, that might yield errors or strange behaviour sometimes. If so, let me know and I'll try to come up with a solution.

[Bex]

Well this script really returns the data in it's purest form, it doesn't return song objects. Whilst it might be possible, it doesn't really seem to fit with the purpose of the script for me.

You can add songs to a playlist with sql!

[trixmoto]

Sorry, I misunderstood what was being requested!

[nynaevelan]

Well this script really returns the data in it's purest form, it doesn't return song objects. Whilst it might be possible, it doesn't really seem to fit with the purpose of the script for me.

I understand, but there are some who use the script for purposes which it was not created for but it has the ability to accomplish. For myself, who is not a scripter, I use several sql statements (given to me by scripters) in order to gather information that is not available in a script or information that would not require a script but an sql statement to gather. Just wanted to help you understand how this non-scripter uses the script.

Bex:

If it is possible to extract the data to a playlist, this will eliminate the need for me to export it to Excel.

Nyn