public void TestSaveAndRemoveItem()
{
var repository = new SqlRewriteDbRepository(new SqlConnection(Settings.Default.ConnectionString));
var item = new SqlRewriteRule
{
Id = 0, // This will cause insertion
DatabaseRegEx = ".*",
QueryMatchRegEx = "SELECT @@VERSION",
QueryReplacementString = "SELECT DBDATE()",
RegExOptions = 0
};
repository.SaveSqlRewriteRule(item);
try
{
Assert.AreNotEqual(0, item.Id);
var items = repository.LoadSqlRewriteRules();
ItemExists(items, item);
repository.RemoveSqlRewriteRule(item.Id);
items = repository.LoadSqlRewriteRules();
Assert.ThrowsException <Exception>(() => { ItemExists(items, item); });
}
finally
{
try
{
repository.RemoveSqlRewriteRule(item.Id);
}
catch (Exception)
{
// Ignore exceptions trying to remove to cleanup
}
}
}
public void TestBasicSqlRewriteRuleService()
{
using var conn = new SqlConnection(Settings.Default.ConnectionString);
var repo = new SqlRewriteDbRepository(conn);
var rule = new SqlRewriteRule
{
DatabaseRegEx = ".*", QueryMatchRegEx = Stm, QueryReplacementString = "SELECT GETDATE()"
};
repo.SaveSqlRewriteRule(rule);
using var service = new SqlRewriteRuleService(repo, true);
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using var cmd = new SqlCommand(Stm, con);
var version = cmd.ExecuteScalar().ToString();
Assert.IsFalse(version.Contains("Microsoft"));
service.RemoveRule(rule.Id);
service.RefreshRulesFromRepository();
using var con2 = new SqlConnection(Settings.Default.ConnectionString);
con2.Open();
using var cmd2 = new SqlCommand(Stm, con2);
version = cmd2.ExecuteScalar().ToString();
Assert.IsTrue(version.Contains("Microsoft"));
}
public void TestStoredProcRewrite()
{
SqlCommandInterceptor.Enabled = true;
var rules = new SqlRewriteRule[1];
rules[0] = new SqlRewriteRule
{
DatabaseRegEx = ".*",
QueryMatchRegEx = Stm,
QueryReplacementString = "sp_getsqlqueueversion"
};
SqlCommandRegExProcessor.SqlRewriteRules = rules;
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using var cmd = new SqlCommand(Stm, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.ExecuteScalar();
throw new Exception("Expecting exception missing parameter executing stored proc");
}
catch (SqlException e)
{
if (!e.Message.Contains("parameter"))
{
throw;
}
}
}
public void SaveSqlRewriteRule(SqlRewriteRule rule)
{
using var saveSqlRewriteRule = new SqlCommand(@"
MERGE SqlRewriteRegistry AS t
USING (
SELECT @ID, @DatabaseRegEx, @QueryMatchRegEx, @QueryReplacementString, @RegExOptions
) AS src (ID, DatabaseRegEx, QueryMatchRegEx, QueryReplacementString, RegExOptions)
ON (t.ID = src.ID)
WHEN MATCHED THEN
UPDATE SET
t.DatabaseRegEx = src.DatabaseRegEx,
t.QueryMatchRegEx = src.QueryMatchRegEx,
t.QueryReplacementString = src.QueryReplacementString,
t.RegExOptions = src.RegExOptions
WHEN NOT MATCHED THEN
INSERT (DatabaseRegEx, QueryMatchRegEx, QueryReplacementString, RegExOptions)
VALUES (src.DatabaseRegEx, src.QueryMatchRegEx, src.QueryReplacementString, src.RegExOptions)
OUTPUT Inserted.ID;", _sqlConnection);
saveSqlRewriteRule.Parameters.AddRange(new[]
{
new SqlParameter("@ID", rule.Id),
new SqlParameter("@DatabaseRegEx", rule.DatabaseRegEx),
new SqlParameter("@QueryMatchRegEx", rule.QueryMatchRegEx),
new SqlParameter("@QueryReplacementString", rule.QueryReplacementString),
new SqlParameter("@RegExOptions", rule.RegExOptions)
});
var result = saveSqlRewriteRule.ExecuteScalar();
if (result is DBNull)
{
throw new SqlRewriteRuleDbRepositoryException("Call to saveSqlRewriteRule.ExecuteScalar() should always return a non-null value");
}
rule.Id = (int)result;
}
private static void Main()
{
Console.TreatControlCAsInput = true;
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
repo.RemoveAllSqlRewriteRules();
var lastRefreshFromRepo = DateTime.Now;
using var svc = new SqlRewriteRuleService(repo, true)
{
AutoRefreshRulesAndSettingsEnabled = true
};
svc.AutoRefreshEvent += () => lastRefreshFromRepo = DateTime.Now;
using var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
SqlRewriteRule rule = null;
var input = "";
do
{
Console.Clear();
if (input != "")
{
rule ??= new SqlRewriteRule()
{
DatabaseRegEx = ".*",
QueryMatchRegEx = "SELECT @@VERSION"
};
rule.QueryReplacementString = $"{input} /*x*/";
repo.SaveSqlRewriteRule(rule);
}
PrintCurrentRuleSet(repo);
using var cmd = CreateSqlCommand(conn);
Console.WriteLine($"Current datetime: {DateTime.Now}");
Console.WriteLine($"Last refresh from repository: {lastRefreshFromRepo}");
Console.WriteLine($"Auto-refresh interval (milliseconds): {svc.AutoRefreshTimerInterval}");
Console.WriteLine($"Query:\r\n{cmd.CommandText}\r\n");
Console.WriteLine($"Result:\r\n{cmd.ExecuteScalar()}\r\n");
Console.WriteLine("Press Enter to continue, enter a replacement SQL and press Enter or press Ctrl-C to finish execution");
input = "";
do
{
var keyPressed = Console.ReadKey();
if (keyPressed.Key == ConsoleKey.C && keyPressed.Modifiers == ConsoleModifiers.Control)
{
return;
}
if (keyPressed.Key == ConsoleKey.Enter)
{
break;
}
input += keyPressed.KeyChar;
} while (true);
Console.WriteLine();
} while (true);
}
private static void ItemExists(IEnumerable <SqlRewriteRule> items, SqlRewriteRule item)
{
foreach (var loadedItem in items)
{
if (loadedItem.Id != item.Id)
{
continue;
}
Assert.AreEqual(item.Id, loadedItem.Id);
Assert.AreEqual(item.DatabaseRegEx, loadedItem.DatabaseRegEx);
Assert.AreEqual(item.QueryMatchRegEx, loadedItem.QueryMatchRegEx);
Assert.AreEqual(item.QueryReplacementString, loadedItem.QueryReplacementString);
Assert.AreEqual(item.RegExOptions, loadedItem.RegExOptions);
return;
}
throw new Exception("Item doesn't exist");
}
public void TestCompleteRewrite()
{
SqlCommandInterceptor.Enabled = true;
var rules = new SqlRewriteRule[1];
rules[0] = new SqlRewriteRule();
rules[0].DatabaseRegEx = ".*";
rules[0].QueryMatchRegEx = $"(.*){Stm}(.*)";
rules[0].QueryReplacementString = $"$1SELECT GETDATE()$2\r\n{SqlCommandRegExProcessor.RegReplacementIndicator}";
SqlCommandRegExProcessor.SqlRewriteRules = rules;
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using var cmd = new SqlCommand(Stm, con);
Assert.IsTrue(cmd.CommandText.EndsWith(SqlCommandRegExProcessor.RegReplacementIndicator), $"SQL should end with {SqlCommandRegExProcessor.RegReplacementIndicator}");
var version = cmd.ExecuteScalar().ToString();
Assert.IsFalse(version.Contains("Microsoft"));
}
public void TestBadRegEx()
{
SqlCommandInterceptor.Enabled = true;
var rules = new SqlRewriteRule[1];
rules[0] = new SqlRewriteRule();
Assert.ThrowsException <ArgumentException>(() =>
{
rules[0].DatabaseRegEx = ".(*"; // Bad regex
});
SqlCommandRegExProcessor.SqlRewriteRules = rules;
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using var cmd = new SqlCommand(Stm, con);
var version = cmd.ExecuteScalar().ToString();
Assert.IsTrue(version.Contains("Microsoft"));
}
public IEnumerable <SqlRewriteRule> LoadSqlRewriteRules()
{
var result = new List <SqlRewriteRule>();
using var loadSqlRewriteRules = new SqlCommand(@"
SELECT ID, DatabaseRegEx, QueryMatchRegEx, QueryReplacementString, RegExOptions
FROM SqlRewriteRegistry", _sqlConnection);
using var resultSet = loadSqlRewriteRules.ExecuteReader();
while (resultSet.Read())
{
var item = new SqlRewriteRule
{
Id = (int)resultSet[0],
DatabaseRegEx = (string)resultSet[1],
QueryMatchRegEx = (string)resultSet[2],
QueryReplacementString = (string)resultSet[3],
RegExOptions = (RegexOptions)resultSet[4]
};
result.Add(item);
}
return(result);
}
