1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
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>
|
