public void SelectFrom_Resolver_IDataReader_Table()
{
using (TSQLEngine sut = new TSQLEngine())
{
sut.AddTableResolver((name, path) =>
{
if (name == "Persons")
{
Func <CsvDataReader> @delegate = () =>
{
var reader = new StreamReader("csv.csv");
var csvReader = new CsvReader(reader, CultureInfo.InvariantCulture, false);
return(new CsvDataReader(csvReader));
};
return(@delegate);
}
return(null);
});
var table = sut.RunAndReturnTable("SELECT * FROM Persons");
Assert.AreEqual(2, table.Count);
Assert.AreEqual("1", table[0][0]);
Assert.AreEqual("one", table[0][1]);
Assert.AreEqual("2", table[1][0]);
Assert.AreEqual("two", table[1][1]);
}
}
public object Run(string script, TSQLEngine engine, CancellationToken cancellationToken)
{
try
{
this.lexer = new Lexer(script, true);
this.parser = new Parser.Parser(lexer);
this.queryTree = parser.ComposeAll();
var prepareQuery = new PrepareQueryVisitor(engine, cancellationToken);
this.prepareQueryTraverser = new PrepareQueryTraverseVisitor(prepareQuery, cancellationToken);
queryTree.Accept(prepareQueryTraverser);
queryTree = prepareQuery.Root;
this.requestData = new RequestDataVisitor(engine, cancellationToken);
this.requestDataTraverser = new RequestDataTraverseVisitor(requestData, cancellationToken);
queryTree.Accept(requestDataTraverser);
queryTree = requestData.Root;
this.runQuery = new RunQueryVisitor(engine, cancellationToken);
this.csharpRewriteTraverser = new RunQueryTraverseVisitor(runQuery, cancellationToken);
queryTree.Accept(csharpRewriteTraverser);
return(runQuery.Result);
} catch (AggregateException ex)
{
throw ex.InnerException;
}
}
public void Select_All_FromWithAlias_Where()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
},
new Person {
Id = 2, FirstName = "John", LastName = "Doe"
},
new Person {
Id = 2, FirstName = "Joe", LastName = "Block"
}
});
var resunt = sut.RunAndReturnTable("SELECT * FROM Persons p WHERE FirstName = 'John'");
Assert.AreEqual(2, resunt.Count);
Assert.AreEqual(1, resunt[0][0]);
Assert.AreEqual("John", resunt[0][1]);
Assert.AreEqual("Smith", resunt[0][2]);
Assert.AreEqual(2, resunt[1][0]);
Assert.AreEqual("John", resunt[1][1]);
Assert.AreEqual("Doe", resunt[1][2]);
}
public void Select_TwoQueries()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
},
new Person {
Id = 2, FirstName = "John", LastName = "Doe"
},
new Person {
Id = 3, FirstName = "Joe", LastName = "Block"
}
});
sut.AddTable("Persons2", new Person[] {
new Person {
Id = 5, FirstName = "Daniel", LastName = "Json"
},
new Person {
Id = 6, FirstName = "Mark", LastName = "Stanford"
},
});
var resunt = sut.Run("SELECT * FROM Persons WHERE FirstName = 'John'; SELECT * FROM Persons2 WHERE FirstName = 'Daniel'");
Assert.AreEqual(1, resunt.Count);
Assert.AreEqual(5, resunt[0][0]);
Assert.AreEqual("Daniel", resunt[0][1]);
Assert.AreEqual("Json", resunt[0][2]);
}
public DataTable RunAndReturnTable(string script, TSQLEngine engine)
{
object result = Run(script, engine);
if (result is System.Collections.IEnumerable enumerableResult)
{
var itemType = result.GetType().GenericTypeArguments.FirstOrDefault();
List <DataColumn> columns2 = new List <DataColumn>();
int index = 0;
foreach (var field in itemType.GetFields())
{
columns2.Add(new DataColumn(field.Name, field.FieldType, index));
index++;
}
DataTable t = new DataTable("entities", columns2.ToArray());
foreach (var row in enumerableResult)
{
object[] values = new object[columns2.Count];
for (int i = 0; i < columns2.Count; i++)
{
values[i] = itemType.GetField(columns2[i].ColumnName).GetValue(row);
}
DataRow row2 = new DataRow(values);
t.Add(row2);
}
return(t);
}
return(null);
}
public void BoolToBool()
{
TSQLEngine sut = new TSQLEngine();
Assert.AreEqual(true, sut.RunAndReturnTable("SELECT CONVERT(bit, CONVERT(bit, 1)) AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, CONVERT(bit, 0)) AS [value]")[0][0]);
}
public void Select_WhereWithProperty()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person
{
Id = 1,
FirstName = "John",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"John\", \"age\":\"23\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
},
new Person
{
Id = 2,
FirstName = "Stive",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"age\":\"32\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
},
new Person
{
Id = 3,
FirstName = "Tom",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"Tom\", \"age\":\"37\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
}
});
var resunt = sut.RunAndReturnTable("SELECT Id, Json.age FROM Persons WHERE Json.name = 'John'");
Assert.AreEqual(1, resunt.Count);
Assert.AreEqual(1, resunt[0][0]);
Assert.AreEqual("23", resunt[0][1].ToString());
}
public void SelectFrom_DefaultDatabase_CustomSchema_Table()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new string[2] {
"hr", "dbo"
}, new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
},
new Person {
Id = 2, FirstName = "John", LastName = "Doe"
},
new Person {
Id = 2, FirstName = "Joe", LastName = "Block"
}
});
var resunt = sut.RunAndReturnTable("SELECT * FROM dbo.Persons WHERE FirstName = 'John'");
Assert.AreEqual(2, resunt.Count);
Assert.AreEqual(1, resunt[0][0]);
Assert.AreEqual("John", resunt[0][1]);
Assert.AreEqual("Smith", resunt[0][2]);
Assert.AreEqual(2, resunt[1][0]);
Assert.AreEqual("John", resunt[1][1]);
Assert.AreEqual("Doe", resunt[1][2]);
}
public IEnumerable Run(string sql, CancellationToken ct = default)
{
using (TSQLEngine sqlEngine = new TSQLEngine())
{
sqlEngine.AddTableResolver((name, path) =>
{
var alias = path.FirstOrDefault();
var connector = Connectors.FirstOrDefault(x => string.Equals(x.Config.Alias, alias, StringComparison.InvariantCultureIgnoreCase));
if (connector == null)
{
throw new ApplicationException($"Cannot find the connector with the alias '{alias}'");
}
Delegate @delegate = connector.ResolveTable(name, path, ct);
return(@delegate);
});
sqlEngine.AddMethodResolver((name, path, arguments) =>
{
var alias = path.FirstOrDefault();
var connector = Connectors.FirstOrDefault(x => string.Equals(x.Config.Alias, alias, StringComparison.InvariantCultureIgnoreCase));
if (connector == null)
{
throw new ApplicationException($"Cannot find the connector with the alias '{alias}'");
}
Delegate @delegate = connector.ResolveMethod(name, path, arguments, ct);
return(@delegate);
});
return(sqlEngine.Run(sql, ct));
}
}
public void Set_Variable()
{
TSQLEngine sut = new TSQLEngine();
sut.SetVariable("@MasterPath", "/path/to/resource");
var result = sut.RunAndReturnTable("SET @MasterPath = '/new/path/to/resource'");
Assert.AreEqual("/new/path/to/resource", sut.GetVariable("@MasterPath").Value);
}
public void StringToDate()
{
TSQLEngine sut = new TSQLEngine();
var result = sut.RunAndReturnTable("SELECT CONVERT(datetime, '2009-07-16 08:28:01') AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual(new DateTimeOffset(2009, 07, 16, 08, 28, 01, 0, TimeSpan.Zero), result[0][0]);
}
public void StringToExactDate_BadStyle_ThrowError()
{
TSQLEngine sut = new TSQLEngine();
var result = sut.RunAndReturnTable("SELECT CONVERT(datetime, 'May 1 2020 9:12AM', 9876) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual(new DateTimeOffset(2020, 05, 01, 09, 12, 00, 00, TimeSpan.Zero), result[0][0]);
}
public void NullStringToExactDate()
{
TSQLEngine sut = new TSQLEngine();
var result = sut.RunAndReturnTable("SELECT CONVERT(datetime, NULL, 100) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual(null, result[0][0]);
}
static public Traficante.TSQL.Tests.DataTable RunAndReturnTable(this TSQLEngine engine, string script, CancellationToken ct = default)
{
var result = engine.Run(script, ct);
if (result == null)
{
return(null);
}
return(new Traficante.TSQL.Tests.DataTable(result));
}
public void NullDateToExactString()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <DateTimeOffset?>("GetTestDate", () => null);
var result = sut.RunAndReturnTable("SELECT CONVERT(varchar, null, 2) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual(null, result[0][0]);
}
public void DateTimeToDate()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <DateTime?>("GetTestDate", () => new DateTime(2020, 05, 01, 09, 12, 00, 00));
var result = sut.RunAndReturnTable("SELECT CONVERT(datetime, GetTestDate()) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual(new DateTimeOffset(new DateTime(2020, 05, 01, 09, 12, 00, 00)), result[0][0]);
}
public void Select_Variable()
{
TSQLEngine sut = new TSQLEngine();
sut.SetVariable("@@VERSION", 123);
var table = sut.RunAndReturnTable("SELECT @@VERSION AS 'SQL Server Version'");
Assert.AreEqual(1, table.Count);
Assert.AreEqual(123, table[0][0]);
}
public void DateToExactString_BadStyle_ThrowException()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <DateTimeOffset?>("GetTestDate", () => new DateTimeOffset(2020, 05, 01, 09, 12, 00, 00, TimeSpan.Zero));
var result = sut.RunAndReturnTable("SELECT CONVERT(varchar, GetTestDate(), 9876) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual("20.05.01", result[0][0]);
}
public void DateToString()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <DateTimeOffset?>("GetTestDate", () => new DateTimeOffset(2020, 05, 01, 09, 12, 00, 00, TimeSpan.Zero));
var result = sut.RunAndReturnTable("SELECT CONVERT(varchar, GetTestDate()) AS [date]");
Assert.IsNotNull(result);
Assert.AreEqual("date", result.Columns.First().ColumnName);
Assert.AreEqual("May 1 2020 9:12AM", result[0][0]);
}
public void Declare_Variable()
{
TSQLEngine sut = new TSQLEngine();
var result = sut.RunAndReturnTable("declare @MasterPath nvarchar(512)");
Assert.IsNull(result);
var variable = sut.GetVariable("@MasterPath");
Assert.AreEqual("@MasterPath", variable.Name);
Assert.AreEqual(typeof(string), variable.Type);
}
public void Declare_SetString()
{
TSQLEngine sut = new TSQLEngine();
var result = sut.RunAndReturnTable("declare @MasterPath nvarchar(512); SET @MasterPath = '/new/path/to/resource'");
Assert.IsNull(result);
var variable = sut.GetVariable("@MasterPath");
Assert.AreEqual("@MasterPath", variable.Name);
Assert.AreEqual("/new/path/to/resource", variable.Value);
}
public void Select_OrderByInt()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person
{
Id = 1,
FirstName = "John",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"John\", \"age\":23, \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
},
new Person
{
Id = 2,
FirstName = "Stive",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"Stive\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
},
new Person
{
Id = 3,
FirstName = "Tom",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"Tom\", \"age\":25, \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
}
,
new Person
{
Id = 4,
FirstName = "Joe",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"Joe\", \"age\":\"24\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
}
,
new Person
{
Id = 5,
FirstName = "Dan",
LastName = "Smith",
Json = JsonDocument.Parse("{ \"name\":\"Dan\", \"age\":\"29\", \"address\": { \"city\":\"London\", \"street\":\"Moorgate Street\" }}").RootElement
}
});
var resunt = sut.RunAndReturnTable("SELECT Id, Json.name FROM Persons ORDER BY Json.age desc");
Assert.AreEqual(5, resunt.Count);
Assert.AreEqual("Dan", resunt[0][1].ToString());
Assert.AreEqual("Tom", resunt[1][1].ToString());
Assert.AreEqual("Joe", resunt[2][1].ToString());
Assert.AreEqual("John", resunt[3][1].ToString());
Assert.AreEqual("Stive", resunt[4][1].ToString());
}
public void Select_Function_Cast()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <string, bool>("SERVERPROPERTY", x => true);
var result = sut.RunAndReturnTable("SELECT CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled]");
Assert.IsNotNull(result);
Assert.AreEqual("IsHadrEnabled", result.Columns.First().ColumnName);
Assert.AreEqual(true, result[0][0]);
}
public void Select_FunctionDoesNotExist()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
}
});
var resunt = sut.RunAndReturnTable("SELECT SomeFunction(FirstName) FROM Persons");
}
public void SelectFrom_ColumnDoesNotExist()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
}
});
var resunt = sut.RunAndReturnTable("SELECT NotExistingColumn FROM Persons");
}
public void Select_ISNULL()
{
TSQLEngine sut = new TSQLEngine();
sut.SetVariable("@alwayson", (int?)null);
var result = sut.Run("SELECT ISNULL(@alwayson, -1) AS [AlwaysOn]");
Assert.IsNotNull(result);
Assert.AreEqual("AlwaysOn", result.Columns.First().ColumnName);
Assert.AreEqual(-1, result[0][0]);
}
public void Declare_SetCast()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <string, string>("SERVERPROPERTY", x => "Standard Edition");
var result = sut.RunAndReturnTable("DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); ");
Assert.IsNull(result);
var variable = sut.GetVariable("@edition");
Assert.AreEqual("@edition", variable.Name);
Assert.AreEqual("Standard Edition", variable.Value);
}
public void Select_Function_WithDatabaseAndSchema()
{
TSQLEngine sut = new TSQLEngine();
sut.AddFunction <bool>("fn_syspolicy_is_automation_enabled", new string[2] {
"msdb", "dbo"
}, () => true);
var result = sut.Run("SELECT msdb.dbo.fn_syspolicy_is_automation_enabled()");
Assert.IsNotNull(result);
Assert.AreEqual("msdb.dbo.fn_syspolicy_is_automation_enabled()", result.Columns.First().ColumnName);
Assert.AreEqual(true, result[0][0]);
}
public void StringToBool()
{
TSQLEngine sut = new TSQLEngine();
Assert.AreEqual(true, sut.RunAndReturnTable("SELECT CONVERT(bit, 'True') AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, 'False') AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, '0') AS [value]")[0][0]);
Assert.AreEqual(true, sut.RunAndReturnTable("SELECT CONVERT(bit, '1') AS [value]")[0][0]);
Assert.AreEqual(true, sut.RunAndReturnTable("SELECT CONVERT(bit, '5') AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, '0.0') AS [value]")[0][0]);
Assert.AreEqual(true, sut.RunAndReturnTable("SELECT CONVERT(bit, '0.01') AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, '') AS [value]")[0][0]);
Assert.AreEqual(false, sut.RunAndReturnTable("SELECT CONVERT(bit, ' ') AS [value]")[0][0]);
Assert.AreEqual(null, sut.RunAndReturnTable("SELECT CONVERT(bit, NULL) AS [value]")[0][0]);
}
public void SelectTableWithAlias()
{
TSQLEngine sut = new TSQLEngine();
sut.AddTable("Persons", new Person[] {
new Person {
Id = 1, FirstName = "John", LastName = "Smith"
}
});
Assert.AreEqual("John", sut.RunAndReturnTable("SELECT p.FirstName FROM Persons p")[0][0]);
Assert.AreEqual("John", sut.RunAndReturnTable("SELECT p.FirstName FROM Persons [p]")[0][0]);
Assert.AreEqual("John", sut.RunAndReturnTable("SELECT [p p].FirstName FROM Persons [p p]")[0][0]);
Assert.AreEqual("John", sut.RunAndReturnTable("SELECT [p p].FirstName FROM Persons AS [p p]")[0][0]);
}
