Opened 14 months ago

Closed 13 months ago

Last modified 13 months ago

#23030 closed Bug (fixed)

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

Reported by: kunitoki Owned by: claudep
Component: GIS Version: 1.7-rc-1
Severity: Release blocker Keywords: gis spatialite sqlite geometry
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (2)

spatialite_migrations_fix.patch (1.2 KB) - added by kunitoki 14 months ago.
This works with migrations
spatialite_migrations_regression.patch (2.7 KB) - added by kunitoki 14 months ago.
It's possible to showcase the error with this test

Download all attachments as: .zip

Change History (13)

comment:1 Changed 14 months ago by kunitoki

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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)

comment:2 Changed 14 months ago by kunitoki

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...

comment:3 Changed 14 months ago by kunitoki

  • Summary changed from Migration code not working with GeometryField and more than 2 foreign keys to Geo model table rename (in migrations) with spatialite UNIQUE constraint failed

comment:4 Changed 14 months ago by kunitoki

  • Has patch set

comment:5 Changed 14 months ago by timo

  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted

Could you add a regression test?

comment:6 Changed 14 months ago by kunitoki

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.

Changed 14 months ago by kunitoki

This works with migrations

comment:7 Changed 14 months ago by kunitoki

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

Changed 14 months ago by kunitoki

It's possible to showcase the error with this test

comment:8 Changed 14 months ago by claudep

  • Owner changed from nobody to claudep
  • Status changed from new to assigned

comment:10 Changed 13 months ago by Claude Paroz <claude@…>

  • Resolution set to fixed
  • Status changed from assigned to closed

In 8c30df15f17c180fbfb3e378c5469c63cde6599b:

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

Thanks kunitoki for the report and initial patches.

comment:11 Changed 13 months ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top