kawasin73/prsqlite

Execute simple SELECT query

kawasin73 opened this issue · 5 comments

https://www.sqlite.org/c3ref/column_blob.html

The pointers returned are valid until a type conversion occurs as described above, or until sqlite3_step() or sqlite3_reset() or sqlite3_finalize() is called. The memory space used to hold strings and BLOBs is freed automatically. Do not pass the pointers returned from sqlite3_column_blob(), sqlite3_column_text(), etc. into sqlite3_free().

KeyInfo

  • struct KeyInfo is the select condition.
/*
** An instance of the following structure is passed as the first
** argument to sqlite3VdbeKeyCompare and is used to control the
** comparison of the two index keys.
**
** Note that aSortOrder[] and aColl[] have nField+1 slots.  There
** are nField slots for the columns of an index then one extra slot
** for the rowid at the end.
*/
struct KeyInfo {
  u32 nRef;           /* Number of references to this KeyInfo object */
  u8 enc;             /* Text encoding - one of the SQLITE_UTF* values */
  u16 nKeyField;      /* Number of key columns in the index */
  u16 nAllField;      /* Total columns, including key plus others */
  sqlite3 *db;        /* The database connection */
  u8 *aSortFlags;     /* Sort order for each column. */
  CollSeq *aColl[1];  /* Collating sequence for each term of the key */
};
  • The actual condition is CollSeq.
/*
** A "Collating Sequence" is defined by an instance of the following
** structure. Conceptually, a collating sequence consists of a name and
** a comparison routine that defines the order of that sequence.
**
** If CollSeq.xCmp is NULL, it means that the
** collating sequence is undefined.  Indices built on an undefined
** collating sequence may not be read or written.
*/
struct CollSeq {
  char *zName;          /* Name of the collating sequence, UTF-8 encoded */
  u8 enc;               /* Text encoding handled by xCmp() */
  void *pUser;          /* First argument to xCmp() */
  int (*xCmp)(void*,int, const void*, int, const void*);
  void (*xDel)(void*);  /* Destructor for pUser */
};

#1 (comment)

KeyInfo in btree

