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

[Teknojnky]

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]

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!

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

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.

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

Click the link at the bottom of the first post!

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

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.

[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

[nynaevelan]

You are my SQL King, thank you for this, it was not something I "needed" but it is sure nice to have.

Nyn

[Bex]

Thanks Nyn!

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

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

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

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.