Adding upstream version 3.40.1.upstream/3.40.1 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
commit: 18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree: 17b438b680ed45a996d7b59951e6aa34023783f2 /www/limits.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
1 files changed, 535 insertions, 0 deletions
diff --git a/www/limits.html b/www/limits.html
new file mode 100644
index 0000000..32d3c5c
--- /dev/null
+++ b/www/limits.html
@@ -0,0 +1,535 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>Implementation Limits For SQLite</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+
+
+
+<h2>Limits In SQLite</h2>
+
+<p>
+"Limits" in the context of this article means sizes or
+quantities that can not be exceeded.  We are concerned
+with things like the maximum number of bytes in a
+BLOB or the maximum number of columns in a table.
+</p>
+
+<p>
+SQLite was originally designed with a policy of avoiding
+arbitrary limits.
+Of course, every program that runs on a machine with finite
+memory and disk space has limits of some kind.  But in SQLite, 
+those limits
+were not well defined.  The policy was that if it would fit
+in memory and you could count it with a 32-bit integer, then
+it should work.
+</p>
+
+<p>
+Unfortunately, the no-limits policy has been shown to create
+problems.  Because the upper bounds were not well
+defined, they were not tested, and bugs were often found
+when pushing SQLite to extremes.  For this reason, versions
+of SQLite since about release 3.5.8 (2008-04-16)
+have well-defined limits, and those limits are tested as part of
+the <a href="testing.html">test suite</a>.
+</p>
+
+<p>
+This article defines what the limits of SQLite are and how they
+can be customized for specific applications.  The default settings
+for limits are normally quite large and adequate for almost every
+application.  Some applications may want to increase a limit here
+or there, but we expect such needs to be rare.  More commonly,
+an application might want to recompile SQLite with much lower
+limits to avoid excess resource utilization in the event of
+bug in higher-level SQL statement generators or to help thwart 
+attackers who inject malicious SQL statements.
+</p>
+
+<p>
+Some limits can be changed at run-time on a per-connection basis
+using the <a href="c3ref/limit.html">sqlite3_limit()</a> interface with one of the
+<a href="c3ref/c_limit_attached.html#sqlitelimitlength">limit categories</a> defined for that interface.
+Run-time limits are designed for applications that have multiple
+databases, some of which are for internal use only and others which
+can be influenced or controlled by potentially hostile external agents.
+For example, a web browser application might use an internal database
+to track historical page views but have one or more separate databases
+that are created and controlled by javascript applications that are
+downloaded from the internet.
+The <a href="c3ref/limit.html">sqlite3_limit()</a> interface allows internal databases managed by
+trusted code to be unconstrained while simultaneously  placing tight
+limitations on databases created or controlled by untrusted external
+code in order to help prevent a denial of service attack.
+</p>
+
+
+<ol>
+<a name="max_length"></a>
+<li><p><b>Maximum length of a string or BLOB</b></p>
+
+<p>
+The maximum number of bytes in a string or BLOB in SQLite is defined
+by the preprocessor macro SQLITE_MAX_LENGTH.  The default value
+of this macro is 1 billion (1 thousand million or 1,000,000,000).
+You can raise or lower this value at compile-time using a command-line 
+option like this:
+</p>
+
+<blockquote>-DSQLITE_MAX_LENGTH=123456789</blockquote>
+
+<p>
+The current implementation will only support a string or BLOB
+length up to 2<small><sup>31</sup></small>-1 or 2147483647.  And
+some built-in functions such as hex() might fail well before that
+point.  In security-sensitive applications it is best not to
+try to increase the maximum string and blob length.  In fact,
+you might do well to lower the maximum string and blob length
+to something more in the range of a few million if that is
+possible.
+</p>
+
+<p>
+During part of SQLite's INSERT and SELECT processing, the complete
+content of each row in the database is encoded as a single BLOB.
+So the SQLITE_MAX_LENGTH parameter also determines the maximum
+number of bytes in a row.
+</p>
+
+<p>
+The maximum string or BLOB length can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlength">SQLITE_LIMIT_LENGTH</a>,size) interface.
+</p>
+</li><a name="max_column"></a>
+<li><p><b>Maximum Number Of Columns</b></p>
+
+<p>
+The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
+bound on:
+</p>
+
+<ul>
+<li>The number of columns in a table</li>
+<li>The number of columns in an index</li>
+<li>The number of columns in a view</li>
+<li>The number of terms in the SET clause of an UPDATE statement</li>
+<li>The number of columns in the result set of a SELECT statement</li>
+<li>The number of terms in a GROUP BY or ORDER BY clause</li>
+<li>The number of values in an INSERT statement</li>
+</ul>
+
+<p>
+The default setting for SQLITE_MAX_COLUMN is 2000.  You can change it
+at compile time to values as large as 32767. On the other hand, many
+experienced database designers will argue that a well-normalized database
+will never need more than 100 columns in a table.
+</p>
+
+<p>
+In most applications, the number of columns is small - a few dozen.
+There are places in the SQLite code generator that use algorithms
+that are O(N&sup2;) where N is the number of columns.  
+So if you redefine SQLITE_MAX_COLUMN to be a
+really huge number and you generate SQL that uses a large number of
+columns, you may find that <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
+runs slowly.</p>
+
+
+<p>
+The maximum number of columns can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcolumn">SQLITE_LIMIT_COLUMN</a>,size) interface.
+</p>
+
+</li><a name="max_sql_length"></a>
+<li><p><b>Maximum Length Of An SQL Statement</b></p>
+
+<p>
+The maximum number of bytes in the text of an SQL statement is 
+limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.
+</p>
+
+<p>
+If an SQL statement is limited to be a million bytes in length, then
+obviously you will not be able to insert multi-million byte strings
+by embedding them as literals inside of INSERT statements.  But
+you should not do that anyway.  Use host <a href="lang_expr.html#varparam">parameters</a> 
+for your data.  Prepare short SQL statements like this:
+</p>
+
+<blockquote>
+INSERT INTO tab1 VALUES(?,?,?);
+</blockquote>
+
+<p>
+Then use the <a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> functions
+to bind your large string values to the SQL statement.  The use of binding
+obviates the need to escape quote characters in the string, reducing the
+risk of SQL injection attacks.  It also runs faster since the large
+string does not need to be parsed or copied as much.
+</p>
+
+<p>
+The maximum length of an SQL statement can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitsqllength">SQLITE_LIMIT_SQL_LENGTH</a>,size) interface.
+</p>
+</li><li><p><b>Maximum Number Of Tables In A Join</b></p>
+
+<p>
+SQLite does not support joins containing more than 64 tables.
+This limit arises from the fact that the SQLite code generator
+uses bitmaps with one bit per join-table in the query optimizer.
+</p>
+
+<p>
+SQLite uses an efficient <a href="queryplanner-ng.html">query planner algorithm</a>
+and so even a large join can be <a href="c3ref/prepare.html">prepared</a> quickly.
+Hence, there is no mechanism to raise or lower the limit on the
+number of tables in a join.
+</p>
+</li><a name="max_expr_depth"></a>
+<li><p><b>Maximum Depth Of An Expression Tree</b></p>
+
+<p>
+SQLite parses expressions into a tree for processing.  During
+code generation, SQLite walks this tree recursively.  The depth
+of expression trees is therefore limited in order to avoid
+using too much stack space.
+</p>
+
+<p>
+The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
+tree depth.  If the value is 0, then no limit is enforced.  The
+current implementation has a default value of 1000.
+</p>
+
+<p>
+The maximum depth of an expression tree can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) interface if the
+SQLITE_MAX_EXPR_DEPTH is initially positive.  In other words, the maximum
+expression depth can be lowered at run-time if there is already a 
+compile-time limit on the expression depth.  If SQLITE_MAX_EXPR_DEPTH is
+set to 0 at compile time (if the depth of expressions is unlimited) then
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) is a no-op.
+</p>
+
+
+</li><a name="max_function_arg"></a>
+<li><p><b>Maximum Number Of Arguments On A Function</b></p>
+
+<p>
+The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
+of parameters that can be passed to an SQL function.  The default value
+of this limit is 100.  SQLite should work with functions that have 
+thousands of parameters.  However, we suspect that anybody who tries
+to invoke a function with more than a few parameters is really
+trying to find security exploits in systems that use SQLite, 
+not do useful work, 
+and so for that reason we have set this parameter relatively low.</p>
+
+<p>The number of arguments to a function is sometimes stored in a signed
+character.  So there is a hard upper bound on SQLITE_MAX_FUNCTION_ARG
+of 127.</p>
+
+<p>
+The maximum number of arguments in a function can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitfunctionarg">SQLITE_LIMIT_FUNCTION_ARG</a>,size) interface.
+</p>
+</li><a name="max_compound_select"></a>
+<li><p><b>Maximum Number Of Terms In A Compound SELECT Statement</b></p>
+
+<p>
+A compound <a href="lang_select.html">SELECT</a> statement is two or more SELECT statements connected
+by operators UNION, UNION ALL, EXCEPT, or INTERSECT.  We call each
+individual SELECT statement within a compound SELECT a "term".
+</p>
+
+<p>
+The code generator in SQLite processes compound SELECT statements using
+a recursive algorithm.  In order to limit the size of the stack, we
+therefore limit the number of terms in a compound SELECT.  The maximum
+number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
+We think this is a generous allotment since in practice we almost
+never see the number of terms in a compound select exceed single digits.
+</p>
+
+<p>
+The maximum number of compound SELECT terms can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcompoundselect">SQLITE_LIMIT_COMPOUND_SELECT</a>,size) interface.
+</p>
+
+
+</li><a name="max_like_pattern_length"></a>
+<li><p><b>Maximum Length Of A LIKE Or GLOB Pattern</b></p>
+
+<p>
+The pattern matching algorithm used in the default <a href="lang_expr.html#like">LIKE</a> and <a href="lang_expr.html#glob">GLOB</a>
+implementation of SQLite can exhibit O(N&sup2;) performance (where
+N is the number of characters in the pattern) for certain pathological
+cases.  To avoid denial-of-service attacks from miscreants who are able
+to specify their own LIKE or GLOB patterns, the length of the LIKE
+or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
+The default value of this limit is 50000.  A modern workstation can
+evaluate even a pathological LIKE or GLOB pattern of 50000 bytes
+relatively quickly.  The denial of service problem only comes into
+play when the pattern length gets into millions of bytes.  Nevertheless,
+since most useful LIKE or GLOB patterns are at most a few dozen bytes
+in length, paranoid application developers may want to reduce this
+parameter to something in the range of a few hundred if they know that
+external users are able to generate arbitrary patterns.
+</p>
+
+<p>
+The maximum length of a LIKE or GLOB pattern can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlikepatternlength">SQLITE_LIMIT_LIKE_PATTERN_LENGTH</a>,size) interface.
+</p>
+</li><a name="max_variable_number"></a>
+<li><p><b>Maximum Number Of Host Parameters In A Single SQL Statement</b></p>
+
+<p>
+A host <a href="lang_expr.html#varparam">parameter</a> is a place-holder in an SQL statement that is filled
+in using one of the
+<a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces.
+Many SQL programmers are familiar with using a question mark ("?") as a
+host parameter.  SQLite also supports named host parameters prefaced
+by ":", "$", or "@" and numbered host parameters of the form "?123".
+</p>
+
+<p>
+Each host parameter in an SQLite statement is assigned a number.  The
+numbers normally begin with 1 and increase by one with each new
+parameter.  However, when the "?123" form is used, the host parameter
+number is the number that follows the question mark.
+</p>
+
+<p>
+SQLite allocates space to hold all host parameters between 1 and the
+largest host parameter number used.  Hence, an SQL statement that contains
+a host parameter like ?1000000000 would require gigabytes of storage.
+This could easily overwhelm the resources of the host machine.
+To prevent excessive memory allocations, 
+the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER,
+which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22)
+or 32766 for SQLite versions after 3.32.0.
+</p>
+
+<p>
+The maximum host parameter number can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitvariablenumber">SQLITE_LIMIT_VARIABLE_NUMBER</a>,size) interface.
+</p>
+</li><a name="max_trigger_depth"></a>
+<li><p><b>Maximum Depth Of Trigger Recursion</b></p>
+
+<p>
+SQLite limits the depth of recursion of triggers in order to prevent
+a statement involving recursive triggers from using an unbounded amount
+of memory.  
+</p>
+
+<p>Prior to SQLite <a href="releaselog/3_6_18.html">version 3.6.18</a> (2009-09-11), 
+triggers were not recursive and so
+this limit was meaningless.  Beginning with version 3.6.18, recursive triggers
+were supported but had to be explicitly enabled using the
+<a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a> statement.  
+Beginning with <a href="releaselog/3_7_0.html">version 3.7.0</a> (2009-09-11), 
+recursive triggers are enabled by default but can be manually disabled
+using <a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a>.  The SQLITE_MAX_TRIGGER_DEPTH is
+only meaningful if recursive triggers are enabled.</p>
+
+<p>The default maximum trigger recursion depth is 1000.</p>
+</li><a name="max_attached"></a>
+<li><p><b>Maximum Number Of Attached Databases</b></p>
+
+<p>
+The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
+that allows two or more databases to be associated to the same database
+connection and to operate as if they were a single database.  The number
+of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED
+which is set to 10 by default.
+The maximum number of attached databases cannot be increased above 125.</p>
+
+<p>
+The maximum number of attached databases can be lowered at run-time using
+the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitattached">SQLITE_LIMIT_ATTACHED</a>,size) interface.
+</p>
+</li><a name="max_page_count"></a>
+<li><p><b>Maximum Number Of Pages In A Database File</b></p>
+
+<p>
+SQLite is able to limit the size of a database file to prevent
+the database file from growing too large and consuming too much
+disk space.
+The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
+1073741823, is the maximum number of pages allowed in a single
+database file.  An attempt to insert new data that would cause
+the database file to grow larger than this will return
+SQLITE_FULL.
+</p>
+
+<p>
+The largest possible setting for SQLITE_MAX_PAGE_COUNT is 4294967294.
+When used with the maximum page size of 65536, this gives a maximum
+SQLite database size of about 281 terabytes.</p>
+
+<p>
+The <a href="pragma.html#pragma_max_page_count">
+max_page_count PRAGMA</a> can be used to raise or lower this
+limit at run-time.
+</p>
+</li><li><p><b>Maximum Number Of Rows In A Table</b></p>
+
+<p>
+The theoretical maximum number of rows in a table is
+2<sup><small>64</small></sup> (18446744073709551616 or about 1.8e+19).
+This limit is unreachable since the maximum database size of 281 terabytes
+will be reached first.  A 281 terabytes database can hold no more than
+approximately 2e+13 rows, and then only if there are no indices and if
+each row contains very little data.
+</li><li><p><b>Maximum Database Size</b></p>
+
+<p>
+Every database consists of one or more "pages".  Within a single database,
+every page is the same size, but different databases can have page sizes
+that are powers of two between 512 and 65536, inclusive.  The maximum
+size of a database file is 4294967294 pages.  At the maximum page size
+of 65536 bytes, this translates into a maximum database size of 
+approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or
+281474 gigabytes or 256,000 gibibytes).
+<p>
+This particular upper bound is untested since the developers do not 
+have access to hardware capable of reaching this limit.  However, tests
+do verify that SQLite behaves correctly and sanely when a database 
+reaches the maximum file size of the underlying filesystem (which is
+usually much less than the maximum theoretical database size) and when
+a database is unable to grow due to disk space exhaustion.
+</li><li><p><b>Maximum Number Of Tables In A Schema</b></p>
+
+<p>
+Each table and index requires at least one page in the database file.
+An "index" in the previous sentence means an index created explicitly
+using a <a href="lang_createindex.html">CREATE INDEX</a> statement or implicit indices created by UNIQUE
+and PRIMARY KEY constraints.  Since the maximum number of pages in a
+database file is 2147483646 (a little over 2 billion) this is also then
+an upper bound on the number of tables and indices in a schema.
+<p>
+Whenever a database is opened, the entire schema is scanned and parsed
+and a parse tree for the schema is held in memory.  That means that
+database connection startup time and initial memory usage
+is proportional to the size of the schema.
+</li>
+</ol>
+<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/limits.in?m=896d3c205a3ca0d90">2022-01-20 21:38:08</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
commit	18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree	17b438b680ed45a996d7b59951e6aa34023783f2 /www/limits.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip