private void PerformPreExecutionChecks()
{
try
{
DataLoadEventListener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Performing pre-execution checks"));
var thrower = new ThrowImmediatelyCheckNotifier()
{
WriteToConsole = false
};
_preExecutionChecker.Check(thrower);
}
catch (Exception e)
{
Exception = e;
ExitCode = ExitCodeType.Error;
}
}
/// <summary>
/// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
/// </summary>
public void RegenerateSQL()
{
var checkNotifier = new ThrowImmediatelyCheckNotifier();
_sql = "";
//reset the Parameter knowledge
ParameterManager.ClearNonGlobals();
#region Setup to output the query, where we figure out all the joins etc
//reset everything
SelectColumns.Sort();
//work out all the filters
Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);
TableInfo primary;
TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);
//force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if theres WHERE sql that references them but no columns
if (_forceJoinsToTheseTables != null)
{
foreach (var force in _forceJoinsToTheseTables)
{
if (!TablesUsedInQuery.Contains(force))
{
TablesUsedInQuery.Add(force);
}
}
}
this.PrimaryExtractionTable = primary;
SqlQueryBuilderHelper.FindLookups(this);
JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);
//deal with case when there are no tables in the query or there are only lookup descriptions in the query
if (TablesUsedInQuery.Count == 0)
{
throw new Exception("There are no TablesUsedInQuery in this dataset");
}
_syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);
if (TopX != -1)
{
SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, TopX);
}
else
{
SqlQueryBuilderHelper.ClearTopX(this);
}
//declare parameters
ParameterManager.AddParametersFor(Filters);
#endregion
/////////////////////////////////////////////Assemble Query///////////////////////////////
#region Preamble (including variable declarations/initializations)
//assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
string toReturn = "";
foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
{
//if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
var needsToldTheSyntaxHelper = parameter as IInjectKnown <IQuerySyntaxHelper>;
if (needsToldTheSyntaxHelper != null)
{
needsToldTheSyntaxHelper.InjectKnown(_syntaxHelper);
}
if (CheckSyntax)
{
parameter.Check(checkNotifier);
}
toReturn += GetParameterDeclarationSQL(parameter);
}
//add user custom Parameter lines
toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);
#endregion
#region Select (including all IColumns)
toReturn += Environment.NewLine;
toReturn += "SELECT " + LimitationSQL + Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.SELECT);
toReturn += Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);
for (int i = 0; i < SelectColumns.Count; i++)
{
//output each of the ExtractionInformations that the user requested and record the line number for posterity
string columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, _syntaxHelper);
//there is another one coming
if (i + 1 < SelectColumns.Count)
{
columnAsSql += ",";
}
toReturn += columnAsSql + Environment.NewLine;
}
#endregion
//work out basic JOINS Sql
toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);
//add user custom JOIN lines
toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);
#region Filters (WHERE)
toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);
toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);
toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);
_sql = toReturn;
SQLOutOfDate = false;
#endregion
}
public void DumpAllIdentifiersInTable_UnexpectedColumnFoundInIdentifierDumpTable()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "surname");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(20)";
preDiscardedColumn1.SaveToDatabase();
var preDiscardedColumn2 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn2.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn2.SqlDataType = "varchar(50)";
preDiscardedColumn2.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
dumper.Check(new AcceptAllCheckNotifier());
DiscoveredTable tableInDump = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable);
Assert.IsTrue(tableInDump.Exists(), "ID table did not exist");
var columnsInDump = tableInDump.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
//works and creates table on server
Assert.Contains("hic_validFrom", columnsInDump);
Assert.Contains("forename", columnsInDump);
Assert.Contains("chi", columnsInDump);
Assert.Contains("surname", columnsInDump);
//now delete it!
preDiscardedColumn2.DeleteInDatabase();
//now create a new dumper and watch it go crazy
IdentifierDumper dumper2 = new IdentifierDumper(tableInfoCreated);
var thrower = new ThrowImmediatelyCheckNotifier();
thrower.ThrowOnWarning = true;
try
{
var ex = Assert.Throws <Exception>(() => dumper2.Check(thrower));
Assert.AreEqual("Column forename was found in the IdentifierDump table ID_BulkData but was not one of the primary keys or a PreLoadDiscardedColumn", ex.Message);
}
finally
{
//Drop all this stuff
var server = IdentifierDump_Database.Server;
using (var con = server.GetConnection())
{
con.Open();
//leave the identifier dump in the way we found it (empty)
var cmdDrop = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable, con);
cmdDrop.ExecuteNonQuery();
var cmdDropArchive = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable + "_Archive", con);
cmdDropArchive.ExecuteNonQuery();
}
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
