Empty value handling

English forums
1

Hi!

I’m migrating from an older version to 4.7.7 and have trouble copying certain fields (e.g. oxorder.oxpaid) because they have been nullable in the old version db but are non null in the new one. Of course there is no meaningful value I could fill in these fields for existing records, so I went looking into the code how are they used.

One of the usages I found is this (order_main.php):
if ( $oOrder->oxorder__oxpaid->value != “0000-00-00 00:00:00”) {

This looks like you are using this zero date as the “null” value. I find this a very strange practice and would suggest Oxid developers to re-think this approach as it is very unconvenient and unintuitive to do these kind of tricks when SQL databases support real null values nicely.

2

Hi Adam,
even in a date field? So it’s maybe worth a bug entry, isn’t it. Do you know how to do it?

Best

3

Hi Marco!

Sorry for my late reply, I forgot to subscribe to the thread an didn’t notice your answer.

I don’t know if I should be reporting this as a bug, as it’s clearly done this way in several places in the code (22 places to be exact, just search the codebase for ‘0000-00-00 00:00:00’). So at least one developer at Oxid thinks that this is a good idea :slight_smile: I just wanted to point out that there is a better way of handling empty values.

4

Hi Adam,

I’ve got an answer for this one as well. The reason for entering a value like ‘0000-00-00 00:00:00’ instead of NULL can be traced back to performance issues as well: In O’Reilly’s book “High Performance MySQL: Optimization, Backups, and Replication” it is recommended to avoid NULL if possible: http://goo.gl/e7fCSO

Regards

5

I really appreciate digging that one up Marco :slight_smile: On the other hand it only says not to use nullable columns where you wouldn’t store null values anyway. This is clearly not the case in Oxid where we are talking about exactly those columns that do contain nulls. Even in that case they write that there is only a slight performance benefit so (again :slight_smile: )I don’t think that this is a good tradeoff (confusing code vs performance).
It’s also very non-standard, in my years of building database-based apps I never encountered this practice. It should be the database’s job to store the data efficiently instead of the application trying to be cozy with a certain version of a certain storage type of a certain database and compromising it’s own code quality.

PS. I don’t expect anyone rushing to change stuff because of my comments, but I still feel the need to vent sometimes, while I’m working with the code :slight_smile: