SQL-Viewer 2.4 [Script] 2009-10-25
-
- Posts: 5559
- Joined: Wed Feb 07, 2007 11:07 pm
- Location: New Jersey, USA
- Contact:
Bex:
Would it be possible to have the script export the results of queries into a report or a playlist, preferably a report??
Nyn
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
Link to Favorite Scripts/Skins
Join Dropbox, the online site to share your files
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
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
-
- Posts: 5559
- Joined: Wed Feb 07, 2007 11:07 pm
- Location: New Jersey, USA
- Contact:
No rush...
Nyn
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
Link to Favorite Scripts/Skins
Join Dropbox, the online site to share your files
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
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
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
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.
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.
-
- Posts: 106
- Joined: Mon Jun 27, 2005 2:47 pm
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
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
I'll sure back it up. Don't worry.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.
Thanks for the fast response. I'll try that ASAP, then I'll post a report...
cheers.
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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?
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?
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
-
- 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]
I like this idea, I hope it's possible.
Nyn
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
Link to Favorite Scripts/Skins
Join Dropbox, the online site to share your files
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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!
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!
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
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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:
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:
The sql can be much more complex than above. The key is that the it must start with:
(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")
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:
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.
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'
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
Code: Select all
SELECT ThePlaylistIdHere, Songs.ID FROM
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
Code: Select all
DELETE FROM PlaylistSongs WHERE IDPlaylist = ThePlaylistIdHere
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
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
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
You can add songs to a playlist with sql!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.
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
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
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
Sorry, I misunderstood what was being requested!
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.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
-
- 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]
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.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.
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
Link to Favorite Scripts/Skins
Join Dropbox, the online site to share your files