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:

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

Post by 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
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

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

Post by 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'
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:

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

Post by nynaevelan »

You are an SQL genius, thank you AGAIN. :D :D :D

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

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

Post by Bex »

No problem!
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:

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

Post by nynaevelan »

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

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
jjbuchan
Posts: 11
Joined: Thu May 11, 2006 6:05 pm

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

Post by 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
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 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. :wink:

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
jjbuchan
Posts: 11
Joined: Thu May 11, 2006 6:05 pm

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

Post by 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
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 »

Bex:

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

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

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

Post by Bex »

Sure!
Just set the "Display only X Rows" to a low value.
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:

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

Post by 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
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

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

Post by 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
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:

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

Post by 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
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

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

Post by 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.
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
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

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

Post by 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.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Post Reply