Persistence & WHERE Clause
Making data survive across sessions and filtering rows with conditional expressions
Implement durable file-backed storage so inserted data persists across program restarts, introduce the --db flag for database file control, and add WHERE clause evaluation for conditional row filtering.
Core Concepts
- Flushing in-memory Buffer Pool pages to a physical database file on disk via db_close()
- Reconstructing page inventory from an existing database file during db_open() using file size arithmetic
- Controlling database file paths via the --db CLI flag for session isolation and testing
- Evaluating WHERE clause expressions against deserialized row fields during cursor traversal
From Volatile Memory to Durable Storage
Up to Stage 8, our database engine has been a purely in-memory affair. Every INSERT, every carefully balanced B+Tree split, every meticulously sorted leaf node — all of it vanishes the instant the user types .exit. In production databases, this would be catastrophic. Users expect their data to survive power failures, program restarts, and system reboots.
In this stage, we bridge the gap between volatile RAM and durable disk storage by implementing a complete persistence layer. The core insight is elegantly simple: since our Pager already manages data in uniform 4KB pages, writing those pages to a file is a straightforward block copy operation.
Rendering diagram...
The db_close() Flush Protocol
When the user exits the REPL, db_close() must guarantee that every modified page in the Buffer Pool is written to the database file before the process terminates. This is the fundamental durability contract:
- Iterate through every page slot in the cache array
- Write each non-null page to its correct file offset:
page_num × PAGE_SIZE - Close the file descriptor and free memory
The write offset calculation is critical: Page 0 starts at byte 0, Page 1 at byte 4096, Page 2 at byte 8192, and so forth. Since every page is exactly 4096 bytes, the file becomes a perfectly aligned sequence of raw page images.
Reconstructing the B+Tree from Disk
When db_open() encounters an existing database file, it must reconstruct the Pager state without scanning individual rows:
Rendering diagram...
The beauty of our page-based architecture is that the entire B+Tree structure — node types, cell arrays, routing keys, sibling pointers — is encoded directly in the raw page bytes. No separate index file or reconstruction pass is needed!
The --db Flag: Database File Control
To support automated testing and database isolation, the engine must accept a --db <path> CLI flag:
./c-db # uses default "droid.db"
./c-db --db /tmp/mytest.db # uses specified path
./c-db --db /tmp/test.db -c "select * from users;"
This flag enables multi-session persistence tests where Session 1 inserts data and Session 2 verifies it survived, each using the same --db path pointing to a temporary file.
WHERE Clause: Conditional Row Filtering
The WHERE clause transforms our SELECT from a brute-force full table dump into a precision surgical query tool. The evaluation logic differs based on the filtered column:
-
Primary Key Filter (
WHERE id = N): Since the id is the B+Tree key, this could theoretically usebtree_find()for O(log n) lookup. In this stage, we implement the simpler approach of filtering during cursor traversal. Stage 10 will optimize this with the Query Planner. -
Non-Key Filter (
WHERE name = 'alice'): There is no index on thenamecolumn, so the engine must perform a sequential scan through all leaf cells, deserializing each row and comparing thenamefield viastrcmp().
Rendering diagram...
Conceptual Execution Algorithms
Page Flush & Durable Close Protocol
When the user exits the REPL or the program terminates, all modified in-memory pages must be written to disk before releasing resources.
- [1]Iterate over every allocated page slot in the Buffer Pool cache array (indices 0 through num_pages - 1).
- [2]For each non-null page pointer, invoke a write system call to serialize the raw 4096-byte memory block to the correct file offset: page_num × PAGE_SIZE.
- [3]After all pages are flushed, close the file descriptor and free all allocated page memory buffers.
- [4]On the next program startup, the database file will contain the complete B+Tree structure ready for reconstruction.
File-Based Page Reconstruction on Open
When opening an existing database file, the Pager must detect how many pages were previously written and prepare the cache accordingly.
- [1]Open the database file using a read-write file descriptor (creating the file if it does not exist).
- [2]Query the file size using fseek/ftell or fstat system calls.
- [3]Calculate num_pages = file_size / PAGE_SIZE. This instantly reconstructs the page inventory without scanning individual records.
- [4]When a page is requested via pager_get_page(N), check the cache first. On a CACHE_MISS, read PAGE_SIZE bytes from offset N × PAGE_SIZE into a freshly allocated buffer.
WHERE Clause Filter Evaluation
During SELECT execution, each row produced by the cursor must be tested against the WHERE predicate before being included in the result set.
- [1]Parse the WHERE clause during SQL parsing (Stage 3) to extract the column name, comparison operator, and target value.
- [2]During cursor traversal, deserialize each row from its leaf cell into a Row struct.
- [3]Compare the specified row field against the WHERE value: for integer columns (id), perform uint32_t equality; for string columns (name, email), use strcmp.
- [4]If the comparison matches, include the row in the output. If not, skip to the next cell via cursor_advance().