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