ILC wrote: ↑Tue Mar 12, 2024 1:06 am
I want to execute 360 lines of SQL that look like this:
UPDATE songs SET Custom1 = '009' WHERE albumartist = 'Coldplay' AND album = 'Viva la Vida';
I have this program that did not work:
You can do this easily.
You just have to do it outside of MM5.
You should be able to do it easily inside MM5, but you can't:
- No technical Wiki as there was with previous versions of MM5
- No response to your question; it has sat here unanswered for a week
It is dead simple outside MM5.
Here is an
example of what you want to achieve.
And here is the
proof that it works.
Special tools that you require: none that you have to pay for.
I did exaggerate slightly about it being dead simple. There are a couple of challenges, but the workaround is simple.
The challenges arise from the fact that MM5 modified the standard SQL engine. with their own collation, and a tokeniser.
They don't ship the former, but it is available on their web site.
The later is not available.
You need to download a copy of the free open source tool named DB Browser for Sqlite.
Use this tool to open your MM5 database (close the MM5 app first).
Take the Tools>LoadExtension option to load the SQLite3MMExt.dll file that you downloaded from MM.
That step have fixed the missing collation.
The missing tokeniser will bite you when you try and update any MM5 tag that is covered by their Full Text Search facility.
The Customx tags are affected by this.
What you have to do is delete the SQL trigger, in the MM5 db, that is monitoring changes to custom tag that you want to update.
My query above updates the Custom8 tag. The name of the trigger that I deleted was 'update_songs_custom8'
What this trigger does it is monitor the custom8 tag for any change that you make, and puts your new value in their full text search files. Now that it is deleted you won't be able to find any newly assigned value,
in that custom tag column, using MM5 Advanced Search facility facility.
You can still see your tags in MM5, you sort the display by that column and everything looks ok. You can use the MM5 Column Browser facility as filter for those tag values OK. You can use the custom tag when for criteria for playlists, or Custom Collections.
the only thing that is affected AFAIK is queries like xxx, where MM5 finds every track that contains xxx in any tag.
In my case I am OK with this. I am putting album play count in one custom tag, and date album last played in another.
I don't want those numbers in my free form search anyway. ... You illustration shows you putting an integer in a custom tag, so you are probably in the same situation.
Delete the trigger using the same open source tool mentioned above. Use the Database Structure tab. Scroll to find the trigger, and delete it using the context menu. ... Someone is going to warn against this action. I have been doing it for many years, and haven't been burnt yet.
I don't ever bother to recreate the deleted triggers.
But you could do so after you have made all your updates.
in MM5 use the "rebuild database" option Files>ManageDatabase. This option is non-destructive to your database. It will recreate the missing trigger.
The rebuild fulltext search option at the same place will push your integers into into the indices, but I don't imagine you would want that.
More information in
this thread, including location of download from MM web site
Make sure that you download the 32 bit version of DB Browser for Sqlite, not the 64 bit version.
MM only make a 32 bit version available, which is really annoying.