Another undocumented feature of the PHP SQLite3 object, available since 2009 is openBlob. Basically it's a function that will return a stream pointer to a blob value in a table. Very very useful when you are dealing with files stored in a SQLite3 database.
Source code says:
proto resource SQLite3::openBlob(string table, string column, int rowid [, string dbname])
Open a blob as a stream which we can read / write to.
But despite that it's not possible to write to the blob, only to read (the write method is empty in the extension source code: it won't return any error but no change will be recorded).
One example of use:
<?php
$db = new SQLite3('files.sqlite');
$db->exec('CREATE TABLE files (id INTEGER PRIMARY KEY, filename TEXT, content BLOB);');
$statement = $db->prepare('INSERT INTO files (filename, content) VALUES (?, ?);');
$statement->bindValue('filename', 'Archive.zip');
$statement->bindValue('content', file_get_contents('Archive.zip'));
$statement->execute();
$fp = $db->openBlob('files', 'content', $id);
while (!feof($fp))
{
echo fgets($fp);
}
fclose($fp);
?>
You can also seek in the stream. This is pretty useful for saving large files from the database too, this way you can use stream_copy_to_stream, it will be faster and more memory-efficient than dumping the file in memory before writing it to the disk.
Please note that openBlob() won't work on VIRTUAL FTS4 compressed tables.
