3_newdatabase.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "python3",
            "display_name": "Python 3"
        },
        "language_info": {
            "name": "python",
            "version": "3.6.6",
            "mimetype": "text/x-python",
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "pygments_lexer": "ipython3",
            "nbconvert_exporter": "python",
            "file_extension": ".py"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": "# A real world example: New Database extension\n\n#### Why build this?\nI built the New Database extension because we need empty DBs to test against all the time. We can use the `sqlCreateDatabase` snippet, but when done a _lot_ having to open a query window and type / execute a snippet felt too slow. I want a new database now!\n> This extension was also a great way to show the power of extensions since it connects, issues a SQL query, and kicks off a background task.\n\n### Install the extension\n* <a href=\"command:workbench.view.extensions\">View Extensions</a> and Search **New Database**\n* Click install, download VSIX and use **Install from VSIX** command to install\n\n### Create a new database\n\n|Step| Contribution Point | What's added | \n|----|----|----|\n|Right-click on the Databases folder for a connection| Menus |  **New Database** menu item, when node label is Databases| \n|Choose **New Database** | Command |  **New Database** command | \n|Enter database name| Quick Pick menu | Request simple input | \n|See task progress| Tasks window | Create Database started/succeeded/failed | \n|Notify with more information / next steps| Show Message | | \n\n<br/>\n\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "## The Data Management Protocol (DMP)\n\nTo create a database something needs to connect to a DB and run a SQL command. You could write your own backend in Typescript, .Net Core, Python etc. but why do this when we already have connect, query and many more actions built-in?\n\nThe Data Management Protocol (DMP) defines the APIs needed to connect, query, and more. \n* SQL Server and PostgreSQL connections work using the DMP and run in extensions\n* All the core operations in Azure Data Studio use the DMP\n* Using this avoids issues where your driver doesn't support an authentication type (e.g. Azure Active Directory), so it's a great idea to use it!\n\n| Action | DMP Provider | Notes |\n|----|----|----|\n| Connect to a SQL DB | **ConnectionProvider**| |\n| Run a query | **QueryProvider** | |\n| Object Explorer / Schema Browsing |  **ObjectExplorerProvider**  | Primary provider of schema / metadata about a connection in the **Connections** view. Should provide all the basic schema information, management folders and more |\n| Add nodes under an Object Explorer session  | **ObjectExplorerNodeProvider** | Augment SQL Server or other connection type with additional subtrees. For example: Data Services & HDFS folders under SQL Server connections use this since they're using REST instead of TDS to query metadata. Great for adding Monitoring, customized metadata, or other features. |\n| Script objects | **ScriptingProvider** | |\n\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "\n## Walkthrough: Writing the New Database extension\n",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "!code ./3-newdatabase/",
            "metadata": {},
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": "\n### 1. Define new command\n\n#### package.json\n* Add a command `newdatabase.createdb`\n* Show in on right-click if we're on the Databases node\n\n```json\n\n\"activationEvents\": [\n    \"onCommand:newdatabase.createdb\"\n],\n\"contributes\": {\n    \"commands\": [\n        {\n            \"command\": \"newdatabase.createdb\",\n            \"title\": \"New Database\"\n        }\n    ],\n    \"menus\": {\n        \"objectExplorer/item/context\": [\n            {\n                \"command\": \"newdatabase.createdb\",\n                \"when\": \"connectionProvider == MSSQL && nodeType && nodeType == Folder && nodeLabel=~/^Databases$/\",\n                \"group\": \"1data1\"\n            }\n        ]\n    },\n    \"keybindings\": [\n        {\n            \"command\": \"newdatabase.createdb\",\n            \"key\": \"Ctrl+Shift+d b\"\n        }\n    ]\n},\n```\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "\n### 2. Define the contribution in extension.ts\n\n```ts\nexport function activate(context: vscode.ExtensionContext) {\n    context.subscriptions.push(vscode.commands.registerCommand('newdatabase.createdb', async (context: sqlops.ObjectExplorerContext) => {\n        createDatabase(context);\n    }));\n}\n```\n\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "### 3. Basic task flows\n\nThe basic flow of any task is to verify inputs, prompt a user if anything extra is needed, then kick off a task. This is really easy using the Azure Data Studio APIs.\n\n```ts\nasync function createDatabase(context: sqlops.ObjectExplorerContext): Promise<void> {\n    let connection = await makeSureWeAreConnected(context);\n    if (!connection) {\n        vscode.window.showInformationMessage('Cannot create database as no active connection could be found');\n        return;\n    }\n\n    // Prompt the user for a new database name\n    let dbName = await vscode.window.showInputBox({\n        prompt: 'Name of database to create on server ' + connection.options['server']\n    });\n    if (!dbName) {\n        return;\n    }\n\n    // Run the create database as a task\n    sqlops.tasks.startBackgroundOperation({\n        connection: connection as sqlops.connection.Connection,\n        displayName: 'Creating Database ' + dbName,\n        description: '',\n        isCancelable: false,\n        operation: (op) => doCreateDatabase(op, dbName, connection)\n    });\n}\n```",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "### Using the DMP to connect\n\nConnection is a 2-step process in the DMP. To simplify things until we get new APIs ready, I created a helper package to create a new connection for use with queries.\n\n**package.json**\n```json\n\"dependencies\": {\n    \"azuredatastudio-dmpwrapper\": \"^0.5.0\"\n}\n```\n\n**extension.ts**\n\nImport the helper:\n```ts\nimport { ConnectionContext } from 'azuredatastudio-dmpwrapper';\n```\n\nThen create it after getting the right provider\n```ts\nlet connectionProvider = sqlops.dataprotocol.getProvider<sqlops.ConnectionProvider>(\n    'MSSQL', sqlops.DataProviderType.ConnectionProvider);\nlet connectionContext = new ConnectionContext(connectionProvider);\n```\n\nRun connection before any queries \n```ts\nlet isConnected = await connectionContext.tryConnect(connection);\n```\n\nAnd dispose the connection when you're done - be sure to put this in a `finally` block!\n```ts\n} finally {\n    connectionContext.dispose();\n}\n```",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "### Using the DMP to run a simple query\n\nThe `runQueryAndReturn` method is the simplest and best one right now. We will add a more robust API in the future the one drawback is you need to return a resultset.\n\nNote you should also try to sanitize inputs, per the example below!\n\n```ts\ndbName = dbName.replace(/]/g , \"]]\");\nlet query = 'BEGIN TRY\n    CREATE DATABASE [' + dbName + `]\n    SELECT 1 AS NoError\nEND TRY\nBEGIN CATCH\n    SELECT ERROR_MESSAGE() AS ErrorMessage;\nEND CATCH\n`;\n\nlet result = await connectionContext.runQueryAndReturn(query);\n```\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "### Completing the task\n\nFor task to complete, set the status to Succeeded or Failed:\n```ts\ntry {\n    ...\n    let successMsg = 'Database ' + dbName + ' created. Refresh the Databases node to see it';\n    operation.updateStatus(sqlops.TaskStatus.Succeeded, successMsg);\n} catch (error) {\n    let errorString = error instanceof Error ? error.message : error;\n    operation.updateStatus(sqlops.TaskStatus.Failed, 'Error adding database: ' + errorString);\n}\n\n```",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "### Summary\n\nBy now, you've seen how to use advanced APIs in Azure Data Studio. There are a lot more, including wizards, dialogs and other UI. Check out the [Azure Data Studio wiki on Extension Authoring](https://github.com/Microsoft/azuredatastudio/wiki/Extension-Authoring) and our [Samples projects](https://github.com/microsoft/azuredatastudio/tree/master/samples) to learn more.",
            "metadata": {}
        }
    ]
}