Execute simple SELECT query
kawasin73 opened this issue · 5 comments
sqlite C API: https://www.sqlite.org/cintro.html
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 */
};
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
- interface function, this calls
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;
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];
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;
}