[sf-perl] [Fwd: class DBI postgres date time format]

[Fred Moyer]

2009/3/18 ken uhl <kenuhl at berkeley.edu>:
>
>
> How do I set up table class method to correctly format and write 'updated'
>  column  date and time stamp to postgres?

With PostgreSQL you don't need to deflate an object if you just want
to mark the time the row was updated.

I have two columns, cts (creation time stamp) and mts (modification
time stamp).  cts gets set automatically when the row is inserted, and
mts updates to now() on subsequent updates via a trigger.

cts              | timestamp without time zone | default now()
mts              | timestamp without time zone | default now()

create or replace function update_modified_column() returns trigger as $$ BEGIN
NEW.mts = now();
return new;
END;
$$ language 'plpgsql';

create trigger update_table_mts BEFORE UPDATE ON router FOR EACH ROW
EXECUTE PROCEDURE update_modified_column();

Here's an actual record update, all I did was change a boolean in the record:

cts              | 2007-08-29 12:55:22.028357
mts              | 2008-08-12 11:58:37.77211

cts              | 2007-08-29 12:55:22.028357
mts              | 2009-03-19 00:43:54.714981


Granted, this approach involves no Perl, but TMTOWTDI, and row
modification creation and timestamping seems like something that
should be done in the database (although I need a trigger for each
table I track the cts and mts.  Modifying other date columns
represents a chance though to use object inflation and deflation in
Perl.

>
> I have table entry object that contains this - to inflate and deflate :
>
> I think I need to add  "has_timestamp"
> .....
>
>    22 __PACKAGE__->table('dhcpmac');
>    23 __PACKAGE__->columns( Primary => qw/rowid/ );
>    24 __PACKAGE__->columns( All => qw/rowid mac calnetuid updated updatedby
> dynhostname ishmael disable/ );
>    25
>    26 __PACKAGE__->has_a( updated => 'DateTime',
>    27                     inflate => sub {
> DateTime::Format::Pg->parse_timestamptz(shift); },
>    28                     deflate => sub {
> DateTime::Format::Pg->format_timestamptz(shift); } );
>
> ...
>
> my CGI  does this :
>
> ...
> #  retrieve old row data   301          my $delta_row =
> IST::DNSDB::dhcpmac->retrieve($old_rowid) ;
>   302          my $old_mac = $delta_row->mac();
> ...
> #  replace some of the data - including 'updated'
>   305          $delta_row->mac($new_validated_mac);
>   306              $delta_row->updated( "now()" );
> ..
>   308          $delta_row->updatedby($calnetuid);
> ...
>   313           $delta_row->update();
>   314           $delta_row->commit();
>
> ( line 306 crashes with invalid SQL command line value, obviously, but what
> goes here? )
>
> ?
>
> TIA,
> Ken Uhl,
> UC Berkeley
>
>
>
>
> _______________________________________________
> SanFrancisco-pm mailing list
> SanFrancisco-pm at pm.org
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>
>