Re: [CDBI] Using before_update trigger to history

[prev] [thread] [next] [Date index for 2006/01/21]

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 =3D> 'History' , 'row_id' , {constraint =3D>
{table_name =3D> __PACKAGE__->table}});

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

# retrieve some history

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

cheers,


On 1/20/06, Bill Moseley <moseley@xxxx.xxx> 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 =3D> sub {
>     my $self        =3D shift;
>     my $table       =3D $self->table;
>     my $id_col      =3D $self->primary_column->name;
>     my $hist_table  =3D $table . '_history';
>     my $hist_class  =3D ref($self) . 'History';
>
>     $self->last_updated( 'now()' )
>         if $self->find_column( 'last_updated' );
>
>     # Gather up columns common in both tables.
>     my @hist_cols =3D
>         grep { $hist_class->find_column( $_ ) }
>             grep { $_ ne $id_col }
>                 map { $_->name } $self->columns;
>
>
>     my @source_cols =3D @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 =3D join ', ', @source_cols;
>     my $hist_cols =3D join ', ', @hist_cols;
>
>
>     my $sth =3D $self->db_Main->prepare(<<"");
>         INSERT INTO $hist_table ($hist_cols)
>             SELECT $source_cols from $table
>             WHERE id =3D ?
>
>     $sth->execute( $self->id );
>
> });
>
>
>
>
>
>
> --
> Bill Moseley
> moseley@xxxx.xxx
>
>
> _______________________________________________
> ClassDBI mailing list
> ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
>


--
pjs

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi