[odb-users] SQLite3 Version (and Native View Question)

[Brian E. Coggins]

Hello Boris and friends,

By chance I discovered that the newest versions of SQLite finally support full outer joins, but the version incorporated into libodb-sqlite 2.5.0-b.21 (which I think is still current, if I’m interpreting bpkg’s output correctly…my apologies if I’m misreading it) still does not.  This leads to three questions:

1.  Can we expect a newer version of sqlite3 to be incorporated into ODB’s distribution soon?  (Or I am dumb and missing an already-existing update, perhaps by using bpkg incorrectly?)

2.  I thought about switching to the system's libsqlite3, except that seems to be a mess.  Current versions of macOS distribute a /usr/bin/sqlite3 that supports full outer joins, but the libsqlite3 in /usr/local/lib is a different and older version.  I’m guessing /usr/local/lib/libsqlite3 doesn’t come with the system…which got me wondering, does the ODB build2 process in fact generate this libsqlite3?  The dates seem to match.  If so, is that also waiting for an update in the ODB codebase?  Otherwise I am at a loss for what is generating libsqlite3, and how best to update it.

3.  Absent a sqlite3 upgrade, I thought I’d try to workaround the lack of outer join support by using a complex query of the structure WITH (xxx UNION yyy) SELECT zzz as part of my ODB native view.  The query works in the sqlite3 CLI, but the ODB compiler complains “view XXX has an incomplete query template and no associated objects.”  Is the ODB compiler trying to parse the query and getting hung up on this syntax?  Any advice?

Thanks,
Brian