SQLite crashes when changing the case of db_table

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

[Tim Graham]

The database is SQLite.

[Simon Charette]

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

[Shai Berger]

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

[Simon Charette]

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.

[Simon Charette]

​PR

[Simon Charette <charette.s@…>]

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.

[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