Fix DB migration with postgresql

Check if a table already exists before creating it. This prevents the errors:

INFO migrate.versioning.api [designate-manage - - - - -] 69 -> 70...
CRITICAL designate [designate-manage - - - - -] ProgrammingError: \
         (psycopg2.ProgrammingError) type "resource_statuses" already exists

INFO migrate.versioning.api [designate-manage - - - - -] 85 -> 86...
CRITICAL designate [designate-manage - - - - -]
         ProgrammingError: (psycopg2.ProgrammingError) relation
         "unique_pool_host_port" already exists

INFO migrate.versioning.api [designate-manage - - - - -] 96 -> 97...
CRITICAL designate [designate-manage - - - - -]
         ProgrammingError: (psycopg2.ProgrammingError) type "service_statuses"
         already exists

ERROR designate ProgrammingError: (psycopg2.ProgrammingError) type
      "service_statuses" already exists
ERROR designate
      HINT:  A relation has an associated type of the same name, so you must
      use a name that doesn't conflict with any existing type.

when running the migration with postgresql.

Closes-Bug: #1640453
Change-Id: I3e8928c1b92d5c42e634abb992a4d931c44be01b
(cherry picked from commit 3a8452c65d7b89d4d5b1b87fba63a84239a2b342)

5 files changed, 31 insertions, 22 deletions

diff --git a/designate/sqlalchemy/base.py b/designate/sqlalchemy/base.py
index 747dca1a..17ad355b 100644
--- a/designate/sqlalchemy/base.py
+++ b/designate/sqlalchemy/base.py
@@ -306,7 +306,8 @@ class SQLAlchemy(object):
             select_from(rzjoin).where(zones_table.c.deleted == '0')
 
         if index_hint:
