support sqlite as kv backend

[darkskygit]

SQLite can provide excellent read and write performance. At the same time, compared with other kv backends, it can exist as a single file, which allows applications with data migration requirements to better save and transmit their ydoc.

I would be happy to provide a pr for this proposal.

[Horusiath]

I was thinking about it, but the question is: should it really be just a key-value overlay lib around SQLite, or maybe a native SQLite extension that introduces data type and methods capable of working from SQL? We already have such extensions for Postgres, and I think there were people working on them for SQLite as well.

[darkskygit]

I think you want to design a plugin for SQLite/PostgreSQL, which allows the database to create a ydoc using a syntax like :

create table docs (id integer primary key autoincrement, doc ydoc not null);

and update a column with ydoc using a syntax like:

update docs set doc from ydoc_merge(doc, update)

This can be achieved in SQLite through table-valued functions. When modifying the internal data, pointer passing interfaces need to be used to modify a block of contiguous memory. When returning, the database will completely overwrite the old data with the modified data.

I'm not sure if the Postgres extension you're referring to is pg_crdt. Due to similar reasons, the current implementation of pg_crdt needs to fully update the ydoc in the table every time it is updated, which violates the design of allowing ydoc to save updates in small granularities.

Therefore, I believe implementing a reading and writing a SQLite/PostgreSQL table based on yrs-persistence‘s traits may be a better choice.