CREATE TABLE: The Catalog

After this lesson your engine accepts CREATE TABLE, remembers each table's name and columns, and prints them back with .tables and .schema. That list of tables is the catalog: every later statement (INSERT, SELECT, UPDATE) starts by looking its table up here.

What a schema records

For CREATE TABLE users (id INTEGER, name TEXT, age INTEGER) the engine stores no data yet, only metadata:

The order is data. Keep columns in a list, not in an unordered map.

Parsing is following a grammar

You already have tokens, so there is nothing to split or match with patterns. A statement is a grammar, and the parser walks the token list reading exactly what the grammar allows next:

create_table := CREATE TABLE name "(" column { "," column } ")" [ ";" ]
column       := name type
type         := INTEGER | TEXT

A trace for CREATE TABLE users (id INTEGER, name TEXT):

CREATE TABLE      checked by the router, parsing starts at token 3
users             name
(                 expected
id INTEGER        column 1
,                 more columns follow
name TEXT         column 2
)                 closes the list
(end)             nothing may follow except an optional ;

Any step that finds something else reports ERR syntax error: () (no columns), (a FLOAT) (not a type), (a INTEGER b TEXT) (missing comma), (a INTEGER,) (comma with nothing after it), or trailing junk after ). Because the parser works on tokens, a quoted name such as "my col" or "count" needs no special treatment: the lexer already turned it into one IDENTIFIER.

Check in this order, then change state

1. the statement is well formed (syntax)
2. no table with that name exists: ERR table already exists: <name>
3. no column name appears twice: ERR duplicate column: <col>

Only after all three pass does the table enter the catalog. Registering first and validating afterwards leaves a half-built table behind after the error, and the next CREATE TABLE of the same name is then refused.

Dot-commands

.tables prints every table name on its own line, sorted by bytes. Hash maps and dictionaries often iterate in an unstable order, so sort explicitly. Bytewise order puts Users before users, because upper-case letters have smaller codes. Names are case-sensitive.

.schema <table> prints the columns as name TYPE, name TYPE. An unknown table is ERR unknown table: <name>. Dot-commands do not go through the lexer, so they are routed on their first word; an unknown one is ERR unknown command: <word>.

Where real databases keep this

SQLite stores the catalog inside the database file, in an ordinary table called sqlite_master. Each row holds the original CREATE TABLE text. Opening a file means re-parsing those statements to rebuild exactly the structure you build here, which is why the parser you write now is used again on every start-up.

Common mistakes

• Registering the table before validating. CREATE TABLE t (a INTEGER, a TEXT) must leave .tables empty.
• Unsorted .tables. The output order must not depend on creation order or map layout.
• Ignoring what follows ). CREATE TABLE t (a INTEGER) extra is a syntax error, not a success.
• Treating names as case-insensitive. Keywords are, names are not.

Your exercise

The starter holds the lexer from last lesson, the catalog types, the statement router and everything else. You write three functions: parse and register CREATE TABLE, print .tables, print .schema. The mistake to avoid is changing the catalog before the last check has passed.