Re: How to approach converting to CDBI?

[prev] [thread] [next] [Date index for 2005/07/04]

Brett Sanger wrote:
> I've been browsing about the list here for a while, and I'm tackling
> converting the code at work to start using CDBI.
> 
> I've read several posts indicating that CBDI is not a drop-in
> replacement for SQL, because it's a different way of thinking about
> things.
> 
> That's fine, I'm not the world's biggest SQL fan, and I'm willing to
> adapt.
> 
> However, I'm unsure what the wisest way to convert some of our code over
> would be.  Advice and pointers appreciated.
> 
> For example, we have a db of office memos.  One commonly accessed
> function is to look at the most recent memos.  This is a simple WHERE
> clause with a LIMIT command.  (Postgres).
> 
> In CDBI view, i'm tackling this with add_constructor because of the
> LIMIT...does that make sense?


Yeah, that makes perfect sense and it's the way I do it most of the time.

> Next, I've found a statement like this (trimmed):
> 
> select id, extract(year from filedate), extract(month from filedate),
> extract(day from filedate), email, ap_date(filedate) from table
> where...
> 
> It looks as though they are pull YMD separately from the date field, and
> then later calling a stored procedure ap_date, which does the ugly
> translation of the date into AP (Associated Press) style (known here as
> the "hates computers" style).
> 
> has_a will let me fiddle with the date, but is that really the best way
> to tackle this?  Is the CDBI concept to stop doing transformations and
> twistings of columns on the SQL-side?

Yeah, this is actually one of my favorite things about C::DBI and that's
column inflation/deflation. I would inflatet the date (or datetime)
columns into DateTime objects (some people like Time::Piece, but it's
the same approach).

__PACKAGE__->has_a(
    filedate => 'DateTime',
    inflate  => sub { DateTime::Format::Pg->parse_timestamp(shift) },
    deflate  => sub { DateTime::Format::Pg->format_datetime(shift) },
);

Now anytime in your application you just call
 $obj->filedate()
and it's a DateTime object, so you can manipulate it any way you want.

> Lastly, we have a lot of code that sets a timestamp to "now()".  Should
> I replace that with the perl-thought of current time?

That's a hard one. For instance if your database is remote or on a
separate box you probably want it's time and not the application's perl
time.

But if you wanted to use the current 'perl' time, then it's as simple as
  DateTime->now();

        -- 
        Michael Peters
Developer
Plus Three, LP