/ Changes On Branch set-last-insert-rowid
Login

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

Changes In Branch set-last-insert-rowid Excluding Merge-Ins

This is equivalent to a diff from 660f9569 to fe41bb56

2017-02-27
16:15
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. (check-in: 952a3906 user: dan tags: trunk)
14:52
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. (Closed-Leaf check-in: fe41bb56 user: dan tags: set-last-insert-rowid)
12:23
Remove references to special handling in virtual table methods from the documentation for sqlite3_last_insert_rowid(). (check-in: 660f9569 user: dan tags: trunk)
2017-02-25
20:57
Add an 'extern "C"' block to header file sqlite3userauth.h. (check-in: ffd61fb4 user: dan tags: trunk)

Changes to ext/fts3/fts3.c.

  3404   3404     ** of blocks from the segments table. But this is not considered overhead
  3405   3405     ** as it would also be required by a crisis-merge that used the same input 
  3406   3406     ** segments.
  3407   3407     */
  3408   3408     const u32 nMinMerge = 64;       /* Minimum amount of incr-merge work to do */
  3409   3409   
  3410   3410     Fts3Table *p = (Fts3Table*)pVtab;
  3411         -  int rc = sqlite3Fts3PendingTermsFlush(p);
         3411  +  int rc;
         3412  +  i64 iLastRowid = sqlite3_last_insert_rowid(p->db);
  3412   3413   
         3414  +  rc = sqlite3Fts3PendingTermsFlush(p);
  3413   3415     if( rc==SQLITE_OK 
  3414   3416      && p->nLeafAdd>(nMinMerge/16) 
  3415   3417      && p->nAutoincrmerge && p->nAutoincrmerge!=0xff
  3416   3418     ){
  3417   3419       int mxLevel = 0;              /* Maximum relative level value in db */
  3418   3420       int A;                        /* Incr-merge parameter A */
  3419   3421   
................................................................................
  3420   3422       rc = sqlite3Fts3MaxLevel(p, &mxLevel);
  3421   3423       assert( rc==SQLITE_OK || mxLevel==0 );
  3422   3424       A = p->nLeafAdd * mxLevel;
  3423   3425       A += (A/2);
  3424   3426       if( A>(int)nMinMerge ) rc = sqlite3Fts3Incrmerge(p, A, p->nAutoincrmerge);
  3425   3427     }
  3426   3428     sqlite3Fts3SegmentsClose(p);
         3429  +  sqlite3_set_last_insert_rowid(p->db, iLastRowid);
  3427   3430     return rc;
  3428   3431   }
  3429   3432   
  3430   3433   /*
  3431   3434   ** If it is currently unknown whether or not the FTS table has an %_stat
  3432   3435   ** table (if p->bHasStat==2), attempt to determine this (set p->bHasStat
  3433   3436   ** to 0 or 1). Return SQLITE_OK if successful, or an SQLite error code

Changes to ext/fts5/fts5_storage.c.

  1088   1088     return rc;
  1089   1089   }
  1090   1090   
  1091   1091   /*
  1092   1092   ** Flush any data currently held in-memory to disk.
  1093   1093   */
  1094   1094   int sqlite3Fts5StorageSync(Fts5Storage *p, int bCommit){
         1095  +  int rc = SQLITE_OK;
         1096  +  i64 iLastRowid = sqlite3_last_insert_rowid(p->pConfig->db);
  1095   1097     if( bCommit && p->bTotalsValid ){
  1096         -    int rc = fts5StorageSaveTotals(p);
         1098  +    rc = fts5StorageSaveTotals(p);
  1097   1099       p->bTotalsValid = 0;
  1098         -    if( rc!=SQLITE_OK ) return rc;
         1100  +  }
         1101  +  if( rc==SQLITE_OK ){
         1102  +    rc = sqlite3Fts5IndexSync(p->pIndex, bCommit);
  1099   1103     }
  1100         -  return sqlite3Fts5IndexSync(p->pIndex, bCommit);
         1104  +  sqlite3_set_last_insert_rowid(p->pConfig->db, iLastRowid);
         1105  +  return rc;
  1101   1106   }
  1102   1107   
  1103   1108   int sqlite3Fts5StorageRollback(Fts5Storage *p){
  1104   1109     p->bTotalsValid = 0;
  1105   1110     return sqlite3Fts5IndexRollback(p->pIndex);
  1106   1111   }
  1107   1112   

