SQLite - легкий SQL

Недавно я узнал о новой библиотеке для работы с базой данных. Имя этой библиотеке - SQLite. Зачем ее рассматривать, у нас же есть MySQL? Дело в том, что принцип работы у этой библиотеки несколько иной, в отличие от MySQL и PostgreSQL (если кто слышал).

Про MySQL, например, не совсем корректно было бы говорить, что это библиотека. На самом деле это скорее СУБД. А SQLite наоборот трудно назвать СУБД, но легко - библиотекой. В чем же дело?

SQLite не требует наличия серверной программы для работы. Для ее работы нужен просто модуль PHP. Т.е. если на хостинге нет поддержки MySQL, можно вполне обойтись и SQLite (если конечно есть модуль SQLite). Все базы данных хранятся в файлах Вашего сайта. А это значит, что перенести сайт с одного хостинга на другой - значит просто скопировать все файлы сайта вместе с файлами базы данных и все!

В имени этой библиотеки (будем ее так называть) не зря есть слово Lite. SQLite - это облегченный язык запросов к базе данных SQL. В нем есть только самое основное, что может потребоваться для хранения и извлечения информации. И, думаю, для веб-проектов он вполне подойдет.

Установка SQLite

Будем устанавливать модуль для работы с SQLite в наш любимый PHP. Здесь есть несколько нюансов.

Во-первых, существует две версии SQLite: 2 и 3. Версию 2 мы рассматривать не будем, т.к. она уже давно считается устаревшей. В ней, в отличие от версии 3:

  • некомпактный формат файлов баз данных
  • слабая типизация данных
  • нет поддержки всегда очень нужных кодировок UTF-8 и UTF-16
  • плохая сортировка
  • уникальный идентификатор каждой строки в таблице имеет всего 32 бита вместо 64-х
  • плохой параллелизм в операциях

Во-вторых, чтобы подключить php_sqlite3.dll в php.ini, нужно сначала подключить расширения php_pdo.dll и php_pdo_sqlite.dll. Именно сначала:

extension=php_pdo.dll
extension=php_pdo_sqlite.dll
extension=php_sqlite3.dll

А не наоборот. Иначе не включится модуль, т.к. расширение PDO загрузится позже. PDO - это специальный универсальный интерфейс для общения с различными СУБД.

В третьих, где взять версию 3 DLL для SQLite3? Ведь все PHP версии до сегодняшнего дня несут в себе поддержку только версии 2.8. Не волнуйтесь, у меня есть для этого готовая php_sqlite3.dll, которую можно взять из этого архива. На день написания этой статьи последняя версия SQLite была 3.6.1.

Скачав этот архив, Вы должны скопировать php_sqlite3.dll в папку ext Вашей корневой директории PHP. И еще скопировать файл sqlite3.dll в папку Windows/System32. После этого нужно перезапустить Apache (если PHP подключен к нему статически, а не динамически).

Теперь, чтобы проверить правильность установки, мы должны выполнить следующую строчку кода:

echo sqlite3_libversion();

Она покажет версию установленной библиотеки. У меня показывает 3.6.1. Если Вам в будущем захочется обновить версию, просто скачивайте свежую sqlite3.dll с сайта www.sqlite.org и ставьте ее в Windows/System32. Только не забывайте перезапускать апач.

Менеджер для SQLite

Существует для этой библиотеки бесплатная программа-менеджер (типа phpmyadmin для MySQL), которая позволяет управлять базами данных SQLite. Называется она SQLiteAdmin и взять ее можно с сайта [ссылка]. Только написана она не на PHP, а судя по всему на Delphi. На момент написания статьи версия была еще недостаточно доработанная, но вполне пригодная для использования - 0.8.3.2 Public Beta. Лучше такую, чем совсем никакую.

Давайте попробуем

что-нибудь создать, сохранить, извлечь и посмотреть, как это все делается на PHP.

Для начала нам нужно создать базу данных, назовем ее test_db, и таблицу в ней (например, my_clients). Пусть в этой таблице будут храниться основные данные чьих-то клиентов. Для этого нам нужно создать следующие поля:

  • id - уникальное, первичный ключ
  • name - имя клиента
  • surname - фамилия
  • age - возраст

