Open Stack

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 <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 <OpenStack-dev at lists.openstack.org>
>>> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>>>
>>
>>
>> ______________________________**_________________
>> OpenStack-dev mailing list
>> OpenStack-dev at lists.openstack.**org <OpenStack-dev at lists.openstack.org>
>> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>>
>>
>
> ______________________________**_________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.**org <OpenStack-dev at lists.openstack.org>
> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130820/1498aa78/attachment.html>