path: root/ansible_collections/azure/azcollection/plugins/modules/azure_rm_sqldatabase.py

#!/usr/bin/python
#
# Copyright (c) 2017 Zim Kalinowski, <zikalino@microsoft.com>
#
# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)

from __future__ import absolute_import, division, print_function
__metaclass__ = type


DOCUMENTATION = '''
---
module: azure_rm_sqldatabase
version_added: "0.1.2"
short_description: Manage SQL Database instance
description:
    - Create, update and delete instance of SQL Database.

options:
    resource_group:
        description:
            - The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.
        required: True
        type: str
    server_name:
        description:
            - The name of the server.
        required: True
        type: str
    name:
        description:
            - The name of the database to be operated on (updated or created).
        required: True
        type: str
    location:
        description:
            - Resource location. If not set, location from the resource group will be used as default.
        type: str
    collation:
        description:
            - The collation of the database. If not I(create_mode=default), this value is ignored.
        type: str
    create_mode:
        description:
            - Specifies the mode of database creation.
            - C(default), regular database creation.
            - C(copy), creates a database as a copy of an existing database.
            - C(online_secondary)/C(non_readable_secondary), creates a database as a (readable or nonreadable) secondary replica of an existing database.
            - C(point_in_time_restore), Creates a database by restoring a point in time backup of an existing database.
            - C(recovery), Creates a database by restoring a geo-replicated backup.
            - C(restore), Creates a database by restoring a backup of a deleted database.
            - C(restore_long_term_retention_backup), Creates a database by restoring from a long term retention vault.
            - C(copy), C(non_readable_secondary), C(online_secondary) and C(restore_long_term_retention_backup) are not supported for C(data_warehouse) edition.
        type: str
        choices:
            - 'copy'
            - 'default'
            - 'non_readable_secondary'
            - 'online_secondary'
            - 'point_in_time_restore'
            - 'recovery'
            - 'restore'
            - 'restore_long_term_retention_backup'
    source_database_id:
        description:
            - Required unless I(create_mode=default) or I(create_mode=restore_long_term_retention_backup).
            - Specifies the resource ID of the source database.
        type: str
    source_database_deletion_date:
        description:
            - Required if I(create_mode=restore) and I(source_database_id) is the deleted database's original resource id when it existed (as
               opposed to its current restorable dropped database ID), then this value is required. Specifies the time that the database was deleted.
        type: str
    restore_point_in_time:
        description:
            - Required if I(create_mode=point_in_time_restore), this value is required. If I(create_mode=restore), this value is optional.
            - Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database.
            - Must be greater than or equal to the source database's earliestRestoreDate value.
        type: str
    recovery_services_recovery_point_resource_id:
        description:
            - Required if I(create_mode=restore_long_term_retention_backup), then this value is required.
            - Specifies the resource ID of the recovery point to restore from.
        type: str
    edition:
        description:
            - (Deprecate)The edition of the database. The DatabaseEditions enumeration contains all the valid editions.
            - This option will be deprecated in 2.11, use I(sku) instead.
            - Cannot set C(sku) when this field set.
        type: str
        choices:
            - 'web'
            - 'business'
            - 'basic'
            - 'standard'
            - 'premium'
            - 'free'
            - 'stretch'
            - 'data_warehouse'
            - 'system'
            - 'system2'
    sku:
        description:
            - The sku of the database. The DatabaseEditions enumeration contains all the valid sku.
            - If I(create_mode=non_readable_secondary) or I(create_mode=online_secondary), this value is ignored.
            - To see possible values, query the capabilities API (/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{locationID}/capabilities)
              referred to by operationId:'Capabilities_ListByLocation'.
            - Cannot set C(edition) when this field set.
        type: dict
        suboptions:
            name:
                description:
                    - Name of the database SKU, typically, a letter + Number code, e.g. P3
                required: True
                type: str
            tier:
                description:
                    - The tier or edition of the particular SKU, e.g. Basic, Premium
                type: str
            capacity:
                description:
                    - Capacity of the particular SKU.
                type: int
            size:
                description:
                    - Size of the particular SKU
                type: str
            family:
                description:
                    - If the service has different generations of hardware, for the same SKU, then that can be used here
                type: str
    max_size_bytes:
        description:
            - The max size of the database expressed in bytes.
            - If not I(create_mode=default), this value is ignored.
            - To see possible values, query the capabilities API (/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{locationID}/capabilities).
              referred to by operationId:'Capabilities_ListByLocation'.
        type: str
    elastic_pool_name:
        description:
            - The name of the elastic pool the database is in. Not supported for I(edition=data_warehouse).
        type: str
    read_scale:
        description:
            - If the database is a geo-secondary, indicates whether read-only connections are allowed to this database or not.
            - Not supported for I(edition=data_warehouse).
        type: bool
        default: False
    sample_name:
        description:
            - Indicates the name of the sample schema to apply when creating this database.
            - If not I(create_mode=default), this value is ignored.
            - Not supported for I(edition=data_warehouse).
        type: str
        choices:
            - 'adventure_works_lt'
    zone_redundant:
        description:
            - Is this database is zone redundant? It means the replicas of this database will be spread across multiple availability zones.
        type: bool
        default: False
    force_update:
        description:
            - SQL Database will be updated if given parameters differ from existing resource state.
            - To force SQL Database update in any circumstances set this parameter to True.
        type: bool
    state:
        description:
            - Assert the state of the SQL Database. Use C(present) to create or update an SQL Database and C(absent) to delete it.
        default: present
        type: str
        choices:
            - absent
            - present

extends_documentation_fragment:
    - azure.azcollection.azure
    - azure.azcollection.azure_tags

author:
    - Zim Kalinowski (@zikalino)

'''

