unknown tokenizer

To discuss development of addons / skins / customization of MediaMonkey.

Moderators: jiri, drakinite, Addon Administrators

Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

unknown tokenizer

Post by Barry4679 »

Hi Petr,

have you any update for this info. I cannot find SQLite3MMExt.dll in MM5.

I have an external application which updates some numeric data into the MM database. I have been doing this to MM3 & MM4 for many years. In MM4 I was storing my (music-related) data in couple of unused (video-related) columns.

It looks like the CustomX columns in MM5 have been enhanced so that numeric data is now sorted correctly (ie.1,2,10 and not 1,10,2). So I was wanting to change my app to use the Customx fields.

I attach the MM5 database to my application's Sqlite db, via SQL Attach. My problem is that when I try to update a CustomX column in the MM database from Python I get this error; class 'sqlite3.OperationalError'>; unknown tokenizer: mm ... where mm is the alias for the attached MM database.

The error is coming from your insert trigger for the Customx columns. I know this because I can make the error go away by deleting the update trigger for the column that I am updating.

My question is would SQLite3MMExt.dll address my problem, or have you any updated advice about how to update a MM5.db column that is covered by FTS? Python-orientated advice would be appreciated. ... I can delete the triggers, but they would come back ManageDatabase|Rebuild, etc.

Also a Request: it would be good if Tools|Options|Library|Fields had a checkbox, for each of the CustomX columns, which controlled whether or not the column was covered by the FTS facility. Not every use of a CustomX column is a candidate for Full Text Search. And also an update of a FTS column introduces the above complexity, and it would be good to avoid this. Up until now I have "borrowed" the BPM column, because while it is text based, it is not covered by FTS. ... It would have been best if there was the better option of just using a CustomX column.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 518
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: unknown tokenizer

Post by drakinite »

One way you can communicate with the MM UI in Python is via Chromium devtools protocol, which TIV73 laid out very helpfully over here: viewtopic.php?p=447737#p447737

Here is some python code I threw together that sets the currently-playing item's custom1 to "My Custom 1".
I'm sure the websocket code can be improved for better performance, but I just took a "getting started" example from a website and ran with it. Let me know if you come up with something more robust. :slight_smile:

Code: Select all

import asyncio
import websockets
import requests

def getMainWindowWebsocketURL():
	# Send web request to get the Chromium devtools pages
	devtoolsInfo = requests.get('http://localhost:9222/json').json()
	
	# Search through the items and get the one titled mainwindow.html
	url = None
	for item in devtoolsInfo:
		if item["title"] == "mainwindow.html":
			url = item["webSocketDebuggerUrl"]
	# Error check
	if not url:
		print("Error: Could not find the webSocketDebuggerUrl for the Chromium devtools")
	else:
		print("webSocketDebuggerUrl: " + url)
	return url


def compileRequest(code):
	
	request = '{"id":1,"method":"Runtime.evaluate","params":{"expression":"' + code + '"}}'
	
	return request

async def setNowPlayingCustom1(uri):
	async with websockets.connect(uri) as websocket:
		
		request = compileRequest("var track = player.getCurrentTrack(); track.custom1 = 'My Custom 1'; track.commitAsync();")
		await websocket.send(request)
		
		output = await websocket.recv()
		print(output)
		
websocketURL = getMainWindowWebsocketURL()
asyncio.get_event_loop().run_until_complete(setNowPlayingCustom1(websocketURL))
I'm not an expert on sqlite3, but I don't know if it's a good idea to directly access the MM database while MM is running; so I think it would be better to tell MM itself what to do, and let it manage the database.

An alternative to the track.commitAsync() method is to use methods from app.db to directly execute SQL queries: https://www.mediamonkey.com/docs/api/classes/DB.html
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
PetrCBR
Posts: 1742
Joined: Tue Mar 07, 2006 5:31 pm
Location: Czech
Contact:

Re: unknown tokenizer

Post by PetrCBR »

Hi Barry.

As i know old SQLite3MMExt.dll extension library will not work with latest SQLite used by MM5.
How to make a debuglog - step 4b: viewtopic.php?f=30&t=86643
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

PetrCBR wrote: Wed Apr 28, 2021 3:52 pm As i know old SQLite3MMExt.dll extension library will not work with latest SQLite used by MM5.
thanks. I guessed that since you didn't ship it with MM5.

What is your updated answer to that thread, ie. how to get past the ""unknown tokenizer: mm" error, without doing something nuclear like deleting the SQL trigger?

In the past I used cols not covered by FTS like BPM (for int affinity) and SeasonNumber (for text affinity) ... but with MM5 I was going to try to migrate to the CustomX columns that you provide for this purpose. ... Is it possible?