KeyInfo is used for index in btree layer.
https://github.com/sqlite/sqlite/blob/master/src/btree.c#L5472-L5475

  /* If pCur->pKeyInfo is not NULL, then the caller that opened this cursor
  ** expected to open it on an index b-tree. Otherwise, if pKeyInfo is
  ** NULL, the caller expects a table b-tree. If this is not the case,
  ** return an SQLITE_CORRUPT error.

How KeyInfo is generated

sqlite3CodeRhsOfIN() in src/expr.c is for sub query.

  • sqlite3_step()
    • interface function, this calls sqlite3Step() mainly
    • vdbeapi.c
  • sqlite3Step()
    • vdbeapi.c
sqlite> CREATE TABLE example(id integer primary key, col1, col2);
sqlite> INSERT INTO example(id, col1, col2) VALUES (1, 2, 3);
sqlite> INSERT INTO example(id, col1, col2) VALUES (2, 4, 5);
sqlite> INSERT INTO example(id, col1, col2) VALUES (5, 6, 7);
sqlite> INSERT INTO example(id, col1, col2) VALUES (3, 8, 9);
sqlite> INSERT INTO example(id, col1, col2) VALUES (3, 8, 9);
sqlite> explain select * from example;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       0     2     0     3              0   root=2 iDb=0; example
2     Explain        2     0     0     SCAN example   0
3     Rewind         0     9     0                    0
4       Rowid          0     1     0                    0   r[1]=example.rowid
5       Column         0     1     2                    0   r[2]=example.col1
6       Column         0     2     3                    0   r[3]=example.col2
7       ResultRow      1     3     0                    0   output=r[1..3]
8     Next           0     4     0                    1
9     Halt           0     0     0                    0
10    Transaction    0     0     1     0              1   usesStmtJournal=0
11    Goto           0     1     0                    0
sqlite> select * from example;
1|2|3
2|4|5
3|8|9
5|6|7
sqlite> explain select * from example where id = 3;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       0     2     0     3              0   root=2 iDb=0; example
2     Explain        2     0     0     SEARCH example USING INTEGER PRIMARY KEY (rowid=?)  0
3     Integer        3     1     0                    0   r[1]=3
4     SeekRowid      0     9     1                    0   intkey=r[1]
5     Rowid          0     2     0                    0   r[2]=example.rowid
6     Column         0     1     3                    0   r[3]=example.col1
7     Column         0     2     4                    0   r[4]=example.col2
8     ResultRow      2     3     0                    0   output=r[2..4]
9     Halt           0     0     0                    0
10    Transaction    0     0     1     0              1   us
sqlite> explain select * from example where col2 = 3;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     12    0                    0   Start at 12
1     OpenRead       0     2     0     3              0   root=2 iDb=0; example
2     Explain        2     0     0     SCAN example   0
3     Rewind         0     11    0                    0
4       Column         0     2     1                    0   r[1]=example.col2
5       Ne             2     10    1     BINARY-8       81  if r[1]!=r[2] goto 10
6       Rowid          0     3     0                    0   r[3]=example.rowid
7       Column         0     1     4                    0   r[4]=example.col1
8       Column         0     2     5                    0   r[5]=example.col2
9       ResultRow      3     3     0                    0   output=r[3..5]
10    Next           0     4     0                    1
11    Halt           0     0     0                    0
12    Transaction    0     0     1     0              1   usesStmtJournal=0
13    Integer        3     2     0                    0   r[2]=3
14    Goto           0     1     0                    0
sqlite> explain select * from example where id > 3;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       0     2     0     3              0   root=2 iDb=0; example
2     Explain        2     0     0     SEARCH example USING INTEGER PRIMARY KEY (rowid>?)  0
3     SeekGT         0     9     1                    0   key=r[1]; pk
4       Rowid          0     2     0                    0   r[2]=example.rowid
5       Column         0     1     3                    0   r[3]=example.col1
6       Column         0     2     4                    0   r[4]=example.col2
7       ResultRow      2     3     0                    0   output=r[2..4]
8     Next           0     4     0                    0
9     Halt           0     0     0                    0
10    Transaction    0     0     1     0              1   usesStmtJournal=0
11    Integer        3     1     0                    0   r[1]=3
12    Goto           0     1     0                    0

OP_column

It uses BtCursor in VdbeCursor.

  VdbeCursor *pC;    /* The VDBE cursor */
  BtCursor *pCrsr;   /* The B-Tree cursor corresponding to pC */
/*
** A VdbeCursor is an superclass (a wrapper) for various cursor objects:
**
**      * A b-tree cursor
**          -  In the main database or in an ephemeral database
**          -  On either an index or a table
**      * A sorter
**      * A virtual table
**      * A one-row "pseudotable" stored in a single register
*/
typedef struct VdbeCursor VdbeCursor;

how to parse records?

pC->nHdrParsed holds the cached records' offsets & types.

/* Fill in pC->aType[i] and aOffset[i] values through the p2-th field. */
    op_column_read_header:
      i = pC->nHdrParsed;
      offset64 = aOffset[i];
      zHdr = zData + pC->iHdrOffset;
      zEndHdr = zData + aOffset[0];
      testcase( zHdr>=zEndHdr );
      do{
        if( (pC->aType[i] = t = zHdr[0])<0x80 ){
          zHdr++;
          offset64 += sqlite3VdbeOneByteSerialTypeLen(t);
        }else{
          zHdr += sqlite3GetVarint32(zHdr, &t);
          pC->aType[i] = t;
          offset64 += sqlite3VdbeSerialTypeLen(t);
        }
        aOffset[++i] = (u32)(offset64 & 0xffffffff);
      }while( (u32)i<=p2 && zHdr<zEndHdr );

How to handle overflow pages

The metadata for each column of a record is packed in the header. The header should not that big.

aOffset[0] is the whole header size. If it is bigger than the fetched buffer size, it loads the payload with aOffset[0] size into Mem sMem.

