Search code examples
javasqliteapache-cayenne

How to create custom SQLite function and execute under Cayenne

I'm trying to write a custom SQLite function that takes two args and run with Apache Cayenne's performQuery() which looks something like:

String query = "SELECT * FROM TABLE WHERE MYFUNC(arg1, arg2) <= SOME_VALUE"

ObjectContext ctx = ...
ctx.performQuery(query)

I tried using org.sqlite.Function.create(), but it failed since my connection is wrapped by Cayenne.

Solution

  • My understanding is that whatever method you are using for registering a function, it only happens for a single connection. In Cayenne you are normally working with a connection pool with multiple connections. Moreover those can be closed and reopened transparently to the rest of the app. You need to take this into account.

    In other words you need to make sure each connection in the pool gets this function registered. A quick and dirty way might be to limit the pool to just a single connection. A more advanced and cleaner solution is to implement your own DataSourceFactory and load it into Cayenne runtime on startup via a custom module. An implementation would wrap methods that create new connections, adding custom function to each one of them.