public void RemoveJoinsForTable(QueryFromTable t)
{
var lostJoins = SelectedQuery.FromTableJoins.Where(j => j.Source.LeftObjectAlias == t.Alias || j.Source.RightObjectAlias == t.Alias);
SelectedQuery.FromTableJoins = SelectedQuery.FromTableJoins.Except(lostJoins).ToList();
UpdateLinksFromTableJoins();
}
public void SelectFieldsUniquePassTest()
{
var qc = new QueryController(null);
var t1 = new QueryFromTable(new StoreDefinition {
Name = "table1"
})
{
Alias = "t"
};
var p1 = new QueryFromProperty {
Alias = "id2", OriginalStoreProperty = new StoreProperty {
Name = "id"
}
};
var p2 = new QueryFromProperty {
OriginalStoreProperty = new StoreProperty {
Name = "id"
}
};
var p3 = new QueryFromProperty {
Alias = "myid", OriginalStoreProperty = new StoreProperty {
Name = "id"
}
};
qc.AddSelectionProperty(t1, p1);
qc.AddSelectionProperty(t1, p2);
qc.AddSelectionProperty(t1, p3);
var rule = new SelectFieldsUnique();
var vr = rule.Validate(qc.MainQuery);
Assert.Null(vr);
}
public void SelectFieldsUniqueFailTest()
{
var qc = new QueryController(null);
var t1 = new QueryFromTable {
Alias = "t", StoreDefinition = new StoreDefinition {
Name = "table1"
}
};
var p1 = new QueryFromProperty {
Alias = "id", StoreProperty = new StoreProperty {
Name = "x1"
}
};
var p2 = new QueryFromProperty {
StoreProperty = new StoreProperty {
Name = "id"
}
};
var p3 = new QueryFromProperty {
Alias = "myid", StoreProperty = new StoreProperty {
Name = "x2"
}
};
qc.AddSelectionProperty(t1, p1);
qc.AddSelectionProperty(t1, p2);
qc.AddSelectionProperty(t1, p3);
var rule = new SelectFieldsUnique();
var vr = rule.Validate(qc);
Assert.NotNull(vr);
Assert.True(vr.IsFailed);
Assert.Contains("id", vr.Message);
Assert.DoesNotContain("myid", vr.Message);
}
public void RemoveSelectionProperty(QueryFromTable table, QueryFromProperty property)
{
var item = SelectedQuery.SelectionProperties.FirstOrDefault(s => s.StoreProperty.Name == property.Name && s.FromTable.Alias == table.Alias);
if (item != null)
{
SelectedQuery.SelectionProperties.Remove(item);
}
}
public void AddFromTable(StoreDefinition table)
{
var ft = new QueryFromTable(table);
ft.Alias = GetDefaultAlias(ft);
FromTables.Add(ft);
RegenerateTableLinks();
_engine.SelectPropertiesFromNewTable(this, ft);
}
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 AddSelectionProperty(QueryFromTable table, QueryFromProperty property)
{
if (!SelectedQuery.SelectionProperties.Any(s => s.FromTable.Alias == table.Alias && s.StoreProperty.Name == property.Name))
{
var newSelectProperty = new QuerySelectProperty(table, property.OriginalStoreProperty)
{
IsOutput = true, Alias = property.Alias
};
SelectedQuery.SelectionProperties.Add(newSelectProperty);
}
}
public void GenerateJoinsForNewTable(QueryFromTable t)
{
var joins = GenerateJoins();
joins = joins.Where(j => j.LeftObjectAlias == t.Alias || j.RightObjectAlias == t.Alias).ToList();
var addJoins = joins.Select(j => new TableJoinModel {
Source = j, JoinType = "Inner"
});
SelectedQuery.FromTableJoins.AddRange(addJoins);
UpdateLinksFromTableJoins();
}
public void SelectPropertiesFromNewTable(IQueryController qc, QueryFromTable newTable)
{
var selectedProps = qc.SelectionProperties.ToDictionary(p => p.OutputName, p => p);
foreach (var p in newTable.Properties)
{
if (!selectedProps.ContainsKey(p.StoreProperty.Name))
{
p.Selected = true;
qc.AddSelectionProperty(newTable, p);
}
}
}
//public void RegenerateTableLinks()
//{
// var joins = GenerateJoins();
// var newJoins = joins.Where(j => !SelectedQuery.FromTableJoins.Any(f => f.Source.GetJoinString() == j.GetJoinString()));
// SelectedQuery.FromTableJoins.AddRange(newJoins.Select(j => new TableJoinModel { Source = j, JoinType = "Inner" }));
// var lostJoins = SelectedQuery.FromTableJoins.Where(j => !SelectedQuery.FromTables.Any(t => t.Alias == j.Source.LeftObjectAlias) || !SelectedQuery.FromTables.Any(t => t.Alias == j.Source.RightObjectAlias));
// SelectedQuery.FromTableJoins = SelectedQuery.FromTableJoins.Except(lostJoins).ToList();
// UpdateLinksFromTableJoins();
//}
private string GetDefaultAlias(QueryFromTable ft)
{
var count = SelectedQuery.FromTables.Where(t => t.Name == ft.Name).Count();
var sfx = count > 0 ? (count + 1).ToString() : "";
var used = SelectedQuery.FromTables.Select(t => t.Alias).ToList();
for (int i = 1; i <= 5; i++)
{
var alias = ft.Name.Substring(0, i).ToLower() + sfx;
if (!used.Contains(alias))
{
return(alias);
}
}
// alias not found
return("");
}
public void AddFromTable(DesignQueryObject qo)
{
if (qo.IsSubQuery)
{
var ft = new QueryFromTable(qo.Query);
ft.Alias = GetDefaultAlias(ft);
SelectedQuery.FromTables.Add(ft);
//RegenerateTableLinks();
GenerateJoinsForNewTable(ft);
_engine.SelectPropertiesFromNewTable(this, ft);
}
else
{
var ft = new QueryFromTable(qo.Table);
ft.Alias = GetDefaultAlias(ft);
SelectedQuery.FromTables.Add(ft);
//RegenerateTableLinks();
GenerateJoinsForNewTable(ft);
_engine.SelectPropertiesFromNewTable(this, ft);
}
}
public void SelectFieldsUniqueFailTest()
{
var qc = new QueryController(null);
var t1 = new QueryFromTable(new StoreDefinition {
Name = "table1"
})
{
Alias = "t"
};
var p1 = new QueryFromProperty {
Alias = "id", OriginalStoreProperty = new StoreProperty {
Name = "x1"
}
};
var p2 = new QueryFromProperty {
OriginalStoreProperty = new StoreProperty {
Name = "id"
}
};
var p3 = new QueryFromProperty {
Alias = "myid", OriginalStoreProperty = new StoreProperty {
Name = "x2"
}
};
qc.AddSelectionProperty(t1, p1);
qc.AddSelectionProperty(t1, p2);
qc.AddSelectionProperty(t1, p3);
var rule = new SelectFieldsUnique();
var vr = rule.Validate(qc.MainQuery);
Assert.NotNull(vr);
Assert.Equal(ValidationResultTypes.Error, vr.Type);
Assert.Contains("id", vr.Message);
Assert.DoesNotContain("myid", vr.Message);
}
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);
}
