Внутренние таблицы SQLite
В данном посте описаны внутренние таблицы SQLite: их строение и назначение.
SQLITE_MASTER
Первая страница файла базы данных – это корневая страница «table B – tree», содержащая специальную таблицу «sqlite_master» (либо «sqlite_temp_master» для временной базы данных). Структура таблицы описана в таблице 1.
В данной таблице одна строка – это один объект базы данных. В дополнение к пользовательским объектам в «sqlite_master» хранятся и внутренние объекты базы, за исключением самой таблицы «sqlite_master». Имена внутренних объектов базы начинаются с «sqlite_», соответственно база запрещает пользователю создавать объекты, имена которых начинаются с «sqlite_».
SQLITE_SEQUENCE
SQLITE_STAT1
SQLITE_STAT2
Устаревшая внутренняя таблица для версий SQLite 3.6.18 – 3.7.8. Содержала дополнительную информацию о распределении ключей.
SQLITE_STAT3
SQLITE_STAT4
SQLITE_STAT3 vs SQLITE_STAT4
sqlite_stat4 является обобщением таблицы sqlite_stat3. В таблице sqlite_stat3 предоставляется информация о крайнем левом столбце индекса, тогда как таблица sqlite_stat4 предоставляет информацию обо всех столбцах индекса.
RECORD FORMAT
Формат записи (RF) определяет последовательность значений, соответствующих столбцов в таблице или индексе. RF определяет количество столбцов, тип данных каждого столбца и содержание каждого столбца. Для записи используется VARINT (целое переменной длинны, 1-9 байт в длину, метод статического кодирования Хаффмана).
Заголовок RF начинается с одного VARINT, который определяет общее число байт в заголовке (включая сам VARINT). Затем следует несколько VARINT (по одному на каждый столбец таблицы/индекса). Эти дополнительные числа называются Serial Type значения которых указаны в таблице 2. Значение каждого столбца идет сразу за заголовком.
Пример для понимания работы SQLITE_STAT1:
Допустим, имеется база данных:
CREATE TABLE test (a, b, c);
CREATE INDEX ind ON test(«a»,«b»,«c»)
Данные таблицы test:
Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
Базы данных
Системные таблицы SQLite
Системные таблицы SQLite могут запрашиваться в базе данных с помощью оператора SELECT, как и любая другая таблица.
Ниже приведен список системных таблиц SQLite, которые обычно используются.
| Системная таблица | Описание |
|---|---|
| sqlite_master | Главный список всех объектов базы данных в базе данных и SQL, используемый для создания каждого объекта. |
| sqlite_sequence | «Перечисляет последний порядковый номер, используемый для столбца AUTOINCREMENT в таблице. Таблица sqlite_sequence будет создана только после того, как в базе данных будет определен столбец AUTOINCREMENT, а в базе данных будет сгенерировано и использовано хотя бы одно значение порядкового номера.» |
| sqlite_stat1 | Эта таблица создается командой ANALYZE для хранения статистической информации об анализируемых таблицах и индексах. Эта информация будет позже использована оптимизатором запросов. |
Обзор системных таблиц
Давайте дополнительно изучим информацию, которую вы можете найти в каждой из этих системных таблиц.
1. sqlite_master
Таблица sqlite_master содержит следующие столбцы:
| Столбец | Описание |
|---|---|
| type | Тип объекта базы данных, такой как таблица, индекс, триггер или представление. |
| name | Имя объекта базы данных. |
| tbl_name | Имя таблицы, с которой связан объект базы данных. |
| rootpage | Корневая страница. |
| sql | SQL используется для создания объекта базы данных. |
2. sqlite_sequence
Таблица sqlite_sequence содержит следующие столбцы:
| Столбец | Описание |
|---|---|
| name | Имя таблицы, связанное со столбцом AUTOINCREMENT. |
| seq | Последний порядковый номер, использованный в столбце AUTOINCREMENT. |
3. sqlite_stat1
Таблица sqlite_stat1 содержит следующие столбцы:
SQLite — замечательная встраиваемая БД (часть 3)
Третья часть — тонкости и особенности.
Эта часть является сборной солянкой всевозможных особенностей SQLite. Я собрал здесь (на мой взгляд) наиболее важные темы, без понимания которых невозможно постичь SQLite нирвану.
Поскольку, опять-таки, информации очень много, то формат статьи будет такой: небольшая вводная в интересную тему и ссылка на родной сайт, где подробности. Сайт, увы, на английском.
Использование SQLite в многопоточных приложениях
SQLite может быть собран в однопоточном варианте (параметр компиляции SQLITE_THREADSAFE = 0).
В этом варианте его нельзя одновременно использовать из нескольких потоков, поскольку полностью отсутствует код синхронизации. Зачем? Для бешеной скорости.
Проверить, есть ли многопоточность можно через вызов sqlite3_threadsafe(): если вернула 0, то это однопоточный SQLite.
По умолчанию, SQLite собран с поддержкой потоков (sqlite3.dll).
Есть два способа использования многопоточного SQLite: serialized и multi-thread.
Serialized (надо указать флаг SQLITE_OPEN_FULLMUTEX при открытии соединения). В этом режиме потоки могут как угодно дергать вызовы SQLite, никаких ограничений. Но все вызовы блокируют друг друга и обрабатываются строго последовательно.
Multi-thread (SQLITE_OPEN_NOMUTEX). В этом режиме нельзя использовать одно и то же соединение одновременно из нескольких потоков (но допускается одновременное использование разных соединений разными потоками). Обычно используется именно этот режим.
Формат данных
База данных SQLite может хранить (текстовые) данные в UTF-8 или UTF-16.
Набор вызовов API состоит из вызовов, которые получают UTF-8 (sqlite3_XXX) и вызовов, которые получают UTF-16 (sqlite3_XXX16).
Если тип данных интерфейса и соединения не совпадает, то выполняется конвертация «на лету».
Всегда используйте UTF-8.
Поддержка UNICODE
И некоторые собирают SQLite DLL уже с ним.
Типы данных и сравнение значений
Как уже говорилось, SQLIte позволяет записать в любой столбец любое значение.
Значение внутри БД может принадлежать к одному из следующих типов хранения (storage class):
NULL,
INTEGER (занимает 1,2,3,4,6 или 8 байт),
REAL (число с плавающей точкой, 8 байт в формате IEEE),
TEXT (строка в формате данных базы, обычно UTF-8),
BLOB (двоичные данные, хранятся «как есть»).
Порядок сортировки значений разных типов:
— NULL меньше всего (включая другой NULL);
— INTEGER и REAL меньше любого TEXT и BLOB, между собой сравниваются арифметически;
— TEXT меньше любого BLOB, между собой сравниваются на базе своих collation;
— BLOB-ы сравниваются между собой через memcmp().
SQLite выполняет неявные преобразования типов «на лету» в нескольких местах:
— при занесении значения в столбец (тип столбца задает рекомендацию по преобразованию);
— при сравнении значений между собой.
Столбец может иметь следующие рекомендации приведения типа: TEXT, NUMERIC, INTEGER, REAL, NONE.
Значения BLOB и NULL всегда заносятся в любой столбец «как есть».
В столбец TEXT значения TEXT заносятся «как есть», значения INTEGER и REAL становятся строками.
В столбец NUMERIC, INTEGER числа записываются «как есть», а строки становятся числами, если _могут_ (то есть допустимо обратное преобразование «без потерь»).
Для столбца REAL правила похожи на INTEGER(NUMERIC); отличие в том, что все числа представлены в формате с плавающей запятой.
В столбец NONE значения заносятся «как есть» (этот тип используется по умолчанию, если не задан другой).
При сравнении значений разного типа между собой может выполняться дополнительное преобразование типов.
При сравнении числа со строкой, если строка может быть преобразована в число «без потерь», она становится числом.
Отмечу здесь, что в SQLite в уникальном индексе может быть сколько угодно NULL значений (с этим согласен Oracle и не согласен MS SQL).
База данных в памяти
Если в вызове sqlite3_open() передать имя файла как «:memory:», то SQLite создаст соединение к новой (чистой) БД в памяти.
Это соединение абсолютно неотличимо от соединения к БД в файле по логике использования: доступен тот же набор SQL команд.
Увы, не существует возможности открыть два соединения к одной и той же БД в памяти.
UPD: Уже, оказывается, можно открыть два соединения к одной БД в памяти.
Присоединение одновременно к нескольким БД
Чтобы открыть соединение к БД используется вызов sqlite3_open().
В любой момент времени мы можем к открытому соединению присоединить еще до 10 баз данных через SQL команду ATTACH DATABASE.
Теперь все таблицы БД в файле db1.sqlite3 стали прозрачно доступны в нашем соединении.
Для разрешения конфликтов имен следует использовать имя присоединения (основная база называется «main»):
Ничего не мешает присоединить к БД новую базу в памяти и использовать ее для кэширования и пр.
Это очень полезная возможность. Присоединяемые БД должны иметь формат данных такой же, как и у основной БД, иначе — ошибка.
Временная база данных
Передайте пустую строку вместо имени файла в sqlite3_open() и будет создана временная БД в файле на диске. Причем, после закрытия соединения к БД, она будет удалена с диска.
Тонкие настройки БД через команду PRAGMA
SQL команда PRAGMA служит для задания всевозможных настроек у соединения или у самой БД:
Настройку соединения (очевидно) следует проводить сразу после открытия и до его использования.
Полное описание всех параметров находится здесь.
Остановлюсь на важнейших вещах.
Журнал и фиксация транзакций
Вот и подошли к теме, овладение которой сразу переводит вас на третий уровень магистра SQLite.
SQLite тщательно блюдет целостность данных в БД (ACID), реализуя механизм изменения данных через транзакции.
Кратко о транзакциях: транзакция либо полностью накатывается, либо полностью откатывается. Промежуточных состояний быть не может.
Отсюда, кстати, и жалобы на «медленность» SQLite. SQLite может вставлять и до 50 тыс записей в секунду, но фиксировать транзакций он не может больше, чем
Именно поэтому, не получается вставлять записи быстро, используя неявную транзакцию.
При настройках по умолчанию SQLite гарантирует целостность БД даже при отключении питания в процессе работы.
Достигается подобное изумительное поведение ведением журнала (специального файла) и хитроумным механизмом синхронизации изменений на диске.
Кратенько обновление данных в БД работает так:
— до любой модификации БД SQLite сохраняет изменяемые страницы из БД в отдельном файле (журнале), то есть просто копирует их туда;
— убедившись, что копия страниц создана, SQLite начинает менять БД;
— убедившись, что все изменения в БД «дошли до диска» и БД стала целостной, SQLite стирает журнал.
Подробно атомарность механизма транзакций описана тут.
Если SQLite открывает соединение к БД и видит, что журнал уже есть, он соображает, что БД находится в незавершенном состоянии и автоматически откатывает последнюю транзакцию.
То есть механизм восстановления БД после сбоев, фактически, встроен в SQLite и работает незаметно для пользователя.
Это означает, что файл журнала удаляется после завершения транзакции. Сам факт наличия файла с журналом в этом режиме означает для SQLite, что транзакция не была завершена, база нуждается в восстановлении. Файл журнала имеет имя файла БД, к которому добавлено «-journal».
В режиме TRUNCATE файл журнала обрезается до нуля (на некоторых системах это работает быстрее, чем удаление файла).
В режиме PERSIST начало файла журнала забивается нулями (при этом его размер не меняется и он может занимать кучу места).
В режиме MEMORY файл журнала ведется в памяти и это работает быстро, но не гарантирует восстановление базы при сбоях (копии данных-то нету на диске).
А можно и совсем отключить журнал (PRAGMA journal_mode = OFF). В этой ситуации перестает работать откат транзакций (команда ROLLBACK) и база, скорее всего, испортится, если программа будет завершена аварийно.
Для базы данных в памяти режим журнала может быть только либо MEMORY, либо OFF.
Вернемся немного назад. Как же SQLite «убеждается», что база всегда будет целостной?
Мы знаем, что современные системы используют хитроумное кэширование для повышения производительности и могут откладывать запись на диск.
Допустим, SQLite завершил запись в БД и хочет стереть файл журнала, чтобы отметить факт фиксации транзакции.
А вдруг файл сотрется раньше, чем обновится БД?
Если в этот промежуток времени отключится питание, то журнала уже не будет, а БД еще не будет целостной — потеря данных!
Короче говоря, хитроумный механизм фиксации изменений должен полагаться на некоторые гарантии со стороны дисковой системы и ОС.
PRAGMA synchronous задает степень «паранойи» SQLite на это счет.
Режим OFF (или 0) означает: SQLite считает, что данные фиксированы на диске сразу после того как он передал их ОС (то есть сразу после вызова соот-го API ОС).
Это означает, что целостность гарантирована при аварии приложения (поскольку ОС продолжает работать), но не при аварии ОС или отключении питания.
Режим синхронизации NORMAL (или 1) гарантирует целостность при авариях ОС и почти при всех отключениях питания. Существует ненулевой шанс, что при потере питания в самый неподходящий момент база испортится. Это некий средний, компромисный режим по производительности и надежности.
Режим FULL гарантирует целостность всегда и везде и при любых авариях. Но работает, разумеется, медленнее, поскольку в определенных местах делаются паузы ожидания. И это режим по умолчанию.
Итак, осталась неохваченной только тема журнала типа WAL.
Режим журнала WAL
По умолчанию, режим журнала БД всегда «возвращается» в DELETE. Допустим, мы открыли соединение к БД и установили режим PERSIST. Изменили данные, закрыли соединение.
На диске остался файл журнала (начало которого забито нулями).
Открываем соединение к БД снова. Если не задать режим журнала в этом соединении, он опять будет работать в DELETE. Как только мы обновим данные, механизм фиксации транзакций сотрет файл журнала.
Режим журнала WAL работает иначе — он «постоянный». Как только мы перевели базу в режим WAL, она останется в этом режиме, пока ей явно не поменяют режим журнала на другой.
Итак, зачем он нужен?
Изначально SQLite проектировалась как встроенная БД. Архитектура разделения одновременного доступа к данным была устроена примитивно: одновременно несколько соединений могут читать БД, а вот записывать в данный момент времени может только одно соединение. Это, как минимум, означает, что пишущее соединение ждет «освобождения» БД от читающих. При попытке записать в «занятую» БД приложение получает ошибку SQLITE_BUSY (не путать с SQLITE_LOCKED!). Достигается этот механизм разделения доступа через API блокировки файлов (которые плохо работают на сетевых дисках, поэтому там не рекомендуется использовать SQLite; узнать больше )
В режиме WAL (Write-Ahead Logging) «читатели» БД и «писатели» в БД уже не мешают друг другу, то есть допускается модификация данных при одновременном чтении. Короче говоря, это шаг в сторону больших и серьезных СУБД, в которых все так и есть. Утверждается также, что SQLite в WAL работает быстрее.
Но есть и недостатки:
— требуется некоторые дополнительные ништяки от ОС (unix и Windows имеют эти ништяки);
— БД занимает несколько файлов (файлы «XXX-wal» и «XXX-shm»);
— плохо работает на больших транзакциях (условно, если транзакция больше 50 Мбайт);
— нельзя открыть такую БД в режиме «только чтение»;
— возникает дополнительная операция checkpoint.
Фактически, в режиме WAL данные БД разделяются между БД и файлом журнала. Операция checkpoint переносит данные в БД. По умолчанию, это делается автоматически, если журнал занял 1000 страниц БД.
То есть, идут быстрые COMMIT-ы и вдруг какой-то COMMIT задумался и начал делать checkpoint. Если такое поведение нежелательно, можно делать checkpoint вручную (когда все спокойно), можно это делать и в отдельном процессе.
Пределы
Несмотря на миниатюрность, SQLite в реальности не накладывает серьезных ограничений на размеры полей, таблиц или БД.
По умолчанию, BLOB или строкое значение могут занимать 1 Гбайт и это же ограничение размера одной записи (можно поднять до 2^31 — 1, параметр SQLITE_MAX_LENGTH).
Количество столбцов: 2000 (можно поднять до 32767, SQLITE_MAX_COLUMN).
Размер SQL оператора: 1 МБайт (1073741824 байт, SQLITE_MAX_SQL_LENGTH).
Одновременный join: 64 таблицы.
Присоединить баз к соединению: 10 (до 62, SQLITE_MAX_ATTACHED)
Максимальное количество страниц в БД: 1073741823 (до 2147483646, SQLITE_MAX_PAGE_COUNT).
Если задать размер страницы 65636 байт, то максимальный размер БД будет примерно 14 Терабайт.
Максимальное число записей в таблице: 2^64 — 1, но на практике, конечно, ограничение размера вступит раньше.
UDP: Ссылки по оптимизации SQLite: 1 2 android-1 android-2
SQLite: как организовывать таблицы
Apr 10 · 12 min read
Организация данных — все равно, что поддержание порядка на рабочем месте. Хорошо всегда знать, где что находится, и в случае необходимости освобождать больше места для хранения или работы.
В настоящее время мир состоит из библиотек реляционных и нереляционных баз данных, причем и у тех, и у других свои преимущества и недостатки. В этой статье сфокусируемся на рассмотрении реляционных баз данных.
Увидим, как с помощью библиотеки SQLite с оздаются таблицы и как минимизируется добавление неправильных данных через предоставление дополнительной информации в момент построения таблицы и после ее создания. Но прежде попытаемся дать общее понятие о том, что представляет собой эта библиотека SQLite.
SQLite
SQLite — это реляционная база данных, основанная на языке SQL. «Реляционная» означает, что в базе данных есть таблицы, которые связаны друг с другом через общие атрибуты. Эти таблицы представляют собой двумерные массивы информации, состоящие из строк (или записей) и столбцов (или атрибутов).
Ниже показан пример таблицы с тремя записями и тремя атрибутами, содержащими различные типы данных. Построение примера осуществлено с использованием приложения DB Browser для SQLite.
SQL — это стандартизированный язык. Тем не менее разработчики вольны соблюдать или не соблюдать стандарты до определенной степени. Это зависит от конкретной реализации. Одной из таких реализаций и является SQLite. В отличие от MySQL или PostgreSQL, это более простая библиотека, созданная для использования локально и без необходимости внешних серверов.
Бессерверность SQLite обусловливает наличие других функциональных особенностей. Во-первых, это независимость, подразумевающая то, что SQLite не нуждается во внешних ресурсах и поэтому пригодна для использования в любой среде. Во-вторых, это нулевая конфигурация, ведь сервера нет и настраивать нечего.
А кроме того, SQLite транзакционная: все изменения либо происходят полностью, либо не происходят вообще. Транзакции должны соответствовать набору правил под названием ACID. Каждая буква этого акронима обозначает определенные требования.
A расшифровывается как Atomicity («Атомарность») и означает, что изменения происходят в целом и их нельзя разделить. C значит Consistency («Согласованность») и предполагает, что состояние базы данных должно оставаться согласованным после того, как результаты транзакции зафиксированы или произошел откат к предыдущему состоянию. I означает Isolation («Изолированность») и указывает на то, что транзакции видны только в текущем сеансе. Наконец, D расшифровывается как Durability («Долговечность») и означает, что изменения должны быть сохранены.
А теперь создадим базу данных с информацией о расах и подрасах из игры Dungeons and Dragons («Подземелья и драконы»). Почему я выбрал ее? Уж очень она отличается от традиционных примеров и в игру эту мы с удовольствием играем с друзьями.
Начнем создание базы данных с использования следующих команд SQLite в командной строке:
Первой командой меняем каталог в файл, в котором будет база данных, а второй создаем базу данных, активировав библиотеку sqlite3 и выбрав для нее название.
Создание таблицы
Таблицы — это структуры, которые удерживают данные, вставленные в реляционные и подобные им базы данных. Вот обобщенное представление кода для создания таблицы:
В круглых скобках указываем имена атрибутов или столбцов, их типы данных и ограничения на уровне атрибутов или таблицы. Типы данных и ограничения приводить необязательно, но лучше их указать. Так пользователь будет понимать, какая информация хранится в каждом атрибуте.
О том, что такое типы данных и ограничения, расскажем позже. А пока достаточно знать, что они существуют и определяются в момент создания таблицы.
Точка с запятой указывает SQLite, что фрагмент кода завершен и готов к обработке. Создадим новую таблицу races, которая будет содержать информацию для различных рас:
Чтобы вы понимали, race_name — это название той или иной расы в игре «Подземелья и драконы», например Эльфы, Гномы или Дворфы (будем использовать здесь чуть более экзотические примеры). А в ability_score_increase содержатся оценки способностей, по которым раса по своей природе превосходит другие, например Aasimar более харизматичны, поэтому их харизма выше на два пункта.
Атрибут size — это то, насколько велики в среднем по размеру представители той или иной расы, как правило они либо маленькие, либо средние. Speed — это максимальное расстояние, которое представители расы способны пробежать за один раунд сражения. Languages — это языки, на которых говорит раса, а age — это средняя продолжительность жизни представителей расы.
race_id — это число, присваиваемое расе как целочисленное свойство. К этому важному свойству мы вернемся позже, когда будем говорить об ограничениях.
Многие скажут, что многовато новой информации для того, кто никогда не играл в эту настольную игру или даже не слышал о ней. На самом деле вовсе и не нужно понимать, что означают атрибуты. Что действительно важно — это их типы данных и ограничения.
А теперь заполним таблицу. Для этого напишем оператор INSERT с тремя записями:
Посмотрите: мы передаем название таблицы, а также ее атрибуты, куда вставляем значения, за которыми идут фактические, желаемые нами значения. Вот данные, сохраняющиеся в races:
sqlite>.read races.txt автоматически создаст таблицу и заполнит ее.
Теперь мы умеем создавать таблицы и вставлять в них значения. Дальше узнаем, что такое типы данных и ограничения и как они помогают идентифицировать и поддерживать информацию в таблице. Сначала рассмотрим типы данных.
Типы данных
Вообще говоря, тип данных TEXT представляет собой текстовую информацию, заключенную в кавычки или отделенную строками. В таблице races атрибут race_name установлен для хранения данных TEXT текстового типа, а строки Aasimar, Kalashtar и Genasi заполняют этот атрибут.
Тип данных INTEGER представляет собой целые числа (положительные или отрицательные) размером от 1 до 8 байтов. Speed — хороший пример того, где использовать целые числа, потому что этот атрибут обозначает количество футов, которые персонаж способен пробежать в раунде сражения.
REAL — это тип данных, содержащий числа длиной более 8 байтов или десятичные и экспоненциальные числа, например 1,5 или 2⁴.
NULL используется для отсутствующей или неизвестной информации. Допустим, нам не удалось найти, на каких языках говорят представители расы Genasi. В этом случае на какое-то время будем сохранять это значение как NULL.
Имейте в виду, что SQLite, в отличие от других библиотек SQL, принимает различные данные для хранения в атрибутах, тип данных которых предопределен. Например, определение атрибута как содержащего данные TEXT не помешает SQLite принимать целые числа или любые другие типы данных.
И тем не менее определение типа данных дает пользователю прозрачный намек на то, какие данные должны храниться в каждом атрибуте, позволяя лучше организовывать таблицы.
Ограничения
Ограничения помогают устанавливать границы в атрибутах, значениях по умолчанию или связях между таблицами. Начнем с последнего. Есть два ограничения, которые играют важную роль в том, что называется нормализацией. Это PRIMARY KEY (первичный ключ) и FOREIGN KEY (внешний ключ).
Прежде чем переходить к более подробному рассмотрению этих двух ограничений, создадим новую таблицу subraces, содержащую связь с races, и попробуем понять, что такое нормализация.
Благодаря такой ссылке мы сохраняем информацию о подгруппах, сводя к минимуму повторы этой информации в базе данных. Посмотрите на значения в таблице subraces:
Без нормализации нам пришлось бы создавать таблицу типа races_subraces со следующим кодом: