/ Check-in [51be9558]
Login

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

Overview
Comment:Add an experimental location(X) SQL function that attempt to return the location of the payload within the database for the record that contains column X. location(X) returns NULL if X is not an ordinary table column or if SQLite cannot figure out the location because it is using a covering index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | location-function
Files: files | file ages | folders
SHA3-256: 51be9558164301c5dd4df23ab8b3e67de0b522f8d36f79f3d84d45d3dc2a83a4
User & Date: drh 2017-12-16 20:20:37
Context
2017-12-29
13:35
Merge recent enhancements from trunk. check-in: 6251e438 user: drh tags: location-function
2017-12-16
20:20
Add an experimental location(X) SQL function that attempt to return the location of the payload within the database for the record that contains column X. location(X) returns NULL if X is not an ordinary table column or if SQLite cannot figure out the location because it is using a covering index. check-in: 51be9558 user: drh tags: location-function
19:36
Add the sqlite3_vtab_collation() function, which allows an xBestIndex callback to determine the collation sequence that SQLite will use for a comparison. And the SQLITE_DBCONFIG_FULL_EQP configuration option, which enhances the output of "EXPLAIN QUERY PLAN" so that it includes statements run by triggers. And the code for the sqlite3_expert extension and command line application. check-in: 4c782c95 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  4427   4427   i64 sqlite3BtreeIntegerKey(BtCursor *pCur){
  4428   4428     assert( cursorHoldsMutex(pCur) );
  4429   4429     assert( pCur->eState==CURSOR_VALID );
  4430   4430     assert( pCur->curIntKey );
  4431   4431     getCellInfo(pCur);
  4432   4432     return pCur->info.nKey;
  4433   4433   }
         4434  +
         4435  +/*
         4436  +** Return the offset into the database file for the start of the
         4437  +** payload to which the cursor is pointing.
         4438  +*/
         4439  +i64 sqlite3BtreeLocation(BtCursor *pCur){
         4440  +  assert( cursorHoldsMutex(pCur) );
         4441  +  assert( pCur->eState==CURSOR_VALID );
         4442  +  assert( pCur->curIntKey );
         4443  +  getCellInfo(pCur);
         4444  +  return (i64)pCur->pBt->pageSize*(i64)pCur->pPage->pgno +
         4445  +         (i64)(pCur->info.pPayload - pCur->pPage->aData);
         4446  +}
  4434   4447   
  4435   4448   /*
  4436   4449   ** Return the number of bytes of payload for the entry that pCur is
  4437   4450   ** currently pointing to.  For table btrees, this will be the amount
  4438   4451   ** of data.  For index btrees, this will be the size of the key.
  4439   4452   **
  4440   4453   ** The caller must guarantee that the cursor is pointing to a non-NULL

Changes to src/btree.h.

   287    287                          int flags, int seekResult);
   288    288   int sqlite3BtreeFirst(BtCursor*, int *pRes);
   289    289   int sqlite3BtreeLast(BtCursor*, int *pRes);
   290    290   int sqlite3BtreeNext(BtCursor*, int flags);
   291    291   int sqlite3BtreeEof(BtCursor*);
   292    292   int sqlite3BtreePrevious(BtCursor*, int flags);
   293    293   i64 sqlite3BtreeIntegerKey(BtCursor*);
          294  +i64 sqlite3BtreeLocation(BtCursor*);
   294    295   int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);
   295    296   const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt);
   296    297   u32 sqlite3BtreePayloadSize(BtCursor*);
   297    298   
   298    299   char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
   299    300   struct Pager *sqlite3BtreePager(Btree*);
   300    301   i64 sqlite3BtreeRowCountEst(BtCursor*);

Changes to src/expr.c.

  3867   3867           pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
  3868   3868         }
  3869   3869   #endif
  3870   3870         if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  3871   3871           if( !pColl ) pColl = db->pDfltColl; 
  3872   3872           sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
  3873   3873         }
  3874         -      sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
  3875         -                        constMask, r1, target, (char*)pDef, P4_FUNCDEF);
  3876         -      sqlite3VdbeChangeP5(v, (u8)nFarg);
         3874  +      if( pDef->funcFlags & SQLITE_FUNC_LOCATION ){
         3875  +        Expr *pArg = pFarg->a[0].pExpr;
         3876  +        if( pArg->op==TK_COLUMN ){
         3877  +          sqlite3VdbeAddOp2(v, OP_Location, pArg->iTable, target);
         3878  +        }else{
         3879  +          sqlite3VdbeAddOp2(v, OP_Null, 0, target);
         3880  +        }
         3881  +      }else{
         3882  +        sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
         3883  +                          constMask, r1, target, (char*)pDef, P4_FUNCDEF);
         3884  +        sqlite3VdbeChangeP5(v, (u8)nFarg);
         3885  +      }
  3877   3886         if( nFarg && constMask==0 ){
  3878   3887           sqlite3ReleaseTempRange(pParse, r1, nFarg);
  3879   3888         }
  3880   3889         return target;
  3881   3890       }
  3882   3891   #ifndef SQLITE_OMIT_SUBQUERY
  3883   3892       case TK_EXISTS:

Changes to src/func.c.

  1795   1795   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  1796   1796       FUNCTION2(unlikely,          1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1797   1797       FUNCTION2(likelihood,        2, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1798   1798       FUNCTION2(likely,            1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1799   1799   #ifdef SQLITE_DEBUG
  1800   1800       FUNCTION2(affinity,          1, 0, 0, noopFunc,  SQLITE_FUNC_AFFINITY),
  1801   1801   #endif
         1802  +    FUNCTION2(location,          1, 0, 0, noopFunc,  SQLITE_FUNC_LOCATION|
         1803  +                                                     SQLITE_FUNC_TYPEOF),
  1802   1804       FUNCTION(ltrim,              1, 1, 0, trimFunc         ),
  1803   1805       FUNCTION(ltrim,              2, 1, 0, trimFunc         ),
  1804   1806       FUNCTION(rtrim,              1, 2, 0, trimFunc         ),
  1805   1807       FUNCTION(rtrim,              2, 2, 0, trimFunc         ),
  1806   1808       FUNCTION(trim,               1, 3, 0, trimFunc         ),
  1807   1809       FUNCTION(trim,               2, 3, 0, trimFunc         ),
  1808   1810       FUNCTION(min,               -1, 0, 1, minmaxFunc       ),

Changes to src/sqliteInt.h.

  1626   1626   #define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */
  1627   1627   #define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */
  1628   1628   #define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */
  1629   1629   #define SQLITE_FUNC_MINMAX   0x1000 /* True for min() and max() aggregates */
  1630   1630   #define SQLITE_FUNC_SLOCHNG  0x2000 /* "Slow Change". Value constant during a
  1631   1631                                       ** single query - might change over time */
  1632   1632   #define SQLITE_FUNC_AFFINITY 0x4000 /* Built-in affinity() function */
         1633  +#define SQLITE_FUNC_LOCATION 0x8000 /* Built-in location() function */
  1633   1634   
  1634   1635   /*
  1635   1636   ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
  1636   1637   ** used to create the initializers for the FuncDef structures.
  1637   1638   **
  1638   1639   **   FUNCTION(zName, nArg, iArg, bNC, xFunc)
  1639   1640   **     Used to create a scalar function definition of a function zName

Changes to src/vdbe.c.

  2344   2344     assert( p->apCsr[pOp->p1]!=0 );
  2345   2345     if( p->apCsr[pOp->p1]->nullRow ){
  2346   2346       sqlite3VdbeMemSetNull(aMem + pOp->p3);
  2347   2347       goto jump_to_p2;
  2348   2348     }
  2349   2349     break;
  2350   2350   }
         2351  +
         2352  +/* Opcode: Location P1 P2 * * *
         2353  +** Synopsis: r[P2] = location(P1)
         2354  +**
         2355  +** Store in register r[P2] the location in the database file that is the
         2356  +** start of the payload for the record at which that cursor P1 is currently
         2357  +** pointing.
         2358  +*/
         2359  +case OP_Location: {          /* out2 */
         2360  +  VdbeCursor *pC;    /* The VDBE cursor */
         2361  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         2362  +  pC = p->apCsr[pOp->p1];
         2363  +  pOut = out2Prerelease(p, pOp);
         2364  +  if( pC==0 || pC->eCurType!=CURTYPE_BTREE ){
         2365  +    pOut->flags = MEM_Null;
         2366  +  }else{
         2367  +    pOut->u.i = sqlite3BtreeLocation(pC->uc.pCursor);
         2368  +  }
         2369  +  break;
         2370  +}
  2351   2371   
  2352   2372   /* Opcode: Column P1 P2 P3 P4 P5
  2353   2373   ** Synopsis: r[P3]=PX
  2354   2374   **
  2355   2375   ** Interpret the data that cursor P1 points to as a structure built using
  2356   2376   ** the MakeRecord instruction.  (See the MakeRecord opcode for additional
  2357   2377   ** information about the format of the data.)  Extract the P2-th column

Added test/func6.test.

            1  +# 2017-12-16
            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  +# Test cases for the location() function.
           13  +#
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +
           17  +do_execsql_test func6-100 {
           18  +  CREATE TABLE t1(a,b,c,d);
           19  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
           20  +   INSERT INTO t1(a,b,c,d) SELECT printf('abc%03x',x), x, 1000-x, NULL FROM c;
           21  +}
           22  +do_execsql_test func6-110 {
           23  +  SELECT a, typeof(location(a)) FROM t1 ORDER BY rowid LIMIT 2;
           24  +} {abc001 integer abc002 integer}
           25  +do_execsql_test func6-120 {
           26  +  SELECT a, typeof(location(+a)) FROM t1 ORDER BY rowid LIMIT 2;
           27  +} {abc001 null abc002 null}
           28  +do_execsql_test func6-130 {
           29  +  CREATE INDEX t1a ON t1(a);
           30  +  SELECT a, typeof(location(a)) FROM t1 ORDER BY a LIMIT 2;
           31  +} {abc001 null abc002 null}
           32  +do_execsql_test func6-140 {
           33  +  SELECT a, typeof(location(a)) FROM t1 NOT INDEXED ORDER BY a LIMIT 2;
           34  +} {abc001 integer abc002 integer}
           35  +
           36  +finish_test