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

[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]

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

[Bex]

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.

[Bex]

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?

[nynaevelan]

So it works for you now?

Yes it does imagine my delight at being able to convert some of my sql statements to a playlist so that I can report on them. 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.

Nyn

[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!

[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

[Bex]

Strange, I just deleted 20 000 tracks from a playlist in 1.1 second!

[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

[Bex]

Ok, that could explain it then. Just send it to me and I'll have a look.

[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

[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

[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.

Nyn

[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 ...

[Bex]

Thanks fd929!