EXAMPLES = '''
- name: Create (or update) SQL Database
  azure_rm_sqldatabase:
    resource_group: myResourceGroup
    server_name: sqlcrudtest-5961
    name: testdb
    location: eastus

- name: Restore SQL Database
  azure_rm_sqldatabase:
    resource_group: myResourceGroup
    server_name: sqlcrudtest-5961
    name: restoreddb
    location: eastus
    create_mode: restore
    restorable_dropped_database_id: "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.Sql/s
                                     ervers/testsvr/restorableDroppedDatabases/testdb2,131444841315030000"

- name: Create SQL Database in Copy Mode
  azure_rm_sqldatabase:
    resource_group: myResourceGroup
    server_name: sqlcrudtest-5961
    name: copydb
    location: eastus
    create_mode: copy
    source_database_id: "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/tests
                         vr/databases/testdb"

- name: Create (or update) SQL Database with SKU
  azure_rm_sqldatabase:
    resource_group: myResourceGroup
    server_name: sqlcrudtest-5961
    name: testdb
    location: eastus
    sku:
      name: S0
'''

RETURN = '''
id:
    description:
        - Resource ID.
    returned: always
    type: str
    sample: "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/sqlcrudtest-5961/databases/t
            estdb"
database_id:
    description:
        - The ID of the database.
    returned: always
    type: str
    sample: database_id
status:
    description:
        - The status of the database.
    returned: always
    type: str
    sample: Online
'''

import time
from ansible_collections.azure.azcollection.plugins.module_utils.azure_rm_common import AzureRMModuleBase, format_resource_id

try:
    import dateutil.parser
    from azure.core.exceptions import ResourceNotFoundError
    from azure.core.polling import LROPoller
    from azure.mgmt.sql.models import Sku
except ImportError:
    # This is handled in azure_rm_common
    pass

sku_spec = dict(
    name=dict(type='str', required=True),
    tier=dict(type='str'),
    size=dict(type='str'),
    family=dict(type='str'),
    capacity=dict(type='int')
)


def get_sku_name(edition):
    edition = edition.upper()
    if edition == 'FREE':
        return 'Free'
    elif edition == 'SYSTEM':
        return 'GP_Gen5_2'
    elif edition in ['BUSINESS', 'SYSTEM2']:
        return 'BC_Gen5_2'
    elif edition == 'BASIC':
        return 'Basic'
    elif edition in ['STANDARD', 'WEB']:
        return 'S1'
    elif edition == 'PREMIUM':
        return 'P2'
    elif edition == 'STRETCH':
        return 'DS100'
    elif edition == 'DATA_WAREHOUSE':
        return 'DW100c'
    else:
        return None


class Actions:
    NoAction, Create, Update, Delete = range(4)


