public void GetRuntimeName_Strings_Pass()
{
var syntax = new MicrosoftQuerySyntaxHelper();
Assert.AreEqual(syntax.GetRuntimeName("[test]"), "test");
Assert.AreEqual(syntax.GetRuntimeName("`test`"), "`test`");
Assert.AreEqual(syntax.GetRuntimeName("`[test]`"), "`[test]`");
Assert.AreEqual(syntax.GetRuntimeName("[mydb].[test]"), "test");
Assert.AreEqual(syntax.GetRuntimeName("`mymysqldb`.`test`"), "`test`");
Assert.AreEqual(syntax.GetRuntimeName("[mydb]..[test]"), "test");
Assert.AreEqual(syntax.GetRuntimeName("[SERVER].[mydb]..[test]"), "test");
}
public string GetRuntimeName()
{
var helper = new MicrosoftQuerySyntaxHelper();
return(Alias ?? helper.GetRuntimeName(SelectSQL));
}
private string GetPivotPart1(List <CustomLine> lines, IQueryAxis axis, out IQuerySyntaxHelper syntaxHelper, out string pivotAlias, out string countAlias, out string axisColumnAlias)
{
syntaxHelper = new MicrosoftQuerySyntaxHelper();
//find the pivot column e.g. 'hb_extract AS Healthboard'
var pivotSelectLine = lines.Single(l =>
l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Pivot);
//the LHS e.g. hb_extract
string pivotSqlWithoutAlias;
//the RHS e.g. Healthboard
syntaxHelper.SplitLineIntoSelectSQLAndAlias(pivotSelectLine.Text, out pivotSqlWithoutAlias, out pivotAlias);
//ensure it has an RHS
if (string.IsNullOrWhiteSpace(pivotAlias))
{
pivotAlias = syntaxHelper.GetRuntimeName(pivotSqlWithoutAlias);
}
var countSelectLine = lines.Single(l =>
l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction);
string countSqlWithoutAlias;
syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias);
CustomLine axisColumn;
string axisColumnWithoutAlias;
CustomLine axisGroupBy;
//if there is an axis we don't want to pivot on values that are outside that axis restriction.
if (axis != null)
{
AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy);
}
else
{
axisColumnAlias = null;
axisColumnWithoutAlias = null;
}
//Part 1 is where we get all the unique values from the pivot column (after applying the WHERE logic)
bool anyFilters = lines.Any(l => l.LocationToInsert == QueryComponent.WHERE);
string orderBy = countSqlWithoutAlias + " desc";
var topXOrderByLine =
lines.SingleOrDefault(l => l.LocationToInsert == QueryComponent.OrderBy && l.Role == CustomLineRole.TopX);
if (topXOrderByLine != null)
{
orderBy = topXOrderByLine.Text;
}
string havingSqlIfAny = string.Join(Environment.NewLine,
lines.Where(l => l.LocationToInsert == QueryComponent.Having).Select(l => l.Text));
string part1 = string.Format(
@"
/*DYNAMICALLY FETCH COLUMN VALUES FOR USE IN PIVOT*/
DECLARE @Columns as VARCHAR(MAX)
{0}
/*Get distinct values of the PIVOT Column if you have columns with values T and F and Z this will produce [T],[F],[Z] and you will end up with a pivot against these values*/
set @Columns = (
{1}
',' + QUOTENAME({2}) as [text()]
{3}
{4}
{5} ( {2} IS NOT NULL and {2} <> '' {7})
group by
{2}
{8}
order by
{6}
FOR XML PATH(''), root('MyString'),type
).value('/MyString[1]','varchar(max)')
set @Columns = SUBSTRING(@Columns,2,LEN(@Columns))
DECLARE @FinalSelectList as VARCHAR(MAX)
SET @FinalSelectList = {9}
--Split up that pesky string in tsql which has the column names up into array elements again
DECLARE @value varchar(8000)
DECLARE @pos INT
DECLARE @len INT
set @pos = 0
set @len = 0
WHILE CHARINDEX('],', @Columns +',', @pos+1)>0
BEGIN
set @len = CHARINDEX('],[', @Columns +'],[', @pos+1) - @pos
set @value = SUBSTRING(@Columns, @pos+1, @len)
--We are constructing a version that turns: '[fish],[lama]' into 'ISNULL([fish],0) as [fish], ISNULL([lama],0) as [lama]'
SET @FinalSelectList = @FinalSelectList + ', ISNULL(' + @value + ',0) as ' + @value
set @pos = CHARINDEX('],[', @Columns +'],[', @pos+@len) +1
END
if LEFT(@FinalSelectList,1) = ','
SET @FinalSelectList = RIGHT(@FinalSelectList,LEN(@FinalSelectList)-1)
",
//select SQL and parameter declarations
string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)),
string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SELECT)),
pivotSqlWithoutAlias,
//FROM and JOINs that are not to the calendar table
string.Join(Environment.NewLine,
lines.Where(l =>
l.LocationToInsert == QueryComponent.FROM || l.LocationToInsert == QueryComponent.JoinInfoJoin &&
l.Role != CustomLineRole.Axis)),
string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.WHERE)),
anyFilters ? "AND" : "WHERE",
orderBy,
axisColumnWithoutAlias == null ? "": "AND " + axisColumnWithoutAlias + " is not null",
havingSqlIfAny,
axis != null ? "'joinDt'":"''"
);
return(part1);
}