Пределы реализации SQLite

Ограничения в SQLite

«Ограничения» в контексте этой статьи означают размеры или количества, которые не могут быть превышены. Мы имеем дело с такими вещами, как максимальное количество байтов в BLOB или максимальное количество столбцов в таблице.

SQLite изначально разрабатывался с политикой избегания произвольных ограничений. Конечно, у каждой программы, работающей на машине с ограниченным объемом памяти и дискового пространства, есть какие-то ограничения. Но в SQLite эти ограничения не были четко определены. Политика заключалась в том, что если это помещается в память, и вы можете посчитать это с помощью 32-битного целого числа, то это должно работать.

К сожалению, политика отсутствия ограничений показала свою несостоятельность. Поскольку верхние границы не были четко определены, они не тестировались, и ошибки часто находились при доведении SQLite до крайних пределов. По этой причине в версиях SQLite начиная примерно с 3.5.8 (2008-04-16) были четко определены ограничения, и эти ограничения тестируются как часть набора тестов тестового пакета.

В этой статье определены ограничения SQLite и способы их настройки для конкретных приложений. Значения по умолчанию для ограничений обычно довольно велики и подходят для почти всех приложений. Некоторые приложения могут захотеть увеличить то или иное ограничение, но мы ожидаем, что такие потребности будут редки. Чаще всего приложение может захотеть перекомпилировать SQLite с значительно более низкими ограничениями, чтобы избежать чрезмерного использования ресурсов в случае ошибки в генераторах SQL-запросов более высокого уровня или для защиты от злоумышленников, которые вводят вредоносные SQL-запросы.

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

Максимальная длина строки или BLOB

Максимальное количество байтов в строке или BLOB в SQLite определяется препроцессорной макрос SQLITE_MAX_LENGTH. Значение по умолчанию этой макросы составляет 1 миллиард (1 тысяча миллионов или 1 000 000 000). Вы можете увеличить или уменьшить это значение во время компиляции, используя опцию командной строки так:

-DSQLITE_MAX_LENGTH=123456789

Текущая реализация будет поддерживать длину строки или BLOB только до 2³¹-1 или 2147483647. А некоторые встроенные функции, такие как hex(), могут работать неправильно ещё до этого момента. В приложениях, чувствительных к безопасности, лучше не пытаться увеличить максимальную длину строк и BLOB. На самом деле, вы можете с пользой уменьшить максимальную длину строк и BLOB до чего-то в пределах нескольких миллионов, если это возможно.

Во время части обработки INSERT и SELECT в SQLite всё содержимое каждой строки в базе данных кодируется как один BLOB. Таким образом, параметр SQLITE_MAX_LENGTH также определяет максимальное количество байтов в строке.

Максимальную длину строки или BLOB можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size).
Максимальное количество столбцов

Параметр SQLITE_MAX_COLUMN во время компиляции используется для задания верхнего предела:
- Количество столбцов в таблице
- Количество столбцов в индексе
- Количество столбцов в представлении
- Количество терминов в операторе SET в инструкции UPDATE
- Количество столбцов в результирующем наборе оператора SELECT
- Количество терминов в операторах GROUP BY или ORDER BY
- Количество значений в инструкции INSERT
Значение по умолчанию для SQLITE_MAX_COLUMN — 2000. Вы можете изменить его во время компиляции на значения до 32767. С другой стороны, многие опытные разработчики баз данных утверждают, что хорошо нормализованная база данных никогда не потребует более 100 столбцов в таблице.

В большинстве приложений количество столбцов невелико — несколько десятков. Существуют места в генераторе кода SQLite, где используются алгоритмы с O(N²), где N — количество столбцов. Поэтому, если вы переопределите SQLITE_MAX_COLUMN на очень большое число и сгенерируете SQL, использующий большое количество столбцов, вы можете обнаружить, что sqlite3_prepare_v2() работает медленно.

Максимальное количество столбцов можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_COLUMN,size).
Максимальная длина SQL-запроса

Максимальное количество байтов в тексте SQL-запроса ограничено SQLITE_MAX_SQL_LENGTH, по умолчанию равным 1 000 000 000.

