Index: ext/rbu/rbudiff.test ================================================================== --- ext/rbu/rbudiff.test +++ ext/rbu/rbudiff.test @@ -51,50 +51,79 @@ rbudb close step_rbu $target rbu.db } proc rbudiff_cksum {db1} { + set txt "" + sqlite3 dbtmp $db1 - set txt [dbtmp eval { - SELECT a || '.' || b || '.' || c FROM t1 ORDER BY 1; - SELECT a || '.' || b || '.' || c FROM t2 ORDER BY 1; - }] + foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] { + append txt [dbtmp eval \ + "SELECT a || '.' || b || '.' || c FROM $tbl ORDER BY 1" + ] + } dbtmp close + md5 $txt } -sqlite3 db test.db -do_execsql_test 1.0 { - CREATE TABLE t1(a PRIMARY KEY, b, c); - INSERT INTO t1 VALUES(1, 2, 3); - INSERT INTO t1 VALUES(4, 5, 6); - - CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c)); - INSERT INTO t2 VALUES(1, 2, 3); - INSERT INTO t2 VALUES(4, 5, 6); -} - -db close -forcedelete test.db2 -forcecopy test.db test.db2 - -sqlite3 db test.db -do_execsql_test 1.1 { - INSERT INTO t1 VALUES(7, 8, 9); - DELETE FROM t1 WHERE a=4; - UPDATE t1 SET c = 11 WHERE a = 1; - - INSERT INTO t2 VALUES(7, 8, 9); - DELETE FROM t2 WHERE a=4; - UPDATE t2 SET c = 11 WHERE a = 1; -} -db close - -do_test 1.2 { - set sql [get_rbudiff_sql test.db test.db2] - apply_rbudiff $sql test.db -} {SQLITE_DONE} - -do_test 1.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] +foreach {tn init mod} { + 1 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + + CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c)); + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + } { + INSERT INTO t1 VALUES(7, 8, 9); + DELETE FROM t1 WHERE a=4; + UPDATE t1 SET c = 11 WHERE a = 1; + + INSERT INTO t2 VALUES(7, 8, 9); + DELETE FROM t2 WHERE a=4; + UPDATE t2 SET c = 11 WHERE a = 1; + } + + 2 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c)); + INSERT INTO t1 VALUES('u', 'v', 'w'); + INSERT INTO t1 VALUES('x', 'y', 'z'); + } { + DELETE FROM t1 WHERE a='u'; + INSERT INTO t1 VALUES('a', 'b', 'c'); + } + +} { + + catch { db close } + + forcedelete test.db test.db2 + sqlite3 db test.db + db eval "$init" + sqlite3 db test.db2 + db eval "$init ; $mod" + db close + + do_test 1.$tn.2 { + set sql [get_rbudiff_sql test.db test.db2] + apply_rbudiff $sql test.db + } {SQLITE_DONE} + do_test 1.$tn.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] + + forcedelete test.db test.db2 + sqlite3 db test.db + db eval "$init ; $mod" + sqlite3 db test.db2 + db eval "$init" + 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 Index: tool/sqldiff.c ================================================================== --- tool/sqldiff.c +++ tool/sqldiff.c @@ -787,42 +787,48 @@ strPrintf(pSql, "\n)"); /* Deleted rows: */ strPrintf(pSql, "\nUNION ALL\nSELECT "); strPrintfArray(pSql, ", ", "%s", azCol, nPK); - strPrintf(pSql, ", "); - strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1); + if( azCol[nPK] ){ + strPrintf(pSql, ", "); + strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1); + } strPrintf(pSql, ", 1"); /* Set ota_control to 1 for a delete */ strPrintf(pSql, " FROM main.%Q AS n WHERE NOT EXISTS (\n", zTab); strPrintf(pSql, " SELECT 1 FROM ", zTab); strPrintf(pSql, " aux.%Q AS o WHERE ", zTab); strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); strPrintf(pSql, "\n) "); - /* Updated rows: */ - strPrintf(pSql, "\nUNION ALL\nSELECT "); - strPrintfArray(pSql, ", ", "n.%s", azCol, nPK); - strPrintf(pSql, ",\n"); - strPrintfArray(pSql, " ,\n", - " CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1 - ); - - if( bOtaRowid==0 ){ - strPrintf(pSql, ", '"); - strPrintfArray(pSql, "", ".", azCol, nPK); - strPrintf(pSql, "' ||\n"); - }else{ - strPrintf(pSql, ",\n"); - } - strPrintfArray(pSql, " ||\n", - " CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1 - ); - strPrintf(pSql, "\nAS ota_control"); - - strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab); - strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); - strPrintf(pSql, " AND ota_control LIKE '%%x%%'"); + /* Updated rows. If all table columns are part of the primary key, there + ** can be no updates. In this case this part of the compound SELECT can + ** be omitted altogether. */ + if( azCol[nPK] ){ + strPrintf(pSql, "\nUNION ALL\nSELECT "); + strPrintfArray(pSql, ", ", "n.%s", azCol, nPK); + strPrintf(pSql, ",\n"); + strPrintfArray(pSql, " ,\n", + " CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1 + ); + + if( bOtaRowid==0 ){ + strPrintf(pSql, ", '"); + strPrintfArray(pSql, "", ".", azCol, nPK); + strPrintf(pSql, "' ||\n"); + }else{ + strPrintf(pSql, ",\n"); + } + strPrintfArray(pSql, " ||\n", + " CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1 + ); + strPrintf(pSql, "\nAS ota_control"); + + strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab); + strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); + strPrintf(pSql, " AND ota_control LIKE '%%x%%'"); + } /* Now add an ORDER BY clause to sort everything by PK. */ strPrintf(pSql, "\nORDER BY "); for(i=1; i<=nPK; i++) strPrintf(pSql, "%s%d", ((i>1)?", ":""), i); }