SQLite deprecating double-quotes for string literals

[Geoff Fellows]

In
​https://github.com/django/django/blob/1f3f0cd8cabd201063ac024cb72eea27ea8c4aa8/django/db/backends/sqlite3/introspection.py#L319

    table_schema = cursor.execute(
        "SELECT sql FROM sqlite_master WHERE type='table' and name=%s"
            % (self.connection.ops.quote_name(table_name),)
        ).fetchone()[0]

quote_name ​https://github.com/django/django/blob/1f3f0cd8cabd201063ac024cb72eea27ea8c4aa8/django/db/backends/sqlite3/operations.py#L189 returns double quotation marks around the table_name which in this case should be a string literal and surrounded by single quotation marks.

The version of SQLite I'm using throws an error. I was using the package DjangoCMS:

    python -m manage version
    5.1.1
    python -m manage migrate
    ...
    File "/usr/home/geoff/Projects/django/.venv/lib/python3.11/site-packages/django/db/backends/sqlite3/base.py", line 352, in execute
    return super().execute(query)
    ^^^^^^^^^^^^^^^^^^^^^^
    django.db.utils.OperationalError: no such column: "cms_page" - should this be a string literal in single-quotes?

Trying the SQL statement using CLI SQLite:

    SQLite version 3.46.0 2024-05-23 13:25:27
...
    sqlite> SELECT sql FROM sqlite_master WHERE type='table' and name="cms_page";
    Parse error: no such column: "cms_page" - should this be a string literal in single-quotes?

Replacing with single quotation marks around 'cms_page' yields:

sqlite> SELECT sql FROM sqlite_master WHERE type='table' and name='cms_page';
CREATE TABLE "cms_page" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "created_by" varchar(70) NOT NULL, "changed_by" varchar(70) NOT     NULL, "creation_date" datetime NOT NULL, "changed_date" datetime NOT NULL, "publication_date" datetime NULL, "publication_end_date" datetime NULL,     "in_navigation" bool NOT NULL, "soft_root" bool NOT NULL, "reverse_id" varchar(40) NULL, "navigation_extenders" varchar(80) NULL, "template"     varchar(100) NOT NULL, "login_required" bool NOT NULL, "limit_visibility_in_menu" smallint NULL, "is_home" bool NOT NULL, "application_urls"     varchar(200) NULL, "application_namespace" varchar(200) NULL, "publisher_is_draft" bool NOT NULL, "languages" varchar(255) NULL, "revision_id"     integer unsigned NOT NULL CHECK ("revision_id" >= 0), "xframe_options" integer NOT NULL, "site_id" integer NOT NULL REFERENCES "django_site" ("id")     DEFERRABLE INITIALLY DEFERRED, "depth" integer unsigned NOT NULL CHECK ("depth" >= 0), "numchild" integer unsigned NOT NULL CHECK ("numchild"     >= 0), "path" varchar(255) NOT NULL UNIQUE, "parent_id" integer NULL REFERENCES "cms_page" ("id") DEFERRABLE INITIALLY DEFERRED,     "publisher_public_id" integer NULL UNIQUE REFERENCES "cms_page" ("id") DEFERRABLE INITIALLY DEFERRED)

See ​https://sqlite.org/quirks.html#dblquote
The SQLite version I'm using must have been compiled with this restriction whereas the version used by the DjangoCMS developer accepted the legacy usage.

[Mohamed Nabil Rady]

Makes sense to use single quotes if they are disabled by default in later versions of sqlite

[Claude Paroz]

​PR

[GitHub <noreply@…>]

In 8b9a2bf3:

Fixed #35762 -- Avoided unneeded quote_name() calls in SQLite introspection.

Double-quoting string literals is deprecated in recent SQLite versions.

Co-authored-by: Mariusz Felisiak <felisiak.mariusz@…>