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); } }