Issue with Plex Media Server Database

[d8ahazard]

Details

So, this is 100% an issue with a custom feature added to the database used by the software "Plex Media Server", and not necessarily with SqliteStudio. I was just hoping maybe some kind of workaround could be added to fix the problem...maybe a plugin?

Basically, they've added some kind of tokenizer for faster data lookups, "collating".

Reading data from the DB works fine, but when trying to execute an UPDATE command, I get the following error:

"[14:44:30] Error while executing SQL query on database 'com.plexapp.plugins.library': unknown tokenizer: collating"

Steps to reproduce

Open a plex DB with the software, try to make an update command.

'''
UPDATE metadata_items
SET user_fields = 'lockedFields='
WHERE library_section_id = ''
'''

Operating system

Windows 11.

SQLiteStudio version

3.3.3

[pawelsalawa]

It looks like they are using fts4 extension to SQLite - which is fine for SQLiteStudio, it can also load fts4 extension. Problem is that they also use a custom tokenizer called collating within that fts4 and I cannot find anywhere in the internet a dll file with that tokenizer, so it seems they keep it private. Without it there is nothing that SQLiteStudio can do.

[d8ahazard]

plexdlls.zip

[pawelsalawa]

Unfortunately it is hard to tell whether any of these dll files provide the tokenizer.
Even worse is that even if we had dll file with the tokenizer inside, it's not that easy to load it. A provider of tokenizer must intentionally expose it for loading, at least that's how I understood this: https://www.sqlite.org/fts3.html#custom_application_defined_tokenizers
The pointer address must be known.

Generally tokenizers are being registered from the C level and it looks like there is nothing we can do.

[d8ahazard]

They also provide a separate SQLite 3 executable... But it's somehow be possible to just use this for the application? Guessing probably not...

…

On Fri, Jan 21, 2022, 4:31 PM Paweł Salawa ***@***.***> wrote: Unfortunately it is hard to tell whether any of these dll files provide the tokenizer. Even worse is that even if we had dll file with the tokenizer inside, it's not that easy to load it. A provider of tokenizer must intentionally expose it for loading, at least that's how I understood this: https://www.sqlite.org/fts3.html#custom_application_defined_tokenizers The pointer address must be known. Generally tokenizers are being registered from the C level and it looks like there is nothing we can do. — Reply to this email directly, view it on GitHub <#4382 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMO4NG3W4JHAG3FYLYULKDUXHNFJANCNFSM5MQSTTGA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.*** com>

[pawelsalawa]

No, it's not possible.

[d8ahazard]

What information would you need exactly? I can probably ask the developers...

…

On Fri, Jan 21, 2022, 5:15 PM Paweł Salawa ***@***.***> wrote: No, it's not possible. — Reply to this email directly, view it on GitHub <#4382 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMO4NGJPZKL6ETZ4NAD6F3UXHSHTANCNFSM5MQSTTGA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.*** com>

[pawelsalawa]

It's no about information. The value for fts3_tokenizer() requires runtime memory address of the tokenizer module. This means it requires C code implementation based on the source code of the tokenizer. The memory address is different each time you run the application.