User Defined Functions?

[matthewkastor]

I'm looking for a way to create user functions. In PHP's binding to SQLite3 there is a way to create functions at runtime. I'm thinking that if there were something similar in node-sqlite3 it would be awesome.

I don't want to try loading an extension because that would require me to keep tabs on versions and / or incorporating a compilation step in my modules. I'd rather just define functions in the javascript and be able to hook them into SQLite for use in queries.

http://www.sqlite.org/lang_expr.html#regexp
http://www.sqlite.org/c3ref/create_function.html

[springmeyer]

Well, loading extensions is supported now, so I would recommend using that unless you want to code up the support for js land functions yourself.

[matthewkastor]

@springmeyer That would work if I even knew how to do that. The problem is, I wouldn't even know where to begin. Mostly all I want to be able to do is use REGEXP in sql statements and I thought it would be easy to wire up the regular expression functionality that's built into javascript, for someone who knows what they're doing in the SQLite code. I don't really know what the difference would be between hard coding the regular expressions from JS or creating some generic wrapper for passing JS functions into SQLite as user defined functions. Maybe it would be easier to make the wrapper? It would be cool, I can't do it without a lot of hand holding and pointing though, which would probably just be frustrating for you.

There's no way for me to write an extension in JavaScript is there? I mean, I can write programs in JS, PHP, a little Python, MySQL, etc. I don't know C well enough to program in it though. I can read it mostly, but I never write programs in it. I'm currently under the impression that I'd need to know C in order to do anything useful with the SQLite API.

[springmeyer]

@matthewkastor - I don't know the answers here, but I understand this would be a useful feature. Perhaps you could reach out to the fellow at #93 or the node mailing list to see if someone could code it up? I'm currently maintaining node-sqlite3 but do not have bandwidth for new features.

[matthewkastor]

It looks like he is just as lost as I am. I'll post something on the mailing list and maybe someone out there will be inspired to make a pull request. :D

[matthewkastor]

kk I posted to the mailing list. The subject starts with "Are you an expert coder?". Waiting for moderator approval.

[kkaefer]

It is indeed possible to add the ability for custom callbacks, but it's non-trivial to implement because we're executing queries in another thread. We could either create a new v8 isolate and somehow transfer the callback function there (webworker style), or call back to the main thread (extremely slow).

I'm not going to work on this, but I'll be happy to review and accept pull requests adding this feature.

[matthewkastor]

Good to know. Thanks. :D

[wbyoung]

@kkaefer just in case you didn't see #448, I figured I'd comment here.

I started work on supporting custom functions. It mostly works, but I could use a little guidance and a review.

Thanks!

[alixaxel]

@kkaefer Any plans to merge #448? UDFs would be extremely useful to me as well.

[kkaefer]

@wbyoung Very good patch in #448. I'm wondering whether we can change it so that we don't have to allocate the whole slew of uv_mutex, uv_async, etc. for every single function call, and instead use a batched queue?

[wbyoung]

@kkaefer this is my first time using libuv. Would a batched queue be implemented using uv_queue_work and a baton that represents the batch? Is there any example of this style in the existing code base that would explain the idea without you having to type out all the details?

Also, any thoughts why this would fail with iojs on Linux? Your two comments may point to the reason, but I figured I'd ask.

[kkaefer]

@wbyoung You'll have to initialize the uv_async handle in the main thread. Ideally, you're just using one handle and a mutexed queue. uv_async_send() can be called from other threads as well, so you'd first lock the queue, add the item, unlock the queue, then call uv_async_send() to make sure that the queue will be processed.

Instead of uv_cond, you could also use std::future/std::promise as that's likely easier to use and handles spurious wakeups internally, and allows transferring the result data (though I'm not sure that's needed in this case).

[wbyoung]

@kkaefer I hopefully addressed these issues & left a comment in over on #448.

[wbyoung]

I was thinking about attempting @kkaefer's suggestion of a separate v8 isolate and transferring the callback there, but realized that this could introduce serious problems if the function were to access anything defined outside of its scope.

