[Django] #35762: SQLite deprecating double-quotes for string literals
12 views
Skip to first unread message
Django
Sep 15, 2024, 7:35:39 AM9/15/24
to django-...@googlegroups.com
#35762: SQLite deprecating double-quotes for string literals
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: SQLite "string | Triage Stage:
literals" | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35762>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: SQLite "string | Triage Stage:
literals" | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35762>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Sep 15, 2024, 7:50:15 PM9/15/24
to django-...@googlegroups.com
#35762: SQLite deprecating double-quotes for string literals
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: (none)
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLite "string | Triage Stage: Accepted
literals" |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mohamed Nabil Rady):
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Makes sense to use single quotes if they are disabled by default in later
versions of sqlite
--
Ticket URL: <https://code.djangoproject.com/ticket/35762#comment:1>
Django
Sep 16, 2024, 2:56:40 AM9/16/24
to django-...@googlegroups.com
#35762: SQLite deprecating double-quotes for string literals
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: Claude
-------------------------------------+-------------------------------------
| Paroz
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLite "string | Triage Stage: Accepted
literals" |
Has patch: 1 | Needs documentation: 0
Severity: Normal | Resolution:
Keywords: SQLite "string | Triage Stage: Accepted
literals" |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Claude Paroz):
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* owner: (none) => Claude Paroz
* status: new => assigned
* version: 5.1 => dev
Comment:
[https://github.com/django/django/pull/18586 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/35762#comment:2>
Django
Sep 16, 2024, 5:13:22 AM9/16/24
to django-...@googlegroups.com
#35762: SQLite deprecating double-quotes for string literals
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: Claude
| Paroz
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLite "string | Triage Stage: Ready for
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: Claude
| Paroz
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
literals" | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/35762#comment:3>
Django
Sep 17, 2024, 2:56:52 AM9/17/24
to django-...@googlegroups.com
#35762: SQLite deprecating double-quotes for string literals
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: Claude
| Paroz
Type: Bug | Status: closed
-------------------------------------+-------------------------------------
Reporter: Geoff Fellows | Owner: Claude
| Paroz
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
(models, ORM) |
Keywords: SQLite "string | Triage Stage: Ready for
literals" | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by GitHub <noreply@…>):
literals" | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* resolution: => fixed
* status: assigned => closed
Comment:
In [changeset:"8b9a2bf34e132ccf0ab0a074440dc55f90c76598" 8b9a2bf3]:
{{{#!CommitTicketReference repository=""
revision="8b9a2bf34e132ccf0ab0a074440dc55f90c76598"
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...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35762#comment:4>
Reply all
Reply to author
Forward
0 new messages