Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch sqldiff-vtab-support Excluding Merge-Ins
This is equivalent to a diff from 913e5956 to 5d0a9d4c
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) | |
12:35 | Fix the build for -DSQLITE_OMIT_VIRTUALTABLE (check-in: 91113142 user: drh 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) | |
17:22 | Allow LIKE operators that appear in a WHERE clause to be included in the cursor-hint for a cursor on the rhs of a LEFT JOIN. (Closed-Leaf check-in: 7455d932 user: dan tags: cursor-hints) | |
2016-06-16
| ||
17:14 | Add a missing OP_ColumnsUsed opcode to code for expressions like "? IN (SELECT ...)" in cases where expression can use an index that may contain NULL values. (check-in: 0b1579ca user: dan tags: trunk) | |
Changes to ext/rbu/rbudiff.test.
︙ | ︙ | |||
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 | 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 } set rc } proc apply_rbudiff {sql target} { 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' } { | > > > > > > > > > | | > > | | 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 | 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 } set rc } proc apply_rbudiff {sql target} { 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 return "" } 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 } |
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 162 163 | 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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 | /* ** 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 */ | > | 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | /* ** 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 */ |
︙ | ︙ | |||
1730 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 | 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( "Output SQL text that would transform DB1 into DB2.\n" "Options:\n" " --changeset FILE Write a CHANGESET into FILE\n" " -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; | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 | 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( "Output SQL text that would transform DB1 into DB2.\n" "Options:\n" " --changeset FILE Write a CHANGESET into FILE\n" " -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; |
︙ | ︙ | |||
1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 | 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]; | > > > | 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 | 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]; |
︙ | ︙ | |||
1871 1872 1873 1874 1875 1876 1877 | "WITHOUT ROWID;\n" ); } if( zTab ){ xDiff(zTab, out); }else{ /* Handle tables one by one */ | | < < < < < < < | 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 | "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; } |