Oracle: blank, null, and empty_strings_allowed

[André Pang]

Hi all,

The Django Oracle documentation has the following to say about NULL and empty strings (emphasis mine):

Django generally prefers to use the empty string ('') rather than NULL, but Oracle treats both identically. To get around this, the Oracle backend coerces the null=True option on fields that have the empty string as a possible value.

However, I've found that the Oracle schema that Django generates always allows NULLs on all string-like fields (CharField, TextField, FilePathField, etc). I'd assume that Django would only generate schemas where NULL is allowed for fields where blank=True, and that fields where blank=False would have "NOT NULL" included in the Oracle DDL.

It appears that the Oracle backend looks at an undocumented "empty_strings_allowed" Field attribute to determine whether to output NOT NULL for the DDL. I've got a local patch to override the empty_strings_allowed field so that it's dependent on the Field's blank attribute, like so:

@property
def empty_strings_allowed_if_blank(self):
    return self.blank
models.fields.CharField.empty_strings_allowed = empty_strings_allowed_if_blank 

models.fields.TextField.empty_strings_allowed = empty_strings_allowed_if_blank 

[etc]

This behavior feels like a bug in the Oracle backend that should be fixed, but I might be misunderstanding the documentation. I'm also unsure whether it's the Field subclasses that should patched or the Oracle backend that's in error.

If someone could take a look so that Django 1.5 can have this fixed, that'd be great.  Thanks!

[Ian]

On Monday, June 18, 2012 2:17:26 PM UTC-6, André Pang wrote:

Hi all,

The Django Oracle documentation has the following to say about NULL and empty strings (emphasis mine):

Django generally prefers to use the empty string ('') rather than NULL, but Oracle treats both identically. To get around this, the Oracle backend coerces the null=True option on fields that have the empty string as a possible value.

However, I've found that the Oracle schema that Django generates always allows NULLs on all string-like fields (CharField, TextField, FilePathField, etc).

Because those are all "fields that have the empty string as a possible value".

 

I'd assume that Django would only generate schemas where NULL is allowed for fields where blank=True, and that fields where blank=False would have "NOT NULL" included in the Oracle DDL.

blank=True is a validation option, not a database option.  A field with blank=False can still have the empty string as a possible value, and it can be stored like that in any of the other backends; it's just not accepted by the admin site.  In this case it's mainly a cross-compatibility issue -- if an app stores the empty string on a blank=False field in Postgres, it should be able to do that when the backend is Oracle as well.

Cheers,
Ian

[André Pang]

On Jun 18, 2012, at 4:05 PM, Ian wrote:

>> I'd assume that Django would only generate schemas where NULL is allowed for fields where blank=True, and that fields where blank=False would have "NOT NULL" included in the Oracle DDL.
>
> blank=True is a validation option, not a database option. A field with blank=False can still have the empty string as a possible value, and it can be stored like that in any of the other backends; it's just not accepted by the admin site.

My mistake; I forgot that the blank attribute is an admin validation option only.

> In this case it's mainly a cross-compatibility issue -- if an app stores the empty string on a blank=False field in Postgres, it should be able to do that when the backend is Oracle as well.

Gotcha: since Oracle uses NULL to denote empty strings, the Oracle backend overrides Field.null to always be True.

What I'd like to do is (1) disallow NULLs, and (2) disallow empty strings. It looks like there's no current way to do this with Oracle since the backend overrides null to always be True, and blank is an admin validation thing only.

I'm happy to make my own local subclass of CharField/TextField/etc that has the behavior that I'd like; I'm guessing that the Django team don't intend to change this behavior. (It seems that the only way to do this would be to change the semantics of null, which seems foolhardy, or add yet another option, which would be quite confusing with null and blank already.)

[Kurtis Mullins]

I've read over this many a time -- never paid much attention because I don't use Oracle: https://docs.djangoproject.com/en/dev/ref/models/fields/#null

Anyways, if you don't allow NULL and you don't allow empty strings, what are you going to put in there when there's nothing?

--
You received this message because you are subscribed to the Google Groups "Django users" group.

To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[André Pang]

The idea is to try to ensure that no empty strings make it to the database, so you're not allowed to insert them into the database. You can ensure this at the Django (ORM) layer right now with blank=True for the admin or by checking the blank attribute manually in your own code, but there's no database-level restriction, which is what I'm after.

In Oracle:

SQL> create table T (c nvarchar2(16) not null);
Table created.
SQL> insert into T values('hello');
1 row created.
SQL> insert into T values ('');
insert into T values ('')
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("T"."C")

[Ian]

On Monday, June 18, 2012 5:59:27 PM UTC-6, André Pang wrote:

What I'd like to do is (1) disallow NULLs, and (2) disallow empty strings.  It looks like there's no current way to do this with Oracle since the backend overrides null to always be True, and blank is an admin validation thing only.