Added ext/fts5/test/fts5lastrowid.test.

            1  +# 2017 Feb 27
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Tests of the last_insert_rowid functionality with fts5.
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] fts5_common.tcl]
           16  +set testprefix fts5lastrowid
           17  +
           18  +# If SQLITE_ENABLE_FTS5 is defined, omit this file.
           19  +ifcapable !fts5 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE VIRTUAL TABLE t1 USING fts5(str);
           26  +}
           27  +
           28  +do_execsql_test 1.1 {
           29  +  INSERT INTO t1 VALUES('one string');
           30  +  INSERT INTO t1 VALUES('two string');
           31  +  INSERT INTO t1 VALUES('three string');
           32  +  SELECT last_insert_rowid();
           33  +} {3}
           34  +
           35  +do_execsql_test 1.2 {
           36  +  BEGIN;
           37  +    INSERT INTO t1 VALUES('one string');
           38  +    INSERT INTO t1 VALUES('two string');
           39  +    INSERT INTO t1 VALUES('three string');
           40  +  COMMIT;
           41  +  SELECT last_insert_rowid();
           42  +} {6}
           43  +
           44  +do_execsql_test 1.3 {
           45  +  INSERT INTO t1(rowid, str) VALUES(-22, 'some more text');
           46  +  SELECT last_insert_rowid();
           47  +} {-22}
           48  +
           49  +do_execsql_test 1.4 {
           50  +  BEGIN;
           51  +    INSERT INTO t1(rowid, str) VALUES(45, 'some more text');
           52  +    INSERT INTO t1(rowid, str) VALUES(46, 'some more text');
           53  +    INSERT INTO t1(rowid, str) VALUES(222, 'some more text');
           54  +    SELECT last_insert_rowid();
           55  +  COMMIT;
           56  +  SELECT last_insert_rowid();
           57  +} {222 222}
           58  +
           59  +do_execsql_test 1.5 {
           60  +  CREATE TABLE x1(x);
           61  +  INSERT INTO x1 VALUES('john'), ('paul'), ('george'), ('ringo');
           62  +  INSERT INTO t1 SELECT x FROM x1;
           63  +  SELECT last_insert_rowid();
           64  +} {226}
           65  +
           66  +do_execsql_test 1.6 {
           67  +  INSERT INTO t1(rowid, str) SELECT rowid+10, x FROM x1;
           68  +  SELECT last_insert_rowid();
           69  +} {14}
           70  +
           71  +
           72  +finish_test
           73  +

