protected override void SetRootSelectStatments(TempTableNode root, QueryExpand queryExpand)
{
Query query = queryExpand.Query;
root.TempTableName = GetTempTableName();
SelectClauseCollection clauses = GenerateSelectClauseCollection(query, out DbParameter[] dbParameters);
List <string> list = new List <string>
{
clauses.Select,
"INTO " + root.TempTableName,
clauses.From,
string.Join(" ", clauses.LeftJoins),
clauses.Where ?? string.Empty,
//clauses.OrderBy ?? string.Empty
};
string sql = string.Join(" ", list);
root.BeforeExecuteStatments.Add(new SQLStatment(sql, dbParameters));
//
string fetchSql = string.Format("SELECT * FROM {0}", root.TempTableName);
if (query.Orderby != null)
{
IEnumerable <string> orderby = query.Orderby.Orders.Select(order => order.Property + ((order is DescendingOrder) ? " DESC" : " ASC"));
fetchSql += " ORDER BY " + string.Join(",", orderby);
}
root.FetchTableStatment = new SQLStatment(fetchSql);
}
protected override void SetRootPagingStatments(TempTableNode root, QueryExpand queryExpand)
{
Query query = queryExpand.Query;
root.TempTableName = GetTempTableName();
PagingClauseCollection clauses = GeneratePagingClauseCollection(query, out DbParameter[] dbParameters);
List <string> list = new List <string>
{
string.Format("CREATE GLOBAL TEMPORARY TABLE {0} ON COMMIT PRESERVE ROWS AS", root.TempTableName),
clauses.Select,
clauses.From,
string.Join(" ", clauses.LeftJoins),
clauses.Where ?? string.Empty,
clauses.OrderBy ?? string.Empty
};
list.AddRange(clauses.Clauses);
string sql = string.Join(" ", list);
root.BeforeExecuteStatments.Add(new SQLStatment(sql, dbParameters));
//
string fetchSql = string.Format("SELECT * FROM {0}", root.TempTableName);
root.FetchTableStatment = new SQLStatment(fetchSql);
}
//SELECT [T].[Id], [T].[RoleName], [S1].[EmployeeId] [EmployeeId], ROW_NUMBER() OVER(ORDER BY [S1].[EmployeeId], [T].Id) [RowNumber]
//INTO [#V2]
//FROM [Roles] [T]
//INNER JOIN [UsersRoles] [S] ON [T].[Id] = [S].[RoleId]
//INNER JOIN [Users] [S1] ON [S1].[Id] = [S].[UserId]
//INNER JOIN [#V1] S2 ON S2.Id = [S1].[EmployeeId]
//LEFT JOIN...
//WHERE ...
private string GeneratePrimarySql(TempTableNode node, Relationship relationship, Query query, out DbParameter[] dbParameters,
out string tempTableName1, out string rowNumberAlias)
{
ForeignKey[] foreignKeys = CreateUndirectedForeignKeys(relationship, query.Schema);
IEnumerable <string> innerJoins = GenerateInnerJoins(foreignKeys, node.ParentTempTableName);
IEnumerable <string> relatedKeyColProps = GetRelatedKeyColProps(node, foreignKeys[0], out IEnumerable <string> relatedKeyCols);
IEnumerable <string> overOrderByCols = GetOverOrderByCols(relatedKeyCols, query);
rowNumberAlias = GetRowNumberAlias(query.Select, node.RelatedKey);
//ROW_NUMBER() OVER(ORDER BY [S1].[EmployeeId], [T].[Id]) [RowNumber]
string rowNumberSelect = string.Format("ROW_NUMBER() OVER(ORDER BY {0}) {1}", string.Join(", ", overOrderByCols), rowNumberAlias);
tempTableName1 = GetTempTableName();
SelectClauseCollection clauses = GenerateSelectClauseCollection(query, out dbParameters);
List <string> list = new List <string>
{
string.Join(", ", clauses.Select, string.Join(", ", relatedKeyColProps), rowNumberSelect),
string.Format("INTO {0}", tempTableName1),
clauses.From,
string.Join(" ", innerJoins),
string.Join(" ", clauses.LeftJoins),
clauses.Where ?? string.Empty,
};
return(string.Join(" ", list));
}
protected static ResultNode ToResultNode(TempTableNode node)
{
ResultNode resultNode;
if (node is EntityTempNode)
{
resultNode = new EntityResultNode(node.Name, node.Select, node.Table, node.RelatedKey, node.Entity);
}
else if (node is CollectionTempNode)
{
CollectionTempNode collectionTempNode = node as CollectionTempNode;
resultNode = new CollectionResultNode(node.Name, node.Select, collectionTempNode.OrderBy, node.Table, node.RelatedKey, collectionTempNode.Entity);
}
else
{
throw new NotSupportedException(node.GetType().ToString()); // never
}
resultNode.Path = node.Path;
List <ResultNode> resultChildren = new List <ResultNode>();
foreach (TempTableNode child in node.Children)
{
resultChildren.Add(ToResultNode(child));
}
resultNode.Children = resultChildren.ToArray();
return(resultNode);
}
protected IEnumerable <string> GetRelatedKeyColProps(TempTableNode node, ForeignKey fistForeignKey, out IEnumerable <string> relatedKeyCols)
{
List <string> colProps = new List <string>();
List <string> cols = new List <string>();
string[] aliases = node.RelatedKey.Values.ToArray();
for (int i = 0; i < fistForeignKey.RelatedColumns.Length; i++)
{
string decorateColumnName = DecorateColumnName(fistForeignKey.RelatedColumns[i], fistForeignKey.RelatedTableAlias);
colProps.Add(string.Format("{0} {1}", decorateColumnName, DecorateColumnAlias(aliases[i])));
cols.Add(decorateColumnName);
}
relatedKeyCols = cols;
return(colProps);
}
private void ExecuteNode(TempTableNode node)
{
foreach (SQLStatment statment in node.BeforeExecuteStatments)
{
int i = DatabaseExecuteNonQuery(statment.Sql, statment.Parameters);
}
node.Table = Database.ExecuteDataTable(node.FetchTableStatment.Sql, node.FetchTableStatment.Parameters);
node.Table.TableName = node.Name;
//Database.RecoverColumnNamesCaseSensitivity(node.Table, node.Select);
foreach (SQLStatment statment in node.AfterExecuteStatments)
{
int i = DatabaseExecuteNonQuery(statment.Sql, statment.Parameters);
}
}
public override ResultNode GetCollection(QueryExpand queryExpand)
{
TempTableNode root = BuildTempTableTree(queryExpand);
Database.Connection.Open();
try
{
Execute(root);
}
finally
{
Database.Connection.Close();
}
return(root.ToResultNode());
}
// breadth-first
private void ExecuteChildren(TempTableNode parent)
{
foreach (TempTableNode child in parent.Children)
{
ExecuteNode(child);
}
foreach (SQLStatment statment in GenerateDropTempTableStatements(parent.TempTableName))
{
int i = DatabaseExecuteNonQuery(statment.Sql);
}
foreach (TempTableNode child in parent.Children)
{
ExecuteChildren(child);
}
}
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));
}
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);
}
}
//SELECT [Id], [RoleName], [EmployeeId]
//INTO [#V3]
//FROM [#V2]
//WHERE [RowNumber] IN
//(SELECT MIN([RowNumber]) [RowNumber] FROM [#V2] GROUP BY [EmployeeId], [Id])
private string GenerateDistinctSql(TempTableNode node, Query query, string tempTableName1, string rowNumberAlias)
{
IEnumerable <string> groupbyProps = GetDistinctGroupByProps(node.DistinctKey);
string groupby = string.Join(", ", groupbyProps);
//SELECT MIN([RowNumber]) [RowNumber] FROM [#V2] GROUP BY [EmployeeId], [Id]
string groupbySql = string.Format("SELECT MIN({0}) {0} FROM {1} GROUP BY {2}", rowNumberAlias, tempTableName1, groupby);
//
node.TempTableName = GetTempTableName();
IEnumerable <string> selectProps = GetDistinctSelectProps(query.Select.Properties, node.RelatedKey);
List <string> list = new List <string>()
{
string.Format("SELECT {0}", string.Join(", ", selectProps)),
string.Format("INTO {0}", node.TempTableName),
string.Format("FROM {0}", tempTableName1),
string.Format("WHERE {0} IN ({1})", rowNumberAlias, groupbySql)
};
return(string.Join(" ", list));
}
//CREATE GLOBAL TEMPORARY TABLE TEMP3 ON COMMIT PRESERVE ROWS AS
//SELECT Id, RoleName, EmployeeId
//FROM TEMP2
//WHERE ROW_NUM IN
//(SELECT MIN(ROW_NUM) ROW_NUM FROM TEMP2 GROUP BY EmployeeId, Id)
private string GenerateDistinctSql(TempTableNode node, Query query, string tempTableName1, string rowNumberAlias)
{
IEnumerable <string> groupbyProps = GetDistinctGroupByProps(node.DistinctKey);
string groupby = string.Join(", ", groupbyProps);
//SELECT MIN(ROW_NUM) ROW_NUM FROM TEMP2 GROUP BY EmployeeId, Id
string groupbySql = string.Format("SELECT MIN({0}) {0} FROM {1} GROUP BY {2}", rowNumberAlias, tempTableName1, groupby);
//
node.TempTableName = GetTempTableName();
IEnumerable <string> selectProps = GetDistinctSelectProps(query.Select.Properties, node.RelatedKey);
List <string> list = new List <string>()
{
string.Format("CREATE GLOBAL TEMPORARY TABLE {0} ON COMMIT PRESERVE ROWS AS", node.TempTableName),
string.Format("SELECT {0}", string.Join(", ", selectProps)),
string.Format("FROM {0}", tempTableName1),
string.Format("WHERE {0} IN ({1})", rowNumberAlias, groupbySql)
};
return(string.Join(" ", list));
}
//Create TEMPORARY TABLE temp2
//SELECT T.Id, T.RoleName, S1.EmployeeId EmployeeId
//FROM Roles T
//INNER JOIN UsersRoles S ON T.Id = S.RoleId
//INNER JOIN Users S1 ON S1.Id = S.UserId
//INNER JOIN #V1 S2 ON S2.Id = S1.EmployeeId
//LEFT JOIN...
//WHERE ...
private string GeneratePrimarySql(TempTableNode node, Relationship relationship, Query query, out DbParameter[] dbParameters,
out string tempTableName1)
{
ForeignKey[] foreignKeys = CreateUndirectedForeignKeys(relationship, query.Schema);
IEnumerable <string> innerJoins = GenerateInnerJoins(foreignKeys, node.ParentTempTableName);
IEnumerable <string> relatedKeyColProps = GetRelatedKeyColProps(node, foreignKeys[0], out IEnumerable <string> relatedKeyCols);
tempTableName1 = GetTempTableName();
SelectClauseCollection clauses = GenerateSelectClauseCollection(query, out dbParameters);
List <string> list = new List <string>
{
string.Format("Create TEMPORARY TABLE {0}", tempTableName1),
string.Join(", ", clauses.Select, string.Join(", ", relatedKeyColProps)),
clauses.From,
string.Join(" ", innerJoins),
string.Join(" ", clauses.LeftJoins),
clauses.Where ?? string.Empty
};
return(string.Join(" ", list));
}
protected void Execute(TempTableNode node)
{
ExecuteNode(node);
ExecuteChildren(node);
}
protected abstract void SetNodeStatments(TempTableNode node, ExpandNode queryNode);
// overload
protected Dictionary <ExpandNode, TempTableNode> CreateChildren(ExpandNode queryNode, TempTableNode node,
out IEnumerable <string> relatedPropertiesForSelect)
{
return(CreateChildren(queryNode.Children, node, out relatedPropertiesForSelect));
}
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));
}
protected Dictionary <ExpandNode, TempTableNode> CreateChildren(IEnumerable <ExpandNode> qChildren, TempTableNode node,
out IEnumerable <string> relatedPropertiesForSelect)
{
Dictionary <ExpandNode, TempTableNode> childDict = new Dictionary <ExpandNode, TempTableNode>();
List <string> childrenForSelect = new List <string>();
foreach (ExpandNode qChild in qChildren)
{
TempTableNode child;
string[] select = new string[qChild.Query.Select.Properties.Length];
qChild.Query.Select.Properties.CopyTo(select, 0);
if (qChild is EntityExpandNode)
{
child = new EntityTempNode(qChild.Property, select, qChild.Entity);
}
else if (qChild is CollectionExpandNode)
{
Order[] orderby = GetOrders(qChild.Query);
child = new CollectionTempNode(qChild.Property, select, orderby, qChild.Entity);
}
else
{
throw new NotSupportedException(qChild.GetType().ToString()); // never
}
child.Path = node.Path + "/" + qChild.Property;
//
DirectRelationship firstDirectRelationship = qChild.Relationship.DirectRelationships[0];
Dictionary <string, string> relatedKey = new Dictionary <string, string>();
for (int i = 0; i < firstDirectRelationship.Properties.Length; i++)
{
relatedKey.Add(firstDirectRelationship.Properties[i], firstDirectRelationship.RelatedProperties[i]);
}
child.RelatedKey = relatedKey;
node.Children.Add(child);
// NativeProperties
childrenForSelect.AddRange(firstDirectRelationship.Properties);
childDict.Add(qChild, child);
}
relatedPropertiesForSelect = childrenForSelect.Distinct();
return(childDict);
}
protected abstract void SetRootPagingStatments(TempTableNode root, QueryExpand queryExpand);
