База данных SQLite в файле: практическое использование с PHP

Расширенные примеры работы с SQLite в PHP

Ниже приведены практические сценарии, демонстрирующие возможности SQLite: внешние ключи, транзакции, работа с двоичными данными, использование агрегатных функций и интеграция с файловой системой.

Пример 1. Создание таблицы с внешними ключами

<?php
$pdo = new PDO('sqlite:/tmp/shop.db');
$pdo->exec('PRAGMA foreign_keys = ON');
$pdo->exec('CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, category_id INTEGER, FOREIGN KEY (category_id) REFERENCES categories(id))');
$pdo->exec('INSERT INTO categories VALUES (1, "Электроника")');
// Попытка вставить продукт с несуществующей категорией вызовет ошибку
$stmt = $pdo->prepare('INSERT INTO products (title, category_id) VALUES (?, ?)');
$stmt->execute(['Телефон', 2]); // Ошибка: нет категории с id 2
?>

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed

Пример 2. Массовая вставка с транзакциями

<?php
$pdo = new PDO('sqlite:/tmp/bench.db');
$pdo->exec('CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)');
// Без транзакции: 1000 вставок за ~1.5 сек
$start = microtime(true);
for ($i = 0; $i < 1000; $i++) {
    $pdo->exec('INSERT INTO test (value) VALUES ("item '.$i.'")');
}
echo 'Без транзакции: '.round(microtime(true)-$start, 3).' сек'."\n";

// С транзакцией: 1000 вставок за ~0.03 сек
$pdo->exec('DELETE FROM test');
$start = microtime(true);
$pdo->beginTransaction();
for ($i = 0; $i < 1000; $i++) {
    $pdo->exec('INSERT INTO test (value) VALUES ("item '.$i.'")');
}
$pdo->commit();
echo 'С транзакцией: '.round(microtime(true)-$start, 3).' сек'."\n";
?>

Без транзакции: 1.452 сек
С транзакцией: 0.027 сек

Пример 3. Поиск по части строки с LIKE

<?php
$pdo = new PDO('sqlite:/tmp/contacts.db');
$pdo->exec('CREATE TABLE contacts (name TEXT, phone TEXT)');
$pdo->exec('INSERT INTO contacts VALUES ("Иванов Иван", "123-456"), ("Петров Петр", "789-012")');
$search = 'Иван';
$stmt = $pdo->prepare('SELECT * FROM contacts WHERE name LIKE :pattern');
$stmt->execute(['pattern' => '%'.$search.'%']);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo $row['name'].' - '.$row['phone']."\n";
}
?>

Иванов Иван - 123-456

Пример 4. Сохранение и чтение изображения (BLOB)

<?php
$pdo = new PDO('sqlite:/tmp/gallery.db');
$pdo->exec('CREATE TABLE images (id INTEGER PRIMARY KEY, data BLOB)');
// Запись
$image = file_get_contents('photo.jpg');
$stmt = $pdo->prepare('INSERT INTO images (data) VALUES (:data)');
$stmt->bindParam(':data', $image, PDO::PARAM_LOB);
$stmt->execute();
// Чтение
$id = $pdo->lastInsertId();
$stmt = $pdo->prepare('SELECT data FROM images WHERE id = :id');
$stmt->execute(['id' => $id]);
$stmt->bindColumn(1, $blob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
file_put_contents('output.jpg', $blob);
?>

Файл output.jpg сохранен с тем же содержимым.

Пример 5. Использование GROUP_CONCAT для агрегации

<?php
$pdo = new PDO('sqlite:/tmp/teams.db');
$pdo->exec('CREATE TABLE players (team TEXT, name TEXT)');
$pdo->exec('INSERT INTO players VALUES ("A", "Alice"), ("A", "Bob"), ("B", "Charlie")');
foreach ($pdo->query('SELECT team, GROUP_CONCAT(name, ", ") AS members FROM players GROUP BY team') as $row) {
    echo $row['team'].': '.$row['members']."\n";
}
?>

A: Alice, Bob
B: Charlie

Пример 6. Миграция: добавление столбца с проверкой существования

<?php
$pdo = new PDO('sqlite:/tmp/myapp.db');
$table = 'users';
$column = 'age';
$stmt = $pdo->query("PRAGMA table_info($table)");
$columns = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
if (!in_array($column, $columns)) {
    $pdo->exec("ALTER TABLE $table ADD COLUMN $column INTEGER DEFAULT 0");
    echo 'Столбец добавлен.';
} else {
    echo 'Столбец уже существует.';
}
?>

Столбец добавлен. (при первом запуске)