public void TestAggregateBuilding_NoConfigurationTwoDimension_Top10DimensionOrder(bool asc)
{
var topX = new AggregateTopX(CatalogueRepository, _configuration, 10);
topX.OrderByDimensionIfAny_ID = _dimension1.ID;
topX.OrderByDirection = asc
? AggregateTopXOrderByDirection.Ascending
: AggregateTopXOrderByDirection.Descending;
topX.SaveToDatabase();
var builder = _configuration.GetQueryBuilder();
Assert.AreEqual(CollapseWhitespace(@"/*MyConfig*/
SELECT
TOP 10
Col1,
Col2,
count(*) AS MyCount
FROM
T1
group by
Col1,
Col2
order by
Col1 " + (asc ? "asc" : "desc")), CollapseWhitespace(builder.SQL));
topX.DeleteInDatabase();
}
public void Test_AggregateBuilder_MySql_Top32()
{
_ti.DatabaseType = DatabaseType.MySql;
_ti.SaveToDatabase();
var builder = new AggregateBuilder(null, "count(*)", null);
builder.AddColumn(_dimension1);
var topx = new AggregateTopX(CatalogueRepository, _configuration, 32);
topx.OrderByDimensionIfAny_ID = _dimension1.ID;
topx.SaveToDatabase();
builder.AggregateTopX = topx;
Assert.AreEqual(CollapseWhitespace(@"/**/
SELECT
Col1,
count(*) AS MyCount
FROM
T1
group by
Col1
order by
Col1 desc
LIMIT 32"), CollapseWhitespace(builder.SQL.Trim()));
topx.DeleteInDatabase();
}
public void TestAggregateBuilding_NoConfigurationTwoDimension_Top10(string countColField, bool asc)
{
var topX = new AggregateTopX(CatalogueRepository, _configuration, 10);
topX.OrderByDirection = asc
? AggregateTopXOrderByDirection.Ascending
: AggregateTopXOrderByDirection.Descending;
topX.SaveToDatabase();
var beforeCountSQL = _configuration.CountSQL;
_configuration.CountSQL = countColField;
var builder = _configuration.GetQueryBuilder();
Assert.AreEqual(CollapseWhitespace(@"/*MyConfig*/
SELECT
TOP 10
Col1,
Col2,
" + countColField + @" AS MyCount
FROM
T1
group by
Col1,
Col2
order by
" + countColField + " " + (asc?"asc":"desc")), CollapseWhitespace(builder.SQL));
_configuration.CountSQL = beforeCountSQL;
topX.DeleteInDatabase();
}
private void tbTopX_TextChanged(object sender, EventArgs e)
{
if (bLoading)
{
return;
}
//user is trying to delete an existing TopX
if (_topX != null && string.IsNullOrWhiteSpace(tbTopX.Text))
{
_topX.DeleteInDatabase();
Activator.RefreshBus.Publish(this, new RefreshObjectEventArgs(_aggregate));
return;
}
//user is typing something illegal like 'ive got a lovely bunch o coconuts'
int i;
if (!int.TryParse(tbTopX.Text, out i))
{
//not an int
tbTopX.ForeColor = Color.Red;
return;
}
//user put in a negative
if (i <= 0)
{
tbTopX.ForeColor = Color.Red;
return;
}
tbTopX.ForeColor = Color.Black;
//there isn't one yet
if (_topX == null)
{
_topX = new AggregateTopX(Activator.RepositoryLocator.CatalogueRepository, _aggregate, i);
}
else
{
//there is one so change it's topX
_topX.TopX = i;
_topX.SaveToDatabase();
}
Activator.RefreshBus.Publish(this, new RefreshObjectEventArgs(_aggregate));
}
public void GroupBy_PivotWithSum_HAVING_Top1_WHERE(DatabaseType type)
{
Catalogue catalogue;
ExtractionInformation[] extractionInformations;
TableInfo tableInfo;
var tbl = UploadTestDataAsTableToServer(type, out catalogue, out extractionInformations, out tableInfo);
//setup the aggregate pivot (and axis)
AggregateDimension axisDimension;
AggregateDimension pivotDimension;
var configuration = SetupAggregateWithPivot(type, extractionInformations, catalogue, out axisDimension, out pivotDimension);
configuration.CountSQL = "sum(NumberInTrouble)";
configuration.PivotOnDimensionID = pivotDimension.ID; //pivot on the Category
configuration.HavingSQL = "count(*)<5"; //throws out 'T'
configuration.SaveToDatabase();
var topx = new AggregateTopX(CatalogueRepository, configuration, 1); //Top 1 (highest count columns should be used for pivot)
topx.OrderByDirection = AggregateTopXOrderByDirection.Descending;
topx.SaveToDatabase();
try
{
//get the result of the aggregate
var builder = new AggregateBuilder(null, configuration.CountSQL, configuration);
builder.AddColumn(axisDimension);
builder.AddColumn(pivotDimension);
builder.SetPivotToDimensionID(pivotDimension);
AddWHEREToBuilder_CategoryIsTOrNumberGreaterThan42(builder, type);
var resultTable = GetResultForBuilder(builder, tbl);
//axis is ordered ascending by date starting in 2000 so that row should come first
Assert.IsTrue(AreBasicallyEquals("2000", resultTable.Rows[0][0]));
//where logic matches T in spades but HAVING statement throws it out for having more than 4 records total
Assert.AreEqual("E&, %a' mp;E", resultTable.Columns[1].ColumnName);
//Only E appears because of Top 1 pivot statement
VerifyRowExist(resultTable, "2000", null); //all E records are discarded except 59 because that is the WHERE logic
VerifyRowExist(resultTable, "2001", null);
VerifyRowExist(resultTable, "2002", null);
VerifyRowExist(resultTable, "2003", null);
VerifyRowExist(resultTable, "2004", null);
VerifyRowExist(resultTable, "2005", 59);
VerifyRowExist(resultTable, "2006", null);
VerifyRowExist(resultTable, "2007", null);
}
finally
{
Destroy(tbl, topx, configuration, catalogue, tableInfo);
}
}
public void GroupBy_PivotWithSum_Top2AlphabeticalAsc_WHEREStatement(DatabaseType type)
{
Catalogue catalogue;
ExtractionInformation[] extractionInformations;
TableInfo tableInfo;
var tbl = UploadTestDataAsTableToServer(type, out catalogue, out extractionInformations, out tableInfo);
//setup the aggregate pivot (and axis)
AggregateDimension axisDimension;
AggregateDimension pivotDimension;
var configuration = SetupAggregateWithPivot(type, extractionInformations, catalogue, out axisDimension, out pivotDimension);
configuration.CountSQL = "sum(NumberInTrouble)";
configuration.PivotOnDimensionID = pivotDimension.ID; //pivot on the Category
configuration.SaveToDatabase();
var topx = new AggregateTopX(CatalogueRepository, configuration, 2);
topx.OrderByDirection = AggregateTopXOrderByDirection.Descending;
topx.OrderByDimensionIfAny_ID = pivotDimension.ID;
topx.OrderByDirection = AggregateTopXOrderByDirection.Ascending;
topx.SaveToDatabase();
try
{
//get the result of the aggregate
var builder = new AggregateBuilder(null, configuration.CountSQL, configuration);
builder.AddColumn(axisDimension);
builder.AddColumn(pivotDimension);
builder.SetPivotToDimensionID(pivotDimension);
AddWHEREToBuilder_CategoryIsTOrNumberGreaterThan42(builder, type);
var resultTable = GetResultForBuilder(builder, tbl);
//axis is ordered ascending by date starting in 2000 so that row should come first
Assert.IsTrue(AreBasicallyEquals("2000", resultTable.Rows[0][0]));
//sort in AggregateTopX is the pivot dimension asc (i.e. order alphabetically)
Assert.AreEqual("E&, %a' mp;E", resultTable.Columns[1].ColumnName);
Assert.AreEqual("G", resultTable.Columns[2].ColumnName);
//E,G (note that only 1 value appears for E because WHERE throws out rest). Also note the two columns are E and G because that is Top 2 when alphabetically sorted of the pivot values (E,F,G,T) that match the filter (F doesn't)
VerifyRowExist(resultTable, "2000", null, null); //no records in 2000 but it is important it appears still because that is what the axis says
VerifyRowExist(resultTable, "2001", null, 53);
VerifyRowExist(resultTable, "2002", null, null);
VerifyRowExist(resultTable, "2003", null, null);
VerifyRowExist(resultTable, "2004", null, null);
VerifyRowExist(resultTable, "2005", 59, null);
VerifyRowExist(resultTable, "2006", null, null);
VerifyRowExist(resultTable, "2007", null, null);
}
finally
{
Destroy(tbl, topx, configuration, catalogue, tableInfo);
}
}
public void GroupBy_PivotWithSum_Top2BasedonCountColumnDesc(DatabaseType type)
{
Catalogue catalogue;
ExtractionInformation[] extractionInformations;
TableInfo tableInfo;
var tbl = UploadTestDataAsTableToServer(type, out catalogue, out extractionInformations, out tableInfo);
//setup the aggregate pivot (and axis)
AggregateDimension axisDimension;
AggregateDimension pivotDimension;
var configuration = SetupAggregateWithPivot(type, extractionInformations, catalogue, out axisDimension, out pivotDimension);
configuration.CountSQL = "sum(NumberInTrouble)";
configuration.PivotOnDimensionID = pivotDimension.ID; //pivot on the Category
configuration.SaveToDatabase();
var topx = new AggregateTopX(CatalogueRepository, configuration, 2);
topx.OrderByDirection = AggregateTopXOrderByDirection.Descending;
topx.SaveToDatabase();
try
{
//get the result of the aggregate
var builder = new AggregateBuilder(null, configuration.CountSQL, configuration);
builder.AddColumn(axisDimension);
builder.AddColumn(pivotDimension);
builder.SetPivotToDimensionID(pivotDimension);
var resultTable = GetResultForBuilder(builder, tbl);
//axis is ordered ascending by date starting in 2000 so that row should come first
Assert.IsTrue(AreBasicallyEquals("2000", resultTable.Rows[0][0]));
Assert.AreEqual("T", resultTable.Columns[1].ColumnName);
Assert.AreEqual("E&, %a' mp;E", resultTable.Columns[2].ColumnName);
//T,E,G - F does not appear because WHERE throws it out (both counts are below 42)
VerifyRowExist(resultTable, "2000", null, null); //no records in 2000 but it is important it appears still because that is what the axis says
VerifyRowExist(resultTable, "2001", 67, 37);
VerifyRowExist(resultTable, "2002", 30, 41);
VerifyRowExist(resultTable, "2003", 42, null);
VerifyRowExist(resultTable, "2004", null, null);
VerifyRowExist(resultTable, "2005", null, 59);
VerifyRowExist(resultTable, "2006", null, null);
VerifyRowExist(resultTable, "2007", null, null);
}
finally
{
Destroy(tbl, topx, configuration, catalogue, tableInfo);
}
}