Investigate sqlite spec

[kawasin73]

• SQLite Documentation
• https://github.com/sqlite/sqlite

main important document

• Architecture of SQLite
• The SQLite Bytecode Engine

[kawasin73]

File format

Database File Format

Open sqlite

• sqlite3_open() (main.c)
• openDatabase()
  • sqlite3BtreeOpen() (btree.c) for &pBt->pPager
    • sqlite3PagerOpen() (pager.c)
      • allocate pager
      • sqlite3OsOpen()
      • setSectorSize() & update szPageDflt
      • sqlite3PagerSetPagesize(pPager, &szPageDflt, -1);
        • just set szPageDflt to pPager->pageSize.
        • load database size, allocate 1 page for temporary use.
        • if szPageDflt is zero returns pPager->pageSize.
      • This does not seem to read any header?
    • sqlite3PagerReadFileheader()
      • just load header to zDbHeader in stack memory.
    • pBt->pageSize = (zDbHeader[16]<<8) | (zDbHeader[17]<<16);
      • read big endian
    • validate page size
  • sqlite3BtreeEnter()
    • Enter a mutex on the given BTree object.

initialize page size

setSectorSize()

    /* If the file was successfully opened for read/write access,
    ** choose a default page size in case we have to create the
    ** database file. The default page size is the maximum of:
    **
    **    + SQLITE_DEFAULT_PAGE_SIZE,
    **    + The value returned by sqlite3OsSectorSize()
    **    + The largest page size that can be written atomically.
    */

SQLITE_DEFAULT_PAGE_SIZE is passed by compiler flag. But it is 4096 if not specified.

• load size with sqlite3SectorSize().
  • unixSectorSize just returns SQLITE_DEFAULT_SECTOR_SIZE (=4096 in most case)

after setting sector size, check szPageDflt is not smaller than sectorSize.

validation

sqlite3BtreeOpen()

pBt->pageSize<512 || pBt->pageSize>SQLITE_MAX_PAGE_SIZE
         || ((pBt->pageSize-1)&pBt->pageSize)!=0

Header of header

btree.c

static const char zMagicHeader[] = SQLITE_FILE_HEADER;

• validation
  • by lockBtree()
    • get read lock (sqlite3PagerSharedLock())
    • get first page (page number = 1) and validate
      • btreeGetPage(pBt, 1, &pPage1, 0)
    • by sqlite3BtreeBeginTrans()
      • while( pBt->pPage1==0 && SQLITE_OK==(rc = lockBtree(pBt)) ); before newDatabase()
      • pBt->pPage1 is a pointer.
• write
  • by newDatabase()
  • sqlite3BtreeNewDb
    • sqlite3_backup_step() (backup.c) if( nSrcPage==0 )
  • sqlite3BtreeBeginTrans
  • sqlite3BtreeSavepoint()

[kawasin73]

C API

https://www.sqlite.org/cintro.html

• sqlite3 → The database connection object. Created by sqlite3_open() and destroyed by sqlite3_close().
• sqlite3_stmt → The prepared statement object. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().
• sqlite3_open() → Open a connection to a new or existing SQLite database. The constructor for sqlite3.
• sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the database. The constructor for sqlite3_stmt.
• sqlite3_bind() → Store application data into parameters of the original SQL.
• sqlite3_step() → Advance an sqlite3_stmt to the next result row or to completion.
• sqlite3_column() → Column values in the current result row for an sqlite3_stmt.
• sqlite3_finalize() → Destructor for sqlite3_stmt.
• sqlite3_close() → Destructor for sqlite3.
• sqlite3_exec() → A wrapper function that does sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and sqlite3_finalize() for a string of one or more SQL statements.

[kawasin73]

BtCursor

https://github.com/sqlite/sqlite/blob/master/src/btree.h

• sqlite3BtreeCursor()
  • initialize BtCursor

int sqlite3BtreeTableMoveto(
  BtCursor*,
  i64 intKey,
  int bias,
  int *pRes
);

int sqlite3BtreeIndexMoveto(
  BtCursor*,
  UnpackedRecord *pUnKey,
  int *pRes
);

int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
                       int flags, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int flags);
int sqlite3BtreeEof(BtCursor*);
int sqlite3BtreePrevious(BtCursor*, int flags);
i64 sqlite3BtreeIntegerKey(BtCursor*);
void sqlite3BtreeCursorPin(BtCursor*);
void sqlite3BtreeCursorUnpin(BtCursor*);

int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);
const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt);
u32 sqlite3BtreePayloadSize(BtCursor*);
sqlite3_int64 sqlite3BtreeMaxRecordSize(BtCursor*);

• BtCursor is the interface to read entries from btree
• sqlite3BtreeCursor() to initialize/setup BtCursor.
  • Called from sqlite3VdbeExec(): OP_OpenRead, OP_OpenWrite, OP_OpenAutoindex, OP_OpenEphemeral
• sqlite3BtreeTableMoveto() to move the cursor to specified primary key
• sqlite3BtreeNext() to move the cursor to next entry.
• The parsed cell is cached on sqlite3BtreePayloadSize()

/*
** Create a new cursor for the BTree whose root is on the page
** iTable. If a read-only cursor is requested, it is assumed that
** the caller already has at least a read-only transaction open
** on the database already. If a write-cursor is requested, then
** the caller is assumed to have an open write transaction.
**
** If the BTREE_WRCSR bit of wrFlag is clear, then the cursor can only
** be used for reading.  If the BTREE_WRCSR bit is set, then the cursor
** can be used for reading or for writing if other conditions for writing
** are also met.  These are the conditions that must be met in order
** for writing to be allowed:
**
** 1:  The cursor must have been opened with wrFlag containing BTREE_WRCSR
**
** 2:  Other database connections that share the same pager cache
**     but which are not in the READ_UNCOMMITTED state may not have
**     cursors open with wrFlag==0 on the same table.  Otherwise
**     the changes made by this write cursor would be visible to
**     the read cursors in the other database connection.
**
** 3:  The database must be writable (not on read-only media)
**
** 4:  There must be an active transaction.
**
** The BTREE_FORDELETE bit of wrFlag may optionally be set if BTREE_WRCSR
** is set.  If FORDELETE is set, that is a hint to the implementation that
** this cursor will only be used to seek to and delete entries of an index
** as part of a larger DELETE statement.  The FORDELETE hint is not used by
** this implementation.  But in a hypothetical alternative storage engine 
** in which index entries are automatically deleted when corresponding table
** rows are deleted, the FORDELETE flag is a hint that all SEEK and DELETE
** operations on this cursor can be no-ops and all READ operations can 
** return a null row (2-bytes: 0x01 0x00).
**
** No checking is done to make sure that page iTable really is the
** root page of a b-tree.  If it is not, then the cursor acquired
** will not work correctly.
**
** It is assumed that the sqlite3BtreeCursorZero() has been called
** on pCur to initialize the memory space prior to invoking this routine.
*/
static int btreeCursor(
  Btree *p,                              /* The btree */
  Pgno iTable,                           /* Root page of table to open */
  int wrFlag,                            /* 1 to write. 0 read-only */
  struct KeyInfo *pKeyInfo,              /* First arg to comparison function */
  BtCursor *pCur                         /* Space for new cursor */
){

• 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 */
};

[kawasin73]

Virtual DataBase Engine (VDBE)

https://www.sqlite.org/opcode.html

Execution continues until a Halt instruction is seen, or until the program counter becomes greater than the address of last instruction, or until there is an error.

The ResultRow opcode causes the bytecode engine to pause and the corresponding sqlite3_step() call to return SQLITE_ROW.

VdbeOp