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

ДЕНИС КОЛИСНИЧЕНКО

Использование SQLite и PHP 5
SQLite – альтернатива MySQL

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

Наибольшую популярность и распространенность среди веб-разработчиков давно и заслуженно обрел сервер баз данных MySQL. Он бесплатен, прост в установке, входит в состав большинства дистрибутивов Linux, почти наверняка окажется на сервере вашего хостинг-провайдера. Это – рабочая лошадка Web.

Долгое время поддержка работы с MySQL была по умолчанию встроена в интерпретатор PHP. Но не все так безоблачно. Сейчас почти повсеместно используется PHP 4-й версии, но не за горами выход следующей, пятой версии. И вот тут ситуация может измениться.

Из-за возникающих лицензионных проблем поддержка MySQL скорее всего не будет включена в PHP 5 по умолчанию. Так, из бета-версии PHP 5 уже удалена встроенная поддержка MySQL, зато появилась поддержка SQLite.

Замечание: речь идет о встроенной поддержке по умолчанию. Возможность собрать PHP с поддержкой MySQL остается по-прежнему, но требует «дополнительных телодвижений». А это, согласитесь, сделает не каждый.

MySQL всегда был ориентирован на быстроту, при этом частично жертвуя дополнительными возможностями. Разумеется, в проектах, предполагающих более сложную структуру базы данных, строгий контроль целостности и сложные запросы к базе данных, стоит использовать более развитые СУБД, например, PostgreSQL или Sybase. Но большинство малых и средних проектов, таких как интернет-каталог, обновляемый прайс-лист или веб-форум, вполне позволяют обойтись компактным, слегка ограниченным, но быстрым сервером баз данных. Традиционно таким сервером и был MySQL. Подойдет ли SQLite в качестве достойной замены?

Как любой программный продукт, SQLite обладает своими преимуществами и недостатками. Среди его преимуществ бесплатность и высокая производительность, помните о «трех китах» веб-программирования? Но ничто не дается даром. Производительность SQLite достигается не столько за счет уникального алгоритма, сколько из-за определенных ограничений его возможностей.

Традиционные системы управления базами данных построены на архитектуре клиент-сервер. Это значит, что клиент-приложение обращается к серверу-хранилищу данных с запросом для получения данных или для выполнения каких-либо операций. Сервер выполняет нужные операции и возвращает программе-клиенту результат своей работы. Такой подход предполагает возможность размещать приложение и хранилище данных на разных компьютерах, разделяя нагрузку на аппаратные ресурсы.

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

Перечислю основные характеристики SQLite:

• соответствует стандарту SQL-92. Правда, не полностью: некоторые функции не поддерживаются, но этим «грешил» и MySQL;
• база данных (включая таблицы и индексы) хранится в одном едином файле;
• максимальный размер файла базы данных – 2 Тб, т.е. суммарный размер базы данных, включая таблицы данных и индексы, не может превышать 2 Тб;
• существует реализация для платформ Linux и Windows;
• файлы баз данных имеют универсальный формат и могут быть перенесены из одной операционной системы в другую без потерь и дополнительных преобразований. Т.е. база данных, созданная на компьютере под управлением ОС Linux, может быть просто скопирована на компьютер с ОС Windows и наоборот. Это большой «плюс»;
• примерно в два раза быстрее, чем PostgreSQL и MySQL.

Результаты сравнительного тестирования рассмотрим в конце статьи, а пока поговорим об установке и функциональных возможностях SQLite.

Установка SQLite

Пятая версия PHP обладает встроенной поддержкой SQLite, поэтому вам не нужно производить никаких дополнительных действий по подключению SQLite к PHP5: просто загрузите SQLite и работайте.

Скачать SQLite можно по адресу:

• http://pecl.php.net/package/SQLite
• http://www.sqlite.org

Поддержка SQLite для Windows реализована традиционно для PHP, в виде единой библиотеки – php_sqlite.dll, скачать которую вы можете по адресу http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll

Подключение SQLite проблем не вызывает, поэтому подробно останавливаться на нем мы не будем. Если вы работаете под ОС Windows, то скачайте библиотеку php_sqlite.dll, скопируйте в каталог для модулей расширений PHP и «пропишите» его в файле конфигурации php.ini:

extension=php_sqlite.dll

Каталог модулей расширений задается директивой «extension_dir» файла конфигурации. php.ini. По умолчанию это подкаталог «extensions» каталога, в который вы установили PHP.

