INSERT INTO: Validating and Storing Rows

After this lesson your engine accepts INSERT INTO t VALUES (...), checks the row against the table's schema, stores it, and prints stored rows back with .dump. This is the first statement that changes data, so it is also where the schema from last lesson starts to be enforced.

What is stored

A row is the list of its values in column order, together with the table's next row number (a rowid, covered in lesson 5). The values themselves have three shapes:

-5 arrives from the lexer as two tokens, the operator - and the number 5. Reading a literal means accepting that pair as one negative number. The helper parse_literal in the starter already does this.

Parse first, then look at the schema

insert := INSERT INTO name VALUES "(" literal { "," literal } ")" [ ";" ]

Walk the token list with this grammar. VALUES (), VALUES (1,), INSERT t VALUES (1) and VALUES (x) (a bare name is not a literal) are all ERR syntax error. A statement that parses has a table name and a list of values, and only then is the catalog consulted.

Validate in a fixed order

1. Table exists. Otherwise ERR unknown table: <name>. Do not look at the values at all.
2. Count matches. Otherwise ERR expected N values but got M, where N is the number of columns.
3. Types match, column by column from the left. An INTEGER column takes an integer, a TEXT column takes text, and NULL fits any column. The first misfit gives ERR type mismatch for column <name>, using the column's name.

The order is observable. INSERT INTO ghost VALUES ('a', 'b') is an unknown table, not a count problem, and a row with both a wrong count and a wrong type reports the count.

table t (a INTEGER, b TEXT)
('x', 'y', 'z')   ->  expected 2 values but got 3      (count is checked first)
('x', 5)          ->  type mismatch for column a       (first misfit, from the left)
(5, 5)            ->  type mismatch for column b

Never half-insert

Run every check before you touch the table. Appending the row first and validating later leaves a bad row behind after the error, and .dump will show it. A failed INSERT changes nothing.

.dump

.dump <table> prints each stored row in insertion order, values joined by |, with NULL for a missing value. Rows are not deduplicated or sorted: inserting 3, 1, 3 prints 3, 1, 3. An unknown table is ERR unknown table: <name> and a missing argument is ERR usage: .dump <table>.

An empty text value prints as nothing at all, so the row (7, '') dumps as 7|.

Common mistakes

• Checking types before the count. INSERT INTO t VALUES ('x', 'y', 'z') must report the count.
• Using the table name in the type error. The message names the column.
• Storing before validating. A rejected row must not appear in .dump.
• Treating NULL as a value of the wrong type. It fits every column.

Your exercise

The starter has the lexer, CREATE TABLE, the token cursor and parse_literal. You write insert_into and cmd_dump. The one mistake to avoid: mutating the table before the last check has passed.