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