Простое использование SQLite

09-02-2016Время чтения ~ 5 мин.PHP, SQL 25549

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 может пригодится для небольших задач, которые требуют небольшой базы данных: счетчики, голосования, мета-данные страниц и т.п.