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 TestParserCaseSensitivity()
{
var prefSqlQuery = "SELECT * FROM cars SKYLINE OF cars.price AROUND 15000, cars.mileage LOW";
var expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE ABS(cars_INNER.price - 15000) <= ABS(cars.price - 15000) AND cars_INNER.mileage <= cars.mileage AND ( ABS(cars_INNER.price - 15000) < ABS(cars.price - 15000) OR cars_INNER.mileage < cars.mileage) )";
var common = new SQLCommon();
var actualNoCaseChange = common.ParsePreferenceSQL(prefSqlQuery);
var actualUpperCase = common.ParsePreferenceSQL(prefSqlQuery.ToUpper());
var actualLowerCase = common.ParsePreferenceSQL(prefSqlQuery.ToLower());
Assert.AreEqual(expected.Trim(), actualNoCaseChange.Trim(), true);
Assert.AreEqual(expected.Trim(), actualUpperCase.Trim(), true);
Assert.AreEqual(expected.Trim(), actualLowerCase.Trim(), true);
}
public void TestSamplingSyntaxValidityOfSyntacticallyIncorrectSqlStatements()
{
var hasExceptionBeenRaised = false;
var skylineSampleSQL = TestContext.DataRow["skylineSampleSQL"].ToString();
Debug.WriteLine(skylineSampleSQL);
var common = new SQLCommon {
SkylineType = new SkylineSQL()
};
try
{
common.ParsePreferenceSQL(skylineSampleSQL);
}
catch (Exception)
{
hasExceptionBeenRaised = true;
}
if (!hasExceptionBeenRaised)
{
Assert.Fail("Syntactically incorrect SQL Query should have thrown an Exception.");
}
}
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 TestParserSkylineQueries()
{
string skylineSampleSql = TestContext.DataRow["skylineSQL"].ToString();
Dictionary <string, SkylineStrategy> allResultTypes = new Dictionary <string, SkylineStrategy>
{
{ "parsePreferenceSQLSkylineSQLExpectedResult", new SkylineSQL() },
{ "parsePreferenceSQLSkylineBNLExpectedResult", new SkylineBNL() },
{ "parsePreferenceSQLSkylineBNLSortExpectedResult", new SkylineBNLSort() },
{ "parsePreferenceSQLSkylineHexagonExpectedResult", new SkylineHexagon() },
{ "parsePreferenceSQLSkylineDQExpectedResult", new SkylineDQ() },
{ "parsePreferenceSQLMultipleSkylineBNLExpectedResult", new MultipleSkylineBNL() }
};
int currentDataRowIndex = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow);
SQLCommon common = new SQLCommon();
foreach (KeyValuePair <string, SkylineStrategy> resultType in allResultTypes)
{
common.SkylineType = resultType.Value;
string parsedSql = common.ParsePreferenceSQL(skylineSampleSql);
Assert.AreEqual(TestContext.DataRow[resultType.Key].ToString().Trim(), parsedSql.Trim(),
"Parsed result in data row " + currentDataRowIndex + " for " + resultType.Key + " is incorrect.");
}
}
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 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 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 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 TestParserDisfavour()
{
string strPrefSQL = "SELECT * FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name DISFAVOUR '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 * -1 <= CASE WHEN colors.name = 'red' THEN 1 ELSE 2 END * -1 AND ( CASE WHEN colors_INNER.name = 'red' THEN 1 ELSE 2 END * -1 < CASE WHEN colors.name = 'red' THEN 1 ELSE 2 END * -1) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserWithoutPreference()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.Price FROM cars";
string expected = "SELECT cars.id, cars.title, cars.Price FROM cars";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
Assert.AreEqual(expected, actual, true, "SQL not built correctly");
}
public void TestParserSkylinearound()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price AROUND 15000, cars.mileage LOW";
string expected = "SELECT * FROM cars WHERE NOT EXISTS(SELECT * FROM cars cars_INNER WHERE ABS(cars_INNER.price - 15000) <= ABS(cars.price - 15000) AND cars_INNER.mileage <= cars.mileage AND ( ABS(cars_INNER.price - 15000) < ABS(cars.price - 15000) OR 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 TestParserSkylinetopKeyword()
{
string strPrefSQL = "SELECT TOP 5 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')";
string expected = "SELECT TOP 5 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) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
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");
}
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 TestParserSkyline3Dimensions()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW, cars.horsepower HIGH ORDER BY price ASC, mileage ASC, horsepower DESC";
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 price ASC, mileage ASC, horsepower DESC";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkylineWithUnconventialJoin()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.price, colors.name FROM cars, colors WHERE cars.Color_Id = colors.Id SKYLINE OF cars.price LOW, colors.name ('gray' >> 'red')";
string expected = "SELECT cars.id, cars.title, cars.price, colors.name FROM cars, colors WHERE cars.Color_Id = colors.Id AND NOT EXISTS(SELECT cars_INNER.id, cars_INNER.title, cars_INNER.price, colors_INNER.name FROM cars cars_INNER, colors colors_INNER WHERE cars_INNER.Color_Id = colors_INNER.Id AND cars_INNER.price <= cars.price AND (CASE WHEN colors_INNER.name = 'gray' THEN 0 WHEN colors_INNER.name = 'red' THEN 100 END <= CASE WHEN colors.name = 'gray' THEN 0 WHEN colors.name = 'red' THEN 100 END OR colors_INNER.name = colors.name) AND ( cars_INNER.price < cars.price OR CASE WHEN colors_INNER.name = 'gray' THEN 0 WHEN colors_INNER.name = 'red' THEN 100 END < CASE WHEN colors.name = 'gray' THEN 0 WHEN colors.name = 'red' THEN 100 END) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkylineFavourRot()
{
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 FAVOUR 'red'";
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 = 'red' THEN 1 ELSE 2 END <= CASE WHEN colors.name = 'red' THEN 1 ELSE 2 END AND ( cars_INNER.price < cars.price OR 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
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkylineLowWithLevel()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.Price FROM cars SKYLINE OF cars.price LOW 1000 EQUAL, cars.mileage LOW";
string expected = "SELECT cars.id, cars.title, cars.Price FROM cars WHERE NOT EXISTS(SELECT cars_INNER.id, cars_INNER.title, cars_INNER.Price FROM cars cars_INNER WHERE cars_INNER.price / 1000 <= cars.price / 1000 AND cars_INNER.mileage <= cars.mileage AND ( cars_INNER.price / 1000 < cars.price / 1000 OR cars_INNER.mileage < cars.mileage) ) ";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkylineShowInternalAttributes()
{
string strPrefSQL = "SELECT * FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW, cars.horsepower HIGH";
string expected = "SELECT * , CAST(cars.price AS bigint) AS SkylineAttributecars_price, CAST(cars.mileage AS bigint) AS SkylineAttributecars_mileage, CAST(cars.horsepower * -1 AS bigint) AS SkylineAttributecars_horsepower FROM cars WHERE NOT EXISTS(SELECT * , CAST(cars_INNER.price AS bigint) AS SkylineAttributecars_price, CAST(cars_INNER.mileage AS bigint) AS SkylineAttributecars_mileage, CAST(cars_INNER.horsepower * -1 AS bigint) AS SkylineAttributecars_horsepower 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) ) ";
SQLCommon common = new SQLCommon();
common.ShowInternalAttributes = true;
string actual = common.ParsePreferenceSQL(strPrefSQL);
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 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 TestParserSkyline2DimensionswithAlias()
{
string strPrefSQL = "SELECT * FROM cars t1 SKYLINE OF t1.price LOW, t1.mileage LOW";
string expected = "SELECT * FROM cars t1 WHERE NOT EXISTS(SELECT * FROM cars t1_INNER WHERE t1_INNER.price <= t1.price AND t1_INNER.mileage <= t1.mileage AND ( t1_INNER.price < t1.price OR t1_INNER.mileage < t1.mileage) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkylineWithWhereClause()
{
string strPrefSQL = "SELECT cars.id, cars.title, cars.price, cars.mileage FROM cars WHERE cars.price > 10000 SKYLINE OF cars.price LOW, cars.mileage low";
string expected = "SELECT cars.id, cars.title, cars.price, cars.mileage FROM cars WHERE cars.price > 10000 AND NOT EXISTS(SELECT cars_INNER.id, cars_INNER.title, cars_INNER.price, cars_INNER.mileage FROM cars cars_INNER WHERE cars_INNER.price > 10000 AND cars_INNER.price <= cars.price AND cars_INNER.mileage <= cars.mileage AND ( cars_INNER.price < cars.price OR cars_INNER.mileage < cars.mileage) )";
SQLCommon common = new SQLCommon();
string actual = common.ParsePreferenceSQL(strPrefSQL);
// assert
Assert.AreEqual(expected.Trim(), actual.Trim(), true, "SQL not built correctly");
}
public void TestParserSkyline2DimensionsWithJoin()
{
string strPrefSQL = "SELECT cars.id, cars.price, cars.title, colors.name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name ('red' >> OTHERS EQUAL), cars.price LOW";
string expected = "SELECT cars.id, cars.price, cars.title, colors.name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID WHERE NOT EXISTS(SELECT cars_INNER.id, cars_INNER.price, cars_INNER.title, colors_INNER.name 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 ELSE 100 END <= CASE WHEN colors.name = 'red' THEN 0 ELSE 100 END OR colors_INNER.name = colors.name) AND cars_INNER.price <= cars.price AND ( CASE WHEN colors_INNER.name = 'red' THEN 0 ELSE 100 END < CASE WHEN colors.name = 'red' 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");
}
public void TestParserKeywordsInIdentifier()
{
var prefSqlQuery = "SELECT TOP 5 Id, FieldA AS IDFROM, FieldB AS IDWHERE, FieldC AS IDTOP " +
"FROM table " +
"SKYLINE OF id LOW";
var expected = "SELECT TOP 5 Id, FieldA AS IDFROM, FieldB AS IDWHERE, FieldC AS IDTOP " +
"FROM table " +
"WHERE NOT EXISTS(SELECT Id, FieldA AS IDFROM, FieldB AS IDWHERE, FieldC AS IDTOP FROM table table_INNER WHERE _INNER.id <= .id AND ( _INNER.id < .id) )";
var common = new SQLCommon();
var actual = common.ParsePreferenceSQL(prefSqlQuery);
Assert.AreEqual(expected.Trim(), actual.Trim(), true);
}
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 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");
}