public override void DoOperation(GlyphDefinition glyph)
{
foreach (var replacement in _replacements)
{
try
{
var span = _snapshot.CreateTrackingSpan(replacement.OriginalOffset, replacement.OriginalLength, SpanTrackingMode.EdgeNegative).GetSpan(_snapshot);
if (span.GetText() != replacement.Original)
{
return;
}
var newSpan = span.Snapshot.CreateTrackingSpan(span.Start, replacement.OriginalLength, SpanTrackingMode.EdgeNegative);
_snapshot.TextBuffer.Replace(newSpan.GetSpan(newSpan.TextBuffer.CurrentSnapshot), replacement.Replacement);
}
catch (Exception e)
{
OutputPane.WriteMessage("error unable to do replacement : {0}", e);
}
}
glyph.Tag.Tagger.Reset();
}
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);
}
}
private void CreatetSQLtSchema(object sender, EventArgs e)
{
try
{
var dte = (DTE)GetService(typeof(DTE));
if (dte.ActiveDocument == null)
{
return;
}
var doc = dte.ActiveDocument.Object("TextDocument") as TextDocument;
if (null == doc)
{
return;
}
var ep = doc.StartPoint.CreateEditPoint();
ep.EndOfDocument();
var length = ep.AbsoluteCharOffset;
ep.StartOfDocument();
var originalText = ep.GetText(length);
var builder = new SchemaBuilder(originalText);
builder.CreateSchemas();
}
catch (Exception ex)
{
OutputPane.WriteMessage("Exception creating tSQLt schema, error: {0}", ex.Message);
}
}
private void LowerCase(object sender, EventArgs e)
{
try
{
CallWrapper();
var text = GetCurrentDocumentText();
if (String.IsNullOrEmpty(text))
{
return;
}
var newText = KeywordCaser.KeywordsToLower(text);
if (text != newText)
{
SetCurrentDocumentText(newText);
OutputPane.WriteMessage("Changed keywords to lower case");
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Exception changing keywords to UPPER CASE, error: {0}", ex.Message);
}
}
private void FindDuplicateIndexes(object sender, EventArgs e)
{
try
{
CallWrapper();
var task = new System.Threading.Tasks.Task(() =>
{
OutputPane.WriteMessageAndActivatePane("Finding Duplicate Indexes...");
var finder = new DuplicateIndexFinder();
finder.ShowDuplicateIndexes();
OutputPane.WriteMessageAndActivatePane("Finding Duplicate Indexes...done");
});
task.Start();
if (task.Exception != null)
{
throw task.Exception;
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error finding duplicate indexes: {0}", ex.Message);
}
}
private void ExtractToTvf(object sender, EventArgs e)
{
try
{
var dte = (DTE)GetService(typeof(DTE));
if (dte.ActiveDocument == null)
{
return;
}
var doc = dte.ActiveDocument;
var text = GetCurrentText();
if (String.IsNullOrEmpty(text))
{
return;
}
var newText = new CodeExtractor(text).ExtractIntoFunction();
if (text != newText && !String.IsNullOrEmpty(newText))
{
doc.Activate();
SetCurrentText(newText);
OutputPane.WriteMessage("Code extracted into an inline table valued function");
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error extracting code into a TVF: {0}", ex.Message);
}
}
private void FindDuplicateIndexes(object sender, EventArgs e)
{
try
{
var finder = new DuplicateIndexFinder();
finder.ShowDuplicateIndexes();
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error finding duplicate indexes: {0}", ex.Message);
}
}
private void CorrectCaseTableNames(object sender, EventArgs e)
{
try
{
var finder = new CorrectCaseTableFinder();
finder.CorrectCaseAllTableNames();
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error correcting table name case: {0}", ex.Message);
}
}
private void QuickDeploy(object sender, EventArgs e)
{
try
{
QuickDeployer.DeployFile(GetCurrentDocumentText());
}
catch (Exception ex)
{
OutputPane.WriteMessage("QuickDeploy error: {0}", ex.Message);
Log.WriteInfo("QuickDeploy error: {0}", ex.Message);
}
}
private void QuickDeployToClipboard(object sender, EventArgs e)
{
try
{
var script = QuickDeployer.GetDeployScript(GetCurrentDocumentText());
Clipboard.SetText(script, TextDataFormat.UnicodeText);
}
catch (Exception ex)
{
OutputPane.WriteMessage("QuickDeployToClipboard error: {0}", ex.Message);
Log.WriteInfo("QuickDeployToClipboard error: {0}", ex.Message);
}
}
private void QuickDeployClearConnection(object sender, EventArgs e)
{
try
{
QuickDeployer.ClearConnectionString();
OutputPane.WriteMessageAndActivatePane("Quick Deploy Connection String has been cleared.");
}
catch (Exception ex)
{
OutputPane.WriteMessage("QuickDeployClearConnection error: {0}", ex.Message);
Log.WriteInfo("QuickDeployClearConnection error: {0}", ex.Message);
}
}
private void ToggleQueryCosts(object sender, EventArgs e)
{
try
{
var dte = (DTE)GetService(typeof(DTE));
if (dte.ActiveDocument == null)
{
return;
}
var doc = dte.ActiveDocument.Object("TextDocument") as TextDocument;
if (null == doc)
{
return;
}
var ep = doc.StartPoint.CreateEditPoint();
ep.EndOfDocument();
var length = ep.AbsoluteCharOffset;
ep.StartOfDocument();
var originalText = ep.GetText(length);
DocumentScriptCosters.SetDte(dte);
var coster = DocumentScriptCosters.GetInstance().GetCoster();
if (coster == null)
{
return;
}
if (coster.ShowCosts)
{
coster.ShowCosts = false;
}
else
{
coster.ShowCosts = true;
coster.AddCosts(originalText, dte.ActiveDocument);
}
}
catch (Exception ee)
{
OutputPane.WriteMessage("ToggleQueryCosts error: {0}", ee.Message);
Log.WriteInfo("ToggleQueryCosts error: {0}", ee.Message);
}
}
public static void DeployFile(string newCode)
{
if (String.IsNullOrEmpty(ConnectionString))
{
var connectDialog = new ConnectDialog();
connectDialog.ShowDialog();
ConnectionString = connectDialog.ConnectionString;
if (String.IsNullOrEmpty(ConnectionString))
{
return;
}
}
var procedures = ScriptDom.GetProcedures(newCode);
var deployScripts = new List <string>();
foreach (var procedure in procedures)
{
OutputPane.WriteMessage("Deploying {0}", procedure.ProcedureReference.Name.ToQuotedString());
Deploy(BuildIfNotExistsStatements(procedure));
Deploy(ChangeCreateToAlter(procedure, newCode));
OutputPane.WriteMessage("Deploying {0}...Done", procedure.ProcedureReference.Name.ToQuotedString());
}
var functions = ScriptDom.GetFunctions(newCode);
foreach (var function in functions)
{
OutputPane.WriteMessage("Deploying {0}", function.Name.ToQuotedString());
if (function.ReturnType is SelectFunctionReturnType)
{
Deploy(BuildIfNotExistsStatementsInlineFunction(function));
}
else
{
Deploy(BuildIfNotExistsStatements(function));
}
Deploy(ChangeCreateToAlter(function, newCode));
OutputPane.WriteMessage("Deploying {0}...Done", function.Name.ToQuotedString());
}
foreach (var statement in deployScripts)
{
Deploy(statement);
}
//Deploy();
}
//NOT ALL KEYWORDS ARE done LIKE "RETURN" or datatypes
private void UpperCase(object sender, EventArgs e)
{
var text = GetCurrentDocumentText();
if (String.IsNullOrEmpty(text))
{
return;
}
var newText = KeywordCaser.KeywordsToUpper(text);
if (text != newText)
{
SetCurrentDocumentText(newText);
OutputPane.WriteMessage("Changed keywords to UPPER CASE");
}
}
private static void Deploy(string statement)
{
try
{
using (var con = new SqlConnection(ConnectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = statement;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error Deploying File: {0}\r\n", ex.Message);
}
}
private void NameConstraintsCalled(object sender, EventArgs e)
{
try
{
CallWrapper();
var dte = (DTE)GetService(typeof(DTE));
if (null == dte || dte.ActiveDocument == null)
{
return;
}
var doc = dte.ActiveDocument.Object("TextDocument") as TextDocument;
if (null == doc)
{
return;
}
var ep = doc.StartPoint.CreateEditPoint();
ep.EndOfDocument();
var length = ep.AbsoluteCharOffset;
ep.StartOfDocument();
var originalText = ep.GetText(length);
var namer = new ConstraintNamer(originalText);
var modifiedText = namer.Go();
if (originalText != modifiedText)
{
ep.Delete(length);
ep.Insert(modifiedText);
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Exception naming constraints, error: {0}", ex.Message);
}
}
public void ShowDuplicateIndexes()
{
var statements = new StatementEnumerator().GetIndexes();
var indexes = new Dictionary <string, List <CodeStatement <CreateIndexStatement> > >();
foreach (var statement in statements)
{
var key = BuildKey(statement.Statement);
if (indexes.ContainsKey(key))
{
indexes[key].Add(statement);
}
else
{
indexes[key] = new List <CodeStatement <CreateIndexStatement> > {
statement
};
}
}
var dups = indexes.Where(p => p.Value.Count > 1);
foreach (var d in dups)
{
OutputPane.WriteMessage("Duplicate Indexes Found: ");
foreach (var statement in d.Value)
{
OutputPane.WriteMessageWithLink(statement.FileName, statement.Line, "{0}",
ScriptDom.GenerateTSql(statement.Statement));
}
}
if (dups == null || !dups.Any())
{
OutputPane.WriteMessage("No Duplicate Indexes Found.");
}
}
public static string GetDeployScript(string newCode)
{
var procedures = ScriptDom.GetProcedures(newCode);
var deployScripts = new List <string>();
foreach (var procedure in procedures)
{
OutputPane.WriteMessage("Generating Deploy Script for {0}", procedure.ProcedureReference.Name.ToQuotedString());
deployScripts.Add(BuildIfNotExistsStatements(procedure));
deployScripts.Add(ChangeCreateToAlter(procedure, newCode));
}
var functions = ScriptDom.GetFunctions(newCode);
foreach (var function in functions)
{
OutputPane.WriteMessage("Generating script for {0}", function.Name.ToQuotedString());
if (function.ReturnType is SelectFunctionReturnType)
{
deployScripts.Add(BuildIfNotExistsStatementsInlineFunction(function));
}
else
{
deployScripts.Add(BuildIfNotExistsStatements(function));
}
deployScripts.Add(ChangeCreateToAlter(function, newCode));
}
StringBuilder script = new StringBuilder();
foreach (var statement in deployScripts)
{
script.AppendFormat("{0}\r\nGO\r\n", statement);
}
return(script.ToString());
}
private void CreatetSQLtTest(object sender, EventArgs e)
{
try
{
CallWrapper();
var dte = (DTE)GetService(typeof(DTE));
if (dte.ActiveDocument == null)
{
return;
}
var doc = dte.ActiveDocument.Object("TextDocument") as TextDocument;
if (null == doc)
{
return;
}
var ep = doc.StartPoint.CreateEditPoint();
ep.EndOfDocument();
var length = ep.AbsoluteCharOffset;
ep.StartOfDocument();
var originalText = ep.GetText(length);
var builder = new TestBuilder(originalText, dte.ActiveDocument.ProjectItem.ContainingProject);
builder.Go();
// builder.CreateTests();
}
catch (Exception ex)
{
OutputPane.WriteMessage("Exception creating tSQLt tests, error: {0}", ex.Message);
}
}
private void CorrectCaseTableNames(object sender, EventArgs e)
{
try
{
var task = new System.Threading.Tasks.Task(() =>
{
OutputPane.WriteMessageAndActivatePane("Correcting the case of table names...");
var finder = new CorrectCaseTableFinder();
finder.CorrectCaseAllTableNames();
OutputPane.WriteMessageAndActivatePane("Correcting the case of table names...done");
});
task.Start();
if (task.Exception != null)
{
throw task.Exception;
}
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error correcting table name case: {0}", ex.Message);
}
}
public void CorrectCaseAllTableNames()
{
var statements = new StatementEnumerator().GetStatements();
var dacTables = new List <TableDescriptor>();
foreach (var project in new ProjectEnumerator().Get(ProjectType.SSDT))
{
try
{
var path = DacpacPath.Get(project);
dacTables.AddRange(new TableRepository(path).Get());
}
catch (Exception ex)
{
OutputPane.WriteMessage("Error getting list of tables in project: {0}, error: {1}", project.Name, ex.Message);
}
}
var alreadyChanged = new Dictionary <string, object>();
foreach (var statement in statements.OrderByDescending(p => p.Statement.StartOffset))
{
var scriptTables = ScriptDom.GetTableList(statement.Statement).Where(p => p is NamedTableReference).Cast <NamedTableReference>().ToList();
var rewriter = new TableReferenceRewriter(statement.Script, scriptTables);
var replacements = rewriter.GetReplacements(dacTables);
var approvedReplacements = new List <Replacements>();
foreach (var replacement in replacements)
{
var key = string.Format("{0}:{1}:{2}", statement.FileName, replacement.OriginalOffset, replacement.OriginalLength);
if (alreadyChanged.ContainsKey(key))
{
continue;
}
approvedReplacements.Add(replacement);
alreadyChanged[key] = null;
}
if (approvedReplacements.Count > 0)
{
var script = File.ReadAllText(statement.FileName);
OutputPane.WriteMessage("File: {0}", statement.FileName);
foreach (var replacement in approvedReplacements.OrderByDescending(p => p.OriginalOffset))
{
var from = script.Substring(replacement.OriginalOffset, replacement.OriginalLength);
var to = replacement.Replacement;
OutputPane.WriteMessageWithLink(statement.FileName, statement.Line, "\tChanging case of {0} to {1}", from, to);
script = script.Substring(0, replacement.OriginalOffset) + to + script.Substring(replacement.OriginalOffset + replacement.OriginalLength);
}
File.WriteAllText(statement.FileName, script);
}
}
}