drakinite wrote: Wed Apr 28, 2021 3:42 pm Here is some python code I threw together that sets the currently-playing item's custom1 to "My Custom 1".
I'm sure the websocket code can be improved for better performance, but I just took a "getting started" example from a website and ran with it. Let me know if you come up with something more robust. :slight_smile:
Thanks for the Python snippet Drakinite. I will have a play with it.

drakinite wrote: Wed Apr 28, 2021 3:42 pm I'm not an expert on sqlite3, but I don't know if it's a good idea to directly access the MM database while MM is running; so I think it would be better to tell MM itself what to do, and let it manage the database.
Sqlite is designed to be OK for a moderate level of concurrency.
I have been doing this, without consequence, for many years.
I don't want pass the updates to MM because I have no guarantee that it is running at the time, and in many cases it won't be.

drakinite wrote: Wed Apr 28, 2021 3:42 pm An alternative to the track.commitAsync() method is to use methods from app.db to directly execute SQL queries: https://www.mediamonkey.com/docs/api/classes/DB.html
Is that the COM interface? I have also used that in the past. I just used to it for single row inserts, where I passed it all the values that I wanted to store in MM.db.

In this case I want to run a multi-database update query, to update a couple of the CustomX columns in MM5.db, for many, and maybe all, rows.

Would I be able to ATTACH my db to MM5 and then pass executeQueryAsync an update query with references to both dbs?
I am guessing not.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 518
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: unknown tokenizer

Post by drakinite »

Barry4679 wrote: Wed Apr 28, 2021 10:40 pm Is that the COM interface? I have also used that in the past. I just used to it for single row inserts, where I passed it all the values that I wanted to store in MM.db.
Nope - This is passing JavaScript through the Chromium debugger protocol to be run by MM.
Barry4679 wrote: Wed Apr 28, 2021 10:40 pm In this case I want to run a multi-database update query, to update a couple of the CustomX columns in MM5.db, for many, and maybe all, rows.

Would I be able to ATTACH my db to MM5 and then pass executeQueryAsync an update query with references to both dbs?
I am guessing not.
Ah, I see. I didn't know that was what your plan was. I don't think the executeQueryAsync method will work on a separate database, as MediaMonkey specifically works with the one MM5.DB file. It may be still possible to use another SQLite library to run your queries, if the old method does not work.
Barry4679 wrote: Wed Apr 28, 2021 10:40 pm Sqlite is designed to be OK for a moderate level of concurrency.
I have been doing this, without consequence, for many years.
I don't want pass the updates to MM because I have no guarantee that it is running at the time, and in many cases it won't be.
Ok, I stand corrected.
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

drakinite wrote: Wed Apr 28, 2021 11:07 pm Ah, I see. I didn't know that was what your plan was. I don't think the executeQueryAsync method will work on a separate database, as MediaMonkey specifically works with the one MM5.DB file. It may be still possible to use another SQLite library to run your queries, if the old method does not work.
Sorry, I didn't explain the background.
I am updating the MM5.db using values in my own Sqllite database.

Essentially I have outsourced collection and sanitisation of MM play history collection to something that can see what my Sonos is playing (even when my PC is off), can determine that it me who is listening, and can also see what I play when away from home ... something which is also album-ware, ie, creates album play counts and last album play date, for use as selection criteria in my MM playlists.

I want to use the Customx fields, and was hoping that I didn't have to hack things by deleting MM SQL triggers to achieve this.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
PetrCBR
Posts: 1742
Joined: Tue Mar 07, 2006 5:31 pm
Location: Czech
Contact:

Re: unknown tokenizer

Post by PetrCBR »

Barry4679 wrote: Wed Apr 28, 2021 10:40 pm Is that the COM interface? I have also used that in the past. I just used to it for single row inserts, where I passed it all the values that I wanted to store in MM.db.
You can run JS command (like app.db.executeQueryAsync) using SDB.runJSCode.
How to make a debuglog - step 4b: viewtopic.php?f=30&t=86643
Peke
Posts: 14599
Joined: Tue Jun 10, 2003 7:21 pm
Location: Serbia
Contact:

Re: unknown tokenizer

Post by Peke »

Barry4679 wrote: Wed Apr 28, 2021 10:40 pm What is your updated answer to that thread, ie. how to get past the ""unknown tokenizer: mm" error, without doing something nuclear like deleting the SQL trigger?
There is no workarround regarding tokenizer, it is custom full text search index service directly hard coded into SQLite3, so only way to have it back correctly is that after you change DB Values use MM Manage Database -> Reindex full text search or provide specific custom SQLite3 Engine to the app developers so that they can use it on MM5.DB. Data changed using SQLiteSpy. Let me suggest that you delete SongsText Table on backupped DB, do the change and load it into MM to create new full text Search index.

