private void GetToolsByProducts()
{
var query = Query.GetCategoryQuery(View.GetSelectedCategory());
if (query == "one product")
{
query = Query.GetProdIdByProductName(View.GetChoosenProd());
}
else
{
query = SelectFormatter.Get("prod_id", "(" + query + ")");
}
var conditions = new List <string>();
conditions.Add("time_end<=to_date('" + View.GetTimeEnd() + "','dd.mm.yyyy')");
conditions.Add("time_begin>=to_date('" + View.GetTimeBegin() + "','dd.mm.yyyy')");
conditions.Add(QueryFormatter.In("prod_id", query));
query = QueryFormatter.SelectFormatter.Get("prod_id"
, "products_exps"
, conditions);
query = Query.GetToolsByProdIdLab(query, View.GetChoosenLab());
Controller.Run <DirectorScientistsPresenter, string>(query);
}
public void GetRowValuesString_DateTimeValue_ShouldHaveSqlFormatting(DateTime dateTime, string expectedFormat)
{
var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(DateTime)) };
var dataTable = GetDataTable(dataColumns);
dataTable.Rows.Add(dateTime);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"'{expectedFormat}'");
}
public void GetRowValuesString_StringValue_ShouldBeWrappedByQuotes()
{
var data = "foo";
var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(string)) };
var dataTable = GetDataTable(dataColumns);
dataTable.Rows.Add(data);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"'{data}'");
}
static internal SqlPreCommandSimple Format(Expression expression)
{
QueryFormatter qf = new QueryFormatter();
qf.Visit(expression);
var parameters = qf.parameterExpressions.Values.Select(pi => pi.Parameter).ToList();
var sqlpc = new SqlPreCommandSimple(qf.sb.ToString(), parameters);
return(PostFormatter.Value == null ? sqlpc : PostFormatter.Value.Invoke(sqlpc));
}
public void Format_Commas_OnNewLine()
{
var formatted = QueryFormatter.Format("select cola, colb, colc from dbo.table where cola = \"test\"");
Assert.Equal(@"SELECT
cola
,colb
,colc
FROM
dbo.table
WHERE
cola = ""test""", formatted);
}
public void GetRowValuesString_DateTimeValue_ShouldBeWrappedByQuotes()
{
var data = DateTime.Now;
var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(DateTime)) };
var dataTable = GetDataTable(dataColumns);
dataTable.Rows.Add(data);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().StartWith("'");
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().EndWith("'");
}
public void GetRowValuesString_DecimalValue_ShouldNotBeWrappedByQuotes()
{
var data = 1.1m;
var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(decimal)) };
var dataTable = GetDataTable(dataColumns);
dataTable.Rows.Add(data);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"{data}");
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().NotContain("'");
}
public void ColumnListString_ShouldReturnColumnsList()
{
var col1 = new DataColumn("Col1", typeof(string));
col1.AllowDBNull = true;
var col2 = new DataColumn("Col2", typeof(DateTime));
col2.AllowDBNull = true;
var col3 = new DataColumn("Col3", typeof(int));
col3.AllowDBNull = true;
var dataColumns = new DataColumn[] { col1, col2, col3 };
var dataTable = GetDataTable(dataColumns);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetColumnsListString(dataTable.Columns.ToList()).Should().BeEquivalentTo("Col1, Col2, Col3");
}
public void GetRowValuesString_ShouldProduceNull_IfColumnIsNullableAndValueIsNull()
{
var data = new object[] { null, null, null };
var col1 = new DataColumn("Col1", typeof(string));
col1.AllowDBNull = true;
var col2 = new DataColumn("Col2", typeof(DateTime));
col2.AllowDBNull = true;
var col3 = new DataColumn("Col3", typeof(int));
col3.AllowDBNull = true;
var dataColumns = new DataColumn[] { col1, col2, col3 };
var dataTable = GetDataTable(dataColumns);
dataTable.Rows.Add(data);
IQueryFormatter queryFormatter = new QueryFormatter();
queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"NULL, NULL, NULL");
}
private TranslateResult Translate(Expression expression)
{
ProjectionExpression projection = expression as ProjectionExpression;
if (projection == null)
{
Expression modified = Evaluator.PartialEval(expression, CanBeEvaluatedLocally);
modified = new QueryBinder(this).Bind(modified);
//expression = new OrderByRewriter().Rewrite(expression);
modified = new UnusedColumnRemover().Remove(modified);
modified = new RedundantSubqueryRemover().Remove(modified);
projection = (ProjectionExpression)modified;
}
string commandText = new QueryFormatter().Format(projection.Source);
//LambdaExpression projector = new ProjectionBuilder().Build(projection);
LambdaExpression projector = ((UnaryExpression)((MethodCallExpression)expression).Arguments[1]).Operand as LambdaExpression;
return(new TranslateResult {
CommandText = commandText, Projector = projector
});
}
/// <summary>
/// Deletes every row in the table that satisfies the given predicate.
/// </summary>
/// <returns>The number of rows deleted.</returns>
public int Delete(Expression <Func <T, bool> > predicate)
{
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
var pred = PropertyReplacer.Replace(predicate.Body, predicate.Parameters[0]);
pred = ((SQLiteQueryProvider)Provider).Translate(pred);
if (pred is ProjectionExpression projection)
{
pred = projection.Source;
}
var args = new List <object>();
var cmdText = $"DELETE FROM [{Name}] WHERE {QueryFormatter.Format(pred, args)};";
_db.Execute(cmdText, args.ToArray());
return(_db.Changes);
}
internal IEnumerable <T> ExecuteQuery <T>(ProjectionExpression projection)
{
List <object> args = new List <object>();
string sql = QueryFormatter.Format(projection.Source, args);
Database.Log?.Invoke(sql);
using (SQLiteQuery query = Database.ExecuteQuery(sql, args.ToArray()))
{
LambdaExpression projectorExpr = ProjectionBuilder.Build(
projection.Projector,
projection.Source.Alias,
name => string.IsNullOrEmpty(name) ? 0 : query.GetColumnIndex(name)
);
var projector = (Func <SQLiteQueryProvider, SQLiteQuery, T>)projectorExpr.Compile();
while (query.Step())
{
yield return(projector(this, query));
}
}
}
public override string ToString()
{
return(QueryFormatter.Format(this));
}
public static string GetExperimentsByScientist(string nameScientist)
{
var name = nameScientist.Split(' ');
var expIdQuery = QueryFormatter.SelectFormatter.Get("exp_id"
, "investigators"
, new List <string> {
"first_name='" + name[0] + "'"
, "last_name='" + name[1] + "'"
, "patronymic='" + name[2] + "'"
});
var prodExpQuery = QueryFormatter.SelectFormatter.Get(new List <string> {
"time_begin"
, "time_end"
, "prod_id"
, "exp_id"
}
, "products_exps"
, expIdQuery
, "exp_id");
var expNameQuery = QueryFormatter.SelectFormatter
.Get(new List <string> {
"id", "name"
}, "experiments", expIdQuery, "id");
var joinQuery = QueryFormatter.Join(prodExpQuery
, expNameQuery
, new List <string> {
"exp_id=id"
}
, "t1"
, "t2");
joinQuery = QueryFormatter.SelectFormatter.Get("*", joinQuery);
var unionQuery = QueryFormatter.SelectFormatter
.Union(new List <string> {
"name", "prod_id"
}
, new List <string> {
"rockets"
, "gliders"
, "hang_gliders"
, "helicopters"
, "planes"
, "other_prods"
});
joinQuery = QueryFormatter.Join(joinQuery
, unionQuery
, new List <string> {
"prod_id=prod_id"
}
, "t3"
, "t4");
return(QueryFormatter.SelectFormatter.Get(new List <string> {
"t3.name"
, "t4.name"
, "time_begin"
, "time_end"
}
, joinQuery));
}
