1# --------------------------------------------------------------------------------------------
2# Copyright (c) Microsoft Corporation. All rights reserved.
3# Licensed under the MIT License. See License.txt in the project root for license information.
4# --------------------------------------------------------------------------------------------
5# pylint: disable=unused-argument
6from azure.cli.core.util import sdk_no_wait
7from azure.mgmt.synapse.models import SqlPool, SqlPoolPatchInfo, Sku
8from knack.util import CLIError
9from .._client_factory import cf_synapse_client_workspace_factory
10from ..constant import SynapseSqlCreateMode, SqlPoolConnectionClientAuthenticationType, SqlPoolConnectionClientType
11
12
13# Synapse sqlpool
14def create_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, performance_level, tags=None,
15                    no_wait=False):
16    workspace_client = cf_synapse_client_workspace_factory(cmd.cli_ctx)
17    workspace_object = workspace_client.get(resource_group_name, workspace_name)
18    location = workspace_object.location
19
20    sku = Sku(name=performance_level)
21
22    sql_pool_info = SqlPool(sku=sku, location=location, create_mode=SynapseSqlCreateMode.Default, tags=tags)
23
24    return sdk_no_wait(no_wait, client.begin_create, resource_group_name, workspace_name, sql_pool_name, sql_pool_info)
25
26
27def update_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, sku_name=None, tags=None):
28    sku = Sku(name=sku_name)
29    sql_pool_patch_info = SqlPoolPatchInfo(sku=sku, tags=tags)
30    return client.update(resource_group_name, workspace_name, sql_pool_name, sql_pool_patch_info)
31
32
33def restore_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, destination_name,
34                     performance_level=None, restore_point_in_time=None, source_database_deletion_date=None,
35                     no_wait=False, **kwargs):
36    """
37    Restores an existing or deleted SQL pool (i.e. create with 'Restore'
38    or 'PointInTimeRestore' create mode.)
39
40    Custom function makes create mode more convenient.
41    """
42    if not (restore_point_in_time or source_database_deletion_date):
43        raise CLIError('Either --time or --deleted-time must be specified.')
44
45    # Set create mode properties
46    is_deleted = source_database_deletion_date is not None
47    create_mode = SynapseSqlCreateMode.Restore if is_deleted else SynapseSqlCreateMode.PointInTimeRestore
48
49    source_sql_pool_info = client.get(resource_group_name, workspace_name, sql_pool_name)
50
51    # get the default performance_level
52    if performance_level is None:
53        performance_level = source_sql_pool_info.sku.name
54
55    # create source database id
56    source_database_id = _construct_database_resource_id(cmd.cli_ctx, resource_group_name, workspace_name,
57                                                         sql_pool_name)
58
59    sku = Sku(name=performance_level)
60    dest_sql_pool_info = SqlPool(sku=sku, location=source_sql_pool_info.location, create_mode=create_mode,
61                                 restore_point_in_time=restore_point_in_time, source_database_id=source_database_id)
62
63    return sdk_no_wait(no_wait, client.begin_create, resource_group_name, workspace_name, destination_name,
64                       dest_sql_pool_info)
65
66
67def sql_pool_show_connection_string(
68        cmd,
69        client_provider,
70        sql_pool_name='<sql pool name>',
71        workspace_name='<workspace name>',
72        auth_type=SqlPoolConnectionClientAuthenticationType.SqlPassword.value):
73    """
74    Builds a SQL connection string for a specified client provider.
75    """
76
77    workspace_sql_pool_compute_suffix = cmd.cli_ctx.cloud.suffixes.synapse_analytics_endpoint.replace('dev', 'sql')
78
79    conn_str_props = {
80        'workspace': workspace_name,
81        'workspace_fqdn': '{}{}'.format(workspace_name, workspace_sql_pool_compute_suffix),
82        'workspace_suffix': workspace_sql_pool_compute_suffix,
83        'sql_pool': sql_pool_name
84    }
85
86    formats = {
87        SqlPoolConnectionClientType.AdoDotNet: {
88            SqlPoolConnectionClientAuthenticationType.SqlPassword:
89                'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;'
90                'User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;'
91                'TrustServerCertificate=False;Connection Timeout=30;',
92            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword:
93                'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;'
94                'User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;'
95                'TrustServerCertificate=False;Authentication="Active Directory Password";',
96            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated:
97                'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;'
98                'User ID=<username>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;'
99                'Authentication="Active Directory Integrated";'
100        },
101        SqlPoolConnectionClientType.Jdbc: {
102            SqlPoolConnectionClientAuthenticationType.SqlPassword:
103                'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};user=<username>@{workspace};'
104                'password=<password>;encrypt=true;trustServerCertificate=false;'
105                'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;',
106            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword:
107                'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};user=<username>;'
108                'password=<password>;encrypt=true;trustServerCertificate=false;'
109                'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;authentication=ActiveDirectoryPassword',
110            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated:
111                'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};'
112                'encrypt=true;trustServerCertificate=false;'
113                'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;Authentication=ActiveDirectoryIntegrated',
114        },
115        SqlPoolConnectionClientType.Odbc: {
116            SqlPoolConnectionClientAuthenticationType.SqlPassword:
117                'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;'
118                'Database={sql_pool};Uid=<username>;Pwd=<password>;Encrypt=yes;'
119                'TrustServerCertificate=no;Connection Timeout=30;',
120            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword:
121                'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;'
122                'Database={sql_pool};Uid=<username>;Pwd=<password>;Encrypt=yes;'
123                'TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword',
124            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated:
125                'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;'
126                'Database={sql_pool};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
127                'Authentication=ActiveDirectoryIntegrated',
128        },
129        SqlPoolConnectionClientType.Php: {
130            # pylint: disable=line-too-long
131            SqlPoolConnectionClientAuthenticationType.SqlPassword:
132                '$connectionOptions = array("UID"=>"<username>@{workspace}", "PWD"=>"<password>", "Database"=>{sql_pool}, "LoginTimeout" => 30, "Encrypt" => 1, "TrustServerCertificate" => 0); $serverName = "tcp:{workspace_fqdn},1433"; $conn = sqlsrv_connect($serverName, $connectionOptions);',
133            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword:
134                CLIError('PHP sqlsrv driver only supports SQL Password authentication.'),
135            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated:
136                CLIError('PHP sqlsrv driver only supports SQL Password authentication.'),
137        },
138        SqlPoolConnectionClientType.PhpPdo: {
139            # pylint: disable=line-too-long
140            SqlPoolConnectionClientAuthenticationType.SqlPassword:
141                '$conn = new PDO("sqlsrv:server = tcp:{workspace_fqdn},1433; Database = {sql_pool}; LoginTimeout = 30; Encrypt = 1; TrustServerCertificate = 0;", "<username>", "<password>");',
142            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword:
143                CLIError('PHP Data Object (PDO) driver only supports SQL Password authentication.'),
144            SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated:
145                CLIError('PHP Data Object (PDO) driver only supports SQL Password authentication.'),
146        }
147    }
148
149    f = formats[client_provider][auth_type]
150
151    if isinstance(f, Exception):
152        # Error
153        raise f
154
155    # Success
156    return f.format(**conn_str_props)
157
158
159def _construct_database_resource_id(cli_ctx, resource_group_name, server_name, database_name):
160    # url parse package has different names in Python 2 and 3. 'six' package works cross-version.
161    from six.moves.urllib.parse import quote  # pylint: disable=import-error
162    from azure.cli.core.commands.client_factory import get_subscription_id
163
164    return '/subscriptions/{}/resourceGroups/{}/providers/Microsoft.Sql/servers/{}/databases/{}'.format(
165        quote(get_subscription_id(cli_ctx)),
166        quote(resource_group_name),
167        quote(server_name),
168        quote(database_name))
169