-            inner_q = inner_q.with_hint(recordsets_table, index_hint)
+            inner_q = inner_q.with_hint(recordsets_table, index_hint,
+                                        dialect_name='mysql')
 
         if marker is not None:
             marker = utils.check_marker(recordsets_table, marker,
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/070_liberty.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/070_liberty.py
index 6a20fbcf..748ca8da 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/070_liberty.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/070_liberty.py
@@ -94,9 +94,10 @@ domains = Table('domains', metadata,
            server_default='0'),
     Column('deleted_at', DateTime, nullable=True, default=None),
     Column('description', Unicode(160), nullable=True),
-    Column('status', Enum(name='resource_statuses', *RESOURCE_STATUSES),
+    Column('status', Enum(name='domains_resource_statuses',
+                          *RESOURCE_STATUSES),
            nullable=False, server_default='PENDING', default='PENDING'),
-    Column('action', Enum(name='actions', *ACTIONS),
+    Column('action', Enum(name='domain_actions', *ACTIONS),
            default='CREATE', server_default='CREATE', nullable=False),
     Column('pool_id', UUID, default=None, nullable=True),
     Column('reverse_name', String(255), nullable=False, server_default=''),
@@ -177,7 +178,7 @@ records = Table('records', metadata,
     Column('managed_plugin_name', Unicode(50), default=None, nullable=True),
     Column('hash', String(32), nullable=False),
     Column('description', Unicode(160), nullable=True),
-    Column('status', Enum(name='resource_statuses', *RESOURCE_STATUSES),
+    Column('status', Enum(name='record_resource_statuses', *RESOURCE_STATUSES),
            server_default='PENDING', default='PENDING', nullable=False),
     Column('tenant_id', String(36), default=None, nullable=True),
     Column('recordset_id', UUID, nullable=False),
@@ -185,7 +186,7 @@ records = Table('records', metadata,
     Column('managed_resource_region', Unicode(100), default=None,
            nullable=True),
     Column('managed_extra', Unicode(100), default=None, nullable=True),
-    Column('action', Enum(name='actions', *ACTIONS),
+    Column('action', Enum(name='record_actions', *ACTIONS),
            default='CREATE', server_default='CREATE', nullable=False),
     Column('serial', Integer(), server_default='1', nullable=False),
     Column('domain_shard', SmallInteger(), nullable=False),
@@ -304,7 +305,8 @@ zone_transfer_requests = Table('zone_transfer_requests', metadata,
     Column("description", String(255)),
     Column("tenant_id", String(36), default=None, nullable=False),
     Column("target_tenant_id", String(36), default=None, nullable=True),
-    Column("status", Enum(name='resource_statuses', *TASK_STATUSES),
+    Column("status", Enum(name='zone_transfer_requests_resource_statuses',
+                          *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
 
@@ -323,7 +325,8 @@ zone_transfer_accepts = Table('zone_transfer_accepts', metadata,
     Column('domain_id', UUID, nullable=False),
     Column('zone_transfer_request_id', UUID, nullable=False),
     Column("tenant_id", String(36), default=None, nullable=False),
-    Column("status", Enum(name='resource_statuses', *TASK_STATUSES),
+    Column("status", Enum(name='zone_transfer_accepts_resource_statuses',
+                          *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
 
@@ -348,7 +351,7 @@ zone_tasks = Table('zone_tasks', metadata,
     Column('task_type', Enum(name='task_types', *ZONE_TASK_TYPES),
            nullable=True),
     Column('message', String(160), nullable=True),
-    Column('status', Enum(name='resource_statuses', *TASK_STATUSES),
+    Column('status', Enum(name='zone_tasks_resource_statuses', *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
     Column('location', String(160), nullable=True),
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/086_new_pools_tables.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/086_new_pools_tables.py
index 602a1143..039eee2e 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/086_new_pools_tables.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/086_new_pools_tables.py
@@ -106,7 +106,8 @@ pool_also_notifies = Table('pool_also_notifies', meta,
     Column('port', Integer(), nullable=False),
 
     ForeignKeyConstraint(['pool_id'], ['pools.id'], ondelete='CASCADE'),
-    UniqueConstraint('pool_id', 'host', 'port', name='unique_pool_host_port'),
+    UniqueConstraint('pool_id', 'host', 'port',
+                     name='unique_pool_also_notifies_pool0host0port'),
 
     mysql_engine='InnoDB',
     mysql_charset='utf8',
@@ -119,8 +120,8 @@ def upgrade(migrate_engine):
     # Load the pool_attributes_table table schema for relations
     Table('pools', meta, autoload=True)
 
-    pool_nameservers.create()
-    pool_targets.create()
-    pool_target_options.create()
-    pool_target_masters.create()
-    pool_also_notifies.create()
+    pool_nameservers.create(checkfirst=True)
+    pool_targets.create(checkfirst=True)
+    pool_target_options.create(checkfirst=True)
+    pool_target_masters.create(checkfirst=True)
+    pool_also_notifies.create(checkfirst=True)
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/097_add_services.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/097_add_services.py
index 97b41542..29895738 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/097_add_services.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/097_add_services.py
@@ -34,8 +34,9 @@ SERVICE_STATES = [
 def upgrade(migrate_engine):
     meta.bind = migrate_engine
 
-    status_enum = Enum(name='service_statuses', metadata=meta, *SERVICE_STATES)
-    status_enum.create()
+    status_enum = Enum(name='service_statuses_enum', metadata=meta,
+                       *SERVICE_STATES)
+    status_enum.create(checkfirst=True)
 
     service_status_table = Table('service_statuses', meta,
         Column('id', UUID(), default=utils.generate_uuid, primary_key=True),
@@ -49,4 +50,4 @@ def upgrade(migrate_engine):
         Column('stats', Text, nullable=False),
         Column('capabilities', Text, nullable=False),
     )
-    service_status_table.create()
+    service_status_table.create(checkfirst=True)
diff --git a/designate/storage/impl_sqlalchemy/tables.py b/designate/storage/impl_sqlalchemy/tables.py
index 0cd00303..259a8589 100644
--- a/designate/storage/impl_sqlalchemy/tables.py
+++ b/designate/storage/impl_sqlalchemy/tables.py
@@ -128,7 +128,7 @@ zones = Table('zones', metadata,
     Column('expire', Integer, default=CONF.default_soa_expire, nullable=False),
     Column('minimum', Integer, default=CONF.default_soa_minimum,
            nullable=False),
-    Column('status', Enum(name='resource_statuses', *RESOURCE_STATUSES),
+    Column('status', Enum(name='zones_resource_statuses', *RESOURCE_STATUSES),
            nullable=False, server_default='PENDING', default='PENDING'),
     Column('parent_zone_id', UUID, default=None, nullable=True),
     Column('action', Enum(name='actions', *ACTIONS),
@@ -226,7 +226,8 @@ records = Table('records', metadata,
            nullable=True),
     Column('managed_resource_id', UUID, default=None, nullable=True),
     Column('managed_tenant_id', String(36), default=None, nullable=True),
-    Column('status', Enum(name='resource_statuses', *RESOURCE_STATUSES),
+    Column('status', Enum(name='records_resource_statuses',
+                          *RESOURCE_STATUSES),
            server_default='PENDING', default='PENDING', nullable=False),
     Column('action', Enum(name='actions', *ACTIONS),
            default='CREATE', server_default='CREATE', nullable=False),
@@ -420,7 +421,8 @@ zone_transfer_requests = Table('zone_transfer_requests', metadata,
     Column("description", String(255), nullable=False),
     Column("tenant_id", String(36), default=None, nullable=False),
     Column("target_tenant_id", String(36), default=None, nullable=True),
-    Column("status", Enum(name='resource_statuses', *TASK_STATUSES),
+    Column("status", Enum(name='zone_transfer_requests_resource_statuses',
+                          *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
 
@@ -439,7 +441,8 @@ zone_transfer_accepts = Table('zone_transfer_accepts', metadata,
     Column('zone_id', UUID, nullable=False),
     Column('zone_transfer_request_id', UUID, nullable=False),
     Column("tenant_id", String(36), default=None, nullable=False),
-    Column("status", Enum(name='resource_statuses', *TASK_STATUSES),
+    Column("status", Enum(name='zone_transfer_accepts_resource_statuses',
+                          *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
 
@@ -464,7 +467,7 @@ zone_tasks = Table('zone_tasks', metadata,
     Column('task_type', Enum(name='task_types', *ZONE_TASK_TYPES),
            nullable=True),
     Column('message', String(160), nullable=True),
-    Column('status', Enum(name='resource_statuses', *TASK_STATUSES),
+    Column('status', Enum(name='zone_tasks_resource_statuses', *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',
            default='ACTIVE'),
     Column('location', String(160), nullable=True),