Delete by setting deleted column to not null

(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