Для примера достаточно.

Теперь сделаем это все на PHP. Будем использовать класс PDO для взаимодействия с SQLite3:

<?
try {
	// Создаем или открываем созданную ранее базу данных
	$db = new PDO('sqlite:'.dirname(__FILE__).DIRECTORY_SEPARATOR.'test_db.db');
	// Создаем таблицу my_clients, если не найдена
	$db->exec('CREATE TABLE IF NOT EXISTS my_clients (
			id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
			name VARCHAR(255)  NOT NULL,
			surname VARCHAR(255)  NOT NULL,
			age NUMERIC  NOT NULL
	)');
	// Проверяем наличие таблицы (создана или нет)
	$st = $db->query('SELECT name FROM sqlite_master WHERE type = \'my_clients\'');
	$result = $st->fetchAll();
	if (sizeof($result) == 0) {
		echo 'Table created successfully'."\n";
	}
	// Вставляем две строки
	if ($db->exec('INSERT INTO my_clients (name, surname, age) VALUES (\'Иван\', \'Иванов\', 28)') > 0) {
		echo 'Inserted'."\n";
	}
	if ($db->exec('INSERT INTO my_clients (name, surname, age) VALUES (\'Вася\', \'Пупкин\', 30)') > 0) {
		echo 'Inserted'."\n";
	}
	// Читаем эти две строки и выводим в окно браузера
	$st = $db->query('SELECT * FROM my_clients');
	$results = $st->fetchAll();
	foreach ($results as $row) {
		echo 'id = '.$row['id'].', name = '.$row['name'].', surname = '.$row['surname'].', age = '.$row['age']."\n";
	}
} catch (PDOException $e) {
	die($e->getMessage());
}
?>

Триггеры в SQLite

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

Добавим к нашей таблице my_clients еще одну - my_clients_phones. Будем там хранить для каждого клиента номера телефонов (по несколько на одного клиента):

CREATE TABLE my_clients_phones (

	[id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,

	[client_id] INTEGER  NOT NULL,

	[phone] VARCHAR(255)  NOT NULL

)

И вставим для Васи Пупкина (его ID у меня = 29) два номера телефона:

INSERT INTO my_clients_phones (client_id, phone) VALUES (29, '123456');

INSERT INTO my_clients_phones (client_id, phone) VALUES (29, '654321');

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

CREATE TRIGGER delete_client BEFORE DELETE ON my_clients FOR EACH ROW

BEGIN

	DELETE FROM my_clients_phones WHERE client_id = OLD.id;

END

Здесь мы создали простой триггер, который выполнит запрос удаления из таблицы my_clients_phones номера телефона, владелец которого будет удален.

Заключение

Здесь я лишь показал, как примерно можно пользоваться PDO и SQLite. За подробной документацией отправляю Вас на следующие сайты:

  • [ссылка] - документация по SQLite
  • [ссылка] - документация по PDO
  • https://www.litewebsite.com/ - как использовать SQLite в PHP
  • [ссылка] - сайт программы SQLiteAdmin

Удачи и до встречи! :)

Другие пишут:



Теги: , ,


Читайте также:



24 Ответов на “SQLite - легкий SQL”

  1. librarian

    Недавно писал мини движок на SQLite, и тот же движок на MySQL, так скулайт быстрее оказался, уж не знаю почему.
    Кстати, популярный блог движок Movable Type работает как раз на SQLite, в том числе.

    Ещё плюсом SQLite можно назвать то, что если MySQL сервер упадёт, то это никак не повлияет на работу движка.

  2. novice

    SQLite быстрее MySQL, только когда база не занимает большого объема. А быстрее он потому, что весь код сконцентрирован в одном PHP модуле, а это значит, что нет затрат времени на взаимодействие с сервисом, как в MySQL.

    А то, что SQLite более устойчив к падениям, это верно замечено!

  3. Goodyear

    Отличная статья, спасибо!

  4. Agile

    Я начал изучать РНР относительно недавно, тогда только появилась версия РНР5, в которую впервые вошла база данных SQLITE. Получилось так, что совсем не знаю MYSQL и совершенно не себя чувствую ущербно. SQLITE полностью покрывает все потребности стандартного РНР сайта, то есть, написание простых скрипов, таких как, голосование, лента новостей, администрирование и регистрация, SQLITE подходит идеально. Но вот вопрос, который мне придется решить ближайшее время - Как написать древовидную систему категорий доски объявлений на SQLITE? Очевидно, нужно будет использовать ТРИГЕРРЫ. Не могу найти примеров ни где. Кто посоветует, как сделать, или где посмотреть?

  5. novice

    Чтобы создать древовидную систему категорий, нужно сделать всего одну таблицу (если я Вас правильно понял, Agile). Например, tbl_categories как минимум со следующими полями:

    id - уникальный идентификатор категории (первичный ключ)
    cat - идентификатор родительской категории (0 - для корня)
    caption - имя категории

    теперь представим что у нас есть категория “Книги” с двумя вложенными категориями “Фэнтези”, “Драма”:

    INSERT INTO tbl_categories (id, cat, caption) VALUES (1, 0, "Книги");
