Persistent user defined functions in SQLite3?

Question

Does anyone know a way to register a "persistent" UDF in SQLite 3 (using C or PHP), or something equivalent? I know I can register a UDF as a callback function with the C or PHP API, but that function only lives as long as the application's handle to the database.

In MySQL, I can achieve this with someting like "CREATE FUNCTION my_udf RETURNS INTEGER SONAME 'shared_lib_containing_my_udf.so'.

The reason why I need this is that "Application A" should be notified when "Application B" inserts, updates or deletes data, using triggers that call my UDF. The application that manipulates the DB is a PHP web application. So if I can't make a UDF persistent, I'd have to register the UDF for every web request - and if the application opens more than one connection per web request, I'd even have to register the UDF multiple times per request. This would probably mean having to change the application in many places, which I really want to avoid.

If this isn't possible, does anyone have a different solution?

Intuitively, I thought a possible solution would be to have a daemon open a connection, register the UDF and keep the connection open indefinitely. But it appears that UDFs are only associated with the current connection and can not be shared across concurrent connections. Any thoughts? — chris
– chris, Commented Jun 14, 2012 at 14:44
Well, modifying SQLite does seem to be the only available option. This won't work for me because of deployment issues, though. I wish SQLite had some kind of plugin mechanism. — chris
– chris, Commented Jun 19, 2012 at 14:14

chris · Accepted Answer · 2013-03-29 10:01:24Z

1

The only solution seems to be to modify the SQLite source to add the required functions.. This won't work in my scenario though because of deployment issues, so I'll have to abandon SQLite altogether and switch to MySQL or PostgreSQL. I wish SQLite had some kind of plugin mechanism to extend it with custom functions without recompilation, like the way UDFs are implemented in MySQL. That would probably make it considerably less "lite", though. As much as I love SQLite, it's just not the right tool for everything.

edited Mar 29, 2013 at 10:01

answered Jun 19, 2012 at 14:20

chris

2,6511 gold badge25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phiresky Over a year ago

sqlite extension solve this fairly easily. at least now, a few years later.

Collectives™ on Stack Overflow

Persistent user defined functions in SQLite3?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related