SQLite schema editor clears deferred SQL on operation requiring table rebuilds

[Yevhen Kozlov]

0. create a models.py, makemigrations for them

from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=50)

1. add manyToMany field only, makemigrations:

from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=50)
	tests = models.ManyToManyField(Test)

Check with sqlmigrate there is table ..._test2_test with UNIQUE INDEX:

CREATE TABLE "m2m_test2_tests" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "test2_id" integer NOT NULL REFERENCES "m2m_test2" ("id"), "test_id" integer NOT NULL REFERENCES "m2m_test" ("id"));
CREATE UNIQUE INDEX "m2m_test2_tests_test2_id_test_id_c4708311_uniq" ON "m2m_test2_tests" ("test2_id", "test_id");
CREATE INDEX "m2m_test2_tests_test2_id_de2c1678" ON "m2m_test2_tests" ("test2_id");
CREATE INDEX "m2m_test2_tests_test_id_9723a1f8" ON "m2m_test2_tests" ("test_id");

2. drop migration created at step 1 and change title field additionally(say, set max_length=100)

from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=100)
	tests = models.ManyToManyField(Test)

Then run makemigrations and check its output:

BEGIN;
--
-- Add field tests to test2
--
CREATE TABLE "m2m_test2_tests" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "test2_id" integer NOT NULL REFERENCES "m2m_test2" ("id"), "test_id" integer NOT NULL REFERENCES "m2m_test" ("id"));
--
-- Alter field title on test2
--
ALTER TABLE "m2m_test2" RENAME TO "m2m_test2__old";
CREATE TABLE "m2m_test2" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" varchar(50) NOT NULL);
INSERT INTO "m2m_test2" ("id", "title") SELECT "id", "title" FROM "m2m_test2__old";
DROP TABLE "m2m_test2__old";
COMMIT;

So no UNIQUE constraint is generated for automatically created table if there is any other field changed in the same migration.
At least it happens as for SQLite

[Simon Charette]

In order to facilitate reproduction could you precise what you mean by _drop migration created at step 1_.

Does that mean deleting the migration file and dropping the database? Are you unapplying the migration before deleting it? Can you reproduce from starting from the second model state (step 2.)?

Thanks!

[Yevhen Kozlov]

Replying to Simon Charette:

In order to facilitate reproduction could you precise what you mean by _drop migration created at step 1_.

Does that mean deleting the migration file and dropping the database? Are you unapplying the migration before deleting it? Can you reproduce from starting from the second model state (step 2.)?

Thanks!

Sorry for unclear description.

When I've run into that initially I applied and unapplied migrations.
While reproducing that on smaller code I went in different way: makemigrations to generate migration then sqlmigrate to see SQL code generated; this way I did not applied or unapplied migrations.
So I was literally deleting migration file on step No 2.

Actually it is not even required to have step No 1.
I included that just to demonstrate UNIQUE index is created properly until I want to change some other field in the same migration.

[Yevhen Kozlov]

have just realized that happens for SQLite; it could be database-specific

[Yevhen Kozlov]

I've not noticed that initially but ordinary indexes are not created also. I believe it's because UNIQUE constraint and INDEX should be created last through .deferred_sql list.

[Simon Charette]

I'm afraid you'll have to be more specific here. Could you described the exact set of commands and operations you ran.

I can't reproduce with the following with the following operations.

1. Define the two models.
2. Run makemigrations
3. Alter the second model's title field
4. Run makemigrations
5. Inspect sqlmigrate 0001 output
6. Inspect sqlmigrate 0002 output

Here's the test application I used: ​https://github.com/charettes/django-ticketing/compare/ticket-28846

[Simon Charette]

Actually I managed to reproduce. I somehow missed that the ManyToManyField was added in the second migration.

[Simon Charette]

The issue is already fixed on master and in the upcoming 2.0 release by 3b429c96736b8328c40e5d77282b0d30de563c3c (#25530).

[Yevhen Kozlov]

Thank you @Simon Charette