public QueryModel LoadFromStoreQuery(IQueryModel qm, StoreQuery q)
{
var result = new QueryModel();
// read parameters
result.StoreParameters.DataService = q.DataService;
result.StoreParameters.QueryName = q.Name;
result.StoreParameters.Namespace = q.Namespace;
result.StoreParameters.QueryReturnType = q.ReturnTypeName;
result.Schema.Name = q.SchemaName;
result.Schema.Version = q.SchemaVersion;
// from
foreach (var t in q.Query.Tables)
{
var schemaTable = qm.Schema.Definitions[t.Value.TableName];
var ft = new QueryFromTable(schemaTable);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
// qc.RegenerateTableLinks();
// fields
foreach (var f in q.Query.Fields.Values)
{
var table = result.FromTables.FirstOrDefault(t => t.Alias == f.Field.ObjectAlias);
if (table == null)
{
throw new Exception($"Table with alias '{f.Field.ObjectAlias}' not found");
}
var storeProperty = table.Properties.FirstOrDefault(p => p.StoreProperty.Name == f.Field.FieldName);
if (storeProperty == null)
{
throw new Exception($"Field '{f.Field.FieldName}' not found in table with alias '{f.Field.ObjectAlias}'");
}
var newSelectionProperty = new QuerySelectProperty(table, storeProperty.StoreProperty)
{
IsOutput = f.IsOutput,
GroupByFunction = f.GroupByFunction,
Alias = f.FieldAlias != f.Field.FieldName ? f.FieldAlias : ""
// ToDo: Filter is not stored and cannot be loaded
};
result.SelectionProperties.Add(newSelectionProperty);
}
// Where
result.WhereClause = QueryExpressionHelper.QueryExprToString(q.Query.Where.Expression);
return(result);
}
public void ExpressionParseToStringAndPriorityMappedTest()
{
var res = new SqlJsonObjectResolver();
var expEngine = new SqlExpressionEngine(res);
var expr = expEngine.BuildExpressionTree("p.project_id = @p1 AND a.allocation_id = @p2 OR p.project_id = 0");
var result = QueryExpressionHelper.ReadFromSqlExpr(expr);
var text = QueryExpressionHelper.QueryExprToString(result, QueryExpressionHelper.CSharpOperatorsMap);
Assert.Equal("((p.project_id == @p1) && (a.allocation_id == @p2)) || (p.project_id == 0)", text);
}
public void ExpressionParseToStringOrPriorityParensTest()
{
var res = new SqlJsonObjectResolver();
var expEngine = new SqlExpressionEngine(res);
var expr = expEngine.BuildExpressionTree("p.project_id = @p1 AND (a.allocation_id = @p2 OR p.project_id = 0)");
var result = QueryExpressionHelper.ReadFromSqlExpr(expr);
var text = QueryExpressionHelper.QueryExprToString(result);
Assert.Equal("(p.project_id = @p1) AND ((a.allocation_id = @p2) OR (p.project_id = 0))", text);
}
public void ExpressionParseToStringTest()
{
var res = new SqlJsonObjectResolver();
var expEngine = new SqlExpressionEngine(res);
var expr = expEngine.BuildExpressionTree(
"((p.project_id = @p1) AND a.allocation_id = @p2) OR ((p.project_id = 0 AND a.allocation_id = 0) OR u.last_name = 'admin')");
var result = QueryExpressionHelper.ReadFromSqlExpr(expr);
var text = QueryExpressionHelper.QueryExprToString(result);
Assert.Equal("((p.project_id = @p1) AND (a.allocation_id = @p2)) OR (((p.project_id = 0) AND (a.allocation_id = 0)) OR (u.last_name = 'admin'))", text);
}
public string QueryExprToString(QueryExpression expr, Dictionary <string, string> operatorsMap = null)
{
return(QueryExpressionHelper.QueryExprToString(expr, operatorsMap));
}
private static IQueryModel ReadQueryTables(List <IQueryModel> loadedSubQueryList, string name, StoreSchema schema, StoreQueryDefinition queryDef, Dictionary <string, StoreQueryDefinition> subQueries)
{
var result = loadedSubQueryList.FirstOrDefault(l => l.Name == name);
if (result != null)
{
return(result);
}
queryDef = queryDef ?? subQueries[name];
result = new QueryModel()
{
Name = name
};
foreach (var t in queryDef.Tables)
{
if (schema.Definitions.ContainsKey(t.Value.TableName))
{
// not subquery table
var schemaTable = schema.Definitions[t.Value.TableName];
var ft = new QueryFromTable(schemaTable);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
else
{
if (!subQueries.ContainsKey(t.Value.TableName))
{
throw new Exception($"Table or SubQuery with name '{t.Value.TableName}' not found");
}
var subQuery = ReadQueryTables(loadedSubQueryList, t.Value.TableName, schema, null, subQueries);
var ft = new QueryFromTable(subQuery);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
}
loadedSubQueryList.Add(result);
// fields
foreach (var f in queryDef.Fields.Values)
{
var table = result.FromTables.FirstOrDefault(t => t.Alias == f.Field.ObjectAlias);
if (table == null)
{
throw new Exception($"Table with alias '{f.Field.ObjectAlias}' not found");
}
var storeProperty = table.Properties.FirstOrDefault(p => p.Name == f.Field.FieldName);
if (storeProperty == null)
{
throw new Exception($"Field '{f.Field.FieldName}' not found in table with alias '{f.Field.ObjectAlias}'");
}
var newSelectionProperty = new QuerySelectProperty(table, storeProperty.OriginalStoreProperty)
{
IsOutput = f.IsOutput,
GroupByFunction = f.GroupByFunction,
Having = f.GroupByFilter,
Alias = f.FieldAlias != f.Field.FieldName ? f.FieldAlias : ""
// ToDo: Filter is not stored and cannot be loaded
};
result.SelectionProperties.Add(newSelectionProperty);
}
// Joins
foreach (var j in queryDef.Joins)
{
}
result.FromTableJoins = queryDef.Joins.Select(j => new TableJoinModel()
{
IsDeleted = false,
JoinType = j.JoinType,
Source = j
}).ToList();
// Where
result.WhereClause = QueryExpressionHelper.QueryExprToString(queryDef.Where.Expression);
return(result);
}