Thanks for your reply - this really looks very promisising.

For my current needs however I may not be able to use it as I am supposed to use only those CPAN-modules that can be found in the ubuntu-repositories.

But be it as it may - is there an easy way in DBIx::Class to create a row belonging to another resultset based on a given resultset that would copy over all shared attributes?

In pseudo-code something like:

my $rs1 = $schema->resultset("table1");
my $rs2 = $schema->resultset("table2");

my $o1 = $rs1->find({ id => $id });
my $o2 = $rs2->create_copy($o1); # does something like this exist?
[download]

As a related question I know I can get plain hashes from a resultset with DBIx::Class::ResultClass::HashRefInflator but only when I use it as a setting on the resultset-class.

But how would I convert a row that I have already retrieved, i.e. something like

my $o = $rs1->find({ id => $id });
my $hash_ref = $o->as_hashref; # does this exist?
[download]

Many thanks!

As for the last part, check out DBIx::Class::ResultSet::HashRef. You use it as a parent class and then you get a handful of methods like hashref_rs which you can use as the end point of a chain.

For a similar but heavier (object inflation is more to much more expensive depending on your columns and inflation types) solution you can also rewrite this–

my $hash_ref = $o->as_hashref; # does this exist?

–as–

my $hash_ref = { $o->get_columns };

It is not exactly what the OP asked for. The journaling system documents the changes, whereas the OP asked for the data that was changed.

Of course it is possible to reconstruct the previous contents by running the journal to just before the SQL that changed the data, but that would be quite cumbersome.

Of course the OP's request is also very naive, as (s)he assumes that all changes will be atomic on a single record. By simply "saving" the previous content into a history table, you are open to all kinds of race conditions, such as two connections editing different fields in the same record, which will make it dificult, if not imossible to determine what the "previous content" was.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Of course the OP's request is also very naive, as (s)he assumes that all changes will be atomic on a single record.

I am not sure I understand your point...

To clarify:

What I want is a way to save an object (representing a row) BEFORE I call any updating methods on it into another table (adding a few extra attibutes e.g user-information).

This will be done in the same transaction that will eventually commit the updates to the original row.

After that I have a "before-image" (the row at transaction start) and an "after-image" (the row after commit) in the database.

Which of my assumption here is naive?

An other strategy for historisation is to have an external process create a "snapshot" of your tables' content regularly. For instance, table T would be copied every night to T_archives, with the same structure plus a timestamp field.

This is usually easier to work with, as the archives contain everything needed, but can be inefficient on large tables that rarely change.

Although this is quite old, I just offer my solution which might be helpful for another future reader, as this one made me known with the Row->copy() and the Journal module. But none of both really helped me.

My problem was the following. Every row has a marker column to mark it as active, history rows are saved in the same table, every row has a timestamp. So I have to copy the current active row, set the active marker to 0 and insert a new copy with the current timestamp and active marker as 1. Here my shortened code:

my $upd_code_with_history = sub {
    my $record = $resultset->find(\%key);
    # save unchanged Data for new row
    my %data   = $record->get_columns;

    # modify the existing row
    $record->set_column( 'active_marker', 0);
    $record->update;

    # now update the saved row data which the changes
    foreach my $key(keys %values) {
        $data{$key} = $values{$key};
    }
    $data{'timestamp_col'} = \['CURRENT TIMESTAMP']; # DB2
    my $new_row = $resultset->create (\%data );      # insert
}; # ende Coderef $upd_code_with_history
[download]

This gives two SQL statements, which is fine as I have to change differnet columns in two rows.

My previous example was the easy one. It is a bit more complicated if you want to historize multiple rows with as little SQL statement as possible. Here DBIx::Class is missing a construct to build something like INSERT...SELECT... I am not deep enough into DBIx::Class, so my solution might not be the optimum, but works pretty well. So improvements are welcome.

my @colnames        = $result_source->columns;
my $upd_code_with_history = sub {
   # Step 1: make a copy of all active rows, but set them inactive
   # these become the history rows

   # Now build the "insert ... select" statement
   # First, build column list for INSERT
   my $insert_cols = join ',',@colnames;    

   # build SELECT col-list with  'is_active' = 0 as not active
   foreach my $col (@colnames) { 
      if ($col =~ /is_active$/) { 
         # replace column name with constant
         $col = '0';       # is reference, change in-place
         last;
      } 
   }

   # create the SQL Select part
   $key{is_active} = 1;     # select all active rows
   my $rsi = $resultset->search(\%key, {select => \@colnames });
   # as_query returns the generated SQL-Statemant and  @params 
   # in correct order.
   my ($select_sql,@params) = @{ ${$rsi->as_query} };
   my $table                = $rsi->result_source->from;

   # now put all tohether
   my $insert_sql = "INSERT INTO $table ($insert_cols) $select_sql";

   # This insert copies all '1' and replaces them with '0'            
   my @stuff = $db_schema->storage->dbh_do(
        sub {
           my ($storage, $dbh, $sql, @bndval) = @_;
           if ($self->config->{debug_sql}) {
               print STDERR $sql, ": '", join("', '", @bndval), "'\n";
           }
           $dbh->do($sql, undef, @bndval);
       },
       # see above for @param structure
       $insert_sql, map { $_->[1] } @params  
   );
            
   # Step 2: Update all '1' elements with current timestamp and values
   # This will be the current rows            
   # $key{elt_aktiv_mm} = 1;        # still set
   $values->{timestamp_col} = \['CURRENT TIMESTAMP'];

   # now update all '1'-rows with the values
   my $rss = $resultset->search(\%key)->update($values);
}; # end Coderef $upd_code
[download]

As said, I am not very perfect with DBIx::Clas, so there might be better solutions. This gives two SQL statement and is that way more performant than updating every row by itself. Hope it helps