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