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