Fix support for PostgreSQL

https://review.openstack.org/152337 - This is a reference for dropping support
for downgrade() in migrations that add values to enums since it's hard to
delete values from a enum type in postgres.

Change-Id: I61e86935b508986470447f02fa2f4b6c9d9f8afd

8 files changed, 123 insertions, 123 deletions

diff --git a/contrib/devstack/lib/designate_plugins/backend-powerdns b/contrib/devstack/lib/designate_plugins/backend-powerdns
index cf99ad17..3b26ec61 100644
--- a/contrib/devstack/lib/designate_plugins/backend-powerdns
+++ b/contrib/devstack/lib/designate_plugins/backend-powerdns
@@ -40,7 +40,15 @@ function install_designate_backend {
         PDNS=pdns-server
     fi
 
-    install_package $PDNS pdns-backend-mysql
+    if is_service_enabled mysql; then
+        PDNS+=" pdns-backend-mysql"
+    elif is_service_enabled postgresql; then
+        PDNS+=" pdns-backend-pgsql"
+    else
+        die $LINENO "PowerDNS backend only supports MySQL / PostgreSQL"
+    fi
+
+    install_package $PDNS
     sudo rm -rf $POWERDNS_CFG_DIR/pdns.d
 }
 
@@ -85,8 +93,20 @@ gmysql-password=$DATABASE_PASSWORD
 gmysql-dbname=designate_pdns
 gmysql-dnssec=yes
 EOF
+    elif is_service_enabled postgresql; then
+        sudo tee -a $POWERDNS_CFG_DIR/pdns.conf > /dev/null <<EOF
+# Launch gpgsql backend
+launch=gpgsql
+
+# gmysql parameters
+gpgsql-host=$DATABASE_HOST
+gpgsql-user=$DATABASE_USER
+gpgsql-password=$DATABASE_PASSWORD
+gpgsql-dbname=designate_pdns
+gpgsql-dnssec=yes
+EOF
     else
-        die $LINENO "PowerDNS backend only supports MySQL"
+        die $LINENO "PowerDNS backend only supports MySQL / PostgreSQL"
     fi
 
     restart_service pdns
@@ -94,6 +114,12 @@ EOF
 
 # init_designate_backend - initialize databases, etc.
 function init_designate_backend {
+    # Stop pdns so that the migration succeeds, if not you get a error
+    # that the schema is still in use.
+    if is_service_enabled postgresql; then
+        stop_designate_backend
+    fi
+
     # (Re)create designate_pdns database
     recreate_database designate_pdns utf8
 
diff --git a/designate/backend/impl_powerdns/migrate_repo/versions/010_records_add_disabled_column.py b/designate/backend/impl_powerdns/migrate_repo/versions/010_records_add_disabled_column.py
index bf46ca49..5d59b6dc 100644
--- a/designate/backend/impl_powerdns/migrate_repo/versions/010_records_add_disabled_column.py
+++ b/designate/backend/impl_powerdns/migrate_repo/versions/010_records_add_disabled_column.py
@@ -13,9 +13,7 @@
 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 # License for the specific language governing permissions and limitations
 # under the License.
-from sqlalchemy import MetaData, Table, Column
-
-from sqlalchemy.dialects.mysql import TINYINT
+from sqlalchemy import MetaData, Table, Column, Boolean
 
 
 meta = MetaData()
@@ -26,7 +24,7 @@ def upgrade(migrate_engine):
 
     records_table = Table('records', meta, autoload=True)
 
-    disabled = Column('disabled', TINYINT(1), server_default='0')
+    disabled = Column('disabled', Boolean(), server_default='0')
     disabled.create(records_table)
 
 
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/039_support_soa_records.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/039_support_soa_records.py
index cf4287b6..58a55bbf 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/039_support_soa_records.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/039_support_soa_records.py
@@ -54,6 +54,16 @@ def upgrade(migrate_engine):
                     'PTR', 'SSHFP', 'SOA']
 
     recordsets_table = Table('recordsets', meta, autoload=True)
+
+    dialect = migrate_engine.url.get_dialect().name
+    if dialect.startswith("postgresql"):
+        with migrate_engine.connect() as conn:
+            conn.execution_options(isolation_level="AUTOCOMMIT")
+            conn.execute(
+                "ALTER TYPE recordset_types ADD VALUE 'SOA' "
+                "AFTER 'SSHFP'")
+            conn.close()
+
     recordsets_table.c.type.alter(type=Enum(name='recordset_types',
                                             *RECORD_TYPES))
 
@@ -155,55 +165,4 @@ def upgrade(migrate_engine):
 
 
 def downgrade(migrate_engine):
