On 16 Feb 2009, at 23:23, Darren Duncan wrote:
fREW Schmidt wrote:
I am sure there is a better term for this, I just don't know what
it is. I would like for my database to have some type of removal
field that would effectively mark a row as deleted without deleting
it. I've seen ORMs that do this before and I was wondering if DBIC
did something or had support for something like this.
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.
Following up on other replies you got ...
What I would do is something a little more generic, which is often
called auditing, and often makes use of a single extra table per
normal table.
So the normal table holds only the current versions of live records
and no deleted records, and the audit table has copies of all the
records that used to exist as well as prior versions of changed
records, and often for completeness, also a duplicate of all
inserted and current records. Note that a record update could also
be conceptually a delete followed by an insert.
The point is that if your reason for a soft delete is to provide a
way to undo something destructive, then a delete isn't the only
common destructive action, but an update is too (and sometimes, so
is an insert).
When I have done this before, generally only the live table had
unique keys etc on it as usual, and the corresponding audit table
did not.
In one form, an audit table for table <foo> might have a name like
"<foo>_audit", or pick some other suffix you don't use elsewhere,
unless your database supports multiple schemas/namespaces in which
case you might use the same name in a different schema.
Also if you want to easily associate changes to multiple tables as
an atomic database change, I also use a separate master table called
audit_change_set, which at least has fields audit_change_set_id (a
generated ordered unique id) and audit_date; if you track other
things like who made the change that can be extra fields here.
Then in each table's audit table there are all the fields in the
normal table plus 2 more, audit_change_set_id and audit_action_type;
the latter containing one of [I,U,D]. For 'I' and 'U' the other
fields are a copy of the record post insert/update, and for 'D' the
other fields are a copy prior to delete; season to taste.
The audit tables are populated effectively by triggers on the main
tables. Other than that, your normal code can just interact with the
main tables without knowing about the added complexity of the audit,
since no structure changes exist in the main tables.
Using the audit tables to either display history or to recover the
database or individual records to an earlier state is left as an
exercise to the reader.
Now I'm sure there are other good solutions, but this is what I did
on the last project I had that did change tracking for recovery or
'history' queries.
I also expect to make some changes to that design on my next
project, probably making the structure more resemble a version
control system like Git, where the database has versions and each
one knows what its parent versions are. Now this isn't useful for
everyone, but for my application it would be, which involves people
making requests for data changes and others approving or rejecting
those changes for public consumption possibly with edits.
-- Darren Duncan
Not sure if someone has mentioned this already or not, but http://search.cpan.org/~nuffin/DBIx-Class-Journal-0.900001_02/
does some/most of the things you suggest.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
