Django produce sql with table name different then specified in meta db_table

[Michał Nowotka]

Hello, I'm using django 1.4 with oracle backend

In my models.py I have:

class ProteinTherapeutics(models.Model):
#...
    class Meta:
        db_table = 'mnowotka\".\"protein_therapeutics'
        managed=False

And this:

ProteinTherapeutics.objects.exists()

produces this SQL:

SELECT *
FROM
  (SELECT ROWNUM AS "_RN",
          "_SUB".*
   FROM
     (SELECT (1) AS "A"
      FROM "MNOWOTKA"."PROTEIN_THERAPEFB7C") "_SUB"
   WHERE ROWNUM <= 1)
WHERE "_RN" > 0  [1.99ms]

Can you help me, please?

[Jacob Kaplan-Moss]

Hi Michal -

See https://docs.djangoproject.com/en/dev/ref/databases/#naming-issues
-- Oracle has a limit on the length of table names, so Django has to
truncate some table names.

In the future, can you please direct questions like this to
django-users? Django-developers is for discussion of developing Django
itself, not usage questions.

Thanks and good luck,

Jacob

> --
> You received this message because you are subscribed to the Google Groups
> "Django developers" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-developers/-/kqFVlwNWEzgJ.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-develop...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-developers?hl=en.

[Michał Nowotka]

I would understand truncating but please take a look:

class DefinedDailyDose(models.Model):
#...
    class Meta:
        db_table = u'mnowotka\".\"defined_daily_dose'
        managed=False

where the name is even longer

DefinedDailyDose.objects.exists()

gives:

SELECT *
FROM
  (SELECT ROWNUM AS "_RN",
          "_SUB".*
   FROM
     (SELECT (1) AS "A"

      FROM "MNOWOTKA"."DEFINED_DAILY_DOSE") "_SUB"
   WHERE ROWNUM <= 1)
WHERE "_RN" > 0  [1.45ms]

Which is perfectly correct.

[Michał Nowotka]

Another thing - this name is not truncated - some junk is appended and not after 30 characters but earlier and only for some table names regardless of length.

I suppose this is a bug in django oracle backend and that's why I'm writing it here.

[Jacob Kaplan-Moss]

Please, read the documentation closely; this is indeed intended behavior:

"""
Oracle imposes a name length limit of 30 characters. To accommodate
this, the backend truncates database identifiers to fit, replacing the
final four characters of the truncated name with a repeatable MD5 hash
value.
"""

Jacob

> --
> You received this message because you are subscribed to the Google Groups
> "Django developers" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/django-developers/-/JE4kDjDwGK8J.

[Łukasz Rekucki]

On 31 October 2012 19:31, Michał Nowotka <mmm...@gmail.com> wrote:
> I would understand truncating but please take a look:
>
> class DefinedDailyDose(models.Model):
> #...
> class Meta:
> db_table = u'mnowotka\".\"defined_daily_dose'
> managed=False
>
> where the name is even longer

Don't want to be mean, but:

>>> len(u'mnowotka\".\"defined_daily_dose')
29
>>> len('mnowotka\".\"protein_therapeutics')
31

So no, that's not longer. It's exactly 2 characters shorter which is
enough not to fall into 30 char limit.

PS. I'm pretty sure you want to have:

db_table='"mnoworka"."defined_daily_dose"'

If the name is already quoted, Django won't alter it. OTOH, see issue
#18514 (which shouldn't be a problem if you have managed=False).

[1]: https://code.djangoproject.com/ticket/18514


--
Łukasz Rekucki

[Shai Berger]

Hi Michal and Django devs,

While for the most part, Jacob is correct in marking this as a usage question,
there does appear to be something buggy here.

Note how Michal is abusing the db_table setting to select a name with a
schema. He's looking for "protein_therapeutics" (<30) in "mnowotka":

> class ProteinTherapeutics(models.Model):
> #...
> class Meta:
> db_table = 'mnowotka\".\"protein_therapeutics'

This, almost worthy of being called an sql injection, can't be the right way
to achieve the goal. In fact, the Oracle backend (or even some higher, more
generic level) should have doubled those '"' characters to make them part of
the name. But -- save length issues -- the ploy succeeds:

> ...

> (SELECT (1) AS "A"
> FROM "MNOWOTKA"."PROTEIN_THERAPEFB7C") "_SUB"

> ...

This is definitely a bug. The correct output should have been

...

(SELECT (1) AS "A"
FROM "MNOWOTKA"".""PROTEIN_THERAPEFB7C") "_SUB"

...

