Использование SQLite в PHP

19-11-2021Время чтения ~ 14 мин.Albireo Framework / CMS, PHP, SQL 6903

Для работы с базой данных (любой), в PHP сейчас принято использовать PDO (PHP Data Objects). Раньше было так: в зависимости от используемой базы, нужно было использовать и её специфичные функции. Поскольку PHP поддерживает больше десятка баз, то появился и «зоопарк» функций: отдельно для MySQL, отдельно для SQLite, отдельно для dBase и т.д.

Но с точки зрения разработчика, работа с базой состоит из нескольких этапов:

• Подключение к базе.
• Выполнение SQL-запросов.
• Получение результата.

То есть не важно какая база, схема работы всегда одна и та же. Таким образом и был создан PDO, который представляет собой единый интерфейс для работы с базами. Вначале рассмотрим «чистый» вариант работы с PDO, а потом как его упростить и сделать удобным с помощью библиотек Albireo Framework.

Подключение к базе

В самом начале нужно подключиться к базе данных. Для этого создаётся объект PDO, где сразу указываются данные для подключения. Вот так можно подключиться в базе MySQL:

$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass); 

Для SQLite не нужны пароль и юзер, но нужно указать имя файла:

$db = new PDO('sqlite:db/pages.sqlite');

Теперь переменная $db — это объект базы и все операции мы можем делать через него.

Строчку, которую мы указываем при создании объекта называется DSN (Data Source Name) — имя источника данных. В ней указываются параметры для доступа к базе, а также её тип. DSN — это универсальный вариант, подходящий для любой базы, поскольку некоторые из них требуют каких-то специфичных настроек, например порт, сокеты или кодировка.

После того, как соединение было создано, можно установить дополнительные параметры. Для этого используется метод setAttribute(). Параметров довольно-таки приличное количество, но большинство всё-таки имеют свою специфику. Наиболее часто используется PDO::ATTR_DEFAULT_FETCH_MODE, который указывает как именно нужно возвращать результат sql-запроса. В подавляющем большинстве случаев это будет обычный ассоциативный массив:

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Работа с PDO строится на php-исключениях. То есть в случае проблем PDO будет генерировать исключение. Чтобы установить такой режим используется атрибут PDO::ATTR_ERRMODE:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

После этого код с PDO заключаем в блок try ... catch:

try {
   $db = new PDO('sqlite:db/pages.sqlite');
   $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
   ... работаем с базой ...
   
} catch (\PDOException $e) {
   // если возникли ошибки
   echo $e->getMessage(); // выводим сообщение 
}

Даже если мы не будем использовать try ... catch, то в случае ошибки, PHP сам вывалит всю информацию о «крушении», где может оказаться детальная информация, включая имя пользователя и прочие секретные данные. Поэтому независимо от выбранного режима ошибок, нужно отлавливать исключения.

Нужно ли закрывать соединение с базой после использования? В старых скриптах и книжках рекомендовали закрывать подключение, чтобы освободить сервер базы как можно раньше. Но уже достаточно давно этого делать не нужно (может для каких-то особых случаев) — PHP самостоятельно закроет все соединения после отработки скрипта.

Обычные SQL-запросы

Для выполнения sql-запросов PDO предлагает несколько функций. Самая простая — это query().

Дальше я буду приводить примеры без кода подключения и обработки исключений.

$rows = $db->query('SELECT * FROM t1');
 
foreach($rows as $row) {
  ... тут выводим массив $row ...	
}

В query() мы можем использовать любые sql-запросы. Особенность этого способа в том, что PDO послушно выполнит команду без какой-либо обработки или проверок. Это очень похоже на то, что мы писали ручками в окне запросов. Понятно, что в реальном приложении такой вариант недопустим, поскольку пользователь может передать в запрос строчку, которая может оказаться либо некорректной (например лишнюю кавычку), либо злонамеренно, чтобы изменить исходный запрос и получить (или изменить) какие-то важные данные (SQL-инъекции).

Поэтому одно из главных правил для разработчика — не доверять входящим данным. Обычно для решения таких задач используется механизм экранирования, который защищает передаваемые данные в sql-запрос так, чтобы не нарушить его работу.

Экранирование

