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.