public void SqlParserTest_verify_match_on_select_with_multi_sets()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "set test; set test2; select * from test");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("test/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestShow()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "show stuff");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("stuff/show", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TableDirectQueryParsed()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.TableDirect, "MyAwesomeTable");
Assert.AreEqual("MyAwesomeTable", parsedDatabaseStatement.Model);
Assert.AreEqual("select", parsedDatabaseStatement.Operation);
}
public void SqlParserTest_TestExecProcedureNoArguments()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "EXEC @RTN = [ClassSearchPublicSite]");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("classsearchpublicsite/ExecuteProcedure", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestTableDirect()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.TableDirect, "MyTable");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("MyTable/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestProcedureWithBrackets()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.StoredProcedure, "[DotNetNuke].[sys].[sp_dude]");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("dotnetnuke.sys.sp_dude/ExecuteProcedure", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_Valid_Sqls_But_NotSupported()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "mystoredprocedure'123'");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "mystoredprocedure\t'123'");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "mystoredprocedure\r\n'123'");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "[mystoredprocedure]123");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "\"mystoredprocedure\"abc");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "mystoredprocedure");
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
}
public void SqlParserTest_TestProcedure()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.StoredProcedure, "MyProc");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("myproc/ExecuteProcedure", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestStoredProcedureTextCommandWithArguments()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "sp_MyProc ?, ?");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("sp_myproc/ExecuteProcedure", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestDelete()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "delete actors where title = 'The Dude'");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("actors/delete", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestCreateProcedure()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "create procedure actors as select * from dudes");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("procedure/create", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_StoredProcedureParsed()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.StoredProcedure, "dbo.MySchema.scalar_getMeSomeData");
Assert.AreEqual("dbo.myschema.scalar_getmesomedata", parsedDatabaseStatement.Model);
Assert.AreEqual("ExecuteProcedure", parsedDatabaseStatement.Operation);
}
public void SqlParserTest_TestInsertWithValues()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "insert into test(id, name) values(6, 'Bubba')");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("test/insert", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestInsertWithSelect()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "INSERT into cars select * from man");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("cars/insert", parsedDatabaseStatement.ToString());
}
public void SqlParsingTest(string inputSql, string expectedOperation, string expectedTable)
{
var parsed = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, inputSql);
Assert.AreEqual(expectedOperation.ToLower(), parsed.Operation, string.Format("Expected operation {0} but was {1}", expectedOperation, parsed.Operation));
Assert.AreEqual(expectedTable?.ToLower(), parsed.Model, string.Format("Expected table {0} but was {1}", expectedTable, parsed.Model));
}
public void Example_DatabaseStatementParser()
{
DatabaseStatementParser dbParser = null;
var cachedThroughput = ThroughputExerciser.Create()
.UsingThreads(10)
.ForDuration(1000) // 1 second
.DoThisToSetup(() => { dbParser = new DatabaseStatementParser(Mock.Create <ICacheStatsReporter>()); })
.DoThisUnitOfWork((threadId, uowIdLocal, uowIdGlobal) =>
{
var stmt = dbParser.ParseDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, $"SELECT * FROM dbo.User WHERE UserID = {uowIdLocal}");
})
.ExecAll();
var notCachedThroughput = ThroughputExerciser.Create()
.UsingThreads(10)
.ForDuration(1000) // 1 second
.DoThisToSetup(() => { dbParser = new DatabaseStatementParser(Mock.Create <ICacheStatsReporter>()); })
.DoThisUnitOfWork((threadId, uowIdLocal, uowIdGlobal) =>
{
var stmt = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, $"SELECT * FROM dbo.User WHERE UserID = {uowIdLocal}");
})
.ExecAll();
//Caching makes things better
Assert.Greater(cachedThroughput.CountUnitsOfWorkPerformed, notCachedThroughput.CountUnitsOfWorkPerformed);
//Chaching makes things 50% better
//Assert.Greater(cachedThroughput.CountUnitsOfWorkPerformed, notCachedThroughput.CountUnitsOfWorkPerformed * 1.5);
}
public void SqlParserTest_verify_match_on_select_with_beginning_comment_and_set()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "/* test */ set nocount on; select * from test");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("test/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestSet()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "SET character_set_results=NULL");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("character_set_results/set", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_verify_match_on_select_with_set_and_subselect()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "set nocount on;select * from test where this in (select * from testing)");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("test/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestSelectMultipleTables()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "SELECT * FROM man, dude where dude.id = man.id");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("man/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestUpdate()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "Update dude set man = 'yeah' where id = 666");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("dude/update", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestSelectWithNestedParens()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "select * from (((dude)))");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("dude/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestSelectMultipleLine()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "Select *\nfrom MAN\nwhere id = 5");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("man/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestShowLongName()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "show wow_this_is_a_really_long_name_isnt_it_cmon_man_it_s_crazy_no_way_bruh");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("show", parsedDatabaseStatement.Operation);
Assert.AreEqual("wow_this_is_a_really_long_name_isnt_it_cmon_man_it", parsedDatabaseStatement.Model);
}
public void SqlParserTest_SelectQueryParsed()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "SELECT * FROM MyAwesomeTable");
Assert.AreEqual("myawesometable", parsedDatabaseStatement.Model);
Assert.AreEqual("select", parsedDatabaseStatement.Operation);
Assert.AreEqual("Datastore/statement/MSSQL/myawesometable/select", parsedDatabaseStatement.DatastoreStatementMetricName);
}
public void SqlParserTest_TestSelect_with_nocount()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "set nocount on; select * from dude");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("select", parsedDatabaseStatement.Operation);
Assert.AreEqual("dude", parsedDatabaseStatement.Model);
}
public void SqlParserTest_TestCompoundSetStatement()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "set @FOO=17; set @BAR=18;");
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("foo", parsedDatabaseStatement.Model);
Assert.AreEqual("set", parsedDatabaseStatement.Operation);
}
public void SqlParserTest_InvalidTextCantBeParsed_And_DoesNotResultInNullParsedDatabaseStatement()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, "Lorem ipsum dolar sit amet");
Assert.IsNotNull(parsedDatabaseStatement); // It is important that GetParsedDatabaseStatement never returns null
Assert.IsNull(parsedDatabaseStatement.Model);
Assert.AreEqual("other", parsedDatabaseStatement.Operation);
Assert.AreEqual(DatastoreVendor.MSSQL, parsedDatabaseStatement.DatastoreVendor);
}
public void SqlParserTest_TestCommentInMiddle()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, @"select *
/* ignore the comment */
from dude" );
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("dude/select", parsedDatabaseStatement.ToString());
}
public void SqlParserTest_TestCommentInFront()
{
var parsedDatabaseStatement = SqlParser.GetParsedDatabaseStatement(DatastoreVendor.MSSQL, CommandType.Text, @"/* ignore the comment */
select * from dude" );
Assert.IsNotNull(parsedDatabaseStatement);
Assert.AreEqual("select", parsedDatabaseStatement.Operation);
Assert.AreEqual("dude", parsedDatabaseStatement.Model);
}
