public override GlyphDefinition GetDefintions(string fragment, TSqlStatement statement, GlyphDefinition definition, List<QuerySpecification> queries)
{
var nonSargableRewriter = new NonSargableRewrites(fragment);
var replacements = nonSargableRewriter.GetReplacements(queries);
if (replacements.Count > 0)
{
definition.Menu.Add(new MenuDefinition()
{
Caption = "Replace non-sargable IsNull",
Action = () => { },
Type = MenuItemType.Header
,
Glyph = definition
});
var offsettedReplacments = new List<Replacements>();
foreach (var replacement in replacements)
{
var replacement1 = replacement;
replacement1.OriginalOffset += statement.StartOffset;
offsettedReplacments.Add(replacement1);
}
if (replacements.Count > 1)
{
var menu = new MenuDefinition();
menu.Operation = new ClippyReplacementOperations(offsettedReplacments);
menu.Action = () => PerformAction(menu.Operation, menu.Glyph);
menu.Glyph = definition;
menu.Caption = GetCaptionForAll(statement);
menu.Type = MenuItemType.MenuItem;
definition.Menu.Add(menu);
}
foreach (var replacement in offsettedReplacments)
{
var menu = new MenuDefinition();
menu.Action = () => PerformAction(menu.Operation, menu.Glyph);
menu.Glyph = definition;
menu.Caption = string.Format("\t\"{0}\" into \"{1}\"", replacement.Original, replacement.Replacement);
menu.Type = MenuItemType.MenuItem;
menu.Operation = new ClippyReplacementOperation(replacement);
definition.Menu.Add(menu);
}
definition.GenerateKey();
}
return definition;
}
public void sargable_rewrites_isnull_not_equals_same_literal()
{
var script = @" select * from dbo.tableaaa
where isnull(a.a_column, 'abc') <> 'abc'
";
var rewriter = new NonSargableRewrites(script);
var replacements = rewriter.GetReplacements(ScriptDom.ScriptDom.GetQuerySpecifications(script));
Assert.AreEqual(1, replacements.Count);
Assert.AreEqual("isnull(a.a_column, 'abc') <> 'abc'", replacements.FirstOrDefault().Original);
Assert.AreEqual("(a.a_column is not null and a.a_column <> 'abc')", replacements.FirstOrDefault().Replacement);
}
public void sargable_rewrites_isnulls_after_unions()
{
var script = @" select * from dbo.tableaaa
where isnull(a_column, 'sss') <> 'abc' union all select * from dbo.tableaaa
where isnull(a_column, 'sss') <> 'abc'
";
var rewriter = new NonSargableRewrites(script);
var replacements = rewriter.GetReplacements(ScriptDom.ScriptDom.GetQuerySpecifications(script));
Assert.AreEqual(2, replacements.Count);
Assert.AreEqual("isnull(a_column, 'sss') <> 'abc'", replacements.FirstOrDefault().Original);
Assert.AreEqual("(a_column is null or a_column <> 'abc')", replacements.FirstOrDefault().Replacement);
Assert.AreEqual("isnull(a_column, 'sss') <> 'abc'", replacements.LastOrDefault().Original);
Assert.AreEqual("(a_column is null or a_column <> 'abc')", replacements.LastOrDefault().Replacement);
}
private void RewriteNonSargableIsNull(object sender, EventArgs e)
{
try
{
var oldDoc = GetCurrentDocumentText();
var newDoc = oldDoc;
var rewriter = new NonSargableRewrites(oldDoc);
var queries = ScriptDom.GetQuerySpecifications(oldDoc);
foreach (var rep in rewriter.GetReplacements(queries))
{
newDoc = newDoc.Replace(rep.Original, rep.Replacement);
OutputPane.WriteMessage("Non-Sargable IsNull re-written from \r\n\"{0}\" \r\nto\r\n\"{1}\"\r\n", rep.Original, rep.Replacement);
}
if(oldDoc != newDoc)
SetCurrentDocumentText(newDoc);
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error re-writing non sargable isnulls {0}", ex.Message);
}
}
