[CDBI] Record versioning

Bill Moseley moseley at hank.org
Wed Mar 15 14:21:28 GMT 2006

On Tue, Mar 14, 2006 at 11:18:24PM -0800, Wake me in a thousand years wrote:
> http://wiki.class-dbi.com/wiki/Versioning_with_two_tables

Thanks for posting that on the Wiki.  I may update the way I'm
creating my history rows.

> The use of rules and locking may rule it out as "sane", though.

I think letting the database do this work is the way to go.  The
method I use doesn't have matching tables so I do the updates in a
CDBI trigger.  My history tables have a "copy_of_id" column that
references the "live" table.

I'm mixed on the need to worry if a row has changed between loading
and updating.  You have to because of the serial number, but that
requirement adds a bit of complexity.   In my setup there's no way
to really tell the order of the modifications other than looking at
a timestamp, and I suppose to updates could happen at the same time.

My updates happen in a transaction, and I use this before_update

__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_time( DateTime->now )
        if $self->find_column( 'last_updated_time' );

    # 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;
    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

More information about the ClassDBI mailing list