Geo model table rename (in migrations) with spatialite UNIQUE constraint failed

[Lucio Asnaghi]

I explain my working test:

• create a virtualenv
• install django 1.7c1
• create a project
• create an app and include to INSTALLED_APPS
• change the database to use 'django.contrib.gis.db.backends.spatialite'
• now go to models.py of the created app and type:

  from django.db import models
  from django.contrib.gis.db import models as geomodels
  from django.contrib.contenttypes.models import ContentType
  from django.contrib.contenttypes.fields import GenericForeignKey
  
  class Institute(models.Model):
      pass
  
  class Company(models.Model):
      pass
  
  class Motivation(models.Model):
      pass
  
  class Manumission(geomodels.Model):
      institute = models.ForeignKey(Institute, blank=True, null=True)
      company = models.ForeignKey(Company, blank=True, null=True)
      motivation = models.ForeignKey(Motivation, blank=True, null=True)
      elements = geomodels.GeometryField(null=True, blank=True)
      objects = geomodels.GeoManager()
  

• then go into a shell and enter the virtualenv and issue:

  spatialite db.sqlite3 "SELECT InitSpatialMetaData();"
  ./manage.py makemigrations appname
  ./manage.py syncdb --noinput
  

The result is this:

SpatiaLite version ..: 3.0.1	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualXL'		[direct XLS access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
the SPATIAL_REF_SYS table already contains some row(s)
 InitSpatiaMetaData ()error:"table spatial_ref_sys already exists"
0
Migrations for 'gis':
  0001_initial.py:
    - Create model Company
    - Create model Institute
    - Create model Manumission
    - Create model Motivation
    - Add field motivation to manumission
Operations to perform:
  Apply all migrations: admin, contenttypes, gis, auth, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying gis.0001_initial...Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 337, in execute
    output = self.handle(*args, **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 532, in handle
    return self.handle_noargs(**options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 27, in handle_noargs
    call_command("migrate", **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 115, in call_command
    return klass.execute(*args, **defaults)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 337, in execute
    output = self.handle(*args, **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 160, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/executor.py", line 62, in migrate
    self.apply_migration(migration, fake=fake)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/executor.py", line 96, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/migration.py", line 107, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/operations/fields.py", line 37, in database_forwards
    field,
  File "/home/x/djangofault/lib/python2.7/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 82, in add_field
    super(SpatialiteSchemaEditor, self).add_field(model, field)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 143, in add_field
    self._remake_table(model, create_fields=[field])
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 125, in _remake_table
    self.alter_db_table(model, temp_model._meta.db_table, model._meta.db_table)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 95, in alter_db_table
    "new_table": self.quote_name(new_db_table),
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/schema.py", line 98, in execute
    cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 485, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: geometry_columns.f_table_name, geometry_columns.f_geometry_column

In fact the migration created seems strange (why the 3rd foreign key is issued separately, so a table rename is needed):

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
import django.contrib.gis.db.models.fields


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Company',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Institute',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Manumission',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('elements', django.contrib.gis.db.models.fields.GeometryField(srid=4326, null=True, blank=True)),
                ('company', models.ForeignKey(blank=True, to='gis.Company', null=True)),
                ('institute', models.ForeignKey(blank=True, to='gis.Institute', null=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Motivation',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.AddField(
            model_name='manumission',
            name='motivation',
            field=models.ForeignKey(blank=True, to='gis.Motivation', null=True),
            preserve_default=True,
        ),
    ]

Also, base=(models.Model,) of 'Manumission' model does not respect django.contrib.gis.db.Model.

[Lucio Asnaghi]

Test case should be shortened to:

from django.db import models
from django.contrib.gis.db.models import GeometryField

class Last(models.Model):
    pass

class First(models.Model):
    fk = models.ForeignKey(Last, blank=True, null=True)
    objs = GeometryField(null=True, blank=True)

[Lucio Asnaghi]

It seems that geometry_columns spatialite table is populated with 2 tables

  Applying gis.0001_initial...
SELECT AddGeometryColumn('gis_first', 'objs', 4326, 'GEOMETRY', 2, 0)
SELECT CreateSpatialIndex("gis_first", "objs")
SELECT AddGeometryColumn('gis_first__new', 'objs', 4326, 'GEOMETRY', 2, 0)
SELECT CreateSpatialIndex("gis_first__new", "objs")

UPDATE geometry_columns SET f_table_name = "gis_first" WHERE f_table_name = "gis_first__new"

then the update is obviously failing cause in the geometry_columns table there are already 2 entries...

[Tim Graham]

Could you add a regression test?

[Lucio Asnaghi]

well, actually my patch don't work as expected, there are some edge cases that make it not viable solution.

regarding regression test, i'm not that practical with writing django test actually, but i can see what i can do.

[Lucio Asnaghi]

This works with migrations

[Lucio Asnaghi]

last patch will work correctly with migrations without leaving the database in a inconsistent state.

i don't know if DatabaseSchema.alter_db_table is called also outside of migrations, so i don't know if it will work in any corner case.

will try to write a test for this

[Lucio Asnaghi]

It's possible to showcase the error with this test

[Claude Paroz]

​https://github.com/django/django/pull/2928

[Claude Paroz <claude@…>]

In 8c30df15f17c180fbfb3e378c5469c63cde6599b:

Fixed #23030 -- Properly handled geometry columns metadata during migrations

Thanks kunitoki for the report and initial patches.

[Claude Paroz <claude@…>]

In ddb5674945725ec8f6e6336f73d3d56e331f34ae:

[1.7.x] Fixed #23030 -- Properly handled geometry columns metadata during migrations

Thanks kunitoki for the report and initial patches.
Backport of 8c30df15f1 from master.