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/autoinc.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
1 files changed, 270 insertions, 0 deletions
diff --git a/www/autoinc.html b/www/autoinc.html
new file mode 100644
index 0000000..d120f0d
--- /dev/null
+++ b/www/autoinc.html
@@ -0,0 +1,270 @@
+<!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>SQLite Autoincrement</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>
+<div class=fancy>
+<div class=nosearch>
+<div class="fancy_title">
+SQLite Autoincrement
+</div>
+</div>
+
+
+
+
+<h1 id="summary"><span>1. </span>Summary</h1>
+
+<ol type="1">
+<li><p>
+  The AUTOINCREMENT keyword imposes extra CPU, memory, disk space,
+  and disk I/O overhead and should be avoided if not strictly needed.
+  It is usually not needed.
+</p></li><li><p>
+  In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the <a href="lang_createtable.html#rowid">ROWID</a>
+  (except in <a href="withoutrowid.html">WITHOUT ROWID</a> tables) which is always a 64-bit signed integer.
+</p></li><li><p>
+  On an <a href="lang_insert.html">INSERT</a>, if the ROWID or INTEGER PRIMARY KEY column is not 
+  explicitly given a value, then it
+  will be filled automatically with an unused integer, usually
+  one more than the largest ROWID currently in use.
+  This is true regardless of whether or not the AUTOINCREMENT keyword is used.
+</p></li><li><p>
+  If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
+  changes the automatic ROWID assignment algorithm to prevent
+  the reuse of ROWIDs over the lifetime of the database.  In other words,
+  the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from
+  previously deleted rows.
+</p></li></ol>
+
+<h1 id="background"><span>2. </span>Background</h1>
+
+<p>
+In SQLite, table rows normally have a 64-bit signed integer <a href="lang_createtable.html#rowid">ROWID</a>
+which is unique among all rows in the same table.
+(<a href="withoutrowid.html">WITHOUT ROWID</a> tables are the exception.)
+</p>
+
+<p>
+You can access the ROWID of an SQLite table using one of the special column
+names ROWID, _ROWID_, or OID.
+Except if you declare an ordinary table column to use one of those special
+names, then the use of that name will refer to the declared column not
+to the internal ROWID.
+</p>
+
+<p>
+If a table contains a column of type <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, then that
+column becomes an alias for the ROWID.  You can then access the ROWID
+using any of four different names, the original three names described above
+or the name given to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column.  All these names are
+aliases for one another and work equally well in any context.
+</p>
+
+<p>
+When a new row is inserted into an SQLite table, the ROWID can either
+be specified as part of the INSERT statement or it can be assigned
+automatically by the database engine.  To specify a ROWID manually,
+just include it in the list of values to be inserted.  For example:
+</p>
+
+<div class="codeblock"><pre>CREATE TABLE test1(a INT, b TEXT);
+INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
+</pre></div>
+
+<p>
+If no ROWID is specified on the insert, or if the specified ROWID has a value
+of NULL, then an appropriate ROWID is created
+automatically.  The usual algorithm is to give the newly created row
+a ROWID that is one larger than the largest ROWID in the table prior
+to the insert.  If the table is initially empty, then a ROWID of 1 is
+used.  If the largest ROWID is equal to the largest possible integer
+(9223372036854775807) then the database
+engine starts picking positive candidate ROWIDs at random until it finds one
+that is not previously used.
+If no unused ROWID can be found after a reasonable number of attempts,
+the insert operation fails with an <a href="rescode.html#full">SQLITE_FULL</a> error.
+If no negative ROWID values are inserted explicitly, then automatically 
+generated ROWID values will always be greater than zero.
+</p>
+
+<p>
+The normal ROWID selection algorithm described above
+will generate monotonically increasing
+unique ROWIDs as long as you never use the maximum ROWID value and you never
+delete the entry in the table with the largest ROWID. 
+If you ever delete rows or if you ever create a row with the maximum possible
+ROWID, then ROWIDs from previously deleted rows might be reused when creating
+new rows and newly created ROWIDs might not be in strictly ascending order.
+</p>
+
+
+<h1 id="the_autoincrement_keyword"><span>3. </span>The AUTOINCREMENT Keyword</h1>
+
+<p>
+If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
+different ROWID selection algorithm is used.  
+The ROWID chosen for the new row is at least one larger than the largest ROWID
+that has ever before existed in that same table.  If the table has never
+before contained any data, then a ROWID of 1 is used.  If the largest possible
+ROWID has previously been inserted, then
+new INSERTs are not allowed and any attempt to insert a new row will
+fail with an SQLITE_FULL error.  
+Only ROWID values from previous transactions that
+were committed are considered.  ROWID values that were rolled back
+are ignored and can be reused.
+</p>
+
+<p>
+SQLite keeps track of the largest ROWID
+using an <a href="fileformat2.html#intschema">internal table</a> named "<a href="fileformat2.html#seqtab">sqlite_sequence</a>".
+The sqlite_sequence table is created
+and initialized automatically whenever a normal table that contains an
+AUTOINCREMENT column is created.  The content of the sqlite_sequence table
+can be modified using ordinary UPDATE, INSERT, and DELETE statements.
+But making modifications to this table will likely perturb the AUTOINCREMENT
+key generation algorithm.  Make sure you know what you are doing before
+you undertake such changes.
+The sqlite_sequence table does not track ROWID changes associated with
+UPDATE statement, only INSERT statements.
+</p>
+
+<p>
+The behavior implemented by the AUTOINCREMENT keyword is subtly different
+from the default behavior.  With AUTOINCREMENT, rows with automatically
+selected ROWIDs are guaranteed to have ROWIDs that have never been used
+before by the same table in the same database.  And the automatically generated
+ROWIDs are guaranteed to be monotonically increasing.  These are important
+properties in certain applications.  But if your application does not
+need these properties, you should probably stay with the default behavior
+since the use of AUTOINCREMENT requires additional work to be done
+as each row is inserted and thus causes INSERTs to run a little slower.
+</p>
+
+<p>Note that "monotonically increasing" does not imply that the ROWID always
+increases by exactly one.  One is the usual increment.  However, if an
+insert fails due to (for example) a uniqueness constraint, the ROWID of
+the failed insertion attempt might not be reused on subsequent inserts,
+resulting in gaps in the ROWID sequence.  AUTOINCREMENT guarantees that
+automatically chosen ROWIDs will be increasing but not that they will be
+sequential.</p>
+
+<p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection
+algorithm, AUTOINCREMENT is not allowed on <a href="withoutrowid.html">WITHOUT ROWID</a> tables or on any
+table column other than INTEGER PRIMARY KEY.  Any attempt to use 
+AUTOINCREMENT on a <a href="withoutrowid.html">WITHOUT ROWID</a> table or on a column other than the
+INTEGER PRIMARY KEY column results in an error.</p>
+<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/autoinc.in?m=bd2decf99d7aab9c9">2017-02-02 23:55:23</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/autoinc.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip