public void FullSelectForCustomerTimeTest()
{
int cnt = 1000000;
string sql = null;
var stopwatch = System.Diagnostics.Stopwatch.StartNew();
for (int i = 0; i < cnt; i++)
{
sql = QueryMaker.New()
.SELECT()
.Col("Id", "Id")
.Col("Name", "Name")
.Col("Description", "Desc")
.Col("Address", "Addr")
.Col("Zip", "Zip")
.Col("Balance", "Balance")
.Col("Registered", "Reg")
.FROM()
.Tab("Customer")
.WHERE("Zip = @zip")
.ORDERBY("Name", SortAs.Asc)
.RawSql();
}
stopwatch.Stop();
var example = "SELECT\n\tId AS Id\n\t, Name AS Name"
+ "\n\t, Description AS Desc\n\t, Address AS Addr\n\t, Zip AS Zip"
+ "\n\t, Balance AS Balance\n\t, Registered AS Reg"
+ "\nFROM\n\t[dbo].[Customer]"
+ "\nWHERE\n\tZip = @zip\nORDER BY Name ASC;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
System.Diagnostics.Trace.WriteLine(stopwatch.Elapsed.TotalMilliseconds.ToString("Total 0.00 ms"));
System.Diagnostics.Trace.WriteLine(((double)(stopwatch.Elapsed.TotalMilliseconds * 1000) /
cnt).ToString("0.00 us per one query"));
}
public void InitInsertTest()
{
var maker = QueryMaker.New(_dbScheme).INSERT("Customer");
var sql = maker.RawSql();
var example = "INSERT INTO [dbo].[Customer]";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void InitUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer");
var sql = maker.RawSql();
var example = "UPDATE [dbo].[Customer];";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void InitSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Name, t.Description, t.Address AS adr");
var sql = maker.RawSql();
var example = "SELECT\n\tName\n\t, t.Description\n\t, t.Address AS adr;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void InitDeleteTest()
{
var maker = QueryMaker.New()
.DELETE("Customer");
var sql = maker.RawSql();
var example = "DELETE FROM [dbo].[Customer];";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void InitSelectDistinctTest()
{
var maker = QueryMaker.New(_dbScheme)
.SelectDistinct("Name, Description, Address");
var sql = maker.RawSql();
var example = "SELECT DISTINCT\n\tName\n\t, Description\n\t, Address;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void SetUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id, Name = @name");
var sql = maker.RawSql();
var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereDeleteTest()
{
var maker = QueryMaker.New()
.DELETE("Customer")
.WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
var example = "DELETE FROM [dbo].[Customer]"
+ "\nWHERE Zip = @zip AND Id >= @id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void InitSqlMaker()
{
var current = QueryMaker.Current;
Assert.That(current, Is.InstanceOf <ISqlMaker>());
var maker = QueryMaker.New();
Assert.That(maker, Is.InstanceOf <ISqlMaker>());
Assert.That(maker, Is.InstanceOf <ISqlFirst>());
Assert.That(maker, Is.Not.EqualTo(current));
}
public void AddColSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Id")
.Col("Name")
.Col("Description", "Desc")
.Col("t.Address", "Addr");
var sql = maker.RawSql();
var example = "SELECT\n\tId\n\t, Name\n\t, Description AS Desc\n\t, t.Address AS Addr;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void AddColumnTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address");
var sql = maker.RawSql();
var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t)";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void EmptySchemeSelectTest()
{
var maker = QueryMaker.New()
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[Customer];";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void ValuesTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address")
.VALUES("@name, @description, @address");
var sql = maker.RawSql();
var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t)\n\tVALUES (\n\t\t@name\n\t\t, @description\n\t\t, @address\n\t);";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id")
.Val("Name", "name")
.WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name"
+ "\nWHERE Zip = @zip AND Id >= @id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer]\nWHERE\n\tZip = @zip AND Id >= @id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void FullJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.FullJoin("Address", "addr");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer]\nFULL JOIN [dbo].[Address] AS addr;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void FromSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Id")
.Col("Name")
.Col("Description", "Desc")
.Col("t.Address", "Addr")
.FROM("table, dbo.table, [dbo].[Customer] cst");
var sql = maker.RawSql();
var example = "SELECT\n\tId\n\t, Name\n\t, Description AS Desc\n\t, t.Address AS Addr\nFROM"
+ "\n\ttable\n\t, dbo.table\n\t, [dbo].[Customer] cst;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void AddTableSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Table")
.Tab("Table", "Tab")
.Tab("Table", "Tab", "tbl");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Table]\n\t, [dbo].[Table] AS Tab\n\t, [tbl].[Table] AS Tab;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OrderBySelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.ORDERBY("Id", SortAs.Desc);
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer] AS cst"
+ "\nORDER BY Id DESC;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void GroupBySelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.GROUPBY("Id");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer] AS cst"
+ "\nGROUP BY Id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void HavingSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.GROUPBY("Zip")
.HAVING("COUNT(Id) >= @id");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer]\nGROUP BY Zip"
+ "\nHAVING COUNT(Id) >= @id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OnJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.JOIN("Address", "addr")
.ON("cst.Id = addr.Id");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer] AS cst"
+ "\nINNER JOIN [dbo].[Address] AS addr"
+ "\n\tON cst.Id = addr.Id;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void SelectUnionTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.UNION();
var sql = maker.RawSql();
var example = "SELECT\nUNION\nSELECT;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id")
.UNION(IsALL: true)
.Col("Id");
sql = maker.RawSql();
example = "SELECT\n\tId\nUNION ALL\nSELECT\n\tId;";
Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
