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