There's also no current way* to do this AFAIK with postgresql, mysql, or sqlite.  I'm not opposed to the feature request, but if we're going to do it, then I think it should be universal, not just for Oracle.  For the other backends I suppose a CHECK constraint would be required.

Cheers,
Ian
 
* Of course with any backend there is the option of writing custom DDL and running it instead of or in addition to syncdb.

[Kurtis Mullins]

So you're trying to, basically, enforce "required=True" at the database-level?

--

You received this message because you are subscribed to the Google Groups "Django users" group.

To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/GbrE1zMU3N4J.

[Melvyn Sopacua]

On 20-6-2012 0:49, Andr� Pang wrote:
> The idea is to try to ensure that no empty strings make it to the
> database, so you're not allowed to insert them into the database. You
> can ensure this at the Django (ORM) layer right now with blank=True for
> the admin or by checking the blank attribute manually in your own code,
> but there's no database-level restriction, which is what I'm after.

Because this would break consistency. Your application would behave
differently for different backends, which is rather confusing.

Your change would throw null violation errors when blank values are
inserted /only in Oracle/. So when empty strings are possibilities the
backend has to enforce "NULL is ok" on the field definition to keep
behavior consistent.
If blank values are not acceptable in your application, you'd do this in
your model definition and form validation. Your real issue is with the
SQL standards body and Oracle and whoever thought it was a good idea to
make NULL and "value of zero length" different concepts.

--
Melvyn Sopacua

[Thomas Lockhart]

On 6/20/12 3:33 AM, Melvyn Sopacua wrote:
> On 20-6-2012 0:49, Andr� Pang wrote:
>> The idea is to try to ensure that no empty strings make it to the
>> database, so you're not allowed to insert them into the database. You
>> can ensure this at the Django (ORM) layer right now with blank=True for
>> the admin or by checking the blank attribute manually in your own code,
>> but there's no database-level restriction, which is what I'm after.
> Because this would break consistency. Your application would behave
> differently for different backends, which is rather confusing.

There is great value in enforcing data integrity at the database level.
Django does a beautiful job of mapping code to database definitions and
in some sense that might distract from the traditional approach of
defining databases and integrity independent of the application. imho
one should not feel shy about adding additional constraints or checks at
the database level. If your app enforces everything you hope it does
then your db constraints will never be triggered, and if your app has a
hole in it then your database will catch it and keep bad data from
polluting it.

There are other ways around this too: in Postgres at least you can
define a rule which would substitute a null for a blank on data entry.

Breaking consistency between backends sounds bad, but designing to the
least common denominator is not a requirement. This may just suggest
that other backends which can distinguish between nulls and zero-length
strings would be a better choice for some apps ;)

hth

- Tom

[André Pang]

Right. There's currently no way to enforce required=True at the database level.

I honestly don't mind whether Django decides to do this or not, since I can always hack our local tree to enforce this at the database level, but I thought it's an idea worth discussing. In my case, I found a couple of stray records with empty strings in columns where that wasn't supposed to happen, so enforcing it at the database level for me is a worthwhile safety concern.

There are obviously questions about whether this idea is generally applicable, and whether it can be implemented across all the backends. I'm just happy that I understand why the Oracle driver now coerces all fields to be null=True, even though that's not suitable for the project I'm working on. (Yay open source :).

[Melvyn Sopacua]

On 20-6-2012 20:31, Andr� Pang wrote:
> Right. There's currently no way to enforce required=True at the database
> level.

So, your model clearly defines that it doesn't allow empty values, yet
some were inserted? Can you pull this out of the abstract with an example?

> I honestly don't mind whether Django decides to do this or not, since I
> can always hack our local tree to enforce this at the database level,
> but I thought it's an idea worth discussing. In my case, I found a
> couple of stray records with empty strings in columns where that wasn't
> supposed to happen, so enforcing it at the database level for me is a
> worthwhile safety concern.

How did you define 'supposed to happen'?

I think a more worthwhile solution is to have the default value for a
field be propagated to the database as default value. In that case,
empty strings on NOT NULL columns will be consistent.

And if the COMMENT syntax for fields/tables wasn't such a mess accross
db's I'd say map help_text to COMMENT as well.
--
Melvyn Sopacua

[André Pang]

On 6/20/2012 11:46 AM, Melvyn Sopacua wrote:

> On 20-6-2012 20:31, André Pang wrote:
>> Right. There's currently no way to enforce required=True at the database
>> level.
>
> So, your model clearly defines that it doesn't allow empty values, yet
> some were inserted? Can you pull this out of the abstract with an example?

There's no way for a model to disallow empty values, AFAIK. The example
I have resembles this:

class Foo(models.Model):
my_field = models.CharField(null=False, blank=True)

In Oracle's case, the null=False (which is the default) is ignored and
always coerced to null=True, which means that empty strings are allowed.
(I'm not sure what the behavior is for other databases; I guess it
depends on whether the backend regards NULL == '' or not.)

