jazzband / jazzband/django-model-utils
SQL identifier quoting
- Dominant language
- Python
- Stars
- 2.8k
- Forks
- 374
- PR merge metrics
- No merged PRs in 30d
Description
Hi,
First of all, thank you very much for this very nice package.
## Problem
I use the `InteritanceManager` and I have an issue migrating from sqlite to mysql/mariadb:
```
django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."cmsmodel_ptr_id" IS NOT NULL))) ORDER BY `cms_cmsmodel`.`prime_position` ASC\' at line 1')
```
This is caused by `instance_of` at https://github.com/jazzband/django-model-utils/blob/master/model_utils/managers.py#L208. The default quoting style for identifiers in mysql/mariadb is backticks (`) rather than the SQL-standard double quotes (").
I can solve the issue on my side by setting
```
DATABASES = {
"default": {
"ENGINE": "django.db.backends.mysql",
...
"OPTIONS": {
"sql_mode": "ANSI_QUOTES",
},
},
}
```
in my `settings.py`, but this is not necessarily a good thing. Rather, you probably should use django's backend-aware `connection.ops.quote_name(...)` function rather than literal double quotes.
Scanning the code for other issues of the same kind, I think that there's a similar problem in `JoinQueryset` at https://github.com/jazzband/django-model-utils/blob/master/model_utils/managers.py#L380: AFAICS, `fk_column` may come from a user-defined `Meta.db_column`, which may e.g. contain whitespace and should therefore be quoted (something django does behind the scenes, as mentioned in the doc: https://docs.djangoproject.com/en/2.2/ref/models/fields/#db-column).
## Environment
- Django Model Utils version: 4.0.0
- Django version: 2.2
- Python version: 3.8
- Other libraries used, if any: (not relevant)
## Code examples
Any use of `instance_of` with a mysql/mariadb backend without `ANSI_QUOTES` set.
Contributor guide
Research direction
Start in model_utils/managers.py at the instance_of code around line 208 and the JoinQueryset code around line 380. Reproduce instance_of with a MySQL or MariaDB backend, including a custom Meta.db_column, and compare the generated SQL. Done means identifier quoting works with the backend defaults without requiring ANSI_QUOTES.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- django, python
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 52/100