Record the Schema Version

Two databases can contain different generations of the same application tables. Looking only at a filename such as catalog.db cannot tell your setup code which generation it opened.

SQLite provides a small integer for this job: PRAGMA user_version. It lives inside the database file, begins at 0, and is available for an application to manage. SQLite does not choose its meaning. In this project, 2 means the current three-table shape with members.email as the final column.

Open catalog_setup.py. Make two focused changes.

First, edit your saved create_current_schema(connection). After all three CREATE TABLE calls succeed, but before the existing commit, execute this static statement:

connection.execute("PRAGMA user_version = 2")

Keeping the version write inside the same transaction matters. If a table creation fails, rollback should leave both the schema and its marker unchanged.

Second, complete the new schema_version(connection) function. Execute the static read statement PRAGMA user_version, fetch its one row, and return the integer in that row. Do not return the Python literal 2. The function must report whichever database it receives.

Press Run. The visible run_setup.py now makes these calls:

create_current_schema(current_connection)
schema_version(current_connection)
schema_version(version_seven_connection)

The second connection is deliberately marked 7, so the important output is:

== record the version ==
Call: schema_version(current_connection)
Current schema version: 2
Call: schema_version(version_seven_connection)
Other stored version: 7

You will also see the earlier rebuild block report Schema version: 2. The runner calls the same saved initializer again and prints what the database actually contains. It is not preserving an old result from an earlier run.

Submit reopens a newly initialized file and also supplies connections marked 1, 2, and 7. It then forces a late schema failure. That failed call must still leave its version at 0, which proves the marker describes a completed schema rather than an attempted one.

The marker travels with the database file.