В PDO используется метод quote(), который экранирует спецсимволы да ещё и с учётом специфика драйвера базы. Но при этом такой способ считается не только устаревшим, но и настоятельно не рекомендуемым к использованию. Дело в том, что в эпоху юникода спецсимволов оказалось слишком уж много и учесть все сложные случаи SQL-инъекций не так просто. Поэтому вместо экранирования применяется другой вариант — подготовка sql-запроса с помощью псевдопеременных.

Псевдопеременные

Идея довольно простая: разделить «чистый» sql-запрос от передаваемых в этот запрос данных. Пример с query():

$rows = $db->query('SELECT * FROM t1 WHERE id = ' . $id);

Здесь мы передаём $id прямо в код запроса. Что если этот id получен из get/post-запроса с сайта и там не число, а строчка 1; DROP TABLE t1;? Понятна проблема?

Поэтому вначале подготавливаем SQL-запрос с использованием псевдопеременных и только потом его выполняем.

$sth = $db->prepare('SELECT * FROM t1 WHERE id = :id');
$result = $sth->execute([':id' => $id]);

Первая строчка — это sql-запрос, где нужно разметить псевдопеременные. Они начинаются с двоеточия. Вторая строчка — непосредственное выполнение этого запроса, где мы указываем массив данных для псевдопеременных. PDO автоматически проверит корректность $id и, если потребуется, самостоятельно выполнит всё экранирование и необходимые преобразования.

В этом способе мы использовали именованные параметры. Можно использовать неименованные — в этом случае используется символ вопроса «?»:

$sth = $db->prepare('SELECT * FROM t1 WHERE id > ? AND id < ?');
$result = $sth->execute([5, 10]);

Второй вариант немного проще для написания, но нужно держать рядом сам sql-запрос, чтобы не запутаться в последовательности элементов массива. Поэтому надёжней будет использование именно именованных параметров.

Иногда псевдопеременные называют заполнителями.

Получение результата sql-запроса

Для выполнения подготовленного (после prepare) запроса PDO предлагает несколько функций. В execute() происходит выполнение, но нет возврата полученных данных. Поэтому обычно execute() используют с такими sql-запросами, как INSERT, UPDATE, DELETE, то есть там, где не нужно получать выборку данных.

Если же мы используем SELECT, то используется (как правило) функция fetchAll()

$sth = $db->prepare('SELECT * FROM t1 WHERE id = :id');
$sth->execute([':id' => $id]);
$rows = $sth->fetchAll();
 
foreach($rows as $row) {
  ... тут выводим массив $row ...
}

Этот пример — достаточно типовой при работе с PDO: вначале подготовили запрос, потом привязали данные/выполнили и получили итоговый массив.

Библиотеки для PDO

Нельзя не отметить, что код для работы с базой получается не самым компактным. Плюс оборачивать все операции в try ... catch тоже его усложняет. Поэтому в большинстве случаев разработчики используют какие-то библиотеки, которые работают как обёртка для PDO, упрощая код.

Я расскажу про библиотеку из Albireo Framework. Она достаточно простая и содержит самый минимум функционала, которого достаточно для большинства операций с базой данных.

Наверное стоит ещё отметить, что большинство аналогичных библиотек предлагают не сколько обёртку (wrapper) для PDO, сколько представляют собой Query Builder — построитель sql-запросов. Это усложняет код библиотеки и требует хорошей документации. В Albireo Framework используется настоящий PDO-объект, с которым можно использовать родные методы.

Классы Albireo для работы с базой

Есть два класса. Первый Pdo\PdoConnect, который служит только для создания подключения к базе. На выходе родной pdo-объект. Второй класс — Pdo\PdoQuery с помощью которого можно выполнять sql-запросы к базе, а также он имеет несколько вспомогательных методов.

Подключение к базе

Класс Pdo\PdoConnect это singleton для того, чтобы исключить дубли объектов. По простому это означает, что можно не переживать о многочисленных подключениях к базе. Даже если сто раз вызвать функцию коннекта, то в реальности будет создан только один, а во всех остальных будет возвращён уже существующий объект PDO.

// получение экземпляра PDO
$pdo = Pdo\PdoConnect::getInstance();
 
// получение соединения PDO
$db = $pdo->connect([
        'dsn' => 'sqlite:' . DATA_DIR . 'storage/test.sqlite'
    ]);

