Document Oracle behavior regarding quoting of database table names

[skoom]

I ran into an error when using Oracle and creating automatic models (for history tracking) from user created models.

My automatic model I call History<ModelName> but I want the tables to be h_<db_table>.

I access Model._meta.db_table and append a h_ to it. Because this db_table is passed in the Meta of the automatic model, it isn't being truncated when the models is created.

File: django.db.models.options

Line: 105

Action: remove one indentation level

[skoom]

Since the patch is just removing one indentation level I haven't included an attachment.

[Russell Keith-Magee]

I disagree that truncation is the right thing to do. db_table is a directive to tell the database backend the literal table name to use. The truncate_name operation will munge the name of the table under certain conditions. To my mind, this would be surprising behavior. If I manually specify a name, I would expect it to be used literally.

However, I would expect to see a validation error if you ever tried to synchronize a model with a db_table specification that exceeded the limits of the backend. Accepting on the basis that there is a missing validation condition.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Tim Graham]

The validation should be added in django.core.management.validation.get_validation_errors() with a test in tests/invalid_models/invalid_models/models.py. The validation and the test will have to be conditional on connection.ops.max_name_length() -- there's already an example in invalid_models/models.py for connection.features.interprets_empty_strings_as_nulls.

[Shai Berger]

The current behavior of the Oracle backend is to truncate, unless the given name is quoted.

That is, if Meta has

    db_table='supercallifragilisticexpialidocious'

it will be truncated; if it has

    db_table='"supercallifragilisticexpialidocious"'

we assume the programmer knows what they're doing. I don't like it much as an API (it is also related to case issues), but I don't think anything needs to be changed for this bug.

Is there is any related issue on other backends? Has anyone played with over-63-chars-long table names on PostgreSQL? Otherwise, I think this can be closed.

[Tim Graham]

Hi Shai, I looked into this on PostgreSQL. Specifying db_name="string longer ... than 63 characters" results in the table name being silently truncated at 63 characters (does not use django.db.backends.utils.truncate_name). Arguably this should be fixed, perhaps there's already a ticket, although it seems quite uncommon and low priority.

The action item I was proposing for this ticket was what Russ proposed in comment 2: "I would expect to see a validation error if you ever tried to synchronize a model with a db_table specification that exceeded the limits of the backend."

Do you disagree with that? Of course, this ticket seems a bit trickier than I expected given the Oracle specific logic in it's definition of quote_name you mentioned regarding a quoted db_name.

[Shai Berger]

Hi Tim,

Yes, I disagree with the proposed solution -- actually, with the bug itself as it stands -- because it means some perfectly-working apps will suddenly stop working on Oracle; apps developed on other backends are particularly susceptible to this.

We probably need a documentation fix here. Other than that, we need to be consistent, but the other backends' limitations are so seldom met, that it is indeed very low priority.

[Shai Berger]

Suggested documentation fixes in ​https://github.com/django/django/pull/1787

[Shai Berger <shai@…>]

In 6de3726423b8df4fb8ab05caa109a8c6308e6060:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

[Shai Berger <shai@…>]

In cc0dcfc64405419d95e87c8dcc612bd441a8d6de:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

[Shai Berger <shai@…>]

In 27f3f341369c8e18ea4a360a1f7c01531836b68b:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

[Shai Berger <shai@…>]

In 317040a73b77be8f8210801793b2ce6d1a69301e:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

[Shai Berger]

Hmm, cherry-picking this was too easy... I forgot to add branch prefixes to the header line.

6de3726423b8df4fb8ab05caa109a8c6308e6060 is for 1.4.x
cc0dcfc64405419d95e87c8dcc612bd441a8d6de is for 1.5.x
27f3f341369c8e18ea4a360a1f7c01531836b68b is for 1.6.x
317040a73b77be8f8210801793b2ce6d1a69301e is for master