unknown tokenizer

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

Moderators: jiri, drakinite, Addon Administrators

ZvezdanD
Posts: 3257
Joined: Thu Jun 08, 2006 7:40 pm

Re: unknown tokenizer

Post by ZvezdanD »

Barry4679 wrote: Sun May 02, 2021 3:20 am It works fine for me
What works for you? Did you try Petr suggestion, which is not working as I explained before:
"I copied SQLite3MM.dll from MM folder to the SQLite Expert folder and renamed it to sqlite3.dll".

Notice that I am talking about SQLite3MM.dll here, not SQLite3MMExt.dll.
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
PetrCBR
Posts: 1763
Joined: Tue Mar 07, 2006 5:31 pm
Location: Czech
Contact:

Re: unknown tokenizer

Post by PetrCBR »

Check what SQLite version they originally using as our SQLite3MM.dll is standard sqlite library only with our tokenizer (we're running on 3.23.1).
How to make a debuglog - step 4b: viewtopic.php?f=30&t=86643
Barry4679
Posts: 2398
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

ZvezdanD wrote: Sun May 02, 2021 3:40 am What works for you? Did you try Petr suggestion, which is not working as I explained before:
"I copied SQLite3MM.dll from MM folder to the SQLite Expert folder and renamed it to sqlite3.dll".

Notice that I am talking about SQLite3MM.dll here, not SQLite3MMExt.dll.
OK, misunderstanding, sorry.
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.
I thought "the mentioned DB Browser for SQLite 3.12.1 32-bit" was referring to my discussion with Petr about an extension library for SQLITE browsers.

What "worked " for me was this:
  • take menu optionTools|LoadExtension. and load Petr's SQLite3MMExt.dll from 2014
  • run a query ==> no IUNICODE error
Something which also fixed the tokenizer issue would be nice, but I have a workaround which is delete the SQL insert triggers for the Customx columns that I am updating.

I didn't need to follow his other advice to get a convenient browser for MM database ... and I haven't tried using SQLite3MM.dll in my application, because I have a 64bit app atm ... I would like to try it if he can make a 64 bit version.
PetrCBR wrote: Sun May 02, 2021 3:49 am Check what SQLite version they originally using as our SQLite3MM.dll is standard sqlite library only with our tokenizer (we're running on 3.23.1).
The DB Browser is built for Sqlite version 3.33.0 (August 2020) ... 3.23.1 is quite old (April 2018).
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
ZvezdanD
Posts: 3257
Joined: Thu Jun 08, 2006 7:40 pm

Re: unknown tokenizer

Post by ZvezdanD »

PetrCBR wrote: Sun May 02, 2021 3:49 am Check what SQLite version they originally using as our SQLite3MM.dll is standard sqlite library only with our tokenizer (we're running on 3.23.1).
It would be nice if you leave the text that you are quoting because it is not obvious to whom you are replaying.

If you are replaying to me, as I said, I tried several your versions from MM4 and from MM5. For example, I tried SQLite Expert Personal 3.4.71, which has included SQlite 3.7.17, with your SQLite 3.7.6.3 from MM4. I think these two versions are close enough to not cause any trouble. Besides, I tried the same version of SQLite Expert with many original DLL files downloaded from SQLite site and it worked fine with non-MM databases.

I don't think that your SQLite3MM.dll is standard library since it is not recognized by two applications that I tried. If you state at least one SQLite application beside of MM that could work with your DLL, I will agree with you.
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
Ludek
Posts: 4947
Joined: Fri Mar 09, 2007 9:00 am

Re: unknown tokenizer

Post by Ludek »

Something which also fixed the tokenizer issue would be nice, but I have a workaround which is delete the SQL insert triggers for the Customx columns that I am updating.
Deleting SQL insert triggers is not a good practice at all -- actually can cause unpredictable issues that could lead for a need to use Manage Database > Rebuild database, the MM tokenizer is needed to insert correct values to our SongsText table (sqlite's FTS engine), it is needed for indexing the text values without the special characters for the full-text-searching to work properly.

You can use the "SQL Editor" addon https://www.mediamonkey.com/addons/brow ... ql-editor/ for executing SQL write operations
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: unknown tokenizer

Post by drakinite »

Ludek wrote: Wed May 05, 2021 9:03 am You can use the "SQL Editor" addon https://www.mediamonkey.com/addons/brow ... ql-editor/ for executing SQL write operations
I believe Barry wanted his Python program to execute SQL operations automatically.
Barry4679 wrote: Wed Apr 28, 2021 10:40 pm 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.
Barry, I just realized that if you use the COM model when MM is not running, it (should) open MM. This can solve your issue of MM not being running.
I believe it would be more beneficial to use the Chromium Devtools API to actually communicate with MM and make it create its database calls, since that will work on all platforms in the future; but if you create a SongsDB5.SDBApplication object, it (should) open MM5 automatically. The reason I'm typing "should" is because I just tested it and it's a regression; tracked as https://www.ventismedia.com/mantis/view.php?id=17823.

So after it's fixed, you can do the following to ensure MM5 is open:

Code: Select all

import win32com.client as win32
sdb = win32.Dispatch('SongsDB5.SDBApplication')
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: unknown tokenizer

Post by drakinite »

drakinite wrote: Wed May 05, 2021 11:22 am
I believe it would be more beneficial to use the Chromium Devtools API to actually communicate with MM and make it create its database calls, since that will work on all platforms in the future; but if you create a SongsDB5.SDBApplication object, it (should) open MM5 automatically. The reason I'm typing "should" is because I just tested it and it's a regression; tracked as https://www.ventismedia.com/mantis/view.php?id=17823.

So after it's fixed, you can do the following to ensure MM5 is open:

Code: Select all

import win32com.client as win32
sdb = win32.Dispatch('SongsDB5.SDBApplication')
Never mind about the not working bit. I just had an issue on my own non-portable install; instantiating SongsDB5.SDBApplication should work with opening MM5. :slight_smile:
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
Barry4679
Posts: 2398
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

drakinite wrote: Wed May 05, 2021 11:22 am Barry, I just realized that if you use the COM model when MM is not running, it (should) open MM. This can solve your issue of MM not being running.
Thanks Drakinite, I was not aware of that.

Ludek wrote: Wed May 05, 2021 9:03 am Deleting SQL insert triggers is not a good practice at all

It works, but it is not my first preference obviously.

My first preference would be that you ship a 64 bit version of your sql-related dlls. That way we could do as Petr intended.
Are you planning this, or is it off the table?

My second preference would be as I requested during the alpha of MM5 ... ie. deliver some songs.customx fields that are not covered by FTS. ... or even better, add a checkbox for each of the customx columns, into Options|Library|Fieldsm to condition whether or not the column is to be covered by FTS.

This way MM5 inter-operability will be enhanced by removing the tokenizer issue for selected customx columns. These columns have been provided for customer-specific purposes, and at the moment they are pain to update from external applications.

In my case I am using two customx columns. Every row will contain a value in both columns, and in one column it will be mostly distinct values. The data is numeric, so I will never want to search these values in FTS. Won't I reduce FTS overhead by their absence?
Ludek wrote: Wed May 05, 2021 9:03 am actually can cause unpredictable issues that could lead for a need to use Manage Database > Rebuild database, the MM tokenizer is needed to insert correct values to our SongsText table (sqlite's FTS engine), it is needed for indexing the text values without the special characters for the full-text-searching to work properly.

If I don't want a customx column covered by FTS search, then deleting the trigger is a no-harm activity isn't it. ... I think that I am better off by not having alll that dead weight is the SongsText file ... aren't I?

If will re look at the COM option after Drakinite's comment. ... Need to check overhead and responsiveness.

I need to to join the MM database to another sql database, and then run a query which updates the MM database.
I have checked, using your SQL Editor addon sandbox, that I can ATTACH and DROP my database, and that is OK. But are you sure that this is going to be a better solution than just dropping FTS support for a couple of unwanted customx columns?
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Barry4679
Posts: 2398
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

64bit version in the future?

Post by Barry4679 »

TIV73 wrote: Fri Aug 06, 2021 4:39 am In the end, it probably comes down to the question "Is it worth it?".
I do think that we need a 64bit version of the SQLite3MMExt.dll, extension, which Ventis supplies to allow 3rd party Developers to interact with the MM database.

You made the point that it may be a large job for Ventis to move from 32bit up to 64bit.
OK, but I think that it is near impossible, and highly undesirable, for a Developer to downgrade their 64bit app down to 32 bits.

Is there going to be a 64bit version of SQLite3MMExt.dll, or even better SQLite3MM.dll?
I have asked this question several times, with no answer.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Doxtur
Posts: 4
Joined: Sat Jun 04, 2011 9:40 am

Re: unknown tokenizer

Post by Doxtur »

I don't know how the MediaMonkey tokenizer works, but this wrapper allows you to make changes to text fields in the Songs table without removing triggers or other tricky manipulations.

You can do this on python 3:

1) Install the following modules:
pip install sqlitefts
pip install cffi

2) Use script:

Code: Select all

import sqlite3
import re
import sqlitefts as fts

class MMTokenizer(fts.Tokenizer):
    _p = re.compile(r'\w+', re.UNICODE)

    def tokenize(self, text):
        for m in self._p.finditer(text):
            s, e = m.span()
            t = text[s:e]
            l = len(t.encode('utf-8'))
            p = len(text[:s].encode('utf-8'))
            yield t, p, p + l


def cmp(a, b):
    return (a > b) - (a < b)
    
    
def i_unicode_collate(s1, s2):
    return cmp(s1.lower(), s2.lower())
    
    
def sqlite_getextension(a):
    return ''
    
conn = sqlite3.connect("MM.DB", check_same_thread=False)
conn.create_collation('IUNICODE', i_unicode_collate)
tk = fts.make_tokenizer_module(MMTokenizer())
fts.register_tokenizer(conn, 'mm', tk)
conn.create_function('getextension', -1, sqlite_getextension)
cursor = conn.cursor()

cursor.execute("INSERT INTO Songs (SongTitle, Artist) VALUES (?,?)", ('foo', 'bar'))
conn.commit()
Barry4679
Posts: 2398
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: unknown tokenizer

Post by Barry4679 »

Doxtur wrote: Wed Jul 13, 2022 4:35 am I don't know how the MediaMonkey tokenizer works, but this wrapper allows you to make changes to text fields in the Songs table without removing triggers or other tricky manipulations.
Thanks Doxtur, that sounds super useful. :D
I will give it a try shortly.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Post Reply