Read and Write Blobs as Byte arrays

[TheDudeFromCI]

One feature that would be extremely useful to have is the ability to read and write blobs alongside the other data types. With Rusqlite, doing this manually requires a few extra steps and really breaks up the existing flow provided by this library, so there is a lot of utility and reduced boilerplate in doing this.

[quietboil]

Could you give me an idea about what you have in mind? A code sample from the future 😉might help. I was staring at Rusqlite BLOB API for some time now and maybe I'm missing something obvious as I cannot envision how Include SQL could add more utility to it.

[TheDudeFromCI]

Well, I believe that adding some extensions to the generated traits might make working with them a bit easier, and allow for translation of &[u8] byte arrays back and forth with the SQL.

For example, I'll make a dummy trait for reading and writing to blobs within Connections.

pub trait BlobIO {
  fn read_blob(&self, table: &str, col: &str, row: i64) -> Result<Vec<u8>>;
  fn write_blob(&self, table: &str, col: &str, row: i64, data: &[u8]) -> Result<()>;
}

impl BlobIO for Connection {
    fn read_blob(&self, table: &str, col: &str, row: i64) -> Result<Vec<u8>> {
        let mut bytes = Vec::new();
        let mut data = [0u8; 1024];

        let blob = self.blob_open(DatabaseName::Main, table, col, row, true)?;

        let mut offset = 0;
        loop {
            let read = blob.read_at(&mut data, offset)?;
            if read == 0 {
                break;
            }

            bytes.extend_from_slice(&data[.. read]);
            offset += read;
        }

        Ok(bytes)
    }

    fn write_blob(&self, table: &str, col: &str, row: i64, data: &[u8]) -> Result<()> {
        self.blob_open(DatabaseName::Main, table, col, row, false)?
            .write_at(data, 0)?;

        Ok(())
    }
}

I'm not quite sure how the underlying implementation is done for the generated methods, but I imagine the most practical method for implementing this would be, um, special SQL methods? Since SQL commands can be buffered and sent as a qroup, it make sense that you can add a Blob-specific command to that buffer on send that could call the read_blob and write_blob methods above.

Like, for example:

-- name: add_server_entry&
-- Add an entry to the server list.
-- param: uuid: &[u8] - The uuid bytes
-- param: name: &str - The name of the server.
-- param: address: &str - The address of the server.
INSERT OR REPLACE INTO server_list VALUES (ZEROBLOB(16), :name, :address);
%WRITE_BLOB server_list uuid $LAST_ROW :uuid;
/

Which would roughly be converted into:

fn add_server_entry(&self, uuid: &[u8], name: &str, address: &str) {
  // Normal connection SQL calls ...

  self.write_blob("server_list", "uuid", self.last_insert_rowid(), uuid);
}

Sadly, it feels like certain actions such as reading multiple blobs by wrapping the &Row<'_> returned by SELECT methods might be a bit more complicated. It would overwrite the value returned by get_ref to be a byte array, thus, effectively allowing for querying multiple blobs with very little boilerplate.

If nothing else, just having the read_blob and write_blob traits automatically imported easily reduces a lot of boilerplate code even without touching the SQL aspects.