Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the sqlite3_changes64() and sqlite3_total_changes64() API functions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
48fdec22c966003f5577e0bf52906ef9 |
User & Date: | dan 2021-06-22 18:32:05.878 |
Context
2021-06-22
| ||
23:30 | This is another alternative fix to the covering index on WHERE_MULTI_OR in a LEFT JOIN problem that is fixed on trunk nearby. In this alternative, covering indexes are simply disabled for WHERE_MULTI_OR on a LEFT JOIN. This might have run-time impact on some obscure queries. This patch is saved for historical reference only. (Closed-Leaf check-in: 668564100e user: drh tags: multi-or-covidx-fix3) | |
23:27 | This is an alternative fix to the covering index on WHERE_MULTI_OR in a LEFT JOIN problem that is fixed nearby. This one works by having the OP_NullRow opcode create the index if it does not already exist. That is slightly more complex. This patch is saved for historical reference only. (Closed-Leaf check-in: 956bafb69f user: drh tags: multi-or-covidx-fix2) | |
23:24 | When an index is used by all branches of the WHERE_MULTI_OR optimization and becomes a covering index, make sure the index has been created prior to NULLing it in the OP_NullRow opcode of a LEFT JOIN. See forum post 0575376e07. The covering-index for WHERE_MULTI_OR optimization was added by [62678be3df35cdcb]. Test cases are in the orindex01.test module of TH3. (check-in: 787c76a865 user: drh tags: trunk) | |
18:32 | Add the sqlite3_changes64() and sqlite3_total_changes64() API functions. (check-in: 48fdec22c9 user: dan tags: trunk) | |
18:06 | Fix a problem causing sqlite3_changes() to return an incorrect value following a "DELETE FROM tbl" command on an intkey table (because internal b+tree cells were being included in the count). (check-in: f662ff4746 user: dan tags: trunk) | |
Changes
Changes to src/btree.c.
︙ | ︙ | |||
9539 9540 9541 9542 9543 9544 9545 | ** Erase the given database page and all its children. Return ** the page to the freelist. */ static int clearDatabasePage( BtShared *pBt, /* The BTree that contains the table */ Pgno pgno, /* Page number to clear */ int freePageFlag, /* Deallocate page if true */ | | | 9539 9540 9541 9542 9543 9544 9545 9546 9547 9548 9549 9550 9551 9552 9553 | ** Erase the given database page and all its children. Return ** the page to the freelist. */ static int clearDatabasePage( BtShared *pBt, /* The BTree that contains the table */ Pgno pgno, /* Page number to clear */ int freePageFlag, /* Deallocate page if true */ i64 *pnChange /* Add number of Cells freed to this counter */ ){ MemPage *pPage; int rc; unsigned char *pCell; int i; int hdr; CellInfo info; |
︙ | ︙ | |||
9602 9603 9604 9605 9606 9607 9608 | ** This routine will fail with SQLITE_LOCKED if there are any open ** read cursors on the table. Open write cursors are moved to the ** root of the table. ** ** If pnChange is not NULL, then the integer value pointed to by pnChange ** is incremented by the number of entries in the table. */ | | | 9602 9603 9604 9605 9606 9607 9608 9609 9610 9611 9612 9613 9614 9615 9616 | ** This routine will fail with SQLITE_LOCKED if there are any open ** read cursors on the table. Open write cursors are moved to the ** root of the table. ** ** If pnChange is not NULL, then the integer value pointed to by pnChange ** is incremented by the number of entries in the table. */ int sqlite3BtreeClearTable(Btree *p, int iTable, i64 *pnChange){ int rc; BtShared *pBt = p->pBt; sqlite3BtreeEnter(p); assert( p->inTrans==TRANS_WRITE ); rc = saveAllCursors(pBt, (Pgno)iTable, 0); |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
119 120 121 122 123 124 125 | ** anywhere - the key is the content. (BTREE_BLOBKEY is used for SQL ** indices.) */ #define BTREE_INTKEY 1 /* Table has only 64-bit signed integer keys */ #define BTREE_BLOBKEY 2 /* Table has keys only - no data */ int sqlite3BtreeDropTable(Btree*, int, int*); | | | 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 | ** anywhere - the key is the content. (BTREE_BLOBKEY is used for SQL ** indices.) */ #define BTREE_INTKEY 1 /* Table has only 64-bit signed integer keys */ #define BTREE_BLOBKEY 2 /* Table has keys only - no data */ int sqlite3BtreeDropTable(Btree*, int, int*); int sqlite3BtreeClearTable(Btree*, int, i64*); int sqlite3BtreeClearTableOfCursor(BtCursor*); int sqlite3BtreeTripAllCursors(Btree*, int, int); void sqlite3BtreeGetMeta(Btree *pBtree, int idx, u32 *pValue); int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value); int sqlite3BtreeNewDb(Btree *p); |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
578 579 580 581 582 583 584 | static void changes( sqlite3_context *context, int NotUsed, sqlite3_value **NotUsed2 ){ sqlite3 *db = sqlite3_context_db_handle(context); UNUSED_PARAMETER2(NotUsed, NotUsed2); | | | 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 | static void changes( sqlite3_context *context, int NotUsed, sqlite3_value **NotUsed2 ){ sqlite3 *db = sqlite3_context_db_handle(context); UNUSED_PARAMETER2(NotUsed, NotUsed2); sqlite3_result_int64(context, sqlite3_changes64(db)); } /* ** Implementation of the total_changes() SQL function. The return value is ** the same as the sqlite3_total_changes() API function. */ static void total_changes( |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
1082 1083 1084 1085 1086 1087 1088 | db->lastRowid = iRowid; sqlite3_mutex_leave(db->mutex); } /* ** Return the number of changes in the most recent call to sqlite3_exec(). */ | | > > > | > > > | 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 | db->lastRowid = iRowid; sqlite3_mutex_leave(db->mutex); } /* ** Return the number of changes in the most recent call to sqlite3_exec(). */ sqlite3_int64 sqlite3_changes64(sqlite3 *db){ #ifdef SQLITE_ENABLE_API_ARMOR if( !sqlite3SafetyCheckOk(db) ){ (void)SQLITE_MISUSE_BKPT; return 0; } #endif return db->nChange; } int sqlite3_changes(sqlite3 *db){ return (int)sqlite3_changes64(db); } /* ** Return the number of changes since the database handle was opened. */ sqlite3_int64 sqlite3_total_changes64(sqlite3 *db){ #ifdef SQLITE_ENABLE_API_ARMOR if( !sqlite3SafetyCheckOk(db) ){ (void)SQLITE_MISUSE_BKPT; return 0; } #endif return db->nTotalChange; } int sqlite3_total_changes(sqlite3 *db){ return (int)sqlite3_total_changes64(db); } /* ** Close all open savepoints. This function only manipulates fields of the ** database handle object, it does not close any savepoints that may be open ** at the b-tree/pager level. */ void sqlite3CloseSavepoints(sqlite3 *db){ |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
2460 2461 2462 2463 2464 2465 2466 | */ void sqlite3_set_last_insert_rowid(sqlite3*,sqlite3_int64); /* ** CAPI3REF: Count The Number Of Rows Modified ** METHOD: sqlite3 ** | | > > > > | < | 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 | */ void sqlite3_set_last_insert_rowid(sqlite3*,sqlite3_int64); /* ** CAPI3REF: Count The Number Of Rows Modified ** METHOD: sqlite3 ** ** ^These functions return the number of rows modified, inserted or ** deleted by the most recently completed INSERT, UPDATE or DELETE ** statement on the database connection specified by the only parameter. ** The two functions are identical except for the type of the return value ** and that if the number of rows modified by the most recent INSERT, UPDATE ** or DELETE is greater than the maximum value supported by type "int", then ** the return value of sqlite3_changes() is undefined. ^Executing any other ** type of SQL statement does not modify the value returned by these functions. ** ** ^Only changes made directly by the INSERT, UPDATE or DELETE statement are ** considered - auxiliary changes caused by [CREATE TRIGGER | triggers], ** [foreign key actions] or [REPLACE] constraint resolution are not counted. ** ** Changes to a view that are intercepted by ** [INSTEAD OF trigger | INSTEAD OF triggers] are not counted. ^The value |
︙ | ︙ | |||
2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 | ** <li> the [sqlite3_total_changes()] interface ** <li> the [count_changes pragma] ** <li> the [changes() SQL function] ** <li> the [data_version pragma] ** </ul> */ int sqlite3_changes(sqlite3*); /* ** CAPI3REF: Total Number Of Rows Modified ** METHOD: sqlite3 ** | > | > > > > | | | 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 | ** <li> the [sqlite3_total_changes()] interface ** <li> the [count_changes pragma] ** <li> the [changes() SQL function] ** <li> the [data_version pragma] ** </ul> */ int sqlite3_changes(sqlite3*); sqlite3_int64 sqlite3_changes64(sqlite3*); /* ** CAPI3REF: Total Number Of Rows Modified ** METHOD: sqlite3 ** ** ^These functions return the total number of rows inserted, modified or ** deleted by all [INSERT], [UPDATE] or [DELETE] statements completed ** since the database connection was opened, including those executed as ** part of trigger programs. The two functions are identical except for the ** type of the return value and that if the number of rows modified by the ** connection exceeds the maximum value supported by type "int", then ** the return value of sqlite3_total_changes() is undefined. ^Executing ** any other type of SQL statement does not affect the value returned by ** sqlite3_total_changes(). ** ** ^Changes made as part of [foreign key actions] are included in the ** count, but those made as part of REPLACE constraint resolution are ** not. ^Changes to a view that are intercepted by INSTEAD OF triggers ** are not counted. ** ** The [sqlite3_total_changes(D)] interface only reports the number |
︙ | ︙ | |||
2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 | ** <li> the [count_changes pragma] ** <li> the [changes() SQL function] ** <li> the [data_version pragma] ** <li> the [SQLITE_FCNTL_DATA_VERSION] [file control] ** </ul> */ int sqlite3_total_changes(sqlite3*); /* ** CAPI3REF: Interrupt A Long-Running Query ** METHOD: sqlite3 ** ** ^This function causes any pending database operation to abort and ** return at its earliest opportunity. This routine is typically | > | 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 | ** <li> the [count_changes pragma] ** <li> the [changes() SQL function] ** <li> the [data_version pragma] ** <li> the [SQLITE_FCNTL_DATA_VERSION] [file control] ** </ul> */ int sqlite3_total_changes(sqlite3*); sqlite3_int64 sqlite3_total_changes64(sqlite3*); /* ** CAPI3REF: Interrupt A Long-Running Query ** METHOD: sqlite3 ** ** ^This function causes any pending database operation to abort and ** return at its earliest opportunity. This routine is typically |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1518 1519 1520 1521 1522 1523 1524 | u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 noSharedCache; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ | | | | 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 | u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 noSharedCache; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ i64 nChange; /* Value returned by sqlite3_changes() */ i64 nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ struct sqlite3InitInfo { /* Information used during initialization */ Pgno newTnum; /* Rootpage of table being initialized */ u8 iDb; /* Which db file is being initialized */ u8 busy; /* TRUE if currently initializing */ unsigned orphanTrigger : 1; /* Last statement is orphaned TEMP trigger */ |
︙ | ︙ | |||
4737 4738 4739 4740 4741 4742 4743 | Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, const Token*, int); Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*); Expr *sqlite3ExprSkipCollate(Expr*); Expr *sqlite3ExprSkipCollateAndLikely(Expr*); int sqlite3CheckCollSeq(Parse *, CollSeq *); int sqlite3WritableSchema(sqlite3*); int sqlite3CheckObjectName(Parse*, const char*,const char*,const char*); | | | 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 | Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, const Token*, int); Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*); Expr *sqlite3ExprSkipCollate(Expr*); Expr *sqlite3ExprSkipCollateAndLikely(Expr*); int sqlite3CheckCollSeq(Parse *, CollSeq *); int sqlite3WritableSchema(sqlite3*); int sqlite3CheckObjectName(Parse*, const char*,const char*,const char*); void sqlite3VdbeSetChanges(sqlite3 *, i64); int sqlite3AddInt64(i64*,i64); int sqlite3SubInt64(i64*,i64); int sqlite3MulInt64(i64*,i64); int sqlite3AbsInt32(int); #ifdef SQLITE_ENABLE_8_3_NAMES void sqlite3FileSuffix3(const char*, char*); #else |
︙ | ︙ |
Changes to src/tclsqlite.c.
︙ | ︙ | |||
2203 2204 2205 2206 2207 2208 2209 | case DB_CHANGES: { Tcl_Obj *pResult; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 2, objv, ""); return TCL_ERROR; } pResult = Tcl_GetObjResult(interp); | | | 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 | case DB_CHANGES: { Tcl_Obj *pResult; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 2, objv, ""); return TCL_ERROR; } pResult = Tcl_GetObjResult(interp); Tcl_SetWideIntObj(pResult, sqlite3_changes64(pDb->db)); break; } /* $db close ** ** Shutdown the database */ |
︙ | ︙ | |||
3251 3252 3253 3254 3255 3256 3257 | case DB_TOTAL_CHANGES: { Tcl_Obj *pResult; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 2, objv, ""); return TCL_ERROR; } pResult = Tcl_GetObjResult(interp); | | | 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 | case DB_TOTAL_CHANGES: { Tcl_Obj *pResult; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 2, objv, ""); return TCL_ERROR; } pResult = Tcl_GetObjResult(interp); Tcl_SetWideIntObj(pResult, sqlite3_total_changes64(pDb->db)); break; } /* $db trace ?CALLBACK? ** ** Make arrangements to invoke the CALLBACK routine for each SQL statement ** that is executed. The text of the SQL is appended to CALLBACK before |
︙ | ︙ |
Changes to src/vacuum.c.
︙ | ︙ | |||
147 148 149 150 151 152 153 | sqlite3_value *pOut /* Write results here, if not NULL. VACUUM INTO */ ){ int rc = SQLITE_OK; /* Return code from service routines */ Btree *pMain; /* The database being vacuumed */ Btree *pTemp; /* The temporary database we vacuum into */ u32 saved_mDbFlags; /* Saved value of db->mDbFlags */ u64 saved_flags; /* Saved value of db->flags */ | | | | 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 | sqlite3_value *pOut /* Write results here, if not NULL. VACUUM INTO */ ){ int rc = SQLITE_OK; /* Return code from service routines */ Btree *pMain; /* The database being vacuumed */ Btree *pTemp; /* The temporary database we vacuum into */ u32 saved_mDbFlags; /* Saved value of db->mDbFlags */ u64 saved_flags; /* Saved value of db->flags */ i64 saved_nChange; /* Saved value of db->nChange */ i64 saved_nTotalChange; /* Saved value of db->nTotalChange */ u32 saved_openFlags; /* Saved value of db->openFlags */ u8 saved_mTrace; /* Saved trace settings */ Db *pDb = 0; /* Database to detach at end of vacuum */ int isMemDb; /* True if vacuuming a :memory: database */ int nRes; /* Bytes of reserved space at the end of each page */ int nDb; /* Number of attached databases */ const char *zDbMain; /* Schema name of database to vacuum */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
6246 6247 6248 6249 6250 6251 6252 | ** by the number of rows in the table being cleared. If P3 is greater ** than zero, then the value stored in register P3 is also incremented ** by the number of rows in the table being cleared. ** ** See also: Destroy */ case OP_Clear: { | | | 6246 6247 6248 6249 6250 6251 6252 6253 6254 6255 6256 6257 6258 6259 6260 | ** by the number of rows in the table being cleared. If P3 is greater ** than zero, then the value stored in register P3 is also incremented ** by the number of rows in the table being cleared. ** ** See also: Destroy */ case OP_Clear: { i64 nChange; sqlite3VdbeIncrWriteCounter(p, 0); nChange = 0; assert( p->readOnly==0 ); assert( DbMaskTest(p->btreeMask, pOp->p2) ); rc = sqlite3BtreeClearTable(db->aDb[pOp->p2].pBt, (u32)pOp->p1, &nChange); if( pOp->p3 ){ |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
176 177 178 179 180 181 182 | #endif int nCursor; /* Number of entries in apCsr */ int pc; /* Program Counter in parent (calling) frame */ int nOp; /* Size of aOp array */ int nMem; /* Number of entries in aMem */ int nChildMem; /* Number of memory cells for child frame */ int nChildCsr; /* Number of cursors for child frame */ | | | | 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | #endif int nCursor; /* Number of entries in apCsr */ int pc; /* Program Counter in parent (calling) frame */ int nOp; /* Size of aOp array */ int nMem; /* Number of entries in aMem */ int nChildMem; /* Number of memory cells for child frame */ int nChildCsr; /* Number of cursors for child frame */ i64 nChange; /* Statement changes (Vdbe.nChange) */ i64 nDbChange; /* Value of db->nChange */ }; /* Magic number for sanity checking on VdbeFrame objects */ #define SQLITE_FRAME_MAGIC 0x879fb71e /* ** Return a pointer to the array of registers allocated for use |
︙ | ︙ | |||
384 385 386 387 388 389 390 | ynVar nVar; /* Number of entries in aVar[] */ u32 iVdbeMagic; /* Magic number defining state of the SQL statement */ int nMem; /* Number of memory locations currently allocated */ int nCursor; /* Number of slots in apCsr[] */ u32 cacheCtr; /* VdbeCursor row cache generation counter */ int pc; /* The program counter */ int rc; /* Value to return */ | | | 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 | ynVar nVar; /* Number of entries in aVar[] */ u32 iVdbeMagic; /* Magic number defining state of the SQL statement */ int nMem; /* Number of memory locations currently allocated */ int nCursor; /* Number of slots in apCsr[] */ u32 cacheCtr; /* VdbeCursor row cache generation counter */ int pc; /* The program counter */ int rc; /* Value to return */ i64 nChange; /* Number of db changes made since last reset */ int iStatement; /* Statement number (or 0 if has no opened stmt) */ i64 iCurrentTime; /* Value of julianday('now') for this statement */ i64 nFkConstraint; /* Number of imm. FK constraints this VM */ i64 nStmtDefCons; /* Number of def. constraints when stmt started */ i64 nStmtDefImmCons; /* Number of def. imm constraints when stmt started */ Mem *aMem; /* The memory locations */ Mem **apArg; /* Arguments to currently executing user function */ |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
5005 5006 5007 5008 5009 5010 5011 | return SQLITE_OK; } /* ** This routine sets the value to be returned by subsequent calls to ** sqlite3_changes() on the database handle 'db'. */ | | | 5005 5006 5007 5008 5009 5010 5011 5012 5013 5014 5015 5016 5017 5018 5019 | return SQLITE_OK; } /* ** This routine sets the value to be returned by subsequent calls to ** sqlite3_changes() on the database handle 'db'. */ void sqlite3VdbeSetChanges(sqlite3 *db, i64 nChange){ assert( sqlite3_mutex_held(db->mutex) ); db->nChange = nChange; db->nTotalChange += nChange; } /* ** Set a flag in the vdbe to update the change counter when it is finalised |
︙ | ︙ |
Changes to test/changes.test.
︙ | ︙ | |||
12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # Tests for the sqlite3_changes() and sqlite3_total_changes() APIs. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix changes foreach {tn nRow wor} { 1 50 "" 2 50 "WITHOUT ROWID" 3 5000 "" 4 5000 "WITHOUT ROWID" | > > > > > > | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | # Tests for the sqlite3_changes() and sqlite3_total_changes() APIs. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix changes # To test that the change-counters do not suffer from 32-bit signed integer # rollover, add the following line to the array of test cases below. The # test will take will over an hour to run. # # 7 (1<<31)+10 "" # foreach {tn nRow wor} { 1 50 "" 2 50 "WITHOUT ROWID" 3 5000 "" 4 5000 "WITHOUT ROWID" |
︙ | ︙ |