Changes to src/main.c.

   916    916     if( !sqlite3SafetyCheckOk(db) ){
   917    917       (void)SQLITE_MISUSE_BKPT;
   918    918       return 0;
   919    919     }
   920    920   #endif
   921    921     return db->lastRowid;
   922    922   }
          923  +
          924  +/*
          925  +** Set the value returned by the sqlite3_last_insert_rowid() API function.
          926  +*/
          927  +void sqlite3_set_last_insert_rowid(sqlite3 *db, sqlite3_int64 iRowid){
          928  +#ifdef SQLITE_ENABLE_API_ARMOR
          929  +  if( !sqlite3SafetyCheckOk(db) ){
          930  +    (void)SQLITE_MISUSE_BKPT;
          931  +    return;
          932  +  }
          933  +#endif
          934  +  sqlite3_mutex_enter(db->mutex);
          935  +  db->lastRowid = iRowid;
          936  +  sqlite3_mutex_leave(db->mutex);
          937  +}
   923    938   
   924    939   /*
   925    940   ** Return the number of changes in the most recent call to sqlite3_exec().
   926    941   */
   927    942   int sqlite3_changes(sqlite3 *db){
   928    943   #ifdef SQLITE_ENABLE_API_ARMOR
   929    944     if( !sqlite3SafetyCheckOk(db) ){

Changes to src/sqlite.h.in.

  2036   2036   ** has a unique 64-bit signed
  2037   2037   ** integer key called the [ROWID | "rowid"]. ^The rowid is always available
  2038   2038   ** as an undeclared column named ROWID, OID, or _ROWID_ as long as those
  2039   2039   ** names are not also used by explicitly declared columns. ^If
  2040   2040   ** the table has a column of type [INTEGER PRIMARY KEY] then that column
  2041   2041   ** is another alias for the rowid.
  2042   2042   **
  2043         -** ^The sqlite3_last_insert_rowid(D) interface returns the [rowid] of the 
  2044         -** most recent successful [INSERT] into a rowid table or [virtual table]
  2045         -** on database connection D.
  2046         -** ^Inserts into [WITHOUT ROWID] tables are not recorded.
  2047         -** ^If no successful [INSERT]s into rowid tables
  2048         -** have ever occurred on the database connection D, 
  2049         -** then sqlite3_last_insert_rowid(D) returns zero.
         2043  +** ^The sqlite3_last_insert_rowid(D) interface usually returns the [rowid] of
         2044  +** the most recent successful [INSERT] into a rowid table or [virtual table]
         2045  +** on database connection D. ^Inserts into [WITHOUT ROWID] tables are not
         2046  +** recorded. ^If no successful [INSERT]s into rowid tables have ever occurred 
         2047  +** on the database connection D, then sqlite3_last_insert_rowid(D) returns 
         2048  +** zero.
         2049  +**
         2050  +** As well as being set automatically as rows are inserted into database
         2051  +** tables, the value returned by this function may be set explicitly by
         2052  +** [sqlite3_set_last_insert_rowid()]
         2053  +**
         2054  +** Some virtual table implementations may INSERT rows into rowid tables as
         2055  +** part of committing a transaction (e.g. to flush data accumulated in memory
         2056  +** to disk). In this case subsequent calls to this function return the rowid
         2057  +** associated with these internal INSERT operations, which leads to 
         2058  +** unintuitive results. Virtual table implementations that do write to rowid
         2059  +** tables in this way can avoid this problem by restoring the original 
         2060  +** rowid value using [sqlite3_set_last_insert_rowid()] before returning 
         2061  +** control to the user.
  2050   2062   **
  2051   2063   ** ^(If an [INSERT] occurs within a trigger then this routine will 
  2052   2064   ** return the [rowid] of the inserted row as long as the trigger is 
  2053   2065   ** running. Once the trigger program ends, the value returned 
  2054   2066   ** by this routine reverts to what it was before the trigger was fired.)^
  2055   2067   **
  2056   2068   ** ^An [INSERT] that fails due to a constraint violation is not a
................................................................................
  2074   2086   ** function is running and thus changes the last insert [rowid],
  2075   2087   ** then the value returned by [sqlite3_last_insert_rowid()] is
  2076   2088   ** unpredictable and might not equal either the old or the new
  2077   2089   ** last insert [rowid].
  2078   2090   */
  2079   2091   sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);
  2080   2092   
         2093  +/*
         2094  +** CAPI3REF: Set the Last Insert Rowid value.
         2095  +** METHOD: sqlite3
         2096  +**
         2097  +** The sqlite3_set_last_insert_rowid(D, R) method allows the application to
         2098  +** set the value returned by calling sqlite3_last_insert_rowid(D) to R 
         2099  +** without inserting a row into the database.
         2100  +*/
         2101  +void sqlite3_set_last_insert_rowid(sqlite3*,sqlite3_int64);
         2102  +
  2081   2103   /*
  2082   2104   ** CAPI3REF: Count The Number Of Rows Modified
  2083   2105   ** METHOD: sqlite3
  2084   2106   **
  2085   2107   ** ^This function returns the number of rows modified, inserted or
  2086   2108   ** deleted by the most recently completed INSERT, UPDATE or DELETE
  2087   2109   ** statement on the database connection specified by the only parameter.

Added test/fts4lastrowid.test.

            1  +# 2017 Feb 27
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Tests of the last_insert_rowid functionality with fts4.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source [file join [file dirname [info script]] tester.tcl]
           17  +set testprefix fts4lastrowid
           18  +
           19  +ifcapable !fts3 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE VIRTUAL TABLE t1 USING fts4(str);
           26  +}
           27  +
           28  +do_execsql_test 1.1 {
           29  +  INSERT INTO t1 VALUES('one string');
           30  +  INSERT INTO t1 VALUES('two string');
           31  +  INSERT INTO t1 VALUES('three string');
           32  +  SELECT last_insert_rowid();
           33  +} {3}
           34  +
           35  +do_execsql_test 1.2 {
           36  +  BEGIN;
           37  +    INSERT INTO t1 VALUES('one string');
           38  +    INSERT INTO t1 VALUES('two string');
           39  +    INSERT INTO t1 VALUES('three string');
           40  +  COMMIT;
           41  +  SELECT last_insert_rowid();
           42  +} {6}
           43  +
           44  +do_execsql_test 1.3 {
           45  +  INSERT INTO t1(rowid, str) VALUES(-22, 'some more text');
           46  +  SELECT last_insert_rowid();
           47  +} {-22}
           48  +
           49  +do_execsql_test 1.4 {
           50  +  BEGIN;
           51  +    INSERT INTO t1(rowid, str) VALUES(45, 'some more text');
           52  +    INSERT INTO t1(rowid, str) VALUES(46, 'some more text');
           53  +    INSERT INTO t1(rowid, str) VALUES(222, 'some more text');
           54  +    SELECT last_insert_rowid();
           55  +  COMMIT;
           56  +  SELECT last_insert_rowid();
           57  +} {222 222}
           58  +
           59  +do_execsql_test 1.5 {
           60  +  CREATE TABLE x1(x);
           61  +  INSERT INTO x1 VALUES('john'), ('paul'), ('george'), ('ringo');
           62  +  INSERT INTO t1 SELECT x FROM x1;
           63  +  SELECT last_insert_rowid();
           64  +} {226}
           65  +
           66  +do_execsql_test 1.6 {
           67  +  INSERT INTO t1(rowid, str) SELECT rowid+10, x FROM x1;
           68  +  SELECT last_insert_rowid();
           69  +} {14}
           70  +
           71  +
           72  +finish_test
           73  +