-    meta.bind = migrate_engine
-    dialect = migrate_engine.url.get_dialect().name
-    zones_table = Table('domains', meta, autoload=True)
-    records_table = Table('records', meta, autoload=True)
-
-    RECORD_TYPES = ['A', 'AAAA', 'CNAME', 'MX', 'SRV', 'TXT', 'SPF', 'NS',
-                    'PTR', 'SSHFP']
-
-    recordsets_table = Table('recordsets', meta, autoload=True)
-
-    # Delete all SOA records
-    recordsets_table.delete().where(recordsets_table.c.type == 'SOA').execute()
-
-    # Remove SOA from the ENUM
-    recordsets_table.c.type.alter(type=Enum(name='recordset_types',
-                                            *RECORD_TYPES))
-
-    # Remove non-delegated NS records
-    # Get all the zones
-    zones = select(
-        columns=[
-            zones_table.c.id,
-            zones_table.c.created_at,
-            zones_table.c.tenant_id,
-            zones_table.c.name,
-            zones_table.c.email,
-            zones_table.c.serial,
-            zones_table.c.refresh,
-            zones_table.c.retry,
-            zones_table.c.expire,
-            zones_table.c.minimum
-        ]
-    ).execute().fetchall()
-
-    for zone in zones:
-        # for each zone, get all non-delegated NS recordsets
-        results = recordsets_table.select().\
-            where(recordsets_table.c.type == 'NS').\
-            where(recordsets_table.c.name == zone.name).execute()
-        for r in results:
-            records_table.delete().\
-                where(records_table.c.recordset_id == r.id).\
-                where(records_table.c.managed == 1).execute()
-        # NOTE: The value 1 is used instead of True because flake8 complains
-
-    # Re-add the constraint for sqlite
-    if dialect.startswith('sqlite'):
-        constraint = UniqueConstraint('domain_id', 'name', 'type',
-                                      name='unique_recordset',
-                                      table=recordsets_table)
-
-        constraint.create()
+    pass
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/043_modify_domains_and_records.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/043_modify_domains_and_records.py
index 11f06f9d..d4270714 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/043_modify_domains_and_records.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/043_modify_domains_and_records.py
@@ -18,38 +18,68 @@ from migrate.changeset.constraint import UniqueConstraint
 
 meta = MetaData()
 
+ACTIONS = ['CREATE', 'DELETE', 'UPDATE', 'NONE']
+
 
 def upgrade(migrate_engine):
     meta.bind = migrate_engine
 
     RESOURCE_STATUSES = ['ACTIVE', 'PENDING', 'DELETED', 'ERROR']
-    ACTIONS = ['CREATE', 'DELETE', 'UPDATE', 'NONE']
 
     # Get associated database tables
     domains_table = Table('domains', meta, autoload=True)
     records_table = Table('records', meta, autoload=True)
 
+    dialect = migrate_engine.url.get_dialect().name
+    if dialect.startswith("postgresql"):
+        migrate_engine.execute(
+            "ALTER TYPE domain_statuses RENAME TO resource_statuses;")
+
+        with migrate_engine.connect() as conn:
+            conn.execution_options(isolation_level="AUTOCOMMIT")
+            conn.execute(
+                "ALTER TYPE resource_statuses ADD VALUE 'ERROR' "
+                "AFTER 'DELETED'")
+            conn.close()
+
+    actions = Enum(name='actions', metadata=meta, *ACTIONS)
+    actions.create()
+
+    resource_statuses = Enum(name='resource_statuses', metadata=meta,
+                             *RESOURCE_STATUSES)
+
     # Upgrade the domains table.
     domains_table.c.status.alter(
-        type=Enum(name='resource_statuses', *RESOURCE_STATUSES),
+        type=resource_statuses,
         default='PENDING', server_default='PENDING')
