private static void RunReport(StringBuilder sb, long contextType, IExpression expr)
{
// Build structured query
ResourceEntity re = new ResourceEntity();
re.EntityTypeId = new EntityRef(contextType);
StructuredQuery sq = new StructuredQuery {
RootEntity = re
};
// Convert script to query
QueryBuilderSettings qsettings = new QueryBuilderSettings();
qsettings.StructuredQuery = sq;
qsettings.ContextEntity = re;
ScalarExpression resExpr = Factory.ExpressionCompiler.CreateQueryEngineExpression(expr, qsettings);
sb.AppendLine("OK");
// Render XML
sb.AppendLine("\nXML:");
sb.AppendLine(StructuredQueryHelper.ToXml(sq));
// Render SQL
sb.AppendLine("\nSQL:");
sq.SelectColumns.Add(new SelectColumn {
Expression = resExpr
});
sb.AppendLine("declare @tenant as bigint = (select min(Id) from _vTenant) -- test harness");
string sql = EDC.ReadiNow.Metadata.Query.Structured.Builder.QueryBuilder.GetSql(sq);
sb.AppendLine(sql);
}
/// <summary>
/// Performs any special processing when the resource is serialized into XML.
/// </summary>
/// <param name="xmlWriter">The writer used to write the image.</param>
protected void OnToXml(System.Xml.XmlWriter xmlWriter)
{
if (this.StructuredQuery != null)
{
StructuredQueryHelper.ToXml(xmlWriter, this.StructuredQuery);
}
}
/// <summary>
/// Generate the query SQL. Do not actually run it.
/// </summary>
/// <param name="query">The structured query object to convert.</param>
/// <param name="settings">Build-time settings for the conversion.</param>
/// <returns>An object structure containing SQL, and other discovered information.</returns>
public QueryBuild BuildSql(StructuredQuery query, QuerySqlBuilderSettings settings)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
// Initialise settings
if (settings == null)
{
settings = new QuerySqlBuilderSettings( );
}
// Optimise tree
StructuredQuery optimisedQuery = StructuredQueryHelper.PruneQueryTree(query);
// Generate SQL
QueryBuilder queryBuilder = new QueryBuilder(optimisedQuery, settings);
QueryBuild result = queryBuilder.GetSqlInternal( );
// Logging
using (MessageContext msg = new MessageContext("Reports"))
{
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "Final structured query:\n" + StructuredQueryHelper.ToXml(optimisedQuery));
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "SQL:\n" + result.Sql);
msg.Append(() => new string( '-', 50 ));
}
// Note: identify cache dependencies after getting SQL, so that any calculations are resolved into the structured query.
IdentifyCacheDependencies(optimisedQuery, settings);
return(result);
}
private void RunTest(string testName, Action <StructuredQuery> additionalProcessing = null, int derivedTypesTempTableThreshold = -1)
{
XmlDocument doc = GetTestCaseXml( );
XmlNode test = doc.DocumentElement.SelectSingleNode("Test[@name='" + testName + "']");
if (test == null)
{
throw new Exception("Test not found in Query tests.xml: " + testName);
}
// Load test
StructuredQuery query;
try
{
query = StructuredQueryHelper.FromXml(test.FirstChild);
}
catch (Exception ex)
{
throw new Exception("Failed to load query for " + testName, ex);
}
query.TimeZoneName = TimeZoneHelper.SydneyTimeZoneName;
if (additionalProcessing != null)
{
additionalProcessing(query);
}
// Generate SQL
var settings = new QuerySettings {
Hint = "test", DebugMode = true, DerivedTypesTempTableThreshold = derivedTypesTempTableThreshold
};
QueryBuild result = QueryBuilder.GetSql(query, settings);
string sql = result.Sql;
sql = Canonical(sql);
// Get expected results
XmlNode selectSingleNode = test.SelectSingleNode("Expect/text()");
if (selectSingleNode != null)
{
string expected = Canonical(selectSingleNode.Value);
expected = expected.Replace("{userResource}", new EntityRef("core", "userResource").Id.ToString(CultureInfo.InvariantCulture));
Assert.AreEqual(expected, sql, "Generated SQL did not match expected.");
}
StructuredQueryHelper.ToXml(query);
}
/// <summary>
/// Get the structured query, possibly from cache.
/// </summary>
/// <param name="report">The report to convert.</param>
/// <param name="settings">The report run settings.</param>
/// <param name="suppressPreload">True if we should suppress preloading.</param>
/// <returns>The structured query.</returns>
private StructuredQuery GetStructuredQuery(Model.Report report, ReportSettings settings, bool suppressPreload)
{
using (MessageContext msg = new MessageContext("Reports"))
{
StructuredQuery immutableStructuredQuery;
StructuredQuery structuredQuery;
bool useStructuredQueryCache = settings.UseStructuredQueryCache;
ReportToQueryConverterSettings converterSettings = new ReportToQueryConverterSettings
{
SuppressPreload = suppressPreload,
RefreshCachedStructuredQuery = settings.RefreshCachedStructuredQuery,
SchemaOnly = settings.RequireSchemaMetadata
};
if (settings != null && settings.UseStructuredQueryCache)
{
// don't allow mutations of cached copy
immutableStructuredQuery = CachedReportToQueryConverter.Convert(report, converterSettings);
}
else
{
// don't allow mutations, just so we can log it correctly
immutableStructuredQuery = NonCachedReportToQueryConverter.Convert(report, converterSettings);
}
structuredQuery = immutableStructuredQuery.DeepCopy( ); // so we can mutate it (in case we need to)
// Logging
msg.Append(() => new String('-', 50));
msg.Append(() => "GetStructuredQuery");
msg.Append(() => "suppressPreload = " + suppressPreload);
msg.Append(() => "useStructuredQueryCache = " + useStructuredQueryCache);
msg.Append(() => "Structured Query:\n" + StructuredQueryHelper.ToXml(immutableStructuredQuery));
msg.Append(() => new String('-', 50));
return(structuredQuery);
}
}