Mem sMem;          /* For storing the record being decoded */

zHdr = zData + pC->iHdrOffset;

If a column in a record is on overflow page, it copies the corresponding data into Mem and parse the data.

      if( len>db->aLimit[SQLITE_LIMIT_LENGTH] ) goto too_big;
      rc = sqlite3VdbeMemFromBtree(pC->uc.pCursor, aOffset[p2], len, pDest);
      if( rc!=SQLITE_OK ) goto abort_due_to_error;
      sqlite3VdbeSerialGet((const u8*)pDest->z, t, pDest);
      pDest->flags &= ~MEM_Ephem;

https://github.com/sqlite/sqlite/blob/2ecadd8869a5aafeaee736fbfd0dcb15990480c7/src/vdbe.c#L3087-L3094

If the column is on non-overflow page, but text or string, it copies the data anyway

      /* If the column value is a string, we need a persistent value, not
      ** a MEM_Ephem value.  This branch is a fast short-cut that is equivalent
      ** to calling sqlite3VdbeSerialGet() and sqlite3VdbeDeephemeralize().
      */
      static const u16 aFlag[] = { MEM_Blob, MEM_Str|MEM_Term };
      pDest->n = len = (t-12)/2;
      pDest->enc = encoding;
      if( pDest->szMalloc < len+2 ){
        if( len>db->aLimit[SQLITE_LIMIT_LENGTH] ) goto too_big;
        pDest->flags = MEM_Null;
        if( sqlite3VdbeMemGrow(pDest, len+2, 0) ) goto no_mem;
      }else{
        pDest->z = pDest->zMalloc;
      }
      memcpy(pDest->z, zData, len);
      pDest->z[len] = 0;
      pDest->z[len+1] = 0;
      pDest->flags = aFlag[t&1];

https://github.com/sqlite/sqlite/blob/2ecadd8869a5aafeaee736fbfd0dcb15990480c7/src/vdbe.c#L3042-L3059

Get value

vdbe.c

case OP_Column: {            /* ncycle */

    /* This is the common case where the desired content fits on the original
    ** page - where the content is not on an overflow page */
    zData = pC->aRow + aOffset[p2];
    if( t<12 ){
      sqlite3VdbeSerialGet(zData, t, pDest);
    }else{
      /* If the column value is a string, we need a persistent value, not
      ** a MEM_Ephem value.  This branch is a fast short-cut that is equivalent
      ** to calling sqlite3VdbeSerialGet() and sqlite3VdbeDeephemeralize().
      */

      memcpy(pDest->z, zData, len);
    /* This branch happens only when content is on overflow pages */
    if( ((pOp->p5 & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG))!=0
          && ((t>=12 && (t&1)==0) || (pOp->p5 & OPFLAG_TYPEOFARG)!=0))
     || (len = sqlite3VdbeSerialTypeLen(t))==0
    ){
      /* Content is irrelevant for
      **    1. the typeof() function,
      **    2. the length(X) function if X is a blob, and
      **    3. if the content length is zero.
      ** So we might as well use bogus content rather than reading
      ** content from disk. 
      **
      ** Although sqlite3VdbeSerialGet() may read at most 8 bytes from the
      ** buffer passed to it, debugging function VdbeMemPrettyPrint() may
      ** read more.  Use the global constant sqlite3CtypeMap[] as the array,
      ** as that array is 256 bytes long (plenty for VdbeMemPrettyPrint())
      ** and it begins with a bunch of zeros.
      */
      sqlite3VdbeSerialGet((u8*)sqlite3CtypeMap, t, pDest);
    }else{
      if( len>db->aLimit[SQLITE_LIMIT_LENGTH] ) goto too_big;
      rc = sqlite3VdbeMemFromBtree(pC->uc.pCursor, aOffset[p2], len, pDest);
      if( rc!=SQLITE_OK ) goto abort_due_to_error;
      sqlite3VdbeSerialGet((const u8*)pDest->z, t, pDest);
      pDest->flags &= ~MEM_Ephem;
    }