Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#26781 closed Bug (fixed)

SQLite crashes when changing the case of db_table

Reported by: lao zzzi Owned by: Simon Charette
Component: Migrations Version: dev
Severity: Normal Keywords: migrate sqlite case
Cc: Shai Berger Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by lao zzzi)

Problem appears after renaming db_table, if you try to rename, for example "Article" to "article" or "Blog" to "blog" or to "bLoG", etc. and migrate this changes to database

Example:

Step 1. Create model:

class Article (models.Model):
    class Meta():
        db_table="Article"
    title = models.CharField(max_length=200)
    body = models.TextField()
    date_cr = models.DateTimeField()

Step 2. make migrations & migrate

Step 3. change the value of db_table from "Article" to "article"

class Article (models.Model):
    class Meta():
        db_table="article"
    title = models.CharField(max_length=200)
    body = models.TextField()
    date_cr = models.DateTimeField()

Step 4. make migrations

Step 5. migrate -- here you'll see errors
.....
django.db.utils.OperationalError: there is already another table or index with this name: article

This error appears any time later, if you try to do something with model and use migrate. Even if you delete the Article model and start "make migrations & migrate" you'll see such error.

Change History (11)

comment:1 Changed 5 years ago by lao zzzi

Description: modified (diff)

comment:2 Changed 5 years ago by lao zzzi

Description: modified (diff)

comment:3 Changed 5 years ago by Tim Graham

Summary: Bug with migrate appears if you try to change the db_table valueSQLite crashes when changing the case of db_table
Triage Stage: UnreviewedAccepted

The database is SQLite.

comment:4 Changed 5 years ago by Simon Charette

Version: 1.9master

The underlying issue here is that SQLite always deal with identifiers in a case-insensitive way, even if quoted.

e.g.

CREATE TABLE Foo ( id integer primary key);
CREATE TABLE foo ( id integer primary key);

Will crash on all SQL compliant backends as the Foo will be lowercased to foo since it's not quoted.

However the following will only crash on SQLite

CREATE TABLE "Foo" ( id integer primary key);
CREATE TABLE "foo" ( id integer primary key);

I guess we should add a feature flag for this and make alter_db_table a noop if ignores_quoted_identifier_case and old_table_name.lower() == new_table_name.lower().

comment:5 Changed 5 years ago by Simon Charette

Keywords: sqlite added

comment:6 Changed 5 years ago by Shai Berger

Cc: Shai Berger added

Plenty of case issues in the Oracle backend as well; perhaps we should do something more general. See for example #20226, #20487

comment:7 Changed 5 years ago by Simon Charette

Owner: changed from nobody to Simon Charette
Status: newassigned

While there's clearly multiple issues in the Oracle backend it behaves correctly in regard to identifier quoting.

The root of all its problems is the fact identifiers are both uppercased and quoted making it impossible to reference non upper cased identifiers.

In the case of SQLite it digresses from SQL92 by treating delimited identifier in a case-insensitive way. This is not an issue with column name alteration as the table is rebuilt anyway.

comment:8 Changed 5 years ago by Simon Charette

Has patch: set
Keywords: case added

comment:9 Changed 5 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:10 Changed 5 years ago by Simon Charette <charette.s@…>

Resolution: fixed
Status: assignedclosed

In c2e62fd:

Fixed #26781 -- Made table name case change a noop on SQLite.

SQLite disgresses from the SQL standard by ignoring case of quoted identifiers.

Thanks to laozzzi for the report and Tim for the review.

comment:11 Changed 5 years ago by Simon Charette <charette.s@…>

In 23ac35a:

[1.10.x] Fixed #26781 -- Made table name case change a noop on SQLite.

SQLite disgresses from the SQL standard by ignoring case of quoted identifiers.

Thanks to laozzzi for the report and Tim for the review.

Backport of c2e62fd1aed093c4d9ff84e3d86e6a85c8aa1917 from master

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