Skip to content

Commit

Permalink
Load profile support for sql project deploy (microsoft#10948)
Browse files Browse the repository at this point in the history 
* load database name from profile.xml

* load sqlcmd variables from profile

* Add warning text

* add tests

* fix file filter for windows

* add comments

* show SQLCMD variables in a table

* reset dialog before testing readPublishProfile callback
  • Loading branch information
@kisantia
kisantia authored Jun 19, 2020
1 parent eaf753f commit 6ad33aa
Show file tree
Hide file tree
Showing 10 changed files with 205 additions and 11 deletions.
9 changes: 9 additions & 0 deletions extensions/sql-database-projects/src/common/constants.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -41,6 +41,7 @@ export const databaseReferenceSameDatabase = localize('databaseReferenceSameData
export const databaseReferenceDifferentDabaseSameServer = localize('databaseReferenceDifferentDabaseSameServer', "Different database, same server");
export const databaseReferenceDatabaseName = localize('databaseReferenceDatabaseName', "Database name");
export const dacpacFiles = localize('dacpacFiles', "dacpac Files");
export const publishSettingsFiles = localize('publishSettingsFiles', "Publish Settings File");
export const systemDatabase = localize('systemDatabase', "System Database");
export function newObjectNamePrompt(objectType: string) { return localize('newObjectNamePrompt', 'New {0} name:', objectType); }

Expand All @@ -61,6 +62,11 @@ export const connectionRadioButtonLabel = localize('connectionRadioButtonLabel',
export const selectConnectionRadioButtonsTitle = localize('selectconnectionRadioButtonsTitle', "Specify connection from:");
export const dataSourceDropdownTitle = localize('dataSourceDropdownTitle', "Data source");
export const noDataSourcesText = localize('noDataSourcesText', "No data sources in this project");
export const loadProfileButtonText = localize('loadProfileButtonText', "Load Profile...");
export const profileWarningText = localize('profileWarningText', "⚠Warning: Only database name and SQLCMD variables are able to be loaded from a profile at this time");
export const sqlCmdTableLabel = localize('sqlCmdTableLabel', "SQLCMD Variables");
export const sqlCmdVariableColumn = localize('sqlCmdVariableColumn', "Variable");
export const sqlCmdValueColumn = localize('sqlCmdValueColumn', "Value");

// Error messages

Expand Down Expand Up @@ -146,6 +152,9 @@ export const NETFrameworkAssembly = 'Microsoft.NETFramework.ReferenceAssemblies'
export const VersionNumber = '1.0.0';
export const All = 'All';

// Profile XML names
export const targetDatabaseName = 'TargetDatabaseName';

// SQL connection string components
export const initialCatalogSetting = 'Initial Catalog';
export const dataSourceSetting = 'Data Source';
Expand Down
18 changes: 18 additions & 0 deletions extensions/sql-database-projects/src/common/utils.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -5,6 +5,7 @@

import * as vscode from 'vscode';
import * as os from 'os';
import * as constants from '../common/constants';
import { promises as fs } from 'fs';

/**
Expand Down Expand Up @@ -95,3 +96,20 @@ export function getSafeNonWindowsPath(filePath: string): string {
filePath = filePath.split('\\').join('/').split('"').join('');
return '"' + filePath + '"';
}

/**
* Read SQLCMD variables from xmlDoc and return them
* @param xmlDoc xml doc to read SQLCMD variables from. Format must be the same that sqlproj and publish profiles use
*/
export function readSqlCmdVariables(xmlDoc: any): Record<string, string> {
let sqlCmdVariables: Record<string, string> = {};
for (let i = 0; i < xmlDoc.documentElement.getElementsByTagName(constants.SqlCmdVariable).length; i++) {
const sqlCmdVar = xmlDoc.documentElement.getElementsByTagName(constants.SqlCmdVariable)[i];
const varName = sqlCmdVar.getAttribute(constants.Include);

const varValue = sqlCmdVar.getElementsByTagName(constants.DefaultValue)[0].childNodes[0].nodeValue;
sqlCmdVariables[varName] = varValue;
}

return sqlCmdVariables;
}
25 changes: 24 additions & 1 deletion extensions/sql-database-projects/src/controllers/projectController.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -10,6 +10,7 @@ import * as path from 'path';
import * as utils from '../common/utils';
import * as UUID from 'vscode-languageclient/lib/utils/uuid';
import * as templates from '../templates/templates';
import * as xmldom from 'xmldom';

import { Uri, QuickPickItem, WorkspaceFolder, extensions, Extension } from 'vscode';
import { IConnectionProfile, TaskExecutionMode } from 'azdata';
Expand All @@ -19,7 +20,7 @@ import { DeployDatabaseDialog } from '../dialogs/deployDatabaseDialog';
import { Project, DatabaseReferenceLocation, SystemDatabase, TargetPlatform } from '../models/project';
import { SqlDatabaseProjectTreeViewProvider } from './databaseProjectTreeViewProvider';
import { FolderNode } from '../models/tree/fileFolderTreeItem';
import { IDeploymentProfile, IGenerateScriptProfile } from '../models/IDeploymentProfile';
import { IDeploymentProfile, IGenerateScriptProfile, PublishSettings } from '../models/IDeploymentProfile';
import { BaseProjectTreeItem } from '../models/tree/baseTreeItem';
import { ProjectRootTreeItem } from '../models/tree/projectTreeItem';
import { ImportDataModel } from '../models/api/import';
Expand Down Expand Up @@ -193,6 +194,7 @@ export class ProjectsController {

deployDatabaseDialog.deploy = async (proj, prof) => await this.executionCallback(proj, prof);
deployDatabaseDialog.generateScript = async (proj, prof) => await this.executionCallback(proj, prof);
deployDatabaseDialog.readPublishProfile = async (profileUri) => await this.readPublishProfile(profileUri);

deployDatabaseDialog.openDialog();

Expand All @@ -216,6 +218,27 @@ export class ProjectsController {
}
}

public async readPublishProfile(profileUri: Uri): Promise<PublishSettings> {
const profileText = await fs.readFile(profileUri.fsPath);
const profileXmlDoc = new xmldom.DOMParser().parseFromString(profileText.toString());

// read target database name
let targetDbName: string = '';
let targetDatabaseNameCount = profileXmlDoc.documentElement.getElementsByTagName(constants.targetDatabaseName).length;
if (targetDatabaseNameCount > 0) {
// if there is more than one TargetDatabaseName nodes, SSDT uses the name in the last one so we'll do the same here
targetDbName = profileXmlDoc.documentElement.getElementsByTagName(constants.targetDatabaseName)[targetDatabaseNameCount - 1].textContent;
}

// get all SQLCMD variables to include from the profile
let sqlCmdVariables = utils.readSqlCmdVariables(profileXmlDoc);

return {
databaseName: targetDbName,
sqlCmdVariables: sqlCmdVariables
};
}

public async schemaCompare(treeNode: BaseProjectTreeItem): Promise<void> {
// check if schema compare extension is installed
if (this.apiWrapper.getExtension(constants.schemaCompareExtensionId)) {
Expand Down
108 changes: 106 additions & 2 deletions extensions/sql-database-projects/src/dialogs/deployDatabaseDialog.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -28,15 +28,19 @@ export class DeployDatabaseDialog {
private targetDatabaseTextBox: azdata.InputBoxComponent | undefined;
private connectionsRadioButton: azdata.RadioButtonComponent | undefined;
private dataSourcesRadioButton: azdata.RadioButtonComponent | undefined;
private loadProfileButton: azdata.ButtonComponent | undefined;
private sqlCmdVariablesTable: azdata.TableComponent | undefined;
private formBuilder: azdata.FormBuilder | undefined;

private connection: azdata.connection.Connection | undefined;
private connectionIsDataSource: boolean | undefined;
private profileSqlCmdVars: Record<string, string> | undefined;

private toDispose: vscode.Disposable[] = [];

public deploy: ((proj: Project, profile: IDeploymentProfile) => any) | undefined;
public generateScript: ((proj: Project, profile: IGenerateScriptProfile) => any) | undefined;
public readPublishProfile: ((profileUri: vscode.Uri) => any) | undefined;

constructor(private apiWrapper: ApiWrapper, private project: Project) {
this.dialog = azdata.window.createModelViewDialog(constants.deployDialogName);
Expand Down Expand Up @@ -87,6 +91,21 @@ export class DeployDatabaseDialog {
this.tryEnableGenerateScriptAndOkButtons();
});

this.loadProfileButton = this.createLoadProfileButton(view);
this.sqlCmdVariablesTable = view.modelBuilder.table().withProperties({
title: constants.sqlCmdTableLabel,
data: this.convertSqlCmdVarsToTableFormat(this.project.sqlCmdVariables),
columns: [
{
value: constants.sqlCmdVariableColumn
},
{
value: constants.sqlCmdValueColumn,
}],
width: 400,
height: 400
}).component();

this.formBuilder = <azdata.FormBuilder>view.modelBuilder.formContainer()
.withFormItems([
{
Expand All @@ -100,6 +119,10 @@ export class DeployDatabaseDialog {
{
title: constants.databaseNameLabel,
component: this.targetDatabaseTextBox
},
{
title: constants.profileWarningText,
component: <azdata.ButtonComponent>this.loadProfileButton
}
]
}
Expand All @@ -110,6 +133,15 @@ export class DeployDatabaseDialog {
width: '100%'
});

// add SQLCMD variables table if the project has any
if (Object.keys(this.project.sqlCmdVariables).length > 0) {
this.formBuilder.insertFormItem({
title: constants.sqlCmdTableLabel,
component: <azdata.TableComponent>this.sqlCmdVariablesTable
},
6);
}

let formModel = this.formBuilder.component();
await view.initializeModel(formModel);
});
Expand Down Expand Up @@ -160,11 +192,12 @@ export class DeployDatabaseDialog {
}

public async deployClick(): Promise<void> {
const sqlCmdVars = this.getSqlCmdVariablesForDeploy();
const profile: IDeploymentProfile = {
databaseName: this.getTargetDatabaseName(),
upgradeExisting: true,
connectionUri: await this.getConnectionUri(),
sqlCmdVariables: this.project.sqlCmdVariables
sqlCmdVariables: sqlCmdVars
};

this.apiWrapper.closeDialog(this.dialog);
Expand All @@ -174,10 +207,11 @@ export class DeployDatabaseDialog {
}

public async generateScriptClick(): Promise<void> {
const sqlCmdVars = this.getSqlCmdVariablesForDeploy();
const profile: IGenerateScriptProfile = {
databaseName: this.getTargetDatabaseName(),
connectionUri: await this.getConnectionUri(),
sqlCmdVariables: this.project.sqlCmdVariables
sqlCmdVariables: sqlCmdVars
};

this.apiWrapper.closeDialog(this.dialog);
Expand All @@ -189,6 +223,18 @@ export class DeployDatabaseDialog {
this.dispose();
}

private getSqlCmdVariablesForDeploy(): Record<string, string> {
// get SQLCMD variables from project
let sqlCmdVariables = { ...this.project.sqlCmdVariables };

// update with SQLCMD variables loaded from profile if there are any
for (const key in this.profileSqlCmdVars) {
sqlCmdVariables[key] = this.profileSqlCmdVars[key];
}

return sqlCmdVariables;
}

public getTargetDatabaseName(): string {
return this.targetDatabaseTextBox?.value ?? '';
}
Expand Down Expand Up @@ -344,6 +390,64 @@ export class DeployDatabaseDialog {
return clearButton;
}

private createLoadProfileButton(view: azdata.ModelView): azdata.ButtonComponent {
let loadProfileButton: azdata.ButtonComponent = view.modelBuilder.button().withProperties({
label: constants.loadProfileButtonText,
title: constants.loadProfileButtonText,
ariaLabel: constants.loadProfileButtonText,
width: '120px'
}).component();

loadProfileButton.onDidClick(async () => {
const fileUris = await this.apiWrapper.showOpenDialog(
{
canSelectFiles: true,
canSelectFolders: false,
canSelectMany: false,
defaultUri: vscode.Uri.parse(this.project.projectFolderPath),
filters: {
[constants.publishSettingsFiles]: ['publish.xml']
}
}
);

if (!fileUris || fileUris.length === 0) {
return;
}

if (this.readPublishProfile) {
const result = await this.readPublishProfile(fileUris[0]);
(<azdata.InputBoxComponent>this.targetDatabaseTextBox).value = result.databaseName;
this.profileSqlCmdVars = result.sqlCmdVariables;
const data = this.convertSqlCmdVarsToTableFormat(this.getSqlCmdVariablesForDeploy());

(<azdata.TableComponent>this.sqlCmdVariablesTable).updateProperties({
data: data
});

// add SQLCMD Variables table if it wasn't there before
if (Object.keys(this.project.sqlCmdVariables).length === 0) {
this.formBuilder?.insertFormItem({
title: constants.sqlCmdTableLabel,
component: <azdata.TableComponent>this.sqlCmdVariablesTable
},
6);
}
}
});

return loadProfileButton;
}

private convertSqlCmdVarsToTableFormat(sqlCmdVars: Record<string, string>): string[][] {
let data = [];
for (let key in sqlCmdVars) {
data.push([key, sqlCmdVars[key]]);
}

return data;
}

// only enable Generate Script and Ok buttons if all fields are filled
private tryEnableGenerateScriptAndOkButtons(): void {
if (this.targetConnectionTextBox!.value && this.targetDatabaseTextBox!.value
Expand Down
5 changes: 5 additions & 0 deletions extensions/sql-database-projects/src/models/IDeploymentProfile.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -15,3 +15,8 @@ export interface IGenerateScriptProfile {
connectionUri: string;
sqlCmdVariables?: Record<string, string>;
}

export interface PublishSettings {
databaseName: string;
sqlCmdVariables: Record<string, string>;
}
8 changes: 1 addition & 7 deletions extensions/sql-database-projects/src/models/project.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -64,13 +64,7 @@ export class Project {
}

// find all SQLCMD variables to include
for (let i = 0; i < this.projFileXmlDoc.documentElement.getElementsByTagName(constants.SqlCmdVariable).length; i++) {
const sqlCmdVar = this.projFileXmlDoc.documentElement.getElementsByTagName(constants.SqlCmdVariable)[i];
const varName = sqlCmdVar.getAttribute(constants.Include);

const varValue = sqlCmdVar.getElementsByTagName(constants.DefaultValue)[0].childNodes[0].nodeValue;
this.sqlCmdVariables[varName] = varValue;
}
this.sqlCmdVariables = utils.readSqlCmdVariables(this.projFileXmlDoc);

// find all database references to include
for (let r = 0; r < this.projFileXmlDoc.documentElement.getElementsByTagName(constants.ArtifactReference).length; r++) {
Expand Down
2 changes: 2 additions & 0 deletions extensions/sql-database-projects/src/test/baselines/baselines.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -19,6 +19,7 @@ export let SSDTUpdatedProjectAfterSystemDbUpdateBaselineWindows: string;
export let SSDTUpdatedProjectAfterSystemDbUpdateBaseline: string;
export let SSDTProjectBaselineWithCleanTarget: string;
export let SSDTProjectBaselineWithCleanTargetAfterUpdate: string;
export let publishProfileBaseline: string;

const baselineFolderPath = __dirname;

Expand All @@ -35,6 +36,7 @@ export async function loadBaselines() {
SSDTUpdatedProjectAfterSystemDbUpdateBaseline = await loadBaseline(baselineFolderPath, 'SSDTUpdatedProjectAfterSystemDbUpdateBaseline.xml');
SSDTProjectBaselineWithCleanTarget = await loadBaseline(baselineFolderPath, 'SSDTProjectBaselineWithCleanTarget.xml');
SSDTProjectBaselineWithCleanTargetAfterUpdate = await loadBaseline(baselineFolderPath, 'SSDTProjectBaselineWithCleanTargetAfterUpdate.xml');
publishProfileBaseline = await loadBaseline(baselineFolderPath, 'publishProfileBaseline.publish.xml');
}

async function loadBaseline(baselineFolderPath: string, fileName: string): Promise<string> {
Expand Down
15 changes: 15 additions & 0 deletions extensions/sql-database-projects/src/test/baselines/publishProfileBaseline.publish.xml
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,15 @@
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>targetDb</TargetDatabaseName>
<DeployScriptFileName>DatabaseProject1.sql</DeployScriptFileName>
<ProfileVersionNumber>1</ProfileVersionNumber>
</PropertyGroup>
<ItemGroup>
<SqlCmdVariable Include="ProdDatabaseName">
<DefaultValue>MyProdDatabase</DefaultValue>
<Value>$(SqlCmdVar__1)</Value>
</SqlCmdVariable>
</ItemGroup>
</Project>
24 changes: 24 additions & 0 deletions extensions/sql-database-projects/src/test/projectController.test.ts
View file
Original file line number Diff line number Diff line change
Expand Up @@ -117,6 +117,7 @@ describe.skip('ProjectsController: project controller operations', function ():

const deployHoller = 'hello from callback for deploy()';
const generateHoller = 'hello from callback for generateScript()';
const profileHoller = 'hello from callback for readPublishProfile()';

let holler = 'nothing';

Expand All @@ -131,6 +132,13 @@ describe.skip('ProjectsController: project controller operations', function ():
holler = deployHoller;
return undefined;
});
projController.setup(x => x.readPublishProfile(TypeMoq.It.isAny())).returns(async () => {
holler = profileHoller;
return {
databaseName: '',
sqlCmdVariables: {}
};
});

projController.setup(x => x.executionCallback(TypeMoq.It.isAny(), TypeMoq.It.is((_): _ is IGenerateScriptProfile => true))).returns(async () => {
holler = generateHoller;
Expand All @@ -146,6 +154,22 @@ describe.skip('ProjectsController: project controller operations', function ():
await dialog.generateScriptClick();

should(holler).equal(generateHoller, 'executionCallback() is supposed to have been setup and called for GenerateScript scenario');

dialog = await projController.object.deployProject(proj);
await projController.object.readPublishProfile(vscode.Uri.parse('test'));

should(holler).equal(profileHoller, 'executionCallback() is supposed to have been setup and called for ReadPublishProfile scenario');
});

it('Should read database name and SQLCMD variables from publish profile', async function (): Promise<void> {
await baselines.loadBaselines();
let profilePath = await testUtils.createTestFile(baselines.publishProfileBaseline, 'publishProfile.publish.xml');
const projController = new ProjectsController(testContext.apiWrapper.object, new SqlDatabaseProjectTreeViewProvider());

let result = await projController.readPublishProfile(vscode.Uri.parse(profilePath));
should(result.databaseName).equal('targetDb');
should(Object.keys(result.sqlCmdVariables).length).equal(1);
should(result.sqlCmdVariables['ProdDatabaseName']).equal('MyProdDatabase');
});
});
});
Expand Down
Loading

0 comments on commit 6ad33aa

Please sign in to comment.