#24015: Django doesn't quote index names in SQL generated by migrations.
----------------------------+--------------------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.33d94dfb0b53cb0996d6f5d0da3d896e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
