Writing large blob of unknown size into SQLite

Question

I am implementing a stream interface that takes binary data from a stream (e.g. a HTTP download ord Android content:// share) and stores it into a SQLite blob.

Is there a way to use the sqlite3_blob_open and sqlite3_blob_write interfaces without knowing the end size in advance?

sqlite3_blob_open states that:

Use the sqlite3_blob_bytes() interface to determine the size of the opened blob. The size of a blob may not be changed by this interface. Use the UPDATE SQL command to change the size of a blob.

---

My current code looks like this. Set initial size (1):

// set size of attachment
{
    auto stmt = session_->prepare(
                "UPDATE attachments_content "
                "SET content = zeroblob(:size) "
                "WHERE rowid = :rowid");
    stmt.bind(":size", size_);
    stmt.bind(":rowid", rowid);
    stmt.execWithoutResult();
}

Get blob handle (2), this is a wrapper around sqlite3_blob_open:

    auto blob = session_->openBlob("main", "attachments_content",
                                  "content", rowid,
                                  SmartSqlite::Blob::READWRITE);

Write a piece of data (3):

        blob.write(data.data(), data.size(),
                   progress.bytesProcessed /* offset */);

Given that the blob object cannot resize the blob, is there any other way to incrementally increase the blob size then receiving more data?

Answer 1

The only three ways of modifying blobs are

• to use SQL statements, or
• to use the sqlite3_blob_*() interface, which cannot resize blobs, or
• to modify the database file directly.

Due to SQLite's record format, changing the size of a blob can require rewriting the entire row. Therefore, what you want cannot be done efficiently.

I'd consider writing the stream into a temporary file and handling it afterwards.