SQLite

📜 Общее описание и историческая справка

SQLite — это встраиваемая реляционная СУБД с открытым исходным кодом, не требующая отдельного серверного процесса. Разработана Д. Ричардом Хиппом в 2000 году для использования в военных проектах США. Изначально создавалась как компактная библиотека на C для встраивания в приложения. Отличительная особенность: вся база данных хранится в одном кросс-платформенном файле, а интерфейс предоставляется через прямые вызовы функций. SQLite является самой распространённой СУБД в мире благодаря использованию в Android, iOS, браузерах (Chrome, Firefox), многих десктопных приложениях, а также во встраиваемых системах. Версии: 2.x (2001), 3.0 (2004) с переработанным API и улучшенной производительностью. SQLite полностью соответствует ACID и поддерживает большинство стандарта SQL-92.

Источники: Официальная история SQLite, книга "The Definitive Guide to SQLite" (Grant Allen, Apress).

🏭 Область применения и рекомендации

SQLite идеально подходит для:

  • Мобильных приложений (локальное хранение данных).
  • Десктопных программ (браузеры, медиаплееры, менеджеры паролей).
  • Встраиваемых систем (IoT, автомобильная электроника).
  • Тестирования и прототипирования.
  • Небольших сайтов с низкой нагрузкой (до 100k запросов/день).

Рекомендации: не используйте SQLite в высоконагруженных системах с множеством параллельных пишущих подключений (ограничения на блокировки записи). Для чтения — отлично масштабируется.

🛠️ Как создать/инициировать базу

База SQLite создаётся автоматически при первом обращении к файлу через командную строку или API:

# В терминале (команда sqlite3)
sqlite3 mydatabase.db

# Создаём таблицу
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);

# Выход
.exit

В приложении (Python, C#, Java) достаточно открыть соединение с файлом.

💡 Основные нюансы языка (SQL в SQLite)

SQLite поддерживает стандартный SQL, но имеет ряд ограничений и особенностей:

  • Динамическая типизация: любой столбец может хранить данные любого типа (кроме INTEGER PRIMARY KEY).
  • Отсутствие полной поддержки прав доступа на уровне пользователей.
  • Поддержка JSON (начиная с версии 3.38) и полнотекстового поиска (FTS5).
  • Ограниченная поддержка ALTER TABLE (можно добавить/переименовать столбец, но не удалить).
  • Встроенная поддержка временных таблиц и триггеров.
  • PRAGMA-команды для настройки поведения (синхронность, кэш и т.д.).

🔑 Наборы ключевых команд

-- Показать все таблицы
.tables

-- Вывести структуру таблицы
.schema products

-- Импорт CSV
.mode csv
.import data.csv mytable

-- Экспорт в SQL
.dump

-- Включить отображение заголовков
.headers on

-- Установить режим вывода
.mode column

📊 Построение запросов (сложные примеры)

Рекурсивный запрос для генерации последовательности:

WITH RECURSIVE cnt(x) AS (
   SELECT 1
   UNION ALL
   SELECT x+1 FROM cnt WHERE x < 100
)
SELECT x FROM cnt;

Оконные функции (доступны с версии 3.25):

SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

Полнотекстовый поиск с использованием FTS5:

-- Создание виртуальной таблицы
CREATE VIRTUAL TABLE docs USING fts5(title, content);

-- Поиск
SELECT * FROM docs WHERE docs MATCH 'database AND sqlite';

📚 Ссылки на документы, статьи, книги