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

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

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

Post by Teknojnky »

Bex wrote: This will do it. No WHERE clause is needed since you want to update all tracks:
Album level:

Code: Select all

UPDATE Songs SET NormalizeAlbum =-999999.0
Track level:

Code: Select all

UPDATE Songs SET NormalizeTrack=-999999.0
(You know Tekno, but to others:)
Note that the tag of the tracks is not affected only the database.

I did this last night and the update completed in like 40 sec (according to sqlviewer dialog) for ~150k tracks, but then mm was sort of hung up so I had to close it with task manager.

Anyway, I found that if you enable auto-volume analysis after this, that MM will start to clear the analysis from tags before starting on re-analysis... if you manually analyse it seems to work fine.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

I did this last night and the update completed in like 40 sec (according to sqlviewer dialog) for ~150k tracks, but then mm was sort of hung up so I had to close it with task manager
I don't know you got a hung-up. Perhaps an internal SQLite thing and since 150k tracks are a lot it takes a while to do whatever it does?
Anyway, I found that if you enable auto-volume analysis after this, that MM will start to clear the analysis from tags before starting on re-analysis... if you manually analyse it seems to work fine.
Great to know!
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
mistresso
Posts: 67
Joined: Sun Feb 24, 2008 11:15 am
Location: New Haven
Contact:

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

Post by mistresso »

While I can appreciate the speed and ease of being able to perform direct SQL batch updates - if you are updating a field that is ALSO mirrored in the MP3 file, doesn't that just make your entire library become "unsynchronized"?

It seems as if SQL is fast, but the file writes are what takes up the bulk of the time in batch updates. And there's not much we can do about that.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

Yes it does. This is an SQL-tool that queries and/or updates the database but it can't update the actual tags within the files. So if you use this tool to update database values that also exists in the tag of the files then it's recommended to "synchronize" your files afterward.
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
vincenam
Posts: 2
Joined: Thu Feb 19, 2009 6:34 pm
Contact:

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

Post by vincenam »

Hi,

New to this forum so I'll apologise for being a bit thick in advance. I'm having trouble with 'greyed out' entries in my podcast subscription list. I think SQL Viewer might be the thing to have a look at what's going on but I can't figure out how to download SQL Viewer. Can someone please advise.

Thanks vincenam.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

Click the link at the bottom of the first post!
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
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States
Contact:

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

Post by Mizery_Made »

I'm currently using this script to try and figure out how to accomplish something, but I've not made much progress after working out the Select statement that'll visually (semi)accomplish what I want. I'm curious if someone else using this script would be able to offer some aid in working out a statement to run. *Looks at Bex out of the corner of his eye*

The Classifications aren't displayed like many other fields like Artist, Album, Genre, etc. in the tree. Instead of being alphabetically sorted automatically, they instead use a sort order provided in the database. What I'm looking to do is take one of these fields, sort it alphabetically, then automatically update the sort order so this "Alpha-Sort" is visually displayed in the tree. Farthest I could get on this is an easy select statement. "SELECT * FROM Lists WHERE IDListType='1' Order By TextData" That'll show me the list of Tempo's in the database, sorted how I'd like to see them, but I can't even figure out if updating the sort order to this type of sorting is even possible.

After hours of searching google, I'm throwing my hands up and turning to the experts to see what they have to say. Any of you out there know if this is even possible to do with an Update statement? It would be awesome if it is and you could provide it too. Heck, I'm sure a little script with a menu option or button to automatically sort the desired Classification alphabetically would be found useful by a few. I would love nothing more than an easy way of reapplying this Alpha-Sort to the Tempo field (as an example) as I add more and more classifications.

Either way, I'd appreciate any help on this matter. Heck, I'd thank you if you even read all the way through this post. Hehe.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

Well, I don't think it's possible. At least not in one statement. I think a script would be best to solve the 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
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

Ok, I solved it with three statements:
Execute them after each other in SQL-Viewer: (Press the "Exectue DDL/DML Statement"-button)

Code: Select all

CREATE TEMP TABLE IF NOT EXISTS tmpSortClass (NewSortOrder INTEGER PRIMARY KEY AUTOINCREMENT, ListID INTEGER);

Code: Select all

INSERT INTO tmpSortClass (ListID) 
SELECT ID FROM Lists
WHERE IdListType IN (1) --1=Tempo, 2=Mood, 3=Occasion, 4=Quality
ORDER BY TextData;

Code: Select all

UPDATE Lists SET SortOrder=(SELECT NewSortOrder FROM tmpSortClass WHERE ListID=ID)
WHERE ID IN (SELECT ListID FROM tmpSortClass);
But an even nicer solution is to execute the statements within SQLite Spy since it allows you to execute them all in one go:
just paste this into SQLite spy and press F9

Code: Select all

CREATE TEMP TABLE IF NOT EXISTS tmpSortClass (NewSortOrder INTEGER PRIMARY KEY AUTOINCREMENT, ListID INTEGER);
INSERT INTO tmpSortClass (ListID) 
SELECT ID FROM Lists
WHERE IdListType IN (1) --1=Tempo, 2=Mood, 3=Occasion, 4=Quality
ORDER BY TextData COLLATE NOCASE;
UPDATE Lists SET SortOrder=(SELECT NewSortOrder FROM tmpSortClass WHERE ListID=ID)
WHERE ID IN (SELECT ListID FROM tmpSortClass);
To add more than Tempo just add the other numbers comma separated.
E.g.
WHERE IdListType IN (1,2,4) --1=Tempo, 2=Mood, 3=Occasion, 4=Quality
Last edited by Bex on Sun Mar 01, 2009 8:34 pm, edited 2 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
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 my SQL King, thank you for this, it was not something I "needed" but it is sure nice to have. :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
Contact:

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

Post by Bex »

Thanks Nyn! :)
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
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States
Contact:

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

Post by Mizery_Made »

That's amazing. The only hiccup I see is after running that in SQLiteSpy, it wiped the SortOrder values for the other ListTypes (Mood, Occassion and Quality), which doesn't seem like it's a good thing.
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 »

Mizery_Made wrote:That's amazing. The only hiccup I see is after running that in SQLiteSpy, it wiped the SortOrder values for the other ListTypes (Mood, Occassion and Quality), which doesn't seem like it's a good thing.
I didn't run into that, of course I sorted all four at the same time...

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
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States
Contact:

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

Post by Mizery_Made »

True, but that also raises another question. If they're all sorted together, is that going to cause any problems? The Monkey gives each Classification set it's own sort order, independent of the other sort orders. Also, the Monkey's sort is 0 based, while this method begins at 1, that going to break anything down the road either?

EDIT: Okay, solved the problem with the other sets sort order being wiped when running it by adding a "WHERE IDListType=1" clause to the Update statement. Though the 0 based/1 based thing is still concerning.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden
Contact:

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

Post by Bex »

Yes, I forgot to add WHERE IDListType=1 in the UPDATE statement. I'll add it later. The all sorted together thing isn't an issue since it doesn't matter what nr the sortorder starts with or if it's in exact sequence or not. It's only the "order" that is important.
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