Summary: in this tutorial, we will show you how to use the transaction features of PHP PDO to ensure the data integrity in the SQLite database.
Let’s create a new table named task_documents
that stores the relationships between a task and a document.
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
);
Code language: SQL (Structured Query Language) (sql)
Basically, a task has multiple documents and a document may belong to many tasks. The relationship between a task and a document is many-to-many.
Whenever we add a new document to the documents
table, we need to assign it to a specific task. We don’t want to be in a situation that a document is inserted without belonging to a task.
To ensure this, we must perform both actions: insert a new document and assign it to a task in the all-or-nothing fashion. To achieve this, we use the PDO transaction feature.
Whenever we execute a statement in PDO, the database commits the operation by default. To wrap multiple operations inside a transaction, we call the beginTransaction()
method of the PDO object as follows:
$pdo->beginTransaction();
Code language: PHP (php)
To commit the transaction, you call the commit()
method:
$pdo->commit();
Code language: PHP (php)
In case something wrong happened, you can roll back all the operations using the rollback()
method as follows:
$pdo->rollback();
Code language: PHP (php)
SQLite PHP transaction example
We create a new class name SQLiteTransaction
for the demonstration.
The following method inserts a new document into the documents
table and returns the document id.
/**
* 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();
}
Code language: PHP (php)
The following method assigns a document to a task.
/**
* 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();
}
Code language: SQL (Structured Query Language) (sql)
The following method inserts a document and assigns it to a task within a single transaction.
/**
* 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;
}
}
Code language: PHP (php)
Notice that you must execute the following statement to enable foreign key support in SQLite:
PRAGMA foreign_keys = ON;
Code language: SQL (Structured Query Language) (sql)
Therefore, from the PHP application, we use the following statement:
$this->pdo->exec('PRAGMA foreign_keys = ON');
Code language: PHP (php)
Let’s create the index.php
file to test the SQLiteTransaction
class.
<?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();
}
Code language: PHP (php)
We assigned an non-existent task_id 9999
to purposely violate the foreign key constraint when assigning the document to the task. As the result, PHP threw a PDO exception that caused all operations to be rolled back.
Now, if you change the task id to any valid value in the tasks
table, a new document will be inserted into the documents
table and also new entry is also inserted into the task_documents
table.
In this tutorial, we have shown you how to perform the transaction in SQLite using PHP PDO transaction API.