Skip to content

bug: the catalog's own rules are not enforced — reserved names accepted, DDL text unnormalized, DROP VIEW missing #726

Description

@dpsiderius

Three gaps in the same CREATE/DROP dispatch and sqlite_master-writing
area that PR #717 just worked in. One PR closes all three.

3a — CREATE TABLE sqlite_master(...) succeeds and leaves a file stock
SQLite cannot open.
SQLite reserves the whole sqlite_ prefix.

$ sqlite-rs exec m.db "CREATE TABLE t(a)"
$ sqlite-rs exec m.db "CREATE TABLE sqlite_master(a)"      # rc=0, no error
$ sqlite3 m.db "PRAGMA integrity_check;"
malformed database schema (sqlite_master) - table sqlite_master already exists (11)

oracle, same statement: "object name reserved for internal use: sqlite_master"
name oracle ours
sqlite_master rejected, reserved accepted → file malformed
sqlite_schema rejected, reserved accepted → file malformed
sqlite_sequence rejected, reserved accepted, integrity_check still ok

Pre-existing; #707 (PR #719) does not cause it (CREATE TABLE never went
through resolve_from_table_schema) but raises the stakes now that
sqlite_master is readable.

3b — catalog writes are refused for the wrong reason. Oracle says
table sqlite_master may not be modified for INSERT/UPDATE/DELETE and
... may not be dropped for DROP; we say no such table: sqlite_master. Right
decision, wrong explanation.

3c — stored DDL text keeps IF NOT EXISTS where SQLite strips it.

$ sqlite-rs exec m.db "CREATE TABLE IF NOT EXISTS t (a TEXT)"
$ sqlite3 m.db "SELECT sql FROM sqlite_master WHERE name='t';"
CREATE TABLE IF NOT EXISTS t (a TEXT)      # ours
CREATE TABLE t (a TEXT)                    # oracle

The stored sql text is part of the on-disk format, so this is a
byte-compatibility divergence, not cosmetics: anything reading schema text — a
migration tool, a diff against a reference database, .schema — sees a
different string.

3d — DROP VIEW is unimplemented. Any form falls through to the
unrecognised-statement path, so a view can be created and never removed.

Scope: reject CREATE TABLE/INDEX/VIEW whose name starts with
sqlite_ (case-insensitive) with the oracle's message, including the guarded
IF NOT EXISTS form; give catalog-write and catalog-drop refusals the oracle's
wording; normalise the persisted sql text as SQLite does (check what else it
normalises — whitespace, TEMP/TEMPORARY — and oracle-diff the stored text
across a spread of DDL shapes); implement DROP VIEW [IF EXISTS] (remove the
sqlite_master row, no b-tree to free, schema cookie bumps exactly once,
DROP VIEW naming a table errors like the oracle).

Complexity

Estimate: medium
Reasoning: Each part is small and they share one code area and one
test file; the care is in the test matrix, not the logic.

Refs: #697, #707, #717, #719

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions