Re: Archiving data in a database

[cleverett]

The simplest way I can think of:

1. use 2 tables (assuming mysql here, adjust as necessary):

   create table d_table (
     d_table_id integer unsigned auto_increment primary key,
     table_name varchar(32) not null.
     d_happened timestamp not null,
     operation enum('insert','update','delete') not null,
     index (table_name, d_happened)
   );
   
   create table d_field (
     d_field_id integer unsigned auto_increment primary key,
     d_table_id integer unsigned not null,
     field_name varchar(32) not null,
     old_value text not null,
     unique index (d_table_id, field_name)
   );
   

2. For table inserts, simply note the insert:

   $dbh->do("INSERT INTO d_table table_name = ?, operation = 'insert'", $
   +table_name);
   

3. For table updates and deletes, run something like:

   my $old_data = $dbh->selectrow_hashref("select * from 
   table_to_change where primary_key_id = ?", $table_row_id);
   
   ## update the table
   
   $dbh->do("INSERT INTO d_table table_name = ?, operation = 'insert'", $
   +table_name);
   
   my $d_table_id = $dbh->selectrow_array("select last_insert_id()");
   
   foreach my $f (@updated_field_names) {
     $dbh->do("INSERT INTO d_field (d_table_id, field_name, old_value) VA
   +LUES (?, ?, ?)",
              $d_table_id, $f, $old_data->{$f});
   
   }
   

   I leave the changes needed for a delete as an exercise to the dear reader :)

Using this general scheme you should find it fairly easy to recapture the state of your database at any time during its existence.

Notes:

• Though I believe in the general soundness of the above, a bunch of situations exist that could thoroughly ream you, unless you extend the model to cover them:
  • a mysql "ALTER TABLE foo AUTO_INCREMENT = 0", which will reset your row numbers (you can handle this by tracking row numbers for your operations though).
  • Global operations will cost you bigtime, but again you can extend this model to turn them into single operations by using an enum field as a flag.
• In your shoes, I would be using $dbh->begin_work(), $dbh->commit() and $dbh->rollback() from the Perl DBI library to keep the d_table and d_field tables synced with the rest of your database.
• If you have to do extensive coding with this discipline, create a subclass of the Class::DBI library to automate the changes to the d_table and d_field tables, instead of coding everything by hand.