How to check whether SQLiteStudio is using build-in REGEXP or extension?

[orangelynx]

It's very useful that SQLiteStudio has built in support von regular expressions via REGEXP, however I feel the used Qt implementation is much slower compared to running a sqlite shell with a pcre wrapper extension loaded (on unix). I therefore compiled pcre and a wrapper extension on windows and loaded it into SQLiteStudio, which accepts it fine. However, I cannot tell if the REGEXP function is now caught by SQLiteStudio and Qt or by my custom extension. Which one takes precedence and how can i verify this?

[pawelsalawa]

REGEXP implementation in SQLite3 is done via registering regexp() custom function.
The way that SQLiteStudio initializes database upon opening is that it loads all registered extensions first - registered extensions are those added in the Extensions Manager window - this one:

Then it registers all functions (including the regexp()).

Now, according to this documentation: https://sqlite.org/appfunc.html#multiple_calls_to_sqlite3_create_function_for_the_same_function whichever function implementation is registered later - will be in use.

This means that if you load extension through the Extension Manager, you will still use SQLiteStudio REGEXP implementation. If you want to use your own - provided by extension, you will need to load the extension manually, later, using SELECT load_extension(...); call.

[tuffnatty]

@pawelsalawa Is there a reason this order can't be changed? SQLiteStudio includes the ICU extension now, and having to call SELECT load_extension with full path to the extension before I can use ICU's version of REGEXP is not very convenient.

[pawelsalawa]

Good point. The order could be changed. We should actually consider 3 groups of function providers:

• SQLiteStudio built-in (which includes this Qt-based REGEXP implementation)
• User's custom functions implemented in SQLiteStudio
• Extension-provided functions

My proposed order would be:

1. built-in functions
2. extension-provided functions
3. user-defined functions

This way built-in functions will always be overwritten by extension or user-defined functions and I think user-defined function should also overwrite extension-provided functions, as this would clearly be intension of the user who defined these.

I will create an issue to track it in a moment.

[pawelsalawa]

Created #4735

[tuffnatty]

@pawelsalawa Thanks! I agree, it seems the proper order is the one you have proposed.