search

coleifer / pysqlite3

SQLite3 DB-API 2.0 driver from Python 3, packaged separately, with improvements
zlib License
189 stars 54 forks source link

Add "session extension" feature to pysqlite3 #30

Closed sotte closed 2 years ago

sotte commented 2 years ago

sqlite offers the wonderful session extension feature:

The session extension provide a mechanism for recording changes to some or all of the rowid tables in an SQLite database, and packaging those changes into a "changeset" or "patchset" file that can later be used to apply the same set of changes to another database with the same schema and compatible starting data. A "changeset" may also be inverted and used to "undo" a session.

I was wondering if there are any plans to add this feature to pysqlite3? I guess it would include exposing part of the session extension C-API which might be out of scope of this project.

coleifer commented 2 years ago

What is your use-case for such an extension? How would you intend to interact with it without a set of corresponding C bindings?

I'm passing for now.

sotte commented 2 years ago

Thanks for the response @coleifer, no worries.

My use case is: I have a little program that is run on a bunch of machines that don't always have internet, but I want to sync the data periodically. The session extension would be a great fit for it.

I prototyped the logic with C basically following the using the session extension section, i.e. using a small subset of the C API.

How would you intend to interact with it without a set of corresponding C bindings?

Some of the functions, probably not all, would have to be exposed. The functions used in the little demo are: sqlite3session_create, sqlite3session_attach, sqlite3session_changeset and sqlite3changeset_apply.

coleifer commented 2 years ago

In the past I've written special-purpose sqlite extensions as standalone c extensions - it's fairly easy, especially with Cython. I imagine you could compile the session extension as a loadable module(?) and write a small c extension to expose the session functionality. You can grab the sqlite3* off a pysqlite3 connection fairly easliy:

sotte commented 2 years ago

Yes, I was thinking something along those lines, using CFFI not Cython, but the pewee example is quite nice and I can probably steal some relevant parts. Thanks for providing the links.

(By the way: also thanks for pewee, I've used it in the past.)