More docs on custom functions

[kentcdodds]

The current example of custom functions is a simple add2 function which only interacts with the arguments given. Could we get more realistic examples that actually operate on data from the database?

Specifically, I'm looking for a way to order by lat/long distance (a la https://stackoverflow.com/questions/3168904/sql-query-to-query-nearby-points-of-interest-based-on-lat-long-sqlite).

I'm also curious of the performance implications of having the function written in JavaScript rather than C.

[Prinzhorn]

The current example of custom functions is a simple add2 function which only interacts with the arguments given. Could we get more realistic examples that actually operate on data from the database?

Not sure I understand the problem. The distance function from the SO post also "only interacts with the arguments given". You can use the add2 function like this SELECT * FROM Locations ORDER BY add2(Latitude, Longitude) too. So creating a distance function is no different from add2 in the docs, but it takes four instead of two arguments.

I'm also curious of the performance implications of having the function written in JavaScript rather than C.

My gut feeling says that when you're doing non-indexed sorting that the time SQLite spends actually sorting the rows (including IO to read them) is at least an order of magnitude larger than the execution time of the comparator function (no matter the language it's written in). But I don't have any benchmarks to back that up, maybe you can benchmark your specific use-case and share the results?

[kentcdodds]

Hi @Prinzhorn,

Thanks for taking the time to respond. I'm sorry for my confusion. That makes sense. I guess it would be handy to have something in the docs that demonstrates using the function with data from the database rather than hard-coded args in the query.

And thank you for the tip on perf challenges. If I do end up trying both and comparing, I will for sure report back on that (not sure I'll have time to try them both since C scares me 😅).

[Prinzhorn]

I guess it would be handy to have something in the docs that demonstrates using the function with data from the database rather than hard-coded args in the query.

Fair point, it is somewhat confusing to newcomers that the docs basically use the function in SQLite for a round-trip back to JS without doing anything with the value on SQLite's end. Maybe something like a distance function could be added as an example.

not sure I'll have time to try them both since C scares me sweat_smile

It do be like that :smile:

[JoshuaWise]

That makes sense. I guess it would be handy to have something in the docs that demonstrates using the function with data from the database rather than hard-coded args in the query.

A valid suggestion. Thanks!

[punkish]

a little bit more on this request (which I also vote in support of). Consider (my attempt below to write) the function below that would create a highlighted snippet from the contents of a column, except it doesn't work

db.function('snippet', (q, cssClass = 'hilite', buffers = 30) => `'…' || 
Substring(a.fulltext, (instr(a.fulltext, @q) - @buffers), @buffers) || 
'<span class="hilite">@q</span>' || 
Substring(a.fulltext, (instr(a.fulltext, @q) + Length(@q)), @buffers) || 
'…' AS snippet`);

const sql = `SELECT a.id, a.tid, snippet(@q) 
FROM a JOIN ftsa ON a.id = ftsa.rowid
WHERE ftsa.fulltext MATCH @q 
LIMIT 10`;

const res = db.prepare(sql2).all({ q: 'agosti' });
console.log(res);

I want to be able to pass q and optionally cssClass and buffers, to return something like so

[
    {
        id: 65234,
        tid: '0382776A4020FFD9CB25FC368E1EED74',
        snippet: '…al. 2011; ARAKELIAN 1994).<span class="hilite">agosti</span>togaster luctans FOREL, 1907…'
    },
]

Instead, I get the following

[
    {
        'snippet(@q)': "'…' || \n" +
          'Substring(a.fulltext, (instr(a.fulltext, @q) - @buffers), @buffers) || \n' +
          `'<span class="@cssClass">@q</span>' || \n` +
          'Substring(a.fulltext, (instr(a.fulltext, @q) + Length(@q)), @buffers) || \n' +
          "'…' AS snippet"
    },
]

What am I doing wrong, or rather, not understanding correctly?

[Prinzhorn]

What am I doing wrong, or rather, not understanding correctly?

User-defined functions in SQLite are completely different from something like stored procedures in other database engines.

The user-defined functions are pure functions. They receive arguments and return a value, with no side-effects. You are returning a string from them expecting it to be evaluated as SQL in the context of your query. You expect it to work like a macro. That's not happening at all. You need to pass all required arguments to the function and then return a constant value (string in your case). You need to move all the Substring and concat || logic to the JavaScript world.

Edit: I hadn't looked at your SQL at all, the snippet(@q) part doesn't make sense, I assume you wanted to pass one of the columns as arguments? snippet(@q) would be constant for every row, since @q is constant for the given execution.

[punkish]

ok, thanks for the clear explanation that what I am trying to do is not possible. It might be worth adding your explanation to the document text so user-expectations are aligned correctly. :)

