0%

Create, Import, and Evolve a Schema · practice

Distinguish a Blank Database from an Unknown One

A database marked version 0 is not automatically empty. Someone may have created a table without recording a version, and setup code must not overwrite that unknown state.

The deciding evidence is the of application tables. SQLite keeps schema records in sqlite_schema, so this query exposes the names in stable order:

SELECT name
FROM sqlite_schema
WHERE type = 'table'
ORDER BY name;

Open catalog_setup.py and complete user_table_names(connection). Execute that exact query, call fetchall(), and build a list of names. SQLite can create its own tables whose names begin with sqlite_; exclude those in Python with name.startswith("sqlite_"). Do not add LIKE to the SQL.

Return the remaining names in the query’s order. The result has a precise meaning for later setup decisions:

  • [] together with version 0 means the database is blank and safe to initialize.

  • Any user-table name together with version 0 means the database is unknown and must be refused.

That second case is protective. A table named notes may belong to a different program or to a manual experiment. Calling it blank because the marker is 0 would allow the initializer to claim a file it does not understand.

Press Run. run_setup.py calls:

user_table_names(blank_connection)
user_table_names(current_connection)

The new block should show:

== distinguish blank from unknown ==
Call: user_table_names(blank_connection)
User tables: []
Call: user_table_names(current_connection)
User tables: ['items', 'loans', 'members']

The only observes. It does not create, drop, rename, or version anything, and it leaves the connection open.

Submit also gives you a database that has an application table plus SQLite’s own sqlite_sequence table. The application table must remain visible while the internal name stays out of the result. That keeps “blank” tied to the state your program is responsible for.

Because this check is read-only, a caller can inspect an unfamiliar file and decide what to do without changing the evidence first.

Task

Complete user_table_names(connection) in catalog_setup.py.

Execute the displayed sqlite_schema query, fetch every row, and return the names in order. In Python, leave out names that begin with sqlite_. Do not use LIKE, and do not change or close the database.

Press Run and confirm that a blank connection returns [] while the current schema returns ['items', 'loans', 'members'].