Dec 26, 2013

Teng::Row and data2row

EDIT: 2014-03-21
Added follow-up artile, Ideas on utilizing Teng#new_row_from_hash

Premise

Lately I switched my O/R mapper from Data::ObjectDriver to Teng and I really enjoy its lightness.
Some O/R mappers provide so many functionalities and, hence, they are heavy and hard to track source codes. DOD's transparent caching was tricky, for instance. Despite the fact that they are functionality-rich, when we try to write our own SQL statement, we have to take DB handle out of them and do the rest by ourselves. Then we can't make use of O/R mapper's result object, any more.
Teng, on the other hand, works nicely as O/R mapper and DBI wrapper. Execute() wraps DBI and it handles the creation of statement handle and its execution. By the way dbi(), which is called in execute(), takes care of process id check and reconnection so it's folk-safe. Other O/R mapper-ish methods for CRUD including search_by_sql() calls execute() and my favorite part is that search_by_sql() still creates Teng::Iterator object so we can use Teng::Row's functionalities with our own SQL statements.
my $itr = $teng->search_by_sql(q{
    SELECT service.*
    FROM service
    LEFT JOIN service_ranking
    ON service.id = service_ranking.service_id
    ORDER BY service_ranking.rank IS NULL service_ranking.rank ASC
}, [], 'service');
# $itr is-a Teng::Iterator
When search_by_sql() is called in list context, it returns $itr->all() so you get an array of MyApp::DB::Row::Service objects. It's really handy that you can execute complex statement and still make use of Teng::Row. It becomes even more powerful when you extend Teng::Row with MyAPP::DB::Row::*. $teng->search_by_sql() will detect table name from statement and creates corresponding table row objects. Or you can explicitly set table name via 3rd arguments, witch is 'service' in the previous example.

My Problem and Workaround

Sometimes, when retrieving column values from 2 or more tables, I get confused which table name I should set on 3rd argument. Since retrieved values don't represent any particular object or table, it's natural that there is no corresponding table row class. In such case, I called $teng->execute() and tried to create table row objects for each table. The code was something like below.
my $sth = $teng->execute(q{
    SELECT user.name, user_ranking.rank, user_ranking.fluctuation 
    FROM user
    LEFT JOIN user_ranking
    ON user.id = user_ranking.user_id
    ORDER BY user_ranking.rank IS NULL user_ranking.rank ASC
}, []);

my $user_row_class = $teng->schema->get_table('user')->{row_class};
my $ranking_row_class = $teng->schema->get_table('user_ranking')->{row_class};
my @ranked_users;
while (my $hashref = $sth->fetchrow_hashref) {
    my $user_row = $user_row_class->new(+{
        row_data => +{
            id => $hashref->{user_id},
            name => $hashref->{name},
        },
        table_name => 'user',
        teng => $teng,
    });

    my $ranking = $ranking_row_class->new(+{
        row_data => +{
            user_id => $hashref->{user_id},
            rank => $hashref->{rank},
            fluctuation => $hashref->{fluctuation},
        },
        table_name => 'user_ranking',
        teng => $teng,
    });

    # do something with MyApp::DB::Row::User and MyApp::DB::Row::UserRanking objects
    push @ranked_users, +{
        name => $user->name,
        rank => $ranking->rank,
        fluctuation => $ranking->fluctuation_str, # fluctuation_str() returns stringified fluctuation: UP, STAY and DOWN 
    };
}

Solution

The previous code worked O.K. to me, but this process was somewhat complex and I didn't really know what arguments I should specify on $row_class->new(). So I looked for some workaround and what I found was this. This $teng->data2row() allows me to do what I did above and, additionally, it sets dummy query as $row->{sql} so it should be easier to debug especially when get_column() doesn't work and the message "Specified column 'some_nongiven_column' not found in row (query: ..... )" is given on croak(). This method, however, is not merged to master branch, yet.
I'm more than happy to have this method in the near future.

EDIT: 2014-03-06

Now this method is merged to master branch as Teng#new_row_from_hash.