[punkish]

Edit: I hadn't looked at your SQL at all, the snippet(@q) part doesn't make sense, I assume you wanted to pass one of the columns as arguments? snippet(@q) would be constant for every row, since @q is constant for the given execution.

The snippet(@q) part is just the label (the key in the returned object) that better-sqlite3 adds to the result. Yes, @q is constant for every row just as cssClass and buffers are, much like the args 12 and 4 are in the documentation of add2(). I provide the args and the query returns the snippet for every row. At least, that is what I was (wrongly) expecting.

[punkish]

I was approaching this all wrong. If I make my function completely in JavaScript, it does work (within limits). Here is an example:

const db = new Database('./test.sqlite');
db.prepare('CREATE TABLE IF NOT EXISTS t (a)').run();
db.prepare("INSERT INTO t VALUES ('something more')").run();
db.prepare("INSERT INTO t VALUES ('one, two and three')").run();
db.prepare("INSERT INTO t VALUES ('this and that and what else')").run();

const params = { q1: 'two', q2: '%two%' };
let res = db.prepare("SELECT a FROM t WHERE a LIKE @q2").all(params);
console.log(res);

// [
//     { a: 'one,two,three' },
//     { a: 'one,two,three' },
//     { a: 'one, two and three' },
//     { a: 'one, two and three' }
// ]

db.function('snippet', (str, cssClass, substr) => str.replace(substr, `<b class="${cssClass}">${substr}</b>`));

res = db.prepare("SELECT snippet(a, 'foo', @q1) AS snip FROM t WHERE a LIKE @q2").all(params);
console.log(res);

// [
//     { snip: 'one,<b class="foo">two</b>,three' },
//     { snip: 'one,<b class="foo">two</b>,three' },
//     { snip: 'one, <b class="foo">two</b> and three' },
//     { snip: 'one, <b class="foo">two</b> and three' }
// ]

The following, that is, a function with default params, doesn't work

db.function('snippet', (str, cssClass = 'hilite', substr) => str.replace(substr, `<b class="${cssClass}">${substr}</b>`));
res = db.prepare("SELECT snippet(a, @q1) AS snip FROM t WHERE a LIKE @q2").all(params);
SqliteError: unable to use function snippet in the requested context

[Prinzhorn]

db.function('snippet', {varargs: true}, (str, cssClass = 'hilite', substr) => str.replace(substr, `<b class="${cssClass}">${substr}</b>`));

https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#functionname-options-function---this

But it doesn't make sense to have a default for cssClass but not for substr, as it goes left to right.

[punkish]

ha! you are absolutely correct in your insight. No need for the varargs qualification as the following works correctly

db.function('snippet', {varargs: true}, (str, substr, cssClass = 'hilite') => str.replace(substr, `<b class="${cssClass}">${substr}</b>`));
res = db.prepare("SELECT snippet(a, @q1) AS snip FROM t WHERE a LIKE @q2").all(params);
console.log(res);
// [
//     { snip: 'one,<b class="hilite">two</b>,three' },
//     { snip: 'one,<b class="hilite">two</b>,three' },
//     { snip: 'one, <b class="hilite">two</b> and three' },
//     { snip: 'one, <b class="hilite">two</b> and three' }
//   ]

res = db.prepare("SELECT snippet(a, @q1, 'foo') AS snip FROM t WHERE a LIKE @q2").all(params);
console.log(res);
// [
//     { snip: 'one,<b class="foo">two</b>,three' },
//     { snip: 'one,<b class="foo">two</b>,three' },
//     { snip: 'one, <b class="foo">two</b> and three' },
//     { snip: 'one, <b class="foo">two</b> and three' }
//   ]

anyway, what I request is that an example like this be added to the docs as it is (in my opinion) a bit more explanatory than the one in the docs right now… it shows how to right a function that works on the SELECT col values.