Perl resources, modules, and sample code

How to force NULL values into empty or undefined fields via DBIx::Class

Thursday, 15 January 2009—subject to ongoing, unnanounced updates

Modern Perl Modern Perl at Powell’s, Modern Perl at Barnes and Noble, Modern Perl at Amazon.com, or download Modern Perl for free and then recommend it to your friends, write a great review, or otherwise spread the word.

Update: The code worked, it was just inefficient. The real problem is, yay, MySQL (5.0.41). It happily sets NOT NULL columns to NULL on updates without complaint. So the technique originally shown was effective only on inserts (given DB defaults, version, etc). :(

The good news being that you can, obviously, easily enforce the constraint in the code now. Example of how to do it is below. Note that your column info has to be setup correctly in the table/row class. DBIx::Class::Schema::Loader is quite good at this and you can always do it manually.

The DBIC component

package DBIx::Class::ForceNULL;
use parent "DBIx::Class::Row";
use DBIx::Class::Exception;
use strict;

my $null = \"NULL";

sub insert {
    my $self = shift;
    for my $col ( $self->columns )
    {
        $self->$col($null)
            if $self->$col eq ""
            or ! defined $self->$col;
    }
    return $self->next::method(@_);
}

sub update {
    my $self = shift;
    my %to_update = $self->get_dirty_columns;
    return $self->next::method(@_) unless keys %to_update;
    for my $col ( keys %to_update )
    {
        next unless $to_update{$col} eq ""
            or not defined $to_update{$col};
        my $info = $self->result_source->column_info($col);
        DBIx::Class::Exception->throw("Column '$col' in " .
                                      $self->table .
                                      " cannot be null")
            unless $info->{is_nullable};

        $self->{_column_data}{$col} = $null;
    }
    return $self->next::method(@_);
}

1;

Usage

package Yesh::Schema::Result::Article;
use strict;
use warnings;
use parent "DBIx::Class";
__PACKAGE__->load_components("ForceNULL", "Core");

Why?

I’m not gonna bag on MySQL because I like it and I’ve achieved a great deal with it. As anyone familiar with it knows, though, it’s got some problems and some truly stupid default settings. Said settings are often impossible to change because you are not the DB admin or have budget hosting where they have to set it up stupid so the PHP can run. This component corrects most of the problem with empty fields being treated as values. This way you get a nice error if you try to set a NOT NULL field to an empty string.

Previously on LA Law: How to auto-generate UUID primary keys via DBIx::Class.

Bonus: for the compulsive code shortener and chained methods fan in the crowd

[Removed, it’s pointless now that the two methods can’t share the code.]