Если длина SQL-запроса ограничена миллионом байтов, то, очевидно, вы не сможете вставить строки длиной в несколько миллионов байтов, вставив их как литералы в инструкции INSERT. Но вам этого и не следует делать. Используйте параметры хоста для данных. Подготовьте короткие SQL-запросы так:

INSERT INTO tab1 VALUES(?,?,?);

Затем используйте функции sqlite3_bind_XXXX() для привязки ваших больших строковых значений к SQL-запросу. Использование привязки исключает необходимость экранирования символов кавычек в строке, снижая риск атак SQL-инъекции. Кроме того, это работает быстрее, поскольку большая строка не должна парсироваться или копироваться так часто.

Максимальную длину SQL-запроса можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size).
Максимальное количество таблиц в соединении

SQLite не поддерживает соединения, содержащие более 64 таблиц. Это ограничение обусловлено тем, что генератор кода SQLite использует битовые карты с одним битом на таблицу присоединения в оптимизаторе запросов.

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

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

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

Максимальную глубину дерева выражения можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_EXPR_DEPTH,size), если значение SQLITE_MAX_EXPR_DEPTH изначально положительно. Другими словами, максимальная глубина выражения может быть уменьшена во время выполнения, если есть ограничение глубины выражения во время компиляции. Если SQLITE_MAX_EXPR_DEPTH установлено в 0 во время компиляции (если глубина выражений не ограничена), то sqlite3_limit(db,SQLITE_LIMIT_EXPR_DEPTH,size) — это бесполезная операция.
Максимальное количество аргументов функции

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

Количество аргументов функции иногда хранится в знаковом символе. Таким образом, существует жёсткое верхнее ограничение для SQLITE_MAX_FUNCTION_ARG — 127.

Максимальное количество аргументов функции можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_FUNCTION_ARG,size).
Максимальное количество терминов в составном операторе SELECT

Составной оператор SELECT — это два или более операторов SELECT, соединённых операторами UNION, UNION ALL, EXCEPT или INTERSECT. Мы называем каждый отдельный оператор SELECT в составе оператора SELECT «термином».

Генератор кода в SQLite обрабатывает составные операторы SELECT с помощью рекурсивного алгоритма. Чтобы ограничить размер стека, мы, следовательно, ограничиваем количество терминов в составном операторе SELECT. Максимальное количество терминов — SQLITE_MAX_COMPOUND_SELECT, по умолчанию равное 500. Мы считаем, что это щедрый лимит, так как на практике количество терминов в составном операторе SELECT почти никогда не превышает однозначных чисел.

Максимальное количество терминов составного оператора SELECT можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_COMPOUND_SELECT,size).
Максимальная длина шаблона LIKE или GLOB

Алгоритм сопоставления с образцом, используемый в реализации LIKE и GLOB по умолчанию в SQLite, может демонстрировать производительность O(N²) (где N — количество символов в шаблоне) для определённых патологических случаев. Чтобы избежать атак типа отказа в обслуживании от злоумышленников, которые могут указать свои собственные шаблоны LIKE или GLOB, длина шаблона LIKE или GLOB ограничена SQLITE_MAX_LIKE_PATTERN_LENGTH байтами. Значение по умолчанию этого ограничения составляет 50000. Современный рабочий стол может обрабатывать даже патологический шаблон LIKE или GLOB размером 50000 байтов относительно быстро. Проблема отказа в обслуживании возникает только тогда, когда длина шаблона достигает миллионов байтов. Тем не менее, так как большинство полезных шаблонов LIKE или GLOB имеют не более нескольких десятков байтов, параноидальные разработчики приложений могут захотеть уменьшить этот параметр до чего-то в диапазоне нескольких сотен, если они знают, что внешние пользователи могут генерировать произвольные шаблоны.

Максимальную длину шаблона LIKE или GLOB можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_LIKE_PATTERN_LENGTH,size).
Максимальное количество параметров хоста в одном SQL-запросе

Параметр хоста хоста — это заполнитель в SQL-запросе, который заполняется с помощью одного из интерфейсов sqlite3_bind_XXXX(). Многие программисты, работающие с SQL, знакомы с использованием вопросительного знака («?») в качестве параметра хоста. SQLite также поддерживает именованные параметры хоста, начинающиеся с «:», «$» или «@», и пронумерованные параметры хоста в форме «?123».

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

SQLite выделяет память для хранения всех параметров хоста между 1 и самым большим номером параметра хоста, используемым. Таким образом, SQL-запрос, содержащий параметр хоста, такой как ?1000000000, потребовал бы гигабайты памяти. Это легко могло бы перегрузить ресурсы машины-хоста. Чтобы предотвратить чрезмерное выделение памяти, максимальное значение номера параметра хоста — SQLITE_MAX_VARIABLE_NUMBER, по умолчанию равное 999 для версий SQLite до 3.32.0 (2020-05-22) или 32766 для версий SQLite после 3.32.0.

Максимальное значение номера параметра хоста можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size).
Максимальная глубина рекурсии триггера

SQLite ограничивает глубину рекурсии триггеров, чтобы предотвратить использование неограниченного количества памяти заявлением, включающим рекурсивные триггеры.

До версии SQLite 3.6.18 (2009-09-11) триггеры не были рекурсивными, поэтому это ограничение было бессмысленным. Начиная с версии 3.6.18, рекурсивные триггеры были поддерживаются, но должны были быть явно включены с помощью инструкции PRAGMA recursive_triggers. Начиная с версии 3.7.0 (2009-09-11), рекурсивные триггеры включены по умолчанию, но могут быть вручную отключены с помощью PRAGMA recursive_triggers. SQLITE_MAX_TRIGGER_DEPTH имеет смысл только при включённых рекурсивных триггерах.

Максимальная глубина рекурсии триггера по умолчанию составляет 1000.

Максимальное количество присоединённых баз данных

Оператор ATTACH — это расширение SQLite, позволяющее связать две или более баз данных с одним соединением и работать с ними как с одной базой данных. Количество одновременно присоединённых баз данных ограничено значением SQLITE_MAX_ATTACHED, которое по умолчанию равно 10. Максимальное число присоединённых баз данных не может быть увеличено более чем до 125.

Максимальное количество присоединённых баз данных можно уменьшить во время выполнения, используя интерфейс sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size).
Максимальное количество страниц в файле базы данных

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

Максимальное возможное значение SQLITE_MAX_PAGE_COUNT — 4294967294 (2³²-2). Начиная с версии 3.45.0 (2024-01-15), 4294967294 также является значением по умолчанию для SQLITE_MAX_PAGE_COUNT. При использовании стандартного размера страницы 4096 байтов это обеспечивает максимальный размер базы данных примерно 17,5 терабайта. Если размер страницы увеличить до максимального значения 65536 байтов, размер файла базы данных может достичь примерно 281 терабайта.

С помощью pragma max_page_count можно изменять это ограничение во время выполнения.
Максимальное количество строк в таблице

Теоретическое максимальное количество строк в таблице — 2⁶⁴ (18446744073709551616 или около 1,8e+19). Это ограничение недостижимо, так как сначала будет достигнут максимальный размер базы данных в 281 терабайт. База данных размером 281 терабайт может содержать не более приблизительно 2e+13 строк, и только в том случае, если нет индексов и каждая строка содержит очень мало данных.
Максимальный размер базы данных

Каждая база данных состоит из одной или нескольких «страниц». В пределах одной базы данных все страницы имеют одинаковый размер, но разные базы данных могут иметь размеры страниц, которые являются степенями двойки в диапазоне от 512 до 65536 включительно. Максимальный размер файла базы данных составляет 4294967294 страницы. При максимальном размере страницы 65536 байтов это соответствует максимальному размеру базы данных приблизительно 1,4e+14 байтов (281 терабайт, или 256 тебибайт, или 281474 гигабайта, или 256 000 гибибайт).

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

Для каждой таблицы и индекса требуется по крайней мере одна страница в файле базы данных. «Индекс» в предыдущем предложении означает индекс, созданный явно с помощью оператора CREATE INDEX, или неявные индексы, созданные ограничениями UNIQUE и PRIMARY KEY. Поскольку максимальное количество страниц в файле базы данных составляет 2147483646 (чуть более 2 миллиардов), это также является верхним пределом для числа таблиц и индексов в схеме.

При открытии базы данных вся схема сканируется и анализируется, и дерево разбора схемы хранится в памяти. Это означает, что время запуска соединения с базой данных и начальное использование памяти пропорциональны размеру схемы.

SQLite is in the Public Domain.
https://sqlite.org/limits.html