Since blank=True is an admin thing only, and required=True is an
attribute of a form and not a model, any non-form or non-admin code that
creates a model can insert an empty string into the database. Most of
the code we've written so far isn't form code nor admin code, e.g. we
have command-line Django scripts that import records from a legacy
FileMaker Pro database to Oracle, with a lot of garbage data containing
empty strings in various columns where they should be illegal.
Enforcing this at the database level would be nice.

> I think a more worthwhile solution is to have the default value for a
> field be propagated to the database as default value. In that case,
> empty strings on NOT NULL columns will be consistent.

In our case, there is no sensible default value for the field; e.g. what
if the field's a ForeignKey or a primary key[1]? The schema we have
requires that the column have good data or not exist at all, which I
don't think is insensible.

1. In Oracle's case, what if you have a model with a primary key that's
a CharField? I suspect that Oracle backend will coerce that to be
null=True, so now you have a primary key field that could have NULL.

[Ian Kelly]

On Wed, Jun 20, 2012 at 12:31 PM, André Pang <andre...@gmail.com> wrote:
> I honestly don't mind whether Django decides to do this or not, since I can
> always hack our local tree to enforce this at the database level, but I
> thought it's an idea worth discussing. In my case, I found a couple of stray
> records with empty strings in columns where that wasn't supposed to happen,
> so enforcing it at the database level for me is a worthwhile safety concern.

Sure. And bear in mind that there is absolutely no requirement to use
syncdb at all. When we wrote the Oracle backend we had in mind that
most users would probably want to have Django generate the DDL scripts
and then tweak them to their liking before handing them off to the DBA
-- due to process concerns or Oracle DBAs who often want to see things
like storage declarations that are way out of scope for Django. At my
workplace, many of the tables that Django deals with are actually
updatable views, which are obviously not produced by syncdb.

Cheers,
Ian

[Melvyn Sopacua]

On 20-6-2012 21:38, André Pang wrote:
> On 6/20/2012 11:46 AM, Melvyn Sopacua wrote:
>
>> On 20-6-2012 20:31, André Pang wrote:
>>> Right. There's currently no way to enforce required=True at the database
>>> level.
>>
>> So, your model clearly defines that it doesn't allow empty values, yet
>> some were inserted? Can you pull this out of the abstract with an
>> example?
>
> There's no way for a model to disallow empty values, AFAIK. The example
> I have resembles this:
>
> class Foo(models.Model):
> my_field = models.CharField(null=False, blank=True)
>
> In Oracle's case, the null=False (which is the default) is ignored and
> always coerced to null=True, which means that empty strings are allowed.
> (I'm not sure what the behavior is for other databases; I guess it
> depends on whether the backend regards NULL == '' or not.)

Isn't your problem here that Blank=True? It should be False if you don't
want empty strings.
Also, it don't matter for the application for this example, since the
difference between NULL and '' is only valid for unique keys, since NULL
values are not subject to constraints.

> Since blank=True is an admin thing only, and required=True is an
> attribute of a form and not a model, any non-form or non-admin code that
> creates a model can insert an empty string into the database.

Isn't this what you're looking for?
<https://docs.djangoproject.com/en/1.4/ref/models/instances/#validating-objects>

This isn't tied to forms, you just need to call the validation methods
and though I'm not in a position to check it right now, I'm pretty sure
that when blank=False is set on the model, validation will fail.

>> I think a more worthwhile solution is to have the default value for a
>> field be propagated to the database as default value. In that case,
>> empty strings on NOT NULL columns will be consistent.
>
> In our case, there is no sensible default value for the field; e.g. what
> if the field's a ForeignKey or a primary key[1]?

Which is /exactly/ why I mention it. If the default value is inserted
instead of an empty string, you can pre-insert the default value and
have it linked to an invalid entry. Any attempts to insert the default
value on the primary key of the linked model will trigger an integrity
error. Any models referencing the pre-inserted "invalid key" can now be
identified and relinked properly.
--
Melvyn Sopacua

[Ian]

On Wednesday, June 20, 2012 1:38:45 PM UTC-6, André Pang wrote:

1. In Oracle's case, what if you have a model with a primary key that's
a CharField?  I suspect that Oracle backend will coerce that to be
null=True, so now you have a primary key field that could have NULL.

Primary keys are an exceptions.  The backend doesn't try to make them nullable, and the database wouldn't accept that as valid DDL anyway.

[Ian]

On Thursday, June 21, 2012 4:37:35 AM UTC-6, Melvyn Sopacua wrote:

Which is /exactly/ why I mention it. If the default value is inserted
instead of an empty string, you can pre-insert the default value and
have it linked to an invalid entry. Any attempts to insert the default
value on the primary key of the linked model will trigger an integrity
error. Any models referencing the pre-inserted "invalid key" can now be
identified and relinked properly.

Then all your application logic and views have to be written to ignore the invalid entry.  It seems a lot simpler and less error-prone to just tweak the DDL to add a "NOT NULL" constraint.