Транзакции в 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.