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