/ Check-in [5d0a9d4c]
Login

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

Overview
Comment:Update the sqldiff utility so that if the --vtab switch is specified "rtree", "fts3", "fts4" and "fts5" tables are diff'd directly and the underlying real database tables ignored. Without this switch, all virtual tables are ignored and the diff is performed on the underlying real tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sqldiff-vtab-support
Files: files | file ages | folders
SHA1: 5d0a9d4c45730e47576bc77373fa7075a74051a5
User & Date: dan 2016-06-21 10:34:41
Context
2016-06-23
16:48
Update the sqldiff utility so that if the --vtab switch is specified "rtree", "fts3", "fts4" and "fts5" tables are diff'd directly and the underlying real database tables ignored. Without this switch, all virtual tables are ignored and the diff is performed on the underlying real tables. check-in: b8671e94 user: dan tags: trunk
2016-06-21
10:34
Update the sqldiff utility so that if the --vtab switch is specified "rtree", "fts3", "fts4" and "fts5" tables are diff'd directly and the underlying real database tables ignored. Without this switch, all virtual tables are ignored and the diff is performed on the underlying real tables. Closed-Leaf check-in: 5d0a9d4c user: dan tags: sqldiff-vtab-support
2016-06-20
17:25
For a table on the rhs of a LEFT JOIN operator, do not include terms like "IS NULL" from the WHERE clause in the cursor-hint. These may be false for rows that the cursor would otherwise visit, but true for a row of all NULL values generated by the LEFT JOIN. check-in: 913e5956 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbudiff.test.

20
21
22
23
24
25
26




27
28
29
30
31
32
33
..
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
..
67
68
69
70
71
72
73

74

75
76
77
78
79
80
81
82
83
...
153
154
155
156
157
158
159
160
161




















































162
163

set PROG [test_find_sqldiff]
db close

proc get_rbudiff_sql {db1 db2} {
  exec $::PROG --rbu $db1 $db2
}





proc step_rbu {target rbu} {
  while 1 {
    sqlite3rbu rbu $target $rbu
    set rc [rbu step]
    rbu close
    if {$rc != "SQLITE_OK"} break
................................................................................
  test_rbucount $sql
  forcedelete rbu.db
  sqlite3 rbudb rbu.db
  rbudb eval $sql
  rbudb close
  step_rbu $target rbu.db
}






# The only argument is the output of an [sqldiff -rbu] run. This command
# tests that the contents of the rbu_count table is correct. An exception
# is thrown if it is not.
#
proc test_rbucount {sql} {
  sqlite3 tmpdb ""
  tmpdb eval $sql
  tmpdb eval {
    SELECT name FROM sqlite_master WHERE name LIKE 'data%' AND type='table'
  } {
    set a [tmpdb eval "SELECT count(*) FROM $name"]
    set b [tmpdb eval {SELECT cnt FROM rbu_count WHERE tbl = $name}]
    if {$a != $b} { 
      tmpdb close
      error "rbu_count error - tbl = $name" 
    }
  }
  tmpdb close
................................................................................

proc rbudiff_cksum {db1} {
  set txt ""

  sqlite3 dbtmp $db1
  foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    set cols [list]

    dbtmp eval "PRAGMA table_info = $tbl" { lappend cols "quote( $name )" }

    append txt [dbtmp eval \
      "SELECT [join $cols {||'.'||}] FROM $tbl ORDER BY 1"
    ]
  }
  dbtmp close

  md5 $txt
}

................................................................................
  db close

  do_test 1.$tn.4 {
    set sql [get_rbudiff_sql test.db test.db2]
    apply_rbudiff $sql test.db
  } {SQLITE_DONE}
  do_test 1.$tn.5 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
}





















































finish_test








>
>
>
>







 







>
>
>
>
>











|







 







>
|
>

|







 









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


20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
..
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
..
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
...
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

set PROG [test_find_sqldiff]
db close

proc get_rbudiff_sql {db1 db2} {
  exec $::PROG --rbu $db1 $db2
}

proc get_vtab_rbudiff_sql {db1 db2} {
  exec $::PROG --vtab --rbu $db1 $db2
}

