XTDB compatibility: nesting, exclude, rename

[seancorfield]

See https://www.xtdb.com/blog/dev-diary-jun-24 for details.

• :nest_one, :nest_many
• :select :exclude :rename

[seancorfield]

NEST_ONE and NEST_MANY should "just work":

user=> (sql/format '{select ((c._id customer_id), name, 
                                 ((nest_many {select ((o._id order_id), value) from ((orders o)) where (= c._id o.customer_id) order-by ((order_date desc)) limit 3}) orders)) 
                                from ((customers c))})
["SELECT c._id AS customer_id, name, 
             NEST_MANY (SELECT o._id AS order_id, value FROM orders AS o WHERE c._id = o.customer_id ORDER BY order_date DESC LIMIT ?) AS orders 
             FROM customers AS c" 3]
user=>

[seancorfield]

Optional SELECT should just work too:

user=> (sql/format '{from table where (= _id ?id)} {:params {:id 42}})
["FROM table WHERE _id = ?" 42]

[seancorfield]

Extra SQL to think about:

• RECORDS (for inserting documents)
• ERASE as a hard alias for DELETE done
• SELECT (people.info).contact[2].tel for navigation into structures (we have some of this I think)
• SETTING DEFAULT SYSTEM_TIME TO AS OF DATE '2020-01-01' (see https://docs.xtdb.com/reference/main/sql/queries.html)
• FROM people FOR SYSTEM_TIME ALL -- pretty sure this is already supported? double-check temporal filter for queries link

[seancorfield]

See tests so far https://github.com/seancorfield/honeysql/blob/develop/test/honey/sql/xtdb_test.cljc

[jarohen]

Thanks @seancorfield, looking good 😊

I don't know if it makes a difference either to users or to HoneySQL implementation, but EXCLUDE and RENAME are syntactically part of the * rather than either the SELECT or a top-level construct. Aware you may want to keep as much at the top-level as possible for ease of composition though. 🤔

[seancorfield]

Since there's already a formatter that works with "select-like" things, and both EXCLUDE and RENAME can take (a subset of) "select-like" things, this was the simplest way to implement a first cut of things.

However, now you mention that specifically, I assume things like SELECT a.* EXCLUDE a._id, b.* RENAME b._id AS the_id FROM... are possible? If so, yes, I'll need to fix that. And there is a Snowflake example of this, which I just hadn't scrolled far enough to see! https://docs.snowflake.com/en/sql-reference/sql/select#selecting-all-columns-from-multiple-tables-excluding-a-column-and-renaming-a-column (and tacking these sorts of qualifiers onto "selectable" things is substantially harder).

None of this is listed on your https://docs.xtdb.com/reference/main/sql/queries.html railroad syntax diagrams tho'?

[seancorfield]

• SELECT (people.info).contact[2].tel for navigation into structures -- James suggested a :get-in syntax:

[:get-in :people.info :contact 2 :tel] ; this is implemented

[seancorfield]

Date/time literals with actual date/time values instead of strings: [:inline :data #inst "2020"]

[seancorfield]

• RECORDS { }, { }, .. -- top-level
• { }, RECORD ( ) -- special syntax; OBJECT ( ) alias (note: parens, not braces)

[seancorfield]

I've added support for exclude/rename clauses in the "alias" position of a select item so for simple select *, you can use them at the top-level as shown above, but for selects with multiple elements, you can {:select [ [:* {:rename [:a :b]}] :c ]} and get SELECT * RENAME a AS b, c.

I've updated the tests to show this, as well as adding tests to show how the :xtdb dialect allows qualified/column names.

[seancorfield]

I've added inline map support (for clj and cljs) as a first step toward RECORD syntax (but I would assume users will mostly use parameters instead of inline?).

[seancorfield]

@jarohen Looking at the XTDB source, it looks like RECORD (_id: 1, name: 'foo') and OBJECT (_id: 1, name: 'foo') are synonyms, but there's also a literal inline syntax of {_id: 1, name: 'foo'} for a record/object -- is that correct?

[seancorfield]

get-in implemented for object navigation.

[seancorfield]

Everything except SETTING has a first pass implementation (without documentation!) so I'm going to deem this "done" so I can cut a release and get some feedback on the existing pieces. I'll also create a documentation ticket for all the XTDB stuff.

[seancorfield]

SETTING has been implemented.

[seancorfield]

I believe this is all documented as part of #556