示例#1
0
        /// <summary>
        ///  Parses a PREFERENE SQL Statement in an ANSI SQL Statement
        /// </summary>
        /// <param name="strInput"></param>
        /// <param name="prefSQLParam"></param>
        /// <returns>Return the ANSI SQL Statement</returns>
        /// <exception cref="Exception">This is exception is thrown because the String is not a valid PrefSQL Query</exception>

        internal string ParsePreferenceSQL(string strInput, PrefSQLModel prefSQLParam)
        {
            SQLSort      sqlSort      = new SQLSort();
            SQLCriterion sqlCriterion = new SQLCriterion();
            string       strSQLReturn = ""; //The SQL-Query that is built on the basis of the prefSQL
            PrefSQLModel prefSQL      = prefSQLParam ?? GetPrefSqlModelFromPreferenceSql(strInput);

            try
            {
                //Check if parse was successful and query contains PrefSQL syntax
                if (prefSQL != null) // && strInput.IndexOf(SkylineOf) > 0
                {
                    if (prefSQL.Skyline.Count > 0)
                    {
                        //Mark as incomparable if needed (to choose the correct algorithm)
                        //withIncomparable = prefSQL.WithIncomparable;

                        //Add all Syntax before the Skyline-Clause
                        strSQLReturn = strInput.Substring(0, strInput.IndexOf(SkylineOf, StringComparison.OrdinalIgnoreCase) - 1).TrimStart(' ');

                        //Add Skyline Attributes to select list. This option is i.e. useful to create a dominance graph.
                        //With help of the skyline values it is easier to create this graph
                        if (ShowInternalAttributes)
                        {
                            //Add the attributes to the existing SELECT clause
                            string strSQLSelectClause  = GetSelectClauseForSkylineAttributes(prefSQL);
                            string strSQLBeforeFrom    = strSQLReturn.Substring(0, strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);
                            string strSQLAfterFromShow = strSQLReturn.Substring(strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);
                            strSQLReturn = strSQLBeforeFrom + strSQLSelectClause + " " + strSQLAfterFromShow;
                        }

                        //Add ORDER BY Clause
                        string strOrderBy = "";
                        if (strInput.IndexOf(" ORDER BY ", StringComparison.OrdinalIgnoreCase) > 0)
                        {
                            if (prefSQL.Ordering == Ordering.AsIs)
                            {
                                string strTmpInput = strInput;

                                //Replace category clauses
                                //Start with latest order by (otherwise substring start, stop position are changed)
                                for (int iIndex = prefSQL.OrderBy.Count - 1; iIndex >= 0; iIndex--)
                                {
                                    OrderByModel model = prefSQL.OrderBy[iIndex];
                                    strTmpInput = strTmpInput.Substring(0, model.Start) + model.Text + strTmpInput.Substring(model.Stop);
                                }

                                strOrderBy = strTmpInput.Substring(strInput.IndexOf(" ORDER BY ", StringComparison.OrdinalIgnoreCase) + 1);
                            }
                            else
                            {
                                strOrderBy = sqlSort.GetSortClause(prefSQL, prefSQL.Ordering); // sqlSort.getSortClause(prefSQL, _OrderType);
                            }
                            //Add space charachter in front of ORDER BY if not already present
                            if (!strOrderBy.Substring(0, 1).Equals(" "))
                            {
                                strOrderBy = " " + strOrderBy;
                            }
                        }


                        ////////////////////////////////////////////
                        //attributes used for native sql algorithm
                        string strWhere = sqlCriterion.GetCriterionClause(prefSQL, strSQLReturn);

                        ////////////////////////////////////////////
                        //attributes used for other algorithms
                        string strOperators;
                        string strAttributesSkyline = BuildPreferencesBNL(prefSQL, out strOperators);
                        //Without SELECT

                        //Remove TOP keyword, expect for the native SQL algorithm
                        if (prefSQL.NumberOfRecords != 0 && SkylineType.IsNative() == false)
                        {
                            //Remove Top Keyword in inner clause
                            int    iPosTop        = strSQLReturn.IndexOf(" TOP ", StringComparison.OrdinalIgnoreCase) + 1;
                            int    iPosTopEnd     = strSQLReturn.Substring(iPosTop + 3).TrimStart().IndexOf(" ", StringComparison.Ordinal);
                            string strSQLAfterTop = strSQLReturn.Substring(iPosTop + 3).TrimStart();
                            strSQLReturn = strSQLReturn.Substring(0, iPosTop) + strSQLAfterTop.Substring(iPosTopEnd + 1);
                        }


                        string strAttributesOutput = ", " + strSQLReturn.Substring(7, strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) - 6);
                        string strSQLAfterFrom     = strSQLReturn.Substring(strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);

                        string strFirstSQL = "SELECT " + strAttributesSkyline + " " + strAttributesOutput + strSQLAfterFrom;
                        if (SkylineType.IsNative())
                        {
                            strFirstSQL = strSQLReturn;
                        }

                        string strOrderByAttributes = sqlSort.GetSortClause(prefSQL, WindowSort);


                        ////////////////////////////////////////////
                        //attributes used for hexagon
                        string[] additionalParameters = new string[6];

                        string strOperatorsHexagon;
                        string strAttributesSkylineHexagon = BuildSelectHexagon(prefSQL, out strOperatorsHexagon);
                        //Without SELECT


                        //Quote quotes because it is a parameter of the stored procedure
                        string strFirstSQLHexagon = "SELECT " + strAttributesSkylineHexagon + " " + strAttributesOutput + strSQLAfterFrom;
                        strFirstSQLHexagon = strFirstSQLHexagon.Replace("'", "''");

                        //Quote quotes because it is a parameter of the stored procedure
                        //string strSelectDistinctIncomparable = "";
                        string weightHexagonIncomparable     = "";
                        string strSelectDistinctIncomparable = BuildIncomparableHexagon(prefSQL, ref weightHexagonIncomparable);
                        strSelectDistinctIncomparable = strSelectDistinctIncomparable.Replace("'", "''");

                        additionalParameters[0] = strFirstSQLHexagon;
                        additionalParameters[1] = strOperatorsHexagon;
                        additionalParameters[2] = strSelectDistinctIncomparable;
                        additionalParameters[3] = weightHexagonIncomparable;

                        _skylineType.SortType                   = (int)prefSQL.Ordering;
                        _skylineType.RecordAmountLimit          = prefSQL.NumberOfRecords;
                        _skylineType.MultipleSkylineUpToLevel   = _skylineUpToLevel;
                        _skylineType.AdditionParameters         = additionalParameters;
                        _skylineType.HasIncomparablePreferences = prefSQL.WithIncomparable;

                        //Now create the query depending on the Skyline algorithm
                        if (!prefSQL.HasSkylineSample)
                        {
                            strSQLReturn = _skylineType.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
                                                                                  strOperators, strOrderByAttributes);
                        }
                        else
                        {
                            var skylineSample = new SkylineSampling
                            {
                                SubsetCount      = prefSQL.SkylineSampleCount,
                                SubsetDimension  = prefSQL.SkylineSampleDimension,
                                SelectedStrategy = _skylineType
                            };
                            strSQLReturn = skylineSample.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
                                                                                   strOperators, strOrderByAttributes);
                        }
                    }
                    if (prefSQL.Ranking.Count > 0)
                    {
                        if (prefSQL.ContainsOpenPreference)
                        {
                            throw new Exception("WeightedSum cannot handle implicit INCOMPARABLE values. Please add the explicit OTHERS EQUAL to the preference");
                        }

                        string strSelectExtremas     = "";
                        string strRankingWeights     = "";
                        string strRankingExpressions = "";
                        string strColumnNames        = "";

                        // Set the decimal seperator, because prefSQL double values are always with decimal separator "."
                        NumberFormatInfo format = new NumberFormatInfo();
                        format.NumberDecimalSeparator = ".";

                        foreach (RankingModel rankingModel in prefSQL.Ranking)
                        {
                            strSelectExtremas     += rankingModel.SelectExtrema + ";";
                            strRankingWeights     += rankingModel.Weight.ToString(format) + ";";
                            strRankingExpressions += rankingModel.Expression + ";";
                            strColumnNames        += rankingModel.FullColumnName.Replace(".", "_") + ";";
                        }
                        strSelectExtremas     = strSelectExtremas.TrimEnd(';');
                        strRankingWeights     = strRankingWeights.TrimEnd(';');
                        strRankingExpressions = strRankingExpressions.TrimEnd(';');
                        strColumnNames        = strColumnNames.TrimEnd(';');

                        SPRanking spRanking = new SPRanking();
                        spRanking.ShowInternalAttributes = ShowInternalAttributes;
                        //Quoting is done in GetStoredProc Command
                        //string strExecSQL = strInput.Replace("'", "''");
                        strSQLReturn = spRanking.GetStoredProcedureCommand(strInput, strSelectExtremas, strRankingWeights, strRankingExpressions, strColumnNames);
                    }
                }
                else
                {
                    //Query does not contain a preference --> return original query
                    strSQLReturn = strInput;
                }
            }

            catch (Exception e)
            {
                //Parse syntaxerror
                throw new Exception(e.Message);
            }

            return(strSQLReturn);
        }
示例#2
0
        /// <summary>
        ///  Parses a PREFERENE SQL Statement in an ANSI SQL Statement
        /// </summary>
        /// <param name="strInput"></param>
        /// <param name="prefSQLParam"></param>
        /// <returns>Return the ANSI SQL Statement</returns>
        /// <exception cref="Exception">This is exception is thrown because the String is not a valid PrefSQL Query</exception>
        internal string ParsePreferenceSQL(string strInput, PrefSQLModel prefSQLParam)
        {
            SQLSort sqlSort = new SQLSort();
            SQLCriterion sqlCriterion = new SQLCriterion();
            string strSQLReturn = ""; //The SQL-Query that is built on the basis of the prefSQL
            PrefSQLModel prefSQL = prefSQLParam ?? GetPrefSqlModelFromPreferenceSql(strInput);

            try
            {
                //Check if parse was successful and query contains PrefSQL syntax
                if (prefSQL != null) // && strInput.IndexOf(SkylineOf) > 0
                {
                    if (prefSQL.Skyline.Count > 0)
                    {
                        //Mark as incomparable if needed (to choose the correct algorithm)
                        //withIncomparable = prefSQL.WithIncomparable;

                        //Add all Syntax before the Skyline-Clause
                        strSQLReturn = strInput.Substring(0, strInput.IndexOf(SkylineOf, StringComparison.Ordinal) - 1).TrimStart(' ');

                        //Add Skyline Attributes to select list. This option is i.e. useful to create a dominance graph.
                        //With help of the skyline values it is easier to create this graph
                        if (ShowInternalAttributes)
                        {
                            //Add the attributes to the existing SELECT clause
                            string strSQLSelectClause = GetSelectClauseForSkylineAttributes(prefSQL);
                            string strSQLBeforeFrom = strSQLReturn.Substring(0, strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));
                            string strSQLAfterFromShow = strSQLReturn.Substring(strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));
                            strSQLReturn = strSQLBeforeFrom + strSQLSelectClause + " " + strSQLAfterFromShow;

                        }

                        //Add ORDER BY Clause
                        string strOrderBy = "";
                        if (strInput.IndexOf("ORDER BY", StringComparison.Ordinal) > 0)
                        {
                            if (prefSQL.Ordering == Ordering.AsIs)
                            {
                                string strTmpInput = strInput;

                                //Replace category clauses
                                //Start with latest order by (otherwise substring start, stop position are changed)
                                for (int iIndex = prefSQL.OrderBy.Count - 1; iIndex >= 0; iIndex--)
                                {
                                    OrderByModel model = prefSQL.OrderBy[iIndex];
                                    strTmpInput = strTmpInput.Substring(0, model.Start) + model.Text + strTmpInput.Substring(model.Stop);
                                }

                                strOrderBy = strTmpInput.Substring(strInput.IndexOf("ORDER BY", StringComparison.Ordinal));
                            }
                            else
                            {
                                strOrderBy = sqlSort.GetSortClause(prefSQL, prefSQL.Ordering); // sqlSort.getSortClause(prefSQL, _OrderType);
                            }
                            //Add space charachter in front of ORDER BY if not already present
                            if (!strOrderBy.Substring(0, 1).Equals(" "))
                            {
                                strOrderBy = " " + strOrderBy;
                            }

                        }

                        ////////////////////////////////////////////
                        //attributes used for native sql algorithm
                        string strWhere = sqlCriterion.GetCriterionClause(prefSQL, strSQLReturn);

                        ////////////////////////////////////////////
                        //attributes used for other algorithms
                        string strOperators;
                        string strAttributesSkyline = BuildPreferencesBNL(prefSQL, out strOperators);
                        //Without SELECT

                        //Remove TOP keyword, expect for the native SQL algorithm
                        if (prefSQL.NumberOfRecords != 0 && SkylineType.IsNative() == false)
                        {
                            //Remove Top Keyword in inner clause
                            int iPosTop = strSQLReturn.IndexOf("TOP", StringComparison.Ordinal);
                            int iPosTopEnd = strSQLReturn.Substring(iPosTop + 3).TrimStart().IndexOf(" ", StringComparison.Ordinal);
                            string strSQLAfterTop = strSQLReturn.Substring(iPosTop + 3).TrimStart();
                            strSQLReturn = strSQLReturn.Substring(0, iPosTop) + strSQLAfterTop.Substring(iPosTopEnd + 1);
                        }

                        string strAttributesOutput = ", " + strSQLReturn.Substring(7, strSQLReturn.IndexOf("FROM", StringComparison.Ordinal) - 7);
                        string strSQLAfterFrom = strSQLReturn.Substring(strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));

                        string strFirstSQL = "SELECT " + strAttributesSkyline + " " + strAttributesOutput + strSQLAfterFrom;
                        if (SkylineType.IsNative())
                        {
                            strFirstSQL = strSQLReturn;
                        }

                        string strOrderByAttributes = sqlSort.GetSortClause(prefSQL, WindowSort);

                        ////////////////////////////////////////////
                        //attributes used for hexagon
                        string[] additionalParameters = new string[6];

                        string strOperatorsHexagon;
                        string strAttributesSkylineHexagon = BuildSelectHexagon(prefSQL, out strOperatorsHexagon);
                        //Without SELECT

                        //Quote quotes because it is a parameter of the stored procedure
                        string strFirstSQLHexagon = "SELECT " + strAttributesSkylineHexagon + " " + strAttributesOutput + strSQLAfterFrom;
                        strFirstSQLHexagon = strFirstSQLHexagon.Replace("'", "''");

                        //Quote quotes because it is a parameter of the stored procedure
                        //string strSelectDistinctIncomparable = "";
                        string weightHexagonIncomparable = "";
                        string strSelectDistinctIncomparable = BuildIncomparableHexagon(prefSQL, ref weightHexagonIncomparable);
                        strSelectDistinctIncomparable = strSelectDistinctIncomparable.Replace("'", "''");

                        additionalParameters[0] = strFirstSQLHexagon;
                        additionalParameters[1] = strOperatorsHexagon;
                        additionalParameters[2] = strSelectDistinctIncomparable;
                        additionalParameters[3] = weightHexagonIncomparable;

                        _skylineType.SortType = (int)prefSQL.Ordering;
                        _skylineType.RecordAmountLimit = prefSQL.NumberOfRecords;
                        _skylineType.MultipleSkylineUpToLevel = _skylineUpToLevel;
                        _skylineType.AdditionParameters = additionalParameters;
                        _skylineType.HasIncomparablePreferences = prefSQL.WithIncomparable;

                        //Now create the query depending on the Skyline algorithm
                        if (!prefSQL.HasSkylineSample)
                        {
                            strSQLReturn = _skylineType.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
                                strOperators, strOrderByAttributes);
                        }
                        else
                        {
                            var skylineSample = new SkylineSampling
                            {
                                SubsetCount = prefSQL.SkylineSampleCount,
                                SubsetDimension = prefSQL.SkylineSampleDimension,
                                SelectedStrategy = _skylineType
                            };
                            strSQLReturn = skylineSample.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
                                strOperators, strOrderByAttributes);
                        }
                    }
                    if(prefSQL.Ranking.Count > 0)
                    {
                        if(prefSQL.ContainsOpenPreference)
                        {
                            throw new Exception("WeightedSum cannot handle implicit INCOMPARABLE values. Please add the explicit OTHERS EQUAL to the preference");
                        }

                        string strSelectExtremas = "";
                        string strRankingWeights = "";
                        string strRankingExpressions = "";
                        string strColumnNames = "";

                        // Set the decimal seperator, because prefSQL double values are always with decimal separator "."
                        NumberFormatInfo format = new NumberFormatInfo();
                        format.NumberDecimalSeparator = ".";

                        foreach (RankingModel rankingModel in prefSQL.Ranking)
                        {
                            strSelectExtremas += rankingModel.SelectExtrema + ";";
                            strRankingWeights += rankingModel.Weight.ToString(format) + ";";
                            strRankingExpressions += rankingModel.Expression + ";";
                            strColumnNames += rankingModel.FullColumnName.Replace(".", "_") + ";";
                        }
                        strSelectExtremas = strSelectExtremas.TrimEnd(';');
                        strRankingWeights = strRankingWeights.TrimEnd(';');
                        strRankingExpressions = strRankingExpressions.TrimEnd(';');
                        strColumnNames = strColumnNames.TrimEnd(';');

                        SPRanking spRanking = new SPRanking();
                        spRanking.ShowInternalAttributes = ShowInternalAttributes;
                        //Quoting is done in GetStoredProc Command
                        //string strExecSQL = strInput.Replace("'", "''");
                        strSQLReturn = spRanking.GetStoredProcedureCommand(strInput, strSelectExtremas, strRankingWeights, strRankingExpressions, strColumnNames);
                    }
                }
                else
                {
                    //Query does not contain a preference --> return original query
                    strSQLReturn = strInput;
                }
            }

            catch (Exception e)
            {
                //Parse syntaxerror
                throw new Exception(e.Message);
            }

            return strSQLReturn;
        }