NOTE: SQLite3MM.DLL includes many changes in SQLite Engine to support various types SQLite DBs in different devices and it is built to be used with MM not third party apps. SQLite3MMExt.dll contain changes for third party apps to use MM iUNICODE.
Best regards,
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
How to add SCREENSHOTS to forum
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

Hi Peke, thanks for trying to help me with this.
Peke wrote: Thu Apr 29, 2021 6:39 am There is no workarround regarding tokenizer, it is custom full text search index service directly hard coded into SQLite3, so only way to have it back correctly is that after you change DB Values use MM Manage Database -> Reindex full text search or provide specific custom SQLite3 Engine to the app developers so that they can use it on MM5.DB. Data changed using SQLiteSpy.
I don't have a problem with Full Text Search.

A better description of my problem is that I cannot run any update query to a text column in the MM database.

A simple test query is:
update songs set Custom9 = 'b' where id = (select min(id) from songs);

If I try and run the query from a SQL browser the error is "no such collation sequence: IUNICODE"

In my app I have worked around this by registering a sql collation with the name IUNICODE. But if I run the test query from inside my app, it gets a little further, but then fails with "sqlite3.OperationalError: unknown tokenizer: mm" ... a forum search shows that this problem has also been existing years now

And if I try and running the query from inside MM5, using the MM SQL Editor addon, the query hangs hangs, and the db is not updated (?!)

But if I delete your SQL trigger for the Custom9 column, there is no error, and the database is updated as expected. This fixes the problem in my App, and also when using an external SQl browser.

But this is not a good workaround because MM5 will restore the SQL trigger the next time the MM5 database is rebuilt using your File|ManageDatabase facility

This is the same set of problems that people have had with the MM database since forever.
Why didn't you make them go away with MM5? The problems turn off community enthusiasm for creating MM add-ons.

There was a proposal from zvezdan in 2014, which you supported btw. He proposed that you ship a SQL extension library with MM, which people could load into their SQL browser, or their application. ... From the Mantis thread that I have just quoted, it looks like Petr made something for MM4, but he is not answering questions about it any more?

The MM database now has 10 customx columns for us to use, which is great,, but we can't easily update them from outside of MM5 (?!) There have been many queries about this over the years, but there is no fix in MM5, and there is no Developer resource showing how to work around the problem ... seems all very disappointing to me.

In my 1st post in this thread I suggested that MM5 config include a separate checkbox for each of the 10 Customx columns, where user could supress the associated Customx column from participating in Full Text Search ... ie. MM5 would not generate a SQL trigger for that column. .... That would at least remove the IUNICODE issue for those column, but you would still need to also address the "unknown tokenizer: mm" error that is downstream from that initial error.

Peke wrote: Thu Apr 29, 2021 6:39 amSQLite3MMExt.dll contain changes for third party apps to use MM iUNICODE.
Where is SQLite3MMExt.dll? I can't find it. That was my initial query. Petr said that the old one does not work anymore.

[UPDATE] I retested the MM Sql Editor again before reporting the problem in the Add On forum.
The query now runs w/o problem, and it does update the database OK.

I think that I must have had the MM5.db locked by the SQl Editor that was using to verify the results of the MM Sql Editor addon.

I can replicate the issue.
I use the Open Source SQL Editor called DB Browser for SQLite.
I can lock the MM db by an update like deleting of it's SQL Triggers.

This causes the Add On to seem to lock.
It has a very long timeout before it issues an error message. ... 2 minutes and 50 seconds ... it should be reduced.
PetrCBR
Posts: 1742
Joined: Tue Mar 07, 2006 5:31 pm
Location: Czech
Contact:

Re: unknown tokenizer

Post by PetrCBR »

SQLite3MMExt.dll library you can download directly from mantis thread you've mentioned.
How to make a debuglog - step 4b: viewtopic.php?f=30&t=86643
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

PetrCBR wrote: Sat May 01, 2021 2:41 am SQLite3MMExt.dll library you can download directly from mantis thread you've mentioned.
Petr, I am getting no assistance from your short cryptic responses.

The dll was posted to that Mantis thread in 2014.

And you have already told me this
PetrCBR wrote: Wed Apr 28, 2021 3:52 pm Hi Barry.
As i know old SQLite3MMExt.dll extension library will not work with latest SQLite used by MM5.
I don't understand.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
PetrCBR
Posts: 1742
Joined: Tue Mar 07, 2006 5:31 pm
Location: Czech
Contact:

Re: unknown tokenizer

Post by PetrCBR »

