[Dbix-class] "soft deletion"

[David Ihnen]

fREW Schmidt wrote:
> To make myself more clear, I'd like either a binary flag or a date 
> which would probably be called deleted or date_deleted or is_deleted 
> (feel free to give input in this) and I'd like it not to show up in 
> regular resultsets unless I explicitly say I want to see the deleted rows.
>
> Thoughts?
You want to abstract the data store from the data view.

The ability to do that layer is provided in some DBs, at a cost of overhead.

Rename the table x to x_raw

Create a view x for normal access that filters based on the field.

Trigger in the db on delete from x that sets the field on x_raw 
appropriately

Trigger in the db on insert to x that inserts into x_raw

Trigger on the db on update to x that updates x_raw

Modify any of your indexes to include the column you're filtering on all 
the time for efficiency.

Recalculate your unique keys, make sure they only apply to rows where 
the deleted field is unset.  If possible.  Otherwise, you have a problem.

You may have a deleted row that you cannot recover as it would conflict 
with another otherwise unique row.  Depending on your application and keys.

OR

Otherwise add a layer between the actual physical data store and your 
application that enforces this rule.

David