-    action_column = Column('action', Enum(name='actions', *ACTIONS),
+
+    action_column = Column('action', actions,
                            default='CREATE', server_default='CREATE',
                            nullable=False)
     action_column.create(domains_table)
 
     # Re-add constraint for sqlite.
-    dialect = migrate_engine.url.get_dialect().name
     if dialect.startswith('sqlite'):
         constraint = UniqueConstraint(
             'name', 'deleted', name='unique_domain_name', table=domains_table)
         constraint.create()
 
     # Upgrade the records table.
-    records_table.c.status.alter(
-        type=Enum(name='resource_statuses', *RESOURCE_STATUSES),
-        default='PENDING', server_default='PENDING')
-    action_column = Column('action', Enum(name='actions', *ACTIONS),
+    if dialect.startswith("postgresql"):
+        sql = "ALTER TABLE records ALTER COLUMN status DROP DEFAULT, " \
+              "ALTER COLUMN status TYPE resource_statuses USING " \
+              "records::text::resource_statuses, ALTER COLUMN status " \
+              "SET DEFAULT 'PENDING';"
+        migrate_engine.execute(sql)
+        record_statuses = Enum(name='record_statuses', metadata=meta,
+                               *RESOURCE_STATUSES)
+        record_statuses.drop()
+    else:
+        records_table.c.status.alter(
+            type=resource_statuses,
+            default='PENDING', server_default='PENDING')
+
+    action_column = Column('action', actions,
                            default='CREATE', server_default='CREATE',
                            nullable=False)
     action_column.create(records_table)
@@ -65,36 +95,4 @@ def upgrade(migrate_engine):
 
 
 def downgrade(migrate_engine):
-    meta.bind = migrate_engine
-
-    RESOURCE_STATUSES = ['ACTIVE', 'PENDING', 'DELETED']
-
-    # Get associated database tables
-    domains_table = Table('domains', meta, autoload=True)
-    records_table = Table('records', meta, autoload=True)
-
-    # Downgrade the domains table.
-    domains_table.c.status.alter(
-        type=Enum(name='resource_statuses', *RESOURCE_STATUSES),
-        default='ACTIVE', server_default='ACTIVE')
-    domains_table.c.action.drop()
-
-    # Re-add constraint for sqlite.
-    dialect = migrate_engine.url.get_dialect().name
-    if dialect.startswith('sqlite'):
-        constraint = UniqueConstraint(
-            'name', 'deleted', name='unique_domain_name', table=domains_table)
-        constraint.create()
-
-    # Downgrade the records table.
-    records_table.c.status.alter(
-        type=Enum(name='resource_statuses', *RESOURCE_STATUSES),
-        default='ACTIVE', server_default='ACTIVE')
-    records_table.c.action.drop()
-    records_table.c.serial.drop()
-
-    # Re-add constraint for sqlite.
-    if dialect.startswith('sqlite'):
-        constraint = UniqueConstraint(
-            'hash', name='unique_record', table=records_table)
-        constraint.create()
+    pass
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/048_add_zone_ownership_transfers_tables.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/048_add_zone_ownership_transfers_tables.py
index add6412f..01c4981d 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/048_add_zone_ownership_transfers_tables.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/048_add_zone_ownership_transfers_tables.py
@@ -22,6 +22,7 @@ TASK_STATUSES = ['ACTIVE', 'PENDING', 'DELETED', 'ERROR', 'COMPLETE']
 
 meta = MetaData()
 
+
 zone_transfer_requests = Table(
     'zone_transfer_requests',
     meta,
@@ -31,7 +32,8 @@ zone_transfer_requests = Table(
     Column('description', String(255), nullable=True),
     Column('tenant_id', String(36), nullable=False),
     Column('target_tenant_id', String(36), nullable=True),
-    Column('status', Enum(name='resource_statuses', *TASK_STATUSES),
+    Column('status',
+           Enum(name='task_statuses_ztr', metadata=meta, *TASK_STATUSES),
            nullable=False, server_default='ACTIVE',),
     Column('created_at', DateTime()),
     Column('updated_at', DateTime()),
@@ -49,7 +51,8 @@ zone_transfer_accepts = Table(
            ForeignKey('zone_transfer_requests.id',
                       ondelete='CASCADE'),
            nullable=False),
-    Column('status', Enum(name='resource_statuses', *TASK_STATUSES),
+    Column('status',
+           Enum(name='task_statuses_zta', metadata=meta, *TASK_STATUSES),
            nullable=False, server_default='ACTIVE'),
     Column('tenant_id', String(36), nullable=False),
     Column('created_at', DateTime()),
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/051_scoped_tsig.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/051_scoped_tsig.py
index 68cb2841..5c62601a 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/051_scoped_tsig.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/051_scoped_tsig.py
@@ -34,9 +34,11 @@ def upgrade(migrate_engine):
     # Load the TSIG Keys tables
     tsigkeys_table = Table('tsigkeys', meta, autoload=True)
 
+    scopes = Enum(name='tsig_scopes', metadata=meta, *TSIG_SCOPES)
+    scopes.create()
+
     # Create the scope and resource columns
-    scope_col = Column('scope', Enum(name='tsig_scopes', *TSIG_SCOPES),
-                       nullable=False, server_default='POOL')
+    scope_col = Column('scope', scopes, nullable=False, server_default='POOL')
     scope_col.create(tsigkeys_table)
 
     # Start with nullable=True and populate_default=True, then convert
@@ -62,10 +64,12 @@ def downgrade(migrate_engine):
 
     # Load the TSIG Keys tables
     tsigkeys_table = Table('tsigkeys', meta, autoload=True)
+    scopes = Enum(name='tsig_scopes', metadata=meta, *TSIG_SCOPES)
 
     # Create the scope and resource columns
     tsigkeys_table.c.scope.drop()
     tsigkeys_table.c.resource_id.drop()
+    scopes.drop()
 
     dialect = migrate_engine.url.get_dialect().name
     if dialect.startswith('sqlite'):
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/052_secondary_zones.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/052_secondary_zones.py
index 0597b53d..5ea53b41 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/052_secondary_zones.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/052_secondary_zones.py
@@ -34,39 +34,43 @@ ZONE_TYPES = ('PRIMARY', 'SECONDARY')
 meta = MetaData()
 
 
-domain_attributes = Table(
-    'domain_attributes', meta,
-    Column('id', UUID(), default=utils.generate_uuid, primary_key=True),
-    Column('version', Integer(), default=1, nullable=False),
-    Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
-    Column('updated_at', DateTime, onupdate=lambda: timeutils.utcnow()),
+def upgrade(migrate_engine):
+    meta.bind = migrate_engine
 
-    Column('key', Enum(name='key', *ZONE_ATTRIBUTE_KEYS)),
-    Column('value', String(255), nullable=False),
-    Column('domain_id', UUID(), nullable=False),
+    keys = Enum(name='key', *ZONE_ATTRIBUTE_KEYS)
 
-    UniqueConstraint('key', 'value', 'domain_id', name='unique_attributes'),
-    ForeignKeyConstraint(['domain_id'], ['domains.id'], ondelete='CASCADE'),
+    domain_attributes_table = Table(
+        'domain_attributes', meta,
+        Column('id', UUID(), default=utils.generate_uuid, primary_key=True),
+        Column('version', Integer(), default=1, nullable=False),
+        Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
+        Column('updated_at', DateTime, onupdate=lambda: timeutils.utcnow()),
 
-    mysql_engine='INNODB',
-    mysql_charset='utf8'
-)
+        Column('key', keys),
+        Column('value', String(255), nullable=False),
+        Column('domain_id', UUID(), nullable=False),
 
+        UniqueConstraint('key', 'value', 'domain_id',
+                         name='unique_attributes'),
+        ForeignKeyConstraint(['domain_id'], ['domains.id'],
+                             ondelete='CASCADE'),
 
-def upgrade(migrate_engine):
-    meta.bind = migrate_engine
+        mysql_engine='INNODB',
+        mysql_charset='utf8'
+    )
 
     domains_table = Table('domains', meta, autoload=True)
+    types = Enum(name='types', metadata=meta, *ZONE_TYPES)
+    types.create()
 
     # Add type and transferred_at to domains
-    type_ = Column('type', Enum(name='type', *ZONE_TYPES), default='PRIMARY',
-                   server_default='PRIMARY')
+    type_ = Column('type', types, default='PRIMARY', server_default='PRIMARY')
     transferred_at = Column('transferred_at', DateTime, default=None)
 
     type_.create(domains_table, populate_default=True)
     transferred_at.create(domains_table, populate_default=True)
 
-    domain_attributes.create()
+    domain_attributes_table.create()
 
     dialect = migrate_engine.url.get_dialect().name
     if dialect.startswith('sqlite'):
@@ -80,6 +84,10 @@ def upgrade(migrate_engine):
 def downgrade(migrate_engine):
     meta.bind = migrate_engine
 
+    keys = Enum(name='key', metadata=meta, *ZONE_ATTRIBUTE_KEYS)
+    types = Enum(name='types', metadata=meta, *ZONE_TYPES)
+
+    domains_attributes_table = Table('domain_attributes', meta, autoload=True)
     domains_table = Table('domains', meta, autoload=True)
 
     domains = select(columns=[domains_table.c.id, domains_table.c.type])\
@@ -94,7 +102,9 @@ def downgrade(migrate_engine):
     domains_table.c.type.drop()
     domains_table.c.transferred_at.drop()
 
-    domain_attributes.drop()
+    domains_attributes_table.drop()
+    keys.drop()
+    types.drop()
 
     dialect = migrate_engine.url.get_dialect().name
     if dialect.startswith('sqlite'):
diff --git a/designate/storage/impl_sqlalchemy/migrate_repo/versions/054_allow_duplicate_domains.py b/designate/storage/impl_sqlalchemy/migrate_repo/versions/054_allow_duplicate_domains.py
index 791d9f0a..b45cb849 100644
--- a/designate/storage/impl_sqlalchemy/migrate_repo/versions/054_allow_duplicate_domains.py
+++ b/designate/storage/impl_sqlalchemy/migrate_repo/versions/054_allow_duplicate_domains.py
@@ -41,6 +41,8 @@ def upgrade(migrate_engine):
 
 
 def downgrade(migrate_engine):
+    meta.bind = migrate_engine
+
     domains_table = Table('domains', meta, autoload=True)
 
     constraint = UniqueConstraint('name', 'deleted', 'pool_id',