Транзакции в SQLite с использованием PHP PDO

В этом уроке мы рассмотрим, как использовать функции транзакций PHP PDO для обеспечения целостности данных в базе данных SQLite.

Давайте создадим новую таблицу с именем task_documents, которая хранит отношения между задачей и документом.

CREATE TABLE IF NOT EXISTS task_documents (
    task_id     INT NOT NULL,
    document_id INT NOT NULL,
    FOREIGN KEY (
        task_id
    )
    REFERENCES tasks (task_id) ON UPDATE CASCADE
                               ON DELETE CASCADE,
    FOREIGN KEY (
        document_id
    )
    REFERENCES documents (document_id) ON UPDATE CASCADE
                                      ON DELETE CASCADE
);

По сути, задача имеет несколько документов, и документ может принадлежать многим задачам. Отношения между задачей и документом многие-ко-многим.

Всякий раз, когда мы добавляем новый документ в таблицу документов, нам нужно назначить его для конкретной задачи. Мы не хотим, чтобы документ вставлялся без участия к какой-либо задаче.

Чтобы убедиться в этом, мы должны выполнить оба действия: вставить новый документ и назначить его задаче «все или ничего». Для этого мы используем функцию транзакции PDO.

Всякий раз, когда мы выполняем оператор в PDO, база данных по умолчанию фиксирует операцию. Чтобы обернуть несколько операций внутри транзакции, мы вызываем метод beginTransaction() объекта PDO следующим образом:

$pdo->beginTransaction();

Чтобы зафиксировать транзакцию, вы вызываете метод commit():

$pdo->commit();

Если что-то пошло не так, вы можете откатить все операции, используя метод rollback() следующим образом:

$pdo->rollback();

Пример транзакции SQLite PHP

Мы создаем новое имя класса SQLiteTransaction для демонстрации.

Следующий метод вставляет новый документ в таблицу документов и возвращает идентификатор документа:

/**
 * Insert blob data into the documents table
 * @param type $pathToFile
 * @return document id
 */
public function insertDoc($mimeType, $pathToFile) {

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

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

  // execute the INSERT statement
  $stmt->execute();

  fclose($fh);

  // return the document id
  return $this->pdo->lastInsertId();
}

Следующий метод назначает документ задаче:

/**
 * Assign a document to a task
 * @param int $taskId
 * @param int $documentId
 */
private function assignDocToTask($taskId, $documentId) {
  $sql = "INSERT INTO task_documents(task_id,document_id) "
          . "VALUES(:task_id,:document_id)";

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

  $stmt->bindParam(':task_id', $taskId);
  $stmt->bindParam(':document_id', $documentId);

  $stmt->execute();
}

Следующий метод вставляет документ и назначает его задаче в рамках одной транзакции.

/**
 * Add a task and associate a document to it
 * @param int $taskId
 * @param string $mimeType
 * @param string $pathToFile
 */
public function attachDocToTask($taskId, $mimeType, $pathToFile) {
  try {

    // to make sure the foreign key constraint is ON
    $this->pdo->exec('PRAGMA foreign_keys = ON');

    // begin the transaction
    $this->pdo->beginTransaction();

    // insert a document first
    $documentId = $this->insertDoc($mimeType, $pathToFile);

    // associate document with the task
    $this->assignDocToTask($taskId, $documentId);

    // commit update
    $this->pdo->commit();
  } catch (\PDOException $e) {
    // rollback update
    $this->pdo->rollback();
    //
    throw $e;
  }
}

Обратите внимание, что вы должны выполнить следующую инструкцию, чтобы включить поддержку внешнего ключа в SQLite:

PRAGMA foreign_keys = ON;

Поэтому из приложения PHP мы используем следующее утверждение:

$this->pdo->exec('PRAGMA foreign_keys = ON');

Давайте создадим файл index.php для проверки класса SQLiteTransaction:

<?php
 
require 'vendor/autoload.php';
 
use App\SQLiteConnection;
use App\SQLiteTransaction;
 
$pdo = (new SQLiteConnection())->connect();
$sqlite = new SQLiteTransaction($pdo);
 
$taskId = 9999;
 
try {
  // add a new task and associate a document
  $sqlite->attachDocToTask($taskId, 'application/pdf', 'assets/test.pdf');
} catch (PDOException $e) {
  echo $e->getMessage();
}

Мы присвоили несуществующий task_id=9999, чтобы преднамеренно нарушать ограничение внешнего ключа при назначении документа задаче. В результате PHP выдал исключение PDO, которое вызвало откат всех операций.

Теперь, если вы измените идентификатор задачи на любое допустимое значение в таблице задач, новый документ будет вставлен в таблицу документов, а также новая запись также будет вставлена в таблицу task_documents.

В этом уроке мы познакомились с выполнением транзакций в SQLite с помощью API транзакций PHP PDO.