class AzureRMSqlDatabase(AzureRMModuleBase):
    """Configuration class for an Azure RM SQL Database resource"""

    def __init__(self):
        self.module_arg_spec = dict(
            resource_group=dict(
                type='str',
                required=True
            ),
            server_name=dict(
                type='str',
                required=True
            ),
            name=dict(
                type='str',
                required=True
            ),
            location=dict(
                type='str'
            ),
            collation=dict(
                type='str'
            ),
            create_mode=dict(
                type='str',
                choices=['copy',
                         'default',
                         'non_readable_secondary',
                         'online_secondary',
                         'point_in_time_restore',
                         'recovery',
                         'restore',
                         'restore_long_term_retention_backup']
            ),
            source_database_id=dict(
                type='str'
            ),
            source_database_deletion_date=dict(
                type='str'
            ),
            restore_point_in_time=dict(
                type='str'
            ),
            recovery_services_recovery_point_resource_id=dict(
                type='str'
            ),
            edition=dict(
                type='str',
                choices=['web',
                         'business',
                         'basic',
                         'standard',
                         'premium',
                         'free',
                         'stretch',
                         'data_warehouse',
                         'system',
                         'system2']
            ),
            sku=dict(
                type='dict',
                options=sku_spec
            ),
            max_size_bytes=dict(
                type='str'
            ),
            elastic_pool_name=dict(
                type='str'
            ),
            read_scale=dict(
                type='bool',
                default=False
            ),
            sample_name=dict(
                type='str',
                choices=['adventure_works_lt']
            ),
            zone_redundant=dict(
                type='bool',
                default=False
            ),
            force_update=dict(
                type='bool'
            ),
            state=dict(
                type='str',
                default='present',
                choices=['present', 'absent']
            )
        )

        self.resource_group = None
        self.server_name = None
        self.name = None
        self.parameters = dict()
        self.tags = None

        self.results = dict(changed=False)
        self.state = None
        self.to_do = Actions.NoAction

        super(AzureRMSqlDatabase, self).__init__(derived_arg_spec=self.module_arg_spec,
                                                 supports_check_mode=True,
                                                 supports_tags=True)

    def exec_module(self, **kwargs):
        """Main module execution method"""

        for key in list(self.module_arg_spec.keys()) + ['tags']:
            if hasattr(self, key):
                setattr(self, key, kwargs[key])
            elif kwargs[key] is not None:
                if key == "location":
                    self.parameters["location"] = kwargs[key]
                elif key == "collation":
                    self.parameters["collation"] = kwargs[key]
                elif key == "create_mode":
                    self.parameters["create_mode"] = _snake_to_camel(
                        kwargs[key], True)
                elif key == "source_database_id":
                    self.parameters["source_database_id"] = kwargs[key]
                elif key == "source_database_deletion_date":
                    try:
                        self.parameters["source_database_deletion_date"] = dateutil.parser.parse(kwargs[key])
                    except dateutil.parser._parser.ParserError:
                        self.fail("Error parsing date from source_database_deletion_date: {0}".format(kwargs[key]))
                elif key == "restore_point_in_time":
                    try:
                        self.parameters["restore_point_in_time"] = dateutil.parser.parse(kwargs[key])
                    except dateutil.parser._parser.ParserError:
                        self.fail("Error parsing date from restore_point_in_time: {0}".format(kwargs[key]))
                elif key == "recovery_services_recovery_point_resource_id":
                    self.parameters["recovery_services_recovery_point_resource_id"] = kwargs[key]
                elif key == "edition":
                    ev = get_sku_name(kwargs[key])
                    self.parameters["sku"] = Sku(name=ev)
                elif key == "sku":
                    ev = kwargs[key]
                    self.parameters["sku"] = Sku(
                        name=ev['name'], tier=ev['tier'], size=ev['size'], family=ev['family'], capacity=ev['capacity'])
                elif key == "max_size_bytes":
                    self.parameters["max_size_bytes"] = kwargs[key]
                elif key == "elastic_pool_name":
                    self.parameters["elastic_pool_id"] = kwargs[key]
                elif key == "read_scale":
                    self.parameters["read_scale"] = 'Enabled' if kwargs[key] else 'Disabled'
                elif key == "sample_name":
                    ev = kwargs[key]
                    if ev == 'adventure_works_lt':
                        ev = 'AdventureWorksLT'
                    self.parameters["sample_name"] = ev
                elif key == "zone_redundant":
                    self.parameters["zone_redundant"] = True if kwargs[key] else False

        old_response = None
        response = None

        resource_group = self.get_resource_group(self.resource_group)

        if "location" not in self.parameters:
            self.parameters["location"] = resource_group.location

        if "elastic_pool_id" in self.parameters:
            self.format_elastic_pool_id()

        old_response = self.get_sqldatabase()

        if not old_response:
            self.log("SQL Database instance doesn't exist")
            if self.state == 'absent':
                self.log("Old instance didn't exist")
            else:
                self.to_do = Actions.Create
        else:
            self.log("SQL Database instance already exists")
            if self.state == 'absent':
                self.to_do = Actions.Delete
            elif self.state == 'present':
                self.log(
                    "Need to check if SQL Database instance has to be deleted or may be updated")
                if ('location' in self.parameters) and (self.parameters['location'] != old_response['location']):
                    self.to_do = Actions.Update
                if (('read_scale' in self.parameters) and
                        (self.parameters['read_scale'] != old_response['read_scale'])):
                    self.to_do = Actions.Update
                if (('max_size_bytes' in self.parameters) and
                        (self.parameters['max_size_bytes'] != old_response['max_size_bytes'])):
                    self.to_do = Actions.Update
                if (('sku' in self.parameters) and
                        (self.parameters['sku'].as_dict() != old_response['sku'])):
                    self.to_do = Actions.Update
                update_tags, newtags = self.update_tags(
                    old_response.get('tags', dict()))
                if update_tags:
                    self.tags = newtags
                    self.to_do = Actions.Update

        if (self.to_do == Actions.Create) or (self.to_do == Actions.Update):
            self.log("Need to Create / Update the SQL Database instance")

            if self.check_mode:
                self.results['changed'] = True
                return self.results

            self.parameters['tags'] = self.tags
            response = self.create_update_sqldatabase()

            if not old_response:
                self.results['changed'] = True
            else:
                self.results['changed'] = old_response.__ne__(response)
            self.log("Creation / Update done")
        elif self.to_do == Actions.Delete:
            self.log("SQL Database instance deleted")
            self.results['changed'] = True

            if self.check_mode:
                return self.results

            self.delete_sqldatabase()
            # make sure instance is actually deleted, for some Azure resources, instance is hanging around
            # for some time after deletion -- this should be really fixed in Azure
            while self.get_sqldatabase():
                time.sleep(20)
        else:
            self.log("SQL Database instance unchanged")
            self.results['changed'] = False
            response = old_response

        if response:
            self.results["id"] = response["id"]
            self.results["database_id"] = response["database_id"]
            self.results["status"] = response["status"]

        return self.results

    def create_update_sqldatabase(self):
        '''
        Creates or updates SQL Database with the specified configuration.

        :return: deserialized SQL Database instance state dictionary
        '''
        self.log(
            "Creating / Updating the SQL Database instance {0}".format(self.name))

        try:
            response = self.sql_client.databases.begin_create_or_update(resource_group_name=self.resource_group,
                                                                        server_name=self.server_name,
                                                                        database_name=self.name,
                                                                        parameters=self.parameters)
            if isinstance(response, LROPoller):
                response = self.get_poller_result(response)
        except Exception as exc:
            self.log('Error attempting to create the SQL Database instance.')
            self.fail(
                "Error creating the SQL Database instance: {0}".format(str(exc)))
        return response.as_dict()

    def delete_sqldatabase(self):
        '''
        Deletes specified SQL Database instance in the specified subscription and resource group.

        :return: True
        '''
        self.log("Deleting the SQL Database instance {0}".format(self.name))
        try:
            response = self.sql_client.databases.begin_delete(resource_group_name=self.resource_group,
                                                              server_name=self.server_name,
                                                              database_name=self.name)
            if isinstance(response, LROPoller):
                response = self.get_poller_result(response)
        except Exception as e:
            self.log('Error attempting to delete the SQL Database instance.')
            self.fail(
                "Error deleting the SQL Database instance: {0}".format(str(e)))

        return True

    def get_sqldatabase(self):
        '''
        Gets the properties of the specified SQL Database.

        :return: deserialized SQL Database instance state dictionary
        '''
        self.log(
            "Checking if the SQL Database instance {0} is present".format(self.name))
        found = False
        try:
            response = self.sql_client.databases.get(resource_group_name=self.resource_group,
                                                     server_name=self.server_name,
                                                     database_name=self.name)
            found = True
            self.log("Response : {0}".format(response))
            self.log("SQL Database instance : {0} found".format(response.name))
        except ResourceNotFoundError:
            self.log('Did not find the SQL Database instance.')
        if found is True:
            return response.as_dict()

        return False

    def format_elastic_pool_id(self):
        parrent_id = format_resource_id(val=self.server_name,
                                        subscription_id=self.subscription_id,
                                        namespace="Microsoft.Sql",
                                        types="servers",
                                        resource_group=self.resource_group)
        self.parameters['elastic_pool_id'] = parrent_id + \
            "/elasticPools/" + self.parameters['elastic_pool_id']


def _snake_to_camel(snake, capitalize_first=False):
    if capitalize_first:
        return ''.join(x.capitalize() or '_' for x in snake.split('_'))
    else:
        return snake.split('_')[0] + ''.join(x.capitalize() or '_' for x in snake.split('_')[1:])


def main():
    """Main execution"""
    AzureRMSqlDatabase()


if __name__ == '__main__':
    main()