diff options
Diffstat (limited to 'www/rbu.html')
| -rw-r--r-- | www/rbu.html | 765 |
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">►</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 = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</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<<i>integer</i>>_<<i>target-table-name</i>>" where +<<i>target-table-name</i>> is the name of the table in the target +database and <<i>integer</i>> 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 <<i>integer</i>> 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 +<<i>integer</i>>. + +</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 <<i>virtual-table-name</i>> 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 "<target>-vacuum", where <target> 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 "<zTarget>-vacuum" for</i> +<i>** the state database, where <zTarget> 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><application has signaled interrupt></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> + |