internal protected virtual SelectClauseCollection GenerateSelectClauseCollection(Query query, out IReadOnlyDictionary <string, object> dbParameterValues)
{
IReadOnlyDictionary <string, object> constants = new Dictionary <string, object>();
IReadOnlyDictionary <string, string> paramMapping = new Dictionary <string, string>();
Table table = new Table(query.Properties, query.Entity, query.Schema);
SelectClauseCollection clauses = new SelectClauseCollection()
{
Select = GenerateSelect(query.Select, table),
From = "FROM " + DecorateTableName(table.Name)
};
clauses.From += " " + DecorateTableAlias(table.TableAlias);
clauses.LeftJoins = GenerateLeftJoins(table.ForeignKeyTrees);
if (query.Filter != null)
{
clauses.Where = GenerateWhere(query, table, out constants, out paramMapping);
}
if (query.Orderby != null)
{
clauses.OrderBy = GenerateOrderBy(query.Orderby, table);
}
dbParameterValues = GetDbParameterValues(constants, paramMapping, query.Parameters.ParameterValues);
return(clauses);
}
protected SelectClauseCollection GenerateSelectClauseCollection(Query query, out DbParameter[] dbParameters)
{
SelectClauseCollection clauses = GenerateSelectClauseCollection(query, out IReadOnlyDictionary <string, object> dbParameterValues);
dbParameters = CreateParameters(dbParameterValues);
return(clauses);
}
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>
{
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
};
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);
}
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);
}
//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));
}
public virtual string GenerateCountStatement(Query query, out IReadOnlyDictionary <string, object> dbParameterValues)
{
SelectClauseCollection clauses = GenerateCountClauseCollection(query, out dbParameterValues);
List <string> list = new List <string>
{
clauses.Select,
clauses.From,
string.Join(" ", clauses.LeftJoins),
clauses.Where ?? string.Empty
};
return(string.Join(" ", list));
}
protected override PagingClauseCollection GeneratePagingClauseCollection(Query query, out IReadOnlyDictionary <string, object> dbParameterValues)
{
long top = (query.Top == 0) ? long.MaxValue : query.Top;
SelectClauseCollection selectClauses = GenerateSelectClauseCollection(query, out dbParameterValues);
PagingClauseCollection PagingClauses = new PagingClauseCollection(selectClauses)
{
Clauses = new string[1]
};
PagingClauses.Clauses[0] = string.Format("LIMIT {0},{1}", query.Skip, top);
return(PagingClauses);
}
internal protected override PagingClauseCollection GeneratePagingClauseCollection(Query query, out IReadOnlyDictionary <string, object> dbParameterValues)
{
SelectClauseCollection selectClauses = GenerateSelectClauseCollection(query, out dbParameterValues);
PagingClauseCollection pagingClauses = new PagingClauseCollection(selectClauses);
List <string> clauses = new List <string>
{
string.Format("OFFSET {0} ROWS", query.Skip)
};
if (query.Top != 0)
{
clauses.Add(string.Format("FETCH NEXT {0} ROWS ONLY", query.Top));
}
pagingClauses.Clauses = clauses.ToArray();
return(pagingClauses);
}
//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));
}
