Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Extend Database#create_function to support creating deterministic functions #551

Open
Jackenmen opened this issue Apr 7, 2023 · 0 comments

Comments

@Jackenmen
Copy link

Per Application-Defined SQL Functions documentation, custom SQL functions can be marked as deterministic by setting SQLITE_DETERMINISTIC bit. This allows such functions to be used in certain contexts where they otherwise wouldn't be able to such as with the WHERE clause of partial indexes or in generated columns (https://www.sqlite.org/c3ref/c_deterministic.html#sqlitedeterministic).

As a point of reference, the better-sqlite3 library exposes this through the options argument in its Database#function() method. One more option that it exposes through this mechanism is SQLITE_DIRECTONLY bit so it may make sense to support that as well.

j-f1 pushed a commit to actualbudget/actual that referenced this issue Apr 7, 2023
…es (#865)

Fixes #840 by creating application-defined SQL functions
(https://www.sqlite.org/appfunc.html) for Unicode-aware implementations
of `LOWER()` and `UPPER()`. This uses
`String.prototype.toLower/UpperCase()` JS method.

I initially wanted to just redefine `LOWER()` and `UPPER()` but due to
[sql.js not supporting the definition of deterministic
functions](sql-js/sql.js#551), I had to just
define them as separate functions and use that in the appropriate
places. It's probably better like that anyway...
FlorianLang06 pushed a commit to FlorianLang06/actual that referenced this issue Mar 7, 2024
…es (actualbudget#865)

Fixes actualbudget#840 by creating application-defined SQL functions
(https://www.sqlite.org/appfunc.html) for Unicode-aware implementations
of `LOWER()` and `UPPER()`. This uses
`String.prototype.toLower/UpperCase()` JS method.

I initially wanted to just redefine `LOWER()` and `UPPER()` but due to
[sql.js not supporting the definition of deterministic
functions](sql-js/sql.js#551), I had to just
define them as separate functions and use that in the appropriate
places. It's probably better like that anyway...
my2 added a commit to my2/actual that referenced this issue Aug 13, 2024
…es (#865)

Fixes #840 by creating application-defined SQL functions
(https://www.sqlite.org/appfunc.html) for Unicode-aware implementations
of `LOWER()` and `UPPER()`. This uses
`String.prototype.toLower/UpperCase()` JS method.

I initially wanted to just redefine `LOWER()` and `UPPER()` but due to
[sql.js not supporting the definition of deterministic
functions](sql-js/sql.js#551), I had to just
define them as separate functions and use that in the appropriate
places. It's probably better like that anyway...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant