Debian Codegraph @

www/rbu.html

raw
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&lt;<i>integer</i>&gt;_&lt;<i>target-table-name</i>&gt;" where
270 &lt;<i>target-table-name</i>&gt; is the name of the table in the target
271 database and &lt;<i>integer</i>&gt; 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 &lt;<i>integer</i>&gt; 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 &lt;<i>integer</i>&gt;.
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 &lt;<i>virtual-table-name</i>&gt; 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 "&lt;target&gt;-vacuum", where &lt;target&gt; 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 "&lt;zTarget&gt;-vacuum" for</i>
720 <i>** the state database, where &lt;zTarget&gt; 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>&lt;application has signaled interrupt&gt;</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