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