Простое использование SQLite
09-02-2016Reading time ~ 5 min.PHP, SQL 25659
SQLite — это база данных, чем-то похожая на MySQL. Принципиальное отличие SQLite от других БД в том, что вся база представляет собой один файл. Если в MySQL база хранится где-то в дебрях сервера и недоступна для переноса, то в SQLite с этим всё до безобразия просто: один файл — одна база.
Конечно же, сервер должен поддерживать драйвер SQLite (также как и любой другой БД), но как правило сейчас с этим проблем нет.
SQLite позволяет привычно работать с базой через SQL, создавать таблицы, поля и т.д. В целом можно сказать, что SQLite ни в чем не уступает привычной MySQL, за исключением, пожалуй более медленной работы с «тяжелыми» sql-запросами по обновлению данных (insert и update). Но, опять же, это для высоконагруженных сайтов.
Огромным плюсом SQLite будет её легкая переносимость. Скопировать файл — что может быть проще? Не нужно заботиться о бэкапах, как в MySQL, не нужно создавать на сервере пользователя с паролем, не нужно создавать саму базу. С SQLite просто берём и пользуемся.
Для работы с базой данных в PHP лучше использовать PDO — Объекты данных PHP — это т.н. абстракция, которая предлагает единый интерфейс для работы с разными базами. В теории, используя PDO, можно переключиться на любую базу, не переделывая SQL-запросы, например с MySQL на SQLite. Меняются только параметры подключения.
Таким образом SQLite будет подключаться через PDO. Для этого ничего не требуется, поскольку сам PDO уже в составе PHP, а драйвер SQLite, как правило, также включен на серверах.
Но, прежде, чем приступать к программированию, нужно создать саму базу. Например для MySQL существует phpMyAdmin, через который можно выполнять различные операции. Для SQLite также есть похожие разработки, но я покажу, как можно это делать через браузер FireFox. Для этого потребуется только установить дополнение SQLite Manager.
Чтобы добавить это дополнение в основное меню FireFox («гамбургер»), нажмите Изменить и перетащите мышью икноку в меню.
На этом SQLite Manager установлен и можно им пользоваться.
Для начала создадим новую базу данных. В SQLite — это отдельный файл, который будет иметь расширение .sqlite
. SQLite Manager предложит указать каталог, где будет храниться этот файл. Выберите или создайте новый каталог. Для нас это пока не имеет особого значения. В результате будет создан sqlite-файл с новой базой.
Этот файл можно перемещать (и переименовывать) куда угодно, а после открывать командой меню Базы данных - Подключить базу данных.
Теперь нужно создать таблицу (или таблицы) в базе данных.
SQLite Manager автоматом создаёт служебные таблицы sqlite_XXX. Мы их не трогаем и нам они не мешают.
Таблица в БД — это то место, где хранится структурированная информация. Таблица должна иметь набор полей с заданными свойствами. Например поле может быть integer — для целых чисел, или text — для текста. Количество полей может быть произвольным и определяется только задачей вебмастера.
Пусть, например, у нас будет таблица pages
с полями
- id — уникальный номер (автоинкремент)
- slug — ссылка
- text — произвольный текст
- hits — число просмотров
После того, как таблица создана, обратите внимание на блок «SQL-оператор создавший этот объект». В нем будет SQL-запрос, которым можно создать таблицу. Он может пригодится, если требуется создать таблицу в базе через PHP.
На вкладке «Просмотр и Поиск» можно редактировать таблицу. Создадим для примера две строчки, где поле slug будет home
и contact
. Это будут две страницы: главная и сайт/contact.
Поле hits будет содержать счетчик просмотров страницы. Текст может быть любым.
Всё, база готова, теперь можно её использовать.
Поставим задачу. Пусть у нас будет простенький сайт, который будет выдавать по короткой ссылке (slug) соответствующий текст и количество просмотров.
Если мы делаем это на локальном сервере, то пусть сайт будет в каталоге sqlite. В нём подкаталог db, куда мы и скопируем наш pages.sqlite.
Роутинг мы можем сделать, как описано в предыдущей статье PHP-роутинг (Routing) для новичков. Файл .htaccess
AddDefaultCharset UTF-8 Options -Indexes <IfModule mod_rewrite.c> RewriteEngine on RewriteBase /sqlite/ RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule (.*) /sqlite/index.php?$1 [QSA,L] </IfModule>
В index.php сам роутинг будет описан одной строчкой:
$page = ($p = key($_GET)) ? $p : 'home';
То есть $page
будет содержать ссылку или home
для главной.
Дальше алгоритм будет такой:
- подключаем базу
- делаем в ней выборку по
$page
- выводим полученные данные
Я намеренно упрощаю алгоритм, чтобы не усложнять php-код.
Существуют два варианта работы с БД. Первый — это нативный php-код. Он не очень сложный, но обилие параметров немного напрягает. Поэтому второй вариант — использование дополнительных библиотек-оберток. С ними код становится лаконичней.
Приведу код index.php
в первом варианте:
<?php $page = ($p = key($_GET)) ? $p : 'home'; try { $pdo = new PDO('sqlite:db/pages.sqlite'); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $sql ='SELECT * FROM pages WHERE slug=:page LIMIT 1'; $sth = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(':page' => $page)); $rows = $sth->fetchAll(); print_r($rows); // здесь выводим данные } catch(Exception $e) { echo $e->getMessage(); } # end of file
Для второго варианта я использовал php-библиотеку с сайта labaka.ru, которую разместил в подкаталог lib.
Код index.php
:
<?php $page = ($p = key($_GET)) ? $p : 'home'; require 'lib/sqlite.php'; try { $db = new Db('db/pages.sqlite'); $row = $db->queryRow('SELECT * FROM pages WHERE slug=:page LIMIT 1', array(':page' => $page)); if ($row) { echo $row['text']; echo '<br>Просмотров: ' . $row['hits'] . '<br>'; $db->update('pages', array('hits' => $row['hits'] + 1), 'id=:id', array(':id' => $row['id'])); } } catch(Exception $e) { echo $e->getMessage(); } # end of file
Данный пример будет выводить для разных адресов разный текст и при этом будет работать счетчик просмотров. Если требуется добавить новую страницу, то её достаточно создать в базе. Для удобства, в SQLite Manager можно подключить базу прямо из каталога сайта.
Здесь следует отметить пару важных моментов.
В первую очередь, вся работа с PDO должна заключаться в блок try..catch
. Таким способом отлавливаются сообщения об ошибках.
Данные, которые отправляются в sql-запрос должны проходить через валидацию. В PDO, когда используется предподготовка данных (PDO::prepare), выполняется принудительное экранирование параметров. Это позволяет защититься от возможных SQL-инъекций.
Когда происходит подключение базы данных SQLite, в случае отсутствия файла базы, он будет создан автоматически. На этом базируется создание базы сразу после его подключения и создание нужных таблиц первым sql-запросом (о котором я написал выше).
Еще одно замечание по SQLite. Поскольку база это файл, то его можно скачать по URL прямо через браузер. Поэтому каталог с SQLite-файлами лучше защищать через .htaccess
строчкой Deny from all
. Или же размещать выше чем основной www-каталог.
SQLite может пригодится для небольших задач, которые требуют небольшой базы данных: счетчики, голосования, мета-данные страниц и т.п.