public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkylineViaGetSkyline()
{
string skylineSampleSQL = TestContext.DataRow["skylineSampleSQL"].ToString();
string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
string testComment = TestContext.DataRow["comment"].ToString();
Debug.WriteLine(testComment);
Debug.WriteLine(skylineSampleSQL);
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
}
};
var prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSQL);
var prefSqlModelEntireSkyline = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL);
var subjectUnderTest = new SQLParser.Helper
{
ConnectionString = Helper.ConnectionString,
DriverString = Helper.ProviderName
};
var sw = new Stopwatch();
sw.Start();
var entireSkyline = subjectUnderTest.GetResults(
common.GetAnsiSqlFromPrefSqlModel(prefSqlModelEntireSkyline), common.SkylineType,
prefSqlModelEntireSkyline, false);
sw.Stop();
Debug.WriteLine("ORIG ElapsedMilliseconds={0}", sw.ElapsedMilliseconds);
Debug.WriteLine("ORIG Algorithm ElapsedMilliseconds={0}", subjectUnderTest.TimeInMilliseconds);
sw.Restart();
var sampleSkyline = subjectUnderTest.GetResults(
common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample), common.SkylineType,
prefSqlModelSkylineSample, false);
sw.Stop();
Debug.WriteLine("SMPL ElapsedMilliseconds={0}", sw.ElapsedMilliseconds);
Debug.WriteLine("SMPL Algorithm ElapsedMilliseconds={0}", subjectUnderTest.TimeInMilliseconds);
var entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline);
var sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline);
Debug.WriteLine("ORIG Count={0}", entireSkylineObjectsIds.Count);
Debug.WriteLine("SMPL Count={0}", sampleSkylineObjectsIds.Count);
Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds),
"Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline).");
}
public void TestSamplingObjectsWithinEntireSkylineCount()
{
var entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
var testComment = TestContext.DataRow["comment"].ToString();
Debug.WriteLine(testComment);
var common = new SQLCommon {
SkylineType = new SkylineBNL()
};
var prefSqlModelEntireSkyline = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL);
var subjectUnderTest = new SQLParser.Helper
{
ConnectionString = Helper.ConnectionString,
DriverString = Helper.ProviderName
};
var entireSkyline = subjectUnderTest.GetResults(
common.GetAnsiSqlFromPrefSqlModel(prefSqlModelEntireSkyline), common.SkylineType,
prefSqlModelEntireSkyline, false);
var expected = TestContext.DataRow["entireCount"].ToString();
var actual = entireSkyline.Rows.Count.ToString(CultureInfo.InvariantCulture);
Assert.AreEqual(expected, actual, "Entire Skyline contains unexpected number of 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).");
}
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");
}
}
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 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 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.");
}
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 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]);
}
}
}
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));
}
private void TestForClusterAnalysis()
{
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
},
ShowInternalAttributes = true
};
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 dt = new DataTable();
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
string strQuery;
string operators;
int numberOfRecords;
string[] parameter;
string ansiSql =
common.GetAnsiSqlFromPrefSqlModel(common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));
prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out strQuery, out operators,
out numberOfRecords);
selectCommand.CommandText = strQuery;
dap.SelectCommand = selectCommand;
dt = new DataTable();
dap.Fill(dt);
DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSql);
int[] skylineAttributeColumns = SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
IReadOnlyDictionary <long, object[]> entireSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(entireSkylineNormalized, skylineAttributeColumns);
DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_skylineSampleSql);
IReadOnlyDictionary <long, object[]> sampleSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(sampleSkylineNormalized, skylineAttributeColumns);
for (var i = 0; i < skylineAttributeColumns.Length; i++)
{
dt.Columns.RemoveAt(0);
}
IReadOnlyDictionary <long, object[]> full = prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dt, 0);
SkylineSamplingHelper.NormalizeColumns(full, skylineAttributeColumns);
ClusterAnalysis.CalcMedians(full, skylineAttributeColumns);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > entireBuckets =
ClusterAnalysis.GetBuckets(entireSkylineNormalized, skylineAttributeColumns);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > sampleBuckets =
ClusterAnalysis.GetBuckets(sampleSkylineNormalized, skylineAttributeColumns);
//IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedEntireBuckets =
// ClusterAnalysis.GetAggregatedBuckets(entireBuckets);
//IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedSampleBuckets =
// ClusterAnalysis.GetAggregatedBuckets(sampleBuckets);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > fullB =
ClusterAnalysis.GetBuckets(full, skylineAttributeColumns);
// IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aFullB =
// ClusterAnalysis.GetAggregatedBuckets(fullB);
IOrderedEnumerable <KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > > sorted = fullB.OrderBy(l => l.Value.Count)
.ThenBy(l => l.Key);
int len = Convert.ToInt32(Math.Pow(2, skylineAttributeColumns.Length));
//for (var i = 0; i < len; i++)
foreach (KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > s in sorted)
{
BigInteger i = s.Key;
int entire = entireBuckets.ContainsKey(i) ? entireBuckets[i].Count : 0;
int sample = sampleBuckets.ContainsKey(i) ? sampleBuckets[i].Count : 0;
double entirePercent = (double)entire / entireSkylineNormalized.Count;
double samplePercent = (double)sample / sampleSkylineNormalized.Count;
int fullX = fullB.ContainsKey(i) ? fullB[i].Count : 0;
double fullP = (double)fullX / full.Count;
Console.WriteLine("-- {0,5} -- {5,6} ({6,7:P2} %) -- {1,6} ({3,7:P2} %) -- {2,6} ({4,7:P2} %)", i,
entire, sample, entirePercent,
samplePercent, fullX, fullP);
}
Console.WriteLine();
Console.WriteLine("{0} - {1} - {2}", entireSkylineNormalized.Count, sampleSkylineNormalized.Count,
full.Count);
}
private void TestForDominatedObjects()
{
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
},
ShowInternalAttributes = true
};
DataTable entireSkylineDataTable =
common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
_entireSkylineSql);
int[] skylineAttributeColumns =
SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
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();
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
string strQueryEntire;
string operatorsEntire;
int numberOfRecordsEntire;
string[] parameterEntire;
string ansiSqlEntire =
common.GetAnsiSqlFromPrefSqlModel(
common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));
prefSQL.SQLParser.Helper.DetermineParameters(ansiSqlEntire, out parameterEntire,
out strQueryEntire, out operatorsEntire,
out numberOfRecordsEntire);
selectCommand.CommandText = strQueryEntire;
dap.SelectCommand = selectCommand;
dtEntire = new DataTable();
dap.Fill(dtEntire);
for (var ii = 0; ii < skylineAttributeColumns.Length; ii++)
{
dtEntire.Columns.RemoveAt(0);
}
DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_skylineSampleSql);
DataTable entireSkylineDataTableBestRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSqlBestRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
DataTable entireSkylineDataTableSumRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSqlSumRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
IReadOnlyDictionary <long, object[]> sampleSkylineDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
IReadOnlyDictionary <long, object[]> entireDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dtEntire, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabaseBestRank =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableBestRank, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabaseSumRank =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableSumRank, 0);
IReadOnlyDictionary <long, object[]> randomSample =
SkylineSamplingHelper.GetRandomSample(entireSkylineDatabase, sampleSkylineDataTable.Rows.Count);
var dominatedObjectsEntireSkyline = new DominatedObjects(entireDatabase, entireSkylineDatabase,
skylineAttributeColumns);
var dominatedObjectsSampleSkyline = new DominatedObjects(entireDatabase, sampleSkylineDatabase,
skylineAttributeColumns);
var dominatedObjectsEntireSkylineBestRank = new DominatedObjects(entireDatabase,
entireSkylineDatabaseBestRank, skylineAttributeColumns);
var dominatedObjectsEntireSkylineSumRank = new DominatedObjects(entireDatabase, entireSkylineDatabaseSumRank,
skylineAttributeColumns);
var dominatedObjectsRandomSample = new DominatedObjects(entireDatabase, randomSample,
skylineAttributeColumns);
Debug.WriteLine("entire database size: {0}", entireDatabase.Keys.ToList().Count);
Debug.WriteLine("entire skyline size: {0}", entireSkylineDataTable.Rows.Count);
Debug.WriteLine("sample skyline size: {0}", sampleSkylineDatabase.Keys.ToList().Count);
Debug.WriteLine("random skyline size: {0}", randomSample.Keys.ToList().Count);
Debug.WriteLine("best skyline size: {0}", entireSkylineDatabaseBestRank.Keys.ToList().Count);
Debug.WriteLine("sum skyline size: {0}", entireSkylineDatabaseSumRank.Keys.ToList().Count);
Debug.WriteLine("");
Debug.WriteLine("");
WriteSummary("entire", dominatedObjectsEntireSkyline);
WriteSummary("sample", dominatedObjectsSampleSkyline);
WriteSummary("random", dominatedObjectsRandomSample);
WriteSummary("best", dominatedObjectsEntireSkylineBestRank);
WriteSummary("sum", dominatedObjectsEntireSkylineSumRank);
WriteDominatingObjects("entire", dominatedObjectsEntireSkyline);
WriteDominatingObjects("sample", dominatedObjectsSampleSkyline);
WriteDominatingObjects("random", dominatedObjectsRandomSample);
WriteDominatingObjects("best", dominatedObjectsEntireSkylineBestRank);
WriteDominatingObjects("sum", dominatedObjectsEntireSkylineSumRank);
}
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.");
}
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).");
}