Extensibility API

SQL Operations Studio provides an API that extensions can use to interact with other parts of the application such as Object Explorer. These APIs are available from the src/sql/sqlops.d.ts file and are described below.

Connection Management

sqlops.connection

Available starting in version 0.26.7 (February Public Preview)

Top-level Functions

• getCurrentConnection(): Thenable<sqlops.connection.Connection> Gets the current connection based on the active editor or Object Explorer selection.

• getActiveConnections(): Thenable<sqlops.connection.Connection[]> Gets a list of all the user's connections that are active. Returns an empty list if there are no such connections.

• getCredentials(connectionId: string): Thenable<{ [name: string]: string }> Gets a dictionary containing the credentials associated with a connection. These would otherwise be returned as part of the options dictionary under a sqlops.connection.Connection object but get stripped from that object.

Connection

• options: { [name: string]: string } The dictionary of connection options
• providerName: string The name of the connection provider (e.g. "MSSQL")
• connectionId: string The unique identifier for the connection

Example Code

> let connection = sqlops.connection.getCurrentConnection();
connection: {
	providerName: ‘MSSQL’,
	connectionId: ‘d97bb63a-466e-4ef0-ab6f-00cd44721dcc’,
	options: {
		server: ‘mairvine-sql-server’,
		user: ‘sa’,
		authenticationType: ‘sqlLogin’,
		…
	},
	…
}
> let credentials = sqlops.connection.getCredentials(connection.connectionId);
credentials: {
	password: ‘abc123’
}

Object Explorer

sqlops.objectexplorer

Available starting in the March Public Preview (currently available in master)

Top-level Functions

• getNode(connectionId: string, nodePath?: string): Thenable<sqlops.objectexplorer.ObjectExplorerNode> Get an Object Explorer node corresponding to the given connection and path. If no path is given, it returns the top-level node for the given connection. If there is no node at the given path, it returns undefined. Note: The nodePath for an object is generated by the SQL Tools Service backend and is difficult to construct by hand. Future API improvements will allow you to get nodes based on metadata you provide about the node, such as name, type and schema.

• getActiveConnectionNodes(): Thenable<sqlops.objectexplorer.ObjectExplorerNode> Get all active Object Explorer connection nodes.

• findNodes(connectionId: string, type: string, schema: string, name: string, database: string, parentObjectNames: string[]): Thenable<sqlops.objectexplorer.ObjectExplorerNode[]> Find all Object Explorer nodes that match the given metadata. The schema, database, and parentObjectNames arguments should be undefined when they are not applicable. parentObjectNames is a list of non-database parent objects, from highest to lowest level in Object Explorer, that the desired object is under. For example, when searching for a column "column1" that belongs to a table "schema1.table1" and database "database1" with connection ID connectionId, call findNodes(connectionId, 'Column', undefined, 'column1', 'database1', ['schema1.table1']). Also see the list of types that SQL Operations Studio supports by default for this API call.

ObjectExplorerNode

• connectionId: string The id of the connection that the node exists under

• nodePath: string The path of the node, as used for a call to the getNode function.

• nodeType: string A string representing the type of the node

• nodeSubType: string A string representing the subtype of the node

• nodeStatus: string A string representing the status of the node

• label: string The label for the node as it appears in Object Explorer

• isLeaf: boolean Whether the node is a leaf node and therefore has no children

• metadata: sqlops.ObjectMetadata Metadata describing the object represented by this node

• errorMessage: string Message shown if the node is in an error state

• isExpanded(): Thenable<boolean> Whether the node is currently expanded in Object Explorer

• setExpandedState(expandedState: vscode.TreeItemCollapsibleState): Thenable<void> Set whether the node is expanded or collapsed. If the state is set to None, the node will not be changed.

• setSelected(selected: boolean, clearOtherSelections?: boolean): Thenable<void> Set whether the node is selected. If clearOtherSelections is true, clear any other selections when making the new selection. If it is false, leave any existing selections. clearOtherSelections defaults to true when selected is true and false when selected is false.

• getChildren(): Thenable<sqlops.objectexplorer.ObjectExplorerNode[]> Get all the child nodes of this node. Returns an empty list if there are no children.

• getParent(): Thenable<sqlops.objectexplorer.ObjectExplorerNode> Get the parent node of this node. Returns undefined if there is no parent.

Example Code

private async interactWithOENode(selectedNode: sqlops.objectexplorer.ObjectExplorerNode): Promise<void> {
	let choices = ['Expand', 'Collapse', 'Select', 'Select (multi)', 'Deselect', 'Deselect (multi)'];
	if (selectedNode.isLeaf) {
		choices[0] += ' (is leaf)';
		choices[1] += ' (is leaf)';
	} else {
		let expanded = await selectedNode.isExpanded();
		if (expanded) {
			choices[0] += ' (is expanded)';
		} else {
			choices[1] += ' (is collapsed)';
		}
	}
	let parent = await selectedNode.getParent();
	if (parent) {
		choices.push('Get Parent');
	}
	let children = await selectedNode.getChildren();
	children.forEach(child => choices.push(child.label));
	let choice = await vscode.window.showQuickPick(choices);
	let nextNode: sqlops.objectexplorer.ObjectExplorerNode = undefined;
	if (choice === choices[0]) {
		selectedNode.setExpandedState(vscode.TreeItemCollapsibleState.Expanded);
	} else if (choice === choices[1]) {
		selectedNode.setExpandedState(vscode.TreeItemCollapsibleState.Collapsed);
	} else if (choice === choices[2]) {
		selectedNode.setSelected(true);
	} else if (choice === choices[3]) {
		selectedNode.setSelected(true, false);
	} else if (choice === choices[4]) {
		selectedNode.setSelected(false);
	} else if (choice === choices[5]) {
		selectedNode.setSelected(false, true);
	} else if (choice === 'Get Parent') {
		nextNode = parent;
	} else {
		let childNode = children.find(child => child.label === choice);
		nextNode = childNode;
	}
	if (nextNode) {
		let updatedNode = await sqlops.objectexplorer.getNode(nextNode.connectionId, nextNode.nodePath);
		this.interactWithOENode(updatedNode);
	}
}

vscode.commands.registerCommand('mssql.objectexplorer.interact', () => {
	sqlops.objectexplorer.getActiveConnectionNodes().then(activeConnections => {
		vscode.window.showQuickPick(activeConnections.map(connection => connection.label + ' ' + connection.connectionId)).then(selection => {
			let selectedNode = activeConnections.find(connection => connection.label + ' ' + connection.connectionId === selection);
			this.interactWithOENode(selectedNode);
		});
	});
});