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
|
<!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 Session 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 Session 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="#introduction">1. Introduction</a></div>
<div class="fancy-toc2"><a href="#typical_use_case">1.1. Typical Use Case</a></div>
<div class="fancy-toc2"><a href="#obtaining_the_session_extension">1.2. Obtaining the Session Extension</a></div>
<div class="fancy-toc2"><a href="#limitations">1.3. Limitations</a></div>
<div class="fancy-toc1"><a href="#concepts">2. Concepts</a></div>
<div class="fancy-toc2"><a href="#changesets_and_patchsets">2.1. Changesets and Patchsets</a></div>
<div class="fancy-toc2"><a href="#conflicts">2.2. Conflicts</a></div>
<div class="fancy-toc2"><a href="#changeset_construction">2.3. Changeset Construction</a></div>
<div class="fancy-toc1"><a href="#using_the_session_extension">3. Using The Session Extension</a></div>
<div class="fancy-toc2"><a href="#capturing_a_changeset">3.1. Capturing a Changeset</a></div>
<div class="fancy-toc2"><a href="#applying_a_changeset_to_a_database">3.2. Applying a Changeset to a Database</a></div>
<div class="fancy-toc2"><a href="#inspecting_the_contents_of_a_changeset">3.3. Inspecting the Contents of a Changeset</a></div>
<div class="fancy-toc1"><a href="#extended_functionality">4. Extended Functionality</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 id="introduction"><span>1. </span>Introduction</h1>
<p>The session extension provide a mechanism for conviently recording
changes to some or all of certain tables in an SQLite database, and
packaging those changes into a "changeset" or "patchset" file that can
later be used to apply the same set of changes to another database with
the same schema and compatible starting data. A "changeset" may
also be inverted and used to "undo" a session.
</p><p>This document is an introduction to the session extension.
The details of the interface are in the separate
<a href="session/intro.html">Session Extension C-language Interface</a> document.
</p><h2 id="typical_use_case"><span>1.1. </span>Typical Use Case</h2>
<p>Suppose SQLite is used as the <a href="appfileformat.html">application file format</a> for a
particular design application. Two users, Alice and Bob, each start
with a baseline design that is about a gigabyte in size. They work
all day, in parallel, each making their own customizations and tweaks
to the design. At the end of the day, they would like to merge their
changes together into a single unified design.
</p><p>The session extension facilitates this by recording all changes to
both Alice's and Bob's databases and writing those changes into
changeset or patchset files. At the end of the day, Alice can send her
changeset to Bob and Bob can "apply" it to his database. The result (assuming
there are no conflicts) is that Bob's database then contains both his
changes and Alice's changes. Likewise, Bob can send a changeset of
his work over to Alice and she can apply his changes to her database.
</p><p>In other words, the session extension provides a facility for
SQLite database files that is similar to the unix
<a href="https://en.wikipedia.org/wiki/Patch_(Unix)">patch</a> utility program,
or to the "merge" capabilities of version control systems such
as <a href="https://www.fossil-scm.org/">Fossil</a>, <a href="https://git-scm.com">Git</a>,
or <a href="http://www.mercurial-scm.org/">Mercurial</a>.
</p><h2 id="obtaining_the_session_extension"><span>1.2. </span>Obtaining the Session Extension</h2>
<p> Since <a href="releaselog/3_13_0.html">version 3.13.0</a> (2016-05-18),
the session extension has been included in the SQLite
<a href="amalgamation.html">amalgamation</a> source distribution. By default, the session extension is
disabled. To enable it, build with the following compiler switches:
</p><div class="codeblock"><pre>-DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK
</pre></div>
<p> Or, if using the autoconf build system,
pass the --enable-session option to the configure script.
</p><h2 id="limitations"><span>1.3. </span>Limitations</h2>
<ul>
<li><p> Prior to SQLite version 3.17.0, the session extension only worked with
<a href="rowidtable.html">rowid tables</a>, not <a href="withoutrowid.html">WITHOUT ROWID</a> tables. As of 3.17.0, both
rowid and WITHOUT ROWID tables are supported. However, extra steps are
needed to record primary keys for WITHOUT ROWID table changes.
</p></li><li><p> There is no support for <a href="vtab.html">virtual tables</a>. Changes to virtual tables are
not captured.
</p></li><li><p> The session extension only works with tables that have a declared
PRIMARY KEY. The PRIMARY KEY of a table may be an INTEGER PRIMARY KEY
(rowid alias) or an external PRIMARY KEY.
</p></li><li><p> SQLite allows <a href="nulls.html">NULL values</a> to be stored in
PRIMARY KEY columns. However, the session extension ignores all
such rows. No changes affecting rows with one or more NULL values
in PRIMARY KEY columns are recorded by the sessions module.
</p></li></ul>
<h1 id="concepts"><span>2. </span>Concepts</h1>
<a name="changeset"></a>
<h2 id="changesets_and_patchsets"><span>2.1. </span>Changesets and Patchsets</h2>
<p> The sessions module revolves around creating and manipulating
changesets. A changeset is a blob of data that encodes a series of
changes to a database. Each change in a changeset is one of the
following:
</p><ul>
<li> <p>An <b>INSERT</b>. An INSERT change contains a single row to add to
a database table. The payload of the INSERT change consists of the
values for each field of the new row.
</p></li><li> <p>A <b>DELETE</b>. A DELETE change represents a row, identified by
its primary key values, to remove from a database table. The payload
of a DELETE change consists of the values for all fields of the
deleted row.
</p></li><li> <p>An <b>UPDATE</b>. An UPDATE change represents the modification of
one or more non-PRIMARY KEY fields of a single row within a database
table, identified by its PRIMARY KEY fields. The payload for an UPDATE
change consists of:
</p><ul>
<li> The PRIMARY KEY values identifying the modified row,
</li><li> The new values for each modified field of the row, and
</li><li> The original values for each modified field of the row.
</li></ul>
<p> An UPDATE change does not contain any information regarding
non-PRIMARY KEY fields that are not modified by the change. It is not
possible for an UPDATE change to specify modifications to PRIMARY
KEY fields.
</p></li></ul>
<p> A single changeset may contain changes that apply to more than one
database table. For each table that the changeset includes at least one change
for, it also encodes the following data:
</p><ul>
<li> The name of the database table,
</li><li> The number of columns the table has, and
</li><li> Which of those columns are PRIMARY KEY columns.
</li></ul>
<p> Changesets may only be applied to databases that contain tables
matching the above three criteria as stored in the changeset.
</p><p> A patchset is similar to a changeset. It is slightly more compact than
a changeset, but provides more limited conflict detection and resolution
options (see the next section for details). The differences between a
patchset and a changeset are that:
</p><ul>
<li><p> For a <b>DELETE</b> change, the payload consists of the PRIMARY KEY
fields only. The original values of other fields are not stored as
part of a patchset.
</p></li><li><p> For an <b>UPDATE</b> change, the payload consists of the PRIMARY KEY
fields and the new values of modified fields only. The original
values of modified fields are not stored as part of a patchset.
</p></li></ul>
<h2 id="conflicts"><span>2.2. </span>Conflicts</h2>
<p> When a changeset or patchset is applied to a database, an attempt is
made to insert a new row for each INSERT change, remove a row for each
DELETE change and modify a row for each UPDATE change. If the target
database is in the same state as the original database that the changeset
was recorded on, this is a simple matter. However, if the contents of the
target database is not in exactly this state, conflicts can occur when
applying the changeset or patchset.
</p><p>When processing an <b>INSERT</b> change, the following conflicts can
occur:
</p><ul>
<li> The target database may already contain a row with the same PRIMARY
KEY values as specified by the INSERT change.
</li><li> Some other database constraint, for example a UNIQUE or CHECK
constraint, may be violated when the new row is inserted.
</li></ul>
<p>When processing a <b>DELETE</b> change, the following conflicts may be
detected:
</p><ul>
<li> The target database may contain no row with the specified PRIMARY
KEY values to delete.
</li><li> The target database may contain a row with the specified PRIMARY
KEY values, but the other fields may contain values that do not
match those stored as part of the changeset. This type of conflict
is not detected when using a patchset.
</li></ul>
<p>When processing an <b>UPDATE</b> change, the following conflicts may be
detected:
</p><ul>
<li> The target database may contain no row with the specified PRIMARY
KEY values to modify.
</li><li> The target database may contain a row with the specified PRIMARY
KEY values, but the current values of the fields that will be modified
by the change may not match the original values stored within the
changeset. This type of conflict is not detected when using a patchset.
</li><li> Some other database constraint, for example a UNIQUE or CHECK
constraint, may be violated when the row is updated.
</li></ul>
<p> Depending on the type of conflict, a sessions application has a variety
of configurable options for dealing with conflicts, ranging from omitting the
conflicting change, aborting the entire changeset application or applying
the change despite the conflict. For details, refer to the documentation for
the <a href="session/sqlite3changeset_apply.html">sqlite3changeset_apply()</a> API.
</p><h2 id="changeset_construction"><span>2.3. </span>Changeset Construction</h2>
<p> After a session object has been configured, it begins monitoring for
changes to its configured tables. However, it does not record an entire
change each time a row within the database is modified. Instead, it records
just the PRIMARY KEY fields for each inserted row, and just the PRIMARY KEY
and all original row values for any updated or deleted rows. If a row is
modified more than once by a single session, no new information is recorded.
</p><p> The other information required to create a changeset or patchset is
read from the database file when <a href="session/sqlite3session_changeset.html">sqlite3session_changeset()</a> or
<a href="session/sqlite3session_patchset.html">sqlite3session_patchset()</a> is called. Specifically,
</p><ul>
<li> <p>For each primary key recorded as a result of an INSERT operation,
the sessions module checks if there is a row with a matching primary
key still in the table. If so, an INSERT change is added to the
changeset.
</p></li><li> <p>For each primary key recorded as a result of an UPDATE or DELETE
operation, the sessions module also checks for a row with a matching
primary key within the table. If one can be found, but one or more
of the non-PRIMARY KEY fields does not match the original recorded
value, an UPDATE is added to the changeset. Or, if there is no row
at all with the specified primary key, a DELETE is added to the
changeset. If the row does exist but none of the non-PRIMARY KEY
fields have been modified, no change is added to the changeset.
</p></li></ul>
<p> One implication of the above is that if a change is made and then
unmade within a single session (for example if a row is inserted and then
deleted again), the sessions module does not report any change at all. Or
if a row is updated multiple times within the same session, all updates
are coalesced into a single update within any changeset or patchset blob.
</p><h1 id="using_the_session_extension"><span>3. </span>Using The Session Extension</h1>
<p> This section provides examples that demonstrate how to use the sessions
extension.
</p><h2 id="capturing_a_changeset"><span>3.1. </span>Capturing a Changeset</h2>
<p> The example code below demonstrates the steps involved in capturing a
changeset while executing SQL commands. In summary:
</p><ol>
<li> <p>A session object (type sqlite3_session*) is created by making a
call to the <a href="session/sqlite3session_create.html">sqlite3session_create()</a> API function.
</p><p>A single session object monitors changes made to a single database
(i.e. "main", "temp" or an attached database) via a single
sqlite3* database handle.
</p></li><li> <p>The session object is configured with a set of tables to monitor
changes on.
</p><p> By default a session object does not monitor changes on any
database table. Before it does so it must be configured. There
are three ways to configure the set of tables to monitor changes
on:
</p><ul>
<li> By explicitly specifying tables using one call to
<a href="session/sqlite3session_attach.html">sqlite3session_attach()</a> for each table, or
</li><li> By specifying that all tables in the database should be monitored
for changes using a call to <a href="session/sqlite3session_attach.html">sqlite3session_attach()</a> with a
NULL argument, or
</li><li> By configuring a callback to be invoked the first time each table
is written to that indicates to the session module whether or
not changes on the table should be monitored.
</li></ul>
<p> The example code below uses the second of the methods enumerated
above - it monitors for changes on all database tables.
</p></li><li> <p> Changes are made to the database by executing SQL statements. The
session object records these changes.
</p></li><li> <p> A changeset blob is extracted from the session object using a call
to <a href="session/sqlite3session_changeset.html">sqlite3session_changeset()</a> (or, if using patchsets, a call to
the <a href="session/sqlite3session_patchset.html">sqlite3session_patchset()</a> function).
</p></li><li> <p> The session object is deleted using a call to the
<a href="session/sqlite3session_delete.html">sqlite3session_delete()</a> API function.
</p><p> It is not necessary to delete a session object after extracting
a changeset or patchset from it. It can be left attached to the
database handle and will continue monitoring for changes on the
configured tables as before. However, if
<a href="session/sqlite3session_changeset.html">sqlite3session_changeset()</a> or <a href="session/sqlite3session_patchset.html">sqlite3session_patchset()</a> is
called a second time on a session object, the changeset or patchset
will contain <em>all</em> changes that have taken place on the connection
since the session was created. In other words,
a session object is not reset or
zeroed by a call to sqlite3session_changeset() or
sqlite3session_patchset().
</p></li></ol>
<div class="codeblock"><pre><i>/*
** Argument zSql points to a buffer containing an SQL script to execute
** against the database handle passed as the first argument. As well as
** executing the SQL script, this function collects a changeset recording
** all changes made to the "main" database file. Assuming no error occurs,
** output variables (*ppChangeset) and (*pnChangeset) are set to point
** to a buffer containing the changeset and the size of the changeset in
** bytes before returning SQLITE_OK. In this case it is the responsibility
** of the caller to eventually free the changeset blob by passing it to
** the sqlite3_free function.
**
** Or, if an error does occur, return an SQLite error code. The final
** value of (*pChangeset) and (*pnChangeset) are undefined in this case.
*/</i>
int sql_exec_changeset(
sqlite3 *db, <i>/* Database handle */</i>
const char *zSql, <i>/* SQL script to execute */</i>
int *pnChangeset, <i>/* OUT: Size of changeset blob in bytes */</i>
void **ppChangeset <i>/* OUT: Pointer to changeset blob */</i>
){
sqlite3_session *pSession = 0;
int rc;
<i>/* Create a new session object */</i>
rc = sqlite3session_create(db, "main", &pSession);
<i>/* Configure the session object to record changes to all tables */</i>
if( rc==SQLITE_OK ) rc = sqlite3session_attach(pSession, NULL);
<i>/* Execute the SQL script */</i>
if( rc==SQLITE_OK ) rc = sqlite3_exec(db, zSql, 0, 0, 0);
<i>/* Collect the changeset */</i>
if( rc==SQLITE_OK ){
rc = sqlite3session_changeset(pSession, pnChangeset, ppChangeset);
}
<i>/* Delete the session object */</i>
sqlite3session_delete(pSession);
return rc;
}
</pre></div>
<h2 id="applying_a_changeset_to_a_database"><span>3.2. </span>Applying a Changeset to a Database</h2>
<p> Applying a changeset to a database is simpler than capturing a changeset.
Usually, a single call to <a href="session/sqlite3changeset_apply.html">sqlite3changeset_apply()</a>, as depicted in the
example code below, suffices.
</p><p> In cases where it is complicated, the complications in applying a
changeset lie in conflict resolution. Refer to the API documentation linked
above for details.
</p><div class="codeblock"><pre><i>/*
** Conflict handler callback used by apply_changeset(). See below.
*/</i>
static int xConflict(void *pCtx, int eConflict, sqlite3_changset_iter *pIter){
int ret = (int)pCtx;
return ret;
}
<i>/*
** Apply the changeset contained in blob pChangeset, size nChangeset bytes,
** to the main database of the database handle passed as the first argument.
** Return SQLITE_OK if successful, or an SQLite error code if an error
** occurs.
**
** If parameter bIgnoreConflicts is true, then any conflicting changes
** within the changeset are simply ignored. Or, if bIgnoreConflicts is
** false, then this call fails with an SQLITE_ABORT error if a changeset
** conflict is encountered.
*/</i>
int apply_changeset(
sqlite3 *db, <i>/* Database handle */</i>
int bIgnoreConflicts, <i>/* True to ignore conflicting changes */</i>
int nChangeset, <i>/* Size of changeset in bytes */</i>
void *pChangeset <i>/* Pointer to changeset blob */</i>
){
return sqlite3changeset_apply(
db,
nChangeset, pChangeset,
0, xConflict,
(void*)bIgnoreConflicts
);
}
</pre></div>
<h2 id="inspecting_the_contents_of_a_changeset"><span>3.3. </span>Inspecting the Contents of a Changeset</h2>
<p> The example code below demonstrates the techniques used to iterate
through and extract the data related to all changes in a changeset. To
summarize:
</p><ol>
<li><p> The <a href="session/sqlite3changeset_start.html">sqlite3changeset_start()</a> API is called to create and
initialize an iterator to iterate through the contents of a
changeset. Initially, the iterator points to no element at all.
</p></li><li><p> The first call to <a href="session/sqlite3changeset_next.html">sqlite3changeset_next()</a> on the iterator moves
it to point to the first change in the changeset (or to EOF, if
the changeset is completely empty). sqlite3changeset_next() returns
SQLITE_ROW if it moves the iterator to point to a valid entry,
SQLITE_DONE if it moves the iterator to EOF, or an SQLite error
code if an error occurs.
</p></li><li><p> If the iterator points to a valid entry, the <a href="session/sqlite3changeset_op.html">sqlite3changeset_op()</a>
API may be used to determine the type of change (INSERT, UPDATE or
DELETE) that the iterator points to. Additionally, the same API
can be used to obtain the name of the table the change applies to
and its expected number of columns and primary key columns.
</p></li><li><p> If the iterator points to a valid INSERT or UPDATE entry, the
<a href="session/sqlite3changeset_new.html">sqlite3changeset_new()</a> API may be used to obtain the new.* values
within the change payload.
</p></li><li><p> If the iterator points to a valid DELETE or UPDATE entry, the
<a href="session/sqlite3changeset_old.html">sqlite3changeset_old()</a> API may be used to obtain the old.* values
within the change payload.
</p></li><li><p> An iterator is deleted using a call to the
<a href="session/sqlite3changeset_finalize.html">sqlite3changeset_finalize()</a> API. If an error occured while
iterating, an SQLite error code is returned (even if the same error
code has already been returned by sqlite3changeset_next()). Or,
if no error has occurred, SQLITE_OK is returned.
</p></li></ol>
<div class="codeblock"><pre><i>/*
** Print the contents of the changeset to stdout.
*/</i>
static int print_changeset(void *pChangeset, int nChangeset){
int rc;
sqlite3_changeset_iter *pIter = 0;
<i>/* Create an iterator to iterate through the changeset */</i>
rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);
if( rc!=SQLITE_OK ) return rc;
<i>/* This loop runs once for each change in the changeset */</i>
while( SQLITE_ROW==sqlite3changeset_next(pIter) ){
const char *zTab; <i>/* Table change applies to */</i>
int nCol; <i>/* Number of columns in table zTab */</i>
int op; <i>/* SQLITE_INSERT, UPDATE or DELETE */</i>
sqlite3_value *pVal;
<i>/* Print the type of operation and the table it is on */</i>
rc = sqlite3changeset_op(pIter, &zTab, &nCol, &op, 0);
if( rc!=SQLITE_OK ) goto exit_print_changeset;
printf("%s on table %s\n",
op==SQLITE_INSERT?"INSERT" : op==SQLITE_UPDATE?"UPDATE" : "DELETE",
zTab
);
<i>/* If this is an UPDATE or DELETE, print the old.* values */</i>
if( op==SQLITE_UPDATE || op==SQLITE_DELETE ){
printf("Old values:");
for(i=0; i<nCol; i++){
rc = sqlite3changeset_old(pIter, i, &pVal);
if( rc!=SQLITE_OK ) goto exit_print_changeset;
printf(" %s", pVal ? sqlite3_value_text(pVal) : "-");
}
printf("\n");
}
<i>/* If this is an UPDATE or INSERT, print the new.* values */</i>
if( op==SQLITE_UPDATE || op==SQLITE_INSERT ){
printf("New values:");
for(i=0; i<nCol; i++){
rc = sqlite3changeset_new(pIter, i, &pVal);
if( rc!=SQLITE_OK ) goto exit_print_changeset;
printf(" %s", pVal ? sqlite3_value_text(pVal) : "-");
}
printf("\n");
}
}
<i>/* Clean up the changeset and return an error code (or SQLITE_OK) */</i>
exit_print_changeset:
rc2 = sqlite3changeset_finalize(pIter);
if( rc==SQLITE_OK ) rc = rc2;
return rc;
}
</pre></div>
<h1 id="extended_functionality"><span>4. </span>Extended Functionality</h1>
<p> Most applications will only use the session module functionality described
in the previous section. However, the following additional functionality is
available for the use and manipulation of changeset and patchset blobs:
</p><ul>
<li> <p>Two or more changeset/patchsets may be combined using the
<a href="session/sqlite3changeset_concat.html">sqlite3changeset_concat()</a> or <a href="session/changegroup.html">sqlite3_changegroup</a> interfaces.
</p></li><li> <p>A changeset may be "inverted" using the <a href="session/sqlite3changeset_invert.html">sqlite3changeset_invert()</a>
API function. An inverted changeset undoes the changes made by the
original. If changeset C<sup>+</sup> is the inverse of changeset C, then
applying C and then C<sup>+</sup> to a database should leave
the database unchanged.
</p></li></ul>
|
