/ Check-in [7250318d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix some bugs in sqlite3changeset_apply().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 7250318dda542c5bbf28852c1f1d0f3c52ae8f96
User & Date: dan 2011-03-19 15:37:02
Context
2011-03-19
16:26
Fix a problem with resizing a hash table in sqlite3session.c. check-in: 6e5907e1 user: dan tags: sessions
15:37
Fix some bugs in sqlite3changeset_apply(). check-in: 7250318d user: dan tags: sessions
08:38
Fix a problem with INTEGER PRIMARY KEY columns and the pre-update hook. check-in: 24d4d5dd user: dan tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added ext/session/session2.test.





















































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
# 2011 Mar 16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The focus of this file is testing the session module.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source [file join [file dirname [info script]] session_common.tcl]
source $testdir/tester.tcl

set testprefix session2

proc test_reset {} {
  catch { db close }
  catch { db2 close }
  forcedelete test.db test.db2
  sqlite3 db test.db
  sqlite3 db2 test.db2
}

proc do_common_sql {sql} {
  execsql $sql db
  execsql $sql db2
}
proc xConflict args { return "OMIT" }

proc do_then_apply_sql {sql} {
  sqlite3session S db main
  db eval {SELECT name FROM sqlite_master WHERE type = 'table'} {
    S attach $name
  }

  db eval $sql
  sqlite3changeset_apply db2 [S changeset] xConflict
  S delete
}

proc do_iterator_test {tn tbl_list sql res} {
  sqlite3session S db main
  foreach t $tbl_list {S attach $t}
  execsql $sql

  set r [list]
  foreach v $res { lappend r $v }

  set x [list]
  sqlite3session_foreach c [S changeset] { lappend x $c }
  uplevel do_test $tn [list [list set {} $x]] [list $r]

  S delete
}

# Compare the contents of all tables in [db1] and [db2]. Throw an error if 
# they are not identical, or return an empty string if they are.
#
proc compare_db {db1 db2} {

  set sql {SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name}
  set lot1 [$db1 eval $sql]
  set lot2 [$db2 eval $sql]

  if {$lot1 != $lot2} { error "databases contain different tables" }

  foreach tbl $lot1 {
    set col1 [list]
    set col2 [list]

    $db1 eval "PRAGMA table_info = $tbl" { lappend col1 $name }
    $db2 eval "PRAGMA table_info = $tbl" { lappend col2 $name }
    if {$col1 != $col2} { error "table $tbl schema mismatch" }

    set sql "SELECT * FROM $tbl ORDER BY [join $col1 ,]"
    set data1 [$db1 eval $sql]
    set data2 [$db2 eval $sql]
    if {$data1 != $data2} { error "table $tbl data mismatch" }
  }

  return ""
}

##########################################################################
# End of proc definitions. Start of tests.
##########################################################################

test_reset
do_execsql_test 1.0 { 
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES('i', 'one');
}
do_iterator_test 1.1 t1 {
  DELETE FROM t1 WHERE a = 'i';
  INSERT INTO t1 VALUES('ii', 'two');
} {
  {DELETE t1 {t i t one} {}} 
  {INSERT t1 {} {t ii t two}}
}

test_reset
do_common_sql {
  CREATE TABLE t1(a PRIMARY KEY, b);
  CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
}

foreach {tn sql} {
  1 { INSERT INTO t1 VALUES(1, 2) } 
  2 {
    INSERT INTO t2 VALUES(1, NULL);
    INSERT INTO t2 VALUES(2, NULL);
    INSERT INTO t2 VALUES(3, NULL);
    DELETE FROM t2 WHERE a = 2;
    INSERT INTO t2 VALUES(4, NULL);
    UPDATE t2 SET b=0 WHERE b=1;
  } 
  3 { INSERT INTO t3 SELECT *, NULL FROM t2 }
  4 {
    INSERT INTO t3 SELECT a||a, b||b, NULL FROM t3;
    DELETE FROM t3 WHERE rowid%2;
  }
  5 { UPDATE t3 SET c = a||b }
  6 { UPDATE t1 SET a = 32 }
} {
  do_then_apply_sql $sql
  do_test $tn { compare_db db db2 } {}
}

finish_test

Changes to ext/session/sqlite3session.c.

1773
1774
1775
1776
1777
1778
1779

1780
1781
1782
1783
1784
1785
1786

1787
1788
1789
1790
1791
1792
1793
1794

1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809

1810
1811
1812
1813
1814
1815
1816
....
2167
2168
2169
2170
2171
2172
2173

2174

2175
2176
2177
2178
2179
2180
2181
  const char *zTab,               /* Table name */
  SessionApplyCtx *p              /* Session changeset-apply context */
){
  int i;
  const char *zSep = "";
  int rc = SQLITE_OK;
  SessionBuffer buf = {0, 0, 0};


  sessionAppendStr(&buf, "DELETE FROM ", &rc);
  sessionAppendIdent(&buf, zTab, &rc);
  sessionAppendStr(&buf, " WHERE ", &rc);

  for(i=0; i<p->nCol; i++){
    if( p->abPK[i] ){

      sessionAppendStr(&buf, zSep, &rc);
      sessionAppendIdent(&buf, p->azCol[i], &rc);
      sessionAppendStr(&buf, " = ?", &rc);
      sessionAppendInteger(&buf, i+1, &rc);
      zSep = "AND ";
    }
  }


  sessionAppendStr(&buf, " AND (?", &rc);
  sessionAppendInteger(&buf, p->nCol+1, &rc);
  sessionAppendStr(&buf, " OR ", &rc);

  zSep = "";
  for(i=0; i<p->nCol; i++){
    if( !p->abPK[i] ){
      sessionAppendStr(&buf, zSep, &rc);
      sessionAppendIdent(&buf, p->azCol[i], &rc);
      sessionAppendStr(&buf, " IS ?", &rc);
      sessionAppendInteger(&buf, i+1, &rc);
      zSep = "AND ";
    }
  }
  sessionAppendStr(&buf, ")", &rc);


  if( rc==SQLITE_OK ){
    rc = sqlite3_prepare_v2(db, (char *)buf.aBuf, buf.nBuf, &p->pDelete, 0);
  }
  sqlite3_free(buf.aBuf);

  return rc;
................................................................................
    for(i=0; rc==SQLITE_OK && i<nCol; i++){
      sqlite3_value *pVal;
      rc = sqlite3changeset_old(pIter, i, &pVal);
      if( rc==SQLITE_OK ){
        rc = sqlite3_bind_value(p->pDelete, i+1, pVal);
      }
    }

    if( rc==SQLITE_OK ) rc = sqlite3_bind_int(p->pDelete, nCol+1, pbRetry==0);

    if( rc!=SQLITE_OK ) return rc;

    sqlite3_step(p->pDelete);
    rc = sqlite3_reset(p->pDelete);
    if( rc==SQLITE_OK && sqlite3_changes(p->db)==0 ){
      rc = sessionConflictHandler(
          SQLITE_CHANGESET_DATA, p, pIter, xConflict, pCtx, pbRetry







>







>




|



>
|
|
|

|
|
|
|
|
|
|
|
|
|
|
>







 







>
|
>







1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
....
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
  const char *zTab,               /* Table name */
  SessionApplyCtx *p              /* Session changeset-apply context */
){
  int i;
  const char *zSep = "";
  int rc = SQLITE_OK;
  SessionBuffer buf = {0, 0, 0};
  int nPk = 0;

  sessionAppendStr(&buf, "DELETE FROM ", &rc);
  sessionAppendIdent(&buf, zTab, &rc);
  sessionAppendStr(&buf, " WHERE ", &rc);

  for(i=0; i<p->nCol; i++){
    if( p->abPK[i] ){
      nPk++;
      sessionAppendStr(&buf, zSep, &rc);
      sessionAppendIdent(&buf, p->azCol[i], &rc);
      sessionAppendStr(&buf, " = ?", &rc);
      sessionAppendInteger(&buf, i+1, &rc);
      zSep = " AND ";
    }
  }

  if( nPk<p->nCol ){
    sessionAppendStr(&buf, " AND (?", &rc);
    sessionAppendInteger(&buf, p->nCol+1, &rc);
    sessionAppendStr(&buf, " OR ", &rc);

    zSep = "";
    for(i=0; i<p->nCol; i++){
      if( !p->abPK[i] ){
        sessionAppendStr(&buf, zSep, &rc);
        sessionAppendIdent(&buf, p->azCol[i], &rc);
        sessionAppendStr(&buf, " IS ?", &rc);
        sessionAppendInteger(&buf, i+1, &rc);
        zSep = "AND ";
      }
    }
    sessionAppendStr(&buf, ")", &rc);
  }

  if( rc==SQLITE_OK ){
    rc = sqlite3_prepare_v2(db, (char *)buf.aBuf, buf.nBuf, &p->pDelete, 0);
  }
  sqlite3_free(buf.aBuf);

  return rc;
................................................................................
    for(i=0; rc==SQLITE_OK && i<nCol; i++){
      sqlite3_value *pVal;
      rc = sqlite3changeset_old(pIter, i, &pVal);
      if( rc==SQLITE_OK ){
        rc = sqlite3_bind_value(p->pDelete, i+1, pVal);
      }
    }
    if( rc==SQLITE_OK && sqlite3_bind_parameter_count(p->pDelete)>nCol ){
      rc = sqlite3_bind_int(p->pDelete, nCol+1, pbRetry==0);
    }
    if( rc!=SQLITE_OK ) return rc;

    sqlite3_step(p->pDelete);
    rc = sqlite3_reset(p->pDelete);
    if( rc==SQLITE_OK && sqlite3_changes(p->db)==0 ){
      rc = sessionConflictHandler(
          SQLITE_CHANGESET_DATA, p, pIter, xConflict, pCtx, pbRetry

Changes to src/test_config.c.

399
400
401
402
403
404
405






406
407
408
409
410
411
412
#endif

#ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_ENABLE_STAT2
  Tcl_SetVar2(interp, "sqlite_options", "stat2", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat2", "0", TCL_GLOBAL_ONLY);
#endif








>
>
>
>
>
>







399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
#endif

#ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_ENABLE_SESSION
  Tcl_SetVar2(interp, "sqlite_options", "session", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "session", "0", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_ENABLE_STAT2
  Tcl_SetVar2(interp, "sqlite_options", "stat2", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat2", "0", TCL_GLOBAL_ONLY);
#endif

Changes to test/permutations.test.

85
86
87
88
89
90
91



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
...
758
759
760
761
762
763
764




765
766
767
768
769
770
771
#   $allquicktests
#
set alltests [list]
foreach f [glob $testdir/*.test] { lappend alltests [file tail $f] }
foreach f [glob -nocomplain $testdir/../ext/rtree/*.test] { 
  lappend alltests $f 
}




if {$::tcl_platform(platform)!="unix"} {
  set alltests [test_set $alltests -exclude crash.test crash2.test]
}
set alltests [test_set $alltests -exclude {
  all.test        async.test         quick.test  veryquick.test
  memleak.test    permutations.test  soak.test   fts3.test
  mallocAll.test  rtree.test
}]

set allquicktests [test_set $alltests -exclude {
  async2.test async3.test backup_ioerr.test corrupt.test
  corruptC.test crash.test crash2.test crash3.test crash4.test crash5.test
  crash6.test crash7.test delete3.test e_fts3.test fts3rnd.test
  fkey_malloc.test fuzz.test fuzz3.test fuzz_malloc.test in2.test loadext.test
................................................................................
  fts3c.test   fts3d.test   fts3e.test   fts3query.test 
}

test_suite "rtree" -description {
  All R-tree related tests. Provides coverage of source file rtree.c.
} -files [glob -nocomplain $::testdir/../ext/rtree/*.test]





test_suite "no_optimization" -description {
  Run test scripts with optimizations disabled using the
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface.
} -files {
  where.test where2.test where3.test where4.test where5.test
  where6.test where7.test where8.test where9.test
  whereA.test whereB.test wherelimit.test







>
>
>







|







 







>
>
>
>







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
...
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
#   $allquicktests
#
set alltests [list]
foreach f [glob $testdir/*.test] { lappend alltests [file tail $f] }
foreach f [glob -nocomplain $testdir/../ext/rtree/*.test] { 
  lappend alltests $f 
}
foreach f [glob -nocomplain $testdir/../ext/session/*.test] { 
  lappend alltests $f 
}

if {$::tcl_platform(platform)!="unix"} {
  set alltests [test_set $alltests -exclude crash.test crash2.test]
}
set alltests [test_set $alltests -exclude {
  all.test        async.test         quick.test  veryquick.test
  memleak.test    permutations.test  soak.test   fts3.test
  mallocAll.test  rtree.test         session.test
}]

set allquicktests [test_set $alltests -exclude {
  async2.test async3.test backup_ioerr.test corrupt.test
  corruptC.test crash.test crash2.test crash3.test crash4.test crash5.test
  crash6.test crash7.test delete3.test e_fts3.test fts3rnd.test
  fkey_malloc.test fuzz.test fuzz3.test fuzz_malloc.test in2.test loadext.test
................................................................................
  fts3c.test   fts3d.test   fts3e.test   fts3query.test 
}

test_suite "rtree" -description {
  All R-tree related tests. Provides coverage of source file rtree.c.
} -files [glob -nocomplain $::testdir/../ext/rtree/*.test]

test_suite "session" -description {
  All session module related tests. 
} -files [glob -nocomplain $::testdir/../ext/session/*.test]

test_suite "no_optimization" -description {
  Run test scripts with optimizations disabled using the
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface.
} -files {
  where.test where2.test where3.test where4.test where5.test
  where6.test where7.test where8.test where9.test
  whereA.test whereB.test wherelimit.test

Added test/session.test.





































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 2008 June 23
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all rtree related tests.
#

set testdir [file dirname $argv0]
source $testdir/permutations.test

ifcapable session {
  run_test_suite session
}

finish_test