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");
}
