1. Введение

Дополнительная таблица это таблица, которая присоединена к тому же самому b-tree как индекс. Дополнительная таблица позволяет содержанию индекса быть запрошенным или измененным, как будто индекс был обычной таблицей.

Дополнительные таблицы предназначаются только для анализа и отладки. Это не особенность, которую большинство разработчиков приложений должны понять или даже знать о ней. Это только для экспертов.

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

2. Детали

Каждая таблица и каждый индекс в SQLite сохранены в отдельном b-дереве в файле базы данных. Каждое b-дерево определяется его номером страницы корня. Номер страницы корня для любого индекса или таблицы может быть найден, запросив столбец "rootpage" таблицы sqlite_schema table. См. здесь и здесь для дальнейшего описания дизайна.

Обычно b-деревья для таблиц и индексов немного отличаются. B-дерево таблицы содержит 64-битное целое число ключа и произвольные данные. 64-битный ключ целого числа это ROWID. B-деревья индекса содержат произвольный двоичный ключ и никаких данных. Таким образом, b-деревья таблицы и b-деревья индекса не непосредственно совместимы.

Однако b-дерево для таблицы WITHOUT ROWID находится в том же самом формате как b-дерево индекса. Таким образом к b-дереву индекса можно получить доступ, как будто это была таблица WITHOUT ROWID.

2.1. Вручную составленные дополнительные таблицы

Один способ составить такую таблицу состоит в том, чтобы непосредственно отредактировать таблицу sqlite_schema, чтобы вставить новую строку, которая описывает таблицу. Например, предположите, что схема похожа на это:

CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
CREATE INDEX t1bc ON t1(b,c);

Таблица WITHOUT ROWID, у которой есть та же самая структура, как у инлдекса t1bc, была бы похожа на это:

CREATE TABLE t2(b TEXT,c INT,a INT, PRIMARY KEY(b,c,a)) WITHOUT ROWID;

Чтобы создать постоянную таблицу "t2" для индекса "t1bc", нужно сначала позволить редактировать таблицу sqlite_schema, выполнив "PRAGMA writable_schema=ON". Старайтесь соблюдать предупреждения, которые сопровождают этот PRAGMA. Ошибка может вызвать серьезное повреждение базы данных. Затем вставляют новый вход в таблицу sqlite_schema:

INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)
SELECT 'table','t2','t2',rootpage,
       'CREATE TABLE t2(b,c,a,PRIMARY KEY(b,c,a))WITHOUT ROWID'
       FROM sqlite_schema WHERE name='t1bc';

INSERT выше добавляет новую строку в таблицу sqlite_schema, которая определяет таблицу "t2", имеющую тот же самый формат на диске, как индекс "t1bc", и указывающую на то же самое b-дерево. После добавления этой записи, таблицу sqlite_schema необходимо закрыть и вновь открыть базу данных, чтобы заставить SQLite перечитывать схему. Тогда таблица "t2" может быть запрошена, чтобы видеть содержание индекса "t1bc".

2.1.1. Испорченная база данных

Серьезная проблема с ручным подходом, описанным выше, состоит в том, что после добавления новой таблицы "t2" в "sqlite_schema" файл базы данных технически будет поврежден. Индекс "t1bc" и таблица "t2" укажут на то же самое b-дерево. Это не вызовет непосредственных проблем, хотя нужно избегать VACUUM.

Возможно написать в таблицу "t2", таким образом изменяя содержание индекса. Но выполнение этого вытащит индекс "t1bc" из синхронизации с его родительской таблицей "t1". Индекс вне синхронизации может привести к неправильным результатам запроса.

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

2.2. Переходные дополнительные таблицы

Другой (более безопасный) подход к составлению таблицы должен добавить вход для дополнительной таблицы к внутренней таблице символов SQLITE, не обновляя таблицу "sqlite_schema" на диске. Тем путем дополнительная таблица существует только в связи базы данных и автоматически удалена каждый раз, когда схема перезагружается.

Создание переходной таблицы включает специальный вызов sqlite3_test_control() В отличие от всего другого API SQLite, sqlite3_test_control() подвергается несовместимым изменениям между выпусками и таким образом, механизм, описанный ниже, как гарантируют, не будет работать в будущих выпусках SQLite. Разработчики SQLite не считают это проблемой, потому что дополнительные таблицы не должны использоваться в приложениях.

Чтобы составить переходную дополнительную таблицу, сначала вызовите sqlite3_test_control():

sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 1, tnum);

Параметр "db" это указатель на соединение с базой данных. Параметр "main" это название схемы, в которой должна быть составлена таблица. Параметр "1" позволяет механизм дополнительных таблиц. Параметр "tnum" это страница корня индекса, который должна отразить дополнительная таблица.

После вызова sqlite3_test_control() call выполните CREATE TABLE, который определит таблицу. С позволенным механизмом это CREATE TABLE не составляет реальную таблицу, но вместо этого просто добавляет вход во внутренней таблице символов SQLITE. Обратите внимание на то, что CREATE TABLE должно быть в правильном формате для индекса. Если дополнительная таблица будет иметь неправильное количество колонок или не будет WITHOUT ROWID или будет в других отношениях несовместима с b-деревом индекса, то при ее использовании будет ошибка SQLITE_CORRUPT.

После управления CREATE TABLE отключите механизм дополнительных таблиц:

sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 0, 0);

Другими словами, сделайте тот же самый sqlite3_test_control() кроме изменения последних двух параметров к нолю.

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

2.3. Команда оболочки .imposter

С SQLite 3.16.0 (2017-01-02) оболочка командной строки содержит точечную команду ".imposter", которая делает всю работу подготовки переходной дополнительной таблицы. Вместо того, чтобы сделать множественные вызовы sqlite3_test_control(), выяснить и вызвать совместимый запрос CREATE TABLE, переходная дополнительная таблица может быть построена следующим образом:

.imposter t1bc t2

Конечно, замените желаемым индексом и именами таблиц вместо "t1bc" и "t2" в этом примере. Команда ".imposter" читает схему индекса "t1bc", использует ту информацию, чтобы построить совместимый запрос CREATE TABLE для дополнительной таблицы, затем сделает все необходимые вызовы, чтобы составить переходную таблицу автоматически.

3. Итоговое и заключительное предупреждение

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