Введение
SQLite – это реляционная база данных, запросы к которой можно осуществлять при помощи языка запросов SQL. База данных не поддерживает все особенности SQL и уступает в функциональности другим развитым СУБД, но вполне подходит для хранения и извлечения информации.
Отличие SQLite от MySQL и аналогичных СУБД
Классические СУБД, такие как MySQL (а так же MS SQL, Oracle, PostgreeSQL) состоят из отдельного сервера, поддерживающего работу базы данных и прослушивающих определённый порт, на предмет обращения клиентов. В качестве клиента может выступать в том числе и расширение PHP, реализующего интерфейс, с помощью которого осуществляются запросы к базе. Движок SQLite и интерфейс к ней реализованы в одной библиотеке, что увеличивает скорость выполнения запросов. Такой сервер часто называют встроенным .
Замечание
Встроенный сервер имеется и у других баз данных, например, у MySQL, но его использование требует лицензионных отчислений, поэтому не получило широкое распространение в мире открытых исходных кодов.
SQLite является бестиповой базой данных. Точнее, есть только два типа – целочисленный "integer" и текстовый "text". Причём "integer" используется преимущественно для первичного ключа таблицы, а для остальных данных пойдёт "text". Длина строки, записываемой в текстовое поле, может быть любой.
Особенности SQLite
Все базы данных хранятся в файлах, по одному файлу на базу. Количество баз данных, а так же таблиц в них, ограниченно только свободным местом, имеющимся на сайте. А максимально возможный объём одной базы данных составляет 2 Тб.
Так как все данные хранятся в файлах, проблем с переносом базы данных с одного хостинга на другой не существует – достаточно лишь скопировать соответствующие файлы.
Установка SQLite
В PHP5 поддержка SQLite установлена и включена по умолчанию.
Установка под Windows: Для установки SQLite необходимо скачать и скопировать в папку с расширениями библиотеку "php_sqlite.dll", которую можно загрузить по ссылке: http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll . Затем необходимо раскомментировать (или добавить) строку "extension=php_sqlite.dll" в файле "php.ini". Для нормального функционирования SQLite также необходимо раскомментировать строку "extension=php_pdo.dll".
Замечание
Замечание
Библиотека "php_pdo.dll" должна загружаться до загрузки "php_sqlite.dll". То есть в php.ini строка "extension=php_sqlite.dll" должна стоять после "extension=php_pdo.dll".
Установка под Unix: Скачайте свежую версию SQLite с официального сайта (http://sqlite.org/download.html). Прочтите файл "INSTALL", поставляемый с исходными тестами модуля. Или просто воспользуйтесь командой установки PEAR: "pear install sqlite".
Работа с SQLite
Создание базы данных: Для того чтобы создать новую базу данных необходимо воспользоваться функцией sqlite_open(). Если базы, имя которой указано в параметре "filename" не существует, то функция создаст новую базу данных с именем "filename" и вернёт идентификатор базы данных.
Resource sqlite_open (string filename [, int mode [, string &error_message]])
В скрипте, преведённом ниже, демонстрируется создание новой базы данных:
// Создадим базу данных
if (!
$db
) exit("Не удалось создать базу данных!"
);
?>
В результате в папке со скриптом у нас появится файл с именем "my_database.db" – наша база данных.
Создание таблиц: Все запросы к базе данных выполняет функция sqlite_query(), которая имеет следующий синтаксис:
Resource sqlite_query (resource dbhandle, string query)
Замечание
Для работы с SQLite, как и любой реляционной базой данных используется язык запросов SQL. Поэтому создать таблицу данных можно при помощи традиционного запроса CREATE TABLE, вставить запись при помощи оператора INSERT, извлечь запись при помощи SELECT, а обновить существующую запись при помощи запроса UPDATE.
В приведённом ниже примере создаётся таблица table1, содержащая три поля: целочисленное поле id, которое выступает в качестве первичного ключа, и два текстовых поля field1 и field2.
$db
=
sqlite_open
("my_database.db"
);
if (!
$db
) exit();
"CREATE TABLE table1
(id INTEGER PRIMARY KEY,
field1 TEXT,
field2 TEXT);
"
);
if (!
$query_table
) exit();
$query_insert
=
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("PHP5", "Apache");"
);
if (!
$query_insert
) exit("Невозможно записать данные в таблицу!"
);
?>
После создания таблицы, в неё добавляется запись, содержащая строки "PHP5" и "Apache", поле id автоматически получает значение 1.
Вывод данных из базы: Для вывода данных из таблиц используется всё та же функция – sqlite_query(). Если выбирается несколько записей, результат выборки следует обработать при помощи цикла while() и функции sqlite_fetch_array(), которая имеет следующий синтаксис:
Array sqlite_fetch_array (resource result [, int result_type [, bool decode_binary]])
Ниже приводится скрипт, демонстрирующий вывод нескольких записей из базы данных:
// Создадим новую базу данных
$db
=
sqlite_open
("my_database.db"
);
if (!
$db
) exit("Невозможно создать базу данных!"
);
// Создадим таблицу "table1" в базе
$query_table
=
sqlite_query
($db
,
"CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id автоматически станет автоинкрементным */
field1 TEXT,
field2 TEXT);
"
);
if (!
$query_table
) exit("Невозможно создать таблицу в базе данных!"
);
// Запишем что-нибудь в таблицу
sqlite_query
($db
,
);
sqlite_query
($db
,
);
sqlite_query
($db
,
);
// Сделаем выборку данных
$res
=
sqlite_query
($db
,
"SELECT * FROM table1;"
);
while ($array
=
sqlite_fetch_array
($res
))
{
"
);
}
?>
В результате работы скрипта получим:
PHP5+Apache (id записи:1)
Редактрирование записи : Для изменения поля воспользуемся функцией sqlite_query() и передадим ей запрос на обновление (UPDATE).
// Создадим новую базу данных
$db
=
sqlite_open
("my_database.db"
);
if (!
$db
) exit("Невозможно создать базу данных!"
);
// Создадим таблицу "table1" в базе
$query_table
=
sqlite_query
($db
,
"CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id автоматически станет автоинкрементным */
field1 TEXT,
field2 TEXT);
"
);
if (!
$query_table
) exit("Невозможно создать таблицу в базе данных!"
);
// Запишем что-нибудь в таблицу
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("PHP5+", "Apache");"
);
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("SQLite – ", "классная вещь");"
);
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("Посетите ", "sqlite.org");"
);
// Изменим поле с id=1
sqlite_query
($db
,
"UPDATE table1 SET field2="Apache+Linux" WHERE id=1;"
);
// Сделаем выборку данных
// В цикле выведем все полученные данные
{
echo($array
[
"field1"
].
$array
[
"field2"
].
" (id записи:"
.
$array
[
"id"
].
")
"
);
}
?>
В результате получим:
PHP5+Apache+Linux (id записи:1)
SQLite – классная вещь (id записи:2)
посетите sqlite.org (id записи:3)
Удаление записи из таблицы : Чтобы удалить запись из таблицы, нужно передать функции sqlite_query() запрос на удаление (DELETE).
// Создадим новую базу данных
$db
=
sqlite_open
("my_database.db"
);
if (!
$db
) exit("Невозможно создать базу данных!"
);
// Создадим таблицу "table1" в базе
$query_table
=
sqlite_query
($db
,
"CREATE TABLE table1
(id INTEGER PRIMARY KEY,
/* id автоматически станет автоинкрементным */
field1 TEXT,
field2 TEXT);
"
);
if (!
$query_table
) exit("Невозможно создать таблицу в базе данных!"
);
// Запишем что-нибудь в таблицу
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("PHP5+", "Apache");"
);
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("SQLite – ", "классная вещь");"
);
sqlite_query
($db
,
"INSERT INTO table1(field1, field2) VALUES ("Посетите ", "sqlite.org");"
);
// Удалим поле с id=2
sqlite_query
($db
,
"DELETE FROM table1 WHERE id=2;"
);
// Сделаем выборку данных
$query
=
sqlite_query
($db
,
"SELECT * FROM table1;"
);
// В цикле выведем все полученные данные
while ($array
=
sqlite_fetch_array
($query
))
{
echo($array
[
"field1"
].
$array
[
"field2"
].
" (id записи:"
.
$array
[
"id"
].
")
"
);
}
?>
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. Для этого потребуется только установить дополнение .
Чтобы добавить это дополнение в основное меню FireFox («гамбургер»), нажмите Изменить и перетащите мышью икноку в меню.
На этом установлен и можно им пользоваться.
Для начала создадим новую базу данных. В 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 .
Роутинг мы можем сделать, как описано в предыдущей статье . Файл.htaccess
AddDefaultCharset UTF-8
Options -Indexes
В index.php сам роутинг будет описан одной строчкой:
$page = ($p = key($_GET)) ? $p: "home";
- подключаем базу
- делаем в ней выборку по $page
- выводим полученные данные
Я намеренно упрощаю алгоритм, чтобы не усложнять php-код.
Существуют два варианта работы с БД. Первый - это нативный php-код. Он не очень сложный, но обилие параметров немного напрягает. Поэтому второй вариант - использование дополнительных библиотек-оберток. С ними код становится лаконичней.
Приведу код index.php в первом варианте:
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:
Данный пример будет выводить для разных адресов разный текст и при этом будет работать счетчик просмотров. Если требуется добавить новую страницу, то её достаточно создать в базе. Для удобства, в SQLite Manager можно подключить базу прямо из каталога сайта.
Здесь следует отметить пару важных моментов.
В первую очередь, вся работа с PDO должна заключаться в блок try..catch . Таким способом отлавливаются сообщения об ошибках.
Данные, которые отправляются в sql-запрос должны проходить через валидацию. В PDO, когда используется предподготовка данных (PDO::prepare), выполняется принудительное экранирование параметров. Это позволяет защититься от возможных SQL-инъекций.
Когда происходит подключение базы данных SQLite, в случае отсутствия файла базы, он будет создан автоматически. На этом базируется создание базы сразу после его подключения и создание нужных таблиц первым sql-запросом (о котором я написал выше).
Еще одно замечание по SQLite. Поскольку база это файл, то его можно скачать по URL прямо через браузер. Поэтому каталог с SQLite-файлами лучше защищать через.htaccess строчкой Deny from all . Или же размещать выше чем основной www-каталог.
SQLite может пригодится для небольших задач, которые требуют небольшой базы данных: счетчики, голосования, мета-данные страниц и т.п.
Вы можете к этой статье.
На днях мне нужно было сделать систему для быстрого формирования данных и сохранения их в SQLite базы данных. И это всё должно было работать очень быстро. К слову сказать, что нужно было добавлять и в MySQL БД. Что касается MySQL, то здесь имеется широкий выбор средств для добавления данных:
- простой INSERT
- INSERT и множеством добавляемых значений (BULK INSERT)
- добавление через CSV файлы
Самым быстрым, без сомнения, оказался вариант №3. Собственного говоря, он и так использовался в системе. К слову сказать, что этот вариант позволяет добавлять в базу до нескольких сотен тысяч / миллионов записей за считанные секунды. Конечно, здесь большую роль играет и начинка сервера, но сейчас это рассматриваться не будет.
Как мы все прекрасно знаем, и не раз убеждались, большие возможности требуют больших ресурсов и/или дополнительного ПО для добавления взаимодействия с этой системой и накладывают дополнительные ограничения.
В отличие от MySQL, SQLite достаточно «лёгкая» и для работы с ней нужна только одна библиотека. Файлы баз данных можно размещать где угодно, передавать. Но, добавление в базу данных можно осуществить только через просто INSERT. Это значит, что добавление через «BULK» INSERT не поддерживается. А, если в таблице имеется хотя бы один ключ (как минимум это будет первичный ключ), то добавление одной записи подразумевает и перестроение индексов. Теперь представьте, нужно добавить несколько тысяч записей за максимально короткое время. Не смотря на всю легковесность и скорость SQLite, это требует достаточно много времени.
Выход нашёлся неожиданный (по крайней мере для меня — я не изучал все возможности этой БД) — использование транзакций. Это дало прирост скорости в 10-100 раз. Сохранение/успешное завершение транзакции выполняется быстро — фактически данные просто скидываются в файл базы данных.
Описанное было хорошо, но для полного комплекта нужно было, чтобы эти данные записывались в разные sqlite-файлы. Для этого был использован простой и проверенный способ — использование нескольких дескрипторов. Благо, файлов было немного. После это всех этих манипуляций мы имеем скрипт, срабатывающий менее чем за 5 минут и записывающий данные в N файлов по M строк в каждой. Оговорюсь сразу — в данном случае в каждом файле базы данных по одной таблице.
А теперь немного цифр:
В тестовых базах данных использовалсяДля каждого файла была открыта транзакция, а затем закрыта с сохранением. Важное замечание: каждый SQLite может позволить использование только одного дескриптора для каждого файла. При попытке взаимодействия с базой данных со вторым дескриптором, будет выдана ошибка.
Как видите, данный подход позволяет добавлять большое количество данных за минимальное время.
Стоит упомянуть, что сказав, что единственным способом добавления в SQLite базы данных является простой INSERT, я не указал на то, что это единственный вариант с использованием языка SQL.
Есть другой вариант — аналог «LOAD DATA …» в MySQL — импортирование данных из CSV файлов. Только этот вариант позволяет добавлять данные непосредственно из среды SQLite. Другими словами, нужно сперва открыть базу данных — к примеру, вызвать из консоли.
Import путь_к csv_файлу таблица
Разделителем по умолчанию является символ «|».
Для того, чтобы сменить разделитель, нужно вызвать команду:
Separator разделитель
В качестве разделителя нужно указать разделитель колонок, например, «,».
Скорость добавления в этом случае превосходит скорость описанного ранее способа раз в 100. Так почему же не был использован такой способ? Дело в следующем — серверный язык программирование (в данном случае PHP), может использовать одну версию sqlite библиотеки, а в системе может быть установлена (а может и нет) другая версия. При комбинации этих двух способов взаимодействия sqlite база данных может быть недоступна для одного из вариантов — при попытка чтения будет выдана ошибка.
Это библиотека, написанная на языке C, которая обеспечивает работу с SQL. Данный инструмент относится к Реляционным системам управления базами данных . Большинство баз данных SQL работает по схеме клиент/сервер. Возьмём к примеру MySQL . В процессе работы данные берутся с MySQL сервера, и отправляются в качестве ответа на запрос. В случае использования SQLite, данные будут браться непосредственно с диска, т.е. не будет необходимости обращаться к серверу.
Установка
Мы будем взаимодействовать с базой данных через интерфейс командной строки sqlite3 (CLI) в Linux. Работа с sqlite3 CLI в MAC OS и Windows осуществляется таким же образом, однако я рекомендую вам потратить 5 минут на установку виртуальной машины, чтобы не захламлять свой компьютер лишним софтом.
Для установки sqlite3 на Linux выполняем команду:
sudo apt-get install sqlite3 libsqlite3-dev
В результате на вашей машине будет установлен sqlite3 . Для установки данного инструмента на других ОС следуйте инструкциям . Для запуска sqlite выполняем команду sqlite3 в консоли. Результат должен быть таким:
Во второй строчке указана подсказка о том, что для получения справки необходимо выполнить команду.help . Давайте сделаем это. В результате мы увидим Мета Команды и их описание.
Мета Команды
Мета Команды - предназначены для формирования таблиц и других административных операций. Все они оканчиваются точкой . Пройдёмся по списку команд, которые могут пригодиться:
Стандартные команды
Теперь давайте пройдёмся по списку стандартных команд sqlite3, которые предназначены для взаимодействия с базой данных. Стандартные команды могут быть классифицированы по трём группам:
- Язык описания данных DDL : команды для создания таблицы, изменения и удаления баз данных, таблиц и прочего.
Заметка : SQLite так же поддерживает и множество других команд, список которых можно найти . Поскольку данный урок предназначен для начинающих, мы ограничимся перечисленным набором команд.
Файлы баз данных SQLite являются кроссплатформенными . Они могут располагаться на различного рода устройствах.
- Комментарий
Из всех этих полей только адрес сайта может быть пустым. Так же можем ввести колонку для нумерации комментриев. Назовём её post_id .
Теперь давайте определимся с типами данных для каждой из колонок:
| Атрибут | Тип данных |
| post_id | INTEGER |
| name | TEXT |
| TEXT | |
| website_url | TEXT |
| comment | TEXT |
Вы сможете найти все типы данных, поддерживаемые в SQLite3.
Так же следует отметить, в SQLite3 данные, вставляемые в колонку могут отличаться от указанного типа. В MySQL такое не пройдёт.
Теперь давайте создадим базу данных. Если вы ещё находитесь в интерфейсе sqlite3, то наберите команду.quit для выхода. Теперь вводим:
sqlite3 comment_section.db
В результате, в текущем каталоге у нас появится файл comment_section.db .
Заметка : если не указать название файла, sqlite3 создаст временную базу данных.
Создание таблицы
Для хранения комментариев нам необходимо создать таблицу. Назовём её comments . Выполняем команду:
CREATE TABLE comments (post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL);
NOT NULL обеспечит уверенность, что ячейка не будет содержать пустое значение. PRIMARY KEY и AUTOINCREMENT расширяют возможности поля post_id .
Чтобы убедиться в том, что таблица была создана, выполняем мета команду.tables . В результате видим нашу таблицу comments .
Заметка : Для получения структуры таблицы наберите.schema comments
Теперь можем внести данные в таблицу.
ВСТАВКА СТРОК
Предположим, что нам необходим внести следующую запись:
Name: Shivam Mamgain Email: [email protected] Website: shivammg.blogspot.com Comment: Great tutorial for beginners.
Для вставки воспользуемся командой INSERT .
INSERT INTO comments (name, email, website_url, comment) VALUES ("Shivam Mamgain", "[email protected]", "shivammg.blogspot.com", "Great tutorial for beginners.");
Указывать значение для post_id не нужно т.к. оно сформируется автоматически благодаря настройке AUTOINCREMENT .
Чтобы набить руку можете вставить ещё несколько строк.
ВЫБОРКА
Для выборки данных воспользуемся командой SELECT .
SELECT post_id, name, email, website_url, comment FROM comments;
Этот же запрос может выглядеть так:
SELECT * FROM comments;
В результате из таблицы будут извлечены все строки. Результат может выглядеть без разграничения по колонкам и без заголовка. Чтобы это исправить выполняем:
Для отображения шапки введите.headers ON .
Для отображения колонок выполните команду.mode column .
Выполняем SELECT запрос ещё раз.
Заметка : вид отображения можно изменить, воспользовавшись мета командой.mode .
ОБНОВЛЕНИЕ
Предположим, что поле email для пользователя ‘Shivam Mamgain’ необходимо изменить на ‘[email protected]’. Выполняем следующую команду:
В результате запись будет изменена.
Заметка : Значение в колонке name может быть не уникально, так что в результате работы команды может быть затронуто более одной строки. Для всех пользователей, где значение name = ‘Shivam Mamgain’, поле email будет изменено на ‘[email protected]’. Для изменения какой-то конкретной строки следует её отследить по полю post_id . Мы его определили как PRIMARY KEY , что обеспечивает уникальность значения.
