private TableInfo GetTableInfoFromConstructorArguments(ArchivalTableLoadInfo toAttemptToDisplay, List <TableInfo> potentialTableInfos, ICheckNotifier checkNotifier)
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Table user is attempting to view updates/inserts for is called " + toAttemptToDisplay.TargetTable, CheckResult.Success));
string runtimeName = new MicrosoftQuerySyntaxHelper().GetRuntimeName(toAttemptToDisplay.TargetTable);
checkNotifier.OnCheckPerformed(new CheckEventArgs("The runtime name of the table is " + runtimeName, CheckResult.Success));
checkNotifier.OnCheckPerformed(
new CheckEventArgs(
"The following TableInfos were given to us to pick from " +
string.Join(",", potentialTableInfos), CheckResult.Success));
var candidates = potentialTableInfos.Where(t => t.GetRuntimeName().Equals(runtimeName)).ToArray();
if (!candidates.Any())
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Could not find an appropriate TableInfo from those mentioned above", CheckResult.Fail));
return(null);
}
if (candidates.Count() > 1)
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Found multiple TableInfos (mentioned above) with the runtime name " + runtimeName + " I don't know which one you want to view", CheckResult.Fail));
return(null);
}
checkNotifier.OnCheckPerformed(new CheckEventArgs("Found the correct TableInfo!", CheckResult.Success));
return(candidates.Single());
}
private void ProcessFile(FileInfo fileInfo, IDataLoadJob job)
{
using (var fs = new FileStream(fileInfo.FullName, FileMode.Open))
{
IWorkbook wb;
if (fileInfo.Extension == ".xls")
{
wb = new HSSFWorkbook(fs);
}
else
{
wb = new XSSFWorkbook(fs);
}
try
{
var source = new ExcelDataFlowSource();
source.PreInitialize(new FlatFileToLoad(fileInfo), job);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
var sheet = wb.GetSheetAt(i);
if (IsWorksheetNameMatch(sheet.SheetName))
{
job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Information,
"Started processing worksheet:" + sheet.SheetName));
string newName = PrefixWithWorkbookName
? Path.GetFileNameWithoutExtension(fileInfo.FullName) + "_" + sheet.SheetName
: sheet.SheetName;
//make it sensible
newName = new MicrosoftQuerySyntaxHelper().GetSensibleTableNameFromString(newName) + ".csv";
string savePath = Path.Combine(job.LoadDirectory.ForLoading.FullName, newName);
var dt = source.GetAllData(sheet, job);
dt.SaveAsCsv(savePath);
job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Information, "Saved worksheet as " + newName));
}
else
{
job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Information, "Ignoring worksheet:" + sheet.SheetName));
}
}
}
finally
{
wb.Close();
}
}
}
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");
}
private string BuildAxisAggregate(List <CustomLine> lines, IQueryAxis axis)
{
var syntaxHelper = new MicrosoftQuerySyntaxHelper();
var countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction);
string countSqlWithoutAlias;
string countAlias;
syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias);
CustomLine axisColumn;
string axisColumnWithoutAlias;
CustomLine axisGroupBy;
string axisColumnAlias;
AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy);
return(string.Format(
@"
{0}
{1}
SELECT
{2} AS joinDt,dataset.{3}
FROM
@dateAxis axis
LEFT JOIN
(
{4}
) dataset
ON dataset.{5} = {2}
ORDER BY
{2}
"
,
string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)),
GetDateAxisTableDeclaration(axis),
GetDatePartOfColumn(axis.AxisIncrement, "axis.dt"),
countAlias,
//the entire query
string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)),
axisColumnAlias
).Trim());
}
public void SplitMethod()
{
var syntaxHelper = new MicrosoftQuerySyntaxHelper();
string contents;
string method;
syntaxHelper.SplitLineIntoOuterMostMethodAndContents("count(*)", out method, out contents);
Assert.AreEqual("count", method);
Assert.AreEqual("*", contents);
syntaxHelper.SplitLineIntoOuterMostMethodAndContents("count()", out method, out contents);
Assert.AreEqual("count", method);
Assert.AreEqual("", contents);
syntaxHelper.SplitLineIntoOuterMostMethodAndContents("LTRIM(RTRIM([Fish]))", out method, out contents);
Assert.AreEqual("LTRIM", method);
Assert.AreEqual("RTRIM([Fish])", contents);
}
public string GetRuntimeName()
{
var helper = new MicrosoftQuerySyntaxHelper();
return(Alias ?? helper.GetRuntimeName(SelectSQL));
}
private void CreateViewOldVersionsTableValuedFunction(string sqlUsedToCreateArchiveTableSQL, DbConnection con)
{
string columnsInArchive = "";
var syntaxHelper = new MicrosoftQuerySyntaxHelper();
Match matchStartColumnExtraction = Regex.Match(sqlUsedToCreateArchiveTableSQL, "CREATE TABLE .*\\(");
if (!matchStartColumnExtraction.Success)
{
throw new Exception("Could not find regex match at start of Archive table CREATE SQL");
}
int startExtractingColumnsAt = matchStartColumnExtraction.Index + matchStartColumnExtraction.Length;
//trim off excess crud at start and we should have just the columns bit of the create (plus crud at the end)
columnsInArchive = sqlUsedToCreateArchiveTableSQL.Substring(startExtractingColumnsAt);
//trim off excess crud at the end
columnsInArchive = columnsInArchive.Trim(new[] { ')', '\r', '\n' });
string sqlToRun = string.Format("CREATE FUNCTION [" + _schema + "].[{0}_Legacy]", _table.GetRuntimeName());
sqlToRun += Environment.NewLine;
sqlToRun += "(" + Environment.NewLine;
sqlToRun += "\t@index DATETIME" + Environment.NewLine;
sqlToRun += ")" + Environment.NewLine;
sqlToRun += "RETURNS @returntable TABLE" + Environment.NewLine;
sqlToRun += "(" + Environment.NewLine;
sqlToRun += "/*the return table will follow the structure of the Archive table*/" + Environment.NewLine;
sqlToRun += columnsInArchive;
//these were added during transaction so we have to specify them again here because transaction will not have been committed yet
sqlToRun = sqlToRun.Trim();
sqlToRun += "," + Environment.NewLine;
sqlToRun += "\thic_validTo datetime," + Environment.NewLine;
sqlToRun += "\thic_userID varchar(128),";
sqlToRun += "\thic_status char(1)";
sqlToRun += ")" + Environment.NewLine;
sqlToRun += "AS" + Environment.NewLine;
sqlToRun += "BEGIN" + Environment.NewLine;
sqlToRun += Environment.NewLine;
var liveCols = _columns.Select(c => c.GetRuntimeName()).Union(new String[] { SpecialFieldNames.DataLoadRunID, SpecialFieldNames.ValidFrom }).ToArray();
string archiveCols = string.Join(",", liveCols) + ",hic_validTo,hic_userID,hic_status";
string cDotArchiveCols = string.Join(",", liveCols.Select(s => "c." + s));
sqlToRun += "\tINSERT @returntable" + Environment.NewLine;
sqlToRun += string.Format("\tSELECT " + archiveCols + " FROM {0} WHERE @index BETWEEN ISNULL(" + SpecialFieldNames.ValidFrom + ", '1899/01/01') AND hic_validTo" + Environment.NewLine, _archiveTable);
sqlToRun += Environment.NewLine;
sqlToRun += "\tINSERT @returntable" + Environment.NewLine;;
sqlToRun += "\tSELECT " + cDotArchiveCols + ",NULL AS hic_validTo, NULL AS hic_userID, 'C' AS hic_status" + Environment.NewLine; //c is for current
sqlToRun += string.Format("\tFROM {0} c" + Environment.NewLine, _table.GetRuntimeName());
sqlToRun += "\tLEFT OUTER JOIN @returntable a ON " + Environment.NewLine;
for (int index = 0; index < _primaryKeys.Length; index++)
{
sqlToRun += string.Format("\ta.{0}=c.{0} " + Environment.NewLine,
syntaxHelper.EnsureWrapped(_primaryKeys[index].GetRuntimeName())); //add the primary key joins
if (index + 1 < _primaryKeys.Length)
{
sqlToRun += "\tAND" + Environment.NewLine; //add an AND because there are more coming
}
}
sqlToRun += string.Format("\tWHERE a.{0} IS NULL -- where archive record doesn't exist" + Environment.NewLine, _primaryKeys.First().GetRuntimeName());
sqlToRun += "\tAND @index > ISNULL(c." + SpecialFieldNames.ValidFrom + ", '1899/01/01')" + Environment.NewLine;
sqlToRun += Environment.NewLine;
sqlToRun += "RETURN" + Environment.NewLine;
sqlToRun += "END" + Environment.NewLine;
var cmd = _server.GetCommand(sqlToRun, con);
cmd.ExecuteNonQuery();
}
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);
}