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

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

Moderators: Peke, Gurus

onenonymous
Posts: 374
Joined: Sat Feb 02, 2008 1:09 pm
Location: Texas

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

Post by onenonymous »

Gee - wouldn't it be great if SQL Viewer could detect if the user had MagicNodes loaded, and if so, give the user a button to automatically move the SQL code into a new MagicNode? just a thought... :)
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 wrote:
To actually add the songs do like this:

Code: Select all

INSERT INTO Playlists (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
I couldn't get this part to work, but it did work if I changed it to:

Code: Select all

INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
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 »

onenonymous wrote:Gee - wouldn't it be great if SQL Viewer could detect if the user had MagicNodes loaded, and if so, give the user a button to automatically move the SQL code into a new MagicNode? just a thought... :)
That would almost be impossible to code. It still pretty easy to just copy the part of the sql which goes into the Magic Node Mask so I don't think I'll try to implement such feature.
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 »

nynaevelan wrote:
Bex wrote:
To actually add the songs do like this:

Code: Select all

INSERT INTO Playlists (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
I couldn't get this part to work, but it did work if I changed it to:

Code: Select all

INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
Nyn
Yeah, this is how it should be. I've corrected the original post as well.

So it works for you now? :)
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 »

Bex wrote:
So it works for you now? :)
Yes it does :D imagine my delight at being able to convert some of my sql statements to a playlist so that I can report on them. :D The only problem I had was with the delete function, it locked up my MM :( , but there were several thousand songs in the playlist. I think I will stick to just deleting them from the playlist manually. Although once I really get the hang of this, I will probably ask you about being able to create a playlist in SV, but I am content for now. Thank you for this one, it has always been frustrating to use SV and now be able to extract the data. :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 »

Deleting tracks should be very fast. Perhaps a compact database will speed things up?

Just ask if you need any assistance with sql and I'll try to help you out! :)
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 compact every week so I don't think that would have helped, but since it was over 10,000 in the list (don't remember the exact number), it took longer than it would have if I would have just did a Control-A/delete.

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 »

Strange, I just deleted 20 000 tracks from a playlist in 1.1 second! :-?
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 »

When I get home I'll send you a copy of the sql which I used, maybe mine was trying to do too much because it wasn't a simple delete. I was combining a delete along with another sql.

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 »

Ok, that could explain it then. Just send it to me and I'll have a look.
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 »

Ok:

I went back and tried the sql again and I think I must have been running many things on my 'puter because I didn't run into the same problem. But I there is one thing about the query which I do not like, it deleted the playlist as well as all the tracks. Is there a way to only delete the tracks within the playlist, I would like to keep the playlist so I do not have to keep looking up the id. Here is the query which I used:

Code: Select all

DELETE FROM Playlists WHERE IDPlaylist = 7429
This is the one I was trying the other day, but the fact it was deleting the playlist first might be why it locked up my system.

Code: Select all

DELETE FROM Playlists WHERE IDPlaylist = 7439 AND INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT 7439, Songs.ID FROM songs WHERE PlayCounter > 0
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 first one should be like this:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPlaylist = 7429
The second one shouldn't work since you can't combine two statements like you have done ("DELETE FROM" and "INSERT INTO"). They must be splitted into two queries that you run separately. Like this: (I also corrected Playlist to PlaylistSongs.)

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPlaylist = 7439

Code: Select all

INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT 7439, Songs.ID FROM songs WHERE PlayCounter > 0
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 »

Ok, thanks for the first, this is the one that I was looking for. I'm not too keen on the combining one, so it doesn't matter that they have to be run in two steps.
I've been testing the delete one again and again, and I am convinced there had to be something else going on which is why the first test was so slow. But I don't remember what I was doing then, so we'll chuck this one up as user error. :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
fd929
Posts: 4
Joined: Tue Sep 16, 2008 3:35 am

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

Post by fd929 »

YOU are a BEAUTIFUL BEAUTIFUL person! THANK YOU SO MUCH!!!!!!!!

I have been racking my brain trying to figure out how I could just delete the \PUBLIC\ out of the songpaths since midnight last night in an effort to not have to rescan my entire library after moving my nas back to the network ....

Let me start over ... hopefully this will help someone else out in the long run.

I have about 40,000 songs for a total of 140 GB that was on two different drives. I wanted to combine them, so I hooked the NAS up to the desktop through USB (quicker, right?). Anyways, once everything was done and set up ... I ended up with the songs on the network, but the library having the incorrect path. I ended up with:
Z:\PUBLIC\Shared Music
when I needed
Z:\Shared Music
No editor I found could make it happen!
SQLite says "no such function: REPLACE"
Maestro says "SQL Error: no such collation sequence: IUNICODE"
Acess 2007 won't even open it "Unexpected table format"

Install Bex script!
run:

Code: Select all

UPDATE Songs SET SongPath = REPLACE(SongPath,':\PUBLIC', ':')
voila!

Thank you again so very very much!!

edit: not sure how my code got escaped before ...
Last edited by fd929 on Tue Sep 16, 2008 5:19 pm, edited 1 time in total.
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 »

Thanks fd929! :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
Post Reply