public void test_Parse() { RelExParser relExParser = new RelExParser(); // generate SQL by query SqlClientDalcFactory factory = new SqlClientDalcFactory(); DbCommandGenerator cmdGenerator = new DbCommandGenerator(factory); for (int i = 0; i < oldRelExSamples.Length; i++) { string relEx = oldRelExSamples[i]; Query q = relExParser.Parse(relEx); IDbCommand cmd = cmdGenerator.ComposeSelect(q); Assert.AreEqual(oldRelExCommandTexts[i], cmd.CommandText, "Parse failed: " + i.ToString()); } for (int i = 0; i < relExSamples.Length; i++) { string relEx = relExSamples[i]; Query q = relExParser.Parse(relEx); IDbCommand cmd = cmdGenerator.ComposeSelect(q); Assert.AreEqual(relExCommandTexts[i], cmd.CommandText, "Parse failed: " + i.ToString()); } // 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.AreNotEqual(null, FindNodeByName(qWithGroups.Condition, "idGroup"), "named group not found"); Assert.AreNotEqual(null, FindNodeByName(qWithGroups.Condition, "ageGroup"), "named group not found"); Assert.AreNotEqual(null, FindNodeByName(qWithGroups.Condition, "emptyGroup"), "named group not found"); // 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.AreEqual(1, complexQ.Fields.Length); Assert.AreEqual(3, complexQ.Sort.Length); Assert.Catch <RelExParseException>(() => { relExParser.Parse("users[id"); }); }
public void test_CommandGenerator() { SqlClientDalcFactory factory = new SqlClientDalcFactory(); DbCommandGenerator cmdGenerator = new DbCommandGenerator(factory); Query 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.ComposeSelect(q); string masterSQL = "SELECT name,(t.age) as 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.AreEqual(cmd.CommandText, masterSQL, "Select command generation failed"); // SELECT WITH TABLE ALIAS TEST cmd = cmdGenerator.ComposeSelect( new Query("accounts.a", new QueryConditionNode((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.AreEqual(masterSQL, cmd.CommandText); DataSet ds = new DataSet(); ds.Tables.Add("test"); ds.Tables["test"].Columns.Add("name", typeof(string)).DefaultValue = "name"; ds.Tables["test"].Columns.Add("age", typeof(int)); ds.Tables["test"].Columns.Add("weight", typeof(double)); ds.Tables["test"].Columns.Add("type", typeof(string)); ds.Tables["test"].PrimaryKey = new DataColumn[] { ds.Tables["test"].Columns["name"] }; // INSERT TEST cmd = cmdGenerator.ComposeInsert(ds.Tables["test"]); masterSQL = "INSERT INTO test (name,age,weight,type) VALUES (@p0,@p1,@p2,@p3)"; Assert.AreEqual(cmd.CommandText, masterSQL, "Insert command generation failed"); Assert.AreEqual(cmd.Parameters.Count, 4, "Insert command generation failed"); // UPDATE TEST cmd = cmdGenerator.ComposeUpdate(ds.Tables["test"]); masterSQL = "UPDATE test SET name=@p0,age=@p1,weight=@p2,type=@p3 WHERE name=@p4"; Assert.AreEqual(cmd.CommandText, masterSQL, "Update command generation failed"); Assert.AreEqual(cmd.Parameters.Count, 5, "Update command generation failed"); // UPDATE TEST (by query) var changes = new Dictionary <string, IQueryValue>() { { "age", (QConst)21 }, { "name", (QConst)"Alexandra" } }; cmd = cmdGenerator.ComposeUpdate(new Query("test", (QField)"id" == (QConst)1), changes); masterSQL = "UPDATE test SET age=@p0,name=@p1 WHERE id=@p2"; Assert.AreEqual(masterSQL, cmd.CommandText, "Update command generation failed"); Assert.AreEqual(3, cmd.Parameters.Count, "Update command generation failed"); // DELETE TEST cmd = cmdGenerator.ComposeDelete(ds.Tables["test"]); masterSQL = "DELETE FROM test WHERE name=@p0"; Assert.AreEqual(cmd.CommandText, masterSQL, "Delete command generation failed"); Assert.AreEqual(cmd.Parameters.Count, 1, "Delete command generation failed"); // DELETE BY QUERY TEST cmd = cmdGenerator.ComposeDelete(new Query("test", (QField)"id" == (QConst)5)); masterSQL = "DELETE FROM test WHERE id=@p0"; Assert.AreEqual(cmd.CommandText, masterSQL, "Delete command (by query) generation failed"); Assert.AreEqual(cmd.Parameters.Count, 1, "Delete command (by query) generation failed"); }