Support Appender#create_query

[Arcovion]

Last week DuckDB 1.4.0 was released, with the notable addition of: duckdb/duckdb#18738
Which adds this to the C API: https://duckdb.org/docs/stable/clients/c/appender.html#duckdb_appender_create_query

Currently when appending to a table we have to 1) ensure that we're inserting all columns and not just a subset of them, 2) ensure the columns are inserted in the exact same order as that of the table schema, and that 3) "The default value of the column must be a constant value. Non-deterministic expressions like nextval('seq') or random() are not supported."
To work around these limitations I've been creating temporary tables, appending to that, and then doing the insert from the temporary table. Despite that additional layer of indirection, the appender still proved faster overall than a typical INSERT INTO table (col3, col2) VALUES ((1, 2), (3, 4)) style SQL query.

The aforementioned PR states that this new interface works like a CTE, which is conceptually similar to using a temporary table. It mentions prior limitations regarding default values and seems to address them.
This potentially makes for a much nicer interface and avoids the need to use temporary tables or other workarounds for cases when you're only inserting a subset of columns or need non-deterministic default values, plus the PR even mentions facilitating "more complex operations like upserts, merge into, delete or update" beyond mere appending.

[suketa]

@Arcovion
Thank you for your report.

I added DuckDB::Appender#create_query and DuckDB::Connection.appender_from_query.
ruby-duckdb 1.4.1.0(build with duckdb 1.4.0 or 1.4.1) provides these methods.

[Arcovion]

Excellent, it works. Thank you!

I have some ideas about improving the ergonomics:

1) It'd be nice to have a block form for these, so:

appender = conn.appender_from_query(query_sql, types, cte_name, columns)
values.each do |row|
  appender.append_row(*row)
end
appender.close

Becomes:

conn.appender_from_query(query_sql, types, cte_name, columns) do |appender|
  values.each do |row|
    appender.append_row(*row)
  end
end

Connection#appender currently works this way, accepting a block like that, so it'd match the current behaviour.

2) I wonder whether types could be auto-computed?

In my case I have access to the database schema, so I can take the type (VARCHAR/INTEGER/...) and map it like so:

types = columns.map do |column|
  ::DuckDB::LogicalType.public_send(schema.assoc(column).dig(-1, :type).downcase) }
end

This works fine but feels a bit awkward - using metaprogramming methods like send - maybe exposing a lookup method could improve it, unless one already exists that I missed? This'd make for a slightly friendlier way to build the types array programmatically.

2.1) Moreover, currently we can use append/append_row to insert Ruby types automatically...

The dispatching there is done via a hardcoded case statement within the #append method. In the future, maybe the logic could be extracted elsewhere so we can lookup the corresponding LogicalType for Ruby classes directly, without needing to know the schema beforehand?
If that's possible, I guess it unfortunately can't be baked into the create_query/from_query methods directly though, which is a shame. In theory you can imagine ways to defer computing the types array, but in practice it'd likely overcomplicate things.

My goal here then is mainly just to better facilitate programmatically building the types array, principally by making Ruby type/column type <=> DuckDB::LogicalType conversion more accessible. I can make a separate issue for that if you like.

[suketa]

1. It'd be nice to have a block form for these, so:

Oops, I released 1.4.1.1.

2. I wonder whether types could be auto-computed?
   2.1) Moreover, currently we can use append/append_row to insert Ruby types automatically...

I have not planned to implement it, but I will consider about these features. Thank you for your good suggestion!