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

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Post by 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
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Yes, that would be possible. I'll see what I can do but I don't know when though...
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Post by nynaevelan »

No rush... :P

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by 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
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
gege
Posts: 866
Joined: Tue Sep 05, 2006 2:10 pm
Location: Brazil

Post by 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! :o
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
Posts: 106
Joined: Mon Jun 27, 2005 2:47 pm

Post by 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
Posts: 866
Joined: Tue Sep 05, 2006 2:10 pm
Location: Brazil

Post by gege »

MDominik13 wrote: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
Posts: 1784
Joined: Mon Nov 28, 2005 11:55 am
Location: Switzerland
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by 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?
Image
Scripts in use: Genre Finder / Last.fm DJ / Magic Nodes / AutoRateAccurate / Last.FM Node
Skins in use: ZuneSkin SP / Eclipse SP
AutoRateAccurate 3.0.0 (New) - Rates all your songs in less than 5 seconds!
About me: icoaching - internet | marketing | design
nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by nynaevelan »

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

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files
trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by 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. :-?
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
Big_Berny
Posts: 1784
Joined: Mon Nov 28, 2005 11:55 am
Location: Switzerland
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by 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! :)
Image
Scripts in use: Genre Finder / Last.fm DJ / Magic Nodes / AutoRateAccurate / Last.FM Node
Skins in use: ZuneSkin SP / Eclipse SP
AutoRateAccurate 3.0.0 (New) - Rates all your songs in less than 5 seconds!
About me: icoaching - internet | marketing | design
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by 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.
Last edited by Bex on Wed Sep 24, 2008 3:58 pm, edited 3 times in total.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

trixmoto wrote: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! :D
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by trixmoto »

Sorry, I misunderstood what was being requested! :oops:
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by nynaevelan »

trixmoto wrote: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. :wink:

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
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files
Post Reply