Для установки SQLite для Linux скачайте файл sqlite.tar.gz (http://www.sqlite.org/sqlite.tar.gz). Затем выполните следующую последовательность команд:

#  Распаковываем архив в каталог «sqlite»

tar xzf sqlite.tar.gz

#  Создаем каталог для сборки SQLite – «bld»

mkdir bld

#  Переходим в этот каталог

cd bld

#  Запускаем конфигуратор

../sqlite/configure

#  Запускаем make для сборки SQLite

make

Указанная последовательность действий необязательна. Например, вам необязательно создавать отдельный каталог для сборки. Однако я настоятельно рекомендую это сделать, чтобы исходные коды не «перемешались» с откомпилированными модулями SQLite.

Сценарий конфигуратора configure для своей работы использует autoconf 2.50 и libtool, поэтому проследите, чтобы до запуска конфигуратора эти пакеты были установлены в вашей системе.

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

После сборки SQLite запустите сценарий install-sh для установки SQLite, вспомогательных программ и сценариев, а также базы данных. Вспомогательными утилитами являются:

• showdb – утилита для просмотра базы данных;
• showjournal – утилита для просмотра журнала базы данных;
• diffdb – утилита сравнения двух баз данных.

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

Основные функции SQLite

В SQLite существуют аналогичные MySQL константы, отвечающие за формирование результата выборки из базы данных:

• SQLITE_ASSOC – результат будет возвращаться в виде ассоциативного массива.
• SQL_NUM – результат будет возвращен в виде списка, то есть ключи массива – числа.
• SQL_BOTH – двойное индексирование элементов массива: в зависимости от вашего желания вы можете работать с массивом так, если бы он был ассоциативным, и так, если бы он был списком.

Эти константы аналогичны константам MYSQL_ASSOC, MYSQL_BOTH, MYSQL_NUM для MySQL.

Вот список основных функций для работы с SQLite, приведенный в алфавитном порядке.

• sqlite_array_query – выполняет SQL-запрос и возвращает результат в виде массива, тип которого задан вышеупомянутыми константами;
• sqlite_busy_timeout – устанавливает тайм-аут;
• sqlite_changes – возвращает число записей, которые были изменены в результате выполнения последнего SQL-запроса;
• sqlite_close – закрывает базу данных;
• sqlite_column – получает одно поле из записи, то есть возвращает одну конкретно указанную колонку результата;
• sqlite_current – получает текущую запись из результата;
• sqlite_error_string – возвращает текстовое описание ошибки;
• sqlite_fetch_array – получает следующую запись из массива-результата;
• sqlite_fetch_single – возвращает первую колонку (поле) записи;
• sqlite_fetch_string –псевдоним для sqlite_fetch_single;
• sqlite_field_name – возвращает имя поля;
• sqlite_has_more – позволяет узнать, есть ли еще записи;
• sqlite_last_error – возвращает код последней ошибки;
• sqlite_last_insert_rowid – возвращает идентификатор последней добавленной записи;
• sqlite_libversion – возвращает версию SQLite;
• sqlite_next – переходит к следующей записи;
• sqlite_num_fields – возвращает число полей (колонок) в результате;
• sqlite_num_rows – возвращает число записей в результате;
• sqlite_open – открывает базу данных;
• sqlite_popen – открывает «постоянное» соединение с базой данных. Если база данных не существует, она будет создана;
• sqlite_query – выполняет SQL-запрос и возвращает идентификатор результата;
• sqlite_rewind – переходит к первой записи.

Остановимся на этих функциях подробнее.

Открытие и закрытие базы данных

Для открытия базы данных используется функция sqlite_open():

resource sqlite_open ( string filename [, int mode [, string &error_message]])

Первый параметр – это имя файла таблицы, второй – режим доступа к таблице. По умолчанию используется режим 0666, позволяющий читать и записывать файл всем желающим – владельцу (то есть вам), вашей группе и всем остальным пользователям. Последний параметр – это строка, в которую будет записано сообщение об ошибке, если базу данных невозможно открыть.

В случае успеха функция возвращает дескриптор базы данных, иначе – значение FALSE («ложь»).

Для того чтобы закрыть базу данных, используется функция sqlite_close():

sqlite_close( resource dbhandle )

В качестве её аргумента следует указать дескриптор соответствующей базы данных.

Замечание: несмотря на то, что механизм работы предполагает автоматическое закрытие используемых ресурсов после выполнения сценария, принудительное закрытие базы данных – хороший тон программирования. Не следует пренебрегать этой операцией. Автоматическая «сборка мусора» и закрытие ресурсов могут произойти не сразу. Это может породить некоторые неожиданные коллизии, которые довольно сложно будет отлавливать. Исключение – использование постоянного соединения с базой данных.

Еще один способ получить доступ к базе данных – функция sqlite_popen():

resource sqlite_popen ( string filename [, int mode [, string &error_message]])

Эта функция открывает «постоянное» соединение с базой данных, её аргументы аналогичны аргументам функции sqlite_open().

Функция sqlite_popen() использует механизм постоянных ресурсов PHP. Вкратце это выглядит так: вы открываете постоянное соединение с базой данных, работаете, потом работа сценария завершается. При этом если вы не указали принудительно, закрытия и освобождения ресурса не происходит. То есть, при следующем запуске сценария функция popen() попытается найти открытое постоянное соединение и, если оно существует, будет его использовать, а не открывать базу данных заново. При частых запусках сценария это позволяет повысить его производительность, так не тратится время на открытие базы.

Однако при большом количестве одновременных обращений к базе данных взамен этой экономии вы можете получить накопление открытых соединений с базой данных, т.е. увеличить нагрузку на внешние ресурсы системы. Второй неприятный момент, с которым вы можете тут столкнуться – некорректное завершение сценария, при котором могут оставаться потерянные соединения с базой данных. В общем, выбор между этими двумя способами требует вдумчивого подхода и некоторого анализа предполагаемой работы вашего приложения.

Выполнение запросов

Для выполнения запросов к базе данных используется функция sqlite_query():

resource sqlite_query ( resource dbhandle, string query [,int result_type])

resource sqlite_query ( string query, resource dbhandle [,int result_type])

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

Листинг 1. Открытие и закрытие базы данных

// открываем базу данных или завершаем выполнение с сообщением об ошибке

if ( !$db = sqlite_open("mysqlitedb", 0666, $sqliteerror)) {

    die ($sqliteerror);

}

// создаем таблицу tbl

sqlite_query($db,"CREATE TABLE tbl (bar varchar(20))");

// добавляем в таблицу новые записи

sqlite_query($db,"INSERT INTO tbl VALUES ("val")");

// получаем результат в виде ассоциативного массива

$result = sqlite_query($db,"select bar from tbl", SQLITE_ASSOC);

// выводим результат

var_dump(sqlite_fetch_array($result));

?>

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

Функции для работы с результатом

Функция sqlite_column() используется для получения только одной колонки из всего результата выборки:

mixed sqlite_column ( resource result, mixed index_or_name [, bool decode_binary])

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

Функция sqlite_fetch_single() возвращает первое поле текущей записи:

string sqlite_fetch_single ( resource result [, int result_type [, bool decode_binary]])

Первый параметр – это результат, а второй – тип результата. Данная функция похожа на функцию sqlite_fetch_array(), но возвращает только первое поле, а не все поля текущей записи сразу.

Листинг 2. Функция sqlite_fetch_single

if ($dbhandle = sqlite_open("mysqlitedb", 0666, $sqliteerror)) {

    $sql = "SELECT id FROM tbl WHERE id = 77";

    $res = sqlite_query($dbhandle, $sql);

    if (sqlite_num_rows($res) > 0) {

// печатаем значение «77»

           echo sqlite_fetch_single($res);

    }

    sqlite_close($dbhandle);

}

?>

Функция sqlite_array_query() выполняет SQL-запрос и возвращает результат в виде массива:

array sqlite_array_query ( resource dbhandle, string query [, int result_type [, bool decode_binary]])

Первый параметр – это дескриптор базы данных, второй – запрос, третий – тип результата (см. константы SQLite), а третий – это флаг кодирования данных.

Функция sqlite_next() перемещает указатель результата на следующую запись:

bool sqlite_next ( resource result)

Параметр result – это результат. Если функция вернула FALSE, значит, достигнута последняя запись.

Функция sqlite_rewind перемещает указатель результата на первую запись:

bool sqlite_rewind ( resource result)

Функция sqlite_seek() переходит к записи с указанным номером:

bool sqlite_seek ( resource result, int rownum)

Если функция возвращает FALSE, значит, нет записи с таким номером.

Информационные функции

Функция sqlite_changes() возвращает число записей, которые были изменены в результате выполнения последнего SQL-запроса:

int sqlite_changes ( resource dbhandle)

Функция sqlite_field_name() возвращает имя поля по его номеру:

string sqlite_field_name ( resource result, int field_index)

Функция sqlite_has_more() возвращает TRUE, если результат еще содержит данные, иначе возвращается FALSE:

bool sqlite_has_more ( resource result)

Функция sqlite_last_error() возвращает код последней ошибки:

int sqlite_last_error ( resource dbhandle)

а функция sqlite_error_string() – ее текстовое описание:

string sqlite_error_string( resource dbhandle )

Функции sqlite_num_rows() и sqlite_num_fields() возвращают, соответственно, число записей и полей в результате:

int sqlite_num_rows(resource dbhandle)

int sqlite_num_fields(resource dbhandle)

Другие функции

Функция sqlite_busy_timeout() позволяет установить тайм-аут ожидания для базы данных:

void sqlite_busy_timeout ( resource dbhandle, int milliseconds)

Напомню, что 1 секунда – это 1000 миллисекунд.

Результаты сравнительного тестирования

В первой части статьи, говоря об основных характеристиках SQLite, я упомянул о том, что SQLite в два раза быстрее PostgreSQL и MySQL.

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

процессор – AMD 1.6 ГГц Athlon;

объем оперативной памяти – 1 Гб;

жесткий диск EIDE;

операционная система – Red Hat Linux 7.2.

Для сравнения использовались PostgreSQL версии 7.1.3 и MySQL версии 3.23.41. Версия SQLite, используемая для тестирования, – 2.7.6.

Первый тест – вставка 1000 записей:

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));

INSERT INTO t1 VALUES(1,13153,"thirteen thousand one hundred fifty three");

INSERT INTO t1 VALUES(2,75560,"seventy five thousand five hundred sixty");

... 995 строк пропущено

INSERT INTO t1 VALUES(998,66289,"sixty six thousand two hundred eighty nine");

INSERT INTO t1 VALUES(999,24322,"twenty four thousand three hundred twenty two");

INSERT INTO t1 VALUES(1000,94142,"ninety four thousand one hundred forty two");

Диаграмма 1: Результаты первого теста (в секундах)

Второй тест – выборка без индексов:

BEGIN;

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b

SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b

... 96 строк пропущено

SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b

SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b

COMMIT;

Диаграмма 2: Результаты второго теста

Третий тест – выборка со сравнением строки:

BEGIN;

SELECT count(*), avg(b) FROM t2 WHERE c LIKE "%one%";

SELECT count(*), avg(b) FROM t2 WHERE c LIKE "%two%";

... 96 строк пропущено

SELECT count(*), avg(b) FROM t2 WHERE c LIKE "%ninety nine%";

SELECT count(*), avg(b) FROM t2 WHERE c LIKE "%one hundred%";

COMMIT;

Диаграмма 3: Результаты третьего теста

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

А теперь попробуем оправдать наши ожидания и показать, что SQLite быстрее в два раза, чем MySQL. В качестве тестового используем запрос, состоящий из 25 000 операторов обновления (UPDATE):