INSERT INTO tbl_categories (id, cat, caption) VALUES (2, 1, "Фэнтези");
INSERT INTO tbl_categories (id, cat, caption) VALUES (3, 1, "Драма");

    Теперь представим, что в категории “Фэнтези” есть еще две подкатегории - “Зарубежное фэнтези” и “Отечественное фэнтези”:

    INSERT INTO tbl_categories (id, cat, caption) VALUES (4, 2, "Зарубежное фэнтези");
INSERT INTO tbl_categories (id, cat, caption) VALUES (5, 2, "Отечественное фэнтези");
  6. Big_Shark

    function get_cat_sql()
    {
    global $DB,$VAR,$Cache,$get_cat_sql;
    if(!$get_cat_sql)
    {
    if(!$get_cat_sql=$Cache->open(‘cat_sql’))
    {
    $get_cat_sql=$DB->super_query(“SELECT * FROM `”.$VAR[‘SQL’][‘prefix’].”module_cat_parts` ORDER BY `sort` ASC, `title` ASC”,true);
    $Cache->save(‘cat_sql’,$get_cat_sql);
    }
    }
    return $get_cat_sql;
    }

    function get_cat_tree($id=0,$type=’id’)
    {
    global $Cache;
    if(!function_exists(“sub_tree”))
    {
    function sub_tree($id=0,$type=’id’,$result)
    {
    global $Cache;
    foreach ($result as $data)
    {
    if($data[‘parent’]==$id)
    {
    $cat_tree[$data[$type]]=$data;
    $cat_tree[$data[$type]][‘attr’]=unserialize($data[‘attr’]);
    if($children=sub_tree($data[‘id’],$type,$result)){$cat_tree[$data[$type]][‘children’]=$children;}
    $Cache->save(‘cat_tree_type_’.$type.’_parent_’.$id,$cat_tree);
    }
    }
    return $cat_tree;
    }
    }
    if(!$cat_tree=$Cache->open(‘cat_tree_type_’.$type.’_parent_’.$id))
    {
    if ($result=get_cat_sql())
    {
    $cat_tree=sub_tree($id,$type,$result);
    return $cat_tree;
    }
    }
    return $cat_tree;
    }

    Перепеши верхний запрос на sqlite структура таблицы примерно такая
    module_cat_parts (id, title , parrent, sort, attr)
    у меня в базе attr эта масив в сюриалайзе при вытаскивании из базы автоматом преобразуеться в подмасив)
    Вообщем надеюсь эта чемто поможет тебе)
    Если поможет то напиши в асю 231744674 я тебе скину еще несколько функций полезных для построения деревьяв такие как узнать всех радителей и тд
    Ах да у удали кеш который тут везде)

  7. Serg

    не дадите случаем прямой ссылки на sqlite3 библиотечку под винду и под линукс, а то что-то на [ссылка] никак не могу разобраться что скачать.

  8. novice

    Для Windows Вам нужно скачать по этой ссылке: [ссылка]
    Для Linux - по этой:
    [ссылка]

  9. Serg

    спасибо!

  10. Serg

    to novice
    скачал версию для винды, делаю все как в туториале, и что-то ничего не подключается в PHP. phpinfo() не показывет, что модуль подключен.

    У меня есть версия 3.5.7 найденная просто в интернете, даже не помню где (тогда очень много перерыл чтоб найти рабочую). С ней все нормально работает, но у заказчика линукс хостинг и я не могу найти данную версию библиотеки под линукс. Открыв ее в Dependency Walker вижу что в ней экспортируется только get_module, а в той что я качаю по вашей ссылке экстпортируются функции sqlite3, а этой функции в списке нет.
    PHP версия 5.2.6
    Кстати еще скачал сегодня альфа релиз php 5.3, и с ним идет сразу php_sqlite3.dll. Но заказчик навряд ли будет использовать альфу.

  11. novice

    Попробуйте с библиотекой, которую я дал в статье

  12. Виктор

    Скопировал библиотеки php_sqlite3.dll и sqlite3.dll куда надо, в php.ini было
    extension=php_pdo.dll
    extension=php_sqlite.dll
    extension=php_pdo_sqlite.dll

    стало:
    extension=php_pdo.dll
    ;extension=php_sqlite.dll
    extension=php_pdo_sqlite.dll
    extension=php_sqlite3.dll

    Но php так и не желает понимать функции с цифрой 3
    echo sqlite_libversion(); - выдает

  13. novice

    Виктор, попробуйте вот так:
    echo sqlite3_libversion();

  14. Виктор

    я пробовал, php выдает ошибку - функция неопределена, это относится к любым функциям с цифрой 3
    возможно dll в system32 не подгружается, можно как-то определить что какая-то библиотека не загрузилась? php4 - мессадж выдавал с ошибкой, а 5-й молчит

  15. novice

    Это значит, что у Вас скорее всего не загружается sqlite3.dll. Я перепроверил у себя все действия - если делать все, как написано в этом посте, работать будет. При этом, если посмотреть в phpinfo(), то выводится информация и о PDO и о SQLite 3. Если у Вас таковая не выводится, значит точно dll не загрузилась по какой-то причине. Еще я проверял echo sqlite_libversion(); - при подключенной третьей версии такая функция не была найдена. У меня подозрение на файл php_pdo_sqlite.dll, который находится в директории ext папки, в которой php у Вас стоит. У меня в описании этой библиотеки значится следующее: “SQLite 3.x driver for PDO”. Проверьте у себя.

  16. 61re

    Я вчера наткнулся на эту CMS [ссылка], установил на сервер…Вполне даже хорошая система управления контентом. Здесь можно скачать форум на mySQL от того же разработчика

  17. дигер

    я тут пробовал на скулайте гостевую зделать и когда посты удаляются , то остаются дыры с id (1,2,5,10)
    как зделать , чтобы их не было ?
    —
    статья супер
    —
    те , у кого денвер можете и не пробовать ставить : замените его хамппом

  18. aaa

    Где хоть один индекс , ппц .

  19. Борис

    Показывает:
    could not find driver

    в phpinfo();
    показывает PDO только для MySQL!

    SQLITE3

    SQLite3 support | enabled
    sqlite3 library version | 3.6.1

  20. Борис

    И еще!
    Вы не могли бы объяснить вот эту строчку:

    $db = new PDO(‘sqlite:’.dirname(__FILE__).DIRECTORY_SEPARATOR.’test_db.db’);

  21. Антон

    А каким образом можно значение переменной записать в таблицу?

    if ($db->exec(‘INSERT INTO my_clients (name, surname, age) VALUES (\’Иван\’, \’Иванов\’, 28)’) > 0) {
    echo ‘Inserted’.”\n”;
    }

    Пробовал и пристыковку и всё что угодно, не выходит :(

  22. web-developer

    Хто ж так ссылки ставит? :)

    [ссылка] – документация по SQLite

  23. web-developer

    https://i-novice.net/gout/W0BCQwocSw1YVAdZXlYRQAZYGg==/
    А нафига так ссылки криптовать? :) Ставишь rel=”nofollow” и живи спокойно. За такие жлобские сео-извращения горят в аду 😉

  24. Жека

    Статья - супер. Спасибо!


© Copyright. . I-Novice. All Rights Reserved. Terms | Site Map