proc step_rbu {target rbu} {
  while 1 {
    sqlite3rbu rbu $target $rbu
    set rc [rbu step]
    rbu close
    if {$rc != "SQLITE_OK"} break
................................................................................
  test_rbucount $sql
  forcedelete rbu.db
  sqlite3 rbudb rbu.db
  rbudb eval $sql
  rbudb close
  step_rbu $target rbu.db
}

proc sqlesc {id} {
  set ret "'[string map {' ''} $id]'"
  set ret
}

# The only argument is the output of an [sqldiff -rbu] run. This command
# tests that the contents of the rbu_count table is correct. An exception
# is thrown if it is not.
#
proc test_rbucount {sql} {
  sqlite3 tmpdb ""
  tmpdb eval $sql
  tmpdb eval {
    SELECT name FROM sqlite_master WHERE name LIKE 'data%' AND type='table'
  } {
    set a [tmpdb eval "SELECT count(*) FROM [sqlesc $name]"]
    set b [tmpdb eval {SELECT cnt FROM rbu_count WHERE tbl = $name}]
    if {$a != $b} { 
      tmpdb close
      error "rbu_count error - tbl = $name" 
    }
  }
  tmpdb close
................................................................................

proc rbudiff_cksum {db1} {
  set txt ""

  sqlite3 dbtmp $db1
  foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    set cols [list]
    dbtmp eval "PRAGMA table_info = [sqlesc $tbl]" { 
      lappend cols "quote( $name )" 
    }
    append txt [dbtmp eval \
      "SELECT [join $cols {||'.'||}] FROM [sqlesc $tbl] ORDER BY 1"
    ]
  }
  dbtmp close

  md5 $txt
}

................................................................................
  db close

  do_test 1.$tn.4 {
    set sql [get_rbudiff_sql test.db test.db2]
    apply_rbudiff $sql test.db
  } {SQLITE_DONE}
  do_test 1.$tn.5 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
}

#-------------------------------------------------------------------------
# Test that if the --vtab switch is present, [sqldiff] handles virtual
# table types fts[345] and rtree correctly.
#
ifcapable fts3&&fts5&&rtree {

foreach {tn init mod} {
  1 {
    CREATE VIRTUAL TABLE t1 USING fts5(c);
    INSERT INTO t1 VALUES('a b c');
    INSERT INTO t1 VALUES('a b c');
  } {
    DELETE FROM t1 WHERE rowid = 1;
    INSERT INTO t1 VALUES('a b c');
  }

  2 {
    CREATE VIRTUAL TABLE "x y" USING 'rtree'(id, x1, x2);
    INSERT INTO "x y" VALUES(1, 2, 3);
    INSERT INTO "x y" VALUES(2, 4, 6);
  } {
    DELETE FROM "x y" WHERE rowid = 1;
    INSERT INTO "x y" VALUES(3, 6, 9);
  }

  3 {
    CREATE VIRTUAL TABLE 'x''y' USING fts3;
    INSERT INTO 'x''y' VALUES('one two three');
    INSERT INTO 'x''y' VALUES('four five six');
  } {
    DELETE FROM 'x''y' WHERE rowid = 1;
    INSERT INTO 'x''y' VALUES('one two three');
  }

} {

  forcedelete test.db test.db2
  sqlite3 db test.db
  db eval "$init"
  sqlite3 db test.db2
  db eval "$init ; $mod"
  db close

  do_test 2.$tn.1 {
    set sql [get_vtab_rbudiff_sql test.db test.db2]
    apply_rbudiff $sql test.db
  } {SQLITE_DONE}
  do_test 2.$tn.2 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
}

}

finish_test

Changes to tool/sqldiff.c.

29
30
31
32
33
34
35

36
37
38
39
40
41
42
....
1730
1731
1732
1733
1734
1735
1736










































































































































1737
1738
1739
1740
1741
1742
1743
....
1747
1748
1749
1750
1751
1752
1753

1754
1755
1756
1757
1758
1759
1760
....
1817
1818
1819
1820
1821
1822
1823



1824
1825
1826
1827
1828
1829
1830
....
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
/*
** All global variables are gathered into the "g" singleton.
*/
struct GlobalVars {
  const char *zArgv0;       /* Name of program */
  int bSchemaOnly;          /* Only show schema differences */
  int bSchemaPK;            /* Use the schema-defined PK, not the true PK */

  unsigned fDebug;          /* Debug flags */
  sqlite3 *db;              /* The database connection */
} g;

/*
** Allowed values for g.fDebug
*/
................................................................................
  
end_changeset_one_table:
  while( nCol>0 ) sqlite3_free(azCol[--nCol]);
  sqlite3_free(azCol);
  sqlite3_free(aiPk);
  sqlite3_free(zId);
}











































































































































/*
** Print sketchy documentation for this utility program
*/
static void showHelp(void){
  printf("Usage: %s [options] DB1 DB2\n", g.zArgv0);
  printf(
................................................................................
"  -L|--lib LIBRARY      Load an SQLite extension library\n"
"  --primarykey          Use schema-defined PRIMARY KEYs\n"
"  --rbu                 Output SQL to create/populate RBU table(s)\n"
"  --schema              Show only differences in the schema\n"
"  --summary             Show only a summary of the differences\n"
"  --table TAB           Show only differences in table TAB\n"
"  --transaction         Show SQL output inside a transaction\n"

  );
}

