SQL-Viewer 2.4 [Script] 2009-10-25
-
- Posts: 374
- Joined: Sat Feb 02, 2008 1:09 pm
- Location: Texas
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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...
-
- 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 couldn't get this part to work, but it did work if I changed it to: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
Code: Select all
INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
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]
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.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...
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]
Yeah, this is how it should be. I've corrected the original post as well.nynaevelan wrote:I couldn't get this part to work, but it did work if I changed it to: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
NynCode: Select all
INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT ThePlaylistIdHere, Songs.ID FROM songs WHERE bla bla bla
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
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:
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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.Bex wrote:
So it works for you now?
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]
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!
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
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:
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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
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]
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
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:
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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
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]
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
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:
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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:
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.
Nyn
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
Code: Select all
DELETE FROM Playlists WHERE IDPlaylist = 7439 AND INSERT INTO PlaylistSongs (IDPlaylist, IDSong) SELECT 7439, Songs.ID FROM songs WHERE PlayCounter > 0
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]
The first one should be like this:
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 = 7429
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
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:
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
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
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
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]
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:
voila!
Thank you again so very very much!!
edit: not sure how my code got escaped before ...
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', ':')
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.
Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]
Thanks fd929!
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