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

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

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

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

Post by Teknojnky » Wed Jan 14, 2009 12:31 pm

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

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

Post by Bex » Wed Jan 14, 2009 6:58 pm

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 » Sat Feb 21, 2009 11:56 am

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

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

Post by Bex » Sat Feb 21, 2009 1:14 pm

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

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

Post by vincenam » Mon Feb 23, 2009 3:15 pm

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

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

Post by Bex » Mon Feb 23, 2009 4:44 pm

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

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

Post by Mizery_Made » Sun Mar 01, 2009 1:29 am

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

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

Post by Bex » Sun Mar 01, 2009 5:50 pm

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

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

Post by Bex » Sun Mar 01, 2009 6:39 pm

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 » Sun Mar 01, 2009 7:00 pm

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

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

Post by Bex » Sun Mar 01, 2009 7:07 pm

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

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

Post by Mizery_Made » Sun Mar 01, 2009 7:12 pm

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 » Sun Mar 01, 2009 7:17 pm

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

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

Post by Mizery_Made » Sun Mar 01, 2009 7:27 pm

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

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

Post by Bex » Sun Mar 01, 2009 8:01 pm

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