int main(int argc, char **argv){
  const char *zDb1 = 0;
  const char *zDb2 = 0;
  int i;
................................................................................
      if( strcmp(z,"table")==0 ){
        if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]);
        zTab = argv[++i];
      }else
      if( strcmp(z,"transaction")==0 ){
        useTransaction = 1;
      }else



      {
        cmdlineError("unknown option: %s", argv[i]);
      }
    }else if( zDb1==0 ){
      zDb1 = argv[i];
    }else if( zDb2==0 ){
      zDb2 = argv[i];
................................................................................
           "WITHOUT ROWID;\n"
    );
  }
  if( zTab ){
    xDiff(zTab, out);
  }else{
    /* Handle tables one by one */
    pStmt = db_prepare(
      "SELECT name FROM main.sqlite_master\n"
      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
      " UNION\n"
      "SELECT name FROM aux.sqlite_master\n"
      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
      " ORDER BY name"
    );
    while( SQLITE_ROW==sqlite3_step(pStmt) ){
      xDiff((const char*)sqlite3_column_text(pStmt,0), out);
    }
    sqlite3_finalize(pStmt);
  }
  if( useTransaction ) printf("COMMIT;\n");

  /* TBD: Handle trigger differences */
  /* TBD: Handle view differences */
  sqlite3_close(g.db);
  return 0;
}







>







 







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







 







>







 







>
>
>







 







|
<
<
<
<
<
<
<












29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
....
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
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
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
....
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
....
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
....
2014
2015
2016
2017
2018
2019
2020
2021







2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
/*
** All global variables are gathered into the "g" singleton.
*/
struct GlobalVars {
  const char *zArgv0;       /* Name of program */
  int bSchemaOnly;          /* Only show schema differences */
  int bSchemaPK;            /* Use the schema-defined PK, not the true PK */
  int bHandleVtab;          /* Handle fts3, fts4, fts5 and rtree vtabs */
  unsigned fDebug;          /* Debug flags */
  sqlite3 *db;              /* The database connection */
} g;

/*
** Allowed values for g.fDebug
*/
................................................................................
  
end_changeset_one_table:
  while( nCol>0 ) sqlite3_free(azCol[--nCol]);
  sqlite3_free(azCol);
  sqlite3_free(aiPk);
  sqlite3_free(zId);
}

/*
** Extract the next SQL keyword or quoted string from buffer zIn and copy it
** (or a prefix of it if it will not fit) into buffer zBuf, size nBuf bytes.
** Return a pointer to the character within zIn immediately following 
** the token or quoted string just extracted.
*/
const char *gobble_token(const char *zIn, char *zBuf, int nBuf){
  const char *p = zIn;
  char *pOut = zBuf;
  char *pEnd = &pOut[nBuf-1];
  char q = 0;                     /* quote character, if any */

  if( p==0 ) return 0;
  while( *p==' ' ) p++;
  switch( *p ){
    case '"': q = '"'; break;
    case '\'': q = '\''; break;
    case '`': q = '`'; break;
    case '[': q = ']'; break;
  }

  if( q ){
    p++;
    while( *p && pOut<pEnd ){
      if( *p==q ){
        p++;
        if( *p!=q ) break;
      }
      if( pOut<pEnd ) *pOut++ = *p;
      p++;
    }
  }else{
    while( *p && *p!=' ' && *p!='(' ){
      if( pOut<pEnd ) *pOut++ = *p;
      p++;
    }
  }

  *pOut = '\0';
  return p;
}

/*
** This function is the implementation of SQL scalar function "module_name":
**
**   module_name(SQL)
**
** The only argument should be an SQL statement of the type that may appear
** in the sqlite_master table. If the statement is a "CREATE VIRTUAL TABLE"
** statement, then the value returned is the name of the module that it
** uses. Otherwise, if the statement is not a CVT, NULL is returned.
*/
static void module_name_func(
  sqlite3_context *pCtx, 
  int nVal, sqlite3_value **apVal
){
  const char *zSql;
  char zToken[32];

  assert( nVal==1 );
  zSql = (const char*)sqlite3_value_text(apVal[0]);

  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  if( zSql==0 || sqlite3_stricmp(zToken, "create") ) return;
  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  if( zSql==0 || sqlite3_stricmp(zToken, "virtual") ) return;
  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  if( zSql==0 || sqlite3_stricmp(zToken, "table") ) return;
  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  if( zSql==0 ) return;
  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  if( zSql==0 || sqlite3_stricmp(zToken, "using") ) return;
  zSql = gobble_token(zSql, zToken, sizeof(zToken));
  
  sqlite3_result_text(pCtx, zToken, -1, SQLITE_TRANSIENT);
}

