/ Check-in [10556ee4]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Experimental API sqlite3_stmt_refresh() to force a prepared statement to recompile if it needs to due to a schema change or other factor.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | sqlite3_stmt_refresh
Files: files | file ages | folders
SHA3-256: 10556ee454edf60dd6542fdf9017f713c783b9ca1b86f25f45e3c17ffb184e24
User & Date: drh 2017-07-20 16:55:51
Context
2017-07-20
16:55
Experimental API sqlite3_stmt_refresh() to force a prepared statement to recompile if it needs to due to a schema change or other factor. Leaf check-in: 10556ee4 user: drh tags: sqlite3_stmt_refresh
15:08
Enhance the built-in date/time functions so that they can be used in CHECK constraints, in the WHERE clause or partial indexes, and index expressions, provided that none of the non-deterministic keywords ("now", "localtime", "utc") are used as arguments. check-in: a90c062d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

3738
3739
3740
3741
3742
3743
3744











3745
3746
3747
3748
3749
3750
3751
** to locate all prepared statements associated with a database 
** connection that are in need of being reset.  This can be used,
** for example, in diagnostic routines to search for prepared 
** statements that are holding a transaction open.
*/
int sqlite3_stmt_busy(sqlite3_stmt*);












/*
** CAPI3REF: Dynamically Typed Value Object
** KEYWORDS: {protected sqlite3_value} {unprotected sqlite3_value}
**
** SQLite uses the sqlite3_value object to represent all values
** that can be stored in a database table. SQLite uses dynamic typing
** for the values it stores.  ^Values stored in sqlite3_value objects







>
>
>
>
>
>
>
>
>
>
>







3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
** to locate all prepared statements associated with a database 
** connection that are in need of being reset.  This can be used,
** for example, in diagnostic routines to search for prepared 
** statements that are holding a transaction open.
*/
int sqlite3_stmt_busy(sqlite3_stmt*);

/*
** CAPI3REF: Recompile A Prepared Statement If Needed
** METHOD: sqlite3_stmt
**
** ^The sqlite3_stmt_refresh(S) examines the [prepared statement] S
** and attempts to recompile it if it has expired, for example due
** to a schema change.  ^If the prepared statement S is up-to-date and
** ready for use, then sqlite3_stmt_refresh(S) is a no-op.
*/
int sqlite3_stmt_refresh(sqlite3_stmt*);

/*
** CAPI3REF: Dynamically Typed Value Object
** KEYWORDS: {protected sqlite3_value} {unprotected sqlite3_value}
**
** SQLite uses the sqlite3_value object to represent all values
** that can be stored in a database table. SQLite uses dynamic typing
** for the values it stores.  ^Values stored in sqlite3_value objects

Changes to src/test1.c.

2633
2634
2635
2636
2637
2638
2639


























2640
2641
2642
2643
2644
2645
2646
....
7487
7488
7489
7490
7491
7492
7493

7494
7495
7496
7497
7498
7499
7500
  }

  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  rc = sqlite3_stmt_busy(pStmt);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(rc));
  return TCL_OK;
}



























/*
** Usage:  uses_stmt_journal  STMT
**
** Return true if STMT uses a statement journal.
*/
static int SQLITE_TCLAPI uses_stmt_journal(
................................................................................
     { "sqlite3_changes",               test_changes       ,0 },
     { "sqlite3_step",                  test_step          ,0 },
     { "sqlite3_sql",                   test_sql           ,0 },
     { "sqlite3_expanded_sql",          test_ex_sql        ,0 },
     { "sqlite3_next_stmt",             test_next_stmt     ,0 },
     { "sqlite3_stmt_readonly",         test_stmt_readonly ,0 },
     { "sqlite3_stmt_busy",             test_stmt_busy     ,0 },

     { "uses_stmt_journal",             uses_stmt_journal ,0 },

     { "sqlite3_release_memory",        test_release_memory,     0},
     { "sqlite3_db_release_memory",     test_db_release_memory,  0},
     { "sqlite3_db_cacheflush",         test_db_cacheflush,      0},
     { "sqlite3_system_errno",          test_system_errno,       0},
     { "sqlite3_db_filename",           test_db_filename,        0},







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
....
7513
7514
7515
7516
7517
7518
7519
7520
7521
7522
7523
7524
7525
7526
7527
  }

  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  rc = sqlite3_stmt_busy(pStmt);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(rc));
  return TCL_OK;
}

