Exemplo n.º 1
0
        /// <summary>
        /// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
        /// </summary>
        public void RegenerateSQL()
        {
            var checkNotifier = new ThrowImmediatelyCheckNotifier();

            _sql = "";

            //reset the Parameter knowledge
            ParameterManager.ClearNonGlobals();

            #region Setup to output the query, where we figure out all the joins etc
            //reset everything

            SelectColumns.Sort();

            //work out all the filters
            Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);

            TableInfo primary;
            TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);

            //force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if theres WHERE sql that references them but no columns
            if (_forceJoinsToTheseTables != null)
            {
                foreach (var force in _forceJoinsToTheseTables)
                {
                    if (!TablesUsedInQuery.Contains(force))
                    {
                        TablesUsedInQuery.Add(force);
                    }
                }
            }

            this.PrimaryExtractionTable = primary;

            SqlQueryBuilderHelper.FindLookups(this);

            JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);

            //deal with case when there are no tables in the query or there are only lookup descriptions in the query
            if (TablesUsedInQuery.Count == 0)
            {
                throw new Exception("There are no TablesUsedInQuery in this dataset");
            }


            _syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);

            if (TopX != -1)
            {
                SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, TopX);
            }
            else
            {
                SqlQueryBuilderHelper.ClearTopX(this);
            }

            //declare parameters
            ParameterManager.AddParametersFor(Filters);

            #endregion

            /////////////////////////////////////////////Assemble Query///////////////////////////////

            #region Preamble (including variable declarations/initializations)
            //assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
            string toReturn = "";

            foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
            {
                //if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
                var needsToldTheSyntaxHelper = parameter as IInjectKnown <IQuerySyntaxHelper>;
                if (needsToldTheSyntaxHelper != null)
                {
                    needsToldTheSyntaxHelper.InjectKnown(_syntaxHelper);
                }

                if (CheckSyntax)
                {
                    parameter.Check(checkNotifier);
                }

                toReturn += GetParameterDeclarationSQL(parameter);
            }

            //add user custom Parameter lines
            toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);

            #endregion

            #region Select (including all IColumns)
            toReturn += Environment.NewLine;
            toReturn += "SELECT " + LimitationSQL + Environment.NewLine;

            toReturn  = AppendCustomLines(toReturn, QueryComponent.SELECT);
            toReturn += Environment.NewLine;

            toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);

            for (int i = 0; i < SelectColumns.Count; i++)
            {
                //output each of the ExtractionInformations that the user requested and record the line number for posterity
                string columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, _syntaxHelper);

                //there is another one coming
                if (i + 1 < SelectColumns.Count)
                {
                    columnAsSql += ",";
                }

                toReturn += columnAsSql + Environment.NewLine;
            }

            #endregion

            //work out basic JOINS Sql
            toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);

            //add user custom JOIN lines
            toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);

            #region Filters (WHERE)

            toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);

            toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);
            toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);

            _sql         = toReturn;
            SQLOutOfDate = false;

            #endregion
        }
