SQLite PHP: Transação

Resumo : neste tutorial mostraremos como usar os recursos de transação do PHP PDO para garantir a integridade dos dados no banco de dados SQLite.

Vamos criar uma nova tabela chamada task_documentsque armazena os relacionamentos entre uma tarefa e um documento.

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
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Basicamente, uma tarefa possui vários documentos e um documento pode pertencer a muitas tarefas. O relacionamento entre uma tarefa e um documento é muitos para muitos.

Sempre que adicionamos um novo documento à documentstabela, precisamos atribuí-lo a uma tarefa específica. Não queremos estar numa situação em que um documento seja inserido sem pertencer a uma tarefa.

Para garantir isso, devemos realizar ambas as ações: inserir um novo documento e atribuí-lo a uma tarefa no estilo tudo ou nada. Para conseguir isso, usamos o recurso de transação PDO.

Sempre que executamos uma instrução no PDO, o banco de dados confirma a operação por padrão. Para agrupar múltiplas operações dentro de uma transação, chamamos o beginTransaction()método do objeto PDO da seguinte forma:

$pdo->beginTransaction();Linguagem de código:  PHP  ( php )

Para confirmar a transação, você chama o commit()método:

$pdo->commit();Linguagem de código:  PHP  ( php )

Caso algo errado aconteça, você pode reverter todas as operações usando o rollback()método a seguir:

$pdo->rollback();Linguagem de código:  PHP  ( php )

Exemplo de transação SQLite PHP

Criamos um novo nome de classe SQLiteTransactionpara a demonstração.

O método a seguir insere um novo documento na documentstabela e retorna o ID do documento.

   /**
     * 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();
    }Linguagem de código:  PHP  ( php )

O método a seguir atribui um documento a uma tarefa.

   /**
     * 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();
    }Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O método a seguir insere um documento e o atribui a uma tarefa em uma única transação.

    /**
     * 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;
        }
    }Linguagem de código:  PHP  ( php )

Observe que você deve executar a seguinte instrução para habilitar o suporte de chave estrangeira no SQLite:

PRAGMA foreign_keys = ON;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Portanto, na aplicação PHP, usamos a seguinte instrução:

$this->pdo->exec('PRAGMA foreign_keys = ON');Linguagem de código:  PHP  ( php )

Vamos criar o index.phparquivo para testar a SQLiteTransactionclasse.

<?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();
}Linguagem de código:  PHP  ( php )

Atribuímos um task_id inexistente 9999para violar propositalmente a restrição de chave estrangeira ao atribuir o documento à tarefa. Como resultado, o PHP lançou uma exceção PDO que fez com que todas as operações fossem revertidas.

Agora, se você alterar o id da tarefa para qualquer valor válido na taskstabela, um novo documento será inserido na documentstabela e também uma nova entrada será inserida na task_documentstabela.

Neste tutorial, mostramos como realizar a transação em SQLite usando a API de transação PHP PDO.