Configuration option to always insert blank value as NULL

[eluser]chris4beta[/eluser]
I prefer to insert empty fields in my DB as NULL vs ''. When using Active Record to do inserts/updates from form data, I currently have to run each array of values through a function like this to get rid of blank strings:

Code:

function db_prep_array($data){
    if (is_array($data)){
        $a = array();
        foreach ($data as $key => $value){
            if (!$value){
                $a[$key] = NULL;
            }
            else{
                $a[$key] = trim($value);
            }
        }
        return $a;
    }
    else{
        return ($data) ? trim($data) : NULL;
    }
}

I have little experience extending CodeIgniter (because it usually does everything I need it to), so is there a way to alter the way the database library handles blank strings? Maybe a configuration option in the future, "Insert blank fields as NULL" : yes/no.

I can go on living my life just fine, remembering to run the above function on every array before inserting, but I thought I'd ask if there was a way to globally tell the database to always insert NULL.

Thanks for any input on this!

[eluser]WanWizard[/eluser]
From a database point of view, NULL is often a bad choice.

Lots of queries involving these columns, most notably COUNT(*) queries, will have to resort to table scans when a field is not defined 'NOT NULL', slowing your application down, and increasing the load on your RDBMS engine.

You should really only use NULL when you actually mean 'this field has no value', not when you mean 'this field is empty', which from a database point of view are two different things.

[eluser]chris4beta[/eluser]
Thanks for the prompt response.

I weighed the pros/cons in the empty vs NULL question before I started building my app (relevant discussion), and decided that NULL was the way I wanted to go.

That said, any ideas of how to solve the original problem?

[eluser]WanWizard[/eluser]
You don't, other than manually.

Whatever method you come up with to automate it, you'll run into big trouble the moment you want to insert an empty value, or a field containing only whitespace, p.e. if have a field that is defined 'NOT NULL' (in which you HAVE to insert a value).
The person that has to maintain your code after you have disappeared from the scene will start making voodoo dolls in your image if he has to fix a problem that's caused by a trick like this one.

[eluser]chris4beta[/eluser]
You bring up a good point about how such an option could wreak havoc on 'NOT NULL' fields, and I certainly don't want people to make voodoo dolls out of me!

However, I'm not talking about generalizations here. In my schema, all NOT NULL fields are either AUTO_INCREMENT, have default values set, or are managed via the app itself to where I don't have to worry about such an issue.

I agree with you that an option to always set NULL could cause some problems for people that haven't addressed these concerns, have made a sloppy DB schema, or are inconsistent with form validation. But, the question remains... If someone DID want to automatically set all blank fields to NULL before insert, how could you go about doing it when the database class cannot be extended? Not possible?

[eluser]WanWizard[/eluser]
Several options:

* You could use a separate function (which you already have).
* You could write your own insert helper and incorporate it there ( and use it to replace $this->db->insert_string(); )
* Extend the form validation class (assuming that you insert form values), and add a 'null_when_empty' rule that you can apply.
* Be stubborn and extend the database classes (which is what I have done, see here). This requires a change to CI code to get this to work though.

[eluser]chris4beta[/eluser]
I might play with customizing the input helper, that's a good idea. Since the query helpers are defined in the main DB_driver.php, can I extend them as easily the standard helpers? If not, how would I go about it?

[eluser]WanWizard[/eluser]
No, you'll have to hack the CI classes to extend the database classes.

We've did that for the active record, the result and the cache class. Our source is public at https://www.exitecms.org/trac8/browser/trunk/system, you can download our modified version and replace yours.

[eluser]chris4beta[/eluser]
Thanks for the info, you've been very helpful.

Seems the path of least resistance is to just use my function before every insert.

I'd still vote for adding the option to the database configuration. Since empty and NULL are so different from a DB's point of view, it would be nice to tell the application how you would like it to treat blank fields - as empty, or doesn't exist.