[odb-users] WITH Queries in Views

[Brian Coggins]

Hey Boris,

I’m provisioning a new machine, and was hoping to install on it the 2.5.0-b.28.20240731125623.c6da3d9c4e9f version that you put together last summer (see below), but it does not seem to be live on the server any more: using queue.stage.build2.org/1/alpha<http://queue.stage.build2.org/1/alpha>, build2 downloads 2.5.0-b.27.  Any chance you could put the updated version back up?  Or if it has moved, could I get a new URL?

Thanks,
Brian


On 31 Jul 2024, at 09:17, Boris Kolpackov <boris at codesynthesis.com> wrote:

Brian Coggins <becoggins at hotmail.com> writes:

Yes, it tries to guess whether it's a complete query or the WHERE
clause by checking if the query starts with one of the known keywords
(like SELECT). I have a TODO item to recognize some additional database-
specific keywords (like WITH).

I’ve run into this limitation again, this time in the context of a
WITH RECURSIVE … query that I don’t think can be accomplished any
other way.  I upgraded to ODB 2.5.0-b.27 but it seems the issue is
still there.

Any chance we might be able to get a patch to loosen up these restrictions?

Ok, I went ahead and added WITH as a recognized SELECT prefix (I see it's
also supported by SQLite). Plus I've added the /*SELECT*/ prefix as a
hint that what follows is a SELECT-like query (this is similar to the
/*CALL*/ hint we use to distinguish procedure calls in PostgreSQL, which
uses SELECT for everything).

If you want to give it a try, I've published the snapshot packages here:

 https://queue.stage.build2.org

Simply replace:

 https://pkg.cppget.org/1/beta

with:

 https://queue.stage.build2.org/1/alpha

in the bpkg commands when building ODB.

Let me know if there any issues.