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 QueryTests()
{
var user = new User
{
DateOfBirth = DateTime.Parse("1994-01-04"),
GPA = 3.10,
ID = 10,
LoginDate = null,
Name = "Zawar",
PIN = 2291,
Rating = 9.01,
Salary = 100000,
TimeStamp = DateTime.Now
};
string BeginTransactionQuery = QueryMaker.BeginTransQuery;
string CommitTransactionQuery = QueryMaker.CommitTransQuery;
string DeleteQuery = QueryMaker.DeleteQuery(user);
string DeleteQuery2 = QueryMaker.DeleteQuery <User>(" where 1 = 1");
var sortAndOrder = new Dictionary <string, string>();
sortAndOrder.Add("ID", "desc");
sortAndOrder.Add("TimeStamp", "asc");
var PagerQuery = QueryMaker.GetPagerQueries <User>(null, sortAndOrder, 0, 10);
string InsertQuery = QueryMaker.InsertQuery(user);
string SelectQuery = QueryMaker.SelectQuery <User>();
string UpdateQuery = QueryMaker.UpdateQuery(user);
return;
}
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 static void InsertProduct(Product p)
{
string query = QueryMaker.InsertQuery(p) + ";DECLARE @ID INT = ( SELECT SCOPE_IDENTITY() );";
foreach (var element in p.Packaging)
{
query += $"INSERT INTO Product_Packaging (ProductID , Packaging) VALUES (@ID, '{element}');";
}
foreach (var element in p.SKU_PackSize)
{
query += $"INSERT INTO Product_SKU (ProductID, SKU, Price) VALUES (@ID, '{element.SKU}', '{element.Price}');";
}
DBHelper.ExecuteQuery(query);
}
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 MakingQueryWithOneProgrammingLanguageReturnsValidSearchResults()
{
//Arrange
var fakeSearchEngines = ReturnSearchEnginesMocks();
var queryMaker = new QueryMaker(fakeSearchEngines);
//Act
var searchResults = queryMaker.QuerySearchEngines(new string[] { "java script" });
//Assert
var numberOfSearchEngines = fakeSearchEngines.Count();
Assert.AreEqual(numberOfSearchEngines, searchResults.Count());
AssertAreValidSearchResults(searchResults, numberOfSearchEngines, "java script");
AssertNumberOfResutsGreaterOrEqualThanZero(searchResults);
}
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 Bm_Login()
{
InitializeComponent();
SystemInfo.server_Ip = HubIniFile.GetIniValue("SERVER", "IP", set_Path);
encType = HubIniFile.GetIniValue("SERVER", "ENC", set_Path);
string id = HubIniFile.GetIniValue("LOGIN", "ID", set_Path);
txt_Id.Text = id;
login_Id = "";
CheckSaveId.Checked = true;
ip = new IPEndPoint(IPAddress.Parse(SystemInfo.server_Ip), 13300);
qm = new QueryMaker();
req = new Request();
}
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);
}
public void MakingQueryWithMultipleProgrammingLanguagesReturnsValidSearchResults()
{
//Arrange
var fakeSearchEngines = ReturnSearchEnginesMocks();
var queryMaker = new QueryMaker(fakeSearchEngines);
//Act
var programmingLanguages = new string[] { "java script", "c#", "Ruby on Rails", "Visual Basic", ".net" };
var searchResults = queryMaker.QuerySearchEngines(programmingLanguages);
//Assert
var numberOfSearchEngines = fakeSearchEngines.Count();
Assert.AreEqual(5 * numberOfSearchEngines, searchResults.Count());
foreach (var programmingLanguage in programmingLanguages)
{
AssertAreValidSearchResults(searchResults, numberOfSearchEngines, programmingLanguage);
}
AssertNumberOfResutsGreaterOrEqualThanZero(searchResults);
}
// main search method
private IEnumerable <CardCriterion> _search(List <KeyValuePair <string, object> > searchCriterion)
{
//ifdict.Value contains * OR ?, replace. If After this, searchString is empty, return new List<>
if (searchCriterion.Where(kvp => string.IsNullOrEmpty(kvp.Value.toString().Replace("?", ""))).ToList().Any())
{
return(new List <CardCriterion>());
}
// set up lucene searcher
using (var searcher = new IndexSearcher(LuceneConfig.Directory, false))
{
IEnumerable <CardCriterion> results; //rename to resultlist
using (var analyzer = new RussianAnalyzer(Version.LUCENE_30)) //contains already a russian stopwordlist
{
Query query = new QueryMaker(analyzer, this, searchCriterion).MakeQuery();
ScoreDoc[] hits = searcher.Search(query, LuceneConfig.HitsLimit).ScoreDocs;
results = _mapLuceneToDataList(hits, searcher);
}
return(results);
}
}
private static string GetExpression(string type, string propName, object value, bool quoted)
{
switch (type.ToLower())
{
case "equals":
case "eq":
return(QueryMaker.EqualsTo(propName, value, quoted));
case "notequals":
case "neq":
case "ne":
return(QueryMaker.NotEquals(propName, value, quoted));
case "contains":
case "c":
return(QueryMaker.Contains(propName, value));
case "notcontains":
case "doesnotcontain":
case "dnc":
case "nc":
return(QueryMaker.NotContains(propName, value));
case "startswith":
case "sw":
return(QueryMaker.StartsWith(propName, value));
case "notstartswith":
case "nsw":
return(QueryMaker.NotStartsWith(propName, value));
case "endswith":
case "ew":
return(QueryMaker.EndsWith(propName, value));
case "notendswith":
case "new":
return(QueryMaker.NotEndsWith(propName, value));
case "isempty":
case "ie":
return(QueryMaker.IsEmpty(propName));
case "isnotempty":
case "ino":
return(QueryMaker.IsNotEmpty(propName));
case "isnull":
case "in":
return(QueryMaker.IsNull(propName));
case "isnotnull":
case "inn":
return(QueryMaker.IsNotNull(propName));
case "greaterthan":
case "gt":
return(QueryMaker.GreaterThan(propName, value, quoted));
case "lessthan":
case "lt":
return(QueryMaker.LessThan(propName, value, quoted));
case "greaterthanorequals":
case "gtoe":
case "gte":
return(QueryMaker.GreaterThanOrEquals(propName, value, quoted));
case "lessthanorequals":
case "ltoe":
case "lte":
return(QueryMaker.LessThanOrEquals(propName, value, quoted));
case "istrue":
case "it":
return(QueryMaker.IsTrue(propName));
case "isfalse":
case "if":
return(QueryMaker.IsFalse(propName));
default:
return("");
}
}