[CDBI] Using before_update trigger to history

Peter Speltz peterspeltz at gmail.com
Sat Jan 21 01:14:47 GMT 2006


I'm curious about this too. The code below is certainly undesireable
to me and gave me a big headache.  But maybe your requirements demand
that.  If an object has its own history table i  dont see why  you
could not just do a copy .

I was going to have a single table -- "history"  to use for the entire
database. :

id               --- autoincrement
created       --- timestamp current_timestamp
table_name --- name table recording history of
row_id         --- id of row
dump          ---- delimeted string  like "col, 'value', col2, 'value' . . ."


Then , i'm pretty sure recent versions of CDBI were patched with  this
has_many constraints patch   :
http://www.spanner.org/lists/cdbi/2004/10/25/87be3cae.html

So say :
__PACKAGE__->has_many(history => 'History' , 'row_id' , {constraint =>
{table_name => __PACKAGE__->table}});

#Then the update trigger would be as simple as
__PACKAGE__->add_trigger( before_update => sub {
     my $self        = shift;
     my $dump = join ( ', ', map { $_ . ", ". $self->$_ } $self->columns );
     $self->add_to_history(dump => $dump);
}

# retrieve some history

my %prev_data  = split( ',' , $self->history->last->dump) ;
# i pretty positive has_many method returns iterator in scalar context.

cheers,


On 1/20/06, Bill Moseley <moseley at hank.org> wrote:
> I thought there was a wiki entry on using before_update to keep a
> history of changes to a record.
>
> I want to create a copy of a row before updating.  I'm not thrilled
> about the code below, so I'm wondering what others might be doing.
>
> Is there a "suggested" way to keep history records?  Probably better
> as a trigger in the database, I suspect.
>
>
>
> __PACKAGE__->add_trigger( before_update => sub {
>     my $self        = shift;
>     my $table       = $self->table;
>     my $id_col      = $self->primary_column->name;
>     my $hist_table  = $table . '_history';
>     my $hist_class  = ref($self) . 'History';
>
>     $self->last_updated( 'now()' )
>         if $self->find_column( 'last_updated' );
>
>     # Gather up columns common in both tables.
>     my @hist_cols =
>         grep { $hist_class->find_column( $_ ) }
>             grep { $_ ne $id_col }
>                 map { $_->name } $self->columns;
>
>
>     my @source_cols = @hist_cols;
>
>     # copy the id from the source table to the id in the history table
>     push @source_cols, $id_col;
>     push @hist_cols, 'copy_of_' . $id_col;
>
>
>
>
>     my $source_cols = join ', ', @source_cols;
>     my $hist_cols = join ', ', @hist_cols;
>
>
>     my $sth = $self->db_Main->prepare(<<"");
>         INSERT INTO $hist_table ($hist_cols)
>             SELECT $source_cols from $table
>             WHERE id = ?
>
>     $sth->execute( $self->id );
>
> });
>
>
>
>
>
>
> --
> Bill Moseley
> moseley at hank.org
>
>
> _______________________________________________
> ClassDBI mailing list
> ClassDBI at lists.digitalcraftsmen.net
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
>


--
pjs




More information about the ClassDBI mailing list