Файлы SQLite: структура, варианты использования и ключевые ограничения

Файл SQLite представляет собой один файл на диске, который содержит всю реляционную базу данных — таблицы, индексы и схему вместе. Собственная документация SQLite называет его «основным файлом базы данных» и отмечает, что в нем «обычно содержится полное состояние базы данных SQLite». Слово «обычно» в этом предложении играет ключевую роль.

Если вам когда-либо передавали файл с расширением .db, .sqlite или .sqlite3 и вы задавались вопросом, все ли данные вы получили, то это именно тот формат, в котором стоит разобраться как следует.

Что на самом деле представляет собой файл SQLite

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

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

Масштаб легко недооценить. На собственной странице «О проекте» SQLite говорится, что это «самая широко развертываемая база данных в мире с большим количеством приложений, чем мы можем сосчитать».

Что находится внутри файла

100-байтовый заголовок

Первые байты идентифицируют формат. Со смещения 0 файл содержит 16-байтовую строку заголовка: SQLite format 3\000. По этой сигнатуре инструменты распознают файл независимо от его расширения.

Следующее поле имеет большее значение, чем кажется. Со смещения 16 находится 2-байтовое целое число, содержащее «размер страницы базы данных в байтах». В документации указано, что оно «должно быть степенью двойки от 512 до 32768 включительно или значением 1, представляющим размер страницы 65536». Все многобайтовые поля в заголовке хранятся в порядке от наиболее значимого байта к наименее значимому.

Еще два байта следуют по смещениям 18 и 19: версия записи и версия чтения формата файла. В документации отмечается, что это значение равно «1 для устаревшего формата; 2 для WAL».

Страницы, а не строки

Под заголовком файл представляет собой стек страниц фиксированного размера. Спецификация выражается предельно ясно: «Основной файл базы данных состоит из одной или нескольких страниц. Размер страницы является степенью двойки от 512 до 65536 включительно. Все страницы в одной базе данных имеют одинаковый размер».

Страницы нумеруются с 1, а максимальный номер страницы — 4,294,967,294. Таблицы и индексы живут внутри этих страниц в виде структур B-tree, поэтому текстовый редактор покажет вам очень мало.

Файлы-сателлиты, о которых никто не упоминает

Это та часть, на которой многие попадаются. В документации говорится, что полное состояние «обычно» находится в одном файле. Затем приводится исключение. Во время транзакции SQLite «сохраняет дополнительную информацию во втором файле, называемом "журналом отката" (rollback journal)». В режиме WAL этот второй файл представляет собой журнал упреждающей записи (write-ahead log).

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

Как открыть файл SQLite

Существует три пути, и выбор правильного зависит от того, что вы собираетесь делать дальше.

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

Выполнение запросов через командную строку или библиотеку. Оболочка sqlite3 и привязки стандартных библиотек в Python, Node и большинстве других языков читают этот формат напрямую. Этот путь подходит, когда вы уже знаете схему и хотите получить конкретное значение.

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

Почему инструмент может сообщить, что файл не является базой данных

Спецификация объясняет и это. Каждый валидный файл начинается с 16-байтовой строки заголовка: SQLite format 3\000. Если программа чтения открывает файл и не находит эту сигнатуру со смещения 0, значит, ей передали не базу данных SQLite.

Большинство случаев объясняются тремя обычными причинами. Файл передался не полностью, поэтому заголовок на месте, но остальная часть усечена. Файл зашифрован или обернут приложением, поэтому первые байты представляют собой что-то другое. Либо расширение вводит в заблуждение, и на самом деле вы получили обычный экспорт, переименованный кем-то из лучших побуждений.

Какого максимального размера может достичь файл SQLite

Больше, чем обычно предполагается в таком вопросе. На странице ограничений SQLite указано, что максимальный размер файла базы данных составляет 4,294,967,294 страницы. При максимальном размере страницы 65,536 байт максимальный размер базы данных составляет около 281 терабайта.

Страница на удивление честна в отношении этой цифры. Там отмечается, что верхний предел «не тестировался, поскольку у разработчиков нет доступа к оборудованию, способному достичь этого лимита».

Количество строк упирается в ту же стену. Теоретический максимум составляет 2^64 строк в таблице. В документации указывается, что этот предел «недостижим, поскольку сначала будет достигнут максимальный размер базы данных в 281 терабайт».

Для практической работы полезный вывод прямо противоположен ограничению. Если кто-то передает вам файл .db и предупреждает, что он большой, формат почти наверняка не станет для вас препятствием. Размер страницы, выбранный при создании файла, и наличие в нем индексов повлияют на работу гораздо сильнее, чем любой задокументированный потолок.

Где вы можете встретить файлы SQLite

• Экспорт из приложений. Настольные и мобильные приложения часто хранят историю, настройки и журналы сообщений в файле SQLite, который вы можете скопировать.
• Передача данных для аналитики. Инженеры отправляют снимок в виде одного файла вместо предоставления доступа к базе данных.
• Устройства и телеметрия. Встраиваемые системы выполняют запись локально, так как нет сервера для связи.
• Архивы. Долгосрочная стабильность формата делает его популярным выбором для наборов данных, которые должны оставаться читаемыми на протяжении многих лет.
• Внутренние механизмы браузеров и инструментов. Многие локальные инструменты сохраняют состояние таким образом, поэтому данное расширение часто фигурирует в тикетах службы поддержки.

Для чего нужны файлы WAL и журналы

Возможно, вы копировали файл .db и обнаруживали рядом с ним файл -wal или -journal. Это те самые сопутствующие файлы, которые описывает спецификация, и их удаление — верный способ потерять данные.

