| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>The RBU Extension</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
The RBU Extension |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#the_rbu_extension">1. The RBU Extension</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#rbu_updates">2. RBU Updates</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#rbu_update_limitations">2.1. RBU Update Limitations</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#preparing_an_rbu_update_file">2.2. Preparing an RBU Update File</a></div> |
| 129 |
<div class="fancy-toc3"><a href="#the_rbu_database_schema">2.2.1. The RBU Database Schema</a></div> |
| 130 |
<div class="fancy-toc3"><a href="#rbu_database_contents">2.2.2. RBU Database Contents</a></div> |
| 131 |
<div class="fancy-toc3"><a href="#using_rbu_with_fts3_4_tables">2.2.3. Using RBU with FTS3/4 Tables</a></div> |
| 132 |
<div class="fancy-toc3"><a href="#automatically_generating_rbu_updates_with_sqldiff">2.2.4. Automatically Generating RBU Updates with sqldiff</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#rbu_update_c_c_programming">2.3. RBU Update C/C++ Programming</a></div> |
| 134 |
<div class="fancy-toc1"><a href="#rbu_vacuum">3. RBU Vacuum</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#rbu_vacuum_limitations">3.1. RBU Vacuum Limitations</a></div> |
| 136 |
<div class="fancy-toc2"><a href="#rbu_vacuum_c_c_programming">3.2. RBU Vacuum C/C++ Programming</a></div> |
| 137 |
</div> |
| 138 |
</details> |
| 139 |
</div> |
| 140 |
|
| 141 |
|
| 142 |
|
| 143 |
<h1 align="center" id="the_rbu_extension"><span>1. </span>The RBU Extension</h1> |
| 144 |
|
| 145 |
<p>The RBU extension is an add-on for SQLite designed for use with large |
| 146 |
SQLite database files on low-power devices at the edge of a network. RBU |
| 147 |
may be used for two separate tasks: |
| 148 |
|
| 149 |
</p><ul> |
| 150 |
<li> <b>RBU Update operations</b>. An <a href="rbu.html#rbu_updates">RBU Update</a> is a bulk update of a |
| 151 |
database file that may include many insert, update and delete |
| 152 |
operations on one or more tables. |
| 153 |
</li><li> <b>RBU Vacuum operations</b>. An <a href="rbu.html#rbu_vacuum">RBU Vacuum</a> optimizes and rebuilds an |
| 154 |
entire database file, with results similar to SQLite's native VACUUM |
| 155 |
command. |
| 156 |
</li></ul> |
| 157 |
|
| 158 |
<p>The acronym RBU stands for "Resumable Bulk Update". |
| 159 |
|
| 160 |
</p><p>Both of the RBU functions may be accomplished using SQLite's built-in |
| 161 |
SQL commands - RBU update via a series of <a href="lang_insert.html">INSERT</a>, <a href="lang_delete.html">DELETE</a> and |
| 162 |
<a href="lang_update.html">UPDATE</a> commands within a single transaction, and RBU vacuum by a single |
| 163 |
<a href="lang_vacuum.html">VACUUM</a> command. The RBU module provides the following advantages over |
| 164 |
these simpler approaches: |
| 165 |
|
| 166 |
|
| 167 |
</p><ol> |
| 168 |
<li><b>RBU may be more efficient</b> |
| 169 |
|
| 170 |
<p>The most efficient way to apply changes to a B-Tree (the data structure |
| 171 |
that SQLite uses to store each table and index on disk) is to make the |
| 172 |
changes in key order. But if an SQL table has one or more indexes, the key |
| 173 |
order for each index may be different from the main table and the other |
| 174 |
auxiliary indexes. As a result, when executing a series of <a href="lang_insert.html">INSERT</a>, |
| 175 |
<a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements it is not generally possible to order the |
| 176 |
operations so that all b-trees are updated in key order. The RBU update |
| 177 |
process works around this by applying all changes to the main table in one |
| 178 |
pass, then applying changes to each index in separate passes, ensuring each |
| 179 |
B-Tree is updated optimally. For a large database file (one that does not |
| 180 |
fit in the OS disk cache) this procedure can result in two orders of |
| 181 |
magnitude faster updates. |
| 182 |
|
| 183 |
</p><p>An RBU Vacuum operation requires less temporary disk space and writes |
| 184 |
less data to disk than an SQLite VACUUM. An SQLite VACUUM requires roughly |
| 185 |
twice the size of the final database file in temporary disk space to run. |
| 186 |
The total amount of data written is around three times the size of the |
| 187 |
final database file. By contrast, an RBU Vacuum requires roughly the size |
| 188 |
of the final database file in temporary disk space and writes a total of |
| 189 |
twice that to disk. |
| 190 |
|
| 191 |
</p><p>On the other hand, an RBU Vacuum uses more CPU than a regular SQLite |
| 192 |
VACUUM - in one test as much as five times as much. For this reason, an RBU |
| 193 |
Vacuum is often significantly slower than an SQLite VACUUM under the same |
| 194 |
conditions. |
| 195 |
|
| 196 |
</p></li><li><b>RBU runs in the background</b> |
| 197 |
|
| 198 |
<p>An ongoing RBU operation (either an update or a vacuum) does not |
| 199 |
interfere with read access to the database file. |
| 200 |
|
| 201 |
</p></li><li><b>RBU runs incrementally</b> |
| 202 |
|
| 203 |
<p>RBU operations may be suspended and then later resumed, perhaps with |
| 204 |
intervening power outages and/or system resets. For an RBU update, the |
| 205 |
original database content remains visible to all database readers until |
| 206 |
the entire update has been applied - even if the update is suspended and |
| 207 |
then later resumed. |
| 208 |
|
| 209 |
</p></li></ol> |
| 210 |
|
| 211 |
<p>The RBU extension is not enabled by default. To enable it, compile the |
| 212 |
<a href="amalgamation.html">amalgamation</a> with the <a href="compile.html#enable_rbu">SQLITE_ENABLE_RBU</a> compile-time option. |
| 213 |
|
| 214 |
<a name="rbu_updates"></a> |
| 215 |
|
| 216 |
</p><h1 id="rbu_updates"><span>2. </span>RBU Updates</h1> |
| 217 |
<h2 id="rbu_update_limitations"><span>2.1. </span>RBU Update Limitations</h2> |
| 218 |
|
| 219 |
<p>The following limitations apply to RBU updates: |
| 220 |
|
| 221 |
</p><ul> |
| 222 |
<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> |
| 223 |
operations only. CREATE and DROP operations are not |
| 224 |
supported.</p></li> |
| 225 |
<li><p><a href="lang_insert.html">INSERT</a> statements may not use default values.</p></li> |
| 226 |
<li><p><a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements must identify the target rows |
| 227 |
by rowid or by non-NULL PRIMARY KEY values.</p></li> |
| 228 |
<li><p><a href="lang_update.html">UPDATE</a> statements may not modify PRIMARY KEY or rowid values. |
| 229 |
</p></li> |
| 230 |
<li><p>RBU updates cannot be applied to any tables that contain a column |
| 231 |
named "rbu_control".</p></li> |
| 232 |
<li><p>The RBU update will not fire any triggers.</p></li> |
| 233 |
<li><p>The RBU update will not detect or prevent foreign key or |
| 234 |
CHECK constraint violations.</p></li> |
| 235 |
<li><p>All RBU updates use the "OR ROLLBACK" constraint handling mechanism. |
| 236 |
</p></li> |
| 237 |
<li><p>The target database may not be in <a href="wal.html">WAL mode</a>.</p></li> |
| 238 |
<li><p></p><s>The target database may not contain <a href="expridx.html">indexes on expressions</a>.</s> |
| 239 |
Indexes on expressions are supported beginning with SQLite 3.30.0 |
| 240 |
(2019-10-04). |
| 241 |
</li><li><p>No other writes may occur on the target database while the |
| 242 |
RBU update is being applied. A read-lock is held on the target |
| 243 |
database to prevent this.</p></li> |
| 244 |
</ul> |
| 245 |
|
| 246 |
|
| 247 |
<h2 id="preparing_an_rbu_update_file"><span>2.2. </span>Preparing an RBU Update File</h2> |
| 248 |
|
| 249 |
<p>All changes to be applied by RBU are stored in a separate SQLite database |
| 250 |
called the "RBU database". The database that is to be modified is called |
| 251 |
the "target database". |
| 252 |
|
| 253 |
</p><p>For each table in the target database that will be modified by the update, |
| 254 |
a corresponding table is created within the RBU database. The RBU database |
| 255 |
table schema is not the same as that of the target database, but is derived |
| 256 |
from it as <a href="rbu.html#database_tables">described below</a>. |
| 257 |
|
| 258 |
</p><p>The RBU database table contains a single row for each target database |
| 259 |
row inserted, updated or deleted by the update. Populating the RBU database |
| 260 |
tables is described in <a href="rbu.html#database_contents">the following section</a>. |
| 261 |
</p> |
| 262 |
|
| 263 |
<a name="database_tables"></a> |
| 264 |
|
| 265 |
<h3 id="the_rbu_database_schema"><span>2.2.1. </span>The RBU Database Schema</h3> |
| 266 |
|
| 267 |
<p> |
| 268 |
For each table in the target database, the RBU database should contain a table |
| 269 |
named "data<<i>integer</i>>_<<i>target-table-name</i>>" where |
| 270 |
<<i>target-table-name</i>> is the name of the table in the target |
| 271 |
database and <<i>integer</i>> is any sequence of zero or more numeric |
| 272 |
characters (0-9). Tables within the RBU database are processed in order by |
| 273 |
name (from smallest to largest according to the BINARY collation sequence), |
| 274 |
so the order in which target tables are updated is influenced by the selection |
| 275 |
of the <<i>integer</i>> portion of the data_% table name. While this can |
| 276 |
be useful when using RBU to update |
| 277 |
<a href="rbu.html#fts4_tables">certain types of virtual tables</a>, there is normally no |
| 278 |
reason to use anything other than an empty string in place of |
| 279 |
<<i>integer</i>>. |
| 280 |
|
| 281 |
</p><p>The data_% table must have all the same columns as the target table, plus |
| 282 |
one additional column named "rbu_control". The data_% table should have no |
| 283 |
PRIMARY KEY or UNIQUE constraints, but each column should have the same type as |
| 284 |
the corresponding column in the target database. The rbu_control column should |
| 285 |
have no type at all. For example, if the target database contains: |
| 286 |
|
| 287 |
</p><div class="codeblock"><pre>CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE); |
| 288 |
</pre></div> |
| 289 |
|
| 290 |
<p>Then the RBU database should contain: |
| 291 |
|
| 292 |
</p><div class="codeblock"><pre>CREATE TABLE data_t1(a INTEGER, b TEXT, c, rbu_control); |
| 293 |
</pre></div> |
| 294 |
|
| 295 |
<p>The order of the columns in the data_% table does not matter. |
| 296 |
|
| 297 |
</p><p>If the target database table is a virtual table or a table that has no |
| 298 |
PRIMARY KEY declaration, the data_% table must also contain a column |
| 299 |
named "rbu_rowid". The rbu_rowid column is mapped to the tables <a href="lang_createtable.html#rowid">ROWID</a>. |
| 300 |
For example, if the target database contains either of the following: |
| 301 |
|
| 302 |
</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE x1 USING fts3(a, b); |
| 303 |
CREATE TABLE x1(a, b); |
| 304 |
</pre></div> |
| 305 |
|
| 306 |
<p>then the RBU database should contain: |
| 307 |
|
| 308 |
</p><div class="codeblock"><pre>CREATE TABLE data_x1(a, b, rbu_rowid, rbu_control); |
| 309 |
</pre></div> |
| 310 |
|
| 311 |
<p>Virtual tables for which the "rowid" column does |
| 312 |
not function like a primary key value cannot be updated using RBU. |
| 313 |
|
| 314 |
</p><p> |
| 315 |
All non-hidden columns (i.e. all columns matched by "SELECT *") of the |
| 316 |
target table must be present in the input table. For virtual tables, |
| 317 |
hidden columns are optional - they are updated by RBU if present in |
| 318 |
the input table, or not otherwise. For example, to write to an fts4 |
| 319 |
table with a hidden languageid column such as: |
| 320 |
|
| 321 |
</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid'); |
| 322 |
</pre></div> |
| 323 |
|
| 324 |
<p>Either of the following input table schemas may be used: |
| 325 |
|
| 326 |
</p><div class="codeblock"><pre>CREATE TABLE data_ft1(a, b, langid, rbu_rowid, rbu_control); |
| 327 |
CREATE TABLE data_ft1(a, b, rbu_rowid, rbu_control); |
| 328 |
</pre></div> |
| 329 |
|
| 330 |
<a name="database_contents"></a> |
| 331 |
|
| 332 |
<h3 id="rbu_database_contents"><span>2.2.2. </span>RBU Database Contents</h3> |
| 333 |
|
| 334 |
<p>For each row to INSERT into the target database as part of the RBU |
| 335 |
update, the corresponding data_% table should contain a single record |
| 336 |
with the "rbu_control" column set to contain integer value 0. The |
| 337 |
other columns should be set to the values that make up the new record |
| 338 |
to insert. |
| 339 |
|
| 340 |
</p><p>The "rbu_control" column may also be set to integer value 2 for |
| 341 |
an INSERT. In this case, the new row silently replaces any existing row that |
| 342 |
has the same primary key values. This is equivalent to a DELETE followed by an |
| 343 |
INSERT with the same primary key values. It is not the same as an SQL REPLACE |
| 344 |
command, as in that case the new row may replace any conflicting rows (i.e. |
| 345 |
those that conflict due to UNIQUE constraints or indexes), not just those with |
| 346 |
conflicting primary keys. |
| 347 |
|
| 348 |
</p><p>If the target database table has an INTEGER PRIMARY KEY, it is not |
| 349 |
possible to insert a NULL value into the IPK column. Attempting to |
| 350 |
do so results in an SQLITE_MISMATCH error. |
| 351 |
|
| 352 |
</p><p>For each row to DELETE from the target database as part of the RBU |
| 353 |
update, the corresponding data_% table should contain a single record |
| 354 |
with the "rbu_control" column set to contain integer value 1. The |
| 355 |
real primary key values of the row to delete should be stored in the |
| 356 |
corresponding columns of the data_% table. The values stored in the |
| 357 |
other columns are not used. |
| 358 |
|
| 359 |
</p><p>For each row to UPDATE from the target database as part of the RBU |
| 360 |
update, the corresponding data_% table should contain a single record |
| 361 |
with the "rbu_control" column set to contain a value of type text. |
| 362 |
The real primary key values identifying the row to update should be |
| 363 |
stored in the corresponding columns of the data_% table row, as should |
| 364 |
the new values of all columns being updated. The text value in the |
| 365 |
"rbu_control" column must contain the same number of characters as |
| 366 |
there are columns in the target database table, and must consist entirely |
| 367 |
of 'x' and '.' characters (or in some special cases 'd' - see below). For |
| 368 |
each column that is being updated, the corresponding character is set to |
| 369 |
'x'. For those that remain as they are, the corresponding character of the |
| 370 |
rbu_control value should be set to '.'. For example, given the tables |
| 371 |
above, the update statement: |
| 372 |
|
| 373 |
</p><div class="codeblock"><pre>UPDATE t1 SET c = 'usa' WHERE a = 4; |
| 374 |
</pre></div> |
| 375 |
|
| 376 |
<p>is represented by the data_t1 row created by: |
| 377 |
|
| 378 |
</p><div class="codeblock"><pre>INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x'); |
| 379 |
</pre></div> |
| 380 |
|
| 381 |
<p>If RBU is used to update a large BLOB value within a target database, it |
| 382 |
may be more efficient to store a patch or delta that can be used to modify |
| 383 |
the existing BLOB instead of an entirely new value within the RBU database. |
| 384 |
RBU allows deltas to be specified in two ways: |
| 385 |
|
| 386 |
</p><ul> |
| 387 |
<li> In the "fossil delta" format - the format used for blob deltas by the |
| 388 |
<a href="http://fossil-scm.org">Fossil source-code management system</a>, or |
| 389 |
|
| 390 |
</li><li> In a custom format defined by the RBU application. |
| 391 |
</li></ul> |
| 392 |
|
| 393 |
<p> The fossil delta format may only be used to update BLOB values. Instead |
| 394 |
of storing the new BLOB within the data_% table, the fossil delta is stored |
| 395 |
instead. And instead of specifying an 'x' as part of the rbu_control string |
| 396 |
for the column to be updated, an 'f' character is stored. When processing |
| 397 |
an 'f' update, RBU loads the original BLOB data from disk, applies the fossil |
| 398 |
delta to it and stores the results back into the database file. The RBU |
| 399 |
databases generated by <a href="rbu.html#sqldiff">sqldiff --rbu</a> make use of fossil deltas wherever |
| 400 |
doing so would save space in the RBU database. |
| 401 |
|
| 402 |
</p><p> To use a custom delta format, the RBU application must register a |
| 403 |
user-defined SQL function named "rbu_delta" before beginning to process the |
| 404 |
update. rbu_delta() will be invoked with two arguments - the original value |
| 405 |
stored in the target table column and the delta value provided as part of |
| 406 |
the RBU update. It should return the result of applying the delta to the |
| 407 |
original value. To use the custom delta function, the character of the |
| 408 |
rbu_control value corresponding to the target column to update must be |
| 409 |
set to 'd' instead of 'x'. Then, instead of updating the target table with the |
| 410 |
value stored in the corresponding data_% column, RBU invokes the user-defined |
| 411 |
SQL function "rbu_delta()" and the store in the target table column. |
| 412 |
|
| 413 |
</p><p>For example, this row: |
| 414 |
|
| 415 |
</p><div class="codeblock"><pre>INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..d'); |
| 416 |
</pre></div> |
| 417 |
|
| 418 |
<p>causes RBU to update the target database table in a way similar to: |
| 419 |
|
| 420 |
</p><div class="codeblock"><pre>UPDATE t1 SET c = rbu_delta(c, 'usa') WHERE a = 4; |
| 421 |
</pre></div> |
| 422 |
|
| 423 |
<p>If the target database table is a virtual table or a table with no PRIMARY |
| 424 |
KEY, the rbu_control value should not include a character corresponding |
| 425 |
to the rbu_rowid value. For example, this: |
| 426 |
|
| 427 |
</p><div class="codeblock"><pre>INSERT INTO data_ft1(a, b, rbu_rowid, rbu_control) |
| 428 |
VALUES(NULL, 'usa', 12, '.x'); |
| 429 |
</pre></div> |
| 430 |
|
| 431 |
|
| 432 |
<p>causes a result similar to: |
| 433 |
|
| 434 |
</p><div class="codeblock"><pre>UPDATE ft1 SET b = 'usa' WHERE rowid = 12; |
| 435 |
</pre></div> |
| 436 |
|
| 437 |
<p>The data_% tables themselves should have no PRIMARY KEY declarations. |
| 438 |
However, RBU is more efficient if reading the rows in from each data_% |
| 439 |
table in "rowid" order is roughly the same as reading them sorted by |
| 440 |
the PRIMARY KEY of the corresponding target database table. In other |
| 441 |
words, rows should be sorted using the destination table PRIMARY KEY |
| 442 |
fields before they are inserted into the data_% tables. |
| 443 |
|
| 444 |
<a name="fts4_tables"></a> |
| 445 |
|
| 446 |
</p><h3 id="using_rbu_with_fts3_4_tables"><span>2.2.3. </span>Using RBU with FTS3/4 Tables</h3> |
| 447 |
|
| 448 |
<p>Usually, an <a href="fts3.html">FTS3 or FTS4</a> table is an example of a virtual table |
| 449 |
with a rowid that works like a PRIMARY KEY. So, for the following FTS4 tables: |
| 450 |
|
| 451 |
</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft1 USING fts4(addr, text); |
| 452 |
CREATE VIRTUAL TABLE ft2 USING fts4; -- implicit "content" column |
| 453 |
</pre></div> |
| 454 |
|
| 455 |
<p>The data_% tables may be created as follows: |
| 456 |
|
| 457 |
</p><div class="codeblock"><pre>CREATE TABLE data_ft1 USING fts4(addr, text, rbu_rowid, rbu_control); |
| 458 |
CREATE TABLE data_ft2 USING fts4(content, rbu_rowid, rbu_control); |
| 459 |
</pre></div> |
| 460 |
|
| 461 |
<p>And populated as if the target table were an ordinary SQLite table with no |
| 462 |
explicit PRIMARY KEY columns. |
| 463 |
|
| 464 |
</p><p><a href="fts3.html#_contentless_fts4_tables_">Contentless FTS4 tables</a> are handled similarly, |
| 465 |
except that any attempt to update or delete rows will cause an error when |
| 466 |
applying the update. |
| 467 |
|
| 468 |
</p><p><a href="fts3.html#_external_content_fts4_tables_">External content FTS4 tables</a> may also be |
| 469 |
updated using RBU. In this case the user is required to configure the RBU |
| 470 |
database so that the same set of UPDATE, DELETE and INSERT operations are |
| 471 |
applied to the FTS4 index as to the underlying content table. As for all |
| 472 |
updates of external content FTS4 tables, the user is also required to ensure |
| 473 |
that any UPDATE or DELETE operations are applied to the FTS4 index before |
| 474 |
they are applied to the underlying content table (refer to FTS4 documentation |
| 475 |
for a detailed explanation). In RBU, this is done by ensuring that the name |
| 476 |
of the data_% table used to write to the FTS4 table sorts before the name |
| 477 |
of the data_% table used to update the underlying content table using the |
| 478 |
<a href="datatype3.html#collation">BINARY</a> collation sequence. In order to avoid duplicating data within the |
| 479 |
RBU database, an SQL view may be used in place of one of the data_% tables. |
| 480 |
For example, for the target database schema: |
| 481 |
|
| 482 |
</p><div class="codeblock"><pre>CREATE TABLE ccc(addr, text); |
| 483 |
CREATE VIRTUAL TABLE ccc_fts USING fts4(addr, text, content=ccc); |
| 484 |
</pre></div> |
| 485 |
|
| 486 |
<p> |
| 487 |
The following RBU database schema may be used: |
| 488 |
|
| 489 |
</p><div class="codeblock"><pre>CREATE TABLE data_ccc(addr, text, rbu_rowid, rbu_control); |
| 490 |
CREATE VIEW data0_ccc_fts AS SELECT * FROM data_ccc; |
| 491 |
</pre></div> |
| 492 |
|
| 493 |
<p> |
| 494 |
The data_ccc table may then be populated as normal with the updates intended |
| 495 |
for target database table ccc. The same updates will be read by RBU from |
| 496 |
the data0_ccc_fts view and applied to FTS table ccc_fts. Because |
| 497 |
"data0_ccc_fts" is smaller than "data_ccc", the FTS table will be updated |
| 498 |
first, as required. |
| 499 |
|
| 500 |
</p><p> |
| 501 |
Cases in which the underlying content table has an explicit INTEGER PRIMARY |
| 502 |
KEY column are slightly more difficult, as the text values stored in the |
| 503 |
rbu_control column are slightly different for the FTS index and its |
| 504 |
underlying content table. For the underlying content table, a character |
| 505 |
must be included in any rbu_control text values for the explicit IPK, but |
| 506 |
for the FTS table itself, which has an implicit rowid, it should not. This |
| 507 |
is inconvenient, but can be solved using a more complicated view, as follows: |
| 508 |
|
| 509 |
</p><div class="codeblock"><pre>-- Target database schema |
| 510 |
CREATE TABLE ddd(i INTEGER PRIMARY KEY, k TEXT); |
| 511 |
CREATE VIRTUAL TABLE ddd_fts USING fts4(k, content=ddd); |
| 512 |
|
| 513 |
-- RBU database schema |
| 514 |
CREATE TABLE data_ccc(i, k, rbu_control); |
| 515 |
CREATE VIEW data0_ccc_fts AS SELECT i AS rbu_rowid, k, CASE |
| 516 |
WHEN rbu_control IN (0,1) THEN rbu_control ELSE substr(rbu_control, 2) END |
| 517 |
FROM data_ccc; |
| 518 |
</pre></div> |
| 519 |
|
| 520 |
<p> |
| 521 |
The substr() function in the SQL view above returns the text of the |
| 522 |
rbu_control argument with the first character (the one corresponding to |
| 523 |
column "i", which is not required by the FTS table) removed. |
| 524 |
|
| 525 |
<a name="sqldiff"></a> |
| 526 |
|
| 527 |
</p><h3 id="automatically_generating_rbu_updates_with_sqldiff"><span>2.2.4. </span>Automatically Generating RBU Updates with sqldiff</h3> |
| 528 |
|
| 529 |
<p> |
| 530 |
As of SQLite <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14), |
| 531 |
the <a href="sqldiff.html">sqldiff</a> utility is able to generate |
| 532 |
RBU databases representing the difference between two databases with |
| 533 |
identical schemas. For example, the following command: |
| 534 |
|
| 535 |
</p><div class="codeblock"><pre>sqldiff --rbu t1.db t2.db |
| 536 |
</pre></div> |
| 537 |
|
| 538 |
<p> |
| 539 |
Outputs an SQL script to create an RBU database which, if used to update |
| 540 |
database t1.db, patches it so that its contents are identical to that of |
| 541 |
database t2.db. |
| 542 |
|
| 543 |
</p><p> |
| 544 |
By default, sqldiff attempts to process all non-virtual tables within |
| 545 |
the two databases provided to it. If any table appears in one database |
| 546 |
but not the other, or if any table has a slightly different schema in |
| 547 |
one database it is an error. The "--table" option may be useful if this |
| 548 |
causes a problem |
| 549 |
|
| 550 |
</p><p> |
| 551 |
Virtual tables are ignored by default by sqldiff. However, it is possible |
| 552 |
to explicitly create an RBU data_% table for a virtual table that features |
| 553 |
a rowid that functions like a primary key using a command such as: |
| 554 |
|
| 555 |
</p><div class="codeblock"><pre>sqldiff --rbu --table <<i>virtual-table-name</i>> t1.db t2.db |
| 556 |
</pre></div> |
| 557 |
|
| 558 |
<p> |
| 559 |
Unfortunately, even though virtual tables are ignored by default, any |
| 560 |
<a href="fts3.html#*shadowtab">underlying database tables</a> that they create in order to |
| 561 |
store data within the database are not, and <a href="sqldiff.html">sqldiff</a> will add these |
| 562 |
to any RBU database. For this reason, users attempting to use sqldiff to |
| 563 |
create RBU updates to apply to target databases with one or more virtual |
| 564 |
tables will likely have to run sqldiff using the --table option separately |
| 565 |
for each table to update in the target database. |
| 566 |
|
| 567 |
</p><h2 id="rbu_update_c_c_programming"><span>2.3. </span>RBU Update C/C++ Programming</h2> |
| 568 |
|
| 569 |
<p>The RBU extension interface allows an application to apply an RBU update |
| 570 |
stored in an RBU database to an existing target database. |
| 571 |
The procedure is as follows: |
| 572 |
|
| 573 |
</p><ol> |
| 574 |
<li><p> |
| 575 |
Open an RBU handle using the sqlite3rbu_open(T,A,S) function. |
| 576 |
|
| 577 |
</p><p>The T argument is the name of the target database file. |
| 578 |
The A argument is the name of the RBU database file. |
| 579 |
The S argument is the name of a "state database" used to store |
| 580 |
state information needed to resume the update after an interruption. |
| 581 |
The S argument can be NULL in which case the state information |
| 582 |
is stored in the RBU database in various tables whose names all |
| 583 |
begin with "rbu_". |
| 584 |
|
| 585 |
</p><p>The sqlite3rbu_open(T,A,S) function returns a pointer to |
| 586 |
an "sqlite3rbu" object, which is then passed into the subsequent |
| 587 |
interfaces. |
| 588 |
|
| 589 |
|
| 590 |
</p></li><li><p> |
| 591 |
Register any required virtual table modules with the database |
| 592 |
handle returned by sqlite3rbu_db(X) (where argument X is the sqlite3rbu |
| 593 |
pointer returned from sqlite3rbu_open()). Also, if required, register |
| 594 |
the rbu_delta() SQL function using |
| 595 |
<a href="c3ref/create_function.html">sqlite3_create_function_v2()</a>. |
| 596 |
|
| 597 |
</p></li><li><p> |
| 598 |
Invoke the sqlite3rbu_step(X) function one or more times on |
| 599 |
the sqlite3rbu object pointer X. Each call to sqlite3rbu_step() |
| 600 |
performs a single b-tree operation, so thousands of calls may be |
| 601 |
required to apply a complete update. The sqlite3rbu_step() |
| 602 |
interface will return SQLITE_DONE when the update has been |
| 603 |
completely applied. |
| 604 |
|
| 605 |
</p></li><li><p> |
| 606 |
Call sqlite3rbu_close(X) to destroy the sqlite3rbu object pointer. |
| 607 |
If sqlite3rbu_step(X) has been called enough times to completely |
| 608 |
apply the update to the target database, then the RBU database |
| 609 |
is marked as fully applied. Otherwise, the state of the RBU |
| 610 |
update application is saved in the state database (or in the RBU |
| 611 |
database if the name of the state database file in sqlite3rbu_open() |
| 612 |
is NULL) for later resumption of the update. |
| 613 |
</p></li></ol> |
| 614 |
|
| 615 |
<p>If an update is only partially applied to the target database by the |
| 616 |
time sqlite3rbu_close() is called, state information is saved |
| 617 |
within the state database if it exists, or otherwise in the RBU database. |
| 618 |
This allows subsequent processes to automatically |
| 619 |
resume the RBU update from where it left off. |
| 620 |
If state information is stored in the RBU database, it can be removed |
| 621 |
by dropping all tables whose names begin with "rbu_". |
| 622 |
|
| 623 |
</p><p>For more details, refer to the comments in |
| 624 |
<a href="http://sqlite.org/src/doc/trunk/ext/rbu/sqlite3rbu.h">header file |
| 625 |
sqlite3rbu.h</a>. |
| 626 |
|
| 627 |
<a name="rbu_vacuum"></a> |
| 628 |
|
| 629 |
</p><h1 id="rbu_vacuum"><span>3. </span>RBU Vacuum</h1> |
| 630 |
|
| 631 |
<h2 id="rbu_vacuum_limitations"><span>3.1. </span>RBU Vacuum Limitations</h2> |
| 632 |
|
| 633 |
<p>When compared with SQLite's built-in VACUUM command, RBU Vacuum has the |
| 634 |
following limitations: |
| 635 |
|
| 636 |
</p><ul> |
| 637 |
<li><p>It may not be used on a database that contains <a href="expridx.html">indexes on expressions</a>. |
| 638 |
</p></li><li><p>The database being vacuumed may not be in <a href="wal.html">WAL mode</a>. |
| 639 |
</p></li></ul> |
| 640 |
|
| 641 |
<h2 id="rbu_vacuum_c_c_programming"><span>3.2. </span>RBU Vacuum C/C++ Programming</h2> |
| 642 |
|
| 643 |
<p> This section provides an overview of and example code demonstrating the |
| 644 |
integration of RBU Vacuum into an application program. For full details, |
| 645 |
refer to the comments in |
| 646 |
<a href="http://sqlite.org/src/doc/trunk/ext/rbu/sqlite3rbu.h">header file |
| 647 |
sqlite3rbu.h</a>. |
| 648 |
|
| 649 |
</p><p> RBU Vacuum applications all implement some variation of the following |
| 650 |
procedure: |
| 651 |
|
| 652 |
</p><ol> |
| 653 |
<li><p> An RBU handle is created by calling sqlite3rbu_vacuum(T, S). |
| 654 |
|
| 655 |
</p><p> Argument T is the name of the database file to vacuum. Argument S is |
| 656 |
the name of a database in which the RBU module will save its state if the |
| 657 |
vacuum operation is suspended. |
| 658 |
|
| 659 |
</p><p> If state database S does not exist when sqlite3rbu_vacuum() is |
| 660 |
invoked, it is automatically created and populated with the single table |
| 661 |
used to store the state of an RBU vacuum - "rbu_state". If an ongoing RBU |
| 662 |
vacuum is suspended, this table is populated with state data. The next |
| 663 |
time sqlite3rbu_vacuum() is called with the same S parameter, it detects |
| 664 |
this data and attempts to resume the suspended vacuum operation. When |
| 665 |
an RBU vacuum operation is completed or encounters an error, RBU |
| 666 |
automatically deletes the contents of the rbu_state table. In this case, |
| 667 |
the next call to sqlite3rbu_vacuum() starts an entirely new vacuum |
| 668 |
operation from scratch. |
| 669 |
|
| 670 |
</p><p> It is a good idea to establish a convention for determining the RBU |
| 671 |
vacuum state database name based on the target database name. The |
| 672 |
example code below uses "<target>-vacuum", where <target> is |
| 673 |
the name of the database being vacuumed. |
| 674 |
|
| 675 |
</p></li><li><p> Any custom collation sequences used by indexes within the database |
| 676 |
being vacuumed are registered with both of the database handles returned |
| 677 |
by the sqlite3rbu_db() function. |
| 678 |
|
| 679 |
</p></li><li><p> Function sqlite3rbu_step() is called on the RBU handle until either |
| 680 |
the RBU vacuum is finished, an error occurs or the application wishes to |
| 681 |
suspend the RBU vacuum. |
| 682 |
|
| 683 |
</p><p> Each call to sqlite3rbu_step() does a small amount of work towards |
| 684 |
completing the vacuum operation. Depending on the size of the database, a |
| 685 |
single vacuum may require thousands of calls to sqlite3rbu_step(). |
| 686 |
sqlite3rbu_step() returns SQLITE_DONE if the vacuum operation has |
| 687 |
finished, SQLITE_OK if the vacuum operation has not finished but no error |
| 688 |
has occurred, and an SQLite error code if an error is encountered. If |
| 689 |
an error does occur, all subsequent calls to sqlite3rbu_step() immediately |
| 690 |
return the same error code. |
| 691 |
|
| 692 |
</p></li><li><p> Finally, sqlite3rbu_close() is called to close the RBU handle. If the |
| 693 |
application stopped calling sqlite3rbu_step() before either the vacuum |
| 694 |
finished or an error occurred, the state of the vacuum is saved in the |
| 695 |
state database so that it may be resumed later on. |
| 696 |
|
| 697 |
</p><p> Like sqlite3rbu_step(), if the vacuum operation has finished, |
| 698 |
sqlite3rbu_close() returns SQLITE_DONE. If the vacuum has not finished |
| 699 |
but no error has occurred, SQLITE_OK is returned. Or, if an error has |
| 700 |
occurred, an SQLite error code is returned. If an error occurred as part |
| 701 |
of a prior call to sqlite3rbu_step(), sqlite3rbu_close() returns the |
| 702 |
same error code. |
| 703 |
</p></li></ol> |
| 704 |
|
| 705 |
<p>The following example code illustrates the techniques described above. |
| 706 |
|
| 707 |
</p><div class="codeblock"><pre><i>/*</i> |
| 708 |
<i>** Either start a new RBU vacuum or resume a suspended RBU vacuum on </i> |
| 709 |
<i>** database zTarget. Return when either an error occurs, the RBU </i> |
| 710 |
<i>** vacuum is finished or when the application signals an interrupt</i> |
| 711 |
<i>** (code not shown).</i> |
| 712 |
<i>**</i> |
| 713 |
<i>** If the RBU vacuum is completed successfully, return SQLITE_DONE.</i> |
| 714 |
<i>** If an error occurs, return SQLite error code. Or, if the application</i> |
| 715 |
<i>** signals an interrupt, suspend the RBU vacuum operation so that it</i> |
| 716 |
<i>** may be resumed by a subsequent call to this function and return</i> |
| 717 |
<i>** SQLITE_OK.</i> |
| 718 |
<i>**</i> |
| 719 |
<i>** This function uses the database named "<zTarget>-vacuum" for</i> |
| 720 |
<i>** the state database, where <zTarget> is the name of the database </i> |
| 721 |
<i>** being vacuumed.</i> |
| 722 |
<i>*/</i> |
| 723 |
int do_rbu_vacuum(const char *zTarget){ |
| 724 |
int rc; |
| 725 |
char *zState; <i>/* Name of state database */</i> |
| 726 |
sqlite3rbu *pRbu; <i>/* RBU vacuum handle */</i> |
| 727 |
|
| 728 |
zState = sqlite3_mprintf("%s-vacuum", zTarget); |
| 729 |
if( zState==0 ) return SQLITE_NOMEM; |
| 730 |
pRbu = sqlite3rbu_vacuum(zTarget, zState); |
| 731 |
sqlite3_free(zState); |
| 732 |
|
| 733 |
if( pRbu ){ |
| 734 |
sqlite3 *dbTarget = sqlite3rbu_db(pRbu, 0); |
| 735 |
sqlite3 *dbState = sqlite3rbu_db(pRbu, 1); |
| 736 |
|
| 737 |
<i>/* Any custom collation sequences used by the target database must</i> |
| 738 |
<i>** be registered with both database handles here. */</i> |
| 739 |
|
| 740 |
while( sqlite3rbu_step(pRbu)==SQLITE_OK ){ |
| 741 |
if( <i><application has signaled interrupt></i> ) break; |
| 742 |
} |
| 743 |
} |
| 744 |
rc = sqlite3rbu_close(pRbu); |
| 745 |
return rc; |
| 746 |
} |
| 747 |
</pre></div> |
| 748 |
<p align="center"><small><i>This page was last updated on 2025-07-12 15:11:36Z </small></i></p> |
| 749 |
|