Четвертый тест – обновление числовых полей:

BEGIN;

UPDATE t2 SET b=468026 WHERE a=1;

UPDATE t2 SET b=121928 WHERE a=2;

... 24996 строк пропущено

UPDATE t2 SET b=35065 WHERE a=24999;

UPDATE t2 SET b=347393 WHERE a=25000;

COMMIT;

Диаграмма 4: Результаты четвертого теста

Вот тут преимущество SQLite налицо!

Этот тест обновлял только целые поля. Согласитесь – действие несколько надуманное и не такое уж частое. Посмотрим, что будет, если нам нужно обновить 25 000 текстовых полей:

Пятый тест – обновление тестовых полей:

BEGIN;

UPDATE t2 SET c="one hundred forty eight thousand three hundred eighty two" WHERE a=1;

UPDATE t2 SET c="three hundred sixty six thousand five hundred two" WHERE a=2;

... 24996 строк пропущено

UPDATE t2 SET c="three hundred eighty three thousand ninety nine" WHERE a=24999;

UPDATE t2 SET c="two hundred fifty six thousand eight hundred thirty" WHERE a=25000;

COMMIT;

Диаграмма 5: Результат пятого теста

Если сравнивать с предыдущим тестом, в котором SQLite проявил себя «шустрее» в 2,6 раза по сравнению с MySQL, то на этот раз SQLite оказался «проворнее» почти в три раза (2,899).

Приведем общую таблицу результатов тестирования, для того чтобы более наглядно оценить производительность SQLite.

Таблица 1. Общая таблица результатов сравнительного тестирования SQLite

Более подробно о сравнительных результатах тестирования вы можете прочитать на сайте продукта: http://www.sqlite.org.

Конечно, переход на SQLite потребует некоторых усилий и изменений. Придется отказаться от автоматического инкремента при создании таблиц, возможности сложного индексирования здесь также не предусмотрены.

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

Комментарии могут оставлять только зарегистрированные пользователи