SQLite PHP: trabalhando com dados BLOB

Resumo : neste tutorial, você aprenderá como gerenciar dados BLOB em banco de dados SQLite usando PHP PDO.

BLOB significa um objeto binário grande que é uma coleção de dados binários armazenados como um valor no banco de dados. Ao usar o BLOB, você pode armazenar documentos, imagens e outros arquivos multimídia no banco de dados.

Criaremos uma nova tabela nomeada documentspara fins de demonstração.

CREATE TABLE IF NOT EXISTS documents (
    document_id INTEGER PRIMARY KEY,
    mime_type   TEXT    NOT NULL,
    doc         BLOB
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Escrevendo BLOB na tabela

Para inserir dados BLOB na tabela, siga as seguintes etapas:

1. Conecte-se ao banco de dados SQLite criando uma instância da classe PDO.
2. Use   fopen()a função para ler o arquivo. A fopen()função retorna um ponteiro de arquivo.
3. Prepare a instrução INSERT para execução chamando o prepare()método do objeto PDO. O prepare()método retorna uma instância da PDOStatementclasse.
4. Use o bindParam()método do PDOStatementobjeto para vincular um parâmetro a um nome de variável. Para os dados BLOB, você vincula um parâmetro ao ponteiro do arquivo.
5. Chame o execute()método do objeto de instrução PDO.

Por exemplo, o seguinte insertDoc()método da SQLiteBLOBclasse insere um novo documento na documentstabela usando as etapas acima:

<?php

namespace App;

/**
 * SQLite PHP Blob Demo
 */
class SQLiteBLOB {

    /**
     * PDO object
     * @var \PDO
     */
    private $pdo;

    /**
     * Initialize the object with a specified PDO object
     * @param \PDO $pdo
     */
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Insert blob data into the documents table
     * @param type $pathToFile
     * @return type
     */
    public function insertDoc($mimeType, $pathToFile) {
        if (!file_exists($pathToFile))
            throw new \Exception("File %s not found.");

        $sql = "INSERT INTO documents(mime_type,doc) "
                . "VALUES(:mime_type,:doc)";

        // read data from the file
        $fh = fopen($pathToFile, 'rb');

        $stmt = $this->pdo->prepare($sql);

        $stmt->bindParam(':mime_type', $mimeType);
        $stmt->bindParam(':doc', $fh, \PDO::PARAM_LOB);
        $stmt->execute();

        fclose($fh);

        return $this->pdo->lastInsertId();
    }
}Linguagem de código:  PHP  ( php )

O seguinte script index.php insere dois documentos: 1 arquivo PDF e 1 imagem da assetspasta na documentstabela.

<?php

require 'vendor/autoload.php';

use App\SQLiteConnection as SQLiteConnection;
use App\SQLiteBLOB as SQLiteBlob;

$sqlite = new SQLiteBlob((new SQLiteConnection)->connect());

// insert a PDF file into the documents table
$pathToPDFFile = 'assets/sqlite-sample database-diagram.pdf';
$pdfId = $sqlite->insertDoc('application/pdf', $pathToPDFFile);

// insert a PNG file into the documents table
$pathToPNGFile = 'assets/sqlite-tutorial-logo.png';
$pngId = $sqlite->insertDoc('image/png', $pathToPNGFile);Linguagem de código:  PHP  ( php )

Executamos este arquivo de script index.php e usamos a seguinte instrução SELECT para verificar a inserção:

SELECT id,
       mime_type,
       doc
  FROM documents;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Lendo BLOB da tabela

Para ler o BLOB do banco de dados, adicionamos um novo método nomeado readDoc() à SQLiteBLOBclasse da seguinte forma:

/**
     * Read document from the documents table
     * @param type $documentId
     * @return type
     */
    public function readDoc($documentId) {
        $sql = "SELECT mime_type, doc "
                . "FROM documents "
                . "WHERE document_id = :document_id";

        // initialize the params
        $mimeType = null;
        $doc = null;
        //
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute([":document_id" => $documentId])) {

            $stmt->bindColumn(1, $mimeType);
            $stmt->bindColumn(2, $doc, \PDO::PARAM_LOB);

            return $stmt->fetch(\PDO::FETCH_BOUND) ?
                    ["document_id" => $documentId,
                     "mime_type" => $mimeType,
                     "doc" => $doc] : null;
        } else {
            return null;
        }
    }Linguagem de código:  PHP  ( php )

O document.phpscript a seguir obtém o document_idda string de consulta e chama o readDoc()método para renderizar o documento.

<?php

require 'vendor/autoload.php';

use App\SQLiteConnection as SQLiteConnection;
use App\SQLiteBLOB as SQLiteBlob;

$pdo = (new SQLiteConnection)->connect();
$sqlite = new SQLiteBlob($pdo);

// get document id from the query string
$documentId = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

// read documet from the database
$doc = $sqlite->readDoc($documentId);
if ($doc != null) {
    header("Content-Type:" . $doc['mime_type']);
    echo $doc['doc'];
} else {
    echo 'Error loading document ' . $documentId;
}Linguagem de código:  PHP  ( php )

Por exemplo, a captura de tela a seguir mostra como o script document.php retorna o arquivo PDF no navegador da web:

Para testar o ID do documento 2, altere o valor na string de consulta conforme mostrado na captura de tela abaixo:

Atualizar dados BLOB

O updateDoc()método a seguir atualiza os dados BLOB na documentstabela.

   /**
    * Update document
    * @param type $documentId
    * @param type $mimeType
    * @param type $pathToFile
    * @return type
    * @throws \Exception
    */
    public function updateDoc($documentId, $mimeType, $pathToFile) {

        if (!file_exists($pathToFile))
            throw new \Exception("File %s not found.");
        
        $fh = fopen($pathToFile, 'rb');

        $sql = "UPDATE documents
                SET mime_type = :mime_type,
                    doc = :doc
                WHERE document_id = :document_id";

        $stmt = $this->conn->prepare($sql);

        $stmt->bindParam(':mime_type', $mimeType);
        $stmt->bindParam(':data', $fh, \PDO::PARAM_LOB);
        $stmt->bindParam(':document_id', $documentId);

        fclose($fh);

        return $stmt->execute();
    }Linguagem de código:  PHP  ( php )

Neste tutorial, mostramos como escrever, ler e atualizar dados BLOB no banco de dados SQLite usando PHP PDO.