jazzband / jazzband/django-model-utils

SQL identifier quoting

Open
#449 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.