 |
|
|
|
|
| WebMoney: WMZ Z294115950220 WMR R409981405661 WME E134003968233 |
Visa 4274 3200 2453 6495 |
|
|
|
|
|
|
| WebMoney: WMZ Z294115950220 WMR R409981405661 WME E134003968233 |
Visa 4274 3200 2453 6495 |
Если вы хотите знать, как библиотека SQLite работает внутренне, необходимо
начать с основательного понимания виртуального ядра базы данных или VDBE.
VDBE происходит прямо в середине потока обработки (см.
архитектурную диаграмму) и таким образом, это,
кажется, касается большинства частей библиотеки. Даже части кода,
которые непосредственно не взаимодействуют с VDBE, обычно находятся в роли
поддержки. VDBE действительно центр SQLite. Эта статья это краткое введение в то, как VDBE работает и в особенности
как различные инструкции VDBE
(описаны здесь) сотрудничают, чтобы сделать
полезные вещи с базой данных. Стиль это обучающая программа, начинаясь с
простых задач и переходя к решению более сложных проблем.
По пути мы посетим большинство подмодулей в библиотеке SQLite.
После завершения этой обучающей программы у вас должно быть довольно хорошее
понимание того, как SQLite работает и вы будете готовы начать изучать
фактический исходный код. VDBE осуществляет виртуальный компьютер, который управляет программой на
языке виртуальной машины. Цель каждой программы состоит в том, чтобы опросить
или изменить базу данных. Машинный язык, который осуществляет VDBE,
специально предназначен, чтобы искать, прочитать и изменить базы данных. Каждая инструкция языка VDBE содержит код операции и три операнда,
маркированые как P1, P2 и P3. Операнд P1 является произвольным целым числом.
P2 это неотрицательное целое число. P3 задает указатель на структуру данных
или законченную нолем последовательность, возможно пустой указатель.
Только несколько инструкций VDBE используют все три операнда.
Много инструкций используют только один или два операнда.
Значительное количество инструкций не использует операндов вообще, но вместо
этого берет их данные и хранит их результаты в стеке выполнения.
Детали того, что делает каждая инструкция и какие операнды она использует,
описаны в отдельном документе документе. Программа VDBE начинает выполнение на инструкции 0 и продолжает
последовательные инструкции до того, как (1) столкнется с фатальной ошибкой,
(2) выполнит инструкцию Halt, (3) счетчик команд дойдет до
последней инструкции программы. Когда VDBE заканчивает выполнение, все
открытые курсоры базы данных закрываются, вся память освобождена и все
вынимается из стека. Таким образом, никогда нет никакого беспокойства об
утечках памяти или неосвобожденных ресурсах. Если вы сделали какое-либо программирование ассемблера или работали с
каким-либо видом абстрактной машины прежде, все эти детали должны быть
знакомы вам. Поэтому давайте начнем смотреть, как работает некоторый код. Мы начинаем с проблемы, которая может быть решена, используя программу
VDBE, которая является всего несколькими инструкциями.
Предположим, что у нас есть таблица SQL, которая была составлена как это: У нас есть таблица базы данных, названная "examp", у которой есть две
колонки данных, названных "one" и "two". Теперь предположите, что мы хотим
вставить единственную запись в эту таблицу: Мы видим программу VDBE, чтобы осуществить этот INSERT, используя
утилиту sqlite. Сначала запустите sqlite
на новой, пустой, базе данных, затем составьте таблицу.
Затем измените выходной формат sqlite к форме, которая разработана,
чтобы работать с дампами программы VDBE, командой ".explain".
Наконец, введите [INSERT], показанный выше, но предварите
специальным ключевым словом [EXPLAIN]. Оно заставит sqlite
печатать программу VDBE, а не выполнять ее. Мы имеем: Как вы видите выше, наш простой запрос вставки осуществляется в 12
инструкциях. Первые 3 и последние 2 инструкции это стандартный пролог и
эпилог, таким образом, реальная работа сделана в средних 7 инструкциях.
Нет никаких скачков, таким образом, программа выполняется сверху донизу
последовательно. Давайте теперь посмотрим на каждую инструкцию подробно.
Transaction начинает транзакцию.
Она заканчивается с кодом операции Commit или Rollback. P1 это индекс файла
базы данных, на котором начата транзакция.
Индекс 0 задает главный файл базы данных. Блокировка записи получена на
файле базы данных, когда транзакция начата. Никакой другой процесс не может
прочитать или написать файл, в то время как идет транзакция.
Начало транзакции также создает журнал обратной перемотки.
Транзакция должна быть начата прежде, чем любые изменения могут быть
внесены в базу данных. Инструкция VerifyCookie
проверяет cookie 0 (версия схемы базы данных), чтобы удостовериться, что это
равно P2 (значение, полученное, когда схема базы данных была в последний раз
прочитана). P1 это номер базы данных (0 для главной базы данных).
Это сделано, чтобы удостовериться, что схема базы данных не была изменена
другим потоком, в этом случае это должно быть перечитано. Вторая инструкция Transaction
начинает начинает транзакцию и журнал обратной перемотки для базы данных 1,
это база данных, используемая для временных таблиц. Инструкция Integer
выдвигает целочисленное значение P1 (0) в стек. Здесь 0 это номер
базы данных, чтобы использовать в следующих инструкциях OpenWrite.
Если P3 не NULL, тогда это строковое представление того же самого целого
числа. Впоследствии стек похож на это: OpenWrite
открывает новый курсор чтения-записи с дескриптором P1 (0 в этом случае) на
таблице "examp", страница корня которой в P2 (3 в этом файле базы данных).
Дескрипторы курсора могут быть любым неотрицательным целым числом.
Но VDBE ассигнует курсоры во множестве с размером множества, являющегося
на единицу больше, чем самый большой курсор.
Таким образом, чтобы сохранить память, лучше использовать дескрипторы,
начинающиеся с ноля и работающие вверх последовательно.
Здесь P3 ("examp") это название открываемой таблицы, но это не
использовано и произведено только, чтобы сделать код понятней.
Эта инструкция получает число базы данных, чтобы использовать (0, главная
база данных) от вершины стека, поэтому впоследствии стек пуст снова. NewRecno создает новый номер записи
integer для таблицы, на которую указывает курсор P1. Номер записи это
в настоящее время не используется в качестве ключа в таблице.
Новый номер записи помещен в стек. Впоследствии стек похож на это: Инструкция String помещает
свой операнд P3 в стек. Впоследствии стек похож на это: Инструкция Integer помещает
свой операнд P1 (99) в стек. Впоследствии стек похож на это: Инструкция MakeRecord
берет верхние P1 элементов из стека (2 в этом случае) и преобразовывает их в
двоичный формат, используемый для хранения записей
в файле базы данных. См. описание
формата файла.
Новая запись, созданная инструкцией MakeRecord, записана обратно в стек.
Впоследствии стек похож на это: Инструкция PutIntKey
использует верхние 2 записи стека, чтобы написать вход в таблицу, на которую
указывает курсор P1. Новый вход создается, если он еще не существует, или
данные для существующего входа переписаны.
Данные это верхний вход стека, ключ это следующий вход вниз.
Стек дважды читается этой инструкцией.
Поскольку операнд P2 равняется 1, количество изменений строки
увеличено, и rowid сохранен для последующего возвращения функцией
sqlite_last_insert_rowid(). Если P2 = 0, количество изменений строки не
изменяется. Эта инструкция это то, где вставка на самом деле происходит. Close закрывает
курсор, ранее открытый как P1 (0, единственный открытый курсор). Если P1 в
настоящее время не открыт, эта инструкция ничего не делает Commit
заставляет все модификации базы данных, которые были сделаны, начиная с
последней команды Transaction, на самом деле вступить в силу.
Никакие дополнительные модификации не позволены, пока другая транзакция
не начата. Инструкция Commit удаляет файл журнала и выпускает блокировку
записи на базе данных. Блокировка чтения продолжает проводиться, если есть
все еще открытые курсоры. Halt предписывает VDBE немедленно
завершиться. Все открытые курсоры, списки, сортировки и прочее
закрываются автоматически. P1 это код результата, возвращенный sqlite_exec().
Для нормальной остановки это должно быть SQLITE_OK (0). Для ошибок это может
быть некоторое другое значение.
Операнд P2 используется только, когда есть ошибка. Есть подразумеваемая
команда "Halt 0 0 0" в конце каждой программы, которую прилагает VDBE,
когда готовит программу. Если библиотека SQLite собрана без макроса препроцессора NDEBUG, то PRAGMA
vdbe_trace
предписывает VDBE прослеживать выполнение программ. Хотя эта особенность была
первоначально предназначена для тестирования и отладки, это может также быть
полезно в приобретении знаний о том, как VDBE работает. Используйте
"PRAGMA vdbe_trace=ON;" для включения и
"PRAGMA vdbe_trace=OFF" для выключения трассировки: С отслеживанием VDBE печатает каждую инструкцию до выполнения.
После того, как инструкция выполняется, несколько верхних
записей в стеке показаны. Показ стека опущен, если стек пуст. При показе стека большинство записей показывают с префиксом,
который говорит тип данных в том входе стека. Целые числа начинаются с
"i:". Значения с плавающей точкой начинаются с "r:".
("r" это "real-number"). Последовательности начинаются
"s:", "t:", "e:" или "z:".
Различие среди префиксов последовательности вызывается тем, как их
память ассигнуется. z: сохранены в памяти, полученной из
malloc(). t: статически ассигнуются. e: эфемерны. У всех других
последовательностей есть префикс s:.
Это не имеет никакого значения для вас, наблюдателя, но это жизненно важно
для VDBE, поскольку z:-последовательности должны быть переданы к
free(), когда они извлечены, чтобы избежать утечки памяти.
Обратите внимание на то, что только первые 10 знаков значения
последовательности показаны и что двоичные значения (такие как результат
инструкции MakeRecord) рассматривают как последовательности.
Единственным другим типом данных, который может быть сохранен в
стеке VDBE, является NULL, который является показом без префикса просто как
"NULL". Если целое число было помещено в стек как целое число и как
последовательность, ее префикс "si:".
В этом пункте необходимо понять основы того, как VDBE пишет базу данных.
Теперь давайте посмотрим на то, как это делает запросы. Мы будем использовать
следующий простой оператор SELECT в качестве нашего примера: Программа VDBE, произведенная для этого SQL-оператора, следующая: Прежде чем мы начнем смотреть на эту проблему, давайте кратко рассмотрим,
как запросы работают в SQLite так, чтобы мы знали, чего мы пытаемся
достигнуть. Для каждой строки в результате запроса SQLite вызовет функцию
обратного вызова со следующим прототипом: Библиотека SQLite поставляет VDBE с указателем на функцию обратного
вызова и указателем pUserData. Отзыв и пользовательские данные были
первоначально переданы как аргументы sqlite_exec() API.
Работа VDBE состоит в том, чтобы придумать значения для nColumn,
azData[] и azColumnName[]. nColumn это количество
колонок в результатах, конечно. azColumnName[] это массив
последовательностей, где каждая последовательность это
название одного из столбцов результата. azData[] это массив
последовательностей, содержащих фактические данные. Первые две инструкции в программе VDBE для нашего запроса касаются
подготовки значений для azColumn.
ColumnName говорят VDBE, что значения
надо заполнить для каждого элемента массива azColumnName[].
Каждый запрос начнется с одной инструкции ColumnName для каждой колонки в
результате, и будет соответствующая инструкция Column для каждой
колонки позже в запросе. Инструкции 2 и 3 открывают курсор чтения
на таблице базы данных, которая должна быть запрошена.
Это работает аналогично инструкции OpenWrite в примере INSERT за исключением
того, что курсор открыт для чтения на этот раз вместо записи.
Инструкция 4 проверяет схему базы данных как в примере INSERT. Rewind инициализирует цикл,
который проходит по таблице "examp".
Это перематывает курсор P1 к первому входу в таблице. Это требуется
инструкциями Column и Next, которые используют курсор, чтобы прпойти через
таблицу. Если таблица пуста, то перейдите к P2 (10),
который является инструкцией после цикла. Если таблица не пуста, перейдите к
следующим инструкциям в 6, которые являются началом тела цикла. Инструкции 6-8 формируют тело цикла, которое выполнится однажды для каждой
записи в файле базы данных. Инструкции Column
по адресам 6 и 7 каждая берет P2-ю колонку от P1-го курсора и выдвигает
ее в стек. В этом примере первая инструкция Column
помещает в стек значение столбца "one", вторая Column помещает в стек
значение столбца "two". Callback
по адресу 8 вызывает функцию callback(). Операнд P1 Callback
становится значением nColumn. Callback берет значения
P1 из стека и использует их, чтобы заполнить массив azData[]. Инструкция по адресу 9 реализует ветвящуюся часть цикла.
Вместе с Rewind по адресу 5 это формирует логику цикла.
Это ключевое понятие, на которое необходимо обратить пристальное внимание.
Next передвигает курсор P1 к следующей
записи. Если прогресс курсора был успешен, то выполняется переход
немедленно к P2 (адрес 6, начало тела цикла).
Если курсор был в конце, то перейдите к следующим инструкциям,
которые заканчивают цикл. Close в конце программы закрывает курсор, который указывает на таблицу
"examp". Необязательно вызывать Close здесь, так как все курсоры будут
автоматически закрыты VDBE, когда программа остановится. Но нам была нужна
инструкция для Rewind, чтобы перейти к ней, таким образом, мы могли бы также
идти вперед и иметь ту инструкцию, которая делает что-то полезное.
Halt заканчивает программу VDBE. Обратите внимание на то, что программа для этого запроса SELECT
не содержала инструкции и Transaction и Commit из примера INSERT.
Поскольку SELECT это операция чтения, которая не изменяет базу данных, она
не требует транзакции. Ключевые пункты предыдущего примера были использованием инструкции
Callback, чтобы вызвать функцию обратного вызова, и Next, чтобы осуществить
цикл по всем записям файла базы данных. Этот пример пытается показать
те идеи, демонстрируя немного более сложный запрос, который включает больше
колонок вывода, некоторые из которых вычисляют значения,
и оператор Where, который ограничивает, какие записи на самом деле
добираются до функции обратного вызова. Рассмотрите этот запрос: Этот запрос, возможно, немного избыточен,
но он действительно служит, чтобы проиллюстрировать наши тезисы.
У результата будет три колонки с именами "one", "two" и "both".
Первые две колонки это прямые копии этих двух колонок в таблице, а третий
столбец результата это последовательность, сформированная, связывая первые и
вторые колонки таблицы. Наконец, оператор Where говорит, что мы будем
брать только строки, где столбец "one" начинается с "H".
Вот на что программа VDBE похожа для этого запроса: За исключением оператора Where, структура программы для этого примера
очень похожа на предшествующий пример, только с дополнительным столбцом.
Есть теперь 3 колонки, вместо 2 как прежде, и есть три инструкции ColumnName.
Курсор открыт, используя инструкцию OpenRead, точно так же, как в
предшествующем примере. Инструкция Rewind по адресу 6 и Next по адресу 17
формируют цикл по всем записям таблицы. Close в конце, чтобы дать инструкции
Rewind что-то, чтобы перейти, когда это сделано. Все это находится точно так
же, как в первой демонстрации запроса. Callback в этом примере должна произвести данные для трех столбцов
результата вместо двух, но иначе, как в первом запросе.
Когда инструкция Callback вызвана, крайний левый столбец результата должен
быть самым низким в стеке, и самый правый столбец результата должен быть
вершиной стека. Мы видим, что стек так настраивается
по адресам 11-15. Инструкции Column в 11 и 12 помещают значения
первых двух колонок в результат. Две инструкции Column по адресам 13 и 14
должны были вычислить третий столбец результата, и инструкция Concat в 15
присоединяется к ним, чтобы добавить запись в стек. Единственной вещью, которая является действительно новой в
текущем примере, является оператор Where, который осуществляется инструкциями
по адресам 7-10. Инструкции по адресу 7 и 8 помещают в стек значение
колонки "one" таблицы и литеральную строку "H%".
Инструкция Function по адресу 9
получает эти два значения из стека и помещает результат функции LIKE() в
стек. Инструкция IfNot получает верхнее
значение стека и вызывает непосредственный переход к инструкции Next,
если верхнее значение false (НЕ как литеральная строка "H%").
Это эффективно пропускает отзыв, который является целым пунктом оператора
Where. Если результат сравнения верен, переход не выполнен, и контроль
переходит к инструкции Callback ниже. Заметьте, как оператор LIKE осуществляется.
Это определенная пользователями функция в SQLite, таким образом, адрес
функции определяется в P3. Операнд P1 является количеством аргументов
функции, чтобы взять от стека. В этом случае функция LIKE()
берет 2 аргумента. Аргументы взяты от стека в обратном порядке (справа
налево), таким образом, образец, чтобы соответствовать является верхним
элементом стека, следующий элемент это данные, чтобы выдержать сравнение.
Возвращаемое значение помещено в стек. Первые два примера запроса иллюстрируют своего рода шаблон, которому будет
следовать каждая программа SELECT. В основном мы имеем: Этот шаблон будет расширен значительно, поскольку мы рассматриваем
дополнительные осложнения, такие как соединения, комплексные select,
используя индексы, чтобы ускорить поиск, сортировку и агрегатные функции с и
без пунктов HAVING и GROUP BY. Но те же самые основные
идеи продолжат применяться. Запросы UPDATE и DELETE закодированы, используя шаблон, который очень
похож на шаблон оператора SELECT. Основное различие, конечно, то, что
действие в конце должно изменить базу данных, а не вызвать функцию обратного
вызова. Поскольку это изменяет базу данных, это будет также использовать
транзакции. Давайте начнем, смотря на DELETE: Этот DELETE удаляет каждую запись из таблицы "examp", где колонка "two"
меньше 50. Код, чтобы сделать это: Вот то, что должна сделать программа. Сначала это должно определить
местонахождение всех записей в "examp", которые должны быть удалены.
Это сделано, используя цикл, очень похожий на тот, который был
в примерах SELECT выше. Как только все записи были найдены,
мы можем возвратиться и удалить их один за другим. Обратите внимание на то,
что мы не можем удалить каждую запись, как только мы находим ее.
Мы должны определить местонахождение всех записей сначала, затем
удалите их. Это вызвано тем, что бэкенд базы данных SQLite мог бы изменить
порядок просмотра после операции удаления. Если изменение порядка просмотра
было посреди просмотра, некоторые записи можно было бы посетить несколько
раз, а другие нельзя было бы посетить вообще. Таким образом, внедрение DELETE находится действительно в двух циклах.
Первый цикл (инструкции 5-11) определяет местонахождение
записей, которые должны быть удалены, и сохраняет их ключи во временный
список, второй цикл (инструкции 16-19) использует ключевой список, чтобы
удалить записи одну за другой. Инструкции с 0 по 4 как в примере INSERT. Они начинают транзакции для
главных и временных баз данных, проверяют схему базы данных для главной базы
данных и открывают читающий курсор на таблице "examp".
Заметьте, что курсор открыт для чтения, не записи.
На этой стадии программы мы только собираемся заниматься просмотром таблицы,
не изменяя ее. Мы вновь откроем таблицу для записи позже в инструкции 15. Как в примере SELECT, инструкция Rewind
перематывает курсор к началу таблицы, подготавливая его для
использования в теле цикла. WHERE осуществляется инструкциями 6-8. Работа where это
пропустить ListWrite, если WHERE = false. С этой целью это переходит вперед к
Next, если столбец "two" (извлеченный инструкцией Column)
больше или равен 50. Как прежде, инструкция Column использует курсор P1 и помещает
запись данных в колонке P2 (1, колонка "two") в стек.
Инструкция Integer выдвигает значение 50 на вершину стека.
После этих двух инструкций стек: Оператор Ge
сравнивает два верхние элемента в стеке, получает их,
затем ветвится на основе результата сравнения. Если второй элемент >=
верхнему, выполняется переход к P2 (Next в конце цикла).
Поскольку P1 верен, если любой операнд NULL (и таким образом результат NULL),
происходит переход. Если мы не переходим, то
просто продвигаемся к следующей инструкции. Recno
выдвигает в стек целое число, которое составляет первые 4 байта ключа
текущей записи в последовательном просмотре таблицы, на которую указывает
курсор P1. Инструкция ListWrite пишет
integer на вершине стека в список временного хранения и получает вершину.
Это важная работа этого цикла, чтобы сохранить ключи записей, которые будут
удалены во втором цикле. После этой инструкции ListWrite стек пуст снова. Next увеличивает курсор, чтобы указать на следующий элемент в таблице, на
которую указывает курсор P0, если это было успешное ветвление к P2 (6,
начало тела цикла). Close закрывает курсор P1. Это не затрагивает список
временного хранения, потому что это не связано с курсором P1,
это глобальный рабочий список (который может быть сохранен ListPush). ListRewind перематывает список
временного хранения к началу. Это готовит его к использованию
во втором цикле. Как в примере INSERT, мы выдвигаем базу данных номер P1 (0, главную базу
данных) в стек и используем OpenWrite, чтобы открыть курсор P1 на таблице P2
(базовая страница 3, "examp") для модификации. Этот цикл делает фактическое удаление. Это организовано по-другому по
сравнению с тем в примере UPDATE. Инструкция ListRead играет роль, которую
Next выполняла в цикле INSERT, но потому что это переходит к P2 на неудаче, и
Next выполняется при успехе, мы помещаем его в начале цикла вместо конца.
Это означает, что мы должны поместить Goto в конце цикла, чтобы перейти
назад к тесту цикла вначале. Таким образом, у этого цикла есть форма C
while(){...} в то время, как цикл в INSERT имеет форму do{...} while().
Delete исполняет роль, которую функция обратного вызова
сделала в предыдущих примерах. ListRead
читает элемент из списка временного хранения и выдвигает его в стек.
Если это было успешно, это продолжается к следующей инструкции.
Если это терпит неудачу, потому что список пуст, он ветвится к P2, который
является инструкцией сразу после цикла. Впоследствии стек похож на: Заметьте подобие между ListRead и Next.
Обе операции работают согласно этому правилу: Одно различие между Next и ListRead это их идея "вещи".
"Вещи" для Next это записи в файле базы данных. "Вещи"
для ListRead это ключи целого числа в списке. Другое различие это переход,
если нет никакой следующей "вещи". В этом случае Next
проваливается, а ListRead переходит.
Позже мы будем видеть другие инструкции (NextIdx и SortNext), которые
управляют использованием того же самого принципа. NotExists
получает верхний элемент стека и использует его в качестве ключа целого
числа. Если запись с тем ключом не существует в таблице P1, то переходит к
P2. Если запись действительно существует, то проваливается к следующей
инструкции. В этом случае P2 переходит к Goto в конце цикла, который
переходит назад к ListRead в начале.
Это, возможно, было закодировано, чтобы P2 был 16, ListRead
в начале цикла, но анализатор SQLite, который произвел этот код,
не сделал эту оптимизацию. Delete делает работу этого цикла:
это получает целое число ключа из стека (помещенное там предыдущим
ListRead) и удаляет запись курсора P1, у которой есть тот ключ.
Поскольку P2 = true, счетчик изменения строки увеличен. Goto
переходит назад к началу цикла. Это конец цикла. Этот блок инструкции очищает программу VDBE. Три из этих инструкций
действительно не требуются, но произведены анализатором SQLite от его кодовых
шаблонов, которые разработаны, чтобы обращаться с более сложными случаями. ListReset
освобождает список временного хранения. Этот список освобожден автоматически,
когда программа VDBE заканчивается, таким образом, это не необходимо в этом
случае. Close закрывает курсор P1. Снова, это сделано VDBE, когда он
закончен, управляя этой программой. Commit заканчивает текущую транзакцию
успешно и все изменения, которые произошли в этой тарназакции,
сохранены в базе данных. Заключительный Halt также не нужен, так как
добавляется к каждой программе VDBE, когда это готово к работе. UPDATE работают очень похоже на DELETE
за исключением того, что вместо того, чтобы удалить запись они заменяют
ее новой. Рассмотрите этот пример: Вместо того, чтобы удалить записи, где колонка "two"
меньше 50, это просто помещает колонку "one" в круглые скобки, за которыми
следует программа VDBE, чтобы осуществить это: Эта программа по существу то же самое, что и программа DELETE, за
исключением того, что тело второго цикла заменяют последовательностью
инструкций (по адресам 17-26), которые обновляют, а не удаляют запись.
Большая часть этой последовательности инструкции должна уже быть знакома вам,
но есть несколько незначительных поворотов, таким образом, мы пробежимся
через нее кратко. Также обратите внимание на то, что порядок
некоторых инструкций прежде и после цикла 2 изменился. Это просто способ,
которым анализатор SQLite принял решение произвести код,
используя различный шаблон. Поскольку мы входим в интерьер второго цикла
(в инструкции 17), стек содержит единственное целое число, которое
является ключом записи, которую мы хотим изменить. Мы собираемся использовать
этот ключ дважды: чтобы получить старое значение и во второй раз, чтобы
написать пересмотренную запись в ответ. Таким образом, первая инструкция
это Dup, чтобы сделать дубликат ключа на вершине стека.
Инструкция Dup дублирует любой элемент стека, не только вершину.
Вы определяете, который элемент скопировать,
используя операнд P1. Когда P1 = 0, вершина стека дублирована.
Когда P1 равняется 1, дублируется следующий элемент вниз. И т.д. После получения дубликата ключа следующая инструкция, NotExists, получает
из стека и использует значение в качестве ключа, чтобы проверить
существование записи в файле базы данных. Если нет никакой записи для этого
ключа, переходит назад к ListRead, чтобы получить другой ключ. Инструкции 19-25 строят новую запись базы данных, которая
будет использоваться, чтобы заменить существующую. Это тот же самый вид кода,
который мы видели в описании INSERT. После того, как инструкция 25
выполняется, стек похож на это: Инструкция PutIntKey (также описана во время дискуссии про INSERT)
пишет вход в файл базы данных, данные которого это вершина стека, а
ключ следующий в стеке, и затем получает данные из стека дважды.
Инструкция PutIntKey перепишет данные существующей записи с тем же самым
ключом, который является тем, что мы хотим здесь. Переписывание не было
проблемой с INSERT, потому что с INSERT ключ был произведен инструкцией
NewRecno, которая, как гарантируют, обеспечит ключ, который
не использовался прежде. Применение CREATE или DROP, чтобы создать или удалить таблицу
или индекс действительно то же самое, как выполнение INSERT или DELETE для
специальной таблицы "sqlite_master", по крайней мере с точки зрения VDBE.
Таблица sqlite_master это спецтаблица, которая автоматически составлена для
каждой базы данных SQLite. Это похоже на: У каждой таблицы (кроме самой "sqlite_master")
и каждого названного индекса в базе данных SQLite есть вход в sqlite_master.
Можно запросить эту таблицу, используя оператор SELECT точно так же, как
любую другую. Но нельзя непосредственно ее изменить, используя UPDATE, INSERT
или DELETE. Изменения sqlite_master должны произойти, используя команды
CREATE и DROP, потому что SQLite также должен обновить некоторые свои
внутренние структуры данных, когда таблицы и индексы
добавлены или удалены. Но с точки зрения VDBE, CREATE работает в значительной степени как INSERT,
а DROP как DELETE. Когда библиотека SQLite открывается к существующей базе
данных, первой вещью, которую она делает, является SELECT, чтобы прочитать
столбец "sql" от всех записей sqlite_master. Столбец "sql" содержит полный
код на SQL запроса CREATE, который первоначально произвел индекс или
таблицу. Этот текст возвращается в анализатор SQLite и используется, чтобы
восстановить внутренние структуры данных, описывающие индекс или таблицу. В запросах в качестве примера выше, каждая строка запрашиваемой таблицы
должна быть загружена с диска и исследована, даже если только небольшой
процент строк нужен в результате. Это может занять много времени на большой
таблице. Чтобы ускорить такие вещи, SQLite может использовать индекс. Файл SQLite связывает ключ с некоторыми данными. Для таблицы SQLite
настраивается файл базы данных так, чтобы ключ был целым числом, и данные
это информация для одной строки. Индексы в SQLite полностью изменяют эту
договоренность. Ключ индекса это хранимая информация, а данные индекса
являются целым числом. Чтобы получить доступ к строке таблицы, у которой есть
некоторое конкретное содержание, мы сначала ищем содержание в таблице
индекса, чтобы найти индекс целого числа, тогда мы используем то целое число,
чтобы искать полную запись в таблице. Обратите внимание на то, что SQLite использует b-деревья, которые являются
сортированной структурой данных, таким образом, индексы могут использоваться,
когда параметр Where оператора SELECT содержит тесты на равенство или
неравенство. Следующие и подобные запросы
могут использовать индекс, если это доступно: Если там будет существовать индекс, который отображает столбец "two"
таблицы "examp" в целые числа, то SQLite будет использовать тот индекс,
чтобы найти ключи целого числа всех строк в examp, у которых есть значение 50
столбца two, или все строки меньше 50. Но следующие запросы
не могут использовать индекс: Обратите внимание на то, что анализатор SQLite не всегда будет
производить код, чтобы использовать индекс, даже если будет возможно сделать
так. Следующие запросы не будут в настоящее время использовать индекс: Чтобы понять лучше, как индексы работают, посмотрим на то, как они
создаются. Давайте идти вперед и поместим индекс в колонку
two таблицы examp: Код VDBE, произведенный вышеупомянутым запросом, похож на следующее: Помните, что у каждой таблицы (кроме sqlite_master)
и каждого названного индекса есть вход в таблице sqlite_master.
Так как мы создаем новый индекс, мы должны добавить новый вход в
sqlite_master. Это обработано инструкциями 3-15. Добавление входа в
sqlite_master работает точно так же, как любой другой оператор INSERT, таким
образом, мы не будем больше говорить об этом здесь. В этом примере мы хотим
сосредоточиться на заполнении нового индекса действительными данными, которое
происходит на инструкциях 16-23. Первая вещь, которая происходит, состоит в том, что мы открываем
таблицу, внесенную в индекс, для чтения. Чтобы построить индекс для таблицы,
мы должны знать то, что находится в таблице. Индекс был уже открыт для
написания, используя курсор 0, инструкциями 3 и 4. Инструкции 18-23 осуществляют цикл по каждой строке внесенной в указатель
таблицы. Для каждой строки таблицы мы сначала извлекаем ключ целого числа для
этой строки, применяя Recno в инструкции 19, затем получаем значение столбца
"two" через Column в инструкции 20.
MakeIdxKey в инструкции 21 конвертирует данные из столбца "two"
(которые находятся на вершине стека) в действительный ключ индекса.
Для индекса на отдельном столбце это в основном не делает ничего.
Но если бы операнд P1 в MakeIdxKey был больше одного, многократные входы
были бы получены из стека и преобразовывались бы в единственный ключ индекса.
Инструкция IdxPut по адресу 22 это
то, что на самом деле создает элемент индекса. IdxPut получает два элемента
от стека. Вершина стека используется в качестве ключа, чтобы принести вход от
таблицы индекса. Тогда целое число, которое было вторым в
стеке, добавляется к набору целых чисел для того индекса, и новая запись
написана в ответ файлу базы данных. Обратите внимание на то, что тот же самый
элемент индекса может сохранить многократные целые числа, если есть две или
больше записи таблицы с тем же самым значением для двух колонок. Теперь давайте посмотрим на то, как этот индекс будет использоваться.
Рассмотрите следующий запрос: SQLite производит следующий код VDBE: SELECT начинается знакомым способом. Сначала имена столбцов
инициализируются, и запрашиваемая таблица открыта.
Различия начинаются с инструкций 5 и 6, где индексный файл также открыт.
Инструкции 7 и 8 делают ключ со значением 50.
MemStore по адресу 9
хранит ключ индекса в ячейке памяти VDBE 0. Память VDBE используется, чтобы
избежать необходимости получения данных из глубины стека, что
может быть сделано, но делает программу тяжелее. Следующая инструкция
MoveTo по адресу 10 получает ключ из
стека и перемещает курсор индекса в первую строку индекса с тем ключом.
Это инициализирует курсор для использования в следующем цикле. Инструкции 11-18 осуществляют проход по всем записям
индекса с ключом, который был принесен инструкцией 8. Все записи индекса
с этим ключом будут смежными в таблице
индекса, таким образом, мы будем идти через них и приносим соответствующий
ключ таблицы от индекса. Этот ключ таблицы используется, чтобы переместить
курсор в ту строку. Остальная часть цикла совпадает с циклом
для неиндексируемого запроса SELECT. Цикл начинается с инструкции MemLoad
по адресу 11, которая выдвигает копию ключа индекса в стек. Инструкция
IdxGT по адресу 12
сравнивает ключ с ключом в текущей записи индекса, на которую указывает
курсор P1. Если ключ индекса в текущей позиции курсора больше, чем индекс,
который мы ищем, то выходим из цикла. IdxRecno по адресу 13 выдвигает в стек
номер записи таблицы от индекса. Следующий MoveTo получает его и перемещает
курсор в ту строку. Следующие 3 инструкции выбирают данные о колонке как в
случае без индекса. Column получает данные о колонке, и функция обратного
вызова вызвана. Next передвигает курсор индекса, не курсор таблицы, к
следующей строке и затем переходит назад к началу цикла, если есть какие-либо
оставленные записи индекса. Так как индекс используется, чтобы искать значения в таблице,
важно что индекс и таблица сохранены последовательно.
Теперь, когда есть индекс на таблице examp,
мы должны будем обновить тот индекс каждый раз, когда данные вставлены,
удалены или изменены в таблице examp.
Помните первый пример выше, где мы смогли вставить новую строку в "examp",
используя 12 инструкций VDBE. Теперь, когда эта таблица
внесена в указатель, 19 инструкций требуются. SQL-оператор: И произведенный код похож на это: В этом пункте необходимо понять VDBE достаточно хорошо, чтобы выяснить
самостоятельно, как вышеупомянутая программа работает. Таким образом, мы не
обсудим ее далее в этом тексте. В join две или больше таблиц объединены, чтобы произвести единственный
результат. Таблица результата состоит из каждой возможной комбинации строк
таблиц, к которым присоединяются. Самый легкий и самый естественный способ
осуществить это с вложенными циклами. Вспомните шаблон запроса выше, где был единственный цикл, который перерыл
каждую запись таблицы. В соединении у нас есть в основном то же самое за
исключением того, что есть вложенные циклы. Например, чтобы присоединиться к
двум таблицам, шаблон мог бы выглядеть примерно так: Этот шаблон будет работать, но это, вероятно, будет медленно, так как мы
теперь имеем дело с O(N2) циклов. Но это часто решает, что
оператор Where может быть включен в условия и что одно или больше тех условий
вовлечет только колонки в первой таблице. Когда это происходит, мы можем
учесть часть теста оператора Where из внутреннего цикла и получить большую
эффективность. Таким образом, лучший шаблон был бы чем-то вроде этого: Дополнительное ускорение может произойти, если индекс может
использоваться, чтобы ускорить поиск. SQLite всегда строит циклы в том же самом порядке, в каком таблицы
появляются в пункте FROM оператора SELECT. Крайняя левая таблица
становится внешним циклом, а самая правая становится внутренним циклом.
Возможно, в теории, переупорядочить циклы при некоторых обстоятельствах,
чтобы ускорить оценку соединения. Но SQLite не делает
попытку этой оптимизации. Вы видите, как SQLite строит вложенные циклы в следующем примере: Внешний цикл по таблице examp выполнен инструкциями 7-23.
Внутренний цикл это инструкции 13-22. Заметьте что термин
"two<50" в WHERE вовлекает только колонки из первой таблицы и может быть
решен вне внутреннего цикла. SQLite проверяет "two<50" в инструкциях
10-12. Проверка "four==two" реализована инструкциями 14-16
во внутреннем цикле. SQLite не накладывает произвольных ограничений на таблицы
в соединении. Это также позволяет соединиться с самой собой. По историческим причинам и для эффективности вся сортировка в настоящее
время делается в памяти. SQLite реализует ORDER BY, используя специальный набор инструкций, чтобы
управлять объектом, названным сортировщиком. В самом внутреннем цикле
запроса, где обычно была бы инструкция Callback,
вместо этого отчет построен, который содержит параметры отзыва и ключ.
Этот отчет добавляется к сортировщику (в связанном списке).
После окончания цикла запроса сортирован список отчетов, и этот список
проходится. Для каждого отчета в списке вызван отзыв.
Наконец, сортировщик закрывается, и память освобождена. Мы видим процесс в действии в следующем запросе: Есть только один объект сортировщика, таким образом, нет никаких
инструкций, чтобы открыть или закрыть его. Это открыто автоматически при
необходимости и закрывается, когда программа VDBE останавливается. Цикл запроса строится из инструкций 5-13. Инструкции 6-8 строят отчет,
который содержит значения azData[] для единственного вызова отзыва.
Ключ сортировки произведен инструкциями 9-11. Инструкция 12 комбинирует
запись с ключом сортировки в однократный вход
и помещает его в список сортировки.
Параметр P3 инструкции 11 особенно интересен. Ключ сортировки сформирован,
предварительно ожидая один символ от P3 для каждой последовательности и
связывая все последовательности. Функция сравнения посмотрит на этот
символ, чтобы определить, поднимается ли порядок сортировки или спускается, и
сортировать как строку или число. В этом примере первая колонка должна быть
сортирована как последовательность в порядке убывания, таким образом, ее
префикс "D", вторая колонка должна сортироваться численно в порядке
возрастания, таким образом, ее префикс "+".
Возрастание на сортировку последовательности использует "A",
спуск по числовому использованию сортировки "-". После окончания цикла запроса запрашиваемая таблица
закрывается в инструкции 14. Это сделано рано, чтобы позволить другим
процессам или потокам получать доступ к той таблице
при желании. Список отчетов, который был создан в цикле запроса, сортирован
инструкцией в 15. Инструкции 16-18 проходят через список записей
(который находится теперь в сортированном порядке)
и вызывают отзыв однажды для каждого отчета. Наконец, сортировщик закрывается
в инструкции 19. Чтобы вычислить агрегатные функции, VDBE осуществляет специальную
структуру данных и инструкции для управления той структурой данных.
Структура данных это неупорядоченный набор букетов, где у каждого букета
есть ключ и одна или более ячеек памяти. В цикле запроса
пункт GROUP BY используется, чтобы построить ключ, и букет
с тем ключом подчеркнут. Новый букет создается с ключом, если он ранее
не существовал. Как только букет находится в центре, ячейки памяти
используются, чтобы накопить значения различных агрегатных функций.
После того, как цикл запроса заканчивается, каждый букет
посещают однажды, чтобы произвести единственную строку результатов. Пример поможет разъяснить это понятие. Рассмотрите следующий запрос: Код VDBE, произведенный для этого запроса: Первая важная инструкция это
AggReset по адресу 2.
AggReset инициализирует набор, чтобы быть пустым множеством и определяет
количество мест памяти, доступных в каждом букете как P2.
В этом примере каждый букет будет держать 3 места памяти. Неочевидно, но если
вы пристально смотрите на остальную часть программы, можно выяснить то, для
чего предназначается каждое из этих мест. Цикл запроса осуществляется инструкциями 8-22. Совокупный ключ,
определенный пунктом GROUP BY, вычисляется инструкциями 9 и 10. Инструкция 11
берет соответствующий букет, чтобы войти в центр.
Если букет с данным ключом не существует, новый создается, и контроль
проваливается к инструкциям 12 и 13, которые его инициализируют.
Если оно уже существует, то сделан переход к инструкции 14.
Значения агрегатных функций обновляются инструкциями между 11 и 21.
Инструкции 14-18 обновляют ячейку памяти 1, чтобы считать следующее
значение "min(three+four)". Тогда сумма столбца "four"
обновляется инструкциями 19-21. После того, как закончен цикл запроса, таблица "examp2"
закрывается в инструкции 23 так, чтобы ее блокировка снялась, и это может
использоваться другими потоками или процессами. Следующий шаг должен
образовать циклы по всем совокупным букетам и произвести одну строку
результата для каждого букета. Это сделано циклом в инструкциях 24-30.
Инструкция AggNext в 24 помещает следующий букет в центр или переходит
до конца цикла, если все букеты уже были исследованы. 3 колонки результата
принесены от накопителя по порядку в инструкциях 25-27.
Наконец, отзыв вызван в инструкции 29. Таким образом, тогда любой запрос с агрегатными функциями осуществляется
двумя циклами. Первый сканирует входную таблицу и вычисляет совокупную
информацию в букеты и второй просмотраивает все букеты, чтобы
вычислить конечный результат. Понимание, что агрегатный запрос это действительно два последовательных
цикла, делает намного легче понять различие между оператором Where и пунктом
HAVING в SQL-запросе. Оператор Where это ограничение на первый цикл,
пункт HAVING это ограничение на второй цикл.
Вы видите это, добавляя WHERE и пункт HAVING к нашему
запросу в качестве примера: Код, произведенный в этом последнем примере, совпадает с предыдущим
за исключением добавления двух условных переходов, используемых, чтобы
осуществить дополнительный WHERE и пункты HAVING. Оператор Where
осуществляется инструкциями 9-11 в цикле запроса.
Пункт HAVING осуществляется инструкцией 28-30 в цикле вывода. Самое имя "Structured Query Language" говорит нам, что SQL должен
поддерживать вложенные запросы. И на самом деле два различных вида
вложения поддерживаются. Любой оператор SELECT, который возвращает
единственную строку, результат отдельного столбца, может использоваться в
качестве термина в выражении другого оператора SELECT.
И оператор SELECT, который возвращает отдельный столбец, многострочный
результат, может использоваться в качестве правого операнда IN и NOT IN.
Мы начнем эту секцию с примера первого вида вложения, где единственная
строка, отдельный столбец SELECT используется в качестве термина в выражении
другого SELECT. Вот наш пример: SQLite имеет дело с этим, сначала выполняя внутренний SELECT
(который для examp2) и сохраняя его результат в частном элементе памяти.
Затем SQLite подставляет значение этого частного элемента памяти во
внутренний SELECT, когда это оценивает внешний SELECT.
Код похож на это: Частный элемент памяти инициализируется к NULL первыми двумя инструкциями.
Инструкции 2-13 осуществляют внутренний оператор SELECT для таблицы examp2.
Заметьте, что вместо того, чтобы послать результат в отзыв или сохранить
результат в сортировщике, результат вопроса выдвинут в элемент памяти
инструкцией 10, и цикл оставлен переходом в инструкции 11.
Переход в инструкции 11 остаточный и никогда не выполняется. Внешний SELECT осуществляется инструкциями 14-25. В частности оператор
Where, который содержит вложенный select, осуществляется инструкциями 19-21.
Вы видите, что результат внутреннего select загружается в стек инструкцией 20
и используется условным переходом в 21. Когда результат под-select это скаляр, единственный частный элемент памяти
может использоваться, как показано в предыдущем примере.
Но когда результат это вектор, такой как тогда, когда под-select
является правый операнд IN или NOT IN,
другой подход необходим. В этом случае результат сохранен в переходной
таблице и ее содержание проверено, используя операторы NotFound или Found.
Рассмотрите этот пример: Код, произведенный, чтобы осуществить этот последний запрос: Переходная таблица, в которой сохранены результаты внутреннего SELECT,
составлена инструкцией OpenTemp
по адресу 0. Этот код операции используется для таблиц, которые существуют
только на время единственного SQL-оператора. Переходный курсор всегда открыт
как read/write, даже если главная база данных только для чтения. Переходная
таблица удалена автоматически, когда курсор закрывается. Значение P2 = 1
значит, что курсор указывает на индекс BTree, который не имеет никаких
данных, но может иметь произвольный ключ. Внутренний оператор SELECT осуществляется инструкциями 1-10. Весь этот код
создает вход во временной таблице для каждой строки таблицы examp2
со значением не-NULL для столбца "three". Ключ для каждого входа временной
таблицы это столбец "three" в examp2, данные это
пустая строка, так как это никогда не используется. Внешний SELECT осуществляется инструкциями 11-25.
В частности, оператор Where, содержащий оператор IN,
осуществляется инструкциями в 16, 17 и 20. Инструкция 16 помещает значение
столбца "two" для текущей строки в стек и инструкция 17 проверяет, чтобы
видеть, что это не-NULL. Если это успешно, выполнение переходит на адрес 20,
где это проверяет, чтобы видеть, соответствует ли вершина стека какому-либо
ключу во временной таблице. Остальная часть кода совпадает с тем,
что показали прежде. SQLite также позволяет двум или больше операторам SELECT быть
присоединенными как равные, использующие операторы UNION, UNION ALL,
INTERSECT и EXCEPT. Эти составные select осуществляются, используя переходные
таблицы. Внедрение немного отличается для каждого оператора, но основные идеи
те же. Для примера мы будем использовать оператор EXCEPT. Результат этого последнего примера должен быть каждым уникальным
значением столбца "two" таблицы examp, кроме любого значения, которое
находится в столбце "four" таблицы examp2.
Код, чтобы осуществить этот запрос: Переходная таблица, в которой строится результат, составлена инструкцией
0. Три цикла следуют. Цикл по инструкциям 5-10 выполняет первый оператор
SELECT. Второй SELECT реализован инструкциями 14-19. Наконец цикл по
инструкциям 22-25 читает переходную таблицу и вызывает отзыв однажды для
каждой строки в результате. Инструкция 1 имеет особое значение в этом примере.
Обычно инструкция Column извлекает значение колонки из большей записи
в данных входа файла SQLite. Инструкция 1 ставит флаг на переходной таблице
так, чтобы Column вместо этого рассматривала ключ входа файла SQLite, как
будто это были данные и информация о колонке извлечена из ключа. Вот то, что собирается произойти: первый оператор SELECT построит
строки результата и сохранит каждую как ключ входа в переходной таблице.
Данные для каждого входа в переходной таблице
никогда не используются, таким образом, мы заполняем его пустой строкой.
Второй оператор SELECT также строит строки, но строки, построенные вторым
SELECT, удалены из переходной таблицы. Именно поэтому мы хотим, чтобы они
были сохранены в ключе файла SQLite вместо данных:
таким образом, они могут быть легко найдены и удалены. Давайте посмотрим более тесно на то, что происходит здесь.
Первый SELECT осуществляется циклом в инструкциях 5-10.
Инструкция 5 инициализирует цикл, перематывая курсор. Инструкция 6 извлекает
значение столбца "two" из "examp", инструкция 7 конвертирует это в строку.
Инструкция 8 помещает пустую строку в стек. Наконец, инструкция 9 пишет
строку во временную таблицу. Но помните, код операции PutStrKey использует
вершину стека как данные записи и следующее в стеке как ключ.
Для оператора INSERT строка, произведенная кодом операции MakeRecord,
является данными, а ключ записи это
целое число, созданное кодом операции NewRecno.
Но здесь роли полностью изменены, и строка, созданная MakeRecord, это
ключ записи, а данные это просто пустая строка. Второй SELECT осуществляется инструкциями 14-19.
Инструкция 14 инициализирует цикл, перематывая курсор.
Новая строка результата создается из столбца "four" таблицы "examp2"
инструкциями 15 и 16. Но вместо того, чтобы использовать PutStrKey, чтобы
написать эту новую строку во временную таблицу, мы вместо этого вызываем
Delete, чтобы удалить строку из временной таблицы, если это существует. Результат составного select посылает в установленный порядок отзыва
циклом по адресам 22-25. Нет ничего нового или поразительно в этом,
за исключением того, что инструкция Column по адресу 23 будет извлекать
колонку из ключа записи, а не из данных. Эта статья рассмотрела все главные методы, используемые VDBE SQLITE, чтобы
осуществить SQL-операторы. То, что не показали, это
то, что большинство этих методов может использоваться в комбинации, чтобы
произвести код для сложного запроса. Например, мы показали, как сортировка
достигается на простом запросе, и мы показали, как осуществить составной
запрос. Но мы не дали пример сортировки в составном запросе.
Это вызвано тем, что сортировка составного запроса не вводит новых понятий:
это просто объединяет две предыдущих идеи (сортировка и сложение)
в той же самой программе VDBE. Для получения дополнительной информации о том, как выглядят библиотечные
функции SQLite, стоит посмотреть на исходный код SQLite непосредственно.
Если вы понимаете материал в этой статье, вы не должны испытывать много
затруднений в следующем изучении. Серьезное изучение внутренностей
SQLite, вероятно, также потребует сделать тщательное исследование кодов
операции VDBE, как описано здесь.
Большая часть документации кодов операции извлечена из комментариев в
исходном коде, используя скрипт, таким образом, можно также получить
информацию о различных кодах операции непосредственно из файла vdbe.c.
Если вы успешно дочитали настолько далеко, вы должны испытать мало
затруднений при понимании остального. Если вы находите ошибки в документации или в коде,
не стесняйтесь фиксировать их и/или связываться с автором по
drh@hwaci.com.
Ваши исправления ошибок или предложения всегда одобрены.
Small. Fast. Reliable.
Choose any three.
Виртуальное ядро базы данных SQLite
Предупреждение о старой документации:
Этот документ описывает виртуальную машину, используемую в версии 2.8.0
SQLite. Виртуальная машина в версии 3.0 и 3.1 SQLite подобна, но теперь
основана на регистре вместо стека,
имеет пять операндов за код операции вместо трех и другой
набор кодов операции. См. документ здесь
для текущего набора кодов операции VDBE и краткого обзора того, как VDBE
работает. Этот документ сохраняется как историческая ссылка.
Предварительные выборы
Вставка записей в базу данных
CREATE TABLE examp(one text, two int);
INSERT INTO examp VALUES('Hello, World!',99);
$ sqlite test_database_1
sqlite> CREATE TABLE examp(one text, two int);
sqlite> .explain
sqlite> EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
3 Integer 0 0
4 OpenWrite 0 3 examp
5 NewRecno 0 0
6 String 0 0 Hello, World!
7 Integer 99 0 99
8 MakeRecord 2 0
9 PutIntKey 0 1
10 Close 0 0
11 Commit 0 0
12 Halt 0 0
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
3 Integer 0 0
4 OpenWrite 0 3 examp
(integer) 0
5 NewRecno 0 0
(integer) new record key
6 String 0 0 Hello, World!
(string) "Hello, World!" (integer) new record key
7 Integer 99 0 99
(integer) 99 (string) "Hello, World!" (integer) new record key
8 MakeRecord 2 0
(record) "Hello, World!", 99 (integer) new record key
9 PutIntKey 0 1
10 Close 0 0
11 Commit 0 0
12 Halt 0 0
Отслеживание выполнения программы VDBE
sqlite> PRAGMA vdbe_trace=ON;
0 Halt 0 0
sqlite> INSERT INTO examp VALUES('Hello, World!',99);
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
3 Integer 0 0
Stack: i:0
4 OpenWrite 0 3 examp
5 NewRecno 0 0
Stack: i:2
6 String 0 0 Hello, World!
Stack: t[Hello,.World!] i:2
7 Integer 99 0 99
Stack: si:99 t[Hello,.World!] i:2
8 MakeRecord 2 0
Stack: s[...Hello,.World!.99] i:2
9 PutIntKey 0 1
10 Close 0 0
11 Commit 0 0
12 Halt 0 0
Простые запросы
SELECT * FROM examp;
sqlite> EXPLAIN SELECT * FROM examp;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 81
5 Rewind 0 10
6 Column 0 0
7 Column 0 1
8 Callback 2 0
9 Next 0 6
10 Close 0 0
11 Halt 0 0
int Callback(void *pUserData, int nColumn, char *azData[], char *azColumnName[]);
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 81
5 Rewind 0 10
6 Column 0 0
7 Column 0 1
8 Callback 2 0
9 Next 0 6
10 Close 0 0
11 Halt 0 0
Немного более сложный запрос
SELECT one, two, one || two AS 'both' FROM examp WHERE one LIKE 'H%'
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 ColumnName 2 0 both
3 Integer 0 0
4 OpenRead 0 3 examp
5 VerifyCookie 0 81
6 Rewind 0 18
7 String 0 0 H%
8 Column 0 0
9 Function 2 0 ptr(0x7f1ac0)
10 IfNot 1 17
11 Column 0 0
12 Column 0 1
13 Column 0 0
14 Column 0 1
15 Concat 2 0
16 Callback 3 0
17 Next 0 7
18 Close 0 0
19 Halt 0 0
Шаблон программ SELECT
Запросы UPDATE и DELETE
DELETE FROM examp WHERE two<50;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
5 Rewind 0 12
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
9 Recno 0 0
10 ListWrite 0 0
11 Next 0 6
12 Close 0 0
13 ListRewind 0 0
14 Integer 0 0
15 OpenWrite 0 3
16 ListRead 0 20
17 NotExists 0 19
18 Delete 0 1
19 Goto 0 16
20 ListReset 0 0
21 Close 0 0
22 Commit 0 0
23 Halt 0 0
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
5 Rewind 0 12
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
(integer) 50 (record) текущая запись для колонки "two"
9 Recno 0 0
10 ListWrite 0 0
11 Next 0 6
12 Close 0 0
13 ListRewind 0 0
14 Integer 0 0
15 OpenWrite 0 3
16 ListRead 0 20
17 NotExists 0 19
18 Delete 0 1
19 Goto 0 16
(integer) ключ текущей записи
Выдвиньте следующую "вещь" в стек и перейдите дальше или
перейдите к P2, в зависимости от того, есть ли следующая "вещь".
20 ListReset 0 0
21 Close 0 0
22 Commit 0 0
23 Halt 0 0
UPDATE examp SET one= '(' || one || ')' WHERE two < 50;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
5 Rewind 0 12
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
9 Recno 0 0
10 ListWrite 0 0
11 Next 0 6
12 Close 0 0
13 Integer 0 0
14 OpenWrite 0 3
15 ListRewind 0 0
16 ListRead 0 28
17 Dup 0 0
18 NotExists 0 16
19 String 0 0 (
20 Column 0 0
21 Concat 2 0
22 String 0 0 )
23 Concat 2 0
24 Column 0 1
25 MakeRecord 2 0
26 PutIntKey 0 1
27 Goto 0 16
28 ListReset 0 0
29 Close 0 0
30 Commit 0 0
31 Halt 0 0
(record) new data record (integer) key Запросы CREATE и DROP
CREATE TABLE sqlite_master (
type TEXT, -- either "table" or "index"
name TEXT, -- name of this table or index
tbl_name TEXT, -- for indices: name of associated table
sql TEXT -- SQL text of the original CREATE statement
)
Используя индексы для ускорения поиска
SELECT * FROM examp WHERE two==50;
SELECT * FROM examp WHERE two<50;
SELECT * FROM examp WHERE two IN (50, 100);
SELECT * FROM examp WHERE two%50 == 10;
SELECT * FROM examp WHERE two&127 == 3;
SELECT * FROM examp WHERE two+10 == 50;
SELECT * FROM examp WHERE two==50 OR two==100;
CREATE INDEX examp_idx1 ON examp(two);
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenWrite 0 2
5 NewRecno 0 0
6 String 0 0 index
7 String 0 0 examp_idx1
8 String 0 0 examp
9 CreateIndex 0 0 ptr(0x791380)
10 Dup 0 0
11 Integer 0 0
12 OpenWrite 1 0
13 String 0 0 CREATE INDEX examp_idx1 ON examp(tw
14 MakeRecord 5 0
15 PutIntKey 0 0
16 Integer 0 0
17 OpenRead 2 3 examp
18 Rewind 2 24
19 Recno 2 0
20 Column 2 1
21 MakeIdxKey 1 0 n
22 IdxPut 1 0 indexed columns are not unique
23 Next 2 19
24 Close 2 0
25 Close 1 0
26 Integer 333 0
27 SetCookie 0 0
28 Close 0 0
29 Commit 0 0
30 Halt 0 0
16 Integer 0 0
17 OpenRead 2 3 examp
18 Rewind 2 24
19 Recno 2 0
20 Column 2 1
21 MakeIdxKey 1 0 n
22 IdxPut 1 0 indexed columns are not unique
23 Next 2 19
SELECT * FROM examp WHERE two==50;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 256
5 Integer 0 0
6 OpenRead 1 4 examp_idx1
7 Integer 50 0 50
8 MakeKey 1 0 n
9 MemStore 0 0
10 MoveTo 1 19
11 MemLoad 0 0
12 IdxGT 1 19
13 IdxRecno 1 0
14 MoveTo 0 0
15 Column 0 0
16 Column 0 1
17 Callback 2 0
18 Next 1 11
19 Close 0 0
20 Close 1 0
21 Halt 0 0
INSERT INTO examp VALUES('Hello, World!',99);
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 256
3 Integer 0 0
4 OpenWrite 0 3 examp
5 Integer 0 0
6 OpenWrite 1 4 examp_idx1
7 NewRecno 0 0
8 String 0 0 Hello, World!
9 Integer 99 0 99
10 Dup 2 1
11 Dup 1 1
12 MakeIdxKey 1 0 n
13 IdxPut 1 0
14 MakeRecord 2 0
15 PutIntKey 0 1
16 Close 0 0
17 Close 1 0
18 Commit 0 0
19 Halt 0 0
Join
CREATE TABLE examp2(three int, four int);
SELECT * FROM examp, examp2 WHERE two<50 AND four==two;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 examp.one
1 ColumnName 1 0 examp.two
2 ColumnName 2 0 examp2.three
3 ColumnName 3 0 examp2.four
4 Integer 0 0
5 OpenRead 0 3 examp
6 VerifyCookie 0 909
7 Integer 0 0
8 OpenRead 1 5 examp2
9 Rewind 0 24
10 Column 0 1
11 Integer 50 0 50
12 Ge 1 23
13 Rewind 1 23
14 Column 1 1
15 Column 0 1
16 Ne 1 22
17 Column 0 0
18 Column 0 1
19 Column 1 0
20 Column 1 1
21 Callback 4 0
22 Next 1 14
23 Next 0 10
24 Close 0 0
25 Close 1 0
26 Halt 0 0
Уточнение ORDER BY
SELECT * FROM examp ORDER BY one DESC, two;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 909
5 Rewind 0 14
6 Column 0 0
7 Column 0 1
8 SortMakeRec 2 0
9 Column 0 0
10 Column 0 1
11 SortMakeKey 2 0 D+
12 SortPut 0 0
13 Next 0 6
14 Close 0 0
15 Sort 0 0
16 SortNext 0 19
17 SortCallback 2 0
18 Goto 0 16
19 SortReset 0 0
20 Halt 0 0
Агрегатные функции, GROUP BY и HAVING
SELECT three, min(three+four)+avg(four) FROM examp2 GROUP BY three;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 three
1 ColumnName 1 0 min(three+four)+avg(four)
2 AggReset 0 3
3 AggInit 0 1 ptr(0x7903a0)
4 AggInit 0 2 ptr(0x790700)
5 Integer 0 0
6 OpenRead 0 5 examp2
7 VerifyCookie 0 909
8 Rewind 0 23
9 Column 0 0
10 MakeKey 1 0 n
11 AggFocus 0 14
12 Column 0 0
13 AggSet 0 0
14 Column 0 0
15 Column 0 1
16 Add 0 0
17 Integer 1 0
18 AggFunc 0 1 ptr(0x7903a0)
19 Column 0 1
20 Integer 2 0
21 AggFunc 0 1 ptr(0x790700)
22 Next 0 9
23 Close 0 0
24 AggNext 0 31
25 AggGet 0 0
26 AggGet 0 1
27 AggGet 0 2
28 Add 0 0
29 Callback 2 0
30 Goto 0 24
31 Noop 0 0
32 Halt 0 0
Место в памяти
Надлежащее использование этого места памяти 0 Столбец "three", ключ к букету 1 Минимум значения "three+four" 2 Сумма всех значений "four". Это используется, чтобы
вычислить "avg(four)".
SELECT three, min(three+four)+avg(four) FROM examp2 WHERE three>four
GROUP BY three HAVING avg(four)<10;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 ColumnName 0 0 three
1 ColumnName 1 0 min(three+four)+avg(four)
2 AggReset 0 3
3 AggInit 0 1 ptr(0x7903a0)
4 AggInit 0 2 ptr(0x790700)
5 Integer 0 0
6 OpenRead 0 5 examp2
7 VerifyCookie 0 909
8 Rewind 0 26
9 Column 0 0
10 Column 0 1
11 Le 1 25
12 Column 0 0
13 MakeKey 1 0 n
14 AggFocus 0 17
15 Column 0 0
16 AggSet 0 0
17 Column 0 0
18 Column 0 1
19 Add 0 0
20 Integer 1 0
21 AggFunc 0 1 ptr(0x7903a0)
22 Column 0 1
23 Integer 2 0
24 AggFunc 0 1 ptr(0x790700)
25 Next 0 9
26 Close 0 0
27 AggNext 0 37
28 AggGet 0 2
29 Integer 10 0 10
30 Ge 1 27
31 AggGet 0 0
32 AggGet 0 1
33 AggGet 0 2
34 Add 0 0
35 Callback 2 0
36 Goto 0 27
37 Noop 0 0
38 Halt 0 0
Используя операторы SELECT как условия в выражении
SELECT * FROM examp WHERE two!=(SELECT three FROM examp2 WHERE four=5);
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 String 0 0
1 MemStore 0 1
2 Integer 0 0
3 OpenRead 1 5 examp2
4 VerifyCookie 0 909
5 Rewind 1 13
6 Column 1 1
7 Integer 5 0 5
8 Ne 1 12
9 Column 1 0
10 MemStore 0 1
11 Goto 0 13
12 Next 1 6
13 Close 1 0
14 ColumnName 0 0 one
15 ColumnName 1 0 two
16 Integer 0 0
17 OpenRead 0 3 examp
18 Rewind 0 26
19 Column 0 1
20 MemLoad 0 0
21 Eq 1 25
22 Column 0 0
23 Column 0 1
24 Callback 2 0
25 Next 0 19
26 Close 0 0
27 Halt 0 0
SELECT * FROM examp WHERE two IN (SELECT three FROM examp2);
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 OpenTemp 1 1
1 Integer 0 0
2 OpenRead 2 5 examp2
3 VerifyCookie 0 909
4 Rewind 2 10
5 Column 2 0
6 IsNull -1 9
7 String 0 0
8 PutStrKey 1 0
9 Next 2 5
10 Close 2 0
11 ColumnName 0 0 one
12 ColumnName 1 0 two
13 Integer 0 0
14 OpenRead 0 3 examp
15 Rewind 0 25
16 Column 0 1
17 NotNull -1 20
18 Pop 1 0
19 Goto 0 24
20 NotFound 1 24
21 Column 0 0
22 Column 0 1
23 Callback 2 0
24 Next 0 16
25 Close 0 0
26 Halt 0 0
Составные запросы SELECT
SELECT two FROM examp EXCEPT SELECT four FROM examp2;
addr opcode p1 p2 p3
---- ------------ ----- ----- -----------------------------------
0 OpenTemp 0 1
1 KeyAsData 0 1
2 Integer 0 0
3 OpenRead 1 3 examp
4 VerifyCookie 0 909
5 Rewind 1 11
6 Column 1 1
7 MakeRecord 1 0
8 String 0 0
9 PutStrKey 0 0
10 Next 1 6
11 Close 1 0
12 Integer 0 0
13 OpenRead 2 5 examp2
14 Rewind 2 20
15 Column 2 1
16 MakeRecord 1 0
17 NotFound 0 19
18 Delete 0 0
19 Next 2 15
20 Close 2 0
21 ColumnName 0 0 four
22 Rewind 0 26
23 Column 0 0
24 Callback 1 0
25 Next 0 23
26 Close 0 0
27 Halt 0 0
Итог