SQLite

Check-in [dd94d6a880]
Login

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

Overview
Comment:Enhance location(X) so that it works with indexes and WITHOUT ROWID tables. The function might return an offset to the main table or to an index, depending on whether the column X would be loaded from the main table or from the index.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | location-function
Files: files | file ages | folders
SHA3-256: dd94d6a880dfec4bddd247239b815b84964f804d24841e25f33f1d46a4b5274d
User & Date: drh 2017-12-29 14:33:54.266
Context
2017-12-29
15:04
Change the function name to sqlite_unsupported_offset(X). Only enable the function if compiled with -DSQLITE_ENABLE_OFFSET_SQL_FUNC. The makefiles add that definition to shell builds. (check-in: 7a7f826e32 user: drh tags: location-function)
14:33
Enhance location(X) so that it works with indexes and WITHOUT ROWID tables. The function might return an offset to the main table or to an index, depending on whether the column X would be loaded from the main table or from the index. (check-in: dd94d6a880 user: drh tags: location-function)
13:35
Merge recent enhancements from trunk. (check-in: 6251e438f2 user: drh tags: location-function)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/btree.c.
4435
4436
4437
4438
4439
4440
4441
4442
4443
4444
4445
4446
4447
4448
4449
4450
4451
/*
** Return the offset into the database file for the start of the
** payload to which the cursor is pointing.
*/
i64 sqlite3BtreeLocation(BtCursor *pCur){
  assert( cursorHoldsMutex(pCur) );
  assert( pCur->eState==CURSOR_VALID );
  assert( pCur->curIntKey );
  getCellInfo(pCur);
  return (i64)pCur->pBt->pageSize*(i64)pCur->pPage->pgno +
         (i64)(pCur->info.pPayload - pCur->pPage->aData);
}

/*
** Return the number of bytes of payload for the entry that pCur is
** currently pointing to.  For table btrees, this will be the amount
** of data.  For index btrees, this will be the size of the key.







<

|







4435
4436
4437
4438
4439
4440
4441

4442
4443
4444
4445
4446
4447
4448
4449
4450
/*
** Return the offset into the database file for the start of the
** payload to which the cursor is pointing.
*/
i64 sqlite3BtreeLocation(BtCursor *pCur){
  assert( cursorHoldsMutex(pCur) );
  assert( pCur->eState==CURSOR_VALID );

  getCellInfo(pCur);
  return (i64)pCur->pBt->pageSize*((i64)pCur->pPage->pgno - 1) +
         (i64)(pCur->info.pPayload - pCur->pPage->aData);
}

/*
** Return the number of bytes of payload for the entry that pCur is
** currently pointing to.  For table btrees, this will be the amount
** of data.  For index btrees, this will be the size of the key.
Changes to src/expr.c.
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
      if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
        if( !pColl ) pColl = db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      if( pDef->funcFlags & SQLITE_FUNC_LOCATION ){
        Expr *pArg = pFarg->a[0].pExpr;
        if( pArg->op==TK_COLUMN ){
          sqlite3VdbeAddOp2(v, OP_Location, pArg->iTable, target);
        }else{
          sqlite3VdbeAddOp2(v, OP_Null, 0, target);
        }
      }else{
        sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
                          constMask, r1, target, (char*)pDef, P4_FUNCDEF);
        sqlite3VdbeChangeP5(v, (u8)nFarg);







|







3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
      if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
        if( !pColl ) pColl = db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      if( pDef->funcFlags & SQLITE_FUNC_LOCATION ){
        Expr *pArg = pFarg->a[0].pExpr;
        if( pArg->op==TK_COLUMN ){
          sqlite3VdbeAddOp3(v, OP_Location, pArg->iTable, pArg->iColumn,target);
        }else{
          sqlite3VdbeAddOp2(v, OP_Null, 0, target);
        }
      }else{
        sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
                          constMask, r1, target, (char*)pDef, P4_FUNCDEF);
        sqlite3VdbeChangeP5(v, (u8)nFarg);
Changes to src/vdbe.c.
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357





2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
  if( p->apCsr[pOp->p1]->nullRow ){
    sqlite3VdbeMemSetNull(aMem + pOp->p3);
    goto jump_to_p2;
  }
  break;
}

/* Opcode: Location P1 P2 * * *
** Synopsis: r[P2] = location(P1)
**
** Store in register r[P2] the location in the database file that is the
** start of the payload for the record at which that cursor P1 is currently
** pointing.





*/
case OP_Location: {          /* out2 */
  VdbeCursor *pC;    /* The VDBE cursor */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  pOut = out2Prerelease(p, pOp);
  if( pC==0 || pC->eCurType!=CURTYPE_BTREE ){
    pOut->flags = MEM_Null;
  }else{
    pOut->u.i = sqlite3BtreeLocation(pC->uc.pCursor);
  }
  break;
}

/* Opcode: Column P1 P2 P3 P4 P5
** Synopsis: r[P3]=PX
**







|
|

|


>
>
>
>
>

|



|

|

|







2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
  if( p->apCsr[pOp->p1]->nullRow ){
    sqlite3VdbeMemSetNull(aMem + pOp->p3);
    goto jump_to_p2;
  }
  break;
}

/* Opcode: Location P1 P2 P3 * *
** Synopsis: r[P3] = location(P1)
**
** Store in register r[P3] the location in the database file that is the
** start of the payload for the record at which that cursor P1 is currently
** pointing.
**
** P2 is the column number for the argument to the location() function.
** This opcode does not use P2 itself, but the P2 value is used by the
** code generator.  The P1, P2, and P3 operands to this opcode are the
** as as for OP_Column.
*/
case OP_Location: {          /* out3 */
  VdbeCursor *pC;    /* The VDBE cursor */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  pOut = &p->aMem[pOp->p3];
  if( pC==0 || pC->eCurType!=CURTYPE_BTREE ){
    sqlite3VdbeMemSetNull(pOut);
  }else{
    sqlite3VdbeMemSetInt64(pOut, sqlite3BtreeLocation(pC->uc.pCursor));
  }
  break;
}

/* Opcode: Column P1 P2 P3 P4 P5
** Synopsis: r[P3]=PX
**
Changes to src/where.c.
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
     && !db->mallocFailed
    ){
      last = sqlite3VdbeCurrentAddr(v);
      k = pLevel->addrBody;
      pOp = sqlite3VdbeGetOp(v, k);
      for(; k<last; k++, pOp++){
        if( pOp->p1!=pLevel->iTabCur ) continue;
        if( pOp->opcode==OP_Column ){
          int x = pOp->p2;
          assert( pIdx->pTable==pTab );
          if( !HasRowid(pTab) ){
            Index *pPk = sqlite3PrimaryKeyIndex(pTab);
            x = pPk->aiColumn[x];
            assert( x>=0 );
          }







|







5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
     && !db->mallocFailed
    ){
      last = sqlite3VdbeCurrentAddr(v);
      k = pLevel->addrBody;
      pOp = sqlite3VdbeGetOp(v, k);
      for(; k<last; k++, pOp++){
        if( pOp->p1!=pLevel->iTabCur ) continue;
        if( pOp->opcode==OP_Column || pOp->opcode==OP_Location ){
          int x = pOp->p2;
          assert( pIdx->pTable==pTab );
          if( !HasRowid(pTab) ){
            Index *pPk = sqlite3PrimaryKeyIndex(pTab);
            x = pPk->aiColumn[x];
            assert( x>=0 );
          }
Changes to test/func6.test.
24
25
26
27
28
29
30
31
32
33
34
35
36
} {abc001 integer abc002 integer}
do_execsql_test func6-120 {
  SELECT a, typeof(location(+a)) FROM t1 ORDER BY rowid LIMIT 2;
} {abc001 null abc002 null}
do_execsql_test func6-130 {
  CREATE INDEX t1a ON t1(a);
  SELECT a, typeof(location(a)) FROM t1 ORDER BY a LIMIT 2;
} {abc001 null abc002 null}
do_execsql_test func6-140 {
  SELECT a, typeof(location(a)) FROM t1 NOT INDEXED ORDER BY a LIMIT 2;
} {abc001 integer abc002 integer}

finish_test







|





24
25
26
27
28
29
30
31
32
33
34
35
36
} {abc001 integer abc002 integer}
do_execsql_test func6-120 {
  SELECT a, typeof(location(+a)) FROM t1 ORDER BY rowid LIMIT 2;
} {abc001 null abc002 null}
do_execsql_test func6-130 {
  CREATE INDEX t1a ON t1(a);
  SELECT a, typeof(location(a)) FROM t1 ORDER BY a LIMIT 2;
} {abc001 integer abc002 integer}
do_execsql_test func6-140 {
  SELECT a, typeof(location(a)) FROM t1 NOT INDEXED ORDER BY a LIMIT 2;
} {abc001 integer abc002 integer}

finish_test