DB API 2.0

[barbieri]

This PR should wait on #7 as most of the commits will be reviewed there.

[honzasp]

I will have a closer look after we deal with #7 :)

[barbieri]

@honzasp, it's now ready to be reviewed. For the API docs, I used sphinx autodoc, so it will get the function/class docstrings.

The test suite only runs on python 3.11 since I used its code, some stuff it check is not available in older version. You can see changes to their code by downloading and diff cpython-3.11.3/Lib/test/test_sqlite3

[honzasp]

Do we have some performance numbers for this client with Turso?

[barbieri]

I didn't run any benchmarks, but I don't see why it would matter, unless you see some clear optimization to be employed. The synchronous characteristics will already add lots of latency

[honzasp]

I definitely see many applications where latency matters. If it turns out that code that switches from sqlite3 to Turso becomes 1000x slower, we will have hard time selling this to people :)

[barbieri]

I definitely see many applications where latency matters. If it turns out that code that switches from sqlite3 to Turso becomes 1000x slower, we will have hard time selling this to people :)

they exist, we can run some benchmarks, but comparing it to sqlite3 makes no sense... you'd have to use an ORM and compare the same code against mysql/postgresql... but then your have not just different transport, you have different engines.

however, as I said: what's the action on it?

• This is a synchronous API, the postgresql and mysql are also synchronous, like all of the other databases. How to improve the benchmarks by changing this code?
• The hrana protocol seems quite verbose in the wire (numbers of bytes per information), on top of another verbose protocol (websockets) on top of a protocol with known buffering/bufferbloat issues (TCP). What would be the impact of making the Hrana protocol less verbose, like 1-2 letter fields, rework the value to be split from {"type": XX, "value": YY} into a more complex solution (see more below)? (likely not that big)
• The hrana protocol doesn't support progressive results, you select and get all the results in a single response... if the benchmark is interactive, doing some operations after reading one result, then it will add a huge latency in there. To me this is the only part where we could improve latency considerably.

Value Returning

I understand sqlite is mostly typeless, but you could easily return the sqlite3_column_type() in the columns (close by decltype) and keep each value to an encoding based strictly on that, like numbers come as string to keep precisions, strings comes as unicode and blobs are base64. Alternatively you could go with always blobs (sqlite3_column_bytes() + sqlite3_column_blob()) and let clients decode. Python, for instance, whenever you require a converter, it will always do that https://github.com/python/cpython/blob/main/Modules/_sqlite/cursor.c#L357-L374

Or you could mix both, using sqlite3_column_type() unless explicitly told by the client they want the results interpreted in another type, so you could pass a map/dict with column name (using AS "name" could help) and desired type (ie: return as blobs, as strings...) using the conversion table https://www.sqlite.org/c3ref/column_blob.html

This would save lots of bandwidth sending results and working with them.

[barbieri]

@honzasp squashed all the fixups, the PR is ready to be merged. Thanks for your help 🙏