Open Stack

This is kind of a stupid example, but it makes the point:

For instances table, we want to make sure 'uuid' is unique.  But we can't put a unique constraint on that alone.  If that instance gets deleted.. we should be able to create another entry with the same uuid without a problem.  So we need a unique constraint on uuid+deleted.  But if 'deleted' is only 0 or 1… we can only have 1 entry deleted and 1 entry not deleted.  Using deleted=`id` to mark deletion solves that problem.  You could use deleted_at… but 2 creates and deletes within the same second would not work. :)

- Chris


On Aug 20, 2013, at 7:33 AM, Jay Pipes <jaypipes at gmail.com> 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 <jaypipes at gmail.com> 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
>>> OpenStack-dev at lists.openstack.org
>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>> 
>> 
>> _______________________________________________
>> OpenStack-dev mailing list
>> OpenStack-dev at lists.openstack.org
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>> 
> 
> 
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev