From 88d702e6bd8a8179b3ceac1ce1d55189aaebec1c Mon Sep 17 00:00:00 2001 From: dan Date: Wed, 24 Jun 2015 17:21:52 +0000 Subject: [PATCH] Add "ON CONFLICT" handling to the spellfix module. FossilOrigin-Name: 1d04def785b6031de68b7f199d400cbb5c76caea --- ext/misc/spellfix.c | 38 +++++++++++--- manifest | 19 ++++--- manifest.uuid | 2 +- test/spellfix.test | 117 ++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 161 insertions(+), 15 deletions(-) diff --git a/ext/misc/spellfix.c b/ext/misc/spellfix.c index a6f780584c..b9514427cf 100644 --- a/ext/misc/spellfix.c +++ b/ext/misc/spellfix.c @@ -2655,6 +2655,31 @@ static int spellfix1Rowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ return SQLITE_OK; } +/* +** This function is called by the xUpdate() method. It returns a string +** containing the conflict mode that xUpdate() should use for the current +** operation. One of: "ROLLBACK", "IGNORE", "ABORT" or "REPLACE". +*/ +static const char *spellfix1GetConflict(sqlite3 *db){ + static const char *azConflict[] = { + /* Note: Instead of "FAIL" - "ABORT". */ + "ROLLBACK", "IGNORE", "ABORT", "ABORT", "REPLACE" + }; + int eConflict = sqlite3_vtab_on_conflict(db); + + assert( eConflict==SQLITE_ROLLBACK || eConflict==SQLITE_IGNORE + || eConflict==SQLITE_FAIL || eConflict==SQLITE_ABORT + || eConflict==SQLITE_REPLACE + ); + assert( SQLITE_ROLLBACK==1 ); + assert( SQLITE_IGNORE==2 ); + assert( SQLITE_FAIL==3 ); + assert( SQLITE_ABORT==4 ); + assert( SQLITE_REPLACE==5 ); + + return azConflict[eConflict-1]; +} + /* ** The xUpdate() method. */ @@ -2686,6 +2711,7 @@ static int spellfix1Update( char *zK1, *zK2; int i; char c; + const char *zConflict = spellfix1GetConflict(db); if( zWord==0 ){ /* Inserts of the form: INSERT INTO table(command) VALUES('xyzzy'); @@ -2746,10 +2772,10 @@ static int spellfix1Update( }else{ newRowid = sqlite3_value_int64(argv[1]); spellfix1DbExec(&rc, db, - "INSERT INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) " - "VALUES(%lld,%d,%d,%Q,%Q,%Q)", - p->zDbName, p->zTableName, - newRowid, iRank, iLang, zWord, zK1, zK2 + "INSERT OR %s INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) " + "VALUES(%lld,%d,%d,%Q,%Q,%Q)", + zConflict, p->zDbName, p->zTableName, + newRowid, iRank, iLang, zWord, zK1, zK2 ); } *pRowid = sqlite3_last_insert_rowid(db); @@ -2757,9 +2783,9 @@ static int spellfix1Update( rowid = sqlite3_value_int64(argv[0]); newRowid = *pRowid = sqlite3_value_int64(argv[1]); spellfix1DbExec(&rc, db, - "UPDATE \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d," + "UPDATE OR %s \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d," " word=%Q, k1=%Q, k2=%Q WHERE id=%lld", - p->zDbName, p->zTableName, newRowid, iRank, iLang, + zConflict, p->zDbName, p->zTableName, newRowid, iRank, iLang, zWord, zK1, zK2, rowid ); } diff --git a/manifest b/manifest index ddf5ffe11c..2372b073fe 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C In\sthe\sfuzzcheck\stest\sprogram,\suse\sthe\sprogress\shandler\sto\slimit\sthe\snumber\nof\sVDBE\scycles\sto\savoid\sgetting\sstuck\sif\sthe\sSQL\sunder\stest\scontains\san\ninfinite\sCTE\sloop.\s\sAdd\sthe\s--limit-vdbe\scommand-line\soption. -D 2015-06-24T14:45:44.257 +C Add\s"ON\sCONFLICT"\shandling\sto\sthe\sspellfix\smodule. +D 2015-06-24T17:21:52.413 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 1063c58075b7400d93326b0eb332b48a54f53025 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -118,7 +118,7 @@ F ext/misc/percentile.c bcbee3c061b884eccb80e21651daaae8e1e43c63 F ext/misc/regexp.c af92cdaa5058fcec1451e49becc7ba44dba023dc F ext/misc/rot13.c 1ac6f95f99b575907b9b09c81a349114cf9be45a F ext/misc/showauth.c 732578f0fe4ce42d577e1c86dc89dd14a006ab52 -F ext/misc/spellfix.c 25810dda37fc904b0772a13efd8ca072fb09e355 +F ext/misc/spellfix.c de9181ec188294dd2a1087b329ca55cfaa76a29d F ext/misc/totype.c 4a167594e791abeed95e0a8db028822b5e8fe512 F ext/misc/vfslog.c fe40fab5c077a40477f7e5eba994309ecac6cc95 F ext/misc/vtshim.c babb0dc2bf116029e3e7c9a618b8a1377045303e @@ -929,7 +929,7 @@ F test/speed4.test abc0ad3399dcf9703abed2fff8705e4f8e416715 F test/speed4p.explain 6b5f104ebeb34a038b2f714150f51d01143e59aa F test/speed4p.test 0e51908951677de5a969b723e03a27a1c45db38b F test/speedtest1.c f42fd04a34a0c1dc289cbe536ef62d706227a736 -F test/spellfix.test 24f676831acddd2f4056a598fd731a72c6311f49 +F test/spellfix.test 0597065ff57042df1f138e6a2611ae19c2698135 F test/sqldiff1.test 8f6bc7c6a5b3585d350d779c6078869ba402f8f5 F test/sqllimits1.test e05786eaed7950ff6a2d00031d001d8a26131e68 F test/stat.test 8de91498c99f5298b303f70f1d1f3b9557af91bf @@ -1286,7 +1286,10 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 48bd54594752d5be3337f12c72f28d2080cb630b F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P b41ef5d6db107cac2b1b46a955e63414434ee976 -R 906785d44a97778169ea8177629aec9d -U drh -Z 5a5062bd8b43ef3a45689ba975547888 +P fbf9c4325e98120914bb03bdf351b57643f7a8c8 +R de10737ebdc70bb8182e7abe767d84f9 +T *branch * spellfix-constraints +T *sym-spellfix-constraints * +T -sym-trunk * +U dan +Z fa582c8ac482ee4085cd5a29af44e41f diff --git a/manifest.uuid b/manifest.uuid index a33822f17d..6b677d1c3c 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -fbf9c4325e98120914bb03bdf351b57643f7a8c8 \ No newline at end of file +1d04def785b6031de68b7f199d400cbb5c76caea \ No newline at end of file diff --git a/test/spellfix.test b/test/spellfix.test index 954bdb21f1..b47001ebd7 100644 --- a/test/spellfix.test +++ b/test/spellfix.test @@ -283,7 +283,124 @@ ifcapable trace { } } +#------------------------------------------------------------------------- +# Test that the spellfix1 table supports conflict handling (OR REPLACE +# and so on). +# +do_execsql_test 7.1 { + CREATE VIRTUAL TABLE t4 USING spellfix1; + PRAGMA table_info = t4; +} { + 0 word {} 0 {} 0 + 1 rank {} 0 {} 0 + 2 distance {} 0 {} 0 + 3 langid {} 0 {} 0 + 4 score {} 0 {} 0 + 5 matchlen {} 0 {} 0 +} + +do_execsql_test 7.2.1 { + INSERT INTO t4(rowid, word) VALUES(1, 'Archilles'); + INSERT INTO t4(rowid, word) VALUES(2, 'Pluto'); + INSERT INTO t4(rowid, word) VALUES(3, 'Atrides'); + INSERT OR REPLACE INTO t4(rowid, word) VALUES(2, 'Apollo'); + SELECT rowid, word FROM t4; +} { + 1 Archilles 2 Apollo 3 Atrides +} +do_catchsql_test 7.2.2 { + INSERT OR ABORT INTO t4(rowid, word) VALUES(1, 'Leto'); +} {1 {constraint failed}} +do_catchsql_test 7.2.3 { + INSERT OR ROLLBACK INTO t4(rowid, word) VALUES(3, 'Zeus'); +} {1 {constraint failed}} +do_catchsql_test 7.2.4 { + INSERT OR FAIL INTO t4(rowid, word) VALUES(3, 'Zeus'); +} {1 {constraint failed}} +do_execsql_test 7.2.5 { + INSERT OR IGNORE INTO t4(rowid, word) VALUES(3, 'Zeus'); + SELECT rowid, word FROM t4; +} { + 1 Archilles 2 Apollo 3 Atrides +} + +do_execsql_test 7.3.1 { + UPDATE OR REPLACE t4 SET rowid=3 WHERE rowid=1; + SELECT rowid, word FROM t4; +} {2 Apollo 3 Archilles} +do_catchsql_test 7.3.2 { + UPDATE OR ABORT t4 SET rowid=3 WHERE rowid=2; +} {1 {constraint failed}} +do_catchsql_test 7.3.3 { + UPDATE OR ROLLBACK t4 SET rowid=3 WHERE rowid=2; +} {1 {constraint failed}} +do_catchsql_test 7.3.4 { + UPDATE OR FAIL t4 SET rowid=3 WHERE rowid=2; +} {1 {constraint failed}} +do_execsql_test 7.3.5 { + UPDATE OR IGNORE t4 SET rowid=3 WHERE rowid=2; + SELECT rowid, word FROM t4; +} {2 Apollo 3 Archilles} +do_execsql_test 7.4.1 { + DELETE FROM t4; + INSERT INTO t4(rowid, word) VALUES(10, 'Agamemnon'); + INSERT INTO t4(rowid, word) VALUES(20, 'Patroclus'); + INSERT INTO t4(rowid, word) VALUES(30, 'Chryses'); + CREATE TABLE t5(i, w); + INSERT INTO t5 VALUES(5, 'Poseidon'); + INSERT INTO t5 VALUES(20, 'Chronos'); + INSERT INTO t5 VALUES(30, 'Hera'); +} + +db_save_and_close +foreach {tn conflict err bRollback res} { + 0 "" {1 {constraint failed}} 0 + {10 Agamemnon 20 Patroclus 30 Chryses} + 1 "OR REPLACE" {0 {}} 0 + {5 Poseidon 10 Agamemnon 20 Chronos 30 Hera} + 2 "OR ABORT" {1 {constraint failed}} 0 + {10 Agamemnon 20 Patroclus 30 Chryses} + 3 "OR ROLLBACK" {1 {constraint failed}} 1 + {10 Agamemnon 20 Patroclus 30 Chryses} + 5 "OR IGNORE" {0 {}} 0 + {5 Poseidon 10 Agamemnon 20 Patroclus 30 Chryses} +} { + db_restore_and_reopen + load_static_extension db spellfix nextchar + + execsql BEGIN + set sql "INSERT $conflict INTO t4(rowid, word) SELECT i, w FROM t5" + do_catchsql_test 7.4.2.$tn.1 $sql $err + do_execsql_test 7.4.2.$tn.2 { SELECT rowid, word FROM t4 } $res + + do_test 7.4.2.$tn.3 { sqlite3_get_autocommit db } $bRollback + catchsql ROLLBACK +} + +foreach {tn conflict err bRollback res} { + 0 "" {1 {constraint failed}} 0 + {10 Agamemnon 20 Patroclus 30 Chryses} + 1 "OR REPLACE" {0 {}} 0 + {15 Agamemnon 45 Chryses} + 2 "OR ABORT" {1 {constraint failed}} 0 + {10 Agamemnon 20 Patroclus 30 Chryses} + 3 "OR ROLLBACK" {1 {constraint failed}} 1 + {10 Agamemnon 20 Patroclus 30 Chryses} + 5 "OR IGNORE" {0 {}} 0 + {15 Agamemnon 20 Patroclus 45 Chryses} +} { + db_restore_and_reopen + load_static_extension db spellfix nextchar + + execsql BEGIN + set sql "UPDATE $conflict t4 SET rowid=rowid + (rowid/2)" + do_catchsql_test 7.5.2.$tn.1 $sql $err + do_execsql_test 7.5.2.$tn.2 { SELECT rowid, word FROM t4 } $res + do_test 7.5.2.$tn.3 { sqlite3_get_autocommit db } $bRollback + catchsql ROLLBACK +} finish_test + -- 2.39.5