Row Storage: The Hidden rowid

After this lesson rowid works like a column you never declared: SELECT rowid, name FROM users shows each row's number, and WHERE rowid = 2 finds one row by it. The number itself has been assigned since lesson 3; what you add is the ability to ask for it.

Why rows need an identity

Rows in a table are identified so far only by their position in a list, and positions move when rows are deleted. Real engines give every row a stable number the moment it is stored. SQLite calls it rowid, and a SQLite table is literally a B-tree keyed by it: finding row 2 means walking that tree to key 2. You will build that tree two chapters from now. Here, as the first step, the number just has to exist and be queryable.

The counter

Each table owns a counter that starts at 1:

CREATE TABLE users (name TEXT, age INTEGER)
INSERT ... ('Alice', 30)     takes rowid 1, counter becomes 2
INSERT ... ('Bob', 25)       takes rowid 2, counter becomes 3

Three rules make the number trustworthy:

• It only goes up. A rowid is never handed out twice. When DELETE arrives (lesson 8) the counter will not move back, so "row 3" can never silently start meaning different data.
• A failed insert uses no number. The counter moves only when a row is really stored. After INSERT INTO t VALUES ('a') is rejected for a type error, the next good row still gets the next number.
• Each table counts separately. Two tables can both have a row 1.

A column that is not in the schema

rowid is not in t.cols, so name resolution has to know about it. The convention used here: column lookup returns the column's position, and -1 stands for the hidden rowid. The row accessor already turns -1 into the stored number, so everything that evaluates a column (the select list, comparisons in WHERE) works without further changes:

Two details decide the hard cases:

• SELECT * expands to the declared columns only. To see the number, ask for it: SELECT rowid, *.
• If the user declares a column named rowid, that column wins. The hidden number is only a fallback for a name that no real column has. Check the schema first.

An unknown name that is not rowid is still ERR unknown column: <name>.

Common mistakes

• Showing rowid in SELECT *. The star means declared columns.
• Putting the rowid check before the schema check. A declared rowid column would be hidden by it.
• Renumbering. The counter is not len(rows) + 1: that goes wrong as soon as a row is removed. Keep a counter and only ever add to it.
• Spending a number on a rejected row.

Your exercise

Everything else is in the starter, including the counter. Make col_index understand rowid. Your change is small, so read the failing tests: they probe the declared-column case and the rejected insert, which are the two places a quick version goes wrong.