public static SchemaObjectReference GetSchemaObjectReference(
this CommonTableExpression commonTableExpression,
ILogger logger,
SchemaFile file
)
{
var cteColumns = commonTableExpression
.QueryExpression
.GetFields(logger, file);
if (commonTableExpression.Columns.Any())
{
for (var i = 0; i < commonTableExpression.Columns.Count(); i++)
{
cteColumns[i].Name = commonTableExpression.Columns[i].Value;
}
}
var cte = new Cte()
{
Identifier = commonTableExpression.ExpressionName.Value,
File = file,
Columns = cteColumns,
};
var cteReference = new SchemaObjectReference()
{
Alias = commonTableExpression.ExpressionName.Value,
Identifier = cte.GetQualifiedIdentfier(),
Value = cte,
};
return(cteReference);
}
public QsiDerivedTableNode VisitCommonTableExpression(CommonTableExpression commonTableExpression)
{
return(TreeHelper.Create <QsiDerivedTableNode>(n =>
{
n.Source.SetValue(VisitQueryExpression(commonTableExpression.QueryExpression));
var columnsDeclaration = new QsiColumnsDeclarationNode();
if (commonTableExpression.Columns == null || commonTableExpression.Columns.Count == 0)
{
columnsDeclaration.Columns.Add(new QsiAllColumnNode());
}
else
{
columnsDeclaration.Columns.AddRange(CreateSequentialColumnNodes(commonTableExpression.Columns));
}
n.Columns.SetValue(columnsDeclaration);
if (commonTableExpression.ExpressionName != null)
{
n.Alias.SetValue(CreateAliasNode(commonTableExpression.ExpressionName));
}
SqlServerTree.PutFragmentSpan(n, commonTableExpression);
}));
}
public void TestGroupedTableJoin()
{
var contact = new Table("ct", "contact");
var phone = new Table("p", "phone");
var company = new Table("c", "company");
var network = new Table("pn", "phonenetwork");
var selectQuery = new SelectQuery().SelectAll().From(company)
.GroupedJoin(contact, "company_id", company, "id",
x => x.Join(phone, "contact_id", contact, "id")
.Join(network, "id", phone, "network_id")
);
var cte = new CommonTableExpression {
Alias = "Cte", Query = selectQuery
};
var query = new Query();
query.CommonTableExpressions.Add(cte);
query.SelectQuery = new SelectQuery()
.From(cte.Alias, cte.Alias)
.Select("CompanyName")
.OrderBy(string.Empty, "CompanyName");
var compiled = query.Compile(false);
Console.WriteLine(compiled.Sql);
}
public void TestUnionCte()
{
var firstTable = new Table("t1", "FirstTable");
var secondTable = new Table("t2", "SecondTable");
var thirdTable = new Table("t3", "ThirdTable");
var selectQuery = new SelectQuery().SelectAll().From(firstTable)
.UnionAll(new SelectQuery().SelectAll().From(secondTable))
.Union(new SelectQuery().SelectAll().From(thirdTable));
var cte = new CommonTableExpression {
Alias = "Cte", Query = selectQuery
};
var query = new Query();
query.CommonTableExpressions.Add(cte);
query.SelectQuery = new SelectQuery()
.From(cte.Alias, cte.Alias)
.Select("ProductName")
.OrderBy(string.Empty, "ProductName");
var compiled = query.Compile(false);
Console.WriteLine(compiled.Sql);
}
public void TestSelectDistinct()
{
var firstTable = new Table("t1", "FirstTable");
var selectQuery = new SelectQuery()
.SelectDistinct()
.SelectAll()
.From(firstTable);
var cte = new CommonTableExpression {
Alias = "Cte", Query = selectQuery
};
var query = new Query();
query.CommonTableExpressions.Add(cte);
query.SelectQuery = new SelectQuery(selectDistinct: true)
.From(cte.Alias, cte.Alias)
.Select("ProductName")
.OrderBy(string.Empty, "ProductName");
var compiled = query.Compile(false);
Console.WriteLine(compiled.Sql);
}
public CompiledCommonTableExpression Compile(CommonTableExpression cte, IQueryParameterManager parameters)
{
var result = new CompiledCommonTableExpression();
result.Alias = cte.Alias;
var queryCompiler = new QueryCompiler();
var selectQueryCompiler = queryCompiler.GetSelectCompiler(cte.Query);
result.SelectQuery = selectQueryCompiler.Compile(cte.Query, parameters);
result.SelectQuery.OrderBy = null; // order by is not allowd in CTE, but may have been used for over( ) statements
return(result);
}
public override void Visit(CommonTableExpression node)
{
if (dict.ContainsKey(this.id_requete_courante))
{
this.dict[id_requete_courante].Add(node);
}
else
{
List <TSqlFragment> listCommonTable = new List <TSqlFragment>();
listCommonTable.Add(node);
this.dict.Add(id_requete_courante, listCommonTable);
}
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add(new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference()
{
SchemaObject = tableName
};
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return(delete);
}
public override CommonTableExpressionStatement Execute()
{
_statement = new CommonTableExpressionStatement();
do
{
var commonTableExpression = new CommonTableExpression();
commonTableExpression.Name = GetDotNotationIdentifier();
if (Tokenizer.IsNextToken(Constants.OpenBracket))
{
using (Tokenizer.ExpectBrackets())
{
commonTableExpression.ColumnNames.AddRange(GetIdentifierList());
}
}
ExpectToken(Constants.As);
using (Tokenizer.ExpectBrackets())
{
ExpectToken(Constants.Select);
var parser = new SelectStatementParser(Tokenizer);
var statement = parser.Execute();
commonTableExpression.Fields = statement.Fields;
commonTableExpression.From = statement.From;
commonTableExpression.Top = statement.Top;
commonTableExpression.Distinct = statement.Distinct;
commonTableExpression.GroupBy = statement.GroupBy;
commonTableExpression.OrderBy = statement.OrderBy;
commonTableExpression.Having = statement.Having;
commonTableExpression.Where = statement.Where;
commonTableExpression.SetOperation = statement.SetOperation;
}
_statement.CommonTableExpressions.Add(commonTableExpression);
}while (Tokenizer.HasMoreTokens && Tokenizer.TokenEquals(Constants.Comma));
ExpectToken(Constants.Select);
var statementParser = new SelectStatementParser(Tokenizer);
_statement.Statement = statementParser.Execute();
return(_statement);
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add( new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference() {SchemaObject = tableName };
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return delete;
}
/// <summary>
/// CTE式をインスタンス化します。
/// </summary>
public WithCteItem(string expressionName, QueryItem query, params string[] columns)
{
try
{
var expressionNameIdentifier = (IdentifierLiteral)TransactSQL._Parser.ParseConstantOrIdentifier(new StringReader(expressionName), out IList <ParseError> errors);
if (errors.Count == 0)
{
CommonTableExpression = new CommonTableExpression()
{
ExpressionName = new Identifier()
{
Value = expressionNameIdentifier.Value,
QuoteType = expressionNameIdentifier.QuoteType,
},
QueryExpression = query.QueryExpression,
};
_Query = query;
foreach (var column in columns)
{
var columnIdentifier = (IdentifierLiteral)TransactSQL._Parser.ParseConstantOrIdentifier(new StringReader(column), out errors);
if (errors.Count == 0)
{
CommonTableExpression.Columns.Add(new Identifier()
{
Value = columnIdentifier.Value,
QuoteType = columnIdentifier.QuoteType,
});
}
else
{
throw new ArgumentException("指定された CTE式 の解析に失敗しました。");
}
}
}
else
{
throw new ArgumentException("指定された CTE式 の解析に失敗しました。");
}
}
catch (InvalidCastException)
{
throw new ArgumentException("指定された CTE式 の解析に失敗しました。");
}
}
protected override List <CommonTableExpression> BuildCommonTableExpressions(MappedSearchRequest request)
{
var result = new List <CommonTableExpression>();
// build the date stats CTE
if (IsDateQuery(request))
{
using (new DebugTimer("StatsQueryBuilder.BuildCommonTableExpressions - date"))
{
var dateStatsQuery = _dataSourceComponents.DateStatsQueryBuilder.Build(request);
var cte = new CommonTableExpression
{
Alias = _dateStatsTableAlias,
Query = dateStatsQuery.SelectQuery
};
result.Add(cte);
}
}
// bulid the transpose stats CTE
var allTransposeStatsColumns = GetAllTransposeStatsColumns(request);
if (allTransposeStatsColumns != null && allTransposeStatsColumns.Any())
{
using (new DebugTimer("StatsQueryBuilder.BuildCommonTableExpressions - transpose"))
{
var transposeCteQuery = _dataSourceComponents.TransposeStatsQueryBuilder.Build(request);
var transposeStatsCte = new CommonTableExpression
{
Alias = _transposeStatsTableAlias,
Query = transposeCteQuery.SelectQuery
};
result.Add(transposeStatsCte);
}
}
return(result);
}
public override void Visit(CommonTableExpression node)
{
CommonTableExpressionIdentifiers.Add(node.ExpressionName.Value);
}
/// <summary>
/// CTE式をインスタンス化します。
/// </summary>
internal WithCteItem(CommonTableExpression commonTableExpression)
{
CommonTableExpression = commonTableExpression;
}
public override void ExplicitVisit(CommonTableExpression fragment)
{
_fragments.Add(fragment);
}
public override void Visit(CommonTableExpression commonTableExpression)
{
var result = ParseSelect(commonTableExpression.QueryExpression);
StoreResult(result);
}
public void Visit(CommonTableExpression component)
{
throw new NotImplementedException();
}
protected virtual void AddCommonTableExpressions(
MappedSearchRequest request,
Query result,
out Dictionary <string, CommonTableExpression> cteDict)
{
cteDict = new Dictionary <string, CommonTableExpression>();
var cteTables =
_tableMappings.GetAllTableRelationships()
.Where(x => x.IsDirect && x.RelationshipType == TableRelationshipType.OneToMany || x.RelationshipType == TableRelationshipType.ManyToOne)
.Select(x => x.Table2.KnownTableName)
.Union(_tableMappings.GetAllTableRelationships()
.Where(x => x.IsDirect && x.RelationshipType == TableRelationshipType.OneToMany || x.RelationshipType == TableRelationshipType.ManyToOne)
.Select(x => x.Table1.KnownTableName)
)
.Distinct()
.ToList();
var siblings = _tableMappings.GetAllTableRelationships()
.Where(x => x.RelationshipType == TableRelationshipType.OneToOne && cteTables.Contains(x.Table1.KnownTableName))
.Select(x => x.Table2.KnownTableName)
.Union(_tableMappings.GetAllTableRelationships()
.Where(x => x.RelationshipType == TableRelationshipType.OneToOne && cteTables.Contains(x.Table2.KnownTableName))
.Select(x => x.Table1.KnownTableName)
).Distinct().ToList();
cteTables = cteTables.Union(siblings).Distinct().ToList();
// todo : may be able to optimise this by not building tables which are not needed and later removed
foreach (var knownTable in cteTables)
{
var table = _tableMappings.GetTableMapping(knownTable);
if (table == null)
{
throw new Exception(string.Format("Could not find '{0}' table in table mappings", knownTable));
}
if (table.TableType == TableType.Stats)
{
continue;
}
using (new DebugTimer("CteQueryBuilder.Build - " + table.CteAlias))
{
var cteBuilder = _dataSourceComponents.OneToManyCteQueryBuliderFactory.Create(table.KnownTableName);
var cteQuery = cteBuilder.Build(request);
var cte = new CommonTableExpression {
Alias = table.CteAlias, Query = cteQuery.SelectQuery
};
result.CommonTableExpressions.Add(cte);
cteDict.Add(table.Alias, cte);
}
}
using (new DebugTimer("CteQueryBuilder.Build - dataCte"))
{
var dataQuery = _dataSourceComponents.DataQueryBuilder.Build(request);
var dataCte = new CommonTableExpression {
Alias = "data", Query = dataQuery.SelectQuery
};
result.CommonTableExpressions.Add(dataCte);
cteDict.Add("data", dataCte);
}
if (request.SummarizeByColumn != null && _dataSourceComponents.MissingSummarizeDataQueryBuilder.IncludeInQuery(request))
{
using (new DebugTimer("CteQueryBuilder.Build - missingSummarizeDataCte"))
{
var dataQuery = _dataSourceComponents.MissingSummarizeDataQueryBuilder.Build(request);
var dataCte = new CommonTableExpression {
Alias = "missingSummarizeData", Query = dataQuery.SelectQuery
};
result.CommonTableExpressions.Add(dataCte);
cteDict.Add("missingSummarizeData", dataCte);
}
}
if (_dataSourceComponents.TableMappings.GetAllTables().Any(x => x.TableType == TableType.Stats))
{
using (new DebugTimer("CteQueryBuilder.Build - statsCte"))
{
var statsQuery = _dataSourceComponents.StatsQueryBuilder.Build(request);
var statsCte = new CommonTableExpression {
Alias = "stats", Query = statsQuery.SelectQuery
};
result.CommonTableExpressions.AddRange(statsQuery.CommonTableExpressions);
result.CommonTableExpressions.Add(statsCte);
cteDict.Add("statsCte", statsCte);
}
}
}
public override void Visit(CommonTableExpression node)
{
TableAliases.Add(node.ExpressionName.Value);
}
private void AnalyzeCommonTableExpression(Dictionary <string, List <TableParsingResult> > cteModel, CommonTableExpression cte)
{
string cteName = cte.ExpressionName.Value.ToLower();
if (cte.QueryExpression is QuerySpecification querySpecification)
{
var items = ExtractTablesFromQuerySpecification(querySpecification); //ExtractTablesUsedInFromClause(querySpecification.FromClause);
// flatten out self rencing ctes
// ;with cte1 as (),
// cte2 as (select from cte1 inner join users)
foreach (var cte1 in cteModel)
{
var item = items.Find(x => x.TableName == cte1.Key);
if (item != null)
{
items.Remove(item);
foreach (var table in cte1.Value)
{
items.AddIfNotExists(table.TableName, table.OperationType, table.Alias);
}
}
}
cteModel.Add(cteName, items);
}
}
public override void Visit(CommonTableExpression node) { this.action(node); }
public void TestShorthand()
{
var countryTable = new Table("c", "Country");
var teamTable = new Table("t", "Team");
var playerTable = new Table("p", "Player");
var cteQuery = new SelectQuery()
// SELECT
.SelectRowNumber("_ROW")
.SelectOrderByColumn("_SORT")
.SelectCount("_COUNT")
.SelectGroupKey("_GROUP_KEY")
.Select("1 as ONE")
.SelectAll()
.Select(countryTable, "ID", "C_ID", Aggregate.Min) // should not aggregate, its the group by
.Select(teamTable, "ID", "T_ID", Aggregate.Min)
.Select(teamTable, "Name", "T_NAME", Aggregate.Avg)
.Select(playerTable, "Name", "P_NAME", Aggregate.Sum)
.Select(playerTable, "IsMale", "P_ALLMALE", Aggregate.Bit)
.Select(playerTable, "IsProfessional", "P_HASPRO", Aggregate.BitMax)
// FROM
.From(countryTable)
.LeftJoin(teamTable, "CountryID", countryTable, "ID")
.Join(playerTable, "TeamID", teamTable, "ID", JoinType.InnerJoin, "AND 1 = 1")
// WHERE
.Where(Combine.And)
.Where("p.Name IS NOT NULL")
.WhereColumnLike(teamTable, "Name", "F.C.", LikeMode.WildcardLeft)
.WhereColumnLike(teamTable, "Description", "\"premier league\" -winners", LikeMode.FreeText)
.WhereColumnContains(teamTable, "Name", "Man -Cit", ContainsMode.FreeText)
.WhereColumnContains(teamTable, "Description", "Winner Runner-up", ContainsMode.AnyWordWildcardRight)
.WhereColumnColumn(teamTable, "ID", Compare.NotEqual, countryTable, "ID")
.WhereColumnValue(playerTable, "FirstName", Compare.NotEqual, "Peter")
.WhereColumnValue(playerTable, "StartDate", Compare.NotEqual, DateTime.Now)
.WhereColumnValue(playerTable, "Score", Compare.LessThanOrEqual, 10, isNullValue: 0)
.WhereColumnValue(playerTable, "Score", Compare.GreaterThan, "1", DbType.Int32, isNullValue: "0")
.WhereCollection(Combine.Or, new WhereFilterCollection()
.WhereColumnColumn(teamTable, "Value1", Compare.GreaterThan, countryTable, "Value2")
.WhereColumnValue(teamTable, "Value3", Compare.LessThan, 1)
)
// GROUP BY
.GroupBy(countryTable, "ID")
// Having
.Having(Combine.And)
.Having("SUM(t.Points) > 4")
.HavingColumnValue(Aggregate.Sum, playerTable, "Goals", Compare.GreaterThan, 10)
.HavingCollection(Combine.Or, new HavingFilterCollection()
.HavingColumnValue(Aggregate.Min, playerTable, "RedCards", Compare.GreaterThan, 1)
.HavingColumnValue(Aggregate.Max, playerTable, "RedCards", Compare.LessThan, 5)
)
.HavingColumnValue(Aggregate.Sum, playerTable, "Score", Compare.LessThan, 100, isNullValue: 0)
// ORDER BY
.OrderBy(countryTable, "ID", OrderDir.Asc)
.OrderByDesc(playerTable, "ID");
var cte = new CommonTableExpression()
{
Alias = "cte1",
Query = cteQuery
};
var query = new Query();
query.CommonTableExpressions.Add(cte);
query.SelectQuery = new SelectQuery()
.SelectRowNumber("_ROW")
.SelectTotal("_TOTAL_ROWS", 0)
.SelectAll()
.From(cte.Alias, cte.Alias)
//.OrderBy(cte.Alias, "_ROW")
.Offset(10)
.Fetch(5);
var compiled = query.Compile();
Console.WriteLine(compiled.ParameterSql);
Console.WriteLine(compiled.Sql);
}
/// <summary>
/// KzLib.SqlServer.TransactSql.ScriptDom.AddCommonTableExpressionVisitor をインスタンス化します。
/// </summary>
/// <param name="commonTableExpression">追加するCTE式</param>
public AddCommonTableExpressionVisitor(CommonTableExpression commonTableExpression)
{
CommonTableExpression = commonTableExpression;
}
