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
_______________________________________________
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]