/*
** Usage:  sqlite3_stmt_refresh  STMT
**
** Recompile the STMT prepared statement if it needs to be recompiled.
*/
static int SQLITE_TCLAPI test_stmt_refresh(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3_stmt *pStmt;
  int rc;

  if( objc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"",
        Tcl_GetStringFromObj(objv[0], 0), " STMT", 0);
    return TCL_ERROR;
  }

  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  rc = sqlite3_stmt_refresh(pStmt);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(rc));
  return TCL_OK;
}

/*
** Usage:  uses_stmt_journal  STMT
**
** Return true if STMT uses a statement journal.
*/
static int SQLITE_TCLAPI uses_stmt_journal(
................................................................................
     { "sqlite3_changes",               test_changes       ,0 },
     { "sqlite3_step",                  test_step          ,0 },
     { "sqlite3_sql",                   test_sql           ,0 },
     { "sqlite3_expanded_sql",          test_ex_sql        ,0 },
     { "sqlite3_next_stmt",             test_next_stmt     ,0 },
     { "sqlite3_stmt_readonly",         test_stmt_readonly ,0 },
     { "sqlite3_stmt_busy",             test_stmt_busy     ,0 },
     { "sqlite3_stmt_refresh",          test_stmt_refresh  ,0 },
     { "uses_stmt_journal",             uses_stmt_journal ,0 },

     { "sqlite3_release_memory",        test_release_memory,     0},
     { "sqlite3_db_release_memory",     test_db_release_memory,  0},
     { "sqlite3_db_cacheflush",         test_db_cacheflush,      0},
     { "sqlite3_system_errno",          test_system_errno,       0},
     { "sqlite3_db_filename",           test_db_filename,        0},

Changes to src/vdbe.c.

3187
3188
3189
3190
3191
3192
3193



3194
3195
3196
3197
3198
3199
3200
    if( db->aDb[pOp->p1].pSchema->schema_cookie!=iMeta ){
      sqlite3ResetOneSchema(db, pOp->p1);
    }
    p->expired = 1;
    rc = SQLITE_SCHEMA;
  }
  if( rc ) goto abort_due_to_error;



  break;
}

/* Opcode: ReadCookie P1 P2 P3 * *
**
** Read cookie number P3 from database P1 and write it into register P2.
** P3==1 is the schema version.  P3==2 is the database format.







>
>
>







3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
    if( db->aDb[pOp->p1].pSchema->schema_cookie!=iMeta ){
      sqlite3ResetOneSchema(db, pOp->p1);
    }
    p->expired = 1;
    rc = SQLITE_SCHEMA;
  }
  if( rc ) goto abort_due_to_error;
  if( p->stopAfterInit && pOp[1].opcode!=OP_Transaction ){
    goto vdbe_return;
  }
  break;
}

/* Opcode: ReadCookie P1 P2 P3 * *
**
** Read cookie number P3 from database P1 and write it into register P2.
** P3==1 is the schema version.  P3==2 is the database format.

Changes to src/vdbeInt.h.

388
389
390
391
392
393
394

395
396
397
398
399
400
401
  bft doingRerun:1;       /* True if rerunning after an auto-reprepare */
  bft explain:2;          /* True if EXPLAIN present on SQL command */
  bft changeCntOn:1;      /* True to update the change-counter */
  bft runOnlyOnce:1;      /* Automatically expire on reset */
  bft usesStmtJournal:1;  /* True if uses a statement journal */
  bft readOnly:1;         /* True for statements that do not write */
  bft bIsReader:1;        /* True for statements that read */

  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
  void *pFree;            /* Free this when deleting the vdbe */
  VdbeFrame *pFrame;      /* Parent frame */
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */







>







388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
  bft doingRerun:1;       /* True if rerunning after an auto-reprepare */
  bft explain:2;          /* True if EXPLAIN present on SQL command */
  bft changeCntOn:1;      /* True to update the change-counter */
  bft runOnlyOnce:1;      /* Automatically expire on reset */
  bft usesStmtJournal:1;  /* True if uses a statement journal */
  bft readOnly:1;         /* True for statements that do not write */
  bft bIsReader:1;        /* True for statements that read */
  bft stopAfterInit:1;    /* Halt after running the last OP_Transaction */
  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
  void *pFree;            /* Free this when deleting the vdbe */
  VdbeFrame *pFrame;      /* Parent frame */
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */

Changes to src/vdbeapi.c.

1603
1604
1605
1606
1607
1608
1609






















1610
1611
1612
1613
1614
1615
1616
/*
** Return true if the prepared statement is in need of being reset.
*/
int sqlite3_stmt_busy(sqlite3_stmt *pStmt){
  Vdbe *v = (Vdbe*)pStmt;
  return v!=0 && v->magic==VDBE_MAGIC_RUN && v->pc>=0;
}























/*
** Return a pointer to the next prepared statement after pStmt associated
** with database connection pDb.  If pStmt is NULL, return the first
** prepared statement for the database connection.  Return NULL if there
** are no more.
*/







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
/*
** Return true if the prepared statement is in need of being reset.
*/
int sqlite3_stmt_busy(sqlite3_stmt *pStmt){
  Vdbe *v = (Vdbe*)pStmt;
  return v!=0 && v->magic==VDBE_MAGIC_RUN && v->pc>=0;
}

/*
** Recompile the prepared statement if it has expired
*/
int sqlite3_stmt_refresh(sqlite3_stmt *pStmt){
  int rc = SQLITE_OK;
  if( !sqlite3_stmt_busy(pStmt) ){
    VdbeOp *aOp;
    Vdbe *v = (Vdbe*)pStmt;
    sqlite3_mutex_enter(v->db->mutex);
    aOp = v->aOp;
    assert( aOp[0].opcode==OP_Init );
    if( aOp[aOp[0].p2].opcode==OP_Transaction ){
      v->stopAfterInit = 1;
      rc = sqlite3_step(pStmt);
      v->stopAfterInit = 0;
      sqlite3_reset(pStmt);
    }
    sqlite3_mutex_leave(v->db->mutex);
  }
  return rc;
}

/*
** Return a pointer to the next prepared statement after pStmt associated
** with database connection pDb.  If pStmt is NULL, return the first
** prepared statement for the database connection.  Return NULL if there
** are no more.
*/

Changes to test/capi3d.test.

175
176
177
178
179
180
181

182
























183
  VACUUM
} {0 {}}

do_test capi3d-4.2.6 {
  sqlite3_finalize $::s1
} {SQLITE_OK}



























finish_test







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
  VACUUM
} {0 {}}

do_test capi3d-4.2.6 {
  sqlite3_finalize $::s1
} {SQLITE_OK}

# Tests for the sqlite3_stmt_refresh() interface
#
do_execsql_test capi3d-5.1 {
  DROP TABLE IF EXISTS t5;
  CREATE TABLE t5(a,b);
  INSERT INTO t5 VALUES(1,2),(3,4);
}
do_test capi3d-5.2 {
  set ::s1 [sqlite3_prepare_v2 db "SELECT * FROM t5" -1 notused]
  sqlite3 db2 test.db
  db2 eval {SELECT * FROM t5 ORDER BY a}
} {1 2 3 4}
do_test capi3d-5.3 {
  sqlite3_column_count $::s1
} 2
do_test capi3d-5.4 {
  db2 eval {ALTER TABLE t5 ADD COLUMN c DEFAULT 9}
  sqlite3_column_count $::s1
} 2
do_test capi3d-5.5 {
  sqlite3_stmt_refresh $::s1
  sqlite3_column_count $::s1
} 3
sqlite3_finalize $::s1
db2 close

finish_test