Provide creating real unique constraints for columns

Main issue is that we can't create unique constraint for columns, because
we are using soft deletion of entries (set `deleted` column to True).

The main idea is to use `deleted` columns to create unique constraint for
columns. For example (`col1`, `deleted`).

To make (`col1`, `deleted`) unique after entry deletion, we should assign the
value of `id` to `deleted` column.

Change type of `deleted` column from Boolean to table.id.type for all tables.

Change models.soft_delete() method to assign table.id instead of True to
`deleted` column.

Change query.soft_delete() method to assign literal_column("id") instead of True

blueprint db-unique-keys

Change-Id: Ie1f67f49a5d085e6371efb63fc23a1c8b25d9464

2 files changed, 4 insertions, 4 deletions

diff --git a/openstack/common/db/sqlalchemy/models.py b/openstack/common/db/sqlalchemy/models.py
index e2eee28..f674da2 100644
--- a/openstack/common/db/sqlalchemy/models.py
+++ b/openstack/common/db/sqlalchemy/models.py
@@ -22,7 +22,7 @@
 SQLAlchemy models.
 """
 
-from sqlalchemy import Column, Boolean
+from sqlalchemy import Column, Integer
 from sqlalchemy import DateTime
 from sqlalchemy.orm import object_mapper
 
@@ -94,10 +94,10 @@ class ModelBase(object):
 
 class SoftDeleteMixin(object):
     deleted_at = Column(DateTime)
-    deleted = Column(Boolean, default=False)
+    deleted = Column(Integer, default=0)
 
     def soft_delete(self, session=None):
         """Mark this object as deleted."""
-        self.deleted = True
+        self.deleted = self.id
         self.deleted_at = timeutils.utcnow()
         self.save(session=session)
diff --git a/openstack/common/db/sqlalchemy/session.py b/openstack/common/db/sqlalchemy/session.py
index 06b198e..a796b92 100644
--- a/openstack/common/db/sqlalchemy/session.py
+++ b/openstack/common/db/sqlalchemy/session.py
@@ -577,7 +577,7 @@ def create_engine(sql_connection):
 class Query(sqlalchemy.orm.query.Query):
     """Subclass of sqlalchemy.query with soft_delete() method."""
     def soft_delete(self, synchronize_session='evaluate'):
-        return self.update({'deleted': True,
+        return self.update({'deleted': literal_column('id'),
                             'updated_at': literal_column('updated_at'),
                             'deleted_at': timeutils.utcnow()},
                            synchronize_session=synchronize_session)