DuckDB ODBC connection

[MJFShark]

Posting this in case anyone looks for it (including myself once I forget

for a "stored" DuckDB database:

Use connection string like:
Driver=DuckDB Driver;Database=C:\Dev\Testing\duckdb\myduck.db

This property must be set:
enhanced_column_info=false

Btw the docs mention that the DuckDB native driver is modeled after SQLite's driver. I wonder how hard it would be to add a native DuckDB driver to Zeoslib. Note that I don't know how popular DuckDB is, so that should be taken into account.

[marsupilami]

Hello Mark,

Btw the docs mention that the DuckDB native driver is modeled after SQLite's driver. I wonder how hard it would be to add a native DuckDB driver to Zeoslib. Note that I don't know how popular DuckDB is, so that should be taken into account.

Egonhugeist once told me that he thinks writing a new driver probably would take about 4 weeks for him. The hardest part usually is doing metadata information queries for system tables and the like.
I have to admit, that I don't like SQLite very much because it isn't type safe. After a quick look at the data types of DuckDB, I saw that the varchar type isn't limited in size -> to be fully compatible, we would have to either map all varchar fields to memo columns or introduce a limit of our own.
I tried to play with the varchar type and checked the metadata information. The documentation lists 13 columns. When querieng the columns view in the information_schema, the duckdb tells me it has 45 columns...

So... writing a preliminary driver shouldn't be too hard but still requires time. The lack in type safety and the lack in documentation will require (hard) decisions that probably will limit the usability of the driver. Getting this under control will require more time later on.

If I have to do a suggestion for an embedded database: Use Firebird. It is much better documented and supported.

[marsupilami]

Doing a simplistic driver seems to be not too hard. I started on a header translation...

[MJFShark]

Thanks for that info! I totally agree on Firebird. I use it for all my internal projects. The DuckDB thing is just because it was being used at a customer site. I've heard some buzz on it, but I don't know how popular it is at all. I'm happy to help with testing or developing btw.

-Mark

[marsupilami]

Hello Mark,

the buzz seemes to be about DuckDB being an easy to use and implement column store, suited for use in OLAP scenarios where data analysis is the important part and not transaction processing.

I uploaded a first version of a DuckDB plain driver. Unfortunately it currently depends on the FPC ctypes unit. I am not sure, if we have a similar unit in Zeos. I didn't do a full translation of the header but functions for connecting, querying and using results are there.

The next steps are:

• make the plain driver compile with Delphi too. -> This could be postponed if you can develop with FPC/Lazarus too
• create a dbc layer driver
• create a statement and implement parameter binding
• implement a result set.
• get the test suite running on DuckDB
• implement the metadata objects - maybe.... I really dislike this part, but think it is an essential capability of Zeos.

For the resultset I intend to use the the "Safe Fetch Functions" first. They seem to be easy to use but are slow and deprecated. Maybe later on I will write a second resultset that uses duckdb_result_get_chunk, although this function is deprecated too. Currently I see no way to read a result without using deprecated functions...

I am not sure where on this you want to step in? Or do you have other ideas on what to do? I also intend to base the development of the statement object on the TZAbstractBeginnerPreparedStatement. Although Egonhugeist dislikes it, it should make development faster but the result will be slower than other drivers.

You see - my approach is to get to results as fast as possible, but by sacrificing speed / performance for that.

Which is the next question: What kind of result sets do you expect to use? Just some rows or thousands of rows that need to be processes as fast as possible?

Please let me know what you think.

Best regards,

Jan

[aehimself]

I don't know how popular DuckDB is

To be completely honest this is the first time I hear about it :)
After RabbitMQ, DuckDuckGo and so on it seems having an animal in product names is trendy lately.

Edit: I'd be careful with a native driver approach, because

DuckDB offers a flexible extension mechanism that allows defining new data types

If it means what I think it is, I can see this opening a huge can of worms in the near future.

[marsupilami]

Sooo - I added a first implementation for a DBC layer connection. Currently it is possible, to connect using a TZConnection object and to use TZConnection.ExecuteDirect. The driver only uses UTF8 because I had the impression that DuckDB is UTF8 only - I couldn't find any documentation about that.

Creating statements, result sets or callable statements is not supported yet and so no usage of TZQuery or TZReadOnlyQuery or any other component is possible.
Also transaction support or changing the default schema is not supported and has to be implemented.

Currently if one wishes to test DuckDB, the ZPlainDuckDB and ZDbcDuckDB units will have to be added manually to the project since the packages don't include them yet.

Edit: I'd be careful with a native driver approach, because

DuckDB offers a flexible extension mechanism that allows defining new data types

If it means what I think it is, I can see this opening a huge can of worms in the near future.

Yes - there are some pitfalls there. But we have the same situation for PostgreSQL. I simply don't plan to support any types that we cannot handle. They have a hugeint type which effectively is Int128 -> I don't know how to support this. It could be converted to a double though.
And their varchar type cannot be restricted in length. So all their varchar fields will be mapped to TMemo for now.

[marsupilami]

Today I added a first go at a statement. Since we don't have a result set, it's use is very limited. The DuckDB connection doesn't return this statement yet but still generates an error.
Also I added a Wiki page for the driver, so there will be a list of todo's and shortcomings as well as ideas for resolving these shortcomings: https://sourceforge.net/p/zeoslib/wiki/DuckDB/

Note: Development speed will slow down considerably from now on. Until today we had hoildays and I was able to spend a lot of time on this. Starting tomorrow I will have to work again