CREATE TABLE — Defining Schema

With a tokenizer in hand, the next milestone is turning CREATE TABLE statements into an in-memory schema — the metadata that every later command (INSERT, SELECT, UPDATE) will consult to know what columns exist and what types they hold.

What a schema is

A schema is just structured metadata, not data. For:

CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)

you need to record:

• the table name (users)
• an ordered list of (column_name, column_type) pairs, because column order matters for things like INSERT ... VALUES (1, 'Alice', 30) (positional values) and for SELECT * (column display order)

A natural representation is a dictionary mapping table name → list of (name, type) tuples, or a small Table class holding name and columns. Keep a single global (or connection-scoped) catalog — e.g. catalog: dict[str, Table] — that every command handler reads and writes.

Parsing the statement

After tokenizing, CREATE TABLE users (id INTEGER, name TEXT, age INTEGER) becomes a token stream: KEYWORD CREATE, KEYWORD TABLE, IDENTIFIER users, SYMBOL (, IDENTIFIER id, KEYWORD INTEGER, SYMBOL ,, ... The parser for this statement is small and mechanical:

1. Expect CREATE, then TABLE.
2. Consume the table name (an identifier).
3. Expect (.
4. Loop: consume name type, then either a , (more columns follow) or ) (done).
5. Expect the statement terminator.

def parse_create_table(tokens):
    expect(tokens, "CREATE"); expect(tokens, "TABLE")
    name = expect_identifier(tokens)
    expect(tokens, "(")
    columns = []
    while True:
        col_name = expect_identifier(tokens)
        col_type = expect_type(tokens)   # INTEGER or TEXT
        columns.append((col_name, col_type))
        if peek(tokens) == ",":
            advance(tokens); continue
        expect(tokens, ")")
        break
    return name, columns

Validation and error handling

Two error conditions are explicitly part of this lesson's contract:

• Duplicate table: CREATE TABLE for a name already in the catalog must not silently overwrite the old schema — return ERR table already exists: <name> and leave the existing table untouched.
• Unknown table lookups: .schema <table> for a name not in the catalog returns ERR unknown table: <name> rather than crashing or returning an empty result.

This "check before mutating, fail loud on misuse" pattern is one you'll reuse for every other statement type — INSERT/UPDATE/DELETE all need to validate the target table exists before touching any state.

Introspection commands

Real SQL engines (and SQLite in particular) expose dot-commands for inspecting the catalog rather than querying it with SQL. You're implementing two:

• .tables — list every table name, sorted alphabetically, one per line. Sorting matters for deterministic, testable output regardless of creation order.
• .schema <table> — print the column list back out in name TYPE, name TYPE form, matching how it was declared.

Keep the catalog as the single source of truth: once this lesson's structure is right, every later lesson (rows, WHERE, indices) just adds more data attached to the same table entry, rather than needing a new metadata store.

Where the schema lives when the process exits

Your catalog is an in-memory dictionary, so it evaporates on exit. SQLite solves the same problem by keeping the catalog inside the database file: a built-in table named sqlite_master holds one row per table, index and view, and that row's sql column stores the original CREATE TABLE text verbatim. Opening a database file means reading sqlite_master and re-parsing those statements to rebuild exactly the catalog you are building here — which is why the tokenizer from the previous lesson earns its keep twice: once when a user types DDL, and again on every open. The real sqlite3 shell's .schema is little more than SELECT sql FROM sqlite_master, which is the same command you are implementing against your own catalog.