Журнал отката (rollback journal) — это более старый механизм. Перед изменением страницы SQLite записывает исходную версию этой страницы в журнал. Если процесс записи прерывается, оригинал можно восстановить, благодаря чему транзакция успешно переносит сбой.

Журнал упреждающей записи (write-ahead log) меняет этот порядок. Изменения сначала записываются в журнал, а основной файл обновляется позже. Заголовок указывает, в каком режиме находится база данных. Версия записи формата файла со смещения 18 равна «1 для устаревшего формата; 2 для WAL».

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

Поэтому, когда вам передают файл базы данных, задайте два вопроса. Было ли приложение корректно закрыто в момент снятия копии и шло ли в комплекте что-то еще? Обычно на оба вопроса ответ положительный, но тот единственный раз, когда это не так, — это как раз тот случай, когда цифры будут незаметно расходиться с реальными данными в продакшене.

Файл SQLite против CSV и Parquet

	Файл SQLite	CSV	Parquet
Структура	Множество таблиц, один файл	Одна таблица, один файл	Одна таблица, один файл или папка
Типы данных	Хранятся вместе с данными	Определяются программой чтения	Хранятся вместе с данными
Связи	Сохраняются с помощью ключей и индексов	Теряются	Теряются
Читаемость человеком	Нет	Да	Нет
Предназначен для запросов	Да, с помощью SQL	Нет	Да, аналитическими движками
Частая проблема	Отсутствие сопутствующего файла журнала или WAL	Угадывание типов и разделителей	Поддержка инструментария

Если вы регулярно работаете с этими форматами, наши руководства по файлам Parquet и файлам TSV подробно описывают эти два формата.

Почему команды выбирают этот формат

Не нужно ничего запускать. Поскольку SQLite «не имеет отдельного серверного процесса», передача данных представляет собой простое копирование файла, а не запрос на выделение ресурсов.

Типы данных сохраняются при переносе. Столбец с датой остается датой. Любой, кто видел, как программа чтения CSV превращает идентификатор в экспоненциальную запись, понимает всю ценность этого.

Связи также сохраняются. Несколько связанных таблиц остаются вместе в одном артефакте, поэтому объединения, которые делали данные осмысленными, по-прежнему доступны.

Надежность заложена в архитектуру. SQLite указывает транзакции «даже после сбоя питания» среди своих ключевых функций, именно поэтому на нее полагается так много встраиваемого программного обеспечения.

Ограничения, о которых стоит знать

Один файл, один пишущий в каждый момент времени. Движок является встраиваемым, а не серверным, поэтому модель параллелизма отличается от клиент-серверных баз данных. Это архитектурное решение, а не недостаток, но оно определяет, для чего подходит этот файл.

Размер страницы фиксируется при создании. Каждая страница в базе данных имеет одинаковый размер, и этот размер записывается в заголовок. Вы выбираете его один раз.

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

Непрозрачность. Файл SQLite нельзя быстро просмотреть глазами, как CSV. Для его чтения требуется специальный инструмент, и именно это препятствие часто тормозит анализ.

Как извлечь ответы из файла SQLite

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

Более короткий путь — задать вопрос напрямую. Powerdrill Bloom позволяет работать с данными на естественном языке и возвращает ответ с указанием источника. На главной странице обещают, что «каждое число возвращается с указанием страницы, строки и стоящей за ним цифры». На основе этого в том же рабочем пространстве можно создавать диаграммы, таблицы или презентации.

Если это ваш регулярный рабочий процесс, стоит знать о двух связанных страницах. Чат с базой данных описывает диалоговый путь работы со структурированными данными, а Текст в SQL — случай, когда вам нужен сам запрос. Если же переданные данные представляют собой плоский экспорт, этот путь описан на странице ИИ-ассистент для CSV.

Еще одна вещь, о которой сообщает заголовок

Поскольку размер страницы находится по фиксированному смещению, вы можете узнать кое-что полезное о файле еще до того, как откроете его должным образом. База данных, созданная с размером страницы 4096 байт, ведет себя иначе, чем база данных, созданная со страницами размером 65 536 байт. Этот выбор был сделан один раз, при создании файла.

Это не то число, которое можно легко изменить впоследствии. Это решение относится скорее к категории проектирования схемы, нежели к простым настройкам.

Заключение

Файл SQLite — это целая база данных в одном артефакте. Он содержит 16-байтовую сигнатуру, размер страницы, записанный со смещения 16, и стек страниц фиксированного размера, в которых хранятся ваши таблицы и индексы. Он легко переносится, сохраняет типы данных и остается читаемым на протяжении многих лет.

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

Когда у вас есть файл и вам нужен ответ, а не схема, попробуйте Powerdrill Bloom и задайте свой вопрос к данным напрямую.

Часто задаваемые вопросы

В чем разница между .db, .sqlite и .sqlite3?

Никакой структурной разницы. Все три расширения являются общепринятыми для одного и того же формата, а настоящим идентификатором служит 16-байтовая строка заголовка SQLite format 3\000 в начале файла.

Как узнать, какой размер страницы использует файл SQLite?

Это записано в заголовке. 2-байтовое целое число со смещения 16 содержит размер страницы в байтах. Оно должно быть степенью двойки от 512 до 32768 или значением 1, представляющим 65536.

Является ли файл SQLite полной базой данных?

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

Могу ли я открыть файл SQLite в Excel?

Не напрямую, так как в файле хранятся страницы B-tree, а не текстовые строки. Обычно сначала экспортируют таблицу в CSV или используют инструмент, который считывает формат базы данных и возвращает результаты.

Является ли SQLite бесплатным для коммерческого использования?

Да. Разработчики SQLite заявляют, что исходный код находится в общественном достоянии и «бесплатен для использования в любых целях, коммерческих или личных».