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