Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
commit: 63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree: 01c7571c7c762ceee70638549a99834fdd7c411b /www/rbu.html
parent: Initial commit. (diff)
download: sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
1 files changed, 765 insertions, 0 deletions
diff --git a/www/rbu.html b/www/rbu.html
new file mode 100644
index 0000000..4ba42d7
--- /dev/null
+++ b/www/rbu.html
@@ -0,0 +1,765 @@
+<!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>The RBU Extension</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">
+The RBU Extension
+</div>
+<div class="fancy_toc">
+<a onclick="toggle_toc()">
+<span class="fancy_toc_mark" id="toc_mk">&#x25ba;</span>
+Table Of Contents
+</a>
+<div id="toc_sub"><div class="fancy-toc1"><a href="#the_rbu_extension">1. The RBU Extension</a></div>
+<div class="fancy-toc1"><a href="#rbu_updates">2. RBU Updates</a></div>
+<div class="fancy-toc2"><a href="#rbu_update_limitations">2.1. RBU Update Limitations</a></div>
+<div class="fancy-toc2"><a href="#preparing_an_rbu_update_file">2.2. Preparing an RBU Update File</a></div>
+<div class="fancy-toc3"><a href="#the_rbu_database_schema">2.2.1. The RBU Database Schema</a></div>
+<div class="fancy-toc3"><a href="#rbu_database_contents">2.2.2. RBU Database Contents</a></div>
+<div class="fancy-toc3"><a href="#using_rbu_with_fts3_4_tables">2.2.3. Using RBU with FTS3/4 Tables</a></div>
+<div class="fancy-toc3"><a href="#automatically_generating_rbu_updates_with_sqldiff">2.2.4. Automatically Generating RBU Updates with sqldiff</a></div>
+<div class="fancy-toc2"><a href="#rbu_update_c_c_programming">2.3. RBU Update C/C++ Programming</a></div>
+<div class="fancy-toc1"><a href="#rbu_vacuum">3. RBU Vacuum</a></div>
+<div class="fancy-toc2"><a href="#rbu_vacuum_limitations">3.1. RBU Vacuum Limitations</a></div>
+<div class="fancy-toc2"><a href="#rbu_vacuum_c_c_programming">3.2. RBU Vacuum C/C++ Programming</a></div>
+</div>
+</div>
+<script>
+function toggle_toc(){
+var sub = document.getElementById("toc_sub")
+var mk = document.getElementById("toc_mk")
+if( sub.style.display!="block" ){
+sub.style.display = "block";
+mk.innerHTML = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</script>
+</div>
+
+
+
+<h1 align="center" id="the_rbu_extension"><span>1. </span>The RBU Extension</h1>
+
+<p>The RBU extension is an add-on for SQLite designed for use with large 
+SQLite database files on low-power devices at the edge of a network. RBU
+may be used for two separate tasks:
+
+</p><ul>
+  <li> <b>RBU Update operations</b>. An <a href="rbu.html#rbu_updates">RBU Update</a> is a bulk update of a
+          database file that may include many insert, update and delete
+          operations on one or more tables.
+  </li><li> <b>RBU Vacuum operations</b>. An <a href="rbu.html#rbu_vacuum">RBU Vacuum</a> optimizes and rebuilds an
+          entire database file, with results similar to SQLite's native VACUUM
+          command.  
+</li></ul>
+
+<p>The acronym RBU stands for "Resumable Bulk Update".
+
+</p><p>Both of the RBU functions may be accomplished using SQLite's built-in 
+SQL commands - RBU update via a series of <a href="lang_insert.html">INSERT</a>, <a href="lang_delete.html">DELETE</a> and 
+<a href="lang_update.html">UPDATE</a> commands within a single transaction, and RBU vacuum by a single
+<a href="lang_vacuum.html">VACUUM</a> command.  The RBU module provides the following advantages over
+these simpler approaches:
+
+
+</p><ol>
+<li><b>RBU may be more efficient</b>
+
+<p>The most efficient way to apply changes to a B-Tree (the data structure
+that SQLite uses to store each table and index on disk) is to make the
+changes in key order. But if an SQL table has one or more indexes, the key
+order for each index may be different from the main table and the other
+auxiliary indexes. As a result, when executing a series of <a href="lang_insert.html">INSERT</a>,
+<a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements it is not generally possible to order the
+operations so that all b-trees are updated in key order. The RBU update
+process works around this by applying all changes to the main table in one 
+pass, then applying changes to each index in separate passes, ensuring each
+B-Tree is updated optimally. For a large database file (one that does not
+fit in the OS disk cache) this procedure can result in two orders of
+magnitude faster updates.
+
+</p><p>An RBU Vacuum operation requires less temporary disk space and writes
+less data to disk than an SQLite VACUUM. An SQLite VACUUM requires roughly
+twice the size of the final database file in temporary disk space to run.
+The total amount of data written is around three times the size of the
+final database file. By contrast, an RBU Vacuum requires roughly the size
+of the final database file in temporary disk space and writes a total of
+twice that to disk.
+
+</p><p>On the other hand, an RBU Vacuum uses more CPU than a regular SQLite
+VACUUM - in one test as much as five times as much. For this reason, an RBU
+Vacuum is often significantly slower than an SQLite VACUUM under the same
+conditions.
+
+</p></li><li><b>RBU runs in the background</b>
+
+<p>An ongoing RBU operation (either an update or a vacuum) does not
+interfere with read access to the database file.
+
+</p></li><li><b>RBU runs incrementally</b>
+
+<p>RBU operations may be suspended and then later resumed, perhaps with
+intervening power outages and/or system resets. For an RBU update, the
+original database content remains visible to all database readers until 
+the entire update has been applied - even if the update is suspended and
+then later resumed.
+
+</p></li></ol>
+
+<p>The RBU extension is not enabled by default. To enable it, compile the
+<a href="amalgamation.html">amalgamation</a> with the <a href="compile.html#enable_rbu">SQLITE_ENABLE_RBU</a> compile-time option.
+
+<a name="rbu_updates"></a>
+
+</p><h1 id="rbu_updates"><span>2. </span>RBU Updates</h1>
+<h2 id="rbu_update_limitations"><span>2.1. </span>RBU Update Limitations</h2>
+
+<p>The following limitations apply to RBU updates:
+
+</p><ul>
+<li><p>The changes must consist of <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a>, and <a href="lang_delete.html">DELETE</a>
+    operations only.  CREATE and DROP operations are not
+    supported.</p></li>
+<li><p><a href="lang_insert.html">INSERT</a> statements may not use default values.</p></li>
+<li><p><a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements must identify the target rows
+    by rowid or by non-NULL PRIMARY KEY values.</p></li>
+<li><p><a href="lang_update.html">UPDATE</a> statements may not modify PRIMARY KEY or rowid values.
+    </p></li>
+<li><p>RBU updates cannot be applied to any tables that contain a column
+       named "rbu_control".</p></li>
+<li><p>The RBU update will not fire any triggers.</p></li>
+<li><p>The RBU update will not detect or prevent foreign key or
+       CHECK constraint violations.</p></li>
+<li><p>All RBU updates use the "OR ROLLBACK" constraint handling mechanism.
+    </p></li>
+<li><p>The target database may not be in <a href="wal.html">WAL mode</a>.</p></li>
+<li><p></p><s>The target database may not contain <a href="expridx.html">indexes on expressions</a>.</s>
+    Indexes on expressions are supported beginning with SQLite 3.30.0
+    (2019-10-04).
+</li><li><p>No other writes may occur on the target database while the
+       RBU update is being applied.  A read-lock is held on the target
+       database to prevent this.</p></li>
+</ul>
+
+
+<h2 id="preparing_an_rbu_update_file"><span>2.2. </span>Preparing an RBU Update File</h2>
+
+<p>All changes to be applied by RBU are stored in a separate SQLite database
+called the "RBU database".  The database that is to be modified is called
+the "target database".
+
+</p><p>For each table in the target database that will be modified by the update,
+a corresponding table is created within the RBU database. The RBU database
+table schema is not the same as that of the target database, but is derived
+from it as <a href="rbu.html#database_tables">described below</a>.
+
+</p><p>The RBU database table contains a single row for each target database 
+row inserted, updated or deleted by the update. Populating the RBU database
+tables is described in <a href="rbu.html#database_contents">the following section</a>.
+</p>
+
+<a name="database_tables"></a>
+
+<h3 id="the_rbu_database_schema"><span>2.2.1. </span>The RBU Database Schema</h3>
+
+<p>
+For each table in the target database, the RBU database should contain a table
+named "data&lt;<i>integer</i>&gt;_&lt;<i>target-table-name</i>&gt;" where
+&lt;<i>target-table-name</i>&gt; is the name of the table in the target
+database and &lt;<i>integer</i>&gt; is any sequence of zero or more numeric
+characters (0-9). Tables within the RBU database are processed in order by 
+name (from smallest to largest according to the BINARY collation sequence),
+so the order in which target tables are updated is influenced by the selection 
+of the &lt;<i>integer</i>&gt; portion of the data_% table name. While this can
+be useful when using RBU to update 
+<a href="rbu.html#fts4_tables">certain types of virtual tables</a>, there is normally no
+reason to use anything other than an empty string in place of
+&lt;<i>integer</i>&gt;.
+
+</p><p>The data_% table must have all the same columns as the target table, plus
+one additional column named "rbu_control". The data_% table should have no
+PRIMARY KEY or UNIQUE constraints, but each column should have the same type as
+the corresponding column in the target database. The rbu_control column should
+have no type at all. For example, if the target database contains:
+
+</p><div class="codeblock"><pre>CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
+</pre></div>
+
+<p>Then the RBU database should contain:
+
+</p><div class="codeblock"><pre>CREATE TABLE data_t1(a INTEGER, b TEXT, c, rbu_control);
+</pre></div>
+
+<p>The order of the columns in the data_% table does not matter.
+
+</p><p>If the target database table is a virtual table or a table that has no
+PRIMARY KEY declaration, the data_% table must also contain a column 
+named "rbu_rowid". The rbu_rowid column is mapped to the tables <a href="lang_createtable.html#rowid">ROWID</a>.
+For example, if the target database contains either of the following:
+
+</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE x1 USING fts3(a, b);
+CREATE TABLE x1(a, b);
+</pre></div>
+
+<p>then the RBU database should contain:
+
+</p><div class="codeblock"><pre>CREATE TABLE data_x1(a, b, rbu_rowid, rbu_control);
+</pre></div>
+
+<p>Virtual tables for which the "rowid" column does 
+not function like a primary key value cannot be updated using RBU.
+
+</p><p>
+All non-hidden columns (i.e. all columns matched by "SELECT *") of the
+target table must be present in the input table. For virtual tables,
+hidden columns are optional - they are updated by RBU if present in
+the input table, or not otherwise. For example, to write to an fts4
+table with a hidden languageid column such as:
+
+</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid');
+</pre></div>
+
+<p>Either of the following input table schemas may be used:
+
+</p><div class="codeblock"><pre>CREATE TABLE data_ft1(a, b, langid, rbu_rowid, rbu_control);
+CREATE TABLE data_ft1(a, b, rbu_rowid, rbu_control);
+</pre></div>
+
+<a name="database_contents"></a>
+
+<h3 id="rbu_database_contents"><span>2.2.2. </span>RBU Database Contents</h3>
+
+<p>For each row to INSERT into the target database as part of the RBU 
+update, the corresponding data_% table should contain a single record
+with the "rbu_control" column set to contain integer value 0. The
+other columns should be set to the values that make up the new record 
+to insert. 
+
+</p><p>The "rbu_control" column may also be set to integer value 2 for 
+an INSERT. In this case, the new row silently replaces any existing row that
+has the same primary key values. This is equivalent to a DELETE followed by an
+INSERT with the same primary key values. It is not the same as an SQL REPLACE
+command, as in that case the new row may replace any conflicting rows (i.e.
+those that conflict due to UNIQUE constraints or indexes), not just those with
+conflicting primary keys.
+
+</p><p>If the target database table has an INTEGER PRIMARY KEY, it is not 
+possible to insert a NULL value into the IPK column. Attempting to 
+do so results in an SQLITE_MISMATCH error.
+
+</p><p>For each row to DELETE from the target database as part of the RBU 
+update, the corresponding data_% table should contain a single record
+with the "rbu_control" column set to contain integer value 1. The
+real primary key values of the row to delete should be stored in the
+corresponding columns of the data_% table. The values stored in the
+other columns are not used.
+
+</p><p>For each row to UPDATE from the target database as part of the RBU 
+update, the corresponding data_% table should contain a single record
+with the "rbu_control" column set to contain a value of type text.
+The real primary key values identifying the row to update should be 
+stored in the corresponding columns of the data_% table row, as should
+the new values of all columns being update. The text value in the 
+"rbu_control" column must contain the same number of characters as
+there are columns in the target database table, and must consist entirely
+of 'x' and '.' characters (or in some special cases 'd' - see below). For 
+each column that is being updated, the corresponding character is set to
+'x'. For those that remain as they are, the corresponding character of the
+rbu_control value should be set to '.'. For example, given the tables 
+above, the update statement:
+
+</p><div class="codeblock"><pre>UPDATE t1 SET c = 'usa' WHERE a = 4;
+</pre></div>
+
+<p>is represented by the data_t1 row created by:
+
+</p><div class="codeblock"><pre>INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x');
+</pre></div>
+
+<p>If RBU is used to update a large BLOB value within a target database, it
+may be more efficient to store a patch or delta that can be used to modify
+the existing BLOB instead of an entirely new value within the RBU database. 
+RBU allows deltas to be specified in two ways:
+
+</p><ul>
+  <li> In the "fossil delta" format - the format used for blob deltas by the 
+  <a href="http://fossil-scm.org">Fossil source-code management system</a>, or
+
+  </li><li> In a custom format defined by the RBU application.
+</li></ul>
+
+<p> The fossil delta format may only be used to update BLOB values. Instead
+of storing the new BLOB within the data_% table, the fossil delta is stored
+instead. And instead of specifying an 'x' as part of the rbu_control string
+for the column to be updated, an 'f' character is stored. When processing
+an 'f' update, RBU loads the original BLOB data from disk, applies the fossil
+delta to it and stores the results back into the database file. The RBU
+databases generated by <a href="rbu.html#sqldiff">sqldiff --rbu</a> make use of fossil deltas wherever
+doing so would save space in the RBU database.
+
+</p><p> To use a custom delta format, the RBU application must register a
+user-defined SQL function named "rbu_delta" before beginning to process the
+update. rbu_delta() will be invoked with two arguments - the original value
+stored in the target table column and the delta value provided as part of
+the RBU update. It should return the result of applying the delta to the
+original value. To use the custom delta function, the character of the
+rbu_control value corresponding to the target column to update must be
+set to 'd' instead of 'x'. Then, instead of updating the target table with the
+value stored in the corresponding data_% column, RBU invokes the user-defined
+SQL function "rbu_delta()" and the store in the target table column.
+
+</p><p>For example, this row:
+
+</p><div class="codeblock"><pre>INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..d');
+</pre></div>
+
+<p>causes RBU to update the target database table in a way similar to:
+
+</p><div class="codeblock"><pre>UPDATE t1 SET c = rbu_delta(c, 'usa') WHERE a = 4;
+</pre></div>
+
+<p>If the target database table is a virtual table or a table with no PRIMARY
+KEY, the rbu_control value should not include a character corresponding 
+to the rbu_rowid value. For example, this:
+
+</p><div class="codeblock"><pre>INSERT INTO data_ft1(a, b, rbu_rowid, rbu_control) 
+  VALUES(NULL, 'usa', 12, '.x');
+</pre></div>
+
+
+<p>causes a result similar to:
+
+</p><div class="codeblock"><pre>UPDATE ft1 SET b = 'usa' WHERE rowid = 12;
+</pre></div>
+
+<p>The data_% tables themselves should have no PRIMARY KEY declarations.
+However, RBU is more efficient if reading the rows in from each data_%
+table in "rowid" order is roughly the same as reading them sorted by
+the PRIMARY KEY of the corresponding target database table. In other 
+words, rows should be sorted using the destination table PRIMARY KEY 
+fields before they are inserted into the data_% tables.
+
+<a name="fts4_tables"></a>
+
+</p><h3 id="using_rbu_with_fts3_4_tables"><span>2.2.3. </span>Using RBU with FTS3/4 Tables</h3>
+
+<p>Usually, an <a href="fts3.html">FTS3 or FTS4</a> table is an example of a virtual table 
+with a rowid that works like a PRIMARY KEY. So, for the following FTS4 tables:
+
+</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft1 USING fts4(addr, text);
+CREATE VIRTUAL TABLE ft2 USING fts4;             -- implicit "content" column
+</pre></div>
+
+<p>The data_% tables may be created as follows:
+
+</p><div class="codeblock"><pre>CREATE TABLE data_ft1 USING fts4(addr, text, rbu_rowid, rbu_control);
+CREATE TABLE data_ft2 USING fts4(content, rbu_rowid, rbu_control);
+</pre></div>
+
+<p>And populated as if the target table were an ordinary SQLite table with no
+explicit PRIMARY KEY columns.
+
+</p><p><a href="fts3.html#_contentless_fts4_tables_">Contentless FTS4 tables</a> are handled similarly,
+except that any attempt to update or delete rows will cause an error when
+applying the update.
+
+</p><p><a href="fts3.html#_external_content_fts4_tables_">External content FTS4 tables</a> may also be 
+updated using RBU. In this case the user is required to configure the RBU
+database so that the same set of UPDATE, DELETE and INSERT operations are
+applied to the FTS4 index as to the underlying content table. As for all
+updates of external content FTS4 tables, the user is also required to ensure
+that any UPDATE or DELETE operations are applied to the FTS4 index before
+they are applied to the underlying content table (refer to FTS4 documentation
+for a detailed explanation). In RBU, this is done by ensuring that the name
+of the data_% table used to write to the FTS4 table sorts before the name
+of the data_% table used to update the underlying content table using the
+<a href="datatype3.html#collation">BINARY</a> collation sequence. In order to avoid duplicating data within the
+RBU database, an SQL view may be used in place of one of the data_% tables.
+For example, for the target database schema:
+
+</p><div class="codeblock"><pre>CREATE TABLE ccc(addr, text);
+CREATE VIRTUAL TABLE ccc_fts USING fts4(addr, text, content=ccc);
+</pre></div>
+
+<p>
+  The following RBU database schema may be used: 
+
+</p><div class="codeblock"><pre>CREATE TABLE data_ccc(addr, text, rbu_rowid, rbu_control);
+CREATE VIEW data0_ccc_fts AS SELECT * FROM data_ccc;
+</pre></div>
+ 
+<p>
+  The data_ccc table may then be populated as normal with the updates intended
+  for target database table ccc. The same updates will be read by RBU from
+  the data0_ccc_fts view and applied to FTS table ccc_fts. Because
+  "data0_ccc_fts" is smaller than "data_ccc", the FTS table will be updated
+  first, as required.
+
+</p><p>
+  Cases in which the underlying content table has an explicit INTEGER PRIMARY
+  KEY column are slightly more difficult, as the text values stored in the
+  rbu_control column are slightly different for the FTS index and its
+  underlying content table. For the underlying content table, a character
+  must be included in any rbu_control text values for the explicit IPK, but
+  for the FTS table itself, which has an implicit rowid, it should not. This
+  is inconvenient, but can be solved using a more complicated view, as follows:
+
+</p><div class="codeblock"><pre>-- Target database schema
+CREATE TABLE ddd(i INTEGER PRIMARY KEY, k TEXT);
+CREATE VIRTUAL TABLE ddd_fts USING fts4(k, content=ddd);
+
+-- RBU database schema
+CREATE TABLE data_ccc(i, k, rbu_control);
+CREATE VIEW data0_ccc_fts AS SELECT i AS rbu_rowid, k, CASE 
+  WHEN rbu_control IN (0,1) THEN rbu_control ELSE substr(rbu_control, 2) END
+FROM data_ccc;
+</pre></div>
+
+<p>
+  The substr() function in the SQL view above returns the text of the
+  rbu_control argument with the first character (the one corresponding to
+  column "i", which is not required by the FTS table) removed.
+
+<a name="sqldiff"></a>
+
+</p><h3 id="automatically_generating_rbu_updates_with_sqldiff"><span>2.2.4. </span>Automatically Generating RBU Updates with sqldiff</h3>
+
+<p>
+  As of SQLite <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14), 
+  the <a href="sqldiff.html">sqldiff</a> utility is able to generate
+  RBU databases representing the difference between two databases with
+  identical schemas. For example, the following command:
+
+</p><div class="codeblock"><pre>sqldiff --rbu t1.db t2.db
+</pre></div>
+
+<p>
+  Outputs an SQL script to create an RBU database which, if used to update
+  database t1.db, patches it so that its contents are identical to that of
+  database t2.db.
+
+</p><p>
+  By default, sqldiff attempts to process all non-virtual tables within
+  the two databases provided to it. If any table appears in one database
+  but not the other, or if any table has a slightly different schema in
+  one database it is an error. The "--table" option may be useful if this
+  causes a problem
+  
+</p><p>
+  Virtual tables are ignored by default by sqldiff. However, it is possible 
+  to explicitly create an RBU data_% table for a virtual table that features
+  a rowid that functions like a primary key using a command such as:
+
+</p><div class="codeblock"><pre>sqldiff --rbu --table &lt;<i>virtual-table-name</i>&gt; t1.db t2.db
+</pre></div>
+
+<p>
+  Unfortunately, even though virtual tables are ignored by default, any
+  <a href="fts3.html#*shadowtab">underlying database tables</a> that they create in order to
+  store data within the database are not, and <a href="sqldiff.html">sqldiff</a> will include add these
+  to any RBU database. For this reason, users attempting to use sqldiff to
+  create RBU updates to apply to target databases with one or more virtual
+  tables will likely have to run sqldiff using the --table option separately
+  for each table to update in the target database.
+
+</p><h2 id="rbu_update_c_c_programming"><span>2.3. </span>RBU Update C/C++ Programming</h2>
+
+<p>The RBU extension interface allows an application to apply an RBU update 
+stored in an RBU database to an existing target database.
+The procedure is as follows:
+
+</p><ol>
+<li><p>
+Open an RBU handle using the sqlite3rbu_open(T,A,S) function.
+
+</p><p>The T argument is the name of the target database file.
+The A argument is the name of the RBU database file.
+The S argument is the name of a "state database" used to store
+state information needed to resume the update after an interruption.
+The S argument can be NULL in which case the state information
+is stored in the RBU database in various tables whose names all
+begin with "rbu_".
+
+</p><p>The sqlite3rbu_open(T,A,S) function returns a pointer to
+an "sqlite3rbu" object, which is then passed into the subsequent
+interfaces.
+
+
+</p></li><li><p>
+Register any required virtual table modules with the database
+handle returned by sqlite3rbu_db(X) (where argument X is the sqlite3rbu
+pointer returned from sqlite3rbu_open()).  Also, if required, register
+the rbu_delta() SQL function using 
+<a href="c3ref/create_function.html">sqlite3_create_function_v2()</a>.
+
+</p></li><li><p>
+Invoke the sqlite3rbu_step(X) function one or more times on
+the sqlite3rbu object pointer X. Each call to sqlite3rbu_step() 
+performs a single b-tree operation, so thousands of calls may be 
+required to apply a complete update.  The sqlite3rbu_step() 
+interface will return SQLITE_DONE when the update has been
+completely applied.
+
+</p></li><li><p>
+Call sqlite3rbu_close(X) to destroy the sqlite3rbu object pointer.
+If sqlite3rbu_step(X) has been called enough times to completely
+apply the update to the target database, then the RBU database
+is marked as fully applied. Otherwise, the state of the RBU 
+update application is saved in the state database (or in the RBU
+database if the name of the state database file in sqlite3rbu_open()
+is NULL) for later resumption of the update.
+</p></li></ol>
+
+<p>If an update is only partially applied to the target database by the
+time sqlite3rbu_close() is called, state information is saved 
+within the state database if it exists, or otherwise in the RBU database. 
+This allows subsequent processes to automatically
+resume the RBU update from where it left off.
+If state information is stored in the RBU database, it can be removed
+by dropping all tables whose names begin with "rbu_".
+
+</p><p>For more details, refer to the comments in 
+<a href="http://sqlite.org/src/doc/trunk/ext/rbu/sqlite3rbu.h">header file
+sqlite3rbu.h</a>.
+
+<a name="rbu_vacuum"></a>
+
+</p><h1 id="rbu_vacuum"><span>3. </span>RBU Vacuum</h1>
+
+<h2 id="rbu_vacuum_limitations"><span>3.1. </span>RBU Vacuum Limitations</h2>
+
+<p>When compared with SQLite's built-in VACUUM command, RBU Vacuum has the
+following limitations:
+
+</p><ul>
+  <li><p>It may not be used on a database that contains <a href="expridx.html">indexes on expressions</a>.
+  </p></li><li><p>The database being vacuumed may not be in <a href="wal.html">WAL mode</a>.
+</p></li></ul>
+
+<h2 id="rbu_vacuum_c_c_programming"><span>3.2. </span>RBU Vacuum C/C++ Programming</h2>
+
+<p> This section provides an overview of and example code demonstrating the
+    integration of RBU Vacuum into an application program.  For full details,
+    refer to the comments in 
+    <a href="http://sqlite.org/src/doc/trunk/ext/rbu/sqlite3rbu.h">header file
+    sqlite3rbu.h</a>.
+
+</p><p> RBU Vacuum applications all implement some variation of the following
+procedure:
+
+</p><ol>
+  <li><p> An RBU handle is created by calling sqlite3rbu_vacuum(T, S).
+
+      </p><p> Argument T is the name of the database file to vacuum. Argument S is
+      the name of a database in which the RBU module will save its state if the
+      vacuum operation is suspended.
+
+      </p><p> If state database S does not exist when sqlite3rbu_vacuum() is
+      invoked, it is automatically created and populated with the single table
+      used to store the state of an RBU vacuum - "rbu_state". If an ongoing RBU
+      vacuum is suspended, this table is populated with state data. The next
+      time sqlite3rbu_vacuum() is called with the same S parameter, it detects
+      this data and attempts to resume the suspended vacuum operation. When
+      an RBU vacuum operation is completed or encounters an error, RBU 
+      automatically deletes the contents of the rbu_state table. In this case,
+      the next call to sqlite3rbu_vacuum() starts an entirely new vacuum
+      operation from scratch.
+
+      </p><p> It is a good idea to establish a convention for determining the RBU
+      vacuum state database name based on the target database name. The
+      example code below uses "&lt;target&gt;-vacuum", where &lt;target&gt; is
+      the name of the database being vacuumed.
+
+  </p></li><li><p> Any custom collation sequences used by indexes within the database
+      being vacuumed are registered with both of the database handles returned
+      by the sqlite3rbu_db() function.
+
+  </p></li><li><p> Function sqlite3rbu_step() is called on the RBU handle until either
+      the RBU vacuum is finished, an error occurs or the application wishes to
+      suspend the RBU vacuum.
+
+      </p><p> Each call to sqlite3rbu_step() does a small amount of work towards
+      completing the vacuum operation. Depending on the size of the database, a
+      single vacuum may require thousands of calls to sqlite3rbu_step().
+      sqlite3rbu_step() returns SQLITE_DONE if the vacuum operation has
+      finished, SQLITE_OK if the vacuum operation has not finished but no error
+      has occurred, and an SQLite error code if an error is encountered. If
+      an error does occur, all subsequent calls to sqlite3rbu_step() immediately
+      return the same error code.
+
+  </p></li><li><p> Finally, sqlite3rbu_close() is called to close the RBU handle. If the
+      application stopped calling sqlite3rbu_step() before either the vacuum
+      finished or an error occurred, the state of the vacuum is saved in the
+      state database so that it may be resumed later on.
+
+      </p><p> Like sqlite3rbu_step(), if the vacuum operation has finished,
+      sqlite3rbu_close() returns SQLITE_DONE. If the vacuum has not finished
+      but no error has occurred, SQLITE_OK is returned. Or, if an error has
+      occurred, an SQLite error code is returned. If an error occurred as part
+      of a prior call to sqlite3rbu_step(), sqlite3rbu_close() returns the
+      same error code.
+</p></li></ol>
+
+<p>The following example code illustrates the techniques described above.  
+
+</p><div class="codeblock"><pre><i>/*</i>
+<i>** Either start a new RBU vacuum or resume a suspended RBU vacuum on </i>
+<i>** database zTarget. Return when either an error occurs, the RBU </i>
+<i>** vacuum is finished or when the application signals an interrupt</i>
+<i>** (code not shown).</i>
+<i>**</i>
+<i>** If the RBU vacuum is completed successfully, return SQLITE_DONE.</i>
+<i>** If an error occurs, return SQLite error code. Or, if the application</i>
+<i>** signals an interrupt, suspend the RBU vacuum operation so that it</i>
+<i>** may be resumed by a subsequent call to this function and return</i>
+<i>** SQLITE_OK.</i>
+<i>**</i>
+<i>** This function uses the database named "&lt;zTarget&gt;-vacuum" for</i>
+<i>** the state database, where &lt;zTarget&gt; is the name of the database </i>
+<i>** being vacuumed.</i>
+<i>*/</i>
+int do_rbu_vacuum(const char *zTarget){
+  int rc;
+  char *zState;                   <i>/* Name of state database */</i>
+  sqlite3rbu *pRbu;               <i>/* RBU vacuum handle */</i>
+
+  zState = sqlite3_mprintf("%s-vacuum", zTarget);
+  if( zState==0 ) return SQLITE_NOMEM;
+  pRbu = sqlite3rbu_vacuum(zTarget, zState);
+  sqlite3_free(zState);
+
+  if( pRbu ){
+    sqlite3 *dbTarget = sqlite3rbu_db(pRbu, 0);
+    sqlite3 *dbState = sqlite3rbu_db(pRbu, 1);
+
+    <i>/* Any custom collation sequences used by the target database must</i>
+    <i>** be registered with both database handles here.  */</i>
+
+    while( sqlite3rbu_step(pRbu)==SQLITE_OK ){
+      if( <i>&lt;application has signaled interrupt&gt;</i> ) break;
+    }
+  }
+  rc = sqlite3rbu_close(pRbu);
+  return rc;
+}
+</pre></div>
+<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/rbu.in?m=0f520f8581">2022-01-08 05:02:57</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
commit	63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree	01c7571c7c762ceee70638549a99834fdd7c411b /www/rbu.html
parent	Initial commit. (diff)
download	sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip