/ Check-in [16b9bf92]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhance existing snapshot tests to serialize/deserialize snapshots. No new tests.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | serializable-snapshot
Files: files | file ages | folders
SHA1:16b9bf92741e4c62874cffd7c6a61763c5054c7a
User & Date: dan 2016-11-18 14:38:41
Context
2016-11-18
18:22
Add tests for snapshot interfaces. check-in: 1f7ee7af user: dan tags: serializable-snapshot
14:38
Enhance existing snapshot tests to serialize/deserialize snapshots. No new tests. check-in: 16b9bf92 user: dan tags: serializable-snapshot
2016-11-15
17:37
Experimental changes toward making snapshots serializable. check-in: b6a81fa1 user: drh tags: serializable-snapshot
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test1.c.

2384
2385
2386
2387
2388
2389
2390











































































































2391
2392
2393
2394
2395
2396
2397
....
7535
7536
7537
7538
7539
7540
7541



7542
7543
7544
7545
7546
7547
7548
  p2 = (sqlite3_snapshot*)sqlite3TestTextToPtr(Tcl_GetString(objv[2]));
  res = sqlite3_snapshot_cmp(p1, p2);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(res));
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */












































































































/*
** Usage: sqlite3_delete_database FILENAME
*/
int sqlite3_delete_database(const char*);   /* in test_delete.c */
static int SQLITE_TCLAPI test_delete_database(
  void * clientData,
  Tcl_Interp *interp,
................................................................................
#endif
     { "vfs_current_time_int64",           vfsCurrentTimeInt64,   0 },
#ifdef SQLITE_ENABLE_SNAPSHOT
     { "sqlite3_snapshot_get", test_snapshot_get, 0 },
     { "sqlite3_snapshot_open", test_snapshot_open, 0 },
     { "sqlite3_snapshot_free", test_snapshot_free, 0 },
     { "sqlite3_snapshot_cmp", test_snapshot_cmp, 0 },



#endif
     { "sqlite3_delete_database", test_delete_database, 0 },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  static int longdouble_size = sizeof(LONGDOUBLE_TYPE);
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>







2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
....
7642
7643
7644
7645
7646
7647
7648
7649
7650
7651
7652
7653
7654
7655
7656
7657
7658
  p2 = (sqlite3_snapshot*)sqlite3TestTextToPtr(Tcl_GetString(objv[2]));
  res = sqlite3_snapshot_cmp(p1, p2);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(res));
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
/*
** Usage: sqlite3_snapshot_get_blob DB DBNAME
*/
static int SQLITE_TCLAPI test_snapshot_get_blob(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int rc;
  sqlite3 *db;
  char *zName;
  sqlite3_snapshot *pSnapshot = 0;

  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB DBNAME");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  zName = Tcl_GetString(objv[2]);

  rc = sqlite3_snapshot_get(db, zName, &pSnapshot);
  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }else{
    Tcl_SetObjResult(interp, 
        Tcl_NewByteArrayObj((unsigned char*)pSnapshot, sizeof(sqlite3_snapshot))
    );
    sqlite3_snapshot_free(pSnapshot);
  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
  /*
  ** Usage: sqlite3_snapshot_open_blob DB DBNAME SNAPSHOT
*/
static int SQLITE_TCLAPI test_snapshot_open_blob(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int rc;
  sqlite3 *db;
  char *zName;
  unsigned char *pBlob;
  int nBlob;

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB DBNAME SNAPSHOT");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  zName = Tcl_GetString(objv[2]);
  pBlob = Tcl_GetByteArrayFromObj(objv[3], &nBlob);
  if( nBlob!=sizeof(sqlite3_snapshot) ){
    Tcl_AppendResult(interp, "bad SNAPSHOT", 0);
    return TCL_ERROR;
  }
  rc = sqlite3_snapshot_open(db, zName, (sqlite3_snapshot*)pBlob);
  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
/*
** Usage: sqlite3_snapshot_cmp_blob SNAPSHOT1 SNAPSHOT2
*/
static int SQLITE_TCLAPI test_snapshot_cmp_blob(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int res;
  unsigned char *p1;
  unsigned char *p2;
  int n1;
  int n2;

  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "SNAPSHOT1 SNAPSHOT2");
    return TCL_ERROR;
  }

  p1 = Tcl_GetByteArrayFromObj(objv[1], &n1);
  p2 = Tcl_GetByteArrayFromObj(objv[2], &n2);

  if( n1!=sizeof(sqlite3_snapshot) || n1!=n2 ){
    Tcl_AppendResult(interp, "bad SNAPSHOT", 0);
    return TCL_ERROR;
  }

  res = sqlite3_snapshot_cmp((sqlite3_snapshot*)p1, (sqlite3_snapshot*)p2);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(res));
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

/*
** Usage: sqlite3_delete_database FILENAME
*/
int sqlite3_delete_database(const char*);   /* in test_delete.c */
static int SQLITE_TCLAPI test_delete_database(
  void * clientData,
  Tcl_Interp *interp,
................................................................................
#endif
     { "vfs_current_time_int64",           vfsCurrentTimeInt64,   0 },
#ifdef SQLITE_ENABLE_SNAPSHOT
     { "sqlite3_snapshot_get", test_snapshot_get, 0 },
     { "sqlite3_snapshot_open", test_snapshot_open, 0 },
     { "sqlite3_snapshot_free", test_snapshot_free, 0 },
     { "sqlite3_snapshot_cmp", test_snapshot_cmp, 0 },
     { "sqlite3_snapshot_get_blob", test_snapshot_get_blob, 0 },
     { "sqlite3_snapshot_open_blob", test_snapshot_open_blob, 0 },
     { "sqlite3_snapshot_cmp_blob", test_snapshot_cmp_blob, 0 },
#endif
     { "sqlite3_delete_database", test_delete_database, 0 },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  static int longdouble_size = sizeof(LONGDOUBLE_TYPE);
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;

Changes to test/snapshot.test.

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



































# "PRAGMA journal_mode=memory", which fails if the database is in wal mode
# and there are one or more existing connections.
if {[permutation]=="inmemory_journal"} {
  finish_test
  return
}

#-------------------------------------------------------------------------
# Check some error conditions in snapshot_get(). It is an error if:
#
#  1) snapshot_get() is called on a non-WAL database, or
#  2) there is an open write transaction on the database.
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
}

do_test 1.1.1 {
  execsql { BEGIN; SELECT * FROM t1; }
  list [catch { sqlite3_snapshot_get db main } msg] $msg
} {1 SQLITE_ERROR}
do_execsql_test 1.1.2 COMMIT

do_test 1.2.1 {
  execsql {
    PRAGMA journal_mode = WAL;
    BEGIN;
      INSERT INTO t1 VALUES(5, 6);
      INSERT INTO t1 VALUES(7, 8);
  }
  list [catch { sqlite3_snapshot_get db main } msg] $msg
} {1 SQLITE_ERROR}
do_execsql_test 1.3.2 COMMIT

#-------------------------------------------------------------------------
# Check that a simple case works. Reuse the database created by the
# block of tests above.
#
do_execsql_test 2.1.0 {
  BEGIN;
    SELECT * FROM t1;
} {1 2 3 4 5 6 7 8}

do_test 2.1.1 {
  set snapshot [sqlite3_snapshot_get db main]
  execsql {
    COMMIT;
    INSERT INTO t1 VALUES(9, 10);
    SELECT * FROM t1;
  }
} {1 2 3 4 5 6 7 8 9 10}

do_test 2.1.2 {
  execsql BEGIN
  sqlite3_snapshot_open db main $snapshot
  execsql {
    SELECT * FROM t1;
  }
} {1 2 3 4 5 6 7 8}

do_test 2.1.3 {
  sqlite3_snapshot_free $snapshot
  execsql COMMIT
} {}

do_test 2.2.0 {
  sqlite3 db2 test.db
  execsql {
    BEGIN;
      SELECT * FROM t1;
  } db2
} {1 2 3 4 5 6 7 8 9 10}

do_test 2.2.1 {
  set snapshot [sqlite3_snapshot_get db2 main]
  execsql {
    INSERT INTO t1 VALUES(11, 12);
    SELECT * FROM t1;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}

do_test 2.2.2 {
  execsql BEGIN
  sqlite3_snapshot_open db main $snapshot
  execsql {
    SELECT * FROM t1;
  }
} {1 2 3 4 5 6 7 8 9 10}

do_test 2.2.3 {
  sqlite3_snapshot_free $snapshot
  execsql COMMIT
  execsql COMMIT db2
  db2 close
} {}

do_test 2.3.1 {
  execsql { DELETE FROM t1 WHERE a>6 }
  set snapshot [sqlite3_snapshot_get db main]
  execsql {
    INSERT INTO t1 VALUES('a', 'b');
    INSERT INTO t1 VALUES('c', 'd');
    SELECT * FROM t1;
  }
} {1 2 3 4 5 6 a b c d}
do_test 2.3.2 {
  execsql BEGIN
  sqlite3_snapshot_open db main $snapshot
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6}

do_test 2.3.3 {
  catchsql {
    INSERT INTO t1 VALUES('x','y')
  }
} {1 {database is locked}}
do_test 2.3.4 {
  execsql COMMIT
  sqlite3_snapshot_free $snapshot
} {}

#-------------------------------------------------------------------------
# Check some errors in sqlite3_snapshot_open(). It is an error if:
#
#   1) the db is in auto-commit mode,
#   2) the db has an open (read or write) transaction,
#   3) the db is not a wal database,
#
# Reuse the database created by earlier tests.
#
do_execsql_test 3.0.0 {
  CREATE TABLE t2(x, y);
  INSERT INTO t2 VALUES('a', 'b');
  INSERT INTO t2 VALUES('c', 'd');
  BEGIN;
    SELECT * FROM t2;
} {a b c d}
do_test 3.0.1 {
  set snapshot [sqlite3_snapshot_get db main]
  execsql { COMMIT }
  execsql { INSERT INTO t2 VALUES('e', 'f'); }
} {}

do_test 3.1 {
  list [catch {sqlite3_snapshot_open db main $snapshot } msg] $msg
} {1 SQLITE_ERROR}

do_test 3.2.1 {
  execsql {
    BEGIN;
      SELECT * FROM t2;
  }
} {a b c d e f}
do_test 3.2.2 {
  list [catch {sqlite3_snapshot_open db main $snapshot } msg] $msg
} {1 SQLITE_ERROR}

do_test 3.2.3 {
  execsql {
    COMMIT;
    BEGIN;
      INSERT INTO t2 VALUES('g', 'h');
  }
  list [catch {sqlite3_snapshot_open db main $snapshot } msg] $msg
} {1 SQLITE_ERROR}
do_execsql_test 3.2.4 COMMIT

do_test 3.3.1 {
  execsql { PRAGMA journal_mode = DELETE }
  execsql { BEGIN }
  list [catch {sqlite3_snapshot_open db main $snapshot } msg] $msg
} {1 SQLITE_ERROR}

do_test 3.3.2 {
  sqlite3_snapshot_free $snapshot
  execsql COMMIT
} {}

#-------------------------------------------------------------------------
# Check that SQLITE_BUSY_SNAPSHOT is returned if the specified snapshot
# no longer exists because the wal file has been checkpointed.
#
#   1. Reading a snapshot from the middle of a wal file is not possible
#      after the wal file has been checkpointed.
#
#   2. That a snapshot from the end of a wal file can not be read once
#      the wal file has been wrapped.
#
do_execsql_test 4.1.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t3(i, j);
  INSERT INTO t3 VALUES('o', 't');
  INSERT INTO t3 VALUES('t', 'f');
  BEGIN;
    SELECT * FROM t3;
} {wal o t t f}

do_test 4.1.1 {
  set snapshot [sqlite3_snapshot_get db main]
  execsql COMMIT
} {}
do_test 4.1.2 {
  execsql { 
    INSERT INTO t3 VALUES('f', 's'); 
    BEGIN;
  }
  sqlite3_snapshot_open db main $snapshot
  execsql { SELECT * FROM t3 }
} {o t t f}

do_test 4.1.3 {
  execsql { 
    COMMIT;
    PRAGMA wal_checkpoint;
    BEGIN;
  }
  list [catch {sqlite3_snapshot_open db main $snapshot} msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}
do_test 4.1.4 {
  sqlite3_snapshot_free $snapshot
  execsql COMMIT
} {}

do_test 4.2.1 {
  execsql {
    INSERT INTO t3 VALUES('s', 'e');
    INSERT INTO t3 VALUES('n', 't');
    BEGIN;
      SELECT * FROM t3;
  }
} {o t t f f s s e n t}
do_test 4.2.2 {
  set snapshot [sqlite3_snapshot_get db main]
  execsql {
    COMMIT;
    PRAGMA wal_checkpoint;
    BEGIN;
  }
  sqlite3_snapshot_open db main $snapshot
  execsql { SELECT * FROM t3 }
} {o t t f f s s e n t}
do_test 4.2.3 {
  execsql {
    COMMIT;
    INSERT INTO t3 VALUES('e', 't');
    BEGIN;
  }
  list [catch {sqlite3_snapshot_open db main $snapshot} msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}
do_test 4.2.4 {
  sqlite3_snapshot_free $snapshot
} {}

#-------------------------------------------------------------------------
# Check that SQLITE_BUSY is returned if a checkpoint is running when
# sqlite3_snapshot_open() is called.
#
reset_db
db close
testvfs tvfs
sqlite3 db test.db -vfs tvfs

do_execsql_test 5.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE x1(x, xx, xxx);
  INSERT INTO x1 VALUES('z', 'zz', 'zzz');
  BEGIN;
    SELECT * FROM x1;
} {wal z zz zzz}

do_test 5.2 {
  set ::snapshot [sqlite3_snapshot_get db main]
  sqlite3 db2 test.db -vfs tvfs
  execsql {
    INSERT INTO x1 VALUES('a', 'aa', 'aaa');
    COMMIT;
  }
} {}

set t53 0
proc write_callback {args} {
  do_test 5.3.[incr ::t53] {
    execsql BEGIN
    list [catch { sqlite3_snapshot_open db main $::snapshot } msg] $msg
  } {1 SQLITE_BUSY}
  catchsql COMMIT
}

tvfs filter xWrite
tvfs script write_callback
db2 eval { PRAGMA wal_checkpoint }
db close
db2 close
tvfs delete
sqlite3_snapshot_free $snapshot

#-------------------------------------------------------------------------
# Test that sqlite3_snapshot_get() may be called immediately after
# "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may
# be called after opening the db handle and running the script
# "PRAGMA user_version; BEGIN".
reset_db
do_execsql_test 6.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE x1(x, xx, xxx);
  INSERT INTO x1 VALUES('z', 'zz', 'zzz');
  BEGIN;
    PRAGMA user_version;
} {wal 0}
do_test 6.2 {
  set ::snapshot [sqlite3_snapshot_get db main]
  execsql {
    INSERT INTO x1 VALUES('a', 'aa', 'aaa');
    COMMIT;
  }
} {}
do_test 6.3 {
  sqlite3 db2 test.db 
  db2 eval "PRAGMA user_version ; BEGIN"
  sqlite3_snapshot_open db2 main $::snapshot
  db2 eval { SELECT * FROM x1 }
} {z zz zzz}
do_test 6.4 {
  db2 close
  sqlite3 db2 test.db 
  db2 eval "PRAGMA application_id"
  db2 eval "BEGIN"
  sqlite3_snapshot_open db2 main $::snapshot
  db2 eval { SELECT * FROM x1 }
} {z zz zzz}

do_test 6.5 {
  db2 close
  sqlite3 db2 test.db 
  db2 eval "BEGIN"
  list [catch {sqlite3_snapshot_open db2 main $::snapshot} msg] $msg
} {1 SQLITE_ERROR}

sqlite3_snapshot_free $snapshot

#-------------------------------------------------------------------------
# The following tests investigate the sqlite3_snapshot_cmp() API.
#

# Compare snapshots $p1 and $p2, checking that the result is $r.
#
proc do_snapshot_cmp_test {tn p1 p2 r} {
  uplevel [list do_test $tn.1 [list sqlite3_snapshot_cmp $p1 $p2] $r]
  uplevel [list do_test $tn.2 [list sqlite3_snapshot_cmp $p2 $p1] [expr $r*-1]]
  uplevel [list do_test $tn.3 [list sqlite3_snapshot_cmp $p1 $p1] 0]
  uplevel [list do_test $tn.4 [list sqlite3_snapshot_cmp $p2 $p2] 0]
}

catch { db2 close }
reset_db

do_execsql_test 7.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x);
} wal

do_test 7.1.2 {
  execsql { BEGIN ; PRAGMA application_id }
  set p1 [sqlite3_snapshot_get db main]
  execsql {
    INSERT INTO t1 VALUES(10);
    COMMIT;
  }
  execsql { BEGIN ; PRAGMA application_id }
  set p2 [sqlite3_snapshot_get db main]
  execsql COMMIT
} {}

do_snapshot_cmp_test 7.1.3 $p1 $p2 -1
sqlite3_snapshot_free $p1
sqlite3_snapshot_free $p2

do_execsql_test 7.2.1 {
  INSERT INTO t1 VALUES(11);
  INSERT INTO t1 VALUES(12);
  INSERT INTO t1 VALUES(13);
  BEGIN; 
    PRAGMA application_id;
} {0}
do_test 7.2.2 {
  set p1 [sqlite3_snapshot_get db main]
  execsql {
    COMMIT;
    INSERT INTO t1 VALUES(14);
    PRAGMA wal_checkpoint;
    BEGIN;
      PRAGMA application_id;
  }
  set p2 [sqlite3_snapshot_get db main]
  execsql COMMIT
} {}

do_snapshot_cmp_test 7.2.3 $p1 $p2 -1
sqlite3_snapshot_free $p2

do_test 7.3.1 {
  execsql {
    INSERT INTO t1 VALUES(14);
    BEGIN;
      PRAGMA application_id;
  }
  set p2 [sqlite3_snapshot_get db main]
  execsql COMMIT
} {}

do_snapshot_cmp_test 7.3.2 $p1 $p2 -1
sqlite3_snapshot_free $p1
sqlite3_snapshot_free $p2

finish_test










































|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
# "PRAGMA journal_mode=memory", which fails if the database is in wal mode
# and there are one or more existing connections.
if {[permutation]=="inmemory_journal"} {
  finish_test
  return
}

