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 TestIssue54()
{
string prefSQL = "SELECT c.Title AS Name, c.Price, c.Consumption, m.Name AS Manufacturer, b.Name AS Body "
+ "FROM Cars c "
+ "LEFT OUTER JOIN Makes m ON c.Make_Id = m.Id "
+ "LEFT OUTER JOIN Bodies b ON c.Body_Id = b.Id "
+ "WHERE m.Name = 'VW' AND b.Name = 'Bus' "
+ "SKYLINE OF c.Price LOW 1000 EQUAL, c.Consumption LOW";
var common = new SQLCommon
{
SkylineType = new SkylineBNL(),
ShowInternalAttributes = true
};
try
{
//If there is no exception in the execution of this query the test is successful
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
Assert.IsTrue(true);
}
catch
{
Assert.IsFalse(true);
}
}
public void TestIssue67()
{
string prefSQL = "SELECT c.Title as Name, c.Price, co.Name As Color "
+ "FROM Cars c "
+ "LEFT OUTER JOIN Colors co ON c.Color_Id = co.Id "
+ "SKYLINE OF c.Price HIGH, co.Name ('pink' >> 'black' >> OTHERS EQUAL) "
+ "ORDER BY BEST_RANK()";
SQLCommon common = new SQLCommon();
common.SkylineType = new SkylineDQ();
try
{
//If there is no exception in the execution of this query the test is successful
DataTable dtStandalone = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
String sql = common.ParsePreferenceSQL(prefSQL);
SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString); //for CLR performance tets
cnnSQL.Open();
SqlDataAdapter dap = new SqlDataAdapter(sql, cnnSQL);
DataTable dtCLR = new DataTable();
dap.Fill(dtCLR);
Assert.AreEqual(dtStandalone.Rows.Count, dtCLR.Rows.Count);
}
catch
{
Assert.IsFalse(true);
}
}
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 void btnExecute_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
btnExecute.Enabled = false;
SQLCommon parser = new SQLCommon();
if (optSQL.Checked)
{
parser.SkylineType = new SkylineSQL();
}
else if (optBNL.Checked)
{
parser.SkylineType = new SkylineBNLSort();
}
else if (optHexagon.Checked)
{
parser.SkylineType = new SkylineHexagon();
}
else if (optDQ.Checked)
{
parser.SkylineType = new SkylineDQ();
}
if (chkShowSkyline.Checked)
{
parser.ShowInternalAttributes = true;
}
else
{
parser.ShowInternalAttributes = false;
}
DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, txtPrefSQL.Text);
BindingSource sBind = new BindingSource();
sBind.DataSource = dt;
gridSkyline.AutoGenerateColumns = true;
gridSkyline.DataSource = dt;
gridSkyline.DataSource = sBind;
gridSkyline.Refresh();
sw.Stop();
txtTime.Text = sw.ElapsedMilliseconds.ToString();
txtTimeAlgo.Text = parser.TimeInMilliseconds.ToString();
txtRecords.Text = dt.Rows.Count.ToString();
btnExecute.Enabled = true;
}
public void TestSQLSelectWithCoalesce()
{
string sql = "select COALESCE(cars.id, 0) FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW";
SQLCommon common = new SQLCommon();
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);
Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
}
public void TestSQLSelectSubqueryWithGroupBy()
{
string sql = "SELECT t.color_id, Amount FROM (SELECT cars.color_id, COUNT(*) AS Amount FROM cars GROUP BY cars.color_id) t";
SQLCommon common = new SQLCommon();
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);
Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
}
public void TestSQLSelectSubquery()
{
string sql = "select t.id FROM (SELECT * FROM cars) t";
SQLCommon common = new SQLCommon();
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);
Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
}
public void TestSQLSkylineWithDisfavour()
{
string sql = "SELECT cars.title, colors.name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name DISFAVOUR 'red', cars.price LOW";
SQLCommon common = new SQLCommon();
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);
Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
}
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 TestWeightedSum_DataTable()
{
string[] strPrefSQL = GetPreferences();
for (int i = 0; i <= strPrefSQL.GetUpperBound(0); i++)
{
SQLCommon common = new SQLCommon();
common.SkylineType = new SkylineSQL();
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strPrefSQL[i]);
//Check tuples (every algorithm should deliver the same amount of tuples)
Assert.AreEqual(dt.Rows.Count, 55208, 0, "WeightedSum Sorting failed");
}
}
public void TestSamplingNumberOfObjectsWithinEntireSkyline()
{
string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
string testComment = TestContext.DataRow["comment"].ToString();
int expectedNumberOfEntireSkylineObjects =
int.Parse(TestContext.DataRow["expectedNumberOfEntireSkylineObjects"].ToString());
Debug.WriteLine(testComment);
Debug.WriteLine(entireSkylineSQL);
var common = new SQLCommon { SkylineType = new SkylineBNL() };
DataTable skyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
entireSkylineSQL);
Assert.AreEqual(expectedNumberOfEntireSkylineObjects, skyline.Rows.Count,
"Unexpected number of Skyline objects.");
}
public void TestSamplingNumberOfObjectsWithinEntireSkyline()
{
string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
string testComment = TestContext.DataRow["comment"].ToString();
int expectedNumberOfEntireSkylineObjects =
int.Parse(TestContext.DataRow["expectedNumberOfEntireSkylineObjects"].ToString());
Debug.WriteLine(testComment);
Debug.WriteLine(entireSkylineSQL);
var common = new SQLCommon {
SkylineType = new SkylineBNL()
};
DataTable skyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
entireSkylineSQL);
Assert.AreEqual(expectedNumberOfEntireSkylineObjects, skyline.Rows.Count,
"Unexpected number of Skyline objects.");
}
public void TestIssue66()
{
string prefSQL = " SELECT * FROM ( SELECT * FROM cars )b";
var common = new SQLCommon
{
SkylineType = new SkylineBNL(),
ShowInternalAttributes = false
};
try
{
//If there is no exception in the execution of this query the test is successful
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
Assert.IsTrue(true);
}
catch
{
Assert.IsFalse(true);
}
}
public void TestIssue63()
{
string prefSQL = "SELECT TOP 5 c.Id, c.title AS IDFROM, c.title AS IDWHERE, c.title AS IDTOP "
+ "FROM Cars c "
+ "SKYLINE OF c.id LOW ";
var common = new SQLCommon
{
SkylineType = new SkylineBNL(),
ShowInternalAttributes = true
};
try
{
//If there is no exception in the execution of this query the test is successful
DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
Assert.IsTrue(true);
}
catch
{
Assert.IsFalse(true);
}
}
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 Run()
{
try
{
//SKYLINE Queries
//string strPrefSQL = "SELECT t.id FROM cars t SKYLINE OF t.price LOW, t.mileage LOW";
//string strPrefSQL = "select cars.id from cars";
//string strPrefSQL = "SELECT * FROM cars t WHERE t.doors = 5 SKYLINE OF t.price LOW, t.mileage LOW, t.horsepower HIGH, t.consumption LOW, t.registrationnumeric HIGH";
//string strPrefSQL = "SELECT t.title AS Modellname, t.price AS Preis, t.consumption AS Verbrauch FROM cars t LEFT OUTER JOIN Makes m ON t.make_id = m.id LEFT OUTER JOIN Bodies b ON t.body_id = b.id WHERE m.name = 'VW' AND b.name = 'Bus' SKYLINE OF t.price LOW 1000 EQUAL, t.consumption LOW";
//string strPrefSQL = "SELECT t.id, t.title, t.price, c.name, t.enginesize FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id SKYLINE OF t.price LOW, c.name ('pink' >> 'black' >> OTHERS INCOMPARABLE), t.enginesize HIGH ORDER BY BEST_RANK()";
//string strPrefSQL = "SELECT t.id, t.title, t.price, c.name, t.enginesize FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id SKYLINE OF t.price LOW, c.name (OTHERS EQUAL >> 'pink' >> 'red'), t.enginesize HIGH ORDER BY SUM_RANK()";
//string strPrefSQL = "SELECT t.id, t.title, t.price FROM cars t SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
//string strPrefSQL = "SELECT t.id, t.price, t.mileage FROM cars t SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY SUM_RANK()";
//string strPrefSQL = "SELECT t.id, t.title, bodies.name AS Chassis, t.price, fuels.name FROM cars t LEFT OUTER JOIN fuels ON t.fuel_id = fuels.ID LEFT OUTER JOIN bodies ON t.body_id = bodies.ID SKYLINE OF bodies.name ('bus' >> OTHERS EQUAL) IS MORE IMPORTANT THAN t.price LOW, fuels.name ('petrol' >> OTHERS EQUAL)";
//string strPrefSQL = "SELECT t.id, t.title, t.price, t.mileage, t.enginesize FROM cars t SKYLINE OF t.mileage LOW IS MORE IMPORTANT THAN t.price LOW, t.engineszie HIGH ";
//SKYLINE Queries with JOINS
//string strPrefSQL = "SELECT c.id, c.price, b.name FROM cars t LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.price LOW, b.name ('Bus' >> 'Kleinwagen')";
//string strPrefSQL = "SELECT c.id, c.price FROM cars t LEFT OUTER JOIN colors cc ON c.color_id = cc.id SKYLINE OF c.horsepower HIGH, cc.name ('red' >> 'blue' >> 'yellow')";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH, colors.name ('red' >> 'blue' >> 'yellow' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.pric FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name (OTHERS INCOMPARABLE >> 'blue' >> 'red')";
//string strPrefSQL = "SELECT c.id, c.price FROM cars t LEFT OUTER JOIN colors cc ON c.color_id = cc.id LEFT OUTER JOIN fuels f ON f.id = c.fuel_id SKYLINE OF c.price LOW 1000 INCOMPARABLE, cc.name ('red' == 'blue' >> OTHERS INCOMPARABLE >> 'gray'), f.name ('petrol' >> OTHERS INCOMPARABLE >> 'diesel')";
//string strPrefSQL = "SELECT c.title AS Name, c.Price FROM cars t LEFT OUTER JOIN colors co ON c.color_id = co.ID LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.Price LOW, c.Mileage LOW";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('silver' >> 'yellow' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t.id, t.title, t.price FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> {'blue', 'yellow'} >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t.id FROM cars t LEFT OUTER JOIN bodies ON t.body_id = bodies.ID LEFT OUTER JOIN colors ON colors.id = t.color_id SKYLINE OF t.price AROUND 10000, colors.name ('red' >> OTHERS EQUAL), bodies.name ('van' >> 'compact car' >> OTHERS EQUAL) ORDER BY BEST_RANK()";
//RANKING Queries
string strPrefSQL = "SELECT t.id, t.title FROM cars t ORDER BY WEIGHTEDSUM (t.price LOW 0.8, t.mileage LOW 0.2)";
//string strPrefSQL = "SELECT t.id, t.title FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id ORDER BY WEIGTHEDSUM (t.price LOW 0.5, c.name ('brown' >> 'green' >> OTHERS EQUAL) 0.5)";
//Playground --> Test here your queries
//string strPrefSQL = "SELECT c.id, c.price, b.name FROM cars_small c LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.price LOW, b.name ('Bus' >> 'Kleinwagen')";
//string strPrefSQL = "SELECT c.id, c.price FROM cars_small c LEFT OUTER JOIN colors cc ON c.color_id = cc.id ORDER BY WEIGHTEDSUM (c.price LOW 0.5, cc.name ('brown' >> 'green') 0.5)";
//string strPrefSQL = "SELECT c.id, c.price FROM cars_small c LEFT OUTER JOIN colors cc ON c.color_id = cc.id SKYLINE OF c.horsepower HIGH, cc.name ('red' >> 'blue' >> 'yellow')";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars_small t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id FROM cars t1 ORDER BY WEIGHTEDSUM (t1.price HIGH 0.5, t1.mileage HIGH 0.5, t1.horsepower LOW 0.5, t1.enginesize LOW 0.5, t1.consumption HIGH 0.5, t1.doors LOW 0.5, t1.cylinders LOW 0.5)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH, colors.name ('red' >> 'blue' >> 'yellow' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
//string strPrefSQL = "SELECT t1.id FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW";
//string strPrefSQL = "SELECT cars.id, cars.consumption FROM cars SKYLINE OF cars.consumption LOW, cars.enginesize HIGH, cars.price LOW";
//string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.registrationnumeric HIGH, cars.mileage LOW, cars.horsepower HIGH 100 EQUAL";
//string strPrefSQL = "SELECT cars.id, cars.horsepower FROM cars SKYLINE OF cars.horsepower HIGH, cars.mileage LOW";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, t1.enginesize FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW, t1.enginesize HIGH ORDER BY SUM_RANK()";
//string strPrefSQL = "SELECT t1.id FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
//string strPrefSQL = "SELECT t1.id FROM cars_norm t1 SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower LOW, t1.enginesize LOW, t1.doors LOW, t1.consumption LOW, t1.cylinders LOW";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
//string strPrefSQL = "SELECT t1.id AS ID, t1.title, t1.price FROM cars_small t1 SKYLINE OF t1.price LOW, t1.title ('hans' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, colors.name FROM cars_small t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, colors.name FROM cars_small t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name (OTHERS INCOMPARABLE >> 'blue' >> 'red')";
//string strPrefSQL = "SELECT * FROM cars_small cs SKYLINE OF cs.price LOW, cs.mileage LOW SAMPLE BY RANDOM_SUBSETS COUNT 2 DIMENSION 1";
//string strPrefSQL = "SELECT t1.id, t1.price, t1.mileage FROM cars_small t1 SKYLINE OF t1.price LOW, t1.mileage LOW";
//string strPrefSQL = "SELECT t1.id, t1.price, t1.mileage FROM cars_small t1 SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY SUM_RANK()";
//string strPrefSQL = "SELECT t1.id FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW";
//string strPrefSQL = "SELECT c.id, c.price FROM cars_medium c LEFT OUTER JOIN colors cc ON c.color_id = cc.id LEFT OUTER JOIN fuels f ON f.id = c.fuel_id SKYLINE OF c.price LOW 1000 INCOMPARABLE, cc.name ('red' == 'blue' >> OTHERS INCOMPARABLE >> 'gray'), f.name ('petrol' >> OTHERS INCOMPARABLE >> 'diesel')";
//string strPrefSQL = "SELECT c.title AS Name, c.Price, c.Mileage, co.Name AS Color, b.Name AS Body FROM Cars AS c LEFT OUTER JOIN colors co ON c.color_id = co.ID LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.Price LOW, c.Mileage LOW";
//string strPrefSQL = "SELECT t.id FROM cars t SKYLINE OF t.price LOW, t.mileage LOW";
//string strPrefSQL = "SELECT t1.id, t1.title, t1.price, colors.name FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('silver' >> 'yellow' >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT cars.id, cars.title FROM cars SKYLINE OF cars.price LOW,cars.mileage LOW,cars.horsepower HIGH,cars.enginesize HIGH,cars.consumption LOW,cars.doors HIGH,cars.seats HIGH";
//string strPrefSQL = "SELECT t.id, t.title, t.price, colors.name AS colour FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> {'blue', 'yellow'} >> OTHERS INCOMPARABLE)";
//string strPrefSQL = "SELECT t.id, t.title FROM cars t ORDER BY WEIGHTEDSUM (t.price LOW 0.8, t.mileage LOW 0.2)";
//string strPrefSQL = "SELECT t.id, t.title FROM cars t ORDER BY WEIGHTEDSUM( t.price LOW 0.8, t.mileage LOW 0.2)";
//Query that results in more than 4000 Characters
/*string strPrefSQL = "SELECT t1.id , t1.title , t1.price , t1.mileage , colors.name FROM cars_small t1 " +
* "LEFT OUTER JOIN colors ON t1.color_id = colors.ID " +
* "LEFT OUTER JOIN bodies ON t1.body_id = bodies.id " +
* "LEFT OUTER JOIN Conditions ON t1.Condition_Id = Conditions.Id " +
* "LEFT OUTER JOIN Models ON t1.Model_Id = Models.Id " +
* "LEFT OUTER JOIN Makes ON t1.Make_Id = Makes.Id " +
* "LEFT OUTER JOIN Drives ON t1.Drive_Id = Drives.Id " +
* "LEFT OUTER JOIN Efficiencies ON t1.Efficiency_Id = Efficiencies.Id " +
* "LEFT OUTER JOIN Pollutions ON t1.Pollution_Id = Pollutions.Id " +
* "LEFT OUTER JOIN Transmissions ON t1.Transmission_Id = Transmissions.Id " +
* "LEFT OUTER JOIN Fuels ON t1.Fuel_Id = Fuels.Id " +
* "WHERE t1.price < 10000 " +
* "SKYLINE OF " +
* "t1.price LOW " +
* ", colors.name " +
* "(" +
* "'anthracite' >> 'beige' >> 'blue' >> 'bordeaux' >> " +
* " 'brown' >> 'yellow' >> 'gold' >> 'gray' >> 'green' >> " +
* " 'orange' >> 'pink' >> 'red' >> 'black' >> 'silver' >> " +
* " 'turquoise' >> 'violet' >> 'white'" +
* ") " +
*
* ", bodies.name " +
* "(" +
* " 'bus' >> 'cabriolet' >> 'coupé' >> 'van' >> " +
* " 'compact car' >> 'estate car' >> 'minivan' >> " +
* " 'limousine' >> 'pick-up' >> 'scooter' >> 'suv' " +
* ")" +
* ", fuels.name " +
* "(" +
* " 'hybrid' >> 'bioethanol' >> 'diesel' >> 'gas' >> " +
* " 'electro' >> 'petrol' " +
* ")";*/
//Query with 2 id spalten
//string strPrefSQL = "SELECT t.id, colors.id as colorid, t.title, t.price, colors.name AS colour FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> 'blue' >> OTHERS EQUAL)";
Debug.WriteLine(strPrefSQL);
SQLCommon parser = new SQLCommon();
//Choose here your algorithm
parser.SkylineType = new SkylineSQL();
//parser.SkylineType = new SkylineBNL();
//parser.SkylineType = new SkylineBNLSort();
//parser.SkylineType = new SkylineHexagon();
//parser.SkylineType = new SkylineDQ();
//parser.SkylineType = new MultipleSkylineBNL();
//parser.SkylineType = new SkylineDecisionTree();
//Some other available properties
parser.ShowInternalAttributes = true;
parser.SkylineUpToLevel = 1;
//First parse only (to get the parsed string for CLR)
Debug.WriteLine(parser.ParsePreferenceSQL(strPrefSQL));
//Now parse and execute
Stopwatch sw = new Stopwatch();
sw.Start();
DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strPrefSQL);
sw.Stop();
StringBuilder sb = new StringBuilder();
sb.AppendLine("------------------------------------------");
sb.AppendLine("STATISTIC");
sb.AppendLine("------------------------------------------");
sb.AppendLine(" skyline size:" + dt.Rows.Count.ToString().PadLeft(6));
sb.AppendLine(" fields:" + dt.Columns.Count.ToString().PadLeft(6));
sb.AppendLine("algo time elapsed ms:" + parser.TimeInMilliseconds.ToString().PadLeft(6));
sb.AppendLine("total time elapsed ms:" + sw.ElapsedMilliseconds.ToString().PadLeft(6));
Debug.Write(sb);
}
catch (Exception ex)
{
Debug.WriteLine("ERROR: " + ex);
}
Environment.Exit(0);
}
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 TestForSetCoverage()
{
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
},
ShowInternalAttributes = true
};
DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName, _entireSkylineSql);
//Console.WriteLine("time entire: " + common.TimeInMilliseconds);
//Console.WriteLine("count entire: " + entireSkylineDataTable.Rows.Count);
int[] skylineAttributeColumns = SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
IReadOnlyDictionary <long, object[]> entireSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(entireSkylineNormalized, skylineAttributeColumns);
for (var i = 0; i < 10; i++)
{
DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName, _skylineSampleSql);
Console.WriteLine("time sample: " + common.TimeInMilliseconds);
Console.WriteLine("count sample: " + sampleSkylineDataTable.Rows.Count);
IReadOnlyDictionary <long, object[]> sampleSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(sampleSkylineNormalized, skylineAttributeColumns);
IReadOnlyDictionary <long, object[]> baseRandomSampleNormalized =
SkylineSamplingHelper.GetRandomSample(entireSkylineNormalized,
sampleSkylineDataTable.Rows.Count);
IReadOnlyDictionary <long, object[]> secondRandomSampleNormalized =
SkylineSamplingHelper.GetRandomSample(entireSkylineNormalized,
sampleSkylineDataTable.Rows.Count);
double setCoverageCoveredBySecondRandomSample = SetCoverage.GetCoverage(baseRandomSampleNormalized,
secondRandomSampleNormalized, skylineAttributeColumns);
double setCoverageCoveredBySkylineSample = SetCoverage.GetCoverage(baseRandomSampleNormalized,
sampleSkylineNormalized, skylineAttributeColumns);
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[]> entireSkylineDataTableBestRankNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableBestRank, 0);
SkylineSamplingHelper.NormalizeColumns(entireSkylineDataTableBestRankNormalized, skylineAttributeColumns);
IReadOnlyDictionary <long, object[]> entireSkylineDataTableSumRankNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableSumRank, 0);
SkylineSamplingHelper.NormalizeColumns(entireSkylineDataTableSumRankNormalized, skylineAttributeColumns);
double setCoverageCoveredByEntireBestRank = SetCoverage.GetCoverage(baseRandomSampleNormalized,
entireSkylineDataTableBestRankNormalized, skylineAttributeColumns);
double setCoverageCoveredByEntireSumRank = SetCoverage.GetCoverage(baseRandomSampleNormalized,
entireSkylineDataTableSumRankNormalized, skylineAttributeColumns);
Console.WriteLine("sc second random: " + setCoverageCoveredBySecondRandomSample);
Console.WriteLine("sc sample : " + setCoverageCoveredBySkylineSample);
Console.WriteLine("sc entire best : " + setCoverageCoveredByEntireBestRank);
Console.WriteLine("sc entire sum : " + setCoverageCoveredByEntireSumRank);
Console.WriteLine();
//Console.WriteLine("set coverage covered by second random sample: " +
// setCoverageCoveredBySecondRandomSample);
//Console.WriteLine("set coverage covered by skyline sample: " + setCoverageCoveredBySkylineSample);
}
}
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 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).");
}
