Microsoft.Data.Sqlite: Incremental blob I/O

[bricelam]

See Open A BLOB For Incremental I/O.

This would light up the GetBytes, GetChars & GetStream methods on SqliteDataReader.

[bricelam]

@buybackoff Would you be interested in contributing in this area? (Possibly just GetBytes)

[buybackoff]

I have it my fork Spreads/Spreads.SQLite@21add79

I do not use much functionality of SQL, only as ordered KV blob storage, so I am pretty much all set already.

[bricelam]

I noticed you had a commit that mentioned it, but I can't/shouldn't look at it unless you submit a PR and sign the CLA. No worries if you're not interested; I just thought I'd ping you to see if you were.

[AlexanderTaeschner]

sqlite3_blob_open is only usable to open a BLOB in a certain row in a specific column of an existing table. Since the SqliteDataReader can only get the column and table name but not the rowid, it can not be used to implement GetBytes!
From my point of view the only use case could be to access a really large BLOB incrementally. Would this be interesting?

[bricelam]

Oh wow, that is restrictive. It's a lower-priority feature, but I think it's still interesting for certain applications.

We could make the APIs use sqlite3_column_blob and a MemoryStream by default. But if you happen to SELECT rowid it can use a more optimized sqlite3_blob_open codepath.

[bricelam]

It's not very discoverable, but at least it unblocks applications that need to either...

• Peek at the first few bytes/characters (e.g. file headers)
• Do some forward-only parsing/querying/interpreting in a memory-optimized way (e.g. XML, JSON, BAML, etc.)

[bricelam]

sqlite3_bind_zeroblob() is related to these scenarios. To stream a BLOB into the database, you'd INSERT using a zeroblob parameter of the correct size then call sqlite3_blob_open and sqlite3_blob_write.

[bricelam]

Initial implementation merged. See #13987 for further improvements.