Оригинал статьи доступен по адресу
http://www.zend.com/php5/articles/php5-sqlite.php
Автор: Ilia Alshanetsky <ilia@prohost.org.>
Для кого эта статья
Данная статья предназначается PHP программистам, проявившим интерес к
расширению SQLite. Статья представит читателю функциональность,
предлагаемую расширением PHP SQLite, а также рассмотрит преимущества
SQLite в сравнении с другими БД. Предполагается, что читатель знаком с
основами PHP и SQL. Некоторый опыт работы с MySQL или PostgreSQL ,
будет способствовать лучшему пониманию того, о чём пойдёт речь.
Введение
В последнее время, вы, возможно, слышали о новом расширении для PHP:
SQLite. Есть много причин, по которым SQLite может показаться лучшим
достижением с тех пор, как научились резать хлеб. SQLite предлагает
добротный SQL интерфейс к нерелятивистской базе данных и создаёт
элегантную альтернативу громоздким интерфейсам других баз данных без
потери функциональности или скорости, как можно было бы ожидать. Мы
рассмотрим в статье это удивительное расширение и, будем надеяться,
нам удастся подтвердить те преимущества, о которых, возможно, вы
наслышаны.
Что такое SQLite?
SQLite - это встраиваемая библиотека в которой реализовано многое из
стандарта SQL 92. Её притязанием на известность является как
собственно сам движок базы, так и её интерфейс (точнее его движок) в
пределах одной библиотеки, а также возможность хранить все данные в
одном файле. Я отношу позицию функциональности SQLite где-то между
MySQL и PostgreSQL. Однако, на практике, SQLite не редко оказывается в
2-3 раза (и даже больше) быстрее. Такое возможно благодаря
высокоупорядоченной внутренней архитектуре и устранению необходимости
в соединениях типа <<сервер-клиент>> и <<клиент-сервер>>.
Всё это, собранное в один пакет, лишь немногим больше по размеру
клиентской части библиотеки MySQL, является впечатляющим достижением
для полноценной базы данных. Используя высоко эффективную
инфраструктуру, SQLite может работать в крошечном объёме выделяемой
для неё памяти, гораздо меньшем, чем в любых других системах БД. Это
делает SQLite очень удобным инструментом с возможностью использования
практически в любых задачах возлагаемых на базу данных.
Что мне проку от SQLite?
Помимо скорости и эффективности у SQLite есть ряд других преимуществ,
которые делают её идеальным решением для многих задач. Так как база
данных SQLite по сути своей - обычные файлы, нет ни какой
необходимости в дополнительных средствах администрирования требующих
много времени на создание сложной структуры прав доступа для защиты
пользовательских баз данных. Всё это уже автоматически поддерживается
организацией прав доступа в самой файловой системе, это также
подразумевает (в области ограничения пространства), что не требуется
ни каких специальных правил для отслеживания заполнения дискового
пространства пользователем. Преимущество для пользователей в том, что
есть возможность создать такое количество баз данных, какое они себе
пожелают плюс абсолютный контроль над всеми этими базами данных.
Факт, что база данных - это единственный файл, делает её легко
переносимой. SQLite к тому же, устраняет необходимость в запуске
дополнительных служебных процессов (daemons), которые могли бы
<<отъедать>> значительное количество памяти и других ресурсов, даже в
случае умеренного использования базы данных.
Расширение SQLite
В качестве новейшего расширения БД, SQLite посчастливилось быть
свободным от кода для обратной совместимости, в противоположность
более старым расширениям, таким как MySQL , которые обязаны
поддерживать устаревшую функциональность по причинам обратной
совместимости. Это также позволяет новому расширению использовать
самые новейшие разработки в PHP для достижения самого высокого уровня
исполнения и функциональности. Разработчики облегчили задачу
пользователям, создав расширение лёгким для перехода к нему от других
систем БД, оставив интерфейс похожим на те, что уже были реализованы в
PHP.
SQLite также поддерживает гибкий механизм для передачи ресурсов базы
данных в процедурные интерфейсы, делая это одинаково лёгким для
перехода из MySQL, где ресурс передаётся последним, и PostgreSQL, где
он передаётся первым.
SQLite также отличает мощный объектно-ориентированный интерфейс,
который может быть использован для эффективного извлечения данных из
базы, избавляя вас от необходимости выполнения вашей собственной
оболочки для процедурного интерфейса. Как показано в примере ниже,
объектно-ориентированный интерфейс также позволяет вам избежать
передачи всех ресурсов кучей.
<?php
// создаём новую базу (OO интерфейс)
$db = new sqlite_db("db.sqlite");
// создаём таблицу foo и вставляем что-нибудь для примера
$db->query("BEGIN;
CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255));
INSERT INTO foo (name) VALUES('Ilia');
INSERT INTO foo (name) VALUES('Ilia2');
INSERT INTO foo (name) VALUES('Ilia3');
COMMIT;");
// выполняем запрос
$result = $db->query("SELECT * FROM foo");
// проходим в цикле по ячейкам
while ($result->hasMore()) {
// получаем текущую ячейку
$row = $result->current();
print_r($row);
// переходим к следующей ячейке
$result->next();
}
// нет особой необходимости, так как PHP сам разорвёт соединение
unset($db);
?>
Установка SQLite
В PHP 5.0 установка SQLite имеет свою особенность, так как и
расширение и библиотека связаны вместе, поэтому всё, что вам
необходимо сделать - это добавить -with-sqlite в строке
конфигурирования. Я бы ещё порекомендовал установить SQLite, но только
в случае если речь об откомпилированном бинарном файле, что позволит
вам открывать базу и управлять ею без использования PHP. Это весьма
полезно для отладки и выполнения различных одновременных команд, а
также полезно для тестирования кода запросов. В будущем, довольно
часто вы сможете обнаружить, что связанная библиотека SQLite немного
"устарела", поэтому связка вашего PHP с внешней библиотекой позволит
вам извлечь выгоду из последних исправлений и нововведений SQLite. Это
также позволит вам в будущем обновлять вашу SQLite без перекомпиляции
PHP.
Чтобы собрать расширение SQLite в качестве внешней библиотеки, просто
используйте -with-sqlite=/path/to/lib/.
Мне также следует упомянуть, что расширение SQLite проходит
исчерпывающую серию тестов, как для объектно-ориентированного, так и
для процедурного интерфейсов. Тестируются каждая отдельная функция, и
каждый метод поддерживаемый SQLite. Это великолепный источник примеров
не только того, как работает каждый метод и каждая функция SQLite, но
и ожидаемого вывода, позволяющего вам видеть конечный результат каждой
операции.
Использование SQLite
Процедурный интерфейс к SQLite почти такой же, как у MySQL и других
расширений БД. По большей части переход к SQLite потребует только
изменить mysql/pq/etc... префикс функции на sqlite.
<?php
// создаём новую базу (процедурный интерфейс)
$db = sqlite_open("db.sqlite");
// создаём таблицу foo
sqlite_query($db, "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))");
// добавляем что-нибудь для примера
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')");
// выполняем запрос
$result = sqlite_query($db, "SELECT * FROM foo");
// проходим в цикле выборкой по ячейкам
while ($row = sqlite_fetch_array($result)) {
print_r($row);
/* каждый результат будет выглядеть примерно так
Array
(
[0] => 1
[id] => 1
[1] => Ilia
[name] => Ilia
)
*/
}
// закрываем соединение с базой
sqlite_close($db);
?>
Собственно значительные отличия между SQLite и другими базами данных
находятся в самом движке. В отличие от других БД в SQLite нет привязки
к типам; все данные сохраняются как строки оканчивающиеся символом
NULL, что лучше, чем двоичное представление данных в столбцах
специального типа. По причине совместимости SQLite пока поддерживает
тип спецификации в конструкциях CREATE TABLE, например, такой как INT,
CHAR, FLOAT, TEXT и тому подобные, но реально их не использует. Внутри
базы, SQLite только делает различие между строковыми и целочисленными
данными во время сортировки. Поэтому, если вы не собираетесь
сортировать данные, вы можете обойтись без указания специального типа
полей при создании таблиц в SQLite.
"Безтиповая природа" SQLite делает сортировку и сопоставление данных в
некотором роде медленнее, так как каждый раз SQLite будет вынуждена
определять тип данных и применять либо строковый механизм
сортировки/сравнения либо числовой. SQL таблицы часто требуют
автоматически присваиваемый ключ для быстрого доступа к ячейкам,
подразумевая возврат ссылки на последнюю добавленную ячейку. Для
SQLite этот синтаксис, мягко говоря, бесполезен. Чтобы создать такую
таблицу, вам понадобится объявить поле как INTEGER PRIMARY KEY, что
более удобно чем указание специализированного типа или присваивание
дополнительных свойств, которые указывают на то, что поле является
автоинкрементным.
Связанные запросы
Как вы можете ожидать, SQLite несёт в себе много новых особенностей
улучшающих исполнение и расширяющих функциональность. Одна из таких
особенностей - возможность выполнять связанные запросы, которые
подразумевают выполнение множественных запросов через функцию
выполнения одного запроса. Это снижает количество задействованных
функций PHP и таким образом увеличивает скорость работы скрипта. А
также позволяет вам легко группировать блоки запросов внутри
транзакций, улучшая выполнение в дальнейшем. Такая возможность может
быть значительным фактором во время выполнения множественных обращений
на запись в базу. Однако существует несколько пикантных особенностей,
о которых не следует забывать.
Если какой-либо запрос в SQLite использует что-то введённое
пользователем напрямую, вам следует предпринять дополнительные меры
предосторожности для проверки такого ввода, чтобы пресечь
нежелательный запрос. В противоположность MySQL, где такое может
повлечь за собой лишь недоразумение при запросе к БД и выдать ошибку,
в SQLite это позволит атакующему выполнить какой-нибудь запрос на
вашем сервере с возможными плачевными последствиями. Если вы
добавляете записи через блок запросов и хотели бы возвращать
идентификатор, то sqlite_last_insert_rowid() хорошо справится с такой
задачей, но вернёт идентификатор только последней записи. С другой
стороны, при попытке определить какое количество ячеек было изменено,
используя sqlite_changes(), мы получим результат, содержащий общее
число ячеек изменённых всеми выполненными запросами. Если ваш блок
запросов содержит SELECT, убедитесь что это самый первый запрос, в
противном случае ваша окончательная выборка не будет содержать ячеек,
возвращаемых этим запросом.
<?php
// создаём новую базу только в памяти
$db = new sqlite_db(":memory:");
// создаём таблицу из двух столбцов bar и добавляем в неё 2 ячейки
/* Чтобы улучшить выполнение, весь блок запроса сгруппирован внутри
транзакции. */
$db->query("BEGIN;
CREATE TABLE bar ( id INTEGER PRIMARY KEY, id2 );
INSERT INTO bar (id2) VALUES(1);
INSERT INTO bar (id2) VALUES(2);
COMMIT;");
// напечатает "2 insert queries"
echo $db->changes()." insert queriesn";
// напечатает: "last inserted row id: 2"
echo "last inserted row id: ".$db->last_insert_rowid();
?>
Новые функции
Помимо своих новых внутренних особенностей, SQLite предлагает ряд
новых функций, которые упрощают и ускоряют извлечение данных из базы.
<?php
$db = new sqlite_db("db.sqlite");
/* Выполняем запрос с выборкой всех ячеек в ассоциативный массив */
$result_array = $db->array_query("SELECT * FROM foo", SQLITE_ASSOC);
print_r($result_array);
?>
Это позволяет и выполнять запрос и извлекать данные одним вызовом
функции, сводя на нет всю лишнюю работу PHP. Сам по себе PHP скрипт
упрощается, за счёт использования всего одной функции там, где иначе
бы Вам пришлось использовать несколько функций для извлечения данных
работающих в цикле. В тех случаях, когда возвращается результат только
одного столбца, можно использовать sqlite_single_query(), которая
мгновенно возвращает строку или массив строк в зависимости от числа
ячеек, из которых извлекается информация.
<?php
$db = sqlite_open("db.sqlite");
// Получаем идентификатор столбца (в качестве строки)
$id = sqlite_single_query($db, "SELECT id FROM foo WHERE
name='Ilia'");
var_dump($id); //string(1)
// В случае, если совпадений больше одного, результатом будет массив
$ids = sqlite_single_query($db, "SELECT id FROM foo WHERE name LIKE 'I%'");
var_dump($ids); // array(3)
?>
Как и в случае с другими особенностями, вы можете этим пользоваться,
но вам не следует этим злоупотреблять. В случае выборки всех данных,
запросом в один проход, вы должны помнить, что все результаты будут
сохраняться в памяти. Если результат выборки содержит большое
количество данных, цена затрат на память безусловно сведёт на нет все
преимущества полученные путём снижения числа вызовов функций.
Следовательно, вам нужно приберечь использование этих функций для
случаев выборки небольшого количества данных.
Итераторы в SQLite
В PHP 5.0 есть другой способ выборки данных запросом с использованием
итераторов.
<?php
$db = new sqlite_db("db.sqlite");
// уменьшаем использование памяти, выполняя запрос без кэширования
$res = $db->unbuffered_query("SELECT * FROM foo");
foreach ($res as $row) { // получаем результат в цикле
// Код вывода
print_r($row);
}
?>
Получение результатов в цикле работает точно так же, как и проход по
массиву в цикле через foreach(), за исключением того, что в это время
у вас нет доступа к ключам и значение представляет собой массив,
содержащий данные в особых ячейках. Так как итераторы это не функции,
а внутренние указатели в самом движке, они требуют гораздо меньше
ресурсов по сравнению с функциями sqlite_fetch_*(), и не требуют,
чтобы результаты кэшировались в памяти. Конечный результат будет
чрезвычайно быстрым, это более простой и доступный метод извлечения
данных. Нет ни каких побочных эффектов в использовании итераторов
объектов в SQLite, и когда бы вам ни понадобилось получить результат в
виде множества строк, вам нужно обязательно помнить о такой
возможности.
Вспомогательные функции
SQLite также отличает ряд вспомогательных функций, которые могут
пригодится во время работы с базой данных. Одна из таких функций,
sqlite_num_fields(), может быть использована для определения
количества столбцов в специальных результатах выборки. Как вариант,
если вы собираетесь извлекать данные, просто используйте count() с
первым результатом, которая вернёт вам то же самое число. Если
извлечены как строковый, так и числовой ключи, вам понадобится
отделить результат, поскольку в результирующем массиве будет столько
вхождений сколько существует полей в таблице. Это число может быть
важно, если вашему скрипту необходимо получить названия полей внутри
какой-то таблицы. В таком случае вы могли бы использовать
sqlite_field_name() в цикле, для доступа к этой информации, как
показано в примере ниже.
<?php
$db = new sqlite_db("db.sqlite");
$res = $db->unbuffered_query("SELECT * FROM foo LIMIT 1");
// получаем количество полей
$n_fields = $res->num_fields();
while ($i < $n_fields) {
// извлекаем отдельные поля
$field_name = $res->field_name($i++);
echo $field_name."n";
}
?>
Конечно, это не идеальный способ для получения имён столбцов из
таблицы, просто по тому, что он окажется неудачным в случае, когда
таблица не содержит ни одной ячейки, а также по тому, что это
потребует от вас извлечения данных, которые вы не собираетесь
использовать. Гораздо лучшим решением будет использование функции
sqlite_fetch_column_types(), которая получает столбцы вместе с их
типами из таблицы не зависимо от того, есть ли там данные или нет.
Преимущество буферизации
В большинстве случаев, по причинам выполнения или использования
памяти, вы не захотите использовать запросы без кеширования. Это может
привести к небольшой потери функциональности, что может быть
необходимо в определённых случаях, для которых запросы без кеширования
не всегда самый лучший выбор.
Например, предположим, вы хотели бы выяснить реальное количество
ячеек, выбранное Вашим запросом. В запросе без кеширования, вам
придётся делать выборку каждой отдельной ячейки, прежде чем вы сможете
её определить. Кешированные же запросы - более простой способ,
заключающийся в использовании функции sqlite_num_rows(), которая легко
получит эту информацию из результата выборки. Запросы без кеширования
также ограничены в прямой выборке данных, подразумевая, что вы обязаны
выбрать данные из всех ячеек за один проход. В кешированных запросах
такого ограничения нет, вы можете использовать sqlite_seek() для
смещения на любую ячейку и выборки из неё данных, или даже для доступа
к ячейкам в обратном порядке, если это необходимо.
<?php
$db = new sqlite_db("db.sqlite");
$res = $db->query("SELECT * FROM foo");
$n_rows = $res->num_rows(); // получаем число ячеек выборки
$res->seek($n_rows - 1); // переходим к позиции следующей ячейки
// извлечение данных в обратном порядке
do {
$data = $res->current(SQLITE_ASSOC); // получаем данные ячейки
print_r($data);
}
while ($res->hasPrev()&& $res->prev()); // и так до первой ячейки
?>
Пользовательские функции
Одна из самых интересных возможностей в том, что расширение SQLite
предоставляет таблицу для возможности создания своих собственных
функций используемых в пределах SQL. Это возможно благодаря тому, что
SQLite содержит в одной библиотеке связанной с PHP, как интерфейс, так
и сам движок базы. Используя sqlite_create_function() вы можете
создавать функции, которые в дальнейшем могут быть применены к
результату выборки или использованы внутри какого-нибудь условия
WHERE.
<?php
/* определяем разницу между строкой введённой пользователем и строкой
из базы исходя из символов содержащихся в строке */
function char_compare($db_str, $user_str) {
return similar_text($db_str, $user_str);
}
$db = new sqlite_db("db.sqlite");
/* Создаём функцию char_compare() внутри SQLite на основании нашей PHP
функции, char_compare(). 3-ий параметр указывает количество аргументов
необходимое функции */
$db->create_function('char_compare', 'char_compare', 2);
/* Выполняем запрос, где char_compare() используется для сравнения
между name и указанной строкой */
$res = $db->array_query("SELECT name, char_compare(name, 'Il2') AS
sim_index FROM foo", SQLITE_ASSOC);
print_r($res);
?>
Возможность использования PHP в SQL позволяет вам упростить данный
скрипт, делая его доступным большей аудитории разработчиков. Это
позволяет использовать PHP в качестве движка шаблонов, который будет
вставлять HTML код в данные из базы. Во многих случаях это может
упростить код так сильно, что не возникнет необходимости в
использовании дополнительного движка шаблонов. Помимо упрощения кода,
это также улучшает выполнение программы и снижает затраты скрипта на
память вследствие того, что нет ни каких действий с данными,
происходящими в пользовательском пространстве.
Не забывайте, что в том случае, если данные, с которыми вы работаете,
могут содержать двоичный код, вам следует использовать функцию
sqlite_udf_decode_binary() для перевода их из внутренней кодировки, в
которой они хранятся в SQLite, прежде чем работать с ними. Если вы так
поступили, вам понадобится закодировать двоичные данные, используя
sqlite_udf_encode_binary() для того, чтобы быть уверенным, что данные
не будут повреждены при следующем к ним доступе.
Подведём итоги
Теперь, посмотрев как работает SQLite, и что она может предложить, вы,
возможно, обратите на неё внимание для использования в своих текущих
или будущих приложениях. Надеюсь, этот краткий обзор дал вам некоторое
представление о функциональности SQLite, и не развеял ничего из того,
что вы слышали о ней хорошего.
Как и любой инструмент, SQLite имеет свои сильные и слабые стороны.
Будучи идеальным решением, для приложений небольших и/или
предназначенных главным образом для считывания данных, она не очень
хорошо подходит для крупномасштабных приложений, часто выполняющих
запись в базу. Это ограничение - следствие того, что архитектура
SQLite основана на одном единственном файле, которая не
предусматривает распределения данных на многократно создаваемые файлы,
а также использует блокировку на запись.
Об авторе
Ilia Alshanetsky занимается разработкой web-приложений более 7 лет,
главным образом на PHP. Последние несколько лет он является активным
участником в разработке PHP и соавтором ряда расширений, в том числе
SQLite. В настоящее время Ilia управляет своей собственной компанией
"Advanced Internet Designs Inc.", которая специализируется на
разработке и поддержке FUDforum (http://fud.prohost.org/forum/),
форума с открытым кодом.
С ним можно связаться по e-mail: <ilia@prohost.org.>
Оригинал статьи доступен по адресу
http://php-mag.net/itr/online_artikel/psecom,id,447,nodeid,114.html
Автор: Wez Furlong
Что такое SQLite?
Так что такое SQLite? Это библиотека, поддерживающая большинство
спецификаций SQL92 и хранящая данные в единственном файле. Кроме того,
библиотека является и маленькой и быстрой (в некоторых случаях быстрее
MySQL и PostgreSQL), при этом, не слишком сильно увеличивая объём
приложения (считая в размере исполняемого кода и используемой памяти).
В отличие от других БД SQL поддерживаемых PHP, здесь для управления БД
не создаётся никакого отдельного процесса сервера - ваше приложение и
есть сам сервер. Это означает, что одновременные запросы (или
параллельные пользователи) должны блокировать файл для безопасного
изменения БД. Данный пункт очень важен, поскольку непосредственно
затрагивает сферу применения SQLite - если в основном используется
чтение данных, тогда никаких проблем нет, но если необходимо делать
большое количество одновременных обновлений, то приложение будет
тратить больше времени на синхронизацию блокировки файлов, чем делать
настоящую работу.
Возможности SQLite:
* Поддерживает большую часть SQL92, включая триггеры и транзанкции.
* Существует система отката, защищающая целостность данных
* Файлы БД могут совместно использоваться между машинами с
различными запросами
* Поддерживаются БД до 2 терабайт, занимая при этом небольшую часть
памяти
* Открытые исходные коды
Почему стоит использовать SQLite, вместо таких БД, как MySQL,
PostgreSQL или Oracle? Во-первых, из-за вопросов администрирования -
эти БД частенько достаточно сложны в установке, или требуют
специальных прав доступа. Хостеры, далеко не всегда предоставляют
такие БД, или берут за них отдельную плату, что делает их недоступными
для многих пользователей PHP. SQLite - это идеальное решении проблемы,
т.к. не требует никаких специальных прав доступа или конфигурации.
Храня данные в единственном файле, она позволяет в считанные секунды
"поднять" свою БД. SQLite - это выход не только для людей использующих
дешёвые тарифные планы, большие приложения также могут получить свою
выгоду от её скорости и универсальности, мы используем SQLite на
php.net для поиска в Online руководстве (там генерируется очень
большой объём трафика).
Установка SQLite
Хватит разговоров, давайте, наконец, установим SQLite. На PHP 4.3.x
можно использовать pear командную строку, для распаковки и установки:
% pear install SQLite
В бетах и snapshot'ах PHP 5, SQLite включена в дистрибутив по
умолчанию.
Утилита pear сама скачает пакет, скомпилирует и установит его. Вам
могут понадобится права рута или администратора для установки данного
модуля. Пользователи Windows могут скачать уже скомпилированные
бинарники со snapshot'ов PHP на сайте (ссылка указана в конце статьи).
На скриншотах показано, что выводится после обработки команды pear
install sqlite, скучный промежуток инсталляции не включён. Стоит
заметить, что данные которые будут выводиться - зависят от
операционной системы и установленного программного обеспечения.
После компиляции и установки, нужно подредактировать php.ini для
загрузки модуля (в PHP5 ничего редактировать не надо):
; загрузка SQLite (UNIX)
extension=sqlite.so
; для пользователей windows
; extension=php_sqlite.dll
Привязка модуля SQLite к PHP служит только для создания и работы с БД
SQLite. В него не включена консоль - с помощью который, можно было бы
играться со своей БД. Если вы любитель консоли MySQL или PostgreSQL,
то можете установить полный дистрибутив SQLite с сайта - ссылка в
конце статьи.
Использование SQLite
Программный интерфейс для SQLite подобен MySQL и PostgreSQL, хотя и
немного отличается. Одна из моих целей при написании модуля была в
том, чтобы не делать большого различия между API библиотеки C и
открытыми функциями PHP, так что вспомните это когда зададитесь
вопросом, почему некоторые функции отличаются - они отличаются потому
что библиотека SQLite отличается, а не потому что я захотел назвать их
по-другому. ;-)
Я уже упоминал, что SQLite поддерживает большинство спецификаций SQL92
- полный синтаксис SQL можно найти на сайте SQLite (ссылка в конце
статьи). Однако, главное различие между SQLite и другими БД SQL - в
SQLite нет типов данных. Все поля хранятся с нулевым разделителем
строк, вместо, например, бинарного представления целого числа. При
создании таблицы, можно использовать любой из типов данных - VARCHAR,
TEXT, INTEGER, FLOAT и т.д., но в целом они будут проигнорированы
движком SQLite - фактически можно их не писать в конструкции CREATE
TABLE, если так больше нравится. Тип данных столбца или выражения
действительно важен только при сортировке и сравнении данных, тогда
движок определяет столбец или выражение как тестовое или числовое.
Если тип текстовый - то используется бинарное сравнение, если же
числовой тип, то значение преобразовывается в число с плавающей точкой
и сравнивается как число. Достаточно разговоров - время написать
немного кода. На листинге 1 показан скрипт создающий БД, таблицу,
вставляющий некоторые данные и получающий их обратно.
Листинг 1. Демонстрация основных возможностей.
<?php
// открытие БД - и создание, если не существует
$db = sqlite_open("example.db") or
die("failed to open/create the database");
// создание простой таблицы
sqlite_query($db, "CREATE TABLE sample(email, name)");
// заполнение информацией
$data = array(
'wez@php.net' => 'Wez Furlong',
'helly@php.net' => 'Marcus Boerger',
'derick@php.net' => 'Derick Rethans',
// извиняюсь перед другими разработчиками PHP
// за то, что их не в списке...
);
foreach ($data as $email => $name) {
$email = sqlite_escape_string($email);
$name = sqlite_escape_string($name);
sqlite_query($db,
"INSERT INTO sample(email, name) "
."VALUES ('$email', '$name')");
}
// вывод
$res = sqlite_query($db, "SELECT name, email from sample");
if (!$res) {
// это не должно случиться :)
echo "No data";
} else {
while ($row = sqlite_fetch_array($res)) {
echo "row: $row[name] -> $row[email]n";
}
}
?>
На листинге 1 показаны четыре функции SQLite - четыре самые часто
используемые функции. Первая из низ sqlite_open, которая возвращает
идентификатор соединения с БД. В отличие от других БД, в SQLite
удалённо нельзя получить доступ к файлу, так что не нужно никаких
дополнительных мер защиты. Существуют дополнительные параметры, но
скорее всего они вам не потребуются. sqlite_open открывает
существующую БД, или создаёт новую, если таковой ещё нет. Стоит
помнить, что вы должны иметь права на запись в каталог, где создаётся
БД. Следующая функция sqlite_query, которая выполняет запрос и
возвращает идентификатор результата. Sqlite_query может выполняет
несколько запросов разделённых точкой с запятой, что очень полезно при
импортировании из файла. Sqlite_query возвращает ложь при ошибке
запроса, идентификатор результата для SELECT, или истина для UPDATE и
DELETE. При успешном выполнение запроса возвращаемое значение
неопределенно (хоти оно и не ложь).
Важно экранировать данные, которы вы вставляете - не столько, чтобы
избежать синтаксических ошибок, сколько чтобы избежать атак используя
SQL. Использование addslashes - не самый правильный путь для
экранирования данных в SQLite, вместо этого необходимо использовать
sqlite_escape_string. sqlite_escape_string выполняет некоторые
"магические" действия над строкой, если она выглядит как двоичные
данные - но это мы обсудим в следующей части статьи. Последняя
функция, это sqlite_fetch_array, которая извлекает текущую запись с
помощью идентификатора результатов. Она работает почти так же, как и
mysql_fetch_array в MySQL.
Это было просто - мне нужно больше!
Перед тем как показать другие примеры использования SQLite, мы
рассмотрим ещё несколько полезных функций и возможностей.
Каждый пользователь должен иметь автоматический целочисленный
идентификатор строки в своей таблице - обычно это реализовано каким-то
полем с автоматическим увеличением счётчика. В SQLite есть эта
возможность - фактически, каждая строка в каждой таблице получает свой
автоматический идентификатор, чем-то похожий на PostgreSQL, это
практичнее чем явно указывать поле с автоматическим увеличением. На
листинге 2 показано как нужно создавать такое поле.
Листинг 2: Создание поля с автоматически увеличивающимся счётчиком
CREATE TABLE ideas(
-- idea_id поле с автоматически увеличивающимся счётчиком
idea_id INTEGER PRIMARY KEY,
-- детали идеи
idea_body TEXT,
-- как хорошо она работает
success_rating INTEGER
);
Когда вы вставляете строку с нулевым значением idea _id, SQLite
автоматически присвоит полю следующее значение счётчика. Если вы
хотите спросить о черточках перед второй, третьей и пяток строками -
это стандартный способ написания комментариев в SQL. Если же вы не
хотите использовать автоматическое увеличение, то есть два пути -
объявить поле как INT PRIMARY KEY или всегда указывать значение для
столбца. Всякий раз после автоматического увеличения - можно
сгенерировать последнее значение счётчика функцией
sqlite_last_insert_rowid в PHP, или функцией SQL last_insert_rowid,
используя её непосредственно в запросе. Другая функция того же
порядка, как и sqlite_last_insert_rowid - это sqlite_changes,
возвращающая число строк изменённых последним запросом (подобно
mysql_affected_rows).
Листинг 3: Добавление элемента в базу БД и получение его номера
Настало время для примеров непосредственного использования SQLite.
Люди делающие первые шаги в создании web-сайтов, частенько желают
создать гостевую книгу. Для опытных программистов PHP это достаточно
тривиальная вещь - но для новичков сложная задача. Вероятно, опытный
программист решил бы её, используя модуль dba, или создал бы файл
собственного формата, чтобы работать с ним по fopen. Оба метода будут
работать (везде, где есть PHP), но сложны, если вы хотите в дальнейшем
добавлять какие-то новые возможности к гостевой книге. Создание своего
формата файла не столь актуально, если существует возможность
дальнейшего развития приложения. Используя SQLite вам не нужно
задумывать как оно работает, или какие типы данных использовать, ведь
SQLite как и PHP - является безтиповым. Давайте, наконец детализируем
нашу гостевую книгу. Во-первых, мы хотим знать что-нибудь о
посетителях, эта информация будет включать в себя их имена и e-mail.
Также мы хотели бы узнать адрес их web-сайта. И конечно самая важная
вещь - это комментарии, которые они оставляют для вас, и вероятно дата
записи.
Т.к. SQLite безтиповая, то она не имеет никакого специального формата
для времени, даты и функций для их обработки. Сперва, это кажется
трагедией, но фактические ничего страшного здесь нет. Мне в голову
приходят два решения - первое, это хранить значение time_t в базе
данных. Time_t - это тип данных С, содержащий UNIX timestamp -
вероятно вы знакомы с этим типом данных, потому что функция time() PHP
возвращает аналогичное целочисленное значение. Просто создайте
целочисленное поле в своей БД, и сохраняйте значение time() когда
вставляет запись. Альтернативный путь - это хранить дату как строку в
формате ISO, например "2003-09-21 15:13:12". Оба варианта сортируются
правильно при использовании ORDER BY в запросе. Первый вариант очень
прост для работы, сравнивать время и вычислять промежутки, можно
используя обычную математику, а при форматировании можно использовать
целую группу функций PHP. Использование формата ISO является более
портативным, но в тоже время вам необходимо будет писать собственные
функции для обработки строки. Т.к. мы стремимся к простоте, то будем
использовать UNIX timestamp. Теперь можно написать немного примеров -
в листинге 4 показаны функции открытия файла, добавления новой записи
и вывода на экран.
Листинг 4: функции гостевой книги
<?php
// открывает БД и инициализируются таблицы
// если они не существуют
function gb_open()
{
$db = sqlite_open('guestbook.db');
$q = sqlite_query($db, "select name from "
. "sqlite_master where type='table' and "
. "tbl_name='guests'");
if (!$q || !sqlite_fetch_array($q)) {
// таблица guest не существует
// тогда создадим её
sqlite_query($db, "create table guests("
. "id INTEGER PRIMARY KEY, "
. "name text, "
. "email text, "
. "comment text, "
. "web text, "
. "posted integer);");
}
return $db;
}
// добавление записи в гостевую книгу
function gb_add($db, $name, $email, $comment, $web)
{
$name = sqlite_escape_string($name);
$email = sqlite_escape_string($comment);
$web = sqlite_escape_string($web);
$now = time();
return sqlite_query($db, "INSERT INTO guests "
. "(id, name, email, comment, web, posted)"
. " VALUES (NULL, '$name', '$email', '$comment',"
. "'$web', $now)");
}
// отображение записей гостевой книге
function gb_show($db)
{
$q = sqlite_query($db, "SELECT * from guests "
. " order by posted desc");
echo "<table><tr><th>date</th><th>Guest</th></tr>";
while ($row = sqlite_fetch_array($q, SQLITE_ASSOC)){
echo "<tr>";
echo "<td>" . strftime("%c", $row['posted']) . "</td>";
echo "<td><a href='mailto:" .
htmlentities($row['email']) .
"'>" . htmlentities($row['name']) . '</a> ';
if (strlen($row['web'])) {
echo "<a href='" . htmlentities($row['web']) .
"'>" . htmlentities($row['web']) . "</a> ";
}
echo "<blockquote>"
. htmlentities($row['comment'])
. "</blockquote>";
echo "</tr>";
}
echo "<table>";
}
?>
Проницательный читатель заметит, что добавилась новая функция
gb_open(), созданная для того чтобы открыть нашу БД и возвратить
идентификатор соединения. Она также проверяет, была ли уже установлена
БД - делается это запрашиванием мета данных из таблицы sqlite_master.
Это специальная таблица, хранящая структуру таблицы и другие мета
данные. Функция gb_open() проверяет существование таблицы guest, если
получен отрицательный результат, то мы должны создать эту таблицу. Вам
не обязательно использовать отдельную функцию для этого, но мы
опять-таки идём по пути упрощения - пользователь хочет только
загрузить файл, не волнуясь о сложностях настройки. Есть ещё две
функции gb_add() и gb_show(). Gb_add() создаёт новую запись в гостевой
книге, а gb_show() все существующие записи. Листинги 5 и 6 показывают
как эти функции используются, я специально опустил все HTML тэги для
удобства.
Листинг 5: guestbook.php
<?php
$db = gb_open();
gb_show($db);
?>
Add a comment!
Идея в том, что посетитель открывает guestbook.php видит список
комментариев, и форму для добавления своей записи. Форма передаёт
данные скрипту new.php, которые создаёт новую запись и направляет
обратно в guestbook.php. Если всё прошло успешно, тогда запись
появится с самого верха страницы. Переадресация позволяет избежать
предупреждений об истечении срока "жизни" страницы, уменьшает
количество вопросов от пользователей и дублирование записей
пользователей.
Итоги
Если вы думаете "Я не могу в это поверить, он писал всё только о
гостевой книге", не волнуйтесь, я тоже не могу. Моему безумию есть
объяснение, большинство вещей, которые вы напишите на PHP - это будут
просто продвинутые гостевые книги. Без сомнения, некоторые из вас
говорят - "Нет"! Моё приложение XYZ - намного больше, чем просто
гостевая книга, в неё есть возможность J, L и K! Хорошо, это правда.
Все ваши дополнительные возможности (которые сохраняют/восстанавливают
данные) - не намного больше, чем просто гостевая книга. Как только вы
осознали это, то можете использовать этот метод для других приложений
PHP. Придумали что? SQLite сможет сделать это запросто. Если вы
написали такой же код на MySQL или PostgreSQL, то должны позаботиться
об имени БД, разрешениях, именах пользователей и паролей. Фактически,
волноваться о человеке устанавливающем вашу гостевую книгу,
волноваться об именах пользователей и паролях. Поскольку поддержка
конечного пользователя занимает достаточно много времени (да и
дорого), по этому этим не любят заниматься - так что всегда приятно,
когда этого можно избежать. Целая цепочка такого обслуживания и
инсталляции описанная в начале статьи, выгодно отличает SQLite от
других БД. И не стоит думать, что SQLite подходит только для написания
гостевых книг - вы можете создавать приложения в духе реляционных БД.
В статье 2 я обучу вас, как написать UDF (user defined functions,
функции определяемых пользователем) для использования в SQLite и
демонстрирующие обработку числовых массивов более 64КБ.
Я надеюсь, что статья оказалась вам полезной, так же надеюсь, что
вторая часть статьи будет столь же полезной! Если вы не можете ждать
выхода следующей статьи, не бойтесь заглянуть в сетевую документацию
SQLite (у них так же есть сообщество, которое поможет решению вашей
проблемы), и руководство PHP для SQLite модуля, которое я написал в
соавторстве с Johann Peter Hartmann.
Ссылки по теме
* SQLite binary for Windows,
http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll
* The main SQLite website, http://www.sqlite.org/
* PHP SQLite API, http://www.php.net/manual/en/ref.sqlite.php
* SQL language reference for SQLite,
http://www.sqlite.org/lang.html
* Comments & Questions, http://forum.php-mag.net/
2197 Прочтений • [Вводное руководство в SQLite (sql database sqlite php)] [08.05.2012] [Комментариев: 0]
Vova