Метод connect() принимает массив конфигурации, где должен быть обязательный ключ dsn: здесь указываем имя файла SQLite. Если файла нет, то он будет автоматически создан. Это стандартное поведение PDO.

Класс Pdo\PdoConnect можно использовать для любых баз данных.

Также доступны несколько других ключей.

• username — если используется MySQL, то можно указать логин
• password — и пароль
• queries — можно указать sql-запросы, которые будут выполнены сразу после подключения. Опять же, актуально для MySQL.
• options — можно указать стандартные PDO-параметры. По умолчанию устанавливается PDO::ATTR_DEFAULT_FETCH_MODE равным PDO::FETCH_ASSOC

Кроме этого для всех подключений устанавливается режим работы с PDO через исключения.

Метод connect() возвращает либо объект PDO, либо false в случае ошибок. При этом выводится сообщение об ошибке. В общем случае код будет такой:

$pdo = Pdo\PdoConnect::getInstance();
$db = $pdo->connect([
        'dsn' => 'sqlite:' . DATA_DIR . 'storage/test.sqlite'
    ]);
 
if (empty($db)) {
    // ошибка
    echo '<div class="t-red600 t-center mar10">Ошибка соединения с БД</div>';
    return; // выходим, поскольку нет возможности работы с базой
} else {
	// всё ок, можно например вывести сообщение
    echo '<div class="t-green600 t-center mar20-b">Соединение с БД установлено</div>';
}
 
... дальше работаем с базой ...

Работа с SQL

Для работы с SQL используется класс Pdo\PdoQuery. Он состоит из static-функций, поэтому нет необходимости инстанцировать сам класс: сразу указываем нужный метод. Методы как правило имеют несколько параметров, где первый — это объект PDO.

Класс состоит из:

• execute() — аналог из PDO
• query() — аналогично из PDO
• fetchAll() — аналог из PDO
• insert() — для более удобного выполнения INSERT (с prepare)
• insertSql() — INSERT, который формируется для query() как «чистый» SQL-запрос
• update() — более удобная работа с UPDATE
• delete() — для работы с DELETE FROM
• tableExists() — хелпер, который проверяет существование таблицы (через запрос к sqlite_master)
• dropTable() — хелпер для удаления таблицы (DROP TABLE IF EXISTS)
• countRecord() — возвращает общее количество записей в таблице (SELECT COUNT(*) as count FROM)
• getPagination() — вспомогательная функция, которая возвращает массив для пагинации.
• outTableRows() — можно вывести все данные из полученного результата запроса.

Покажу несколько примеров.

После того, как установлено соединение, можно проверить существование таблицы.

if (!Pdo\PdoQuery::tableExists($db, 'myPages')) {
   $sql = 'CREATE TABLE myPages (id INTEGER PRIMARY KEY AUTOINCREMENT, ... );';
   Pdo\PdoQuery::query($db, $sql);
}

Здесь мы используем tableExists для проверки. Если метод вернул false, то выполняем query() с запросом на создание таблицы.

Но на самом деле проверку можно переложить прямо в SQL-запрос.

