SQLite
SQLite is a library, not a server: the whole database is one file opened inside your process. Interviews probe its typing, file format, single-writer model and limits.
on this pageshowhide
explore
- Embedded Architecture5 questions
- Type Affinity & Dialect6 questions
- File Format & Journals6 questions
- Locking & Write Concurrency6 questions
- Indexes & Query Planner6 questions
- Limits, Extensions & Corruption6 questions
questions
page 2 of 2Should a mobile app rely on the platform's bundled SQLite or ship its own SQLite build, and how would you decide?
basics
~20 sUse the bundled SQLite when the oldest supported device's version and build options cover your features; ship your own when you need newer features or uniform behaviour, accepting the app size, the patching duty and the one-copy-per-process rule.
In SQLite, when does declaring a table WITHOUT ROWID speed up lookups, and what do you give up by doing it?
basics
~20 sWITHOUT ROWID stores a table as one B-tree keyed by its primary key, so a lookup on a non-integer or composite key takes one search instead of two. It suits small rows and costs AUTOINCREMENT, rowid functions and the update hook.
What is inside a SQLite database file: what does its 100-byte header record, and how are tables and indexes stored in its pages?
basics
~20 sThe file is a run of equal-size pages, 4096 bytes by default since 3.12.0. Page 1 opens with a 100-byte header (magic string, page size, counters, freelist head) and roots sqlite_schema, which records the root page of every table and index B-tree.
What is SQLite's VFS layer, and when would you choose or write a VFS other than the platform default?
basics
~20 sThe VFS is SQLite's operating-system interface: every file open, read, write, sync and lock goes through it. The defaults are unix and win32; alternatives change locking, add tracing or quotas, or put pages somewhere that is not an ordinary file.
In SQLite, a join of contacts c and call_log l shows `SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)`; what does that mean, and what should you change?
basics
~20 sSQLite found no index on call_log.phone, so for this one statement it builds a transient index, uses it for the join, and discards it. The plan works but pays the build cost on every run; a persistent index on call_log(phone) is the fix.
showing 31–35 of 35