/// <summary>
/// Create the ORDER BY-Clause from the preference model
/// </summary>
/// <param name="model"></param>
/// <param name="type"></param>
/// <returns></returns>
public string GetSortClause(PrefSQLModel model, SQLCommon.Ordering type)
{
string strSQL = "";
switch (type)
{
case SQLCommon.Ordering.AttributePosition:
strSQL = GetSortAttributePositionClause(model);
break;
case SQLCommon.Ordering.RankingSummarize:
strSQL = GetSortRankingSumClause(model);
break;
case SQLCommon.Ordering.RankingBestOf:
strSQL = GetSortRankingBestOfClause(model);
break;
case SQLCommon.Ordering.AsIs:
strSQL = ""; //Return no ORDER BY Clause
break;
case SQLCommon.Ordering.Random:
strSQL = GetSortRandomClause();
break;
case SQLCommon.Ordering.EntropyFunction:
{
strSQL = GetSortEntropyValue(model);
break;
}
}
if (strSQL.Length > 0)
{
strSQL = " ORDER BY " + strSQL;
}
return strSQL;
}
public void TestTableAliasBug()
{
// prefSQL: no overlap in schema name and table alias
const string prefQuery1 = "SELECT o.id, someSchema.someUDF(o.id) AS Udf1 " +
"FROM cars AS o " +
"SKYLINE OF o.price LOW ";
const string expectedQuery1 = "SELECT o.id, someSchema.someUDF(o.id) AS Udf1 FROM cars AS o " +
"WHERE NOT EXISTS(SELECT o_INNER.id, someSchema.someUDF(o_INNER.id) AS Udf1 FROM cars AS o_INNER WHERE o_INNER.price <= o.price AND ( o_INNER.price < o.price) )";
// prefSQL: table alias is part of schema name
const string prefQuery2 = "SELECT o.id, dbo.someUDF(o.id) AS Udf1 " +
"FROM cars AS o " +
"SKYLINE OF o.price LOW ";
const string expectedQuery2 = "SELECT o.id, dbo.someUDF(o.id) AS Udf1 FROM cars AS o " +
"WHERE NOT EXISTS(SELECT o_INNER.id, dbo.someUDF(o_INNER.id) AS Udf1 FROM cars AS o_INNER WHERE o_INNER.price <= o.price AND ( o_INNER.price < o.price) )";
// build query
var engine = new SQLCommon();
var actualQuery1 = engine.ParsePreferenceSQL(prefQuery1);
var actualQuery2 = engine.ParsePreferenceSQL(prefQuery2);
// verify outcome
Assert.AreEqual(expectedQuery1, actualQuery1);
Assert.AreEqual(expectedQuery2, actualQuery2);
}
public void TestParserCategoricalOthersFirst()
{
string strPrefSQL = "SELECT t1.id FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 13902824 SKYLINE OF colorrs.name (OTHERS INCOMPARABLE >> 'blue' >> 'red')";
string expected = "SELECT t1.id FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 13902824 AND NOT EXISTS(SELECT t1_INNER.id FROM cars t1_INNER LEFT OUTER JOIN colors colors_INNER ON t1_INNER.color_id = colors_INNER.ID WHERE t1_INNER.price < 13902824 AND (CASE WHEN colorrs_INNER.name = 'blue' THEN 100 WHEN colorrs_INNER.name = 'red' THEN 200 ELSE 1 END <= CASE WHEN colorrs.name = 'blue' THEN 100 WHEN colorrs.name = 'red' THEN 200 ELSE 0 END OR colorrs_INNER.name = colorrs.name) AND ( CASE WHEN colorrs_INNER.name = 'blue' THEN 100 WHEN colorrs_INNER.name = 'red' THEN 200 ELSE 1 END < CASE WHEN colorrs.name = 'blue' THEN 100 WHEN colorrs.name = 'red' THEN 200 ELSE 0 END) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserFavour()
{
string strPrefSQL = "SELECT * FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name FAVOUR 'red'";
string expected = "SELECT * FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID WHERE NOT EXISTS(SELECT * FROM cars cars_INNER LEFT OUTER JOIN colors colors_INNER ON cars_INNER.color_id = colors_INNER.ID WHERE CASE WHEN colors_INNER.name = 'red' THEN 1 ELSE 2 END <= CASE WHEN colors.name = 'red' THEN 1 ELSE 2 END AND ( CASE WHEN colors_INNER.name = 'red' THEN 1 ELSE 2 END < CASE WHEN colors.name = 'red' THEN 1 ELSE 2 END) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected, actual, true, "SQL not built correctly");
}
public void TestParserHigh()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.horsepower HIGH";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE cars_INNER.horsepower * -1 <= cars.horsepower * -1 AND ( cars_INNER.horsepower * -1 < cars.horsepower * -1) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserHighCustom()
{
string strPrefSQL = "SELECT * FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name ('red' >> 'blue' >> OTHERS EQUAL >> 'gray') ";
string expected = "SELECT * FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID WHERE NOT EXISTS(SELECT * FROM cars cars_INNER LEFT OUTER JOIN colors colors_INNER ON cars_INNER.color_id = colors_INNER.ID WHERE (CASE WHEN colors_INNER.name = 'red' THEN 0 WHEN colors_INNER.name = 'blue' THEN 100 WHEN colors_INNER.name = 'gray' THEN 300 ELSE 200 END <= CASE WHEN colors.name = 'red' THEN 0 WHEN colors.name = 'blue' THEN 100 WHEN colors.name = 'gray' THEN 300 ELSE 200 END OR colors_INNER.name = colors.name) AND ( CASE WHEN colors_INNER.name = 'red' THEN 0 WHEN colors_INNER.name = 'blue' THEN 100 WHEN colors_INNER.name = 'gray' THEN 300 ELSE 200 END < CASE WHEN colors.name = 'red' THEN 0 WHEN colors.name = 'blue' THEN 100 WHEN colors.name = 'gray' THEN 300 ELSE 200 END) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserAround()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price AROUND 15000";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE ABS(cars_INNER.price - 15000) <= ABS(cars.price - 15000) AND ( ABS(cars_INNER.price - 15000) < ABS(cars.price - 15000)) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserLow()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.mileage LOW";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE cars_INNER.mileage <= cars.mileage AND ( cars_INNER.mileage < cars.mileage) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserOrderingRankingSum()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW, cars.horsepower HIGH ORDER BY SUM_RANK()";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE cars_INNER.price <= cars.price AND cars_INNER.mileage <= cars.mileage AND cars_INNER.horsepower * -1 <= cars.horsepower * -1 AND ( cars_INNER.price < cars.price OR cars_INNER.mileage < cars.mileage OR cars_INNER.horsepower * -1 < cars.horsepower * -1) ) ORDER BY DENSE_RANK() over (ORDER BY cars.price) + DENSE_RANK() over (ORDER BY cars.mileage) + DENSE_RANK() over (ORDER BY cars.horsepower * -1)";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected, actual, true, "SQL not built correctly");
}
public void TestParserSkyline2DimensionsJoinMultipleAccumulation()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.Price, colors.Name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF cars.price LOW, colors.name ('pink' >> {'red', 'black'} >> 'beige' == 'yellow') ORDER BY cars.price ASC, colors.name('pink'>>{'red','black'}>>'beige'=='yellow')";
string expected = "SELECT cars.id, cars.title, cars.Price, colors.Name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID WHERE NOT EXISTS(SELECT cars_INNER.id, cars_INNER.title, cars_INNER.Price, colors_INNER.Name FROM cars cars_INNER LEFT OUTER JOIN colors colors_INNER ON cars_INNER.color_id = colors_INNER.ID WHERE cars_INNER.price <= cars.price AND (CASE WHEN colors_INNER.name = 'pink' THEN 0 WHEN colors_INNER.name IN ('red','black') THEN 101 WHEN colors_INNER.name = 'beige' THEN 200 WHEN colors_INNER.name = 'yellow' THEN 200 END <= CASE WHEN colors.name = 'pink' THEN 0 WHEN colors.name IN ('red','black') THEN 100 WHEN colors.name = 'beige' THEN 200 WHEN colors.name = 'yellow' THEN 200 END OR colors_INNER.name = colors.name) AND ( cars_INNER.price < cars.price OR CASE WHEN colors_INNER.name = 'pink' THEN 0 WHEN colors_INNER.name IN ('red','black') THEN 101 WHEN colors_INNER.name = 'beige' THEN 200 WHEN colors_INNER.name = 'yellow' THEN 200 END < CASE WHEN colors.name = 'pink' THEN 0 WHEN colors.name IN ('red','black') THEN 100 WHEN colors.name = 'beige' THEN 200 WHEN colors.name = 'yellow' THEN 200 END) ) ORDER BY cars.price ASC, CASE WHEN colors.name = 'pink' THEN 0 WHEN colors.name IN ('red','black') THEN 100 WHEN colors.name = 'beige' THEN 200 WHEN colors.name = 'yellow' THEN 200 END ASC";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkyline2DimensionsJoinOthersAccumulation()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.Price, colors.Name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF cars.price LOW, colors.name ('türkis' >> 'yellow' >> OTHERS INCOMPARABLE)";
string expected = "SELECT cars.id, cars.title, cars.Price, colors.Name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID WHERE NOT EXISTS(SELECT cars_INNER.id, cars_INNER.title, cars_INNER.Price, colors_INNER.Name FROM cars cars_INNER LEFT OUTER JOIN colors colors_INNER ON cars_INNER.color_id = colors_INNER.ID WHERE cars_INNER.price <= cars.price AND (CASE WHEN colors_INNER.name = 'türkis' THEN 0 WHEN colors_INNER.name = 'yellow' THEN 100 ELSE 201 END <= CASE WHEN colors.name = 'türkis' THEN 0 WHEN colors.name = 'yellow' THEN 100 ELSE 200 END OR colors_INNER.name = colors.name) AND ( cars_INNER.price < cars.price OR CASE WHEN colors_INNER.name = 'türkis' THEN 0 WHEN colors_INNER.name = 'yellow' THEN 100 ELSE 201 END < CASE WHEN colors.name = 'türkis' THEN 0 WHEN colors.name = 'yellow' THEN 100 ELSE 200 END) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserOrderingAsIs()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW ORDER BY cars.title";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE cars_INNER.price <= cars.price AND cars_INNER.mileage <= cars.mileage AND ( cars_INNER.price < cars.price OR cars_INNER.mileage < cars.mileage) ) ORDER BY cars.title";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected, actual, true, "SQL not built correctly");
}
public void TestParserSkyline2Dimensions()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW ORDER BY price ASC, mileage ASC";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE cars_INNER.price <= cars.price AND cars_INNER.mileage <= cars.mileage AND ( cars_INNER.price < cars.price OR cars_INNER.mileage < cars.mileage) ) ORDER BY price ASC, mileage ASC";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestIssue49()
{
string prefSQL = "SELECT c.id as id, c.title as name, 'Constant ' + c.reference as Test FROM cars as c ORDER BY WEIGHTEDSUM(c.price around 1 1.0)";
SQLCommon common = new SQLCommon();
common.ShowInternalAttributes = true;
string parsedSQL = common.ParsePreferenceSQL(prefSQL);
string expectedBNLSort = "EXEC dbo.prefSQL_Ranking 'SELECT c.id as id, c.title as name, ''Constant '' + c.reference as Test FROM cars as c ORDER BY WEIGHTEDSUM(c.price around 1 1.0)', 'SELECT MIN(ABS(c.price - 1)), MAX(ABS(c.price - 1)) FROM cars c', 0, '1', 'ABS(c.price - 1)', True, 'c_price'";
Assert.AreEqual(parsedSQL, expectedBNLSort, "Query does not match parsed Query");
}
public void TestIssue47()
{
string prefSQL = "SELECT t1.id FROM cars_small t1 SKYLINE OF t1.price LOW ORDER BY t1.price ASC";
SQLCommon common = new SQLCommon();
common.SkylineType = new SkylineBNL();
common.ShowInternalAttributes = true;
string parsedSQL = common.ParsePreferenceSQL(prefSQL);
string expectedBNLSort = "EXEC dbo.prefSQL_SkylineBNLLevel 'SELECT CAST(t1.price AS bigint) AS SkylineAttribute0 , t1.id , CAST(t1.price AS bigint) AS SkylineAttributet1_price FROM cars_small t1 ORDER BY t1.price ASC', 'LOW', 0, 4";
Assert.AreEqual(parsedSQL, expectedBNLSort, "Query does not match parsed Query");
}
public void TestParserOrderingAsIsWithCategory()
{
string strPrefSQL = "SELECT t1.id, t1.title, t2.name, bodies.name FROM cars_small t1 " +
"LEFT OUTER JOIN colors t2 ON t1.color_id = t2.ID " +
"LEFT OUTER JOIN bodies ON t1.body_id = bodies.ID " +
"SKYLINE OF t1.price LOW, t1.mileage LOW " +
"ORDER BY bodies.name ('minivan' >> OTHERS EQUAL), t2.name ('white' >> OTHERS EQUAL) ";
string expected = "SELECT t1.id, t1.title, t2.name, bodies.name FROM cars_small t1 LEFT OUTER JOIN colors t2 ON t1.color_id = t2.ID LEFT OUTER JOIN bodies ON t1.body_id = bodies.ID WHERE NOT EXISTS(SELECT t1_INNER.id, t1_INNER.title, t2_INNER.name, bodies_INNER.name FROM cars_small t1_INNER LEFT OUTER JOIN colors t2_INNER ON t1_INNER.color_id = t2_INNER.ID LEFT OUTER JOIN bodies bodies_INNER ON t1_INNER.body_id = bodies_INNER.ID WHERE t1_INNER.price <= t1.price AND t1_INNER.mileage <= t1.mileage AND ( t1_INNER.price < t1.price OR t1_INNER.mileage < t1.mileage) ) ORDER BY CASE WHEN bodies.name = 'minivan' THEN 0 ELSE 100 END ASC, CASE WHEN t2.name = 'white' THEN 0 ELSE 100 END ASC ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected, actual, true, "SQL not built correctly");
}
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 TestUdfInOrderClause()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id " +
"FROM cars AS c " +
"SKYLINE OF c.price LOW " +
"ORDER BY c.price ASC, mySchema.myUdf(param1) DESC";
const string expectedQuery = "SELECT c.id FROM cars AS c WHERE NOT EXISTS(SELECT c_INNER.id FROM cars AS c_INNER WHERE c_INNER.price <= c.price AND ( c_INNER.price < c.price) ) ORDER BY c.price ASC, mySchema.myUdf(param1) DESC";
// build query
var engine = new SQLCommon();
var actualQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedQuery, actualQuery);
}
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 TestSortingWithUdfPrecededByCategorial()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id " +
"FROM cars AS c " +
"LEFT OUTER JOIN colors AS cl ON c.color_id = cl.ID " +
"SKYLINE OF c.price LOW " +
"ORDER BY cl.name ('pink' >> {'red','black'} >> 'beige'=='yellow'), mySchema.myUdf(param1) DESC";
const string expectedQuery = "SELECT c.id FROM cars AS c LEFT OUTER JOIN colors AS cl ON c.color_id = cl.ID WHERE NOT EXISTS(SELECT c_INNER.id FROM cars AS c_INNER LEFT OUTER JOIN colors AS cl_INNER ON c_INNER.color_id = cl_INNER.ID WHERE c_INNER.price <= c.price AND ( c_INNER.price < c.price) ) ORDER BY CASE WHEN cl.name = 'pink' THEN 0 WHEN cl.name IN ('red','black') THEN 100 WHEN cl.name = 'beige' THEN 200 WHEN cl.name = 'yellow' THEN 200 END ASC, mySchema.myUdf(param1) DESC";
// build query
var engine = new SQLCommon();
var actualQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedQuery, actualQuery);
}
public void TestIssue48()
{
string prefSQL = "SELECT t1.id, co.name, bo.name FROM cars_small t1 "
+ "LEFT OUTER JOIN colors co ON t1.color_id = co.id "
+ "LEFT OUTER JOIN bodies bo ON t1.body_id = bo.id "
+ "ORDER BY WEIGHTEDSUM (co.Name ('pink' >> 'black' >> OTHERS EQUAL) 0.4 "
+ ", bo.Name ('compact car' >> 'coupé' >> OTHERS EQUAL) 0.6)";
SQLCommon common = new SQLCommon();
common.SkylineType = new SkylineBNLSort();
common.ShowInternalAttributes = true;
string parsedSQL = common.ParsePreferenceSQL(prefSQL);
string expectedBNLSort = "EXEC dbo.prefSQL_Ranking 'SELECT t1.id, co.name, bo.name FROM cars_small t1 LEFT OUTER JOIN colors co ON t1.color_id = co.id LEFT OUTER JOIN bodies bo ON t1.body_id = bo.id ORDER BY WEIGHTEDSUM (co.Name (''pink'' >> ''black'' >> OTHERS EQUAL) 0.4 , bo.Name (''compact car'' >> ''coupé'' >> OTHERS EQUAL) 0.6)', 'SELECT MIN(CASE WHEN co.Name IN (''pink'') THEN 0 WHEN co.Name IN (''black'') THEN 1 ELSE 2 END), MAX(CASE WHEN co.Name IN (''pink'') THEN 0 WHEN co.Name IN (''black'') THEN 1 ELSE 2 END) FROM colors co;SELECT MIN(CASE WHEN bo.Name IN (''compact car'') THEN 0 WHEN bo.Name IN (''coupé'') THEN 1 ELSE 2 END), MAX(CASE WHEN bo.Name IN (''compact car'') THEN 0 WHEN bo.Name IN (''coupé'') THEN 1 ELSE 2 END) FROM bodies bo', 0, '0.4;0.6', 'CASE WHEN co.Name IN (''pink'') THEN 0 WHEN co.Name IN (''black'') THEN 1 ELSE 2 END;CASE WHEN bo.Name IN (''compact car'') THEN 0 WHEN bo.Name IN (''coupé'') THEN 1 ELSE 2 END', True, 'co_Name;bo_Name'";
Assert.AreEqual(parsedSQL, expectedBNLSort, "Query does not match parsed Query");
}
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 TestUdfExpressionsNative()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id, dbo.someUDF(c.price, 1.5) AS SomeUDF1, someSchema.someUDF(c.price, 2.5) AS SomeUDF2 " +
"FROM cars AS c " +
"SKYLINE OF co.Name ('pink' >> 'black' >> OTHERS INCOMPARABLE), c.price LOW " +
"ORDER BY c.price ASC, someSchema.someUDF(c.price, 10) DESC";
const string expectedQuery = "SELECT c.id, dbo.someUDF(c.price, 1.5) AS SomeUDF1, someSchema.someUDF(c.price, 2.5) AS SomeUDF2 FROM cars AS c " +
"WHERE NOT EXISTS(SELECT c_INNER.id, dbo.someUDF(c_INNER.price, 1.5) AS SomeUDF1, someSchema.someUDF(c_INNER.price, 2.5) AS SomeUDF2 FROM cars AS c_INNER WHERE (CASE WHEN co_INNER.Name = 'pink' THEN 0 WHEN co_INNER.Name = 'black' THEN 100 ELSE 201 END <= CASE WHEN co.Name = 'pink' THEN 0 WHEN co.Name = 'black' THEN 100 ELSE 200 END OR co_INNER.Name = co.Name) AND c_INNER.price <= c.price AND ( CASE WHEN co_INNER.Name = 'pink' THEN 0 WHEN co_INNER.Name = 'black' THEN 100 ELSE 201 END < CASE WHEN co.Name = 'pink' THEN 0 WHEN co.Name = 'black' THEN 100 ELSE 200 END OR c_INNER.price < c.price) ) " +
"ORDER BY c.price ASC, someSchema.someUDF(c.price, 10) DESC";
// build query
var engine = new SQLCommon {SkylineType = new SkylineSQL()};
var actualQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedQuery, actualQuery);
}
public void GeneratePerformanceQueries()
{
if (MaxDimensions < MinDimensions)
{
Debug.WriteLine("Max Dimensions must be >= Min Dimensions!");
return;
}
//Open DBConnection --> Otherwise first query is slower as usual, because DBConnection is not open
SQLCommon parser = new SQLCommon();
DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, "SELECT cars.id FROM cars SKYLINE OF cars.price LOW");
//Use the correct line, depending on how incomparable items should be compared
ArrayList listPreferences = new ArrayList();
SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString); //for CLR performance tets
ArrayList preferencesMode = new ArrayList();
if (UseCLR)
{
cnnSQL.Open();
}
switch (Set)
{
case PreferenceSet.ArchiveComparable:
preferencesMode = GetArchiveComparablePreferences();
break;
case PreferenceSet.ArchiveIncomparable:
preferencesMode = GetArchiveIncomparablePreferences();
break;
case PreferenceSet.Jon:
preferencesMode = GetJonsPreferences();
break;
case PreferenceSet.Mya:
preferencesMode = GetMyasPreferences();
break;
case PreferenceSet.Barra:
preferencesMode = GetBarrasPreferences();
break;
case PreferenceSet.All:
preferencesMode = GetAllPreferences();
break;
case PreferenceSet.Numeric:
preferencesMode = GetNumericPreferences();
break;
case PreferenceSet.NumericIncomparable:
preferencesMode = GetNumericIncomparablePreferences();
break;
case PreferenceSet.Categoric:
preferencesMode = GetCategoricalPreferences();
break;
case PreferenceSet.CategoricIncomparable:
preferencesMode = GetCategoricalIncomparablePreferences();
break;
case PreferenceSet.MinCardinality:
preferencesMode = GetMinimalCardinalityPreferences();
break;
case PreferenceSet.LowCardinality:
preferencesMode = GetLowCardinalityPreferences();
break;
case PreferenceSet.HighCardinality:
preferencesMode = GetHighCardinalityPreferences();
break;
case PreferenceSet.LowAndHighCardinality:
preferencesMode = GetLowAndHighCardinalityPreferences();
break;
case PreferenceSet.ForRandom10:
preferencesMode = Get10ForRandomPreferences();
break;
case PreferenceSet.ForRandom17:
preferencesMode = Get17ForRandomPreferences();
break;
}
//Calculate correlationmatrix and cardinality from the preferences
ArrayList correlationMatrix = GetCorrelationMatrix(preferencesMode);
ArrayList listCardinality = GetCardinalityOfPreferences(preferencesMode);
//Depending on the mode create the sets from the preferences
if (Mode == PreferenceChooseMode.Combination)
{
//Tests every possible combination with y preferences from the whole set of preferences
if (MaxDimensions > preferencesMode.Count)
{
Debug.WriteLine("Combination with more dimensions than preferences. Please reduce dimensions!");
return;
}
//create all possible combinations and add it to listPreferences
for (int i = MinDimensions; i <= MaxDimensions; i++)
{
GetCombinations(preferencesMode, i, 0, new ArrayList(), ref listPreferences);
}
}
else if (Mode == PreferenceChooseMode.SameOrder)
{
int draws = MaxDimensions - MinDimensions + 1;
//Tests x times randomly y preferences
for (int iChoose = 0; iChoose < draws; iChoose++)
{
ArrayList preferencesRandom = new ArrayList();
ArrayList preferencesChoose = (ArrayList)preferencesMode.Clone();
//First define define randomly how many dimensions
int sampleDimensions = iChoose + MinDimensions;
//Choose x preferences randomly
for (int i = 0; i < sampleDimensions; i++)
{
preferencesRandom.Add(preferencesChoose[i]);
}
//add random preferences to listPreferences
listPreferences.Add(preferencesRandom);
}
}
else if (Mode == PreferenceChooseMode.Shuffle)
{
//Tests x times randomly y preferences
for (int iChoose = 0; iChoose < _randomDraws; iChoose++)
{
ArrayList preferencesRandom = new ArrayList();
ArrayList preferencesChoose = (ArrayList)preferencesMode.Clone();
//First define define randomly how many dimensions
int differentDimensions = MaxDimensions - MinDimensions + 1;
int sampleDimensions = Rnd.Next(differentDimensions) + MinDimensions;
//Choose x preferences randomly
for (int i = 0; i < sampleDimensions; i++)
{
int r = Rnd.Next(preferencesChoose.Count);
preferencesRandom.Add(preferencesChoose[r]);
preferencesChoose.RemoveAt(r);
}
//add random preferences to listPreferences
listPreferences.Add(preferencesRandom);
}
}
else if (Mode == PreferenceChooseMode.Correlation)
{
if (MaxDimensions > 2)
{
Debug.WriteLine("This test mode only works for 2 dimensions!");
return;
}
//Sort correlations to find the strongest
correlationMatrix.Sort(new CorrelationModel());
//Sort correlations ascending
CorrelationModel model = (CorrelationModel)correlationMatrix[0];
preferencesMode.Clear();
preferencesMode.Add(model.ColA);
preferencesMode.Add(model.ColB);
listPreferences.Add(preferencesMode);
}
else if (Mode == PreferenceChooseMode.AntiCorrelation)
{
if (MaxDimensions > 2)
{
Debug.WriteLine("This test mode only works for 2 dimensions!");
return;
}
//Sort correlations ascending
correlationMatrix.Sort(new CorrelationModel());
//Take only the two preferences with the worst correlation
CorrelationModel model = (CorrelationModel)correlationMatrix[correlationMatrix.Count - 1];
preferencesMode.Clear();
preferencesMode.Add(model.ColA);
preferencesMode.Add(model.ColB);
listPreferences.Add(preferencesMode);
}
else if (Mode == PreferenceChooseMode.Independent)
{
if (MaxDimensions > 2)
{
Debug.WriteLine("This test mode only works for 2 dimensions!");
return;
}
//Sort correlations to find the strongest
correlationMatrix.Sort(new CorrelationModel());
//Find the most independent atributes (closest to zero)
CorrelationModel modelBefore = new CorrelationModel();
CorrelationModel modelAfter = new CorrelationModel();
for (int i = 0; i <= correlationMatrix.Count; i++)
{
CorrelationModel model = (CorrelationModel)correlationMatrix[i];
if (model.Correlation > 0)
{
//continue until the correlation turnaround
modelBefore = model;
}
else
{
modelAfter = model;
//Leave the function, because now the correlation is getting worse
break;
}
}
//Add the two preferences to the list, that are closer to zero
preferencesMode.Clear();
if (Math.Abs(modelBefore.Correlation) > Math.Abs(modelAfter.Correlation))
{
preferencesMode.Add(modelAfter.ColA);
preferencesMode.Add(modelAfter.ColB);
}
else
{
preferencesMode.Add(modelBefore.ColA);
preferencesMode.Add(modelBefore.ColB);
}
listPreferences.Add(preferencesMode);
}
List<SkylineStrategy> listStrategy = new List<SkylineStrategy>();
if (Strategy == null)
{
//If no strategy is defined --> Take all possible algorithms
//listStrategy.Add(new SkylineSQL());
listStrategy.Add(new SkylineBNLSort());
listStrategy.Add(new SkylineBNLSort());
listStrategy.Add(new SkylineDQ());
listStrategy.Add(new SkylineHexagon());
listStrategy.Add(new SkylineDecisionTree());
}
else
{
listStrategy.Add(Strategy);
}
//Generates the R-Commands for the rpref package (for testig exactly the same statements in rpref)
if (WriteRCommand)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("---------------------------------------------");
sb.AppendLine("-----------------R-Commands------------------");
sb.AppendLine("---------------------------------------------");
sb.AppendLine("library(rPref)");
sb.AppendLine("setwd(\"E:\\\\Projekte\\\\prefSQL_Paper\\\\Test Scalagon\")");
sb.AppendLine("mydata = read.csv(\"cars_all.csv\", sep=\";\")");
sb.AppendLine("skylinesize <- array(" + listPreferences.Count + ":1)");
sb.AppendLine("timeins <- array(" + listPreferences.Count + ":1)");
for (int iPreferenceIndex = 0; iPreferenceIndex < listPreferences.Count; iPreferenceIndex++)
{
ArrayList preferences = (ArrayList)listPreferences[iPreferenceIndex];
ArrayList subPreferences = preferences; //.GetRange(0, i);
sb.Append("time = system.time(sky1 <- psel(mydata, ");
foreach(String pref in preferences) {
String rCommand = "";
if(pref.IndexOf("cars.") == -1) {
//Categorical preferences
//String tableName = pref.Substring(0, pref.IndexOf("."));
rCommand = "low(" + pref.Substring(0, pref.IndexOf(" (")).Replace(".name", "") + ")";
/*preferences.Add("colors.name ('red' >> 'blue' >> 'green' >> 'gold' >> 'black' >> 'gray' >> 'bordeaux' >> OTHERS EQUAL)");
preferences.Add("bodies.name ('bus' >> 'cabriolet' >> 'limousine' >> 'coupé' >> 'van' >> 'estate car' >> OTHERS EQUAL)");
preferences.Add("fuels.name ('petrol' >> 'diesel' >> 'bioethanol' >> 'electro' >> 'gas' >> 'hybrid' >> OTHERS EQUAL)");
preferences.Add("makes.name ('BENTLEY' >> 'DAIMLER' >> 'FIAT'>> 'FORD' >> OTHERS EQUAL)");
preferences.Add("conditions.name ('new' >> 'occasion' >> 'demonstration car' >> 'oldtimer' >> OTHERS EQUAL)");
preferences.Add("drives.name ('front wheel' >> 'all wheel' >> 'rear wheel' >> OTHERS EQUAL)");
preferences.Add("transmissions.name ('manual' >> 'automatic' >> OTHERS EQUAL)");*/
}
else
{
//Numeric preference
if (pref.IndexOf("LOW") > 0)
{
//LOW preference
rCommand = "low(" + pref.Substring(0, pref.IndexOf(" ")).Replace("cars.", "") +")";
}
else
{
//HIGH preferences
rCommand = "high(" + pref.Substring(0, pref.IndexOf(" ")).Replace("cars.", "") + ")";
}
}
sb.Append(rCommand);
//Don't add * on last record
if (pref != (string)preferences[preferences.Count-1])
{
sb.Append(" * ");
}
}
sb.AppendLine("))");
sb.AppendLine("timeins[" + (iPreferenceIndex + 1) + "] = time[3]");
sb.AppendLine("skylinesize[" + (iPreferenceIndex + 1) + "] = nrow(sky1)");
sb.AppendLine("skylinesize[" + (iPreferenceIndex + 1) + "]");
sb.AppendLine("timeins[" + (iPreferenceIndex + 1) + "]");
//string strSkylineOf = "SKYLINE OF " + string.Join(",", (string[])subPreferences.ToArray(Type.GetType("System.String")));
//sb.AppendLine(strSkylineOf);
}
sb.AppendLine("mean(skylinesize)");
sb.AppendLine("min(skylinesize)");
sb.AppendLine("max(skylinesize)");
sb.AppendLine("var(skylinesize)");
sb.AppendLine("sd(skylinesize)");
sb.AppendLine("mean(timeins)");
sb.AppendLine("min(timeins)");
sb.AppendLine("max(timeins)");
sb.AppendLine("var(timeins)");
sb.AppendLine("sd(timeins)");
sb.AppendLine("---------------------------------------------");
sb.AppendLine("---------------------------------------------");
sb.AppendLine("---------------------------------------------");
sb.AppendLine("");
sb.AppendLine("");
Debug.Write(sb.ToString());
//create filename
string strFileName = Path + "AllShuffle_RCommands" + DateTime.Now.Ticks + ".txt";
StreamWriter outfile = new StreamWriter(strFileName);
outfile.Write(sb.ToString());
outfile.Close();
}
int iStrategy = 0;
foreach(SkylineStrategy currentStrategy in listStrategy)
{
//Take all strategies
//BNL with attributeposition sorting and bnl with entropy function sorting
//For testing the BNL automatically (shuffle mode), once with attributeposition and once with entropy function
if (iStrategy == 1)
{
WindowSort = SQLCommon.Ordering.AttributePosition;
}
iStrategy++;
StringBuilder sb = new StringBuilder();
string strSeparatorLine;
if (Sampling)
{
strSeparatorLine = PerformanceSampling.GetSeparatorLine(ExcessiveTests);
}
else
{
strSeparatorLine = FormatLineString('-', "", "", "", "", "", "", "", "", "", "", "");
}
if (GenerateScript == false)
{
//Header
sb.AppendLine(" Path: " + Path);
sb.AppendLine(" Algorithm: " + currentStrategy);
sb.AppendLine(" Use CLR: " + UseCLR);
sb.AppendLine(" Preference Set: " + Set.ToString());
sb.AppendLine(" Preference Mode: " + Mode.ToString());
sb.AppendLine(" Host: " + Environment.MachineName);
sb.AppendLine(" Set of Preferences: " + listPreferences.Count);
sb.AppendLine(" Trials: " + Trials);
sb.AppendLine(" Table size: " + TableSize.ToString());
sb.AppendLine(" Dimension from: " + MinDimensions.ToString());
sb.AppendLine(" Dimension to: " + MaxDimensions.ToString());
sb.AppendLine(" BNL Initial Sort: " + WindowSort.ToString());
sb.AppendLine(" BNL Window Handling: " + WindowHandling.ToString());
//sb.AppendLine("Correlation Coefficients:" + string.Join(",", (string[])preferences.ToArray(Type.GetType("System.String"))));
//sb.AppendLine(" Cardinalities:" + string.Join(",", (string[])preferences.ToArray(Type.GetType("System.String"))));
if (Sampling)
{
sb.AppendLine(" Sampling: true");
sb.AppendLine(" Subsets Count: " + SamplingSubsetsCount);
sb.AppendLine(" Subset Dimension: " + SamplingSubsetDimension);
sb.AppendLine(" Sampling Runs: " + SamplingSamplesCount);
}
sb.AppendLine("");
if (Sampling)
{
sb.AppendLine(PerformanceSampling.GetHeaderLine());
}
else
{
sb.AppendLine(FormatLineString(' ', "preference set", "trial", "dimensions", "skyline size", "time total", "time algorithm", "min correlation", "max correlation", "product cardinality", "number of moves", "number of comparisons"));
}
sb.AppendLine(strSeparatorLine);
Debug.Write(sb);
}
List<long> reportDimensions = new List<long>();
List<long> reportSkylineSize = new List<long>();
List<long> reportTimeTotal = new List<long>();
List<long> reportTimeAlgorithm = new List<long>();
List<long> reportNumberOfMoves = new List<long>();
List<long> reportNumberOfComparisons = new List<long>();
List<double> reportMinCorrelation = new List<double>();
List<double> reportMaxCorrelation = new List<double>();
List<double> reportCardinality = new List<double>();
var perfSampling = new PerformanceSampling(SamplingSubsetsCount, SamplingSubsetDimension, SamplingSamplesCount, ExcessiveTests);
//For each preference set in the preference list
for (int iPreferenceIndex = 0; iPreferenceIndex < listPreferences.Count; iPreferenceIndex++)
{
ArrayList preferences = (ArrayList)listPreferences[iPreferenceIndex];
//Go only down two 3 dimension (because there are special algorithms for 1 and 2 dimensional skyline)
//for (int i = MinDimensions; i <= preferences.Count; i++)
//{
//ADD Preferences to SKYLINE
ArrayList subPreferences = preferences; //.GetRange(0, i);
string strSkylineOf = "SKYLINE OF " + string.Join(",", (string[])subPreferences.ToArray(Type.GetType("System.String")));
//SELECT FROM
string strSQL = "SELECT cars.id FROM ";
if (TableSize == Size.Small)
{
strSQL += "cars_small";
}
else if (TableSize == Size.Medium)
{
strSQL += "cars_medium";
}
else if (TableSize == Size.Large)
{
strSQL += "cars_large";
}
if (!UseNormalizedValues)
{
strSQL += " cars ";
}
else
{
strSQL += "cars_normalized cars ";
}
//Add Joins
strSQL += GetJoinsForPreferences(strSkylineOf);
//Add Skyline-Clause
strSQL += strSkylineOf;
//Convert to real SQL
parser = new SQLCommon();
parser.SkylineType = currentStrategy;
parser.ShowInternalAttributes = true;
parser.WindowHandling = WindowHandling;
parser.WindowSort = WindowSort;
if (GenerateScript == false)
{
for (int iTrial = 0; iTrial < Trials; iTrial++)
{
Stopwatch sw = new Stopwatch();
try
{
double minCorrelation = 0;
double maxCorrelation = 0;
SearchCorrelation(subPreferences, correlationMatrix, ref minCorrelation, ref maxCorrelation);
double cardinality = SearchCardinality(subPreferences, listCardinality);
long timeAlgorithm = 0;
long skylineSize = 0;
long numberOfMoves = 0;
long numberOfComparisons = 0;
if (Sampling)
{
string strTrial = iTrial + 1 + " / " + _trials;
string strPreferenceSet = iPreferenceIndex + 1 + " / " + listPreferences.Count;
Console.WriteLine(strPreferenceSet);
string strLine = perfSampling.MeasurePerformance(iTrial, iPreferenceIndex, listPreferences, preferences, parser, sw, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, minCorrelation, maxCorrelation, reportCardinality, cardinality, strSQL, strPreferenceSet, strTrial, reportNumberOfMoves, numberOfMoves, reportNumberOfComparisons, numberOfComparisons);
Debug.WriteLine(strLine);
sb.AppendLine(strLine);
}
else
{
sw.Start();
ArrayList clauseID = new ArrayList();
String strIDs = "";
for (int skylineLevel = 1; skylineLevel <= SkylineUpToLevel; skylineLevel++)
{
if (UseCLR)
{
string strSP = parser.ParsePreferenceSQL(strSQL);
SqlDataAdapter dap = new SqlDataAdapter(strSP, cnnSQL);
dt.Clear(); //clear datatable
dap.Fill(dt);
}
else
{
parser.Cardinality = (long)cardinality;
string strSQLWithWHERE = strSQL;
strSQLWithWHERE = strSQL.Substring(0, strSQL.IndexOf("SKYLINE OF"));
strSQLWithWHERE += strIDs;
strSQLWithWHERE += strSQL.Substring(strSQL.IndexOf("SKYLINE OF"));
dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strSQLWithWHERE);
}
timeAlgorithm += parser.TimeInMilliseconds;
numberOfMoves += parser.NumberOfMoves;
numberOfComparisons += parser.NumberOfComparisons;
skylineSize += dt.Rows.Count;
//only if more queries are requested
if (skylineLevel < SkylineUpToLevel && currentStrategy.GetType() != typeof(prefSQL.SQLSkyline.MultipleSkylineBNL))
{
//Add ids to WHERE clause
foreach (DataRow row in dt.Rows)
{
clauseID.Add((int)row[0]);
}
//Add WHERE clause with IDs that were already in the skyline
strIDs = "";
foreach (int id in clauseID)
{
strIDs += id + ",";
}
if (strIDs.Length > 0)
{
strIDs = "WHERE cars.id NOT IN (" + strIDs.TrimEnd(',') + ") ";
}
}
else
{
skylineLevel = SkylineUpToLevel;
}
}
sw.Stop();
reportDimensions.Add(preferences.Count);
reportSkylineSize.Add(skylineSize);
reportTimeTotal.Add(sw.ElapsedMilliseconds);
reportTimeAlgorithm.Add(timeAlgorithm);
reportNumberOfMoves.Add(numberOfMoves);
reportNumberOfComparisons.Add(numberOfComparisons);
reportMinCorrelation.Add(minCorrelation);
reportMaxCorrelation.Add(maxCorrelation);
reportCardinality.Add(cardinality);
//trial|dimensions|skyline size|time total|time algorithm
string strTrial = iTrial + 1 + " / " + _trials;
string strPreferenceSet = iPreferenceIndex + 1 + " / " + listPreferences.Count;
Console.WriteLine(strPreferenceSet);
string strLine = "";
//Was there an error?
if (dt.Rows.Count == 0)
{
strLine = FormatLineString("Error! " + strPreferenceSet, strTrial, preferences.Count, dt.Rows.Count, sw.ElapsedMilliseconds, timeAlgorithm, minCorrelation, maxCorrelation, cardinality, numberOfMoves, numberOfComparisons);
}
else
{
strLine = FormatLineString(strPreferenceSet, strTrial, preferences.Count, dt.Rows.Count, sw.ElapsedMilliseconds, timeAlgorithm, minCorrelation, maxCorrelation, cardinality, numberOfMoves, numberOfComparisons);
}
Debug.WriteLine(strLine);
sb.AppendLine(strLine);
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return;
}
}
}
else
{
strSQL = parser.ParsePreferenceSQL(strSQL);
string[] sizes = { "small", "medium", "large", "superlarge" };
//Format for each of the customer profiles
sb.AppendLine("PRINT '----- -------------------------------------------------------- ------'");
sb.AppendLine("PRINT '----- " + (preferences.Count + 1) + " dimensions ------'");
sb.AppendLine("PRINT '----- -------------------------------------------------------- ------'");
foreach (string size in sizes)
{
sb.AppendLine("GO"); //we need this in order the profiler shows each query in a new line
sb.AppendLine(strSQL.Replace("cars", "cars_" + size));
}
}
//}
}
////////////////////////////////
//Summary
///////////////////////////////
if (GenerateScript == false)
{
if (Sampling)
{
perfSampling.AddSummary(sb, strSeparatorLine, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, reportCardinality, reportNumberOfMoves, reportNumberOfComparisons);
perfSampling.AddPreferenceSetInformation(sb, listPreferences, strSeparatorLine);
}
else
{
AddSummary(sb, strSeparatorLine, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, reportCardinality, reportNumberOfMoves, reportNumberOfComparisons);
}
}
//Write some empty lines (clarification in output window)
Debug.WriteLine("");
Debug.WriteLine("");
Debug.WriteLine("");
//Write in file
string strFiletype;
if (GenerateScript == false)
{
strFiletype = ".txt";
}
else
{
strFiletype = ".sql";
}
//create filename
string strFileName = Path + Set.ToString() + "_" + Mode.ToString() + "_" + MinDimensions + "_" + MaxDimensions + "_" + currentStrategy + DateTime.Now.Ticks + strFiletype;
StreamWriter outfile = new StreamWriter(strFileName);
outfile.Write(sb.ToString());
outfile.Close();
}
//close connection
if (UseCLR)
{
cnnSQL.Close();
}
}
private DataTable GetSQLFromPreferences(ArrayList preferences, bool cardinality)
{
SQLCommon common = new SQLCommon();
string strPrefSQL = "SELECT cars.id FROM ";
if (TableSize == Size.Small)
{
strPrefSQL += "cars_small";
}
else if (TableSize == Size.Medium)
{
strPrefSQL += "cars_medium";
}
else if (TableSize == Size.Large)
{
strPrefSQL += "cars_large";
}
strPrefSQL += " cars ";
strPrefSQL += "SKYLINE OF ";
for (int i = 0; i < preferences.Count; i++)
{
strPrefSQL += preferences[i] + ",";
}
strPrefSQL = strPrefSQL.TrimEnd(',');
PrefSQLModel prefModel = common.GetPrefSqlModelFromPreferenceSql(strPrefSQL);
string strSQL = "SELECT ";
for (int i = 0; i < prefModel.Skyline.Count; i++)
{
if (cardinality)
{
strSQL += "COUNT(DISTINCT " + prefModel.Skyline[i].Expression + "),";
}
else
{
strSQL += prefModel.Skyline[i].Expression + ",";
}
}
strSQL = strSQL.TrimEnd(',') + " FROM cars ";
strSQL += GetJoinsForPreferences(strSQL);
DataTable dt = Helper.ExecuteStatement(strSQL);
return dt;
}
public void TestUdfExpressionsClr()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id, dbo.someUDF(c.price, 1.5) AS SomeUDF1, someSchema.someUDF(c.price, 2.5) AS SomeUDF2 " +
"FROM cars AS c " +
"SKYLINE OF co.Name ('pink' >> 'black' >> OTHERS INCOMPARABLE), c.price LOW " +
"ORDER BY c.price ASC, someSchema.someUDF(c.price, 10) DESC";
const string expectedQuery = "EXEC dbo.prefSQL_SkylineBNL " +
"'SELECT CAST(CASE WHEN co.Name = ''pink'' THEN 0 WHEN co.Name = ''black'' THEN 100 ELSE 200 END AS bigint) AS SkylineAttribute0, CASE WHEN co.Name = ''pink'' THEN '''' WHEN co.Name = ''black'' THEN '''' ELSE co.Name END, CAST(c.price AS bigint) AS SkylineAttribute1 , c.id, dbo.someUDF(c.price, 1.5) AS SomeUDF1, someSchema.someUDF(c.price, 2.5) AS SomeUDF2 " +
"FROM cars AS c " +
"ORDER BY c.price ASC, someSchema.someUDF(c.price, 10) DESC', " +
"'LOW;INCOMPARABLE;LOW', 0, 4";
// build query
var engine = new SQLCommon { SkylineType = new SkylineBNL() };
var actualQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedQuery, actualQuery);
}
public void TestSkylineAmountOfTupelsMultipleLevelsMSSQLCLR()
{
string strSQL = "SELECT t1.id, t1.price, t1.mileage FROM cars_small t1 ";
string strPreferences = " SKYLINE OF t1.price LOW, t1.mileage LOW";
SQLCommon common = new SQLCommon();
SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString);
cnnSQL.InfoMessage += cnnSQL_InfoMessage;
try
{
cnnSQL.Open();
//Tree Algorithm
common.SkylineType = new MultipleSkylineBNL();
common.SkylineUpToLevel = 3;
string sqlTree = common.ParsePreferenceSQL(strSQL + strPreferences);
ArrayList levelRecordsTree = new ArrayList();
DbCommand command = cnnSQL.CreateCommand();
command.CommandTimeout = 0; //infinite timeout
command.CommandText = sqlTree;
DbDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int level = (int)dataReader["level"];
if (levelRecordsTree.Count > level)
{
levelRecordsTree[level] = (int)levelRecordsTree[level] + 1;
}
else
{
levelRecordsTree.Add(1);
}
}
}
dataReader.Close();
//BNL Algorithm (multiple times)
//As long as Query returns skyline tuples
common.SkylineType = new SkylineBNLSort();
List<int> listIDs = new List<int>();
bool isSkylineEmpty = false;
ArrayList levelRecordsBNLSort = new ArrayList();
int iLevel = 0;
while (isSkylineEmpty == false && iLevel < common.SkylineUpToLevel)
{
//Add WHERE clause with IDs that were already in the skyline
String strIDs = "";
foreach (int id in listIDs)
{
strIDs += id + ",";
}
if (strIDs.Length > 0)
{
strIDs = "WHERE t1.id NOT IN (" + strIDs.TrimEnd(',') + ")";
}
//Parse PreferenceSQL into SQL
string sqlBNLSort = common.ParsePreferenceSQL(strSQL + strIDs + strPreferences);
command = cnnSQL.CreateCommand();
command.CommandTimeout = 0; //infinite timeout
command.CommandText = sqlBNLSort;
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
listIDs.Add(Int32.Parse(dataReader["id"].ToString()));
//int level = (int)sqlReader["level"];
if (levelRecordsBNLSort.Count > iLevel)
{
levelRecordsBNLSort[iLevel] = (int)levelRecordsBNLSort[iLevel] + 1;
}
else
{
levelRecordsBNLSort.Add(1);
}
}
dataReader.Close();
}
else
{
isSkylineEmpty = true;
}
//Next level
iLevel++;
}
cnnSQL.Close();
//Compare the two arrays
if(levelRecordsTree.Count == levelRecordsBNLSort.Count)
{
for (int i = 0; i < levelRecordsTree.Count; i++ )
{
if ((int)levelRecordsBNLSort[i] != (int)levelRecordsTree[i])
{
Assert.Fail("Level " + i + " has another amount of records");
}
}
}
else
{
Assert.Fail("Arrays don't have the same dimension");
}
}
catch (Exception ex)
{
Assert.Fail("Connection failed:" + ex.Message);
}
}
public void TestUdfWithStringParam()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id, dbo.myUdf('actParam') AS udf1 " +
"FROM cars AS c " +
"LEFT OUTER JOIN colors AS cl ON c.color_id = cl.ID " +
"SKYLINE OF c.price LOW";
const string expectedQuery = "SELECT c.id, dbo.myUdf('actParam') AS udf1 FROM cars AS c LEFT OUTER JOIN colors AS cl ON c.color_id = cl.ID WHERE NOT EXISTS(SELECT c_INNER.id, dbo.myUdf('actParam') AS udf1 FROM cars AS c_INNER LEFT OUTER JOIN colors AS cl_INNER ON c_INNER.color_id = cl_INNER.ID WHERE c_INNER.price <= c.price AND ( c_INNER.price < c.price) )";
// build query
var engine = new SQLCommon();
var actualQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedQuery, actualQuery);
}
public void TestUdfPreferenceMixedParam()
{
// prefSQL with UDF
const string prefQuery = "SELECT c.id " +
"FROM cars AS c " +
"SKYLINE OF mySchema.myUdf(c.price, 0.77, 'fixedValue') LOW " +
//"SKYLINE OF c.price LOW " +
"ORDER BY c.price ASC";
const string expectedNativeQuery = "SELECT c.id FROM cars AS c WHERE NOT EXISTS(SELECT c_INNER.id FROM cars AS c_INNER WHERE mySchema.myUdf(c_INNER.price, 0.77, 'fixedValue') <= mySchema.myUdf(c.price, 0.77, 'fixedValue') AND ( mySchema.myUdf(c_INNER.price, 0.77, 'fixedValue') < mySchema.myUdf(c.price, 0.77, 'fixedValue')) ) ORDER BY c.price ASC";
const string expectedClrQuery = "EXEC dbo.prefSQL_SkylineBNLLevel 'SELECT CAST(mySchema.myUdf(c.price, 0.77, ''fixedValue'') AS bigint) AS SkylineAttribute0 , c.id FROM cars AS c ORDER BY c.price ASC', 'LOW', 0, 4";
// build query
var engine = new SQLCommon();
var actualNativeQuery = engine.ParsePreferenceSQL(prefQuery);
engine.SkylineType = new SkylineBNL();
var actualClrQuery = engine.ParsePreferenceSQL(prefQuery);
// verify outcome
Assert.AreEqual(expectedNativeQuery, actualNativeQuery);
Assert.AreEqual(expectedClrQuery, actualClrQuery);
}
public void TestParserSkyline2DimensionsNoJoin()
{
string strPrefSQL = "SELECT cars.id, cars.price, cars.title FROM cars SKYLINE OF cars.title ('MERCEDES-BENZ SL 600' >> OTHERS EQUAL), cars.price LOW";
string expected = "SELECT cars.id, cars.price, cars.title FROM cars WHERE NOT EXISTS(SELECT cars_INNER.id, cars_INNER.price, cars_INNER.title FROM cars cars_INNER WHERE (CASE WHEN cars_INNER.title = 'MERCEDES-BENZ SL 600' THEN 0 ELSE 100 END <= CASE WHEN cars.title = 'MERCEDES-BENZ SL 600' THEN 0 ELSE 100 END OR cars_INNER.title = cars.title) AND cars_INNER.price <= cars.price AND ( CASE WHEN cars_INNER.title = 'MERCEDES-BENZ SL 600' THEN 0 ELSE 100 END < CASE WHEN cars.title = 'MERCEDES-BENZ SL 600' THEN 0 ELSE 100 END OR cars_INNER.price < cars.price) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}