Exemplo n.º 2
0
        /// <summary>
        /// Populates _sql (SQL property) and resolves all parameters, filters containers etc.  Basically Finalizes this query builder
        /// </summary>
        public void RegenerateSQL()
        {
            SelectColumns.Sort();

            //things we discover below, set them all to default values again
            _pivotDimension         = null;
            _axisAppliesToDimension = null;
            _axis = null;
            _isCohortIdentificationAggregate = false;

            ParameterManager.ClearNonGlobals();

            if (_queryLevelParameterProvider != null)
            {
                ParameterManager.AddParametersFor(_queryLevelParameterProvider, ParameterLevel.QueryLevel);
            }

            TableInfo primary;

            TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);

            var tables = _forceJoinsToTheseTables != null
                ? TablesUsedInQuery.Union(_forceJoinsToTheseTables).ToList()
                : TablesUsedInQuery;

            if (!tables.Any())
            {
                throw new QueryBuildingException("No tables could be identified for the query.  Try adding a column or a force join");
            }

            //get the database language syntax based on the tables used in the query
            _syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(tables);


            //tell the count column what language it is
            if (_countColumn != null)
            {
                _isCohortIdentificationAggregate = _aggregateConfigurationIfAny != null && _aggregateConfigurationIfAny.IsCohortIdentificationAggregate;

                //if it is not a cic aggregate then make sure it has an alias e.g. count(*) AS MyCount.  cic aggregates take extreme liberties with this field like passing in 'distinct chi' and '*' and other wacky stuff that is so not cool
                _countColumn.SetQuerySyntaxHelper(_syntaxHelper, !_isCohortIdentificationAggregate);
            }


            IAggregateHelper aggregateHelper = _syntaxHelper.AggregateHelper;

            if (_pivotID != -1)
            {
                try
                {
                    _pivotDimension = SelectColumns.Single(
                        qtc => qtc.IColumn is AggregateDimension
                        &&
                        ((AggregateDimension)qtc.IColumn).ID == _pivotID);
                }
                catch (Exception e)
                {
                    throw new QueryBuildingException("Problem occurred when trying to find PivotDimension ID " + _pivotID + " in SelectColumns list", e);
                }
            }

            foreach (AggregateDimension dimension in SelectColumns.Select(c => c.IColumn).Where(e => e is AggregateDimension))
            {
                var availableAxis = dimension.AggregateContinuousDateAxis;

                if (availableAxis != null)
                {
                    if (_axis != null)
                    {
                        throw new QueryBuildingException(
                                  "Multiple dimensions have an AggregateContinuousDateAxis within the same configuration (Dimensions " + _axisAppliesToDimension.GetRuntimeName() + " and " + dimension.GetRuntimeName() + ")");
                    }
                    else
                    {
                        _axis = availableAxis;
                        _axisAppliesToDimension = dimension;
                    }
                }
            }

            if (_pivotDimension != null)
            {
                if (_pivotDimension.IColumn == _axisAppliesToDimension)
                {
                    throw new QueryBuildingException("Column " + _pivotDimension.IColumn + " is both a PIVOT and has an AXIS configured on it, you cannot have both.");
                }
            }

            //work out all the filters
            Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);

            //tell the manager about them
            ParameterManager.AddParametersFor(Filters);

            if (AggregateTopX != null)
            {
                SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, AggregateTopX.TopX);
            }
            else
            {
                SqlQueryBuilderHelper.ClearTopX(this);
            }

            //if user wants to force join to some other tables that don't appear in the SELECT list, who are we to stop him!
            if (_forceJoinsToTheseTables != null)
            {
                foreach (TableInfo t in _forceJoinsToTheseTables)
                {
                    if (!TablesUsedInQuery.Contains(t))
                    {
                        TablesUsedInQuery.Add(t);
                        ParameterManager.AddParametersFor(t);
                    }

                    //if user has force joined to a primary extraction table
                    if (t.IsPrimaryExtractionTable)
                    {
                        if (primary == null) //we don't currently know the primary (i.e. none of the SELECT columns were from primary tables so use this table as primary)
                        {
                            primary = t;
                        }
                        else if (primary.ID == t.ID) //we know the primary already but it is the same table so thats fine
                        {
                            continue;
                        }
                        else
                        {
                            //this isn't fine
                            throw new QueryBuildingException("You chose to FORCE a join to table " + t + " which is marked IsPrimaryExtractionTable but you have also selected a column called " + primary + " which is also an IsPrimaryExtractionTable (cannot have 2 different primary extraction tables)");
                        }
                    }
                }
            }

            this.PrimaryExtractionTable = primary;

            SqlQueryBuilderHelper.FindLookups(this);

            JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);

            var queryLines = new List <CustomLine>();

            _sql = "";

            ValidateDimensions();

            //assuming we were not told to ignore the writing out of parameters!
            if (!DoNotWriteOutParameters)
            {
                foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
                {
                    queryLines.Add(new CustomLine(QueryBuilder.GetParameterDeclarationSQL(parameter), QueryComponent.VariableDeclaration));
                }
            }

            CompileCustomLinesInStageAndAddToList(QueryComponent.VariableDeclaration, queryLines);

            //put the name in as SQL comments followed by the SQL e.g. the name of an AggregateConfiguration or whatever
            GetSelectSQL(queryLines);

            queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetFROMSQL(this), QueryComponent.FROM));
            CompileCustomLinesInStageAndAddToList(QueryComponent.JoinInfoJoin, queryLines);

            queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetWHERESQL(this), QueryComponent.WHERE));

            CompileCustomLinesInStageAndAddToList(QueryComponent.WHERE, queryLines);

            GetGroupBySQL(queryLines, aggregateHelper);

            queryLines = queryLines.Where(l => !string.IsNullOrWhiteSpace(l.Text)).ToList();

            _sql = aggregateHelper.BuildAggregate(queryLines, _axis);
        }