Pdo\PdoQuery::query($db, "
CREATE TABLE IF NOT EXISTS myPages (
   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
   title TEXT NOT NULL,
   content TEXT NOT NULL DEFAULT '',
   status TEXT NOT NULL DEFAULT 'publish',
   date TEXT NOT NULL DEFAULT '',
   level INTEGER NOT NULL DEFAULT 0
);
");

Обратите внимание, что для строк в php-функции я использую двойные кавычки. Это из-за того, чтобы в самом sql-запросе использовать одинарные (обычно так принято).

Чтобы удалить таблицу можно использовать специальный метод dropTable().

Pdo\PdoQuery::dropTable($db, 'myPages');

Либо обычный sql-запрос:

Pdo\PdoQuery::query($db, "DROP TABLE IF EXISTS myPages;");

Нетрудно заметить, что с помощью query() можно выполнять любые SQL-запросы, где не требуется предварительная подготовка. Скажем так можно добавить данные:

Pdo\PdoQuery::query($db, "
INSERT INTO myPages (title, status, content, date) VALUES
   ('Первая запись', 'publish', 'Текст первой записи', datetime('now', 'localtime')),
   ('Вторая запись', 'draft', 'Текст второй записи', datetime('now', 'localtime')),
   ('Третья запись', 'publish', 'Текст третьей записи', datetime('now', 'localtime')),
   ('Четвёртая запись', 'publish', 'Текст четвёртой записи', datetime('now', 'localtime')),
   ('Пятая запись', 'draft', 'Текст пятой записи', datetime('now', 'localtime')),
   ('Шестая запись', 'publish', 'Текст шестой записи', datetime('now', 'localtime')),
   ('Седьмая запись', 'publish', 'Текст седьмой записи', datetime('now', 'localtime')),
   ('Восьмая запись', 'draft', 'Текст восьмой записи', datetime('now', 'localtime'))
;");

Этот же самый запрос можно выполнить хоть вручную — он будет работать одинаково. С точки зрения php-программиста такой вариант не самый удобный, поэтому можно использовать insertSql(), который будет принимать массив параметров для INSERT:

Pdo\PdoQuery::insertSql($db,  'myPages', [
    'title' => "'Девятая запись'",
    'status' => "'publish'",
    'content' => "'Текст девятой записи'",
    'date' => "datetime('now', 'localtime')",
    'level' => 1,
]);

Обратите внимание на кавычки — данная функция сформирует текст запроса для INSERT, только на основе php-массива. А кавычки следует соблюдать ровно с тем же правилом, что и для обычного SQL.

Если же говорить о наиболее «правильном» варианте, то нужно использовать метод insert(), который «прогоняет» данные через pdo-prepare:

Pdo\PdoQuery::insert($db,  'myPages', [
    'title' => "Десятая запись",
    'status' => "publish",
    'content' => "Текст десятой записи",
    'date' => date("Y-m-d H:i:s"),
]);

Такой вариант наиболее безопасный. Если же в запрос добавляются данные от пользователей, то и единственно верный.

Если нужно добавить сразу несколько данных, то они указываются в виде массивов:

Pdo\PdoQuery::insert($db,  'mytable', 
   ['field1' => $value, 'field2' => $value],
   ['field1' => $value, 'field2' => $value],
   ['field1' => $value, 'field2' => $value],
);

Для обновления данных используется метод update(). Поскольку он сам по себе достаточно своеобразный, то мы используем два массива: первый для указания обновляемых полей и второй — непосредственные данные:

// обновим данные 10-й записи (указывается в id)
Pdo\PdoQuery::update($db, 'myPages',
    [ // какие поля нужно обновить
		'title',
		'content',
		'level'
	],
    [ // данные для полей
        'id' => 10, // используется во WHERE
        'title' => '10-я запись',
        'content' => 'Текст 10-й записи',
        'level' => 2,
    ],
    'id = :id' // условие WHERE
);

Последний параметр — это данные для условия WHERE в формате именованных псевдопеременных PDO.

Теперь рассмотрим функцию fetchAll(), которая возвращает результат выборки.

// сделаем выборку всех данных
$rows = Pdo\PdoQuery::fetchAll($db, 'SELECT * FROM myPages');
 
// выведем на экран
echo Pdo\PdoQuery::outTableRows($rows);

Здесь мы используем SELECT, а результат сохраняем в $rows — это обычный ассоциированный массив, который можно пропустить через foreach(). Но пока мы воспользуемся outTableRows(), которая просто выводит этот массив в виде html-таблицы. Для отладки этот вариант намного наглядней, чем print_r() или отладочной функции pr().

Теперь сделаем выборку через именованные параметры:

// сделаем выборку по условиям через PDO prepare
$id = 7;
$rows = Pdo\PdoQuery::fetchAll($db, 'SELECT * FROM myPages WHERE id = :id', [':id' => $id]);
 
// выведем на экран результат
echo Pdo\PdoQuery::outTableRows($rows);

Здесь переменную $id мы передаём в запрос с помощью именованного параметра :id. PDO автоматом проверит эти данные.

Получить данные можно и через query(). Его можно использовать для случаев, когда в запрос не передаются данные от пользователя.

$rows = Pdo\PdoQuery::query($db, 'SELECT id, title FROM myPages WHERE id > 2 AND id < 5');
echo Pdo\PdoQuery::outTableRows($rows);

Вывод результата происходит как правило через foreach()

foreach($rows as $row) {
   echo '<div>' . $row['id'] . ' : ' . htmlspecialchars($row['title']) . '</div>';
 
   // pr($row); // для отладки
}

Но если уже нужны именованные параметры, то используем уже execute(). В отличие от fetchAll() этот метод не возвращает набор строк:

Pdo\PdoQuery::execute($db, 'SELECT * FROM myPages WHERE id = :id', [':id' => $id]);

Такое использование немного странное, поскольку запрос имеет смысл только с возвратом данных, но с точки зрения PDO ошибок нет.

Использование «родного» PDO

В некоторых случаях может потребоваться использовать PDO напрямую. В Albireo с этим нет никаких ограничений. Например если стоит задача использовать bindValue() (для привязки параметров к запросу), то можно работать с PDO напрямую:

$id = 8; // какие-то данные
 
try {
   $sth = $db->prepare('SELECT * FROM myPages WHERE id = :id'); // готовим sql-запрос
   $sth->bindValue(':id', $id, \PDO::PARAM_INT); // привязываем параметры
   $sth->execute(); // выполняем запрос
   $rows = $sth->fetchAll(); // получаем результат
 
   echo Pdo\PdoQuery::outTableRows($rows); // выводим для контроля
} catch (\PDOException $e) {
   echo $e->getMessage());
}

Здесь, кстати, используется bindValue() для случаев, если нужно указать тип передаваемых данных. В данном случае — это число. По умолчанию используется PDO::PARAM_STR, то есть параметры передаются в виде строк, что может вызвать некоторые вопросы. На самом же деле тип данных во всех случаях именно строковый, поскольку любой SQL-запрос — это текст. Но преобразование в нужный тип данных происходит уже на уровне драйвера базы данных. Поэтому в общем-то нам нет особого смысла задумываться какой тип передаётся в PDO. База данных сама определит и преобразует входящие данные в правильный тип, который определён в полях таблицы.

Более того, преобразование типа переменно можно выполнить стандартными средствами PHP.

Данный код, при желании можно переписать с использованием fetchAll() из Albireo Framework:

$id = 1;
 
$rows = Pdo\PdoQuery::fetchAll($db, 
	'SELECT * FROM myPages WHERE id = :id', // sql-запрос
	[':id' => $id], // параметры
	[':id' => \PDO::PARAM_INT] // типы параметров
);
 
echo Pdo\PdoQuery::outTableRows($rows);

Здесь используется дополнительный массив, где указываются типы данных параметров.

Пагинация

Пагинация или «листалка» страниц реализуется с помощью дополнительных запросов к базе. Чтобы корректно вывести пагинацию нужно знать несколько параметров: всего записей, количество на одну страницу, текущая страница пагинации и т.д. Эти цифры используются в SQL-командах LIMIT и OFFSET.

В Albireo для этого используется вспомогательная функция getPagination():

$current = 1; // номер текущей страницы пагинации начиная с 1
$limit = 10; // записей на одну страницу пагинации
$pag = Pdo\PdoQuery::getPagination($db, 'myPages', $limit, $current); // получаем нужные цифры
 
// используем в запросе 
$rows = Pdo\PdoQuery::fetchAll($db, 
   'SELECT * FROM myPages LIMIT :limit OFFSET :offset', 
   [':limit' => $limit, ':offset' => $pag['offset']]
);
 
echo Pdo\PdoQuery::outTableRows($rows);

В $current указывается текущая страница пагинации. Она может быть в get-параметре страницы. Количество записей на странице указывается в $limit. После getPagination() получаем массив вида:

 Array
 (
   [limit] => 10 - записей на одну страницу пагинации
   [offset] => 70 - смещение offset для sql-запроса
   [records] => 85 - всего записей
   [current] => 8 - текущая страница пагинации
   [max] => 9 - всего страниц пагинации
 )

Для sql-запроса нужны будут $pag['offset'] и $limit. Остальные данные массива могут использоваться для формирования html-кода для ссылок навигации.

Итого

В Albireo Framework в комплекте поставки есть демо-файл, в котором я собрал все примеры по работе с SQLite. Он располагается в albireo-data/pages/sample/sqlite.php и по умолчанию доступен по адресу ваш-сайт/sqlite.

В этом файле я разместил и пример пагинации с блоком навигации. Если вы используете Albireo, то эти примеры помогут вам разобраться как работать PDO.