From 9c58b63c18938a9bdbcc2f774de45747eec74fbc Mon Sep 17 00:00:00 2001 From: dan Date: Mon, 27 Feb 2017 14:52:48 +0000 Subject: [PATCH] Add an sqlite3_set_last_insert_rowid() method. Use it to work around fts4 and fts5 modifying the last-insert-rowid unintuitively from within commit processing. FossilOrigin-Name: fe41bb5632a5d438acfd682809f1bd12315b970a --- ext/fts3/fts3.c | 5 ++- ext/fts5/fts5_storage.c | 11 +++-- ext/fts5/test/fts5lastrowid.test | 73 ++++++++++++++++++++++++++++++++ manifest | 23 ++++++---- manifest.uuid | 2 +- src/main.c | 15 +++++++ src/sqlite.h.in | 36 +++++++++++++--- test/fts4lastrowid.test | 73 ++++++++++++++++++++++++++++++++ 8 files changed, 217 insertions(+), 21 deletions(-) create mode 100644 ext/fts5/test/fts5lastrowid.test create mode 100644 test/fts4lastrowid.test diff --git a/ext/fts3/fts3.c b/ext/fts3/fts3.c index 7c931c42d4..8af62cda45 100644 --- a/ext/fts3/fts3.c +++ b/ext/fts3/fts3.c @@ -3408,8 +3408,10 @@ static int fts3SyncMethod(sqlite3_vtab *pVtab){ const u32 nMinMerge = 64; /* Minimum amount of incr-merge work to do */ Fts3Table *p = (Fts3Table*)pVtab; - int rc = sqlite3Fts3PendingTermsFlush(p); + int rc; + i64 iLastRowid = sqlite3_last_insert_rowid(p->db); + rc = sqlite3Fts3PendingTermsFlush(p); if( rc==SQLITE_OK && p->nLeafAdd>(nMinMerge/16) && p->nAutoincrmerge && p->nAutoincrmerge!=0xff @@ -3424,6 +3426,7 @@ static int fts3SyncMethod(sqlite3_vtab *pVtab){ if( A>(int)nMinMerge ) rc = sqlite3Fts3Incrmerge(p, A, p->nAutoincrmerge); } sqlite3Fts3SegmentsClose(p); + sqlite3_set_last_insert_rowid(p->db, iLastRowid); return rc; } diff --git a/ext/fts5/fts5_storage.c b/ext/fts5/fts5_storage.c index a695887458..1f8ad2f434 100644 --- a/ext/fts5/fts5_storage.c +++ b/ext/fts5/fts5_storage.c @@ -1092,12 +1092,17 @@ int sqlite3Fts5StorageRowCount(Fts5Storage *p, i64 *pnRow){ ** Flush any data currently held in-memory to disk. */ int sqlite3Fts5StorageSync(Fts5Storage *p, int bCommit){ + int rc = SQLITE_OK; + i64 iLastRowid = sqlite3_last_insert_rowid(p->pConfig->db); if( bCommit && p->bTotalsValid ){ - int rc = fts5StorageSaveTotals(p); + rc = fts5StorageSaveTotals(p); p->bTotalsValid = 0; - if( rc!=SQLITE_OK ) return rc; } - return sqlite3Fts5IndexSync(p->pIndex, bCommit); + if( rc==SQLITE_OK ){ + rc = sqlite3Fts5IndexSync(p->pIndex, bCommit); + } + sqlite3_set_last_insert_rowid(p->pConfig->db, iLastRowid); + return rc; } int sqlite3Fts5StorageRollback(Fts5Storage *p){ diff --git a/ext/fts5/test/fts5lastrowid.test b/ext/fts5/test/fts5lastrowid.test new file mode 100644 index 0000000000..0f7628b233 --- /dev/null +++ b/ext/fts5/test/fts5lastrowid.test @@ -0,0 +1,73 @@ +# 2017 Feb 27 +# +# 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. +# +#*********************************************************************** +# +# Tests of the last_insert_rowid functionality with fts5. +# + +source [file join [file dirname [info script]] fts5_common.tcl] +set testprefix fts5lastrowid + +# If SQLITE_ENABLE_FTS5 is defined, omit this file. +ifcapable !fts5 { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t1 USING fts5(str); +} + +do_execsql_test 1.1 { + INSERT INTO t1 VALUES('one string'); + INSERT INTO t1 VALUES('two string'); + INSERT INTO t1 VALUES('three string'); + SELECT last_insert_rowid(); +} {3} + +do_execsql_test 1.2 { + BEGIN; + INSERT INTO t1 VALUES('one string'); + INSERT INTO t1 VALUES('two string'); + INSERT INTO t1 VALUES('three string'); + COMMIT; + SELECT last_insert_rowid(); +} {6} + +do_execsql_test 1.3 { + INSERT INTO t1(rowid, str) VALUES(-22, 'some more text'); + SELECT last_insert_rowid(); +} {-22} + +do_execsql_test 1.4 { + BEGIN; + INSERT INTO t1(rowid, str) VALUES(45, 'some more text'); + INSERT INTO t1(rowid, str) VALUES(46, 'some more text'); + INSERT INTO t1(rowid, str) VALUES(222, 'some more text'); + SELECT last_insert_rowid(); + COMMIT; + SELECT last_insert_rowid(); +} {222 222} + +do_execsql_test 1.5 { + CREATE TABLE x1(x); + INSERT INTO x1 VALUES('john'), ('paul'), ('george'), ('ringo'); + INSERT INTO t1 SELECT x FROM x1; + SELECT last_insert_rowid(); +} {226} + +do_execsql_test 1.6 { + INSERT INTO t1(rowid, str) SELECT rowid+10, x FROM x1; + SELECT last_insert_rowid(); +} {14} + + +finish_test + diff --git a/manifest b/manifest index 1efcbbbcf0..e571698523 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Remove\sreferences\sto\sspecial\shandling\sin\svirtual\stable\smethods\sfrom\sthe\ndocumentation\sfor\ssqlite3_last_insert_rowid(). -D 2017-02-27T12:23:52.119 +C Add\san\ssqlite3_set_last_insert_rowid()\smethod.\sUse\sit\sto\swork\saround\sfts4\sand\nfts5\smodifying\sthe\slast-insert-rowid\sunintuitively\sfrom\swithin\scommit\nprocessing. +D 2017-02-27T14:52:48.644 F Makefile.in edb6bcdd37748d2b1c3422ff727c748df7ffe918 F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc a89ea37ab5928026001569f056973b9059492fe2 @@ -70,7 +70,7 @@ F ext/fts3/README.content fdc666a70d5257a64fee209f97cf89e0e6e32b51 F ext/fts3/README.syntax a19711dc5458c20734b8e485e75fb1981ec2427a F ext/fts3/README.tokenizers e0a8b81383ea60d0334d274fadf305ea14a8c314 F ext/fts3/README.txt 8c18f41574404623b76917b9da66fcb0ab38328d -F ext/fts3/fts3.c c4d7eecb12de9749851bcab6e5ca616a5803047a +F ext/fts3/fts3.c 95c7041ea75d82d2d9a4cd058904ba889751f5b8 F ext/fts3/fts3.h 3a10a0af180d502cecc50df77b1b22df142817fe F ext/fts3/fts3Int.h eb2502000148e80913b965db3e59f29251266d0a F ext/fts3/fts3_aux.c 9edc3655fcb287f0467d0a4b886a01c6185fe9f1 @@ -106,7 +106,7 @@ F ext/fts5/fts5_expr.c c6ecc2280162a3714d15dce2a8f2299f748b627c F ext/fts5/fts5_hash.c 880998e596b60f078348d48732ca4ad9a90caad2 F ext/fts5/fts5_index.c f67032a9a529ba52a545e6e3ab970764199c05d4 F ext/fts5/fts5_main.c f85281445dcf8be32d18841c93a6f90fe27dbfe2 -F ext/fts5/fts5_storage.c de0ed8a06738bde433afe11e92295ceaffbc4e58 +F ext/fts5/fts5_storage.c 1db0b6f859ce910027245cf41e8a32c7aaed0042 F ext/fts5/fts5_tcl.c 4a901f00c8553740dba63511603f5527d741c26a F ext/fts5/fts5_test_mi.c 783b86697ebf773c18fc109992426c0173a055bc F ext/fts5/fts5_test_tok.c db08af63673c3a7d39f053b36fd6e065017706be @@ -165,6 +165,7 @@ F ext/fts5/test/fts5full.test 6f6143af0c6700501d9fd597189dfab1555bb741 F ext/fts5/test/fts5fuzz1.test bece4695fc169b61ab236ada7931c6e4942cbef9 F ext/fts5/test/fts5hash.test 06f9309ccb4d5050a131594e9e47d0b21456837d F ext/fts5/test/fts5integrity.test f5e4f8d284385875068ad0f3e894ce43e9de835d +F ext/fts5/test/fts5lastrowid.test 4fac1aba696dd6c956e03b0cf91f6f1f3aaec494 F ext/fts5/test/fts5matchinfo.test f7dde99697bcb310ea8faa8eb2714d9f4dfc0e1b F ext/fts5/test/fts5merge.test 9f65f090d214ff865c56bef4f864aaa1182af6e3 F ext/fts5/test/fts5merge2.test a6da3c16d694235938d1939f503cfa53f0943d75 @@ -360,7 +361,7 @@ F src/in-operator.md 10cd8f4bcd225a32518407c2fb2484089112fd71 F src/insert.c 3ed64afc49c0a2221e397b9f65d231ffbef506fe F src/legacy.c e88ed13c2d531decde75d42c2e35623fb9ce3cb0 F src/loadext.c a68d8d1d14cf7488bb29dc5311cb1ce9a4404258 -F src/main.c e207b81542d13b9f13d61e78ca441f9781f055b0 +F src/main.c 158326243c5ddc8b98a1e983fa488650cf76d760 F src/malloc.c d0a1474236486165bcb349af82e2a6560178bf7b F src/mem0.c 6a55ebe57c46ca1a7d98da93aaa07f99f1059645 F src/mem1.c fd7cd6fe21d46fe0a4186367dd8dc26d87b787eb @@ -397,7 +398,7 @@ F src/resolve.c f9bc0de45a30a450da47b3766de00be89bf9be79 F src/rowset.c 7b7e7e479212e65b723bf40128c7b36dc5afdfac F src/select.c d12f3539f80db38b09015561b569e0eb1c4b6c5f F src/shell.c bf976d5301be9d8a4c52852c97909cc9a41ee20d -F src/sqlite.h.in 154b80d215289fb9b845a6f4f9b67ea4fcf049f8 +F src/sqlite.h.in 4d0c08f8640c586564a7032b259c5f69bf397850 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 8648034aa702469afb553231677306cc6492a1ae F src/sqliteInt.h a23e18aebdd0d851c2956a74a3a4f12ff202b472 @@ -806,6 +807,7 @@ F test/fts4growth.test e5390da74619cacc389711bac9349640b32c4f9a F test/fts4growth2.test 13ad4e76451af6e6906c95cdc725d01b00044269 F test/fts4incr.test 4e353a0bd886ea984e56fce9e77724fc923b8d0d F test/fts4langid.test 65a7332c9bc257919e259a304aa8a38c41655b9d +F test/fts4lastrowid.test fa5e157955a3121615ef3e16ff5196e96c9e1e64 F test/fts4merge.test d2b39f6b1bd4a9738a13540e2d044cba11c43d47 F test/fts4merge2.test 5faa558d1b672f82b847d2a337465fa745e46891 F test/fts4merge3.test 8d9ccb4a3d41c4c617a149d6c4b13ad02de797d0 @@ -1557,7 +1559,10 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P ffd61fb449a510b2fc90caf86b266733051cc365 -R 8aa5e89f1f59df441ed610ff04b07915 +P 660f9569d76e4ff1f5bd4f37f640e6a4fc2cf87d +R 882d86f84a56568be3256ca713764e4e +T *branch * set-last-insert-rowid +T *sym-set-last-insert-rowid * +T -sym-trunk * U dan -Z e6ccfdfcae6209cec3abae13f87bf4f8 +Z 68fc9fa30db7c037a3f29bd13a0c709d diff --git a/manifest.uuid b/manifest.uuid index d173449ff7..ab91f005ae 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -660f9569d76e4ff1f5bd4f37f640e6a4fc2cf87d \ No newline at end of file +fe41bb5632a5d438acfd682809f1bd12315b970a \ No newline at end of file diff --git a/src/main.c b/src/main.c index 9aad8fdd4c..4ac5327e4f 100644 --- a/src/main.c +++ b/src/main.c @@ -921,6 +921,21 @@ sqlite_int64 sqlite3_last_insert_rowid(sqlite3 *db){ return db->lastRowid; } +/* +** Set the value returned by the sqlite3_last_insert_rowid() API function. +*/ +void sqlite3_set_last_insert_rowid(sqlite3 *db, sqlite3_int64 iRowid){ +#ifdef SQLITE_ENABLE_API_ARMOR + if( !sqlite3SafetyCheckOk(db) ){ + (void)SQLITE_MISUSE_BKPT; + return; + } +#endif + sqlite3_mutex_enter(db->mutex); + db->lastRowid = iRowid; + sqlite3_mutex_leave(db->mutex); +} + /* ** Return the number of changes in the most recent call to sqlite3_exec(). */ diff --git a/src/sqlite.h.in b/src/sqlite.h.in index 79b9996848..076a011319 100644 --- a/src/sqlite.h.in +++ b/src/sqlite.h.in @@ -2040,13 +2040,25 @@ int sqlite3_extended_result_codes(sqlite3*, int onoff); ** the table has a column of type [INTEGER PRIMARY KEY] then that column ** is another alias for the rowid. ** -** ^The sqlite3_last_insert_rowid(D) interface returns the [rowid] of the -** most recent successful [INSERT] into a rowid table or [virtual table] -** on database connection D. -** ^Inserts into [WITHOUT ROWID] tables are not recorded. -** ^If no successful [INSERT]s into rowid tables -** have ever occurred on the database connection D, -** then sqlite3_last_insert_rowid(D) returns zero. +** ^The sqlite3_last_insert_rowid(D) interface usually returns the [rowid] of +** the most recent successful [INSERT] into a rowid table or [virtual table] +** on database connection D. ^Inserts into [WITHOUT ROWID] tables are not +** recorded. ^If no successful [INSERT]s into rowid tables have ever occurred +** on the database connection D, then sqlite3_last_insert_rowid(D) returns +** zero. +** +** As well as being set automatically as rows are inserted into database +** tables, the value returned by this function may be set explicitly by +** [sqlite3_set_last_insert_rowid()] +** +** Some virtual table implementations may INSERT rows into rowid tables as +** part of committing a transaction (e.g. to flush data accumulated in memory +** to disk). In this case subsequent calls to this function return the rowid +** associated with these internal INSERT operations, which leads to +** unintuitive results. Virtual table implementations that do write to rowid +** tables in this way can avoid this problem by restoring the original +** rowid value using [sqlite3_set_last_insert_rowid()] before returning +** control to the user. ** ** ^(If an [INSERT] occurs within a trigger then this routine will ** return the [rowid] of the inserted row as long as the trigger is @@ -2078,6 +2090,16 @@ int sqlite3_extended_result_codes(sqlite3*, int onoff); */ sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*); +/* +** CAPI3REF: Set the Last Insert Rowid value. +** METHOD: sqlite3 +** +** The sqlite3_set_last_insert_rowid(D, R) method allows the application to +** set the value returned by calling sqlite3_last_insert_rowid(D) to R +** without inserting a row into the database. +*/ +void sqlite3_set_last_insert_rowid(sqlite3*,sqlite3_int64); + /* ** CAPI3REF: Count The Number Of Rows Modified ** METHOD: sqlite3 diff --git a/test/fts4lastrowid.test b/test/fts4lastrowid.test new file mode 100644 index 0000000000..b5720a2ef3 --- /dev/null +++ b/test/fts4lastrowid.test @@ -0,0 +1,73 @@ +# 2017 Feb 27 +# +# 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. +# +#*********************************************************************** +# +# Tests of the last_insert_rowid functionality with fts4. +# + +set testdir [file dirname $argv0] +source [file join [file dirname [info script]] tester.tcl] +set testprefix fts4lastrowid + +ifcapable !fts3 { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t1 USING fts4(str); +} + +do_execsql_test 1.1 { + INSERT INTO t1 VALUES('one string'); + INSERT INTO t1 VALUES('two string'); + INSERT INTO t1 VALUES('three string'); + SELECT last_insert_rowid(); +} {3} + +do_execsql_test 1.2 { + BEGIN; + INSERT INTO t1 VALUES('one string'); + INSERT INTO t1 VALUES('two string'); + INSERT INTO t1 VALUES('three string'); + COMMIT; + SELECT last_insert_rowid(); +} {6} + +do_execsql_test 1.3 { + INSERT INTO t1(rowid, str) VALUES(-22, 'some more text'); + SELECT last_insert_rowid(); +} {-22} + +do_execsql_test 1.4 { + BEGIN; + INSERT INTO t1(rowid, str) VALUES(45, 'some more text'); + INSERT INTO t1(rowid, str) VALUES(46, 'some more text'); + INSERT INTO t1(rowid, str) VALUES(222, 'some more text'); + SELECT last_insert_rowid(); + COMMIT; + SELECT last_insert_rowid(); +} {222 222} + +do_execsql_test 1.5 { + CREATE TABLE x1(x); + INSERT INTO x1 VALUES('john'), ('paul'), ('george'), ('ringo'); + INSERT INTO t1 SELECT x FROM x1; + SELECT last_insert_rowid(); +} {226} + +do_execsql_test 1.6 { + INSERT INTO t1(rowid, str) SELECT rowid+10, x FROM x1; + SELECT last_insert_rowid(); +} {14} + + +finish_test + -- 2.39.5