<div dir="ltr">Jay, <div><br></div><div>Don't worry I investigate this question very well.</div><div>There are actually two approaches: </div><div><br></div><div>1) Use deleted_at to create Unique Constraints.</div><div>
<br></div><div>But then we are not able to store in deleted_at NONE value, because it won't work</div><div>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: </div>
<div>(user1, NONE) and (user1, NONE) are different and could be stored in DB because NONE != NONE in mysql. </div><div><br></div><div>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. </div>
<div><br></div><div><br></div><div>2) Use deleted column</div><div><br></div><div>So change type of deleted column to ID type. </div><div>Use 0 or "" as a base value, and store value of ID in deleted column on deletion (which is really UNIQUE)</div>
<div><br></div><div>and use UC as (column1, column2, deleted)</div><div><br></div><div><br></div><div>So I think that second variant is much cleaner then first. </div><div><br></div><div><br></div><div>Best regards,</div>
<div>Boris Pavlovic</div><div>---</div><div>Mirantis Inc. </div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Aug 20, 2013 at 6:33 PM, Jay Pipes <span dir="ltr"><<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">*sigh* I wish I'd been aware of these conversations and been in the Grizzly summit session on soft delete...<br>
<br>
What specific unique constraint was needed that changing the deleted column to use the id value solved?<span class="HOEnZb"><font color="#888888"><br>
<br>
-jay</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
On 08/19/2013 03:56 AM, Chris Behrens wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
'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.<br>
<br>
- Chris<br>
<br>
On Aug 19, 2013, at 12:39 AM, Jay Pipes <<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>> wrote:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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.<br>
<br>
There's a mixin class that looks like so:<br>
<br>
class SoftDeleteMixin(object):<br>
deleted_at = Column(DateTime)<br>
deleted = Column(Integer, default=0)<br>
<br>
def soft_delete(self, session=None):<br>
"""Mark this object as deleted."""<br>
self.deleted = <a href="http://self.id" target="_blank">self.id</a><br>
self.deleted_at = timeutils.utcnow()<br>
self.save(session=session)<br>
<br>
Once mixed in to a concrete model class, the primary join is typically modified to include the deleted column, like so:<br>
<br>
class ComputeNode(BASE, NovaBase):<br>
<snip>...<br>
service = relationship(Service,<br>
backref=backref('compute_node'<u></u>),<br>
foreign_keys=service_id,<br>
primaryjoin='and_('<br>
'ComputeNode.service_id == Service.id,'<br>
'ComputeNode.deleted == 0)')<br>
<br>
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.<br>
<br>
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 ;)<br>
<br>
Thoughts?<br>
<br>
-jay<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</blockquote>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
<br>
</blockquote>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</div></div></blockquote></div><br></div>