Gantry::Utils::Model - a general purpose Object Relational Model base class


    use base 'Gantry::Utils::Model';

    sub get_table_name     { return 'your_table';                           }
    sub get_sequence_name  { return 'your_table_seq';                       }
    sub get_primary_col    { return 'id';                                   }
    sub get_essential_cols { return 'id, text_col';                         }

    sub get_primary_key    { goto &get_id;                                  }

    sub set_id             { croak "Can't change primary key";              }
    sub get_id             { return $_->[0]{id};                            }
    sub quote_id           { return $_[1];                                  }

    sub set_text_col       {
        my $self  = shift;
        my $value = shift;

        $self->{text_col} = $value;
        return $value;
    sub get_text_col       { return $_->[0]{text_col};                      }
    sub quote_text_col     {
        return ( defined $_[1] ) ? "'$_[1]'" : 'NULL';

    sub set_other_text_col {
        my $self  = shift;
        my $value = shift;

        $self->{other_text_col} = $value;

        return $value;
    sub get_other_text_col {
        my $self = shift;
        unless ( defined $self->{other_text_col} ) {
            $self->lazy_fetch( 'other_text_col' );
        return $self->{other_text_col};
    sub quote_other_text_col     {
        return ( defined $_[1] ) ? "'$_[1]'" : 'NULL';


This module is a Class::DBI replacement. Its goal is to reduce the mystery in the internals of that module, while still providing most of its functionality. You'll notice that the inheriting class has a lot more code than a Class::DBI subclass would. This is because we use Bigtop to generate the subclasses. Thus, we don't care so much about the volume of code. The result is code which is easy to read, understand, override and/or modify.


Class::DBI and its cousins provide beautiful APIs for client code. By implementing straightforward database row to Perl object correspondence, they save a lot of mental effort when writing most applications.

They do have drawbacks. My premise is that most of these drawbacks stem from a single fundamental design descision. Perl's traditional Object Relation Mappers (ORMs) do a lot of work at run time. For instance, they build accessors at run time. When I first started using them, I thought this was gorgeous. Class::DBI::mysql was one of my favorite modules. I bought the promise of a future where all you had to say was something like

    package MyModel;

    use Class::DBI::SuperClever
        'dbi:Pg:dbname=somedb', 'user', 'passwd', 'MyModel';

and the whole somedb database would be mapped without another word. Each table would become a class under MyModel with an accessor for each column. Then I could create, retrieve, update, and delete to my heart's content while beholding the power of Perl.

The problem is that use statements like the above example require extreme magic (and not a small amount of time). This leads to a lack of transperency which leaves me with three problems: (1) I worry, in the back of my mind I always have the doubt of not knowing what is going on in these complex beasts (2) I get hit by subtle bugs, like name collisions from inheritence and inadvertant overriding (3) worst, I am left with a system that works really well to do the things the author thought of, but not the thing I really need to do in a particular instance (either because the system is inherently limiting or more likely because it is so complex I can't wrap my small mind around it well enough to carry out my task).

This leads to the fundamental principle of this module: simplicity. Any programmer with intermediate Perl skills and a passing familiarity with SQL databases should be able to digest this in a morning. There are other goals, but simplicity is at the core.

In order to achieve transperency, it is necessary to have more code in the subclasses. This is really why the magical schemes sprang up. But, recently I have been working on generation of code. This amounts to the same thing, but it happens ahead of time. So, instead of code being generated by magic during run time, my code is generated by grammar based parsing before compile time. The generator in question is bigtop which can build a completely funcational web app from a description of its data model and controllers. Then, when a programmer wonders what the model is up to, she has a set of simple modules which explicitly show what is going on. To make change, she may add methods or override the existing generated ones.



Class or instance method. You can pass in a handle or this will call db_Main to get the standard one. In either case, it will rollback any current transaction (if you aren't auto-committing) and disconnect the handle.


Class or instance method. By default the dbh managed by this module has AutoCommit off. Call this to commit your transactions.


Class method. Mainly for internal use. This method takes a hash (usually one bound to a statement handle) and turns it into an object of the subclass through which it was called.


Class method. Accepts sql SELECT statements returns a list (not a reference or iterator) of objects in the class through which it was called. Be careful with column names, they need to be the genuine names of columns in the underlying table.


Class method. Pass a list of key/value pairs or a single hash ref. The only legal key is order_by, its value will be used literally directly after 'ORDER BY' (that means, don't include the ORDER BY keywords in your value). Returns a list of objects.


Class method. Pass a single primary key value. Returns the row with that primary key value as an object or undef if no such row is found.


Class method. Similar to retrieve in Class::DBI. If called with one argument, that argument is taken as a primary key and the request is forwarded to retrieve_by_pk. If called with multiple arguments (or no arguments), those arguments are forwarded to search.

Class method. Similar to search in Class::DBI. Call with the key/value pairs you want to match in a simple list.

Returns a list of objects one each for every row that matched the search criterion.

Add a single hash reference as the last parameter if you like. That hash reference may only contain these keys:


Asks for an ORDER BY clause, the value is used literally to fill in the blank in 'ORDER BY ___'.


Indicates that you want paging. The value is the number of rows per page. There is no default, since the absence of this key is taken to mean you don't want paging.


Ignored unless rows is supplied. Defaults to 1. This is the page number to retrieve.


A synonymn for search to better match the Class::DBI::Sweet API. Note that you must set the rows key in the hash reference passed as the last argument. You may also set the page key. See above.


Instance method. Call with the column name you want to fetch. Returns nothing useful, but sets the column with the value from the corresponding row in the underlying table.


Class method. Call with a hash reference whose keys are the column names you want to populate. The value will be quoted for you according to the corresponding quote_* method in the subclass.


Class method. Returns the next value of the sequence associated with the underlying table. This is not reproduceable, it actually increments the sequence. It only works if the database is using a sequence for the table and the model implements get_sequence_name.


Class method. Call with a hash reference of search criteria (think of a WHERE clause). First, it calls search, taking a single resulting object. If that works, you get the object. Otherwise, it calls create with your hash reference and returns the new object.


Instance method. Issues an UPDATE to SET the dirty values from the invocant. Returns nothing useful, although it could die if the dbh has problems.


Instance method. Deletes the underlying row from its table and renders the invocant reference unusable.


Instance method. Call with a list of columns whose values you want. Returns the values in the invocant for the columns you requested. If you requested only one column a scalar is returned. Otherwise, you get a list.


Instance method. Call with a list of key/value pairs for columns that you want to change. Returns nothing useful.


Instance method. Primarily for internal use. Call with a column name. Returns the value in the column quoted so SQL will take it.


Class or instance method. Call with a column name and a value. Returns the value quoted for SQL as if it were stored in the column of an object. Even if you call this as an instance method, the instance values are not used.


Subclasses are welcome to override this with a meaningful routine. The one here returns an empty hash reference. Yours should provide data given as extra options to DBI during connection.


Returns the id of the row.


You can include any useful method you like in your subclass, but these are the ones this module needs.


Return the name of the table in the database that your class models.


Return the name of the sequence associated with your table. This is needed for the create method.


Return an array reference containing the columns you want to fetch automatically during retrieve, search, etc.


We assume that each table has a unique primary key (though we assume nothing about its name). Return the name of that column.


An instance method. Return the value of the primary key for the invocant.


Provide one of these for each column. Called on an existing object with a new value. It must store the value in the object's hash (whose keys are the column labels) AND set the dirty flag for the column so that eventual updates will be effective. Some callers may expect to receive the new value in return, document whether it returns that value or not. Example:

    sub set_amount {
        my $self  = shift;
        my $value = shift;

        $self->{amount} = $value;

        return $self->{amount};

Provide one of these for each column. Return the unquoted value in the column. Example:

    sub get_amount {
        my $self = shift;

        return $self->{amount};

Called as a class or instance method with one argument. Take that argument, hold it up to the light, examine in detail. Then return something that has the same value properly quoted for SQL.

Note that you should not look in the object, even if one is used as the invocant. Always only work on the other argument.

COL_NAME (completely optional)

Provide one of these for each column only if you like. Dispatch to get_ and set_ methods based on the arguments you receive. These methods are NEVER called internally, but your callers might like them. Example (with apologies to Dr. Conway):

    sub amount {
        my $self  = shift;
        my $value = shift;

        if ( defined $value ) { return $self->set_amount( $value ); }
        else                  { return $self->get_amount();         }


There is no caching. This means two things: (1) no sql statement is prepared with bind parameter place holders and stored for possible reuse (2) objects are always built for each row retrieved, even if there is a live object for that row elsewhere in memory.

There are no triggers. If you need these, put them in the accessors as needed. Feel free to override construct.

There are no iterators. Class::DBI makes iterators, but they only delay object instantiation, the full query results are pulled from the beginning. Replicating that behavior seems like the pursuit of diminishing returns.


Phil Crow <>


Copyright (c) 2006, Phil Crow.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.6 or, at your option, any later version of Perl 5 you may have available.