internal PrefSQLModel GetPrefSqlModelFromPreferenceSql(string preferenceSql) { PrefSQLParser parser = new PrefSQLParser(new CommonTokenStream(new PrefSQLLexer(new AntlrInputStream(preferenceSql)))); // An error listener helps to return detailed parser syntax errors ErrorListener listener = new ErrorListener(); parser.AddErrorListener(listener); IParseTree tree = parser.parse(); // PrefSQLModel is built during the visit of the parse tree SQLVisitor visitor = new SQLVisitor { IsNative = _skylineType.IsNative() }; visitor.Visit(tree); PrefSQLModel prefSql = visitor.Model; if (prefSql != null) { prefSql.OriginalPreferenceSql = preferenceSql; } return(prefSql); }
/// <summary>TODO</summary> /// <param name="model">model of parsed Preference SQL Statement</param> /// <param>Preference SQL Statement WITHOUT PREFERENCES</param> /// <param name="strOperators">Returns the operators</param> /// <returns>TODO</returns> private string BuildPreferencesBNL(PrefSQLModel model, out string strOperators) { string strSQL = ""; strOperators = ""; //Build Skyline only if more than one attribute if (model.Skyline.Count > 0) { //Build the where clause with each column in the skyline for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { strSQL += ", " + model.Skyline[iChild].RankExpression + " AS SkylineAttribute" + iChild; strOperators += "LOW;"; //Incomparable field --> Add string field if (model.Skyline[iChild].Comparable == false) { strSQL += ", " + model.Skyline[iChild].IncomparableAttribute; strOperators += "INCOMPARABLE" + ";"; } } } strOperators = strOperators.TrimEnd(';'); strSQL = strSQL.TrimStart(','); return(strSQL); }
private IEnumerable <CLRSafeHashSet <int> > UseSubsets(PrefSQLModel prefSqlModelSkylineSample) { var preferencesInSubsetExpected = new List <CLRSafeHashSet <int> >(); DataRow[] subsetsExpected = TestContext.DataRow.GetChildRows("TestDataRow_useSubsets")[0].GetChildRows("useSubsets_subset"); foreach (DataRow subsetExpected in subsetsExpected) { DataRow[] subsetExpectedDimensions = subsetExpected.GetChildRows("subset_dimension"); var preferencesInSingleSubsetExpected = new CLRSafeHashSet <int>(); foreach (DataRow singleSubsetExpectedDimension in subsetExpectedDimensions) { for (var i = 0; i < prefSqlModelSkylineSample.Skyline.Count; i++) { AttributeModel attributeModel = prefSqlModelSkylineSample.Skyline[i]; if (attributeModel.FullColumnName == singleSubsetExpectedDimension[0].ToString()) { preferencesInSingleSubsetExpected.Add(i); break; } } } preferencesInSubsetExpected.Add(preferencesInSingleSubsetExpected); } return(preferencesInSubsetExpected); }
/// <summary> /// Parse a prefSQL Query and return the result as a DataTable. /// </summary> /// <param name="connectionString"></param> /// <param name="driverString"></param> /// <param name="strPrefSql"></param> /// <returns>Returns a DataTable with the requested values</returns> public DataTable ParseAndExecutePrefSQL(string connectionString, string driverString, String strPrefSql) { DataTable dt = new DataTable(); try { //return ParseAndExecutePrefSQL(connectionString, driverString, GetPrefSqlModelFromPreferenceSql(strPrefSql)); //Do not build 2 times the model! Helper.ConnectionString = connectionString; Helper.DriverString = driverString; Helper.Cardinality = Cardinality; Helper.WindowHandling = WindowHandling; PrefSQLModel model = GetPrefSqlModelFromPreferenceSql(strPrefSql); string sqlCommand = ParsePreferenceSQL(strPrefSql, model); dt = Helper.GetResults(sqlCommand, SkylineType, model, ShowInternalAttributes); TimeInMilliseconds = Helper.TimeInMilliseconds; NumberOfComparisons = Helper.NumberOfComparisons; NumberOfMoves = Helper.NumberOfMoves; } catch (Exception e) { throw e; } return(dt); }
/// <summary>TODO</summary> /// <param name="model">model of parsed Preference SQL Statement</param> /// <param name="weight"></param> /// <returns>TODO</returns> private string BuildIncomparableHexagon(PrefSQLModel model, ref string weight) { string strDistinctSelect = ""; //Add a RankColumn for each PRIORITIZE preference for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //Add additional columns if attribute is incomparable if (model.Skyline[iChild].Comparable == false && model.Skyline[iChild].AmountOfIncomparables > 0) { //strMaxSQL += "+1"; //99 means OTHER INCOMPARABLE --> not clear at the moment how many distinct values exists if (model.Skyline[iChild].AmountOfIncomparables == 99) { strDistinctSelect += model.Skyline[iChild].IncomparableAttribute + ";"; weight += model.Skyline[iChild].HexagonWeightIncomparable.ToString() + ";"; } } } strDistinctSelect = strDistinctSelect.TrimEnd(';'); weight = weight.TrimEnd(';'); return(strDistinctSelect); }
public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkyline() { string skylineSampleSql = TestContext.DataRow["skylineSampleSQL"].ToString(); string entireSkylineSql = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); Debug.WriteLine(skylineSampleSql); string baseQuery; string operators; int numberOfRecords; string[] parameter; var common = new SQLCommon { SkylineType = new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators, out numberOfRecords); IEnumerable <CLRSafeHashSet <int> > useSubsets = UseSubsets(prefSqlModelSkylineSample); var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount, SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSql); DataTable sampleSkyline = skylineSample.GetSkylineTable(baseQuery, operators); HashSet <int> entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline); HashSet <int> sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline); sampleSkylineObjectsIds.ExceptWith(entireSkylineObjectsIds); Debug.WriteLine("wrong objects:"); foreach (int i in sampleSkylineObjectsIds) { Debug.WriteLine(i); } Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds), "Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline)."); }
private static void ExecuteSampleSkylines(IReadOnlyCollection <IEnumerable <CLRSafeHashSet <int> > > producedSubsets, PrefSQLModel prefSqlModel, SQLCommon common) { var objectsCount = 0; var timeSpent = 0L; string strQuery; string operators; int numberOfRecords; string[] parameter; string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModel); Debug.Write(ansiSql); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out strQuery, out operators, out numberOfRecords); var sw = new Stopwatch(); foreach (IEnumerable <CLRSafeHashSet <int> > subset in producedSubsets) { var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(subset); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModel.SkylineSampleCount, SubsetDimension = prefSqlModel.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; sw.Restart(); DataTable dataTable = skylineSample.GetSkylineTable(strQuery, operators); sw.Stop(); objectsCount += dataTable.Rows.Count; timeSpent += skylineSample.TimeMilliseconds; foreach (CLRSafeHashSet <int> attribute in subset) { Console.Write("["); foreach (int attribute1 in attribute) { Console.Write(attribute1 + ","); } Console.Write("],"); } Console.WriteLine(); Console.WriteLine("alg time : " + skylineSample.TimeMilliseconds); Console.WriteLine("full time : " + sw.ElapsedMilliseconds); Console.WriteLine("objects : " + dataTable.Rows.Count); } Console.WriteLine("time average: " + (double)timeSpent / producedSubsets.Count); Console.WriteLine("objects average: " + (double)objectsCount / producedSubsets.Count); }
public void TestSkylineAmountOfTupelsDataTable() { string skylineSampleSql = TestContext.DataRow["skylineSQL"].ToString(); SQLCommon common = new SQLCommon(); common.SkylineType = new SkylineSQL(); PrefSQLModel model = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); DataTable dtNative = common.ExecuteFromPrefSqlModel(Helper.ConnectionString, Helper.ProviderName, model); common.SkylineType = new SkylineBNL(); DataTable dtBNL = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); common.SkylineType = new SkylineBNLSort(); DataTable dtBNLSort = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); DataTable dtHexagon = new DataTable(); if (model.ContainsOpenPreference == false) { common.SkylineType = new SkylineHexagon(); dtHexagon = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); } DataTable dtDQ = new DataTable(); //D&Q does not work with incomparable tuples if (model.WithIncomparable == false) { common.SkylineType = new SkylineDQ(); dtDQ = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); } int currentDataRowIndex = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //Check tuples (every algorithm should deliver the same amount of tuples) Assert.AreEqual(dtNative.Rows.Count, dtBNL.Rows.Count, 0, "BNL Amount of tupels in query " + currentDataRowIndex + " do not match"); Assert.AreEqual(dtNative.Rows.Count, dtBNLSort.Rows.Count, 0, "BNLSort Amount of tupels in query " + currentDataRowIndex + " do not match"); //Hexagon cannot handle Categorical preference that have no explicit OTHERS if (model.ContainsOpenPreference == false) { Assert.AreEqual(dtNative.Rows.Count, dtHexagon.Rows.Count, 0, "Hexagon Amount of tupels in query " + currentDataRowIndex + " do not match"); } //D&Q does not work with incomparable tuples if (model.WithIncomparable == false) { Assert.AreEqual(dtNative.Rows.Count, dtDQ.Rows.Count, 0, "D&Q Amount of tupels in query " + currentDataRowIndex + " do not match"); } }
/// <summary> /// Sorts the results according to the attributes values. the first attribute has the highest priority. /// For example a tuple has the attributes price and color. The result will be sorted after price and color, whereas /// the price has the higher priority /// </summary> /// <param name="model"></param> /// <returns></returns> private string GetSortEntropyValue(PrefSQLModel model) { string strSQL = ""; for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //First record doesn't need a comma to separate if (iChild > 0) { strSQL += " + "; } strSQL += model.Skyline[iChild].Expression; } return(strSQL); }
/// <summary> /// Sorts the results according to the attributes values. the first attribute has the highest priority. /// For example a tuple has the attributes price and color. The result will be sorted after price and color, whereas /// the price has the higher priority /// </summary> /// <param name="model"></param> /// <returns></returns> private string GetSortAttributePositionClause(PrefSQLModel model) { string strSQL = ""; for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //First record doesn't need a comma to separate if (iChild > 0) { strSQL += ", "; } //strSQL += model.Skyline[iChild].OrderBy.ToString(); strSQL += model.Skyline[iChild].Expression; } return(strSQL); }
public void TestSamplingNumberOfObjectsWithinSampleSkyline() { string skylineSampleSQL = TestContext.DataRow["skylineSampleSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); int expectedNumberOfSkylineSampleObjects = int.Parse(TestContext.DataRow["expectedNumberOfSkylineSampleObjects"].ToString()); Debug.WriteLine(testComment); Debug.WriteLine(skylineSampleSQL); string baseQuery; string operators; int numberOfRecords; string[] parameter; var common = new SQLCommon { SkylineType = new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSQL); string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators, out numberOfRecords); IEnumerable <CLRSafeHashSet <int> > useSubsets = UseSubsets(prefSqlModelSkylineSample); var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount, SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; DataTable skyline = skylineSample.GetSkylineTable(baseQuery, operators); Assert.AreEqual(expectedNumberOfSkylineSampleObjects, skyline.Rows.Count, "Unexpected number of Sample Skyline objects."); }
/// <summary> /// Sorts the results according to their summed ranking. /// For example a tuple has the best, 5th and 7th rank in three attributes. This leads to a ranking of 13. /// </summary> /// <param name="model"></param> /// <returns></returns> private string GetSortRankingSumClause(PrefSQLModel model) { string strSQL = ""; for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //First attribute doesn't need a plus if (iChild > 0) { strSQL += " + "; } string strRankingExpression = "DENSE_RANK() OVER (ORDER BY " + model.Skyline[iChild].Expression + ")"; strSQL += strRankingExpression; } return(strSQL); }
/// <summary> /// Sorts the results according to their best ranking of all attributes /// For example a tuple has the best, 5th and 7th rank in three attributes. This leads to a ranking of 1. /// </summary> /// <param name="model"></param> /// <returns></returns> private string GetSortRankingBestOfClause(PrefSQLModel model) { string strSQL = "CASE "; for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { string strRankingExpression = "DENSE_RANK() OVER (ORDER BY " + model.Skyline[iChild].Expression + ")"; strRankingExpression = strRankingExpression.Replace("DENSE_RANK()", "ROW_NUMBER()"); if (model.Skyline.Count == 1) { //special case if totally only one preference strSQL += "WHEN 1=1 THEN " + strRankingExpression + " "; } if (iChild == model.Skyline.Count - 1) { //Last record only needs ELSE strSQL += " ELSE " + "" + strRankingExpression; } else { strSQL += "WHEN "; string strRanking = strRankingExpression; for (int iSubChild = iChild + 1; iSubChild < model.Skyline.Count; iSubChild++) { string strSubRanking = "DENSE_RANK() OVER (ORDER BY " + model.Skyline[iSubChild].Expression + ")"; strSubRanking = strSubRanking.Replace("DENSE_RANK()", "ROW_NUMBER()"); strSQL += strRanking + " <=" + strSubRanking; if (iSubChild < model.Skyline.Count - 1) { strSQL += " AND "; } } strSQL += " THEN " + strRankingExpression + " "; } } strSQL += " END"; return(strSQL); }
public void TestSamplingNumberOfObjectsWithinSampleSkylineWithCountOneEqualsEntireSkyline() { string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); var common = new SQLCommon { SkylineType = new SkylineBNL() }; DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSQL); PrefSQLModel entirePrefSqlModel = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL); DataTable sampleSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSQL + " SAMPLE BY RANDOM_SUBSETS COUNT 1 DIMENSION " + entirePrefSqlModel.Skyline.Count); Assert.AreEqual(entireSkyline.Rows.Count, sampleSkyline.Rows.Count, "Unexpected number of Skyline objects."); }
/// <summary> /// Create the ORDER BY-Clause from the preference model /// </summary> /// <param name="model"></param> /// <param name="type"></param> /// <returns></returns> public string GetSortClause(PrefSQLModel model, SQLCommon.Ordering type) { string strSQL = ""; switch (type) { case SQLCommon.Ordering.AttributePosition: strSQL = GetSortAttributePositionClause(model); break; case SQLCommon.Ordering.RankingSummarize: strSQL = GetSortRankingSumClause(model); break; case SQLCommon.Ordering.RankingBestOf: strSQL = GetSortRankingBestOfClause(model); break; case SQLCommon.Ordering.AsIs: strSQL = ""; //Return no ORDER BY Clause break; case SQLCommon.Ordering.Random: strSQL = GetSortRandomClause(); break; case SQLCommon.Ordering.EntropyFunction: { strSQL = GetSortEntropyValue(model); break; } } if (strSQL.Length > 0) { strSQL = " ORDER BY " + strSQL; } return(strSQL); }
/// <summary> /// Create the WHERE-Clause according to the preference model /// </summary> /// <param name="model"></param> /// <param name="strPreSQL"></param> /// <returns></returns> public string GetCriterionClause(PrefSQLModel model, string strPreSQL) { //Build Skyline only if more than one attribute string strSQL = GetCriterionSkylineClause(model, strPreSQL); //Check if a WHERE-Clause was built if (strSQL.Length > 0) { //Only add WHERE if there is not already a where clause bool isWherePresent = strPreSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) > 0; if (isWherePresent) { strSQL = " AND " + strSQL; } else { strSQL = " WHERE " + strSQL; } } return(strSQL); }
/// <summary>This method is used for Pareto Dominance Grahps. It adds the Skyline Attributes to the SELECT List.</summary> /// <remarks> /// For the reason that comparing the values is easier, smaller values are always better than higher. /// Therefore HIGH preferences are multiplied with -1 /// Every preference gets 2 output values. the 1st declares the level, the second the exact value if an additional comparison /// is needed, because of incomparability /// </remarks> /// <param name="model">model of parsed Preference SQL Statement</param> /// <returns>Return the extended SQL Statement</returns> private string GetSelectClauseForSkylineAttributes(PrefSQLModel model) { string strSQL = ""; //Build Skyline only if more than one attribute if (model.Skyline.Count > 0) { //Build the where clause with each column in the skyline for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { string strFullColumnName = model.Skyline[iChild].FullColumnName.Replace(".", "_"); strSQL += ", " + model.Skyline[iChild].RankExpression + " AS SkylineAttribute" + strFullColumnName; //Incomparable field --> Add string field if (model.Skyline[iChild].Comparable == false) { strSQL += ", " + model.Skyline[iChild].IncomparableAttribute + " AS SkylineAttributeIncomparable" + strFullColumnName; } } } return(strSQL); }
/// <summary>TODO</summary> /// <param name="model">model of parsed Preference SQL Statement</param> /// <param name="strOperators">Returns the operators</param> /// <returns>TODO</returns> private string BuildSelectHexagon(PrefSQLModel model, out string strOperators) { strOperators = ""; string strSQL = ""; //Add a RankColumn for each PRIORITIZE preference for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //Replace ROW_NUMBER with Rank, for the reason that multiple tuples can have the same value (i.e. mileage=0) string strRank = model.Skyline[iChild].RankExpression; strSQL += ", " + strRank; strOperators += "LOW" + ";"; if (model.Skyline[iChild].Comparable == false && model.Skyline[iChild].AmountOfIncomparables > 0) { strSQL += ", " + model.Skyline[iChild].HexagonIncomparable; if (model.Skyline[iChild].AmountOfIncomparables == 99) { strOperators += "CALCULATEINCOMPARABLE;"; } else { //CASE WHEN colors.name IN ('blau') THEN '001' WHEN colors.name IN ('silver') THEN '010' ELSE '100' END AS RankColorNew for (int iIncomparable = 0; iIncomparable < model.Skyline[iChild].AmountOfIncomparables; iIncomparable++) { strOperators += "INCOMPARABLE;"; } } } } //Add the ranked column before the FROM keyword strSQL = strSQL.TrimStart(','); strOperators = strOperators.TrimEnd(';'); return(strSQL); }
public void TestSkylineAmountOfTupelsMSSQLCLR() { string skylineSampleSql = TestContext.DataRow["skylineSQL"].ToString(); SQLCommon common = new SQLCommon(); common.SkylineType = new SkylineSQL(); PrefSQLModel model = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); string sqlNative = common.GetAnsiSqlFromPrefSqlModel(model); common.SkylineType = new SkylineBNL(); string sqlBNL = common.ParsePreferenceSQL(skylineSampleSql); common.SkylineType = new SkylineBNLSort(); string sqlBNLSort = common.ParsePreferenceSQL(skylineSampleSql); common.SkylineType = new SkylineHexagon(); string sqlHexagon = common.ParsePreferenceSQL(skylineSampleSql); //D&Q does not run with CLR common.SkylineType = new SkylineDQ(); string sqlDQ = common.ParsePreferenceSQL(skylineSampleSql); int amountOfTupelsBNL = 0; int amountOfTupelsBNLSort = 0; int amountOfTupelsSQL = 0; int amountOfTupelsHexagon = 0; int amountOfTupelsDQ = 0; SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString); cnnSQL.InfoMessage += cnnSQL_InfoMessage; try { cnnSQL.Open(); //Native DbCommand command = cnnSQL.CreateCommand(); command.CommandTimeout = 0; //infinite timeout command.CommandText = sqlNative; DbDataReader sqlReader = command.ExecuteReader(); if (sqlReader.HasRows) { while (sqlReader.Read()) { amountOfTupelsSQL++; } } sqlReader.Close(); //BNL command.CommandText = sqlBNL; sqlReader = command.ExecuteReader(); if (sqlReader.HasRows) { while (sqlReader.Read()) { amountOfTupelsBNL++; } } sqlReader.Close(); //BNLSort command.CommandText = sqlBNLSort; sqlReader = command.ExecuteReader(); if (sqlReader.HasRows) { while (sqlReader.Read()) { amountOfTupelsBNLSort++; } } sqlReader.Close(); //Hexagon command.CommandText = sqlHexagon; sqlReader = command.ExecuteReader(); if (sqlReader.HasRows) { while (sqlReader.Read()) { amountOfTupelsHexagon++; } } sqlReader.Close(); //D&Q (does not work with incomparable tuples) if (model.WithIncomparable == false) { command.CommandText = sqlDQ; sqlReader = command.ExecuteReader(); if (sqlReader.HasRows) { while (sqlReader.Read()) { amountOfTupelsDQ++; } } sqlReader.Close(); } cnnSQL.Close(); } catch (Exception ex) { Assert.Fail("Connection failed:" + ex.Message); } int currentDataRowIndex = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //Check tuples (every algorithm should deliver the same amount of tuples) Assert.AreEqual(amountOfTupelsSQL, amountOfTupelsBNLSort, 0, "BNLSort Amount of tupels in query " + currentDataRowIndex + " do not match"); Assert.AreEqual(amountOfTupelsSQL, amountOfTupelsBNL, 0, "BNL Amount of tupels in query " + currentDataRowIndex + " do not match"); //Hexagon cannot handle Categorical preference that have no explicit OTHERS if (model.ContainsOpenPreference == false) { Assert.AreEqual(amountOfTupelsSQL, amountOfTupelsHexagon, 0, "Hexagon Amount of tupels in query " + currentDataRowIndex + " do not match"); } //D&Q does not work with incomparable tuples if (model.WithIncomparable == false) { Assert.AreEqual(amountOfTupelsSQL, amountOfTupelsDQ, 0, "Amount of tupels in query " + currentDataRowIndex + " do not match"); } }
private IEnumerable<CLRSafeHashSet<int>> UseSubsets(PrefSQLModel prefSqlModelSkylineSample) { var preferencesInSubsetExpected = new List<CLRSafeHashSet<int>>(); DataRow[] subsetsExpected = TestContext.DataRow.GetChildRows("TestDataRow_useSubsets")[0].GetChildRows("useSubsets_subset"); foreach (DataRow subsetExpected in subsetsExpected) { DataRow[] subsetExpectedDimensions = subsetExpected.GetChildRows("subset_dimension"); var preferencesInSingleSubsetExpected = new CLRSafeHashSet<int>(); foreach (DataRow singleSubsetExpectedDimension in subsetExpectedDimensions) { for (var i = 0; i < prefSqlModelSkylineSample.Skyline.Count; i++) { AttributeModel attributeModel = prefSqlModelSkylineSample.Skyline[i]; if (attributeModel.FullColumnName == singleSubsetExpectedDimension[0].ToString()) { preferencesInSingleSubsetExpected.Add(i); break; } } } preferencesInSubsetExpected.Add(preferencesInSingleSubsetExpected); } return preferencesInSubsetExpected; }
/// <summary> /// Returns a datatable with the tuples from the SQL statement /// The sql will be resolved into pieces, in order to call the Skyline algorithms without MSSQL CLR /// </summary> /// <param name="strPrefSQL"></param> /// <param name="strategy"></param> /// <param name="model"></param> /// <returns></returns> public DataTable GetResults(String strPrefSQL, SkylineStrategy strategy, PrefSQLModel model, bool ShowInternalAttributes) { DataTable dt = new DataTable(); //Default Parameter string strQuery = ""; string strOperators = ""; int numberOfRecords = 0; string[] parameter = null; //Native SQL algorithm is already a valid SQL statement //Trim prefSQL because of queries starting wit empty characters " SELECT ...." if (strPrefSQL.Trim().StartsWith("SELECT", true, null)) { if (model == null || !model.HasSkylineSample) { //If query doesn't need skyline calculation (i.e. query without preference clause) --> set algorithm to nativeSQL strategy = new SkylineSQL(); } else { throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support } } else { //Determine parameter only with skyline of clause and not with weihtedsum clause DetermineParameters(strPrefSQL, out parameter, out strQuery, out strOperators, out numberOfRecords); } try { if (model != null && model.Ranking.Count > 0) { SPRanking ranking = new SPRanking(); ranking.Provider = DriverString; ranking.ConnectionString = ConnectionString; 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 model.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(';'); dt = ranking.GetRankingTable(strQuery, strSelectExtremas, strRankingWeights, strRankingExpressions, ShowInternalAttributes, strColumnNames); } else if (strategy.IsNative()) { if (model == null || !model.HasSkylineSample) { //Native SQL //Generic database provider //Create the provider factory from the namespace provider, you could create any other provider factory.. for Oracle, MySql, etc... DbProviderFactory factory = DbProviderFactories.GetFactory(DriverString); // use the factory object to create Data access objects. DbConnection connection = factory.CreateConnection(); // will return the connection object, in this case, SqlConnection ... if (connection != null) { connection.ConnectionString = ConnectionString; connection.Open(); DbCommand command = connection.CreateCommand(); command.CommandText = strPrefSQL; DbDataAdapter db = factory.CreateDataAdapter(); if (db != null) { db.SelectCommand = command; db.Fill(dt); } } } else { throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support } } else { if (strategy.SupportImplicitPreference() == false && model.ContainsOpenPreference) { throw new Exception(strategy.GetType() + " does not support implicit preferences!"); } if (strategy.SupportIncomparable() == false && model.WithIncomparable) { throw new Exception(strategy.GetType() + " does not support incomparale tuples"); } //Set the database provider strategy.Provider = DriverString; strategy.ConnectionString = ConnectionString; strategy.Cardinality = Cardinality; strategy.WindowHandling = WindowHandling; strategy.RecordAmountLimit = numberOfRecords; strategy.HasIncomparablePreferences = model.WithIncomparable; strategy.AdditionParameters = parameter; strategy.SortType = (int)model.Ordering; if (!model.HasSkylineSample) { dt = strategy.GetSkylineTable(strQuery, strOperators); TimeInMilliseconds = strategy.TimeMilliseconds; NumberOfComparisons = strategy.NumberOfComparisons; NumberOfMoves = strategy.NumberOfMoves; } else { var skylineSample = new SkylineSampling { SubsetCount = model.SkylineSampleCount, SubsetDimension = model.SkylineSampleDimension, SelectedStrategy = strategy }; dt = skylineSample.GetSkylineTable(strQuery, strOperators); TimeInMilliseconds = skylineSample.TimeMilliseconds; //NumberOfOperations = skylineSample.NumberOfOperations; } } } catch (Exception e) { Debug.WriteLine(e.Message); throw; } return(dt); }
internal DataTable ExecuteFromPrefSqlModel(string dbConnection, string dbProvider, PrefSQLModel prefSqlModel) { return(ParseAndExecutePrefSQL(dbConnection, dbProvider, prefSqlModel)); }
internal string GetAnsiSqlFromPrefSqlModel(PrefSQLModel prefSqlModel) { return(ParsePreferenceSQL(prefSqlModel.OriginalPreferenceSql, prefSqlModel)); }
private void TestExecutionForPerformance(int runs) { Console.WriteLine("entire skyline: " + _entireSkylineSql); Console.WriteLine(); Console.WriteLine("skyline sample: " + _skylineSampleSql); Console.WriteLine(); var common = new SQLCommon { SkylineType = new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; var commonSort = new SQLCommon { SkylineType = new SkylineBNLSort() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; PrefSQLModel prefSqlModel = common.GetPrefSqlModelFromPreferenceSql(_skylineSampleSql); var randomSubsetsProducer = new RandomSkylineSamplingSubsetsProducer { AllPreferencesCount = prefSqlModel.Skyline.Count, SubsetsCount = prefSqlModel.SkylineSampleCount, SubsetDimension = prefSqlModel.SkylineSampleDimension }; // initial connection takes longer //common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,_entireSkylineSql); //var sw = new Stopwatch(); //sw.Restart(); //DataTable dataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, // _entireSkylineSql); //sw.Stop(); //Console.WriteLine("time: " + common.TimeInMilliseconds); //Console.WriteLine("full time : " + sw.ElapsedMilliseconds); //Console.WriteLine("objects: " + dataTable.Rows.Count); //Console.WriteLine(); //sw.Restart(); //DataTable dataTableSort = commonSort.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, //_entireSkylineSql); //sw.Stop(); //Console.WriteLine("time: "+commonSort.TimeInMilliseconds); //Console.WriteLine("full time : " + sw.ElapsedMilliseconds); //Console.WriteLine("objects: " + dataTableSort.Rows.Count); //Console.WriteLine(); var producedSubsets = new List <IEnumerable <CLRSafeHashSet <int> > >(); for (var i = 0; i < runs; i++) { producedSubsets.Add(randomSubsetsProducer.GetSubsets()); } //var temp = new HashSet<HashSet<int>> //{ // new HashSet<int>() {0, 1, 2}, // new HashSet<int>() {2, 3, 4}, // new HashSet<int>() {4, 5, 6} //}; //producedSubsets.Add(temp); //var temp = new HashSet<HashSet<int>> //{ // new HashSet<int>() {0, 1, 2}, // new HashSet<int>() {3, 4, 5}, // new HashSet<int>() {6, 7, 8}, // new HashSet<int>() {9, 10, 11}, // new HashSet<int>() {1, 2, 12}, // new HashSet<int>() {1, 3, 12}, // new HashSet<int>() {1, 4, 8}, // new HashSet<int>() {1, 5, 8}, // new HashSet<int>() {1, 6, 9}, // new HashSet<int>() {2, 6, 9}, // new HashSet<int>() {5, 1, 4}, // new HashSet<int>() {4, 3, 2}, // new HashSet<int>() {2, 5, 1}, // new HashSet<int>() {3, 2, 0}, // new HashSet<int>() {13, 10, 7} //}; //producedSubsets.Add(temp); ExecuteSampleSkylines(producedSubsets, prefSqlModel, common); //Console.WriteLine(); //ExecuteSampleSkylines(producedSubsets, prefSqlModel, commonSort); //ExecuteSampleSkylines(producedSubsets, prefSqlModel, common); //Console.WriteLine(); //Console.WriteLine(); //Console.WriteLine(common.ParsePreferenceSQL(_entireSkylineSql)); //Console.WriteLine(commonSort.ParsePreferenceSQL(_entireSkylineSql)); //Console.WriteLine(common.GetAnsiSqlFromPrefSqlModel(prefSqlModel)); //Console.WriteLine(commonSort.GetAnsiSqlFromPrefSqlModel(prefSqlModel)); }
/// <summary> /// Build the WHERE Clause to implement a Skyline /// </summary> /// <param name="model"></param> /// <param name="strPreSQL"></param> /// <returns></returns> private string GetCriterionSkylineClause(PrefSQLModel model, string strPreSQL) { string strWhereEqual; string strWhereBetter = " AND ( "; string strSQL = ""; //Only add WHERE if there is not already a where clause bool isWherePresent = strPreSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) > 0; if (isWherePresent) { strWhereEqual = " AND "; } else { strWhereEqual = "WHERE "; } //Build the where clause with each column in the skyline for (int iChild = 0; iChild < model.Skyline.Count; iChild++) { //Competition bool needsTextOrClause = model.Skyline[iChild].IsCategorical; //First child doesn't need an OR/AND if (iChild > 0) { strWhereEqual += " AND "; strWhereBetter += " OR "; } //Falls Text-Spalte ein zusätzliches OR einbauen für den Vergleich Farbe = Farbe if (needsTextOrClause) { strWhereEqual += "("; } strWhereEqual += "{INNERcolumn} <= {column}"; strWhereBetter += "{INNERcolumn} < {column}"; strWhereEqual = strWhereEqual.Replace("{INNERcolumn}", model.Skyline[iChild].InnerExpression); strWhereBetter = strWhereBetter.Replace("{INNERcolumn}", model.Skyline[iChild].InnerExpression); strWhereEqual = strWhereEqual.Replace("{column}", model.Skyline[iChild].Expression); strWhereBetter = strWhereBetter.Replace("{column}", model.Skyline[iChild].Expression); //Falls Text-Spalte ein zusätzliches OR einbauen für den Vergleich Farbe = Farbe if (needsTextOrClause) { strWhereEqual += " OR " + model.Skyline[iChild].InnerFullColumnName + " = " + model.Skyline[iChild].FullColumnName; strWhereEqual += ")"; } } //closing bracket for 2nd condition strWhereBetter += ") "; //Format strPreSQL foreach (KeyValuePair <string, string> table in model.Tables) { //Add ALIAS to tablename (Only if not already an ALIAS was set) if (table.Value.Equals("")) { //Replace tablename (for fields) strPreSQL = strPreSQL.Replace(table.Key + ".", table.Key + "_INNER."); string pattern = @"\b" + table.Key + @"\b"; string replace = table.Key + " " + table.Key + "_INNER"; strPreSQL = Regex.Replace(strPreSQL, pattern, replace, RegexOptions.IgnoreCase); } else { //Replace tablename (for fields) strPreSQL = Regex.Replace(strPreSQL, @"\b" + table.Value + @"\.", table.Value + "_INNER.", RegexOptions.IgnoreCase); //Replace ALIAS string pattern = @"\b" + table.Value + @"\b"; string replace = table.Value + "_INNER"; strPreSQL = Regex.Replace(strPreSQL, pattern, replace, RegexOptions.IgnoreCase); } } //Check if SQL contains TOP Keywords if (model.NumberOfRecords != 0) { //Remove Top Keyword in inner clause int iPosTop = strPreSQL.IndexOf(" TOP ", StringComparison.OrdinalIgnoreCase) + 1; int iPosTopEnd = strPreSQL.Substring(iPosTop + 3).TrimStart().IndexOf(" ", StringComparison.Ordinal); string strSQLAfterTop = strPreSQL.Substring(iPosTop + 3).TrimStart(); strPreSQL = strPreSQL.Substring(0, iPosTop) + strSQLAfterTop.Substring(iPosTopEnd + 1); } strSQL += "NOT EXISTS(" + strPreSQL + " " + strWhereEqual + strWhereBetter + ")"; return(strSQL); }
private void TestCompareAlgorithms() { var common = new SQLCommon { SkylineType = new SkylineBNL() }; var sql = "SELECT cs.*, colors.name, fuels.name, bodies.name, makes.name, conditions.name FROM cars_large cs LEFT OUTER JOIN colors ON cs.color_id = colors.ID LEFT OUTER JOIN fuels ON cs.fuel_id = fuels.ID LEFT OUTER JOIN bodies ON cs.body_id = bodies.ID LEFT OUTER JOIN makes ON cs.make_id = makes.ID LEFT OUTER JOIN conditions ON cs.condition_id = conditions.ID SKYLINE OF cs.price LOW, cs.mileage LOW, cs.horsepower HIGH, cs.enginesize HIGH, cs.consumption LOW, cs.cylinders HIGH, cs.seats HIGH, cs.doors HIGH, cs.gears HIGH, colors.name ('red' >> OTHERS INCOMPARABLE), fuels.name ('diesel' >> 'petrol' >> OTHERS EQUAL), bodies.name ('limousine' >> 'coupé' >> 'suv' >> 'minivan' >> OTHERS EQUAL), makes.name ('BMW' >> 'MERCEDES-BENZ' >> 'HUMMER' >> OTHERS EQUAL), conditions.name ('new' >> 'occasion' >> OTHERS EQUAL)"; PrefSQLModel model = common.GetPrefSqlModelFromPreferenceSql(sql); string sqlBNL = common.ParsePreferenceSQL(sql); DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql); DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName); // use the factory object to create Data access objects. DbConnection connection = factory.CreateConnection(); // will return the connection object (i.e. SqlConnection ...) connection.ConnectionString = Helper.ConnectionString; var dtEntire = new DataTable(); DbDataAdapter dap = factory.CreateDataAdapter(); DbCommand selectCommand = connection.CreateCommand(); selectCommand.CommandText = sqlBNL; dap.SelectCommand = selectCommand; dap.Fill(dtEntire); var common2 = new SQLCommon { SkylineType = new SkylineSQL() }; string sqlNative = common2.ParsePreferenceSQL(sql); var dtEntire2 = new DataTable(); DbDataAdapter dap2 = factory.CreateDataAdapter(); DbCommand selectCommand2 = connection.CreateCommand(); selectCommand2.CommandText = sqlNative; dap2.SelectCommand = selectCommand2; dap2.Fill(dtEntire2); connection.Close(); foreach (DataRow i in dtEntire2.Rows) { var has = false; foreach (DataRow j in entireSkylineDataTable.Rows) { if ((int)i[0] == (int)j[0]) { has = true; break; } } if (!has) { Debug.WriteLine(i[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); }
internal string ParsePreferenceSQL(PrefSQLModel prefSQL) { return(ParsePreferenceSQL(prefSQL.OriginalPreferenceSql, prefSQL)); }
internal DataTable ParseAndExecutePrefSQL(string connectionString, string driverString, PrefSQLModel prefSqlModel) { Helper.ConnectionString = connectionString; Helper.DriverString = driverString; Helper.Cardinality = Cardinality; Helper.WindowHandling = WindowHandling; DataTable dt = Helper.GetResults(ParsePreferenceSQL(prefSqlModel), SkylineType, prefSqlModel, ShowInternalAttributes); TimeInMilliseconds = Helper.TimeInMilliseconds; NumberOfComparisons = Helper.NumberOfComparisons; NumberOfMoves = Helper.NumberOfMoves; return(dt); }