public void test_Parse() { var relExParser = new RelexParser(); // generate SQL by query var dbFactory = new TestRelExDbFactory(); var cmdGenerator = new DbCommandBuilder(dbFactory); for (int i = 0; i < oldRelExSamples.Length; i++) { string relEx = oldRelExSamples[i]; Query q = relExParser.Parse(relEx); IDbCommand cmd = cmdGenerator.GetSelectCommand(q); Assert.Equal(oldRelExCommandTexts[i], cmd.CommandText.Trim()); } for (int i = 0; i < relExSamples.Length; i++) { string relEx = relExSamples[i]; Query q = relExParser.Parse(relEx); IDbCommand cmd = cmdGenerator.GetSelectCommand(q); Assert.Equal(relExCommandTexts[i], cmd.CommandText.Trim()); } // test for named nodes string relexWithNamedNodes = @"users( (<idGroup> id=null and id!=null) and (<ageGroup> age>5 or age<2) and (<emptyGroup>) )[count(*)]"; Query qWithGroups = relExParser.Parse(relexWithNamedNodes); Assert.NotEqual(null, FindNodeByName(qWithGroups.Condition, "idGroup")); Assert.NotEqual(null, FindNodeByName(qWithGroups.Condition, "ageGroup")); Assert.NotEqual(null, FindNodeByName(qWithGroups.Condition, "emptyGroup")); // just a parse test for real complex relex string sss = "sourcename( ( ((\"False\"=\"True\") or (\"False\"=\"True\")) and \"contact-of\" in agent_to_agent_role( left_uid in agent_accounts(agent_accounts.id=\"\")[agent_id] and (right_uid=agent_institutions.id) )[role_uid] ) or ( ((agent_institutions.id in events( events.id in event_assignments( person_id in agent_accounts (agent_accounts.id=\"\")[agent_id] )[event_id] )[client_institution_id]) or (agent_institutions.id in events( events.id in event_assignments( person_id in agent_accounts (agent_accounts.id=\"\")[agent_id] )[event_id] )[supplier_institution_id])) and (\"False\"=\"True\") ) or ( (agent_institutions.id in agent_to_agent_role( (left_uid in agent_to_agent_role( left_uid in agent_accounts(agent_accounts.id=\"\")[agent_id] and role_uid='contact-of' )[right_uid]) and role_uid='supplier-of')[right_uid] ) or (agent_institutions.id in events( events.supplier_institution_id in agent_to_agent_role( (agent_to_agent_role.role_uid='contact-of') and (agent_to_agent_role.left_uid in agent_accounts(agent_accounts.id=\"\")[agent_id]) )[agent_to_agent_role.right_uid] )[events.client_institution_id]) or (agent_institutions.id in events( events.client_institution_id in agent_to_agent_role( (agent_to_agent_role.role_uid='contact-of') and (agent_to_agent_role.left_uid in agent_accounts(agent_accounts.id=\"\")[agent_id]) )[agent_to_agent_role.right_uid] )[events.supplier_institution_id]) ) or (\"False\"=\"True\") or ( (\"False\"=\"True\") and (agent_institutions.id in agent_to_agent_role( role_uid='supplier-of' and right_uid = \"\" )[left_uid]) ) or (\"False\"=\"True\") )[*]"; relExParser.Parse(sss); var complexSortAndFields = "users.u( u.id=1 )[\"name+','\";id asc,id desc,\"sum(id)\"]"; var complexQ = relExParser.Parse(complexSortAndFields); Assert.Equal(1, complexQ.Fields.Length); Assert.Equal(3, complexQ.Sort.Length); Assert.Throws <RelexParseException>(() => { relExParser.Parse("users[id"); }); }
public void DataView() { var dbFactory = new DbFactory(SqlClientFactory.Instance); var cmdGenerator = new DbCommandBuilder(dbFactory); cmdGenerator.Views = new Dictionary <string, DbDataView>() { { "persons_view", new DbDataView( @"SELECT @columns FROM persons p LEFT JOIN countries c ON (c.id=p.country_id) @where[ WHERE {0}] @orderby[ ORDER BY {0}]") { FieldMapping = new Dictionary <string, string>() { { "id", "p.id" }, { "count(*)", "count(p.id)" }, { "*", "p.*" }, { "expired", "CASE WHEN DATEDIFF(dd, p.added_date, NOW() )>30 THEN 1 ELSE 0 END" } } } } }; // simple count query test Assert.Equal( "SELECT count(p.id) as cnt FROM persons p LEFT JOIN countries c ON (c.id=p.country_id)", cmdGenerator.GetSelectCommand(new Query("persons_view").Select(QField.Count)).CommandText.Trim() ); // field mapping in select columns Assert.Equal( "SELECT p.id as id,name,CASE WHEN DATEDIFF(dd, p.added_date, NOW() )>30 THEN 1 ELSE 0 END as expired FROM persons p LEFT JOIN countries c ON (c.id=p.country_id)", cmdGenerator.GetSelectCommand(new Query("persons_view") .Select("id", "name", "expired")).CommandText.Trim() ); // field mapping in conditions Assert.Equal( "SELECT p.* FROM persons p LEFT JOIN countries c ON (c.id=p.country_id) WHERE (p.id>@p0) And (CASE WHEN DATEDIFF(dd, p.added_date, NOW() )>30 THEN 1 ELSE 0 END=@p1)", cmdGenerator.GetSelectCommand(new Query("persons_view", (QField)"id" > (QConst)5 & (QField)"expired" == new QConst(true) )).CommandText.Trim() ); }
public void Sqlite_Select() { var cmdBuilder = new DbCommandBuilder(SqliteDb.DbFactory); var countCmd = cmdBuilder.GetSelectCommand(new Query("contacts").Select(QField.Count)); countCmd.Connection = SqliteDb.DbConnection; SqliteDb.OpenConnection(() => { Assert.Equal(5, Convert.ToInt32(countCmd.ExecuteScalar())); }); }
public void Select_Speed() { var dbFactory = new DbFactory(SqlClientFactory.Instance); var cmdGenerator = new DbCommandBuilder(dbFactory); Query q = new Query("test"); q.Condition = createTestQuery(); q.Fields = new QField[] { "name", "age" }; // SELECT TEST var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); for (int i = 0; i < 10000; i++) { IDbCommand cmd = cmdGenerator.GetSelectCommand(q); } stopwatch.Stop(); Console.WriteLine("Speedtest for select command generation (10000 times): {0}", stopwatch.Elapsed); }
public void BuildCommands() { var dbFactory = new DbFactory(SqlClientFactory.Instance); var cmdGenerator = new DbCommandBuilder(dbFactory); var q = new Query(new QTable("test", "t")); q.Condition = createTestQuery(); q.Fields = new QField[] { "name", "t.age", new QField("age_months", "t.age*12") }; // SELECT TEST with prefixes and expressions IDbCommand cmd = cmdGenerator.GetSelectCommand(q); string masterSQL = "SELECT name,t.age,t.age*12 as age_months FROM test t WHERE (((name LIKE @p0) Or (NOT(age>=@p1))) And ((weight=@p2) And (type IN (@p3,@p4)))) Or ((name<>@p5) And (type IS NOT NULL))"; Assert.Equal(masterSQL, cmd.CommandText.Trim()); // SELECT WITH TABLE ALIAS TEST cmd = cmdGenerator.GetSelectCommand( new Query("accounts.a", new QConditionNode((QField)"a.id", Conditions.In, new Query("dbo.accounts.b", (QField)"a.id" != (QField)"b.id")))); masterSQL = "SELECT * FROM accounts a WHERE a.id IN (SELECT * FROM dbo.accounts b WHERE a.id<>b.id)"; Assert.Equal(masterSQL, cmd.CommandText); var testData = new Dictionary <string, object> { { "name", "Test" }, { "age", 20 }, { "weight", 75.6 }, { "type", "staff" } }; // INSERT TEST cmd = cmdGenerator.GetInsertCommand("test", testData); masterSQL = "INSERT INTO test (name,age,weight,type) VALUES (@p0,@p1,@p2,@p3)"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 4); // UPDATE TEST cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData); masterSQL = "UPDATE test SET name=@p0,age=@p1,weight=@p2,type=@p3 WHERE name=@p4"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 5); // UPDATE TEST (by query) var changes = new Dictionary <string, IQueryValue>() { { "age", (QConst)21 }, { "name", (QConst)"Alexandra" } }; cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"id" == (QConst)1), changes); masterSQL = "UPDATE test SET age=@p0,name=@p1 WHERE id=@p2"; Assert.Equal(masterSQL, cmd.CommandText); Assert.Equal(3, cmd.Parameters.Count); // DELETE BY QUERY TEST cmd = cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5)); masterSQL = "DELETE FROM test WHERE id=@p0"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 1); // ------- escape identifiers asserts -------- dbFactory.IdentifierFormat = "[{0}]"; Assert.Equal( "SELECT [name],[t].[age],t.age*12 as [age_months] FROM [test] [t] WHERE ((([name] LIKE @p0) Or (NOT([age]>=@p1))) And (([weight]=@p2) And ([type] IN (@p3,@p4)))) Or (([name]<>@p5) And ([type] IS NOT NULL))", cmdGenerator.GetSelectCommand(q).CommandText.Trim()); Assert.Equal( "INSERT INTO [test] ([name],[age],[weight],[type]) VALUES (@p0,@p1,@p2,@p3)", cmdGenerator.GetInsertCommand("test", testData).CommandText); Assert.Equal( "UPDATE [test] SET [name]=@p0,[age]=@p1,[weight]=@p2,[type]=@p3 WHERE [name]=@p4", cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData).CommandText); Assert.Equal( "DELETE FROM [test] WHERE [id]=@p0", cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5)).CommandText); }
public DbCommand GetSelectCommand(DbTransaction transaction, string tableName) { return(_commandBuilder.GetSelectCommand(transaction, tableName)); }