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