/// <summary> /// 分页查询新闻信息 /// </summary> /// <param name="stuName"></param> /// <param name="pageSize"></param> /// <param name="currentCount"></param> /// <param name="TotalCount"></param> /// <returns></returns> public List <News> GetNews(string CategoryId, int pageSize, int currentCount, out int TotalCount) { string order = string.Format("PublishTime DESC"); string TableName = string.Format("News"); string Where = "1=1"; if (!string.IsNullOrEmpty(CategoryId)) { Where += string.Format(" And CategoryId='{0}'", CategoryId); } DataSet ds = SQLCommon.GetList(pageSize, order, currentCount, TableName, Where, out TotalCount); List <News> result = new List <News>(); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { result.Add(new News() { CategoryId = Convert.ToInt32(dr["CategoryId"]), NewsContents = dr["NewsContents"].ToString(), NewsId = Convert.ToInt32(dr["NewsId"]), NewsTitle = dr["NewsTitle"].ToString(), PublishTime = Convert.ToDateTime(dr["PublishTime"].ToString()) }); } } return(result); }
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 TestSamplingObjectsWithinEntireSkylineCount() { var entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString(); var testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); var common = new SQLCommon { SkylineType = new SkylineBNL() }; var prefSqlModelEntireSkyline = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL); var subjectUnderTest = new SQLParser.Helper { ConnectionString = Helper.ConnectionString, DriverString = Helper.ProviderName }; var entireSkyline = subjectUnderTest.GetResults( common.GetAnsiSqlFromPrefSqlModel(prefSqlModelEntireSkyline), common.SkylineType, prefSqlModelEntireSkyline, false); var expected = TestContext.DataRow["entireCount"].ToString(); var actual = entireSkyline.Rows.Count.ToString(CultureInfo.InvariantCulture); Assert.AreEqual(expected, actual, "Entire Skyline contains unexpected number of objects."); }
public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkyline() { string skylineSampleSql = TestContext.DataRow["skylineSampleSQL"].ToString(); string entireSkylineSql = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); Debug.WriteLine(skylineSampleSql); string baseQuery; string operators; int numberOfRecords; string[] parameter; var common = new SQLCommon { SkylineType = new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators, out numberOfRecords); IEnumerable <CLRSafeHashSet <int> > useSubsets = UseSubsets(prefSqlModelSkylineSample); var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount, SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSql); DataTable sampleSkyline = skylineSample.GetSkylineTable(baseQuery, operators); HashSet <int> entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline); HashSet <int> sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline); sampleSkylineObjectsIds.ExceptWith(entireSkylineObjectsIds); Debug.WriteLine("wrong objects:"); foreach (int i in sampleSkylineObjectsIds) { Debug.WriteLine(i); } Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds), "Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline)."); }
public void TestSQLSelectSubquery() { string sql = "select t.id FROM (SELECT * FROM cars) t"; SQLCommon common = new SQLCommon(); DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql); Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data"); }
public void TestSQLSelectWithCoalesce() { string sql = "select COALESCE(cars.id, 0) FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW"; SQLCommon common = new SQLCommon(); DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql); Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data"); }
public void TestSQLSkylineWithDisfavour() { string sql = "SELECT cars.title, colors.name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name DISFAVOUR 'red', cars.price LOW"; SQLCommon common = new SQLCommon(); DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql); Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data"); }
private void btnExecute_Click(object sender, EventArgs e) { Stopwatch sw = new Stopwatch(); sw.Start(); btnExecute.Enabled = false; SQLCommon parser = new SQLCommon(); if (optSQL.Checked) { parser.SkylineType = new SkylineSQL(); } else if (optBNL.Checked) { parser.SkylineType = new SkylineBNLSort(); } else if (optHexagon.Checked) { parser.SkylineType = new SkylineHexagon(); } else if (optDQ.Checked) { parser.SkylineType = new SkylineDQ(); } if (chkShowSkyline.Checked) { parser.ShowInternalAttributes = true; } else { parser.ShowInternalAttributes = false; } DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, txtPrefSQL.Text); BindingSource sBind = new BindingSource(); sBind.DataSource = dt; gridSkyline.AutoGenerateColumns = true; gridSkyline.DataSource = dt; gridSkyline.DataSource = sBind; gridSkyline.Refresh(); sw.Stop(); txtTime.Text = sw.ElapsedMilliseconds.ToString(); txtTimeAlgo.Text = parser.TimeInMilliseconds.ToString(); txtRecords.Text = dt.Rows.Count.ToString(); btnExecute.Enabled = true; }
public void TestSQLSelectSubqueryWithGroupBy() { string sql = "SELECT t.color_id, Amount FROM (SELECT cars.color_id, COUNT(*) AS Amount FROM cars GROUP BY cars.color_id) t"; SQLCommon common = new SQLCommon(); DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql); Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data"); }
public void 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 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 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 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"); }
private static void ExecuteSampleSkylines(IReadOnlyCollection <IEnumerable <CLRSafeHashSet <int> > > producedSubsets, PrefSQLModel prefSqlModel, SQLCommon common) { var objectsCount = 0; var timeSpent = 0L; string strQuery; string operators; int numberOfRecords; string[] parameter; string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModel); Debug.Write(ansiSql); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out strQuery, out operators, out numberOfRecords); var sw = new Stopwatch(); foreach (IEnumerable <CLRSafeHashSet <int> > subset in producedSubsets) { var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(subset); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModel.SkylineSampleCount, SubsetDimension = prefSqlModel.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; sw.Restart(); DataTable dataTable = skylineSample.GetSkylineTable(strQuery, operators); sw.Stop(); objectsCount += dataTable.Rows.Count; timeSpent += skylineSample.TimeMilliseconds; foreach (CLRSafeHashSet <int> attribute in subset) { Console.Write("["); foreach (int attribute1 in attribute) { Console.Write(attribute1 + ","); } Console.Write("],"); } Console.WriteLine(); Console.WriteLine("alg time : " + skylineSample.TimeMilliseconds); Console.WriteLine("full time : " + sw.ElapsedMilliseconds); Console.WriteLine("objects : " + dataTable.Rows.Count); } Console.WriteLine("time average: " + (double)timeSpent / producedSubsets.Count); Console.WriteLine("objects average: " + (double)objectsCount / producedSubsets.Count); }
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 string ParsePreferenceQuery(string sql) { // only parse if preferences are defined (standart sql is currently not supported by library) if (!sql.Contains(" PREFERENCE ")) { return(sql); } var common = new SQLCommon(); return(common.parsePreferenceSQL(sql)); }
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 TestSkylineAmountOfTupelsDataTable() { string skylineSampleSql = TestContext.DataRow["skylineSQL"].ToString(); SQLCommon common = new SQLCommon(); common.SkylineType = new SkylineSQL(); PrefSQLModel model = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); DataTable dtNative = common.ExecuteFromPrefSqlModel(Helper.ConnectionString, Helper.ProviderName, model); common.SkylineType = new SkylineBNL(); DataTable dtBNL = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); common.SkylineType = new SkylineBNLSort(); DataTable dtBNLSort = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); DataTable dtHexagon = new DataTable(); if (model.ContainsOpenPreference == false) { common.SkylineType = new SkylineHexagon(); dtHexagon = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); } DataTable dtDQ = new DataTable(); //D&Q does not work with incomparable tuples if (model.WithIncomparable == false) { common.SkylineType = new SkylineDQ(); dtDQ = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql); } int currentDataRowIndex = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //Check tuples (every algorithm should deliver the same amount of tuples) Assert.AreEqual(dtNative.Rows.Count, dtBNL.Rows.Count, 0, "BNL Amount of tupels in query " + currentDataRowIndex + " do not match"); Assert.AreEqual(dtNative.Rows.Count, dtBNLSort.Rows.Count, 0, "BNLSort Amount of tupels in query " + currentDataRowIndex + " do not match"); //Hexagon cannot handle Categorical preference that have no explicit OTHERS if (model.ContainsOpenPreference == false) { Assert.AreEqual(dtNative.Rows.Count, dtHexagon.Rows.Count, 0, "Hexagon Amount of tupels in query " + currentDataRowIndex + " do not match"); } //D&Q does not work with incomparable tuples if (model.WithIncomparable == false) { Assert.AreEqual(dtNative.Rows.Count, dtDQ.Rows.Count, 0, "D&Q Amount of tupels in query " + currentDataRowIndex + " do not match"); } }
public void 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 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 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 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 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 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 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 TestSamplingNumberOfObjectsWithinEntireSkyline() { string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); int expectedNumberOfEntireSkylineObjects = int.Parse(TestContext.DataRow["expectedNumberOfEntireSkylineObjects"].ToString()); Debug.WriteLine(testComment); Debug.WriteLine(entireSkylineSQL); var common = new SQLCommon { SkylineType = new SkylineBNL() }; DataTable skyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSQL); Assert.AreEqual(expectedNumberOfEntireSkylineObjects, skyline.Rows.Count, "Unexpected number of Skyline objects."); }
public void TestSamplingNumberOfObjectsWithinSampleSkyline() { string skylineSampleSQL = TestContext.DataRow["skylineSampleSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); int expectedNumberOfSkylineSampleObjects = int.Parse(TestContext.DataRow["expectedNumberOfSkylineSampleObjects"].ToString()); Debug.WriteLine(testComment); Debug.WriteLine(skylineSampleSQL); string baseQuery; string operators; int numberOfRecords; string[] parameter; var common = new SQLCommon { SkylineType = new SkylineBNL() {Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString} }; PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSQL); string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators, out numberOfRecords); IEnumerable<CLRSafeHashSet<int>> useSubsets = UseSubsets(prefSqlModelSkylineSample); var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount, SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; DataTable skyline = skylineSample.GetSkylineTable(baseQuery, operators); Assert.AreEqual(expectedNumberOfSkylineSampleObjects, skyline.Rows.Count, "Unexpected number of Sample Skyline objects."); }
public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkyline() { string skylineSampleSql = TestContext.DataRow["skylineSampleSQL"].ToString(); string entireSkylineSql = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); Debug.WriteLine(skylineSampleSql); string baseQuery; string operators; int numberOfRecords; string[] parameter; var common = new SQLCommon { SkylineType = new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString } }; PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql); string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample); prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators, out numberOfRecords); IEnumerable<CLRSafeHashSet<int>> useSubsets = UseSubsets(prefSqlModelSkylineSample); var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets); var utility = new SkylineSamplingUtility(subsetsProducer); var skylineSample = new SkylineSampling(utility) { SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount, SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension, SelectedStrategy = common.SkylineType }; DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSql); DataTable sampleSkyline = skylineSample.GetSkylineTable(baseQuery, operators); HashSet<int> entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline); HashSet<int> sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline); sampleSkylineObjectsIds.ExceptWith(entireSkylineObjectsIds); Debug.WriteLine("wrong objects:"); foreach (int i in sampleSkylineObjectsIds) { Debug.WriteLine(i); } Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds), "Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline)."); }
public void TestSamplingNumberOfObjectsWithinSampleSkylineWithCountOneEqualsEntireSkyline() { string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString(); string testComment = TestContext.DataRow["comment"].ToString(); Debug.WriteLine(testComment); var common = new SQLCommon { SkylineType = new SkylineBNL() }; DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSQL); PrefSQLModel entirePrefSqlModel = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL); DataTable sampleSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, entireSkylineSQL + " SAMPLE BY RANDOM_SUBSETS COUNT 1 DIMENSION " + entirePrefSqlModel.Skyline.Count); Assert.AreEqual(entireSkyline.Rows.Count, sampleSkyline.Rows.Count, "Unexpected number of Skyline objects."); }