protected override void SetNodeStatments(TempTableNode node, ExpandNode expandNode) { string primarySql = GeneratePrimarySql(node, expandNode.Relationship, expandNode.Query, out DbParameter[] dbParameters, out string tempTableName1, out string rowNumberAlias); node.BeforeExecuteStatments.Add(new SQLStatment(primarySql, dbParameters)); string distinctSql = GenerateDistinctSql(node, expandNode.Query, tempTableName1, rowNumberAlias); node.BeforeExecuteStatments.Add(new SQLStatment(distinctSql)); string fetchTableSql = string.Format("SELECT * FROM {0}", node.TempTableName); node.FetchTableStatment = new SQLStatment(fetchTableSql); node.AfterExecuteStatments.AddRange(GenerateDropTempTableStatements(tempTableName1)); }
// Trips($filter=contains(Name, {f5eac763-e025-4cf8-aa1d-9bb3a2986515}) $select=Id,Name $orderby=Id desc $expand=Hotels),Contacts($filter=Name eq {670bc07f-6b33-47fb-be01-63834e25ff21}) protected ExpandNode[] Compose(string value, XElement parentSchema, string parentPath) { List <ExpandNode> expands = new List <ExpandNode>(); string val = value; // Dictionary <string, string> placeholders = new Dictionary <string, string>(); val = Regex.Replace(val, ParenthesesPairPattern, new MatchEvaluator(m => { string guid = GetGuid(); placeholders.Add(guid, m.Value); return(guid); })); // string[] expandStrings = val.Split(','); for (int i = 0; i < expandStrings.Length; i++) { string expandString = expandStrings[i].Trim(); expandString = DecodeString(expandString, placeholders); string property = GetProperty(expandString, out string select, out string filter, out string orderby, out string expand); XElement[] propertyPath = Schema.GenerateExpandPropertyPath(parentSchema, property); ExpandProperty oProperty = ExpandProperty.Create(property, propertyPath, parentSchema, Schema); string path = parentPath + "/" + property; ExpandNode oExpand = ExpandNode.Create(oProperty, select, filter, orderby, Schema, Parameters); oExpand.Path = path; expands.Add(oExpand); // if (!string.IsNullOrWhiteSpace(expand)) { oExpand.Children = Compose(expand, propertyPath[propertyPath.Length - 1], path); } } return(expands.ToArray()); }
protected void Compose(ExpandNode queryNode, TempTableNode node) { Query query = queryNode.Query; Dictionary <ExpandNode, TempTableNode> childrenDict = CreateChildren(queryNode, node, out IEnumerable <string> relatedPropertiesForSelect); IEnumerable <string> unionSelect = new List <string>(queryNode.Query.Select.Properties); unionSelect = unionSelect.Union(relatedPropertiesForSelect); IEnumerable <string> keyProperties = GetKeyProperties(query); unionSelect = unionSelect.Union(keyProperties); foreach (string key in node.RelatedKey.Keys.ToList()) { string prop = node.RelatedKey[key]; string alias = prop; int i = 1; while (unionSelect.Contains(alias)) { alias += i.ToString(); i++; } node.RelatedKey[key] = alias; } node.DistinctKey = node.RelatedKey.Values.Union(keyProperties); queryNode.Query.Select.Properties = unionSelect.ToArray(); queryNode.Query.Properties.UnionFieldProperties(unionSelect); // SetNodeStatments(node, queryNode); // foreach (KeyValuePair <ExpandNode, TempTableNode> pair in childrenDict) { pair.Value.ParentTempTableName = node.TempTableName; Compose(pair.Key, pair.Value); } }
protected ExpandNode Compose(Expand expand, XElement parentSchema, string parentPath) { string property = expand.Property; XElement[] propertyPath = Schema.GenerateExpandPropertyPath(parentSchema, property); ExpandProperty oProperty = ExpandProperty.Create(property, propertyPath, parentSchema, Schema); string path = parentPath + "/" + property; ExpandNode oExpand = ExpandNode.Create(oProperty, expand.Select, expand.Filter, expand.Orderby, Schema, Parameters); oExpand.Path = path; oExpand.Children = new ExpandNode[expand.Children.Length]; for (int i = 0; i < expand.Children.Length; i++) { oExpand.Children[i] = Compose(expand.Children[i], propertyPath[propertyPath.Length - 1], path); } return(oExpand); }
// overload protected Dictionary <ExpandNode, TempTableNode> CreateChildren(ExpandNode queryNode, TempTableNode node, out IEnumerable <string> relatedPropertiesForSelect) { return(CreateChildren(queryNode.Children, node, out relatedPropertiesForSelect)); }
protected abstract void SetNodeStatments(TempTableNode node, ExpandNode queryNode);
protected override void SetNodeStatments(TempTableNode node, ExpandNode expandNode) { Query query = expandNode.Query; string primarySql = GeneratePrimarySql(node, expandNode.Relationship, query, out DbParameter[] dbParameters, out string tempTableName1); node.BeforeExecuteStatments.Add(new SQLStatment(primarySql, dbParameters)); // string auto_id = GetAutoIdColumnName(query); //ALTER TABLE temp2 ADD auto_id int auto_increment key string auto_increment_sql = string.Format("ALTER TABLE temp2 ADD {0} int auto_increment key", auto_id); node.BeforeExecuteStatments.Add(new SQLStatment(auto_increment_sql)); // IEnumerable <string> groupbyProps = GetDistinctGroupByProps(node.DistinctKey); string groupby = string.Join(", ", groupbyProps); //Create TEMPORARY TABLE temp3 //SELECT MIN(auto_id) auto_id FROM temp2 GROUP BY EmployeeId, Id string tempTableName2 = GetTempTableName(); string groupby_sql = string.Format("SELECT MIN({0}) {0} FROM {1} GROUP BY {2}", auto_id, tempTableName1, groupby); groupby_sql = string.Format("Create TEMPORARY TABLE {0} ", tempTableName2) + groupby_sql; node.BeforeExecuteStatments.Add(new SQLStatment(groupby_sql)); //ALTER TABLE temp3 ADD PRIMARY KEY (auto_id) string add_key_sql = string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})", tempTableName2, auto_id); node.BeforeExecuteStatments.Add(new SQLStatment(add_key_sql)); // IEnumerable <string> selectProps = GetDistinctSelectProps(query.Select.Properties, node.RelatedKey); //Create TEMPORARY TABLE temp4 //SELECT [Id], [RoleName], [EmployeeId] //FROM temp2 //WHERE auto_id IN (SELECT auto_id FROM temp3) node.TempTableName = GetTempTableName(); List <string> list = new List <string>() { string.Format("Create TEMPORARY TABLE {0}", node.TempTableName), string.Format("SELECT {0}", string.Join(", ", selectProps)), string.Format("FROM {0}", tempTableName1), string.Format("WHERE {0} IN (SELECT {0} FROM {1})", auto_id, tempTableName2) }; string distinctSql = string.Join(" ", list); node.BeforeExecuteStatments.Add(new SQLStatment(distinctSql)); //SELECT * FROM temp4 string fetchSql = string.Format("SELECT * FROM {0}", node.TempTableName); node.FetchTableStatment = new SQLStatment(fetchSql); // node.AfterExecuteStatments.AddRange(GenerateDropTempTableStatements(tempTableName1)); node.AfterExecuteStatments.AddRange(GenerateDropTempTableStatements(tempTableName2)); }