[sf-perl] SanFrancisco-pm Digest, Vol 50, Issue 7
ken uhl
kenuhl at berkeley.edu
Thu Mar 19 12:47:05 PDT 2009
Thanks everyone
my solution was from Frost :
$delta_row->updated( DateTime->now() );
Ken U
UCBerkeley
esanfrancisco-pm-request at pm.org wrote:
> Send SanFrancisco-pm mailing list submissions to
> sanfrancisco-pm at pm.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
> or, via email, send a message with subject or body 'help' to
> sanfrancisco-pm-request at pm.org
>
> You can reach the person managing the list at
> sanfrancisco-pm-owner at pm.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of SanFrancisco-pm digest..."
>
>
> Today's Topics:
>
> 1. [Fwd: class DBI postgres date time format] (ken uhl)
> 2. Re: [Fwd: class DBI postgres date time format] (Kevin Frost)
> 3. Re: [Fwd: class DBI postgres date time format] (Dan Dascalescu)
> 4. Re: [Fwd: class DBI postgres date time format] (Kevin Frost)
> 5. Re: [Fwd: class DBI postgres date time format] (Fred Moyer)
> 6. Re: [Fwd: class DBI postgres date time format] (Kevin Frost)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 18 Mar 2009 16:01:40 -0700
> From: ken uhl <kenuhl at berkeley.edu>
> Subject: [sf-perl] [Fwd: class DBI postgres date time format]
> To: perl beginners <sanfrancisco-pm at pm.org>
> Message-ID: <49C17D54.9080908 at berkeley.edu>
> Content-Type: text/plain; charset="iso-8859-1"; Format="flowed"
>
>
> -------------- next part --------------
> An embedded message was scrubbed...
> From: ken uhl <kenuhl at berkeley.edu>
> Subject: class DBI postgres date time format
> Date: Tue, 17 Mar 2009 11:09:22 -0700
> Size: 1716
> URL: <http://mail.pm.org/pipermail/sanfrancisco-pm/attachments/20090318/56a37741/attachment-0001.eml>
>
> ------------------------------
>
> Message: 2
> Date: Thu, 19 Mar 2009 00:14:19 -0700
> From: Kevin Frost <biztos at mac.com>
> Subject: Re: [sf-perl] [Fwd: class DBI postgres date time format]
> To: San Francisco Perl Mongers User Group <sanfrancisco-pm at pm.org>
> Message-ID: <40C5276D-53D6-4E8B-89E2-FFB9316563D5 at mac.com>
> Content-Type: text/plain; charset="us-ascii"; Format="flowed";
> DelSp="yes"
>
> It looks like your "updated" column is mapped as a DateTime object
> (line 26).
>
> You probably want to do something like this:
>
> $delta_row->updated( DateTime->now() );
>
> Further resources:
>
> http://search.cpan.org/dist/DateTime/lib/DateTime.pm
>
> http://search.cpan.org/dist/DateTime-Format-Pg/lib/DateTime/Format/Pg.pm
>
> cheers
>
> -- frosty
>
> On Mar 18, 2009, at 4:01 PM, ken uhl wrote:
>
>
>> From: ken uhl <kenuhl at berkeley.edu>
>> Date: March 17, 2009 11:09:22 AM PDT
>> To: beginners at perl.org
>> Subject: class DBI postgres date time format
>>
>>
>> How do I set up table class method to correctly format and write
>> 'updated' column date and time stamp to postgres?
>>
>> 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
>>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://mail.pm.org/pipermail/sanfrancisco-pm/attachments/20090319/cd7b3d0d/attachment-0001.html>
>
> ------------------------------
>
> Message: 3
> Date: Thu, 19 Mar 2009 01:06:42 -0700
> From: Dan Dascalescu <ddascalescu at gmail.com>
> Subject: Re: [sf-perl] [Fwd: class DBI postgres date time format]
> To: San Francisco Perl Mongers User Group <sanfrancisco-pm at pm.org>
> Message-ID:
> <3561cc6d0903190106n4bbcc53dlbc3c2aa6096b40fe at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
>
>> How do I set up table class method to correctly format and write 'updated'
>> ?column ?date and time stamp to postgres?
>>
>
> DBIx::Class::TimeStamp might take care of that automagicallly.
>
>
> ------------------------------
>
> Message: 4
> Date: Thu, 19 Mar 2009 01:23:26 -0700
> From: Kevin Frost <biztos at mac.com>
> Subject: Re: [sf-perl] [Fwd: class DBI postgres date time format]
> To: San Francisco Perl Mongers User Group <sanfrancisco-pm at pm.org>
> Message-ID: <390FC95E-C446-4E6C-ADE7-C6E9BEE20049 at mac.com>
> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
>
> Is there a Class::DBI equivalent?
>
> Of course you could just subclass Class::DBI and do it automatically
> for everything that has an "updated" column. But for the same amount
> of effort you could learn DBIx::Class, so... :-)
>
> -- f.
>
> On Mar 19, 2009, at 1:06 AM, Dan Dascalescu wrote:
>
>
>>> How do I set up table class method to correctly format and write
>>> 'updated'
>>> column date and time stamp to postgres?
>>>
>> DBIx::Class::TimeStamp might take care of that automagicallly.
>> _______________________________________________
>> SanFrancisco-pm mailing list
>> SanFrancisco-pm at pm.org
>> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>>
>
>
>
> ------------------------------
>
> Message: 5
> Date: Thu, 19 Mar 2009 01:37:08 -0700
> From: Fred Moyer <fred at redhotpenguin.com>
> Subject: Re: [sf-perl] [Fwd: class DBI postgres date time format]
> To: San Francisco Perl Mongers User Group <sanfrancisco-pm at pm.org>
> Message-ID:
> <ad28918e0903190137n55afc1cewf4cd5275436f505b at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> 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
>>
>>
>>
>
>
> ------------------------------
>
> Message: 6
> Date: Thu, 19 Mar 2009 01:55:24 -0700
> From: Kevin Frost <biztos at mac.com>
> Subject: Re: [sf-perl] [Fwd: class DBI postgres date time format]
> To: San Francisco Perl Mongers User Group <sanfrancisco-pm at pm.org>
> Message-ID: <558A5336-CF67-46BF-83EA-DABFB8C4CE10 at mac.com>
> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
>
> Funny, I was almost going to mention triggers in my first response.
>
> The nice thing about doing it in a trigger is there's no way to forget/
> break the logic in your application. The less-nice thing is that you
> have a performance hit per update, which can be a problem in high
> transaction volumes. It's definitely something you'd want to
> benchmark (triggers vs. Perl w/DateTime vs. Perl w/o DateTime) if you
> expect to be doing a lot of updates.
>
> Also, I would argue that in Fred's example the modified timestamp
> ("mts") should not have a default value. When you create a record but
> do not subsequently modify it, its modification timestamp should be
> NULL, so you can tell the difference. This of course is highly
> impractical because you then have to remember about the NULLs in all
> your queries for the rest of your life, so it's an argument I often
> lose.
>
> FWIW PostgreSQL is also pretty forgiving in its date casting, and for
> quick hacks and debugging you can often just skip the TO_DATE
> altogether.
>
> And now back to our regularly scheduled Perl programming...
>
> -- f.
>
>
> On Mar 19, 2009, at 1:37 AM, Fred Moyer wrote:
>
>
>> 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
>>>
>>>
>>>
>> _______________________________________________
>> SanFrancisco-pm mailing list
>> SanFrancisco-pm at pm.org
>> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>>
>
>
>
> ------------------------------
>
> _______________________________________________
> SanFrancisco-pm mailing list
> SanFrancisco-pm at pm.org
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>
>
> End of SanFrancisco-pm Digest, Vol 50, Issue 7
> **********************************************
>
>
More information about the SanFrancisco-pm
mailing list