Работа с BLOB-типом данных SQLite с помощью PHP PDO

В этом уроке вы узнаете, как управлять BLOB-данными в базе данных SQLite с помощью PHP PDO.

BLOB обозначает большой двоичный объект, который представляет собой набор двоичных данных, хранящихся в виде значения в базе данных. Используя BLOB, вы можете хранить документы, изображения и другие мультимедийные файлы в базе данных.

Мы создадим новую таблицу под названием документы для демонстрации.

CREATE TABLE IF NOT EXISTS documents (
    document_id INTEGER PRIMARY KEY,
    mime_type   TEXT    NOT NULL,
    doc         BLOB
);

Запись BLOB в таблицу

Чтобы вставить BLOB-данные в таблицу, используйте следующие шаги:

• Подключитесь к базе данных SQLite, создав экземпляр класса PDO.
• Используйте функцию fopen(), чтобы прочитать файл. Функция fopen() возвращает указатель на файл.
• Подготовьте оператор INSERT к выполнению, вызвав метод prepare() объекта PDO. Метод prepare() возвращает экземпляр класса PDOStatement.
• Используйте метод bindParam() объекта PDOStatement, чтобы связать параметр с именем переменной. Для данных BLOB вы привязываете параметр к указателю файла.
• Вызовите метод execute() объекта оператора PDO.

Например, следующий метод insertDoc() класса SQLiteBLOB вставляет новый документ в таблицу документов, используя описанные выше шаги:

<?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();
  }
}

Следующий скрипт index.php вставляет два документа: 1 файл PDF и 1 изображение из папки ресурсов в таблицу документов.

<?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);

Чтение BLOB из таблицы

Чтобы прочитать BLOB из базы данных, мы добавляем новый метод readDoc() в класс SQLiteBLOB следующим образом:

/**
 * 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;
  }
 }

Следующий скрипт document.php получает document_id из строки запроса и вызывает метод readDoc() для визуализации документа.

<?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;
}

Например, на следующем снимке экрана показано, как скрипт document.php возвращает файл PDF в веб-браузере:

Обновление данных BLOB

Следующий метод updateDoc() обновляет данные BLOB в таблице документов:

/**
* 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();
}

В этом уроке мы узнали о способах: записывать, читать и обновлять BLOB-данные в базе данных SQLite с помощью PHP PDO.