[Django] #24015: Django doesn't quote index names in SQL generated by migrations.
Django
----------------------------+--------------------------------------
Reporter: maraneta | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Severity: Normal | Keywords: migrations, testing, sql
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
----------------------------+--------------------------------------
== Django does NOT quote index names in SQL generated by migrations.==
Using a table name with a space in it (by specifying it with 'db_table' in
a model's Meta options) will result in an error when trying to migrate.
This error only occurs if the model has a foreign key field, because only
then will the resulting SQL contain a 'create index' query for that model.
This is very easy to replicate; I'll slightly change the django tutorial
models as an example. I've created a new project, with an app called
'Polls'.
models.py:
{{{
from django.db import models
class Question(models.Model):
question_text = models.CharField(max_length=200)
pub_date = models.DateTimeField('date published')
class Choice(models.Model):
question = models.ForeignKey(Question)
choice_text = models.CharField(max_length=200)
votes = models.IntegerField(default=0)
class Meta:
db_table = u'Choice Table'
}}}
Once you have these models and a clean database:
1. Run {{{ python manage.py migrate }}} to generate the initial tables if
you haven't already.
2. Run {{{ python manage.py makemigrations polls }}} to create the initial
migration file for the polls app.
Note: If you run {{{ python manage.py sqlmigrate polls 0001 }}} right now,
you can see the following SQL that is supposed to be executed with this
migration:
{{{
BEGIN;
CREATE TABLE "Choice Table" ("id" serial NOT NULL PRIMARY KEY,
"choice_text" varchar(200) NOT NULL, "votes" integer NOT NULL);
CREATE TABLE "polls_question" ("id" serial NOT NULL PRIMARY KEY,
"question_text" varchar(200) NOT NULL, "pub_date" timestamp with time zone
NOT NULL);
ALTER TABLE "Choice Table" ADD COLUMN "question_id" integer NOT NULL;
ALTER TABLE "Choice Table" ALTER COLUMN "question_id" DROP DEFAULT;
CREATE INDEX Choice Table_7aa0f6ee ON "Choice Table" ("question_id");
ALTER TABLE "Choice Table" ADD CONSTRAINT "Choice
Table_question_id_3209f4dfb997962d_fk_polls_question_id" FOREIGN KEY
("question_id") REFERENCES "polls_question" ("id") DEFERRABLE INITIALLY
DEFERRED;
COMMIT;
}}}
3. If you did step 1 before you created the models, you can just run {{{
python manage.py migrate polls }}} to get the error. If you did step 1
after you created the models, you won't get an error in migrating because
the 'Choice' and 'Question' relations will already exist and the migration
will be 'faked'. However, you can get the error either way by running {{{
python manage.py test polls }}}, as this will run these migrations on a
clean temporary test database.
This is the output when trying to run tests:
{{{
...
django.db.utils.ProgrammingError: syntax error at or near "Table_7aa0f6ee"
LINE 1: CREATE INDEX Choice Table_7aa0f6ee ON "Choice Table" ("quest...
}}}
Looking at this error and the SQL code output by {{{ sqlmigrate }}}, this
error is a result of django not quoting "Choice Table_7aa0f6ee" when it's
creating an index.
Keep in mind that this error will only occur if the index (from the
foreign key field) is created '''at the same time or after''' the table is
named with a space in it.
If you exclude the meta options above and migrate, then add it in a later
migration, there will be no error. However, it is difficult to use this
workaround when I already have an existing database.
Quoting these SQL index names will allow users to use spaces in their
db_table names without error.
--
Ticket URL: <https://code.djangoproject.com/ticket/24015>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Keywords: migrations, | Triage Stage: Accepted
testing, sql | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by timgraham):
* needs_better_patch: => 0
* stage: Unreviewed => Accepted
* severity: Normal => Release blocker
* needs_tests: => 0
* needs_docs: => 0
Comment:
Tracked it down to a missing `quote_name()`
[https://github.com/django/django/blob/47912d9f2b8f55044aef5b8ed9b006e8dd8ce976/django/db/backends/schema.py#L394
here]. It looks like this was fixed in master by
6072f17d09cbe8b7cecfbfa1a253d0a4f2152170. A good fix is probably to
backport that commit to 1.7.x and add a test along with it. The committer
can then forwardport the test.
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:1>
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Keywords: migrations, | Triage Stage: Accepted
testing, sql | Needs documentation: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
https://github.com/django/django/pull/3756
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:2>
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Keywords: migrations, | Triage Stage: Ready for
testing, sql | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
Changes (by timgraham):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:3>
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.7
Severity: Release blocker | Resolution: fixed
Keywords: migrations, | Triage Stage: Ready for
testing, sql | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 1 | UI/UX: 0
Changes (by Claude Paroz <claude@…>):
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"f46a16614d0a8339362ed7a48eef8f1914f96c85"]:
{{{
#!CommitTicketReference repository=""
revision="f46a16614d0a8339362ed7a48eef8f1914f96c85"
[1.7.x] Fixed #24015 -- Factorized create_index_sql expression
Backport of 6072f17d0 from master, with one test reinforced.
Thanks Tim Graham for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:4>
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.7
Severity: Release blocker | Resolution: fixed
Keywords: migrations, | Triage Stage: Ready for
testing, sql | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 1 | UI/UX: 0
Comment (by Claude Paroz <claude@…>):
In [changeset:"5b1fb0a75d6961d78ba68c72008f1cc535f9689a"]:
{{{
#!CommitTicketReference repository=""
revision="5b1fb0a75d6961d78ba68c72008f1cc535f9689a"
Forward-ported test and release note from f46a16614
Refs #24015.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:5>
Django
Reporter: maraneta | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.7
Severity: Release blocker | Resolution: fixed
Keywords: migrations, | Triage Stage: Ready for
testing, sql | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 1 | UI/UX: 0
Comment (by claudep):
@maraneta: Sorry, I just realize now that I didn't credited you in the
commit message :-( Thanks anyway!
--
Ticket URL: <https://code.djangoproject.com/ticket/24015#comment:6>