foreach {tn tcl} {
  1 {
    proc snapshot_get {DB DBNAME} {
      uplevel [list sqlite3_snapshot_get $DB $DBNAME]
    }
    proc snapshot_open {DB DBNAME SNAPSHOT} {
      uplevel [list sqlite3_snapshot_open $DB $DBNAME $SNAPSHOT]
    }
    proc snapshot_free {SNAPSHOT} {
      uplevel [list sqlite3_snapshot_free $SNAPSHOT]
    }
    proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
      uplevel [list sqlite3_snapshot_cmp $SNAPSHOT1 $SNAPSHOT2]
    }
  }

  2 {
    proc snapshot_get {DB DBNAME} {
      uplevel [list sqlite3_snapshot_get_blob $DB $DBNAME]
    }
    proc snapshot_open {DB DBNAME SNAPSHOT} {
      uplevel [list sqlite3_snapshot_open_blob $DB $DBNAME $SNAPSHOT]
    }
    proc snapshot_free {SNAPSHOT} {
    }
    proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
      uplevel [list sqlite3_snapshot_cmp_blob $SNAPSHOT1 $SNAPSHOT2]
    }
  }
} {

  reset_db
  eval $tcl

  #-------------------------------------------------------------------------
  # Check some error conditions in snapshot_get(). It is an error if:
  #
  #  1) snapshot_get() is called on a non-WAL database, or
  #  2) there is an open write transaction on the database.
  #
  do_execsql_test $tn.1.0 {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  }

  do_test $tn.1.1.1 {
    execsql { BEGIN; SELECT * FROM t1; }
    list [catch { snapshot_get db main } msg] $msg
  } {1 SQLITE_ERROR}
  do_execsql_test 1.1.2 COMMIT

  do_test $tn.1.2.1 {
    execsql {
      PRAGMA journal_mode = WAL;
      BEGIN;
        INSERT INTO t1 VALUES(5, 6);
        INSERT INTO t1 VALUES(7, 8);
    }
    list [catch { snapshot_get db main } msg] $msg
  } {1 SQLITE_ERROR}
  do_execsql_test $tn.1.3.2 COMMIT

  #-------------------------------------------------------------------------
  # Check that a simple case works. Reuse the database created by the
  # block of tests above.
  #
  do_execsql_test $tn.2.1.0 {
    BEGIN;
      SELECT * FROM t1;
  } {1 2 3 4 5 6 7 8}

  do_test $tn.2.1.1 {
    set snapshot [snapshot_get db main]
    execsql {
      COMMIT;
      INSERT INTO t1 VALUES(9, 10);
      SELECT * FROM t1;
    }
  } {1 2 3 4 5 6 7 8 9 10}

  do_test $tn.2.1.2 {
    execsql BEGIN
    snapshot_open db main $snapshot
    execsql {
      SELECT * FROM t1;
    }
  } {1 2 3 4 5 6 7 8}

  do_test $tn.2.1.3 {
    snapshot_free $snapshot
    execsql COMMIT
  } {}

  do_test $tn.2.2.0 {
    sqlite3 db2 test.db
    execsql {
      BEGIN;
        SELECT * FROM t1;
    } db2
  } {1 2 3 4 5 6 7 8 9 10}

  do_test $tn.2.2.1 {
    set snapshot [snapshot_get db2 main]
    execsql {
      INSERT INTO t1 VALUES(11, 12);
      SELECT * FROM t1;
    }
  } {1 2 3 4 5 6 7 8 9 10 11 12}

  do_test $tn.2.2.2 {
    execsql BEGIN
    snapshot_open db main $snapshot
    execsql {
      SELECT * FROM t1;
    }
  } {1 2 3 4 5 6 7 8 9 10}

  do_test $tn.2.2.3 {
    snapshot_free $snapshot
    execsql COMMIT
    execsql COMMIT db2
    db2 close
  } {}

  do_test $tn.2.3.1 {
    execsql { DELETE FROM t1 WHERE a>6 }
    set snapshot [snapshot_get db main]
    execsql {
      INSERT INTO t1 VALUES('a', 'b');
      INSERT INTO t1 VALUES('c', 'd');
      SELECT * FROM t1;
    }
  } {1 2 3 4 5 6 a b c d}
  do_test $tn.2.3.2 {
    execsql BEGIN
    snapshot_open db main $snapshot
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6}

  do_test $tn.2.3.3 {
    catchsql {
      INSERT INTO t1 VALUES('x','y')
    }
  } {1 {database is locked}}
  do_test $tn.2.3.4 {
    execsql COMMIT
    snapshot_free $snapshot
  } {}

  #-------------------------------------------------------------------------
  # Check some errors in snapshot_open(). It is an error if:
  #
  #   1) the db is in auto-commit mode,
  #   2) the db has an open (read or write) transaction,
  #   3) the db is not a wal database,
  #
  # Reuse the database created by earlier tests.
  #
  do_execsql_test $tn.3.0.0 {
    CREATE TABLE t2(x, y);
    INSERT INTO t2 VALUES('a', 'b');
    INSERT INTO t2 VALUES('c', 'd');
    BEGIN;
      SELECT * FROM t2;
  } {a b c d}
  do_test $tn.3.0.1 {
    set snapshot [snapshot_get db main]
    execsql { COMMIT }
    execsql { INSERT INTO t2 VALUES('e', 'f'); }
  } {}

  do_test $tn.3.1 {
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}

  do_test $tn.3.2.1 {
    execsql {
      BEGIN;
        SELECT * FROM t2;
    }
  } {a b c d e f}
  do_test $tn.3.2.2 {
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}

  do_test $tn.3.2.3 {
    execsql {
      COMMIT;
      BEGIN;
        INSERT INTO t2 VALUES('g', 'h');
    }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}
  do_execsql_test 3.2.4 COMMIT

  do_test $tn.3.3.1 {
    execsql { PRAGMA journal_mode = DELETE }
    execsql { BEGIN }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}

  do_test $tn.$tn.3.3.2 {
    snapshot_free $snapshot
    execsql COMMIT
  } {}

  #-------------------------------------------------------------------------
  # Check that SQLITE_BUSY_SNAPSHOT is returned if the specified snapshot
  # no longer exists because the wal file has been checkpointed.
  #
  #   1. Reading a snapshot from the middle of a wal file is not possible
  #      after the wal file has been checkpointed.
  #
  #   2. That a snapshot from the end of a wal file can not be read once
  #      the wal file has been wrapped.
  #
  do_execsql_test $tn.4.1.0 {
    PRAGMA journal_mode = wal;
    CREATE TABLE t3(i, j);
    INSERT INTO t3 VALUES('o', 't');
    INSERT INTO t3 VALUES('t', 'f');
    BEGIN;
      SELECT * FROM t3;
  } {wal o t t f}

  do_test $tn.4.1.1 {
    set snapshot [snapshot_get db main]
    execsql COMMIT
  } {}
  do_test $tn.4.1.2 {
    execsql { 
      INSERT INTO t3 VALUES('f', 's'); 
      BEGIN;
    }
    snapshot_open db main $snapshot
    execsql { SELECT * FROM t3 }
  } {o t t f}

  do_test $tn.4.1.3 {
    execsql { 
      COMMIT;
      PRAGMA wal_checkpoint;
      BEGIN;
    }
    list [catch {snapshot_open db main $snapshot} msg] $msg
  } {1 SQLITE_BUSY_SNAPSHOT}
  do_test $tn.4.1.4 {
    snapshot_free $snapshot
    execsql COMMIT
  } {}

  do_test $tn.4.2.1 {
    execsql {
      INSERT INTO t3 VALUES('s', 'e');
      INSERT INTO t3 VALUES('n', 't');
      BEGIN;
        SELECT * FROM t3;
    }
  } {o t t f f s s e n t}
  do_test $tn.4.2.2 {
    set snapshot [snapshot_get db main]
    execsql {
      COMMIT;
      PRAGMA wal_checkpoint;
      BEGIN;
    }
    snapshot_open db main $snapshot
    execsql { SELECT * FROM t3 }
  } {o t t f f s s e n t}
  do_test $tn.4.2.3 {
    execsql {
      COMMIT;
      INSERT INTO t3 VALUES('e', 't');
      BEGIN;
    }
    list [catch {snapshot_open db main $snapshot} msg] $msg
  } {1 SQLITE_BUSY_SNAPSHOT}
  do_test $tn.4.2.4 {
    snapshot_free $snapshot
  } {}

  #-------------------------------------------------------------------------
  # Check that SQLITE_BUSY is returned if a checkpoint is running when
  # sqlite3_snapshot_open() is called.
  #
  reset_db
  db close
  testvfs tvfs
  sqlite3 db test.db -vfs tvfs

  do_execsql_test $tn.5.1 {
    PRAGMA journal_mode = wal;
    CREATE TABLE x1(x, xx, xxx);
    INSERT INTO x1 VALUES('z', 'zz', 'zzz');
    BEGIN;
      SELECT * FROM x1;
  } {wal z zz zzz}

  do_test $tn.5.2 {
    set ::snapshot [snapshot_get db main]
    sqlite3 db2 test.db -vfs tvfs
    execsql {
      INSERT INTO x1 VALUES('a', 'aa', 'aaa');
      COMMIT;
    }
  } {}

  set t53 0
  proc write_callback {args} {
    do_test $tn.5.3.[incr ::t53] {
      execsql BEGIN
      list [catch { snapshot_open db main $::snapshot } msg] $msg
    } {1 SQLITE_BUSY}
    catchsql COMMIT
  }

  tvfs filter xWrite
  tvfs script write_callback
  db2 eval { PRAGMA wal_checkpoint }
  db close
  db2 close
  tvfs delete
  snapshot_free $snapshot

  #-------------------------------------------------------------------------
  # Test that sqlite3_snapshot_get() may be called immediately after
  # "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may
  # be called after opening the db handle and running the script
  # "PRAGMA user_version; BEGIN".
  reset_db
  do_execsql_test $tn.6.1 {
    PRAGMA journal_mode = wal;
    CREATE TABLE x1(x, xx, xxx);
    INSERT INTO x1 VALUES('z', 'zz', 'zzz');
    BEGIN;
      PRAGMA user_version;
  } {wal 0}
  do_test $tn.6.2 {
    set ::snapshot [snapshot_get db main]
    execsql {
      INSERT INTO x1 VALUES('a', 'aa', 'aaa');
      COMMIT;
    }
  } {}
  do_test $tn.6.3 {
    sqlite3 db2 test.db 
    db2 eval "PRAGMA user_version ; BEGIN"
    snapshot_open db2 main $::snapshot
    db2 eval { SELECT * FROM x1 }
  } {z zz zzz}
  do_test $tn.6.4 {
    db2 close
    sqlite3 db2 test.db 
    db2 eval "PRAGMA application_id"
    db2 eval "BEGIN"
    snapshot_open db2 main $::snapshot
    db2 eval { SELECT * FROM x1 }
  } {z zz zzz}

  do_test $tn.6.5 {
    db2 close
    sqlite3 db2 test.db 
    db2 eval "BEGIN"
    list [catch {snapshot_open db2 main $::snapshot} msg] $msg
  } {1 SQLITE_ERROR}

  snapshot_free $snapshot

  #-------------------------------------------------------------------------
  # The following tests investigate the sqlite3_snapshot_cmp() API.
  #

  # Compare snapshots $p1 and $p2, checking that the result is $r.
  #
  proc do_snapshot_cmp_test {tn p1 p2 r} {
    uplevel [list do_test $tn.1 [list snapshot_cmp $p1 $p2] $r]
    uplevel [list do_test $tn.2 [list snapshot_cmp $p2 $p1] [expr $r*-1]]
    uplevel [list do_test $tn.3 [list snapshot_cmp $p1 $p1] 0]
    uplevel [list do_test $tn.4 [list snapshot_cmp $p2 $p2] 0]
  }

  catch { db2 close }
  reset_db

  do_execsql_test $tn.7.1 {
    PRAGMA journal_mode = wal;
    CREATE TABLE t1(x);
  } wal

  do_test $tn.7.1.2 {
    execsql { BEGIN ; PRAGMA application_id }
    set p1 [snapshot_get db main]
    execsql {
      INSERT INTO t1 VALUES(10);
      COMMIT;
    }
    execsql { BEGIN ; PRAGMA application_id }
    set p2 [snapshot_get db main]
    execsql COMMIT
  } {}

  do_snapshot_cmp_test $tn.7.1.3 $p1 $p2 -1
  snapshot_free $p1
  snapshot_free $p2

  do_execsql_test $tn.7.2.1 {
    INSERT INTO t1 VALUES(11);
    INSERT INTO t1 VALUES(12);
    INSERT INTO t1 VALUES(13);
    BEGIN; 
      PRAGMA application_id;
  } {0}
  do_test $tn.7.2.2 {
    set p1 [snapshot_get db main]
    execsql {
      COMMIT;
      INSERT INTO t1 VALUES(14);
      PRAGMA wal_checkpoint;
      BEGIN;
        PRAGMA application_id;
    }
    set p2 [snapshot_get db main]
    execsql COMMIT
  } {}

  do_snapshot_cmp_test $tn.7.2.3 $p1 $p2 -1
  snapshot_free $p2

  do_test $tn.7.3.1 {
    execsql {
      INSERT INTO t1 VALUES(14);
      BEGIN;
        PRAGMA application_id;
    }
    set p2 [snapshot_get db main]
    execsql COMMIT
  } {}

  do_snapshot_cmp_test $tn.7.3.2 $p1 $p2 -1
  snapshot_free $p1
  snapshot_free $p2
}

finish_test