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 <jaypi...@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 <jaypi...@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@lists.openstack.org
>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
>>
>> _______________________________________________
>> OpenStack-dev mailing list
>> OpenStack-dev@lists.openstack.org
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
>
>
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev@lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
