Delete by setting deleted column to not null

From ClassDBI

(This is just a copy of something that Tony posted on the list a few months back, but I wanted to have it here for reference.)

It seems that various people use a trick for "deleting" data from their tables when they want to keep the data around for possible restoration or approval of the delete, etc. Instead of actually deleting a row, you have a column called "deleted" that is (say) a timestamp, and defaults to NULL. When you want to "delete" the row, you simply set "deleted" to a not-NULL value--in !MySQL, something like "UPDATE table SET deleted = NOW() WHERE id=$id". Then, all your SELECT queries should have appended to the end of the WHERE clause, "AND deleted IS NULL".

(Naturally this technique is not well suited to extremely large and delete-heavy tables.)

In CDBI, you can accomplish this by overriding a few methods, like so, and you'll get this functionality in a perfectly transparent fashion. And if you want to work with the "deleted" rows in any way, you can have a separate subclass that doesn't have these overrides. This has worked well for me in the past. Just add to a subclass:

__PACKAGE__->set_sql(Retrieve => <<);
       SELECT __ESSENTIAL__
       FROM   __TABLE__
       WHERE  DELETED IS NULL
       AND    %s

__PACKAGE__->set_sql(RetrieveAll => <<);
       SELECT __ESSENTIAL__
       FROM   __TABLE__
       WHERE  DELETED IS NULL

__PACKAGE__->set_sql(DeleteMe => <<"");
      UPDATE __TABLE__
      SET deleted = NOW()
      WHERE  __IDENTIFIER__

et voila!

(Tony warns that this may not be stable for the long-term, but some similar thing is likely to be easy to do in the future.)

---

If you need to *really* delete a record or retrieve records that have been marked deleted, I've had success creating a real_delete function in my class based on the delete subroutine from ClassDBI and then using the retrieve_from_sql() function to retrieve all rows. I hope others add their notes as the techniques below are hacks!

Here's an example of using retrieve_from_sql to get deleted records:

 my $user = DBI::Users->retrieve_from_sql(qq{id = $id OR (id = $id AND deleted is not null)})->first;

I'm not sure if it is possible to use placeholders in the SQL statement passed to retrieve_from_sql so be sure to check the values with what you pass in. The WHERE clause defined in the retrieve_from_sql method gets appended to the one created in the set_sql statement which is why it is necessary to add the OR clause to include records where deleted is not null.

This can be wrapped into a method called real_retrieve in you CDBI subclass as follows:

 # Usage:
 #   my $user = QT::DBI::Users->real_retrieve($id);
 sub real_retrieve {
   my $class = shift;
   my $id = shift;
 
   return $class->retrieve_from_sql(qq{id = $id OR (dtime IS NOT NULL AND id = $id)})->first;
 }

Here is a sample of a real_delete function (not tested with the set_sql statements above):

 sub real_delete {
   my $class = shift || die "Cannot be called as a class method";
 
   $class->call_trigger('before_delete');
 
   eval {
     my $dbh = $class->db_Main();
     my $sql = qq~
       DELETE FROM users
       WHERE id = ?
       ~;
     my $sth = $dbh->prepare_cached($sql);
     $sth->execute($class->id);
     $sth->finish;
   };
   if ($@) {
     return $class->_croak("Can't delete $class $@", err => $@);
   }
   $class->call_trigger('after_delete');
   undef %$class;
   bless $class, 'Class::DBI::Object::Has::Been::Deleted';
   return 1;
 }

If your class has any relationships (e.g., has_a, has_many, etc.), you will want to override the default delete() method to comment out the triggers in order to prevent it from deleting the related records. Warning: This could have a significant impact on your module! YMMV