As if this was the 'mnowotka"."protein_therapeutics' table in the current
schema (wouldn't work -- length 31), and not the protein_therapeutics table
(way shorter than 30) in the mnowotka schema, which is the intended target.

I'm not sure -- perhaps the db_tablespace option is interpreted by the Oracle
backend as schema, which would allow what Michal is trying to do with a sane
API. Otherwise, I'd advise Michal to look at Oracle's table aliases, and mark
this also as a missing feature.

But either way, not doubling quotes is a bug.

(Michal: you don't need the '\' characters; look up Python quoting).

Shai.

[Łukasz Rekucki]

On 31 October 2012 20:23, Shai Berger <sh...@platonix.com> wrote:
> This, almost worthy of being called an sql injection, can't be the right way
> to achieve the goal. In fact, the Oracle backend (or even some higher, more
> generic level) should have doubled those '"' characters to make them part of
> the name. But -- save length issues -- the ploy succeeds:

It seems none of the backends implement any form of quote escaping in
their quote_name() methods. But is it actually possible to have a
table in Oracle with a name containing a double quote?

--
Łukasz Rekucki

[Anssi Kääriäinen]

You can't have table names with '"' characters in them unless you
quote the characters yourself. One instance of this is very
intentional: if the table name is already quoted then Django doesn't
add more quotes. This can be useful, and users are relying on that.

I just have a feeling that this is not worth fixing. How common are "
characters in table names? If you are using them in table names (not
in the schema qualified name but in the table name itself), then you
are just looking for trouble...

Surprising, but name quoting (caused mostly by column name quoting) is
clearly visible when profiling the ORM for certain type of queries.
Lets not make it any slower for this...

- Anssi

[Ian Kelly]

On Wed, Oct 31, 2012 at 1:23 PM, Shai Berger <sh...@platonix.com> wrote:
> This, almost worthy of being called an sql injection, can't be the right way
> to achieve the goal. In fact, the Oracle backend (or even some higher, more
> generic level) should have doubled those '"' characters to make them part of
> the name. But -- save length issues -- the ploy succeeds:

In fact, Oracle table names are not allowed to contain double quote characters at all.  Doubling them does not change this.  Per the Oracle naming rules:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

Cheers,
Ian

[Ian Kelly]

On Wed, Oct 31, 2012 at 1:23 PM, Shai Berger <sh...@platonix.com> wrote:

I'm not sure -- perhaps the db_tablespace option is interpreted by the Oracle
backend as schema, which would allow what Michal is trying to do with a sane
API. Otherwise, I'd advise Michal to look at Oracle's table aliases, and mark
this also as a missing feature.

The Oracle backend interprets db_tablespace as a tablespace.  Ultimately, the proper way to specify a schema will be with the db_schema option, but this depends on ticket #6148.  Until then, synonyms are the officially recommended workaround, but the 'schema"."table' hack is also commonly used.

[Ian Kelly]

On Wed, Oct 31, 2012 at 12:34 PM, Michał Nowotka <mmm...@gmail.com> wrote:

Another thing - this name is not truncated - some junk is appended and not after 30 characters but earlier and only for some table names regardless of length.

The truncation actually truncates to 26 characters and then appends the first four digits of the MD5 hash.  This is done to avoid collisions between long table names that share a common long prefix.

[Michał Nowotka]

I think you mixed many things here.

First of all db_table = 'schema_name\".\"table_name' is a well known
hack to make django support Oracle multiple schemas.
So this is not a bug, but it would be nice if we have better solution.

As the consequence of this hack django treats the hole db_table string
as table name which is not true in this case.

But what is really buggy here is if i explicitly specify that this
table is not managed django should respect db_table and not perform
any truncation. That's all.

[Ian Kelly]

On Wed, Oct 31, 2012 at 2:18 PM, Michał Nowotka <mmm...@gmail.com> wrote:

But what is really buggy here is if i explicitly specify that this
table is not managed django should respect db_table and not perform
any truncation. That's all.

It won't truncate the name if you fully enclose it in quotes.  Instead of:

    db_table = 'schema_name\".\"table_name'

Try:

    db_table = '\"schema_name\".\"table_name\"'

This only works for unmanaged models.  For managed models it results in an error when it tries to create the sequence and trigger using syncdb.

[Łukasz Rekucki]

Why all the slashes ? Unless you specify r'' or make them double, they
don't do anything!

>>> '\"' == '"'
True

--
Łukasz Rekucki

[Ian Kelly]

On Wed, Oct 31, 2012 at 2:57 PM, Łukasz Rekucki <lrek...@gmail.com> wrote:

Why all the slashes ? Unless you specify r'' or make them double, they
don't do anything!

Because the OP had them, and for visual clarity, since it can be hard to distinguish whether '" is an a single quote followed by a double quote or vice-versa.