For instance, the below code looks innocent enough, but access to the required lib means there wouldn't be any way to safely transfer this function to a new isolate without accessing data from the current isolate.

var hashlib = require('some-hash-lib');
db.registerFunction('MY_HASH', function(value) {
  return hashlib.hex(value);
});

One could, however, create a new function in a separate isolate. This could be done by converting the given function to a string & adding it to the new isolate. This could either always be done or only when an option was given.

db.registerFunction('MY_HASH', function(value) {
  return require('some-hash-lib').hex(value);
}, { isolate: true });

// alternative syntax: new Function() style gets added to isolate
db.registerFunction('MY_HASH', 'value', "return require('some-hash-lib').hex(value);");

Any thoughts?

[matthewkastor]

https://github.com/matthewkastor/clone-function
On Jun 30, 2015 3:23 PM, "Whitney Young" notifications@github.com wrote:

I was thinking about attempting @kkaefer https://github.com/kkaefer's
suggestion of a separate v8 isolate and transferring the callback there,
but realized that this could introduce serious problems if the function
were to access anything defined outside of its scope.

For instance, the below code looks innocent enough, but access to the
required lib means there wouldn't be any way to safely transfer this
function to a new isolate without accessing data from the current isolate.

var hashlib = require('some-hash-lib');
db.registerFunction('MY_HASH', function(value) {
return hashlib.hex(value);
});

One could, however, create a new function in a separate isolate. This
could be done by converting the given function to a string & adding it to
the new isolate. This could either always be done or only when an option
was given.

db.registerFunction('MY_HASH', function(value) {
return require('some-hash-lib').hex(value);
}, { isolate: true });
// alternative syntax: new Function() style gets added to isolate
db.registerFunction('MY_HASH', 'value', "return require('some-hash-lib').hex(value);");

Any thoughts?

—
Reply to this email directly or view it on GitHub
#140 (comment)
.

[wbyoung]

@matthewkastor that's essentially what I was proposing, but via native code.

I hacked around on the separate v8 isolate a bit and came up with wbyoung/node-sqlite3 user-functions-isolate. One of the largest issues in creating a separate v8 isolate would be that none of node's environment exists by default in this isolate. We'd have to initialize that as well. That seems possible via node::CreateEnvironment, but seems to be making things work a little funny.

I'm hoping that one of these two approaches can eventually get merged. :)

/cc @kkaefer

[matthewkastor]

You're pretty smart XD
On Jun 30, 2015 6:16 PM, "Whitney Young" notifications@github.com wrote:

@matthewkastor https://github.com/matthewkastor that's essentially what
I was proposing, but via native code.

I hacked around on the separate v8 isolate a bit and came up with wbyoung/node-sqlite3
user-functions-isolate
https://github.com/wbyoung/node-sqlite3/tree/user-functions-isolate.
One of the largest issues in creating a separate v8 isolate would be that
none of node's environment exists by default in this isolate. We'd have to
initialize that as well. That seems possible via node::CreateEnvironment,
but seems to be making things work a little funny.

I'm hoping that one of these two approaches can eventually get merged. :)

/cc @kkaefer https://github.com/kkaefer

—
Reply to this email directly or view it on GitHub
#140 (comment)
.

[jrupik]

Could you provide an example of defining a user function in javascript? db.registerFunction does not work:
TypeError: db.registerFunction is not a function
I need a regexp function.

[ghost]

Could you provide an example of defining a user function in javascript? db.registerFunction does not work:
TypeError: db.registerFunction is not a function
I need a regexp function.

Just want to add in here since this is a 5+ year old thread that manually testing is surprisingly performant* - I'm using something like this and the latency is acceptable in my application:

• Found out the hard way that this appears to hold up the event loop (which is a bit surprising IMO, seems to defeat one of the main purposes of .each() .. )

let search_rows_regex = (pattern, callback) => {
  let matching_rows = [];
  db.each('SELECT * FROM table', [], (error, row) => {
    if (!error) {
      if (pattern.test(row.column)) {
        matching_rows.push(row);
      }
    } else throw error;
  }, () => callback(matching_rows));
};