public void ShouldInsertFromProcedureResult()
{
var selectedQuery = "SELECT * FROM Customer;";
var queryResult = new DataTable();
queryResult.Columns.Add(new DataColumn {
ColumnName = "CustomerID", DataType = typeof(long)
});
queryResult.Columns.Add(new DataColumn {
ColumnName = "Name", DataType = typeof(string)
});
var row = queryResult.NewRow();
row.SetField(queryResult.Columns[0], 150L);
row.SetField(queryResult.Columns[1], "Skogshuggaren");
queryResult.Rows.Add(row);
var targetTable = ObjectMetadata.FromQualifiedString("tempdb.dbo.#Actual");
var queryGen = new QueryBuilder();
var actual = queryGen.CreateTempTableFor(queryResult, targetTable)
.InsertAllColumns(queryResult, targetTable)
.Exec(selectedQuery)
.Build();
string expected = @"CREATE TABLE [tempdb].[dbo].[#Actual] (
" + "\t" + @"[CustomerID] BIGINT,
" + "\t" + @"[Name] NVARCHAR(MAX)
);
INSERT INTO [tempdb].[dbo].[#Actual] ([CustomerID], [Name])
" + selectedQuery;
Assert.That(actual, Is.EqualTo(expected));
}
private ObjectMetadata GetTableMetaFromSelectedText()
{
string selectedText = ShellManager.GetSelectedText();
var meta = ObjectMetadata.FromQualifiedString(selectedText);
return(meta);
}
public void ShouldGetObjectDependencies()
{
var token = new CancellationTokenSource().Token;
var service = new ObjectDependencyService();
var meta = ObjectMetadata.FromQualifiedString("ETL.fillCountry");
var result = service.GetDependencies(meta, GetLocalConnection(), token);
}
public void ShouldUnwrapNames()
{
var tableA = ObjectMetadata.FromQualifiedString("[MYDB].[MySchema].[MockingTable]");
Assert.That(tableA.ObjectName, Is.EqualTo("MockingTable"));
Assert.That(tableA.SchemaName, Is.EqualTo("MySchema"));
Assert.That(tableA.DatabaseName, Is.EqualTo("MYDB"));
}
public void ShouldGetTableDefinitionFromTableName()
{
var tableA = ObjectMetadata.FromQualifiedString("msdb.[dbo].[syscategories]");
ObjectMetadataAccess da = new ObjectMetadataAccess(GetLocalConnection());
var token = new CancellationTokenSource().Token;
var actual = da.SelectTopNFrom(tableA, token);
Assert.That(actual.Columns.Count, Is.EqualTo(4));
}
public static string GetFakeTableStatement(string selectedText)
{
if (string.IsNullOrEmpty(selectedText))
{
throw new ArgumentException("Selected text is empty");
}
var table = ObjectMetadata.FromQualifiedString(selectedText);
return(FakeTable(table));
}
public void ShouldGetFullyQualifiedNamesFromObjectMetadata()
{
var tableA = ObjectMetadata.FromQualifiedString("[MYDB].[MySchema].[MockingTable]").ToFullString();
var tableB = ObjectMetadata.FromQualifiedString("[MySchema].[MockingTable]").ToFullString();
var tableC = ObjectMetadata.FromQualifiedString("[MockingTable]").ToFullString();
Assert.That(tableA, Is.EqualTo("[MYDB].[MySchema].[MockingTable]"));
Assert.That(tableB, Is.EqualTo("[MySchema].[MockingTable]"));
Assert.That(tableC, Is.EqualTo("[dbo].[MockingTable]"));
}
public void ShouldOutputFakeTableWithInserts()
{
var tableMeta = ObjectMetadata.FromQualifiedString("dbo.Customer");
var queryGen = new QueryBuilder();
var actual = queryGen.MockTable(tableMeta).Build();
string expected = @"EXEC tSQLt.FakeTable '[dbo].[Customer]';";
Assert.That(actual, Is.EqualTo(expected));
}
public void ShouldParseTempDbName()
{
var input = "#Temp";
var tableA = ObjectMetadata.FromQualifiedString(input);
Assert.That(tableA.ObjectName, Is.EqualTo("#Temp"));
Assert.That(tableA.SchemaName, Is.EqualTo(""));
Assert.That(tableA.DatabaseName, Is.EqualTo("Tempdb"));
Assert.That(tableA.ToFullString(), Is.EqualTo("[Tempdb]..[#Temp]"));
}
public void ShouldMockAllObjectDependencies()
{
var token = new CancellationTokenSource().Token;
var service = new ObjectDependencyService();
var options = new PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary();
options.EachColumnInSelectOnNewRow = false;
options.EachColumnInValuesOnNewRow = false;
var meta = ObjectMetadata.FromQualifiedString("ETL.fillCountry");
var deps = service.GetDependencies(meta, GetLocalConnection(), token);
var res = TsqltManager.MockAllDependencies(token, options, GetLocalConnection(), deps);
Console.WriteLine(res.ToString());
}
public void ShouldMockHierarchialIDValues()
{
// Due to bug
var token = new CancellationTokenSource().Token;
var service = new ObjectDependencyService();
var options = new PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary();
options.EachColumnInSelectOnNewRow = false;
options.EachColumnInValuesOnNewRow = false;
var meta = ObjectMetadata.FromQualifiedString("REPORT.70_getShortRiskDetailed_IRT");
var deps = service.GetDependencies(meta, GetLocalConnection(), token);
var res = TsqltManager.MockAllDependencies(token, options, GetLocalConnection(), deps);
Console.WriteLine(res.ToString());
}
public override void ExecuteCommand(System.Threading.CancellationToken token)
{
var selectedText = ShellManager.GetSelectedText();
var options = new PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary();
options.EachColumnInSelectOnNewRow = false;
options.EachColumnInValuesOnNewRow = false;
var connectionString = ConnectionManager.GetConnectionStringForCurrentWindow();
var meta = ObjectMetadata.FromQualifiedString(selectedText);
var service = new ObjectDependencyService();
var dependencies = service.GetDependencies(meta, connectionString, token);
var superMockingString = TsqltManager.MockAllDependencies(token, options, connectionString, dependencies);
ShellManager.AppendToEndOfSelection(superMockingString);
}
public void ShouldGenerateInsertIntoMockTable()
{
var meta = ObjectMetadata.FromQualifiedString("msdb.[dbo].[syscategories]");
ObjectMetadataAccess da = new ObjectMetadataAccess(GetLocalConnection());
var token = new CancellationTokenSource().Token;
var table = da.SelectTopNFrom(meta, token);
var actual = TsqltManager.GenerateInsertFor(table, meta);
var expected = @"INSERT INTO [msdb].[dbo].[syscategories] (
[category_id],
[category_class],
[category_type],
[name]
)
" + "VALUES\t(0, 1, 1, '[Uncategorized (Local)]');";
Console.WriteLine(expected);
Console.WriteLine(actual);
Assert.That(actual, Is.EqualTo(expected));
}
public override void ExecuteCommand(CancellationToken token)
{
var selectedText = ShellManager.GetSelectedText();
var meta = ObjectMetadata.FromQualifiedString(selectedText);
var fileName = string.Format("{0}.{1}.sql", meta.SchemaName, meta.ObjectName);
var sourceFolder = PluginOptions["Source Base Path"];
var files = Directory.GetFiles(sourceFolder, fileName, SearchOption.AllDirectories);
if (files.Length > 0)
{
for (int i = 0; i < files.Length; i++)
{
ShellManager.OpenFile(fileName: files[i], newWindow: true);
}
}
else
{
throw new FileNotFoundException("The script file for: " + selectedText + " could not be found in " + sourceFolder);
}
}
public override void ExecuteCommand(CancellationToken token)
{
var options = new PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary();
var ok = new Action <string, PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary>((result, checkedOptions) =>
{
int numRows = 0;
if (!int.TryParse(result, out numRows))
{
ShellManager.ShowMessageBox("Please input a valid number");
return;
}
else
{
if (numRows <= 0)
{
numRows = 0;
}
else if (numRows > 1000)
{
numRows = 1000;
}
}
var selectedText = ShellManager.GetSelectedText();
StringBuilder sb = new StringBuilder();
var connectionString = ConnectionManager.GetConnectionStringForCurrentWindow();
var meta = ObjectMetadata.FromQualifiedString(selectedText);
sb.AppendLine(TsqltManager.MockTableWithRows(token, options, numRows, meta, connectionString));
ShellManager.ReplaceSelectionWith(sb.ToString());
});
var diagManager = new DialogManager.InputWithCheckboxesDialogManager <PeterHenell.SSMS.Plugins.Utils.TsqltManager.MockOptionsDictionary>();
diagManager.Show("How many rows to select? (0=max)", "1", options, ok, cancelCallback);
}
public void ShouldTypeFluentlyToGetGeneratedQueries()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn {
ColumnName = "CustomerID", DataType = typeof(long)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Name", DataType = typeof(string)
});
var tableMeta = ObjectMetadata.FromQualifiedString("tempdb.dbo.#Actual");
var targetTable = ObjectMetadata.FromQualifiedString("tempdb.dbo.#Excpected");
var queryGen = new QueryBuilder();
string expected = @"SELECT [CustomerID], [Name]
INTO [tempdb].[dbo].[#Excpected]
FROM [tempdb].[dbo].[#Actual]
WHERE 1=0;";
string query = queryGen.SelectAllColumns(dt).Into(targetTable).From(tableMeta).Where("1=0").Build();
Assert.That(query, Is.EqualTo(expected));
}
public override void ExecuteCommand(CancellationToken token)
{
var options = new MockOptionsDictionary();
var ok = new Action <string, MockOptionsDictionary>((result, checkedOptions) =>
{
int numRows = 0;
if (!int.TryParse(result, out numRows))
{
ShellManager.ShowMessageBox("Please input a valid number");
return;
}
else
{
if (numRows <= 0)
{
numRows = 0;
}
else if (numRows > 1000)
{
numRows = 1000;
}
}
string selectedText = ShellManager.GetSelectedText();
var sb = new StringBuilder();
using (var ds = new DataSet())
{
QueryManager.Run(ConnectionManager.GetConnectionStringForCurrentWindow(), token, (queryManager) =>
{
queryManager.Fill(string.Format("SET ROWCOUNT {0}; {1}", numRows, selectedText), ds);
});
if (ds.Tables.Count == 1)
{
sb.AppendDropTempTableIfExists("#Actual");
sb.AppendLine();
sb.AppendDropTempTableIfExists("#Expected");
sb.AppendLine();
sb.AppendTempTablesFor(ds.Tables[0], "#Actual");
sb.Append("INSERT INTO #Actual");
ShellManager.AddTextToTopOfSelection(sb.ToString());
sb.Clear();
sb.AppendColumnNameList(ds.Tables[0]);
ShellManager.AppendToEndOfSelection(
string.Format("{0}SELECT {1}INTO #Expected{0}FROM #Actual{0}WHERE 1=0;{0}", Environment.NewLine, sb.ToString())
);
ShellManager.AppendToEndOfSelection(
TsqltManager.GenerateInsertFor(ds.Tables[0], ObjectMetadata.FromQualifiedString("#Expected"), false, false));
}
else
{
return;
}
}
//var meta = ObjectMetadata.FromQualifiedString(selectedText);
//ObjectMetadataAccess da = new ObjectMetadataAccess(ConnectionManager.GetConnectionStringForCurrentWindow());
//var table = da.SelectTopNFrom(meta, numRows);
//StringBuilder sb = new StringBuilder();
//sb.Append(TsqltManager.GetFakeTableStatement(selectedText));
//sb.AppendLine();
//sb.Append(TsqltManager.GenerateInsertFor(table, meta, options.EachColumnInSelectOnNewRow, options.EachColumnInValuesOnNewRow));
//shellManager.ReplaceSelectionWith(sb.ToString());
});
var diagManager = new DialogManager.InputWithCheckboxesDialogManager <MockOptionsDictionary>();
diagManager.Show("How many rows to select? (0=max)", "1", options, ok, cancelCallback);
}