IUNICODE collation and some other methods are defined in SQLite3MMExt.dll, but MM tokenizer not .... so for this reason i wrote it didn't fix your issue (you wrote you do not know where you can get this extension library so for this reason i wrote it's in mantis entry you've mentioned).
MM tokenizer is defined directly in SQLite3MM.dll we're using (you can try to use this library instead of 'original' sqlite3.dll library used by your SQLite app).
How to make a debuglog - step 4b: viewtopic.php?f=30&t=86643
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

PetrCBR wrote: Sat May 01, 2021 3:53 pm IUNICODE collation and some other methods are defined in SQLite3MMExt.dll, but MM tokenizer not .... so for this reason i wrote it didn't fix your issue (you wrote you do not know where you can get this extension library so for this reason i wrote it's in mantis entry you've mentioned).
MM tokenizer is defined directly in SQLite3MM.dll we're using (you can try to use this library instead of 'original' sqlite3.dll library used by your SQLite app).
Thanks, I can understand that explanation..

I had already tried your SQLite3MMExt.dll, extension. ... It would not load "Errror loading extension. The specified module could not be found." . this with this open source SQLITE Browser

Today I have found that is because yours is a 32 bit extension. Can you make a 64 bit version?

Your extension works with the 32 bit version of the browser mentioned above. ... It is good to finally have a good browser for for MM db, without having to screw around with IUNICODE workarounds ... thanks.

As you say it does not get around the tokinizer problem for updates to text columns covered by FTS. Do you have a 64 bit version of SQLite3MM.dll? My app is 64 bit, and it is much harder for me to downgrade that to 64 bit. ... I do have a current workaround by deleting your SQL trigger for the Customx columns that I want to work with. ... Although this workaround is of limited use, because other people many want to update other columns, where trigger removal is not an option.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
ZvezdanD
Posts: 3165
Joined: Thu Jun 08, 2006 7:40 pm

Re: unknown tokenizer

Post by ZvezdanD »

PetrCBR wrote: Sat May 01, 2021 3:53 pm MM tokenizer is defined directly in SQLite3MM.dll we're using (you can try to use this library instead of 'original' sqlite3.dll library used by your SQLite app).
As I already wrote in Mantis 8 years ago:
"When I copied SQLite3MM.dll from MM folder to the SQLite Expert folder and renamed it to sqlite3.dll, when I started SQLite Expert I got the message:
Could not load SQLite library: sqlite3.dll
and it automatically switched to the internal engine."

I just tried the same with the mentioned DB Browser for SQLite 3.12.1 32-bit and I got: Entry Point Not Found - The procedure entry point sqlite3_create_function_v2 could not be located in the dynamic link library sqlite3.dll.

I tried that with SQLite3MM.dll from several MM versions including MM4 and MM5.

And here is one question that was left in another Mantis thread unanswered:
"Is it possible to implement your collations and additional functions and Unicode support, and whatever else you added to SQLite engine, as one or more external .dll extensions and keep the basic SQLite file intact? In that case, we could download it directly from the SQLite site, whatever version we need, even the latest one, and put it in the MediaMonkey folder."

In another words, could you move your tokenizer and all other MM related stuff to the same SQLite3MMExt.dll file, which could be loaded as extension in MM and other SQLite applications that support loading of extensions?

You know, in my opinion this whole mess with unknown tokenizer and unrecognized collations and poorly supported Unicode is just plainly ridiculous from the SQLite authors. I doubt that any else database format has such elementary problems.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4.1  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.3  Add Currently Playing/Selected Track(s) to Playlist 1.2
Barry4679
Posts: 1664
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

ZvezdanD wrote: Sun May 02, 2021 2:38 am In another words, could you move your tokenizer and all other MM related stuff to the same SQLite3MMExt.dll file, which could be loaded as extension in MM and other SQLite applications that support loading of extensions?

You know, in my opinion this whole mess with unknown tokenizer and unrecognized collations and poorly supported Unicode is just plainly ridiculous from the SQLite authors. I doubt that any else database format has such elementary problems.
I agree with these two points, although I doubt that the problem is with Sqlite itself. I expect that the MM implementation is the problem.

I also think that Ventis would do everybody, and themselves, a big favour if they authored a KB entry to cover usage of the MM database by external add-on applications. ... The current situation is a massive time sink for anyone trying to get started. ... This is evidenced by the many unanswered queries over the years. And I believe that the problem is larger than that, because any Community member that was considering a project would see all those posts, and likely silently cancel their project before even starting it.
ZvezdanD wrote: Sun May 02, 2021 2:38 am I just tried the same with the mentioned DB Browser for SQLite 3.12.1 32-bit and I got: Entry Point Not Found - The procedure entry point sqlite3_create_function_v2 could not be located in the dynamic link library sqlite3.dll.
It works fine for me .... but I have only tried a couple of basic queries. What was your query? ... I could try it here.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Post Reply