Jay, Don't worry I investigate this question very well. There are actually two approaches:
1) Use deleted_at to create Unique Constraints. But then we are not able to store in deleted_at NONE value, because it won't work e.g. We have table for Users (user_name, deleted_at, deleted), and we won't to make user_name Unique, then if we just add UC to (user_name, deleted_at) in MySql we will get next behavior: (user1, NONE) and (user1, NONE) are different and could be stored in DB because NONE != NONE in mysql. So to solve this thing we have to add some base VALUE instead of NONE (e.g. 1.1.1970) But this is really dirty thing and produce a lot of hacks. 2) Use deleted column So change type of deleted column to ID type. Use 0 or "" as a base value, and store value of ID in deleted column on deletion (which is really UNIQUE) and use UC as (column1, column2, deleted) So I think that second variant is much cleaner then first. Best regards, Boris Pavlovic --- Mirantis Inc. On Tue, Aug 20, 2013 at 6:33 PM, Jay Pipes <[email protected]> wrote: > *sigh* I wish I'd been aware of these conversations and been in the > Grizzly summit session on soft delete... > > What specific unique constraint was needed that changing the deleted > column to use the id value solved? > > -jay > > > On 08/19/2013 03:56 AM, Chris Behrens wrote: > >> 'deleted' is used so that we can have proper unique constraints by >> setting it to `id` on deletion. This was not the case until Grizzly, and >> before Grizzly I would have agreed completely. >> >> - Chris >> >> On Aug 19, 2013, at 12:39 AM, Jay Pipes <[email protected]> wrote: >> >> I'm throwing this up here to get some feedback on something that's >>> always bugged me about the model base used in many of the projects. >>> >>> There's a mixin class that looks like so: >>> >>> class SoftDeleteMixin(object): >>> deleted_at = Column(DateTime) >>> deleted = Column(Integer, default=0) >>> >>> def soft_delete(self, session=None): >>> """Mark this object as deleted.""" >>> self.deleted = self.id >>> self.deleted_at = timeutils.utcnow() >>> self.save(session=session) >>> >>> Once mixed in to a concrete model class, the primary join is typically >>> modified to include the deleted column, like so: >>> >>> class ComputeNode(BASE, NovaBase): >>> <snip>... >>> service = relationship(Service, >>> backref=backref('compute_node'**), >>> foreign_keys=service_id, >>> primaryjoin='and_(' >>> 'ComputeNode.service_id == Service.id,' >>> 'ComputeNode.deleted == 0)') >>> >>> My proposal is to get rid of the deleted column in the SoftDeleteMixin >>> class entirely, as it is redundant with the deleted_at column. Instead of >>> doing a join condition on deleted == 0, one would instead just do the join >>> condition on deleted_at is None, which translates to the SQL: AND >>> deleted_at IS NULL. >>> >>> There isn't much of a performance benefit -- you're only reducing the >>> row size by 4 bytes. But, you'd remove the redundant data from all the >>> tables, which would make the normal form freaks like myself happy ;) >>> >>> Thoughts? >>> >>> -jay >>> >>> ______________________________**_________________ >>> OpenStack-dev mailing list >>> [email protected].**org <[email protected]> >>> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev> >>> >> >> >> ______________________________**_________________ >> OpenStack-dev mailing list >> [email protected].**org <[email protected]> >> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev> >> >> > > ______________________________**_________________ > OpenStack-dev mailing list > [email protected].**org <[email protected]> > http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev> >
_______________________________________________ OpenStack-dev mailing list [email protected] http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
