/// <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>
/// 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;
}
/// <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.Ordinal) > 0;
if (isWherePresent)
{
strSQL = " AND " + strSQL;
}
else
{
strSQL = " WHERE " + strSQL;
}
}
return strSQL;
}
/// <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;
}
/// <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;
}
/// <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.Ordinal) > 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.Ordinal);
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;
}
/// <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;
}
/// <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;
}
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;
}
internal string GetAnsiSqlFromPrefSqlModel(PrefSQLModel prefSqlModel)
{
return ParsePreferenceSQL(prefSqlModel.OriginalPreferenceSql, prefSqlModel);
}
internal DataTable ExecuteFromPrefSqlModel(string dbConnection, string dbProvider, PrefSQLModel prefSqlModel)
{
return ParseAndExecutePrefSQL(dbConnection, dbProvider, prefSqlModel);
}
/// <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
if (strPrefSQL.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;
}
/// <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;
}