/*
** Return the text of an SQL statement that itself returns the list of
** tables to process within the database.
*/
const char *all_tables_sql(){
  if( g.bHandleVtab ){
    int rc;
  
    rc = sqlite3_exec(g.db, 
        "CREATE TEMP TABLE tblmap(module, postfix);"
        "INSERT INTO temp.tblmap VALUES"
        "('fts3', '_content'), ('fts3', '_segments'), ('fts3', '_segdir'),"
  
        "('fts4', '_content'), ('fts4', '_segments'), ('fts4', '_segdir'),"
        "('fts4', '_docsize'), ('fts4', '_stat'),"
  
        "('fts5', '_data'), ('fts5', '_idx'), ('fts5', '_content'),"
        "('fts5', '_docsize'), ('fts5', '_config'),"
  
        "('rtree', '_node'), ('rtree', '_rowid'), ('rtree', '_parent');"
        , 0, 0, 0
    );
    assert( rc==SQLITE_OK );
  
    rc = sqlite3_create_function(
        g.db, "module_name", 1, SQLITE_UTF8, 0, module_name_func, 0, 0
    );
    assert( rc==SQLITE_OK );
  
    return 
      "SELECT name FROM main.sqlite_master\n"
      " WHERE type='table' AND (\n"
      "    module_name(sql) IS NULL OR \n"
      "    module_name(sql) IN (SELECT module FROM temp.tblmap)\n"
      " ) AND name NOT IN (\n"
      "  SELECT a.name || b.postfix \n"
        "FROM main.sqlite_master AS a, temp.tblmap AS b \n"
        "WHERE module_name(a.sql) = b.module\n" 
      " )\n"
      "UNION \n"
      "SELECT name FROM aux.sqlite_master\n"
      " WHERE type='table' AND (\n"
      "    module_name(sql) IS NULL OR \n"
      "    module_name(sql) IN (SELECT module FROM temp.tblmap)\n"
      " ) AND name NOT IN (\n"
      "  SELECT a.name || b.postfix \n"
        "FROM aux.sqlite_master AS a, temp.tblmap AS b \n"
        "WHERE module_name(a.sql) = b.module\n" 
      " )\n"
      " ORDER BY name";
  }else{
    return
      "SELECT name FROM main.sqlite_master\n"
      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
      " UNION\n"
      "SELECT name FROM aux.sqlite_master\n"
      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
      " ORDER BY name";
  }
}

/*
** Print sketchy documentation for this utility program
*/
static void showHelp(void){
  printf("Usage: %s [options] DB1 DB2\n", g.zArgv0);
  printf(
................................................................................
"  -L|--lib LIBRARY      Load an SQLite extension library\n"
"  --primarykey          Use schema-defined PRIMARY KEYs\n"
"  --rbu                 Output SQL to create/populate RBU table(s)\n"
"  --schema              Show only differences in the schema\n"
"  --summary             Show only a summary of the differences\n"
"  --table TAB           Show only differences in table TAB\n"
"  --transaction         Show SQL output inside a transaction\n"
"  --vtab                Handle fts3, fts4, fts5 and rtree tables\n"
  );
}

int main(int argc, char **argv){
  const char *zDb1 = 0;
  const char *zDb2 = 0;
  int i;
................................................................................
      if( strcmp(z,"table")==0 ){
        if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]);
        zTab = argv[++i];
      }else
      if( strcmp(z,"transaction")==0 ){
        useTransaction = 1;
      }else
      if( strcmp(z,"vtab")==0 ){
        g.bHandleVtab = 1;
      }else
      {
        cmdlineError("unknown option: %s", argv[i]);
      }
    }else if( zDb1==0 ){
      zDb1 = argv[i];
    }else if( zDb2==0 ){
      zDb2 = argv[i];
................................................................................
           "WITHOUT ROWID;\n"
    );
  }
  if( zTab ){
    xDiff(zTab, out);
  }else{
    /* Handle tables one by one */
    pStmt = db_prepare( all_tables_sql() );







    while( SQLITE_ROW==sqlite3_step(pStmt) ){
      xDiff((const char*)sqlite3_column_text(pStmt,0), out);
    }
    sqlite3_finalize(pStmt);
  }
  if( useTransaction ) printf("COMMIT;\n");

  /* TBD: Handle trigger differences */
  /* TBD: Handle view differences */
  sqlite3_close(g.db);
  return 0;
}