public void ToAutoNameParameterで自動的にパラメーター名が設定される()
{
var builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("123");
builder.ToAutoNameParameter(123);
builder.Text.Add("123");
builder.ToAutoNameParameter("ABC");
builder.Text.Add("'Foo'");
builder.ToAutoNameParameter("ABC");
builder.Text.Add("'Fo''o'");
builder.ToAutoNameParameter("ABC");
builder.Text.Add("'Fo''''o'");
builder.ToAutoNameParameter("ABC");
builder.Text.Add("Foo = 1");
builder.ToAutoNameParameter(123);
var def = builder.Build();
Assert.Equal("@p0@p1@p2@p3@p4Foo = @p5", def.SqlText);
Assert.Equal(6, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
Assert.Equal("ABC", def.Parameters[1].Value);
Assert.Equal("ABC", def.Parameters[2].Value);
Assert.Equal("ABC", def.Parameters[3].Value);
Assert.Equal("ABC", def.Parameters[4].Value);
Assert.Equal(123, def.Parameters[5].Value);
}
public void 直前に設定されたテキストを取得できる()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.Text.Add("WHERE A = A");
Assert.Equal("WHERE A = A", builder.Text.Prev());
}
public void テキストを追加できる()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.Text.Add("WHERE 1 = 1");
var def = builder.Build();
Assert.Equal("SELECT * FROM FOO WHERE 1 = 1", def.SqlText);
}
public void パラメーターを追加できる()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.Text.Add("WHERE A = @A").Parameter.Add("A", 1);
var def = builder.Build();
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("A", def.Parameters[0].Name);
Assert.Equal(1, def.Parameters[0].Value);
}
public void Useされていないブロック内のテキストは無視される()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.StartBlock("Block 1")
.Text.Add("WHERE 1 = 1 ")
.EndBlock()
.Text.Add("AND 2 = 2");
var def = builder.Build();
Assert.Equal("SELECT * FROM FOO AND 2 = 2", def.SqlText);
}
public void 直前のテキスト取得はBlock事となる()
{
var builder = new SqlDefinitionBuilder();
builder.StartBlock("Block1");
Assert.Null(builder.Text.Prev());
builder.Text.Add("Foo");
Assert.Equal("Foo", builder.Text.Prev());
builder.EndBlock();
Assert.Null(builder.Text.Prev());
builder.Text.Add("Bar");
Assert.Equal("Bar", builder.Text.Prev());
}
public void Useされていないブロック内のパラメーターは無視される()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.StartBlock("Block 1")
.Text.Add("WHERE A = @A ").Parameter.Add("A", 1)
.EndBlock()
.Text.Add("AND B = @B").Parameter.Add("B", 2);
var def = builder.Build();
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("B", def.Parameters[0].Name);
Assert.Equal(2, def.Parameters[0].Value);
}
public void 子のブロックがUseされていない場合は子のブロックは利用されない()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.StartBlock("Block 1")
.Text.Add("WHERE 1 = 1 ")
.StartBlock("Block 1-1")
.Text.Add("AND 2 = 2 ")
.EndBlock()
.EndBlock()
.Text.Add("AND 3 = 3")
.UseBlock("Block 1");
var def = builder.Build();
Assert.Equal("SELECT * FROM FOO WHERE 1 = 1 AND 3 = 3", def.SqlText);
}
public void すべてのパラメーター名が取得できる()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.StartBlock("Block 1")
.Text.Add("WHERE A = @A ").Parameter.Add("A", 1)
.EndBlock()
.Text.Add("AND B = @B").Parameter.Add("B", 2)
.Text.Add("AND C = @C").Parameter.Add("C", 3);
var names = builder.Parameter.GetNames();
Assert.Equal(3, names.Count());
Assert.True(names.Any(n => n == "A"));
Assert.True(names.Any(n => n == "B"));
Assert.True(names.Any(n => n == "C"));
Assert.False(names.Any(n => n == "D"));
}
public void ToAutoNameInParameterで自動的にパラメーター名が設定される()
{
var builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("( 123, 456, 789 ");
builder.ToAutoNameInParameter(new[] { 111, 222, 333 });
builder.Text.Add(" )");
var def = builder.Build();
Assert.Equal("( @p0_0, @p0_1, @p0_2 )", def.SqlText);
Assert.Equal(3, def.Parameters.Count);
Assert.Equal("p0_0", def.Parameters[0].Name);
Assert.Equal(111, def.Parameters[0].Value);
Assert.Equal("p0_1", def.Parameters[1].Name);
Assert.Equal(222, def.Parameters[1].Value);
Assert.Equal("p0_2", def.Parameters[2].Name);
Assert.Equal(333, def.Parameters[2].Value);
}
public void ToInParameterでIEnumerableをパラメーターにできる()
{
var builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("( 123, 456, 789 ");
builder.ToInParameter("Foo", new[] { 111, 222, 333 });
builder.Text.Add(" )");
var def = builder.Build();
Assert.Equal("( @Foo0, @Foo1, @Foo2 )", def.SqlText);
Assert.Equal(3, def.Parameters.Count);
Assert.Equal("Foo0", def.Parameters[0].Name);
Assert.Equal(111, def.Parameters[0].Value);
Assert.Equal("Foo1", def.Parameters[1].Name);
Assert.Equal(222, def.Parameters[1].Value);
Assert.Equal("Foo2", def.Parameters[2].Name);
Assert.Equal(333, def.Parameters[2].Value);
}
public void 直前のテキストを削除できる()
{
var builder = new SqlDefinitionBuilder();
builder.StartBlock("Block1");
builder.Text.Add("Foo");
Assert.Equal("Foo", builder.Text.Prev());
builder.Text.RemovePrev();
Assert.Null(builder.Text.Prev());
builder.Text.Add("Bar");
Assert.Equal("Bar", builder.Text.Prev());
builder.EndBlock();
Assert.Null(builder.Text.Prev());
builder.Text.Add("Baz");
Assert.Equal("Baz", builder.Text.Prev());
builder.UseBlock("Block1");
Assert.Equal("BarBaz", builder.Build().SqlText);
}
public void じ名前のブロックは一度にUseされる()
{
var builder = new SqlDefinitionBuilder();
builder.Text.Add("SELECT * FROM FOO ")
.StartBlock("Block 1")
.Text.Add("WHERE 1 = 1 ")
.StartBlock("Block 1-1")
.Text.Add("AND 2 = 2 ")
.EndBlock()
.EndBlock()
.Text.Add("AND 3 = 3 ")
.Text.Add("AND EXISTS SELECT 1 FROM BAR WHERE BAR.A = FOO.A ")
.StartBlock("Block 1")
.Text.Add("AND BAR = 1")
.EndBlock()
.UseBlock("Block 1");
var def = builder.Build();
Assert.Equal("SELECT * FROM FOO WHERE 1 = 1 AND 3 = 3 AND EXISTS SELECT 1 FROM BAR WHERE BAR.A = FOO.A AND BAR = 1", def.SqlText);
}
public async Task <SqlDefinition> ExecuteAsync(string sqlTemplate, object model, TemplateOptions options)
{
Throws.NotEmpty(sqlTemplate, nameof(sqlTemplate));
Throws.NotNull(model, nameof(model));
if (options == null)
{
options = TemplateOptions.Default;
}
var modelType = model.GetType();
ValidateModel(modelType, model);
var globalsType = typeof(Globals <>).MakeGenericType(modelType);
var runner = pool.GetOrAdd(Tuple.Create(sqlTemplate, modelType), v =>
{
var parser = new CSharpScriptCodeParser(sqlTemplate);
var parseResult = parser.Parse();
var scriptOptions = ScriptOptions.Default.AddReferences(
typeof(SqlTemplateEngine).GetTypeInfo().Assembly,
modelType.GetTypeInfo().Assembly
).AddImports("Km.Toi.Template", "System", "System.Linq")
.AddImports(parseResult.Imports);
return(CSharpScript.Create(parseResult.Code, scriptOptions, globalsType).CreateDelegate());
});
var builder = new SqlDefinitionBuilder(options);
var global = Activator.CreateInstance(globalsType, model, builder) as IGlobals;
await runner(global);
return(builder.Build());
}
public void ToParameterで文字列をパラメーターにできる()
{
var builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("'Foo'");
builder.ToParameter("Foo", "ABC");
var def = builder.Build();
Assert.Equal("@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("ABC", def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("'Fo''o'");
builder.ToParameter("Foo", "ABC");
def = builder.Build();
Assert.Equal("@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("ABC", def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("'Fo''''o'");
builder.ToParameter("Foo", "ABC");
def = builder.Build();
Assert.Equal("@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("ABC", def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("'F''o''o'");
builder.ToParameter("Foo", "ABC");
def = builder.Build();
Assert.Equal("@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal("ABC", def.Parameters[0].Value);
}
public void ToParameterで値をパラメーターに変換できる()
{
var builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("123");
builder.ToParameter("Num", 123);
var def = builder.Build();
Assert.Equal("@Num", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo = 1");
builder.ToParameter("Foo", 123);
def = builder.Build();
Assert.Equal("Foo = @Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo=1");
builder.ToParameter("Foo", 123);
def = builder.Build();
Assert.Equal("Foo=@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo>1");
builder.ToParameter("Foo", 123);
def = builder.Build();
Assert.Equal("Foo>@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo<1");
builder.ToParameter("Foo", 123);
def = builder.Build();
Assert.Equal("Foo<@Foo", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo=FUNC(1");
builder.ToParameter("Foo", 123);
builder.Text.Add(")");
def = builder.Build();
Assert.Equal("Foo=FUNC(@Foo)", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
builder = new SqlDefinitionBuilder(TemplateOptions.Default);
builder.Text.Add("Foo=FUNC(1,2");
builder.ToParameter("Foo", 123);
builder.Text.Add(")");
def = builder.Build();
Assert.Equal("Foo=FUNC(1,@Foo)", def.SqlText);
Assert.Equal(1, def.Parameters.Count);
Assert.Equal(123, def.Parameters[0].Value);
}
public void 直前に設定されたテキストがない場合はnullが返る()
{
var builder = new SqlDefinitionBuilder();
Assert.Null(builder.Text.Prev());
}
