Как и когда использовать Sqlite
Примечание. В этом руководстве используется устаревшая версия Ubuntu. Нажмите здесь, чтобы прочитать обновленную версию, написанную для Ubuntu 20.04.
Sqlite — очень простой и быстрый механизм SQL с открытым исходным кодом. В этом руководстве объясняется, когда оптимально использовать Sqlite, а не полноценную СУБД, такую как Mysql или Postgres, а также как ее установить и основные примеры использования, охватывающие CRUD — создание, чтение, обновление и удаление.
Заблуждения
Не обманывайтесь, думая, что Sqlite предназначен только для тестирования и разработки. Например, он отлично работает для веб-сайтов, получающих до 100 000 посещений в день — и это консервативный предел. Максимальный размер базы данных Sqlite составляет 140 терабайт (что должно быть достаточно, верно?), и это может быть значительно быстрее, чем полноценная СУБД. Полная база данных и все другие необходимые данные хранятся в обычном файле в файловой системе хоста, поэтому отдельный серверный процесс не требуется (отпадает всякая необходимость в медленном межпроцессном взаимодействии).
Оптимальное использование на вашем VPS
Sqlite ориентирован на простоту. Поскольку он полностью внутренний, он часто значительно быстрее, чем альтернативы. Если вам нужна переносимость (в отношении как языков, так и платформ), простота, скорость и небольшой объем памяти, Sqlite идеально подходит. Его недостатки очевидны только в том случае, если вам требуется высокий уровень параллелизма при чтении или записи: Sqlite может поддерживать только одну запись за раз, а обычно высокая задержка файловой системы может быть неудобной, если необходимо, чтобы многие клиенты одновременно обращались к базе данных Sqlite. Последний возможный недостаток заключается в том, что его синтаксис, хотя и похож на другие системы SQL, уникален. Хотя переход на другую систему довольно тривиален, если вы «перерастете» Sqlite, переход будет связан с некоторыми накладными расходами.
Для получения дополнительной информации в официальной документации есть несколько очень хороших описаний плюсов и минусов Sqlite.
Установите Sqlite на свой VPS
Модуль sqlite3 является частью стандартной библиотеки Python, поэтому при стандартной установке Ubuntu или любой системе с установленным Python дальнейшая установка строго не требуется. Чтобы установить интерфейс командной строки Sqlite в Ubuntu, используйте следующие команды:
sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev
Если вам нужно скомпилировать его из исходного кода, скачайте последнюю версию autoconf с официального сайта SQLite. На момент написания:
wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz
tar xvfz sqlite-autoconf-3080100.tar.gz
cd sqlite-autoconf-3080100
./configure
make
make install
(Примечания по сборке из исходного кода: 1) Не делайте этого при стандартной установке Ubuntu, так как вы, вероятно, получите ошибку «несоответствие версии заголовка и исходного кода» из-за конфликта между уже установленной версией и вновь установленной. 2) Если кажется, что команда make ожидает дальнейшего ввода, просто наберитесь терпения, так как исходный код может занять некоторое время для компиляции).
Основное использование интерфейса командной строки
Чтобы создать базу данных, выполните команду:
sqlite3 database.db
Где «база данных» — это имя вашей базы данных. Если файл database.db уже существует, Sqlite откроет к нему подключение; если он не существует, он будет создан. Вы должны увидеть вывод, похожий на:
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Теперь давайте создадим таблицу и вставим некоторые данные. Эта таблица с именем \wines имеет четыре столбца: для идентификатора, производителя вина, сорта вина и страны происхождения вина. Поскольку еще не пятница, мы вставим в нашу базу данных только три вина:
CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20));
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");
Мы создали базу данных, таблицу и некоторые записи. Теперь нажмите Ctrl + D, чтобы выйти из Sqlite, и введите следующее (снова заменив имя вашей базы данных на «база данных»), которое повторно подключится к базе данных, которую мы только что создали:
sqlite3 database.db
Теперь введите:
SELECT * FROM wines;
И вы должны увидеть записи, которые мы только что сделали:
1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|France
Большой. Это все для создания и чтения. Сделаем обновление и удалим:
UPDATE wines SET country="South Africa" WHERE country="France";
Это обновит базу данных, чтобы все вина, которые указаны как происходящие из Франции, вместо этого были указаны как происходящие из Южной Африки. Проверьте результат с помощью:
SELECT * FROM wines;
И вы должны увидеть:
1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|South Africa
Теперь все наши вина родом из Южной Африки. Давайте выпьем KWV на празднование и удалим его из нашей базы данных:
DELETE FROM wines WHERE id=2;
SELECT * FROM wines;
И в нашем погребе должно быть на одно вино меньше:
1|Rooiberg|Pinotage|South Africa
3|Marks & Spencer|Pinot Noir|South Africa
И это охватывает все основные операции с базой данных. Прежде чем мы закончим, давайте попробуем еще один (немного) менее тривиальный пример, в котором используются две таблицы и базовое соединение.
Выйдите из Sqlite с помощью команды Ctrl + D и повторно подключитесь к новой базе данных с помощью sqlite3 database2.db.
Мы создадим очень похожую таблицу wines, а также таблицу countries, в которой будет храниться название страны и ее текущий президент. Давайте сначала создадим таблицу стран и вставим в нее Южную Африку и Францию с помощью (обратите внимание, что вы можете скопировать и вставить сразу несколько строк кода sqlite):
CREATE TABLE countries (id integer, name varchar(30), president varchar(30));
INSERT INTO countries VALUES (1, "South Africa", "Jacob Zuma");
INSERT INTO countries VALUES(2, "France", "Francois Hollande");
И затем мы можем воссоздать нашу таблицу вин с помощью:
CREATE TABLE wines (id integer, kind varchar(30), country_id integer);
INSERT INTO wines VALUES (1, "Pinotage", 1);
INSERT INTO wines VALUES (2, "Shiraz", 1);
INSERT INTO wines VALUES (3, "Pinot Noir", 2);
Теперь давайте посмотрим, какие вина есть в Южной Африке:
SELECT kind FROM wines JOIN countries ON country_id=countries.id WHERE countries.name="South Africa";
И вы должны увидеть:
Pinotage
Shiraz
И это охватывает базовое соединение. Обратите внимание, что Sqlite многое делает для вас. В приведенном выше операторе соединения по умолчанию используется INNER JOIN, хотя мы просто используем ключевое слово JOIN. Также нам не нужно указывать wines.country_id, так как это однозначно. С другой стороны, если мы попробуем команду:
SELECT kind FROM wines JOIN countries ON country_id=id WHERE country_id=1;
Мы получим сообщение об ошибке Ошибка: неоднозначное имя столбца: id. Что вполне справедливо, так как обе наши таблицы имеют столбец id. Но в целом Sqlite довольно снисходителен. Его сообщения об ошибках, как правило, упрощают поиск и устранение любых проблем, что помогает ускорить процесс разработки.
Для получения дополнительной помощи по синтаксису, вот ссылка на учебник с хорошим обзором большинства типов соединения.
Наконец, Sqlite имеет оболочки и драйверы для всех основных языков и может работать на большинстве систем. [Список многих из них можно найти здесь](http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers target=_blank).