Skip to content

Commit 93a088f

Browse files
RubenCerna2079Aniruddh25
RubenCerna2079
and
Aniruddh25
authored
Create and execute query to return tables and interpolated names for Autoentities (#3082)
## Why make this change? - #2966 - #2965 We need to create a query and send it to the database in order to receive all of the tables that will be turned into entities from the autoentities configuration. This includes the `schema`, `object`, and `entity name`. ## What is this change? - **IQueryBuilder & MsSqlQueryBuilder**: Adds new function that builds the query that returns all of the tables that will be turned into entities. - **SqlMetadataProvider & MsSqlMetadataProvider**: Adds function that executes query and receives the information for all of the existing autoentities. The information that is received for each table is the `schema`, `object`, and `entity name`, an example of this would be `dbo`, `book`, `dbo.book`. The query also transforms the reserved words `{schema}` and `{object}` into the tables schema and object and returns it in the name. - **SqlMetadataProviderUnitTests**: Adds testing to ensure that the query returns the expected output. ## How was this tested? - [ ] Integration Tests - [x] Unit Tests - [x] Manual Testing --------- Co-authored-by: Aniruddh Munde <[email protected]>
1 parent c017efe commit 93a088f

6 files changed

Lines changed: 298 additions & 3 deletions

File tree

‎src/Core/Resolvers/IQueryBuilder.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,5 +90,7 @@ public interface IQueryBuilder
9090
/// DB Connection Param.
9191
/// </summary>
9292
public string QuoteTableNameAsDBConnectionParam(string param);
93+
94+
public string BuildGetAutoentitiesQuery() => throw new NotSupportedException($"{GetType().Name} does not support Autoentities yet.");
9395
}
9496
}

‎src/Core/Resolvers/MsSqlQueryBuilder.cs‎

Lines changed: 184 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -506,7 +506,7 @@ public string BuildStoredProcedureResultDetailsQuery(string databaseObjectName)
506506
/// 2. are computed based on other columns,
507507
/// are considered as read only columns. The query combines both the types of read-only columns and returns the list.
508508
/// </summary>
509-
/// <param name="schemaOrDatabaseParamName">Param name of the schema/database.</param>
509+
/// <param name="schemaParamName">Param name of the schema.</param>
510510
/// <param name="tableParamName">Param name of the table.</param>
511511
/// <returns></returns>
512512
public string BuildQueryToGetReadOnlyColumns(string schemaParamName, string tableParamName)
@@ -560,5 +560,188 @@ protected override string BuildPredicates(SqlQueryStructure structure)
560560
// contains LIKE and add the ESCAPE clause accordingly.
561561
return AddEscapeToLikeClauses(predicates);
562562
}
563+
564+
/// <summary>
565+
/// Builds the query used to get the list of tables with the SQL LIKE
566+
/// syntax that will be transformed into entities.
567+
/// NOTE: Currently this query only returns Tables, support for Views will come later.
568+
/// </summary>
569+
/// <param name="include">Pattern for tables that will be included.</param>
570+
/// <param name="exclude">Pattern for tables that will be excluded.</param>
571+
/// <param name="namePattern">Pattern for naming the entities.</param>
572+
public string BuildGetAutoentitiesQuery()
573+
{
574+
string query = @$"
575+
DECLARE @exclude_invalid_types BIT = 1;
576+
577+
SET NOCOUNT ON;
578+
579+
WITH
580+
{IncludeAndExcludeSplitQuery(true)},
581+
{IncludeAndExcludeSplitQuery(false)},
582+
all_tables AS
583+
(
584+
SELECT
585+
s.name AS schema_name,
586+
t.name AS object_name,
587+
s.name + N'.' + t.name AS full_name,
588+
N'table' AS object_type,
589+
t.object_id
590+
FROM sys.tables AS t
591+
JOIN sys.schemas AS s
592+
ON t.schema_id = s.schema_id
593+
WHERE EXISTS
594+
(
595+
SELECT 1
596+
FROM sys.key_constraints AS kc
597+
WHERE kc.parent_object_id = t.object_id
598+
AND kc.type = 'PK'
599+
)
600+
),
601+
eligible_tables AS
602+
(
603+
SELECT
604+
o.schema_name,
605+
o.object_name,
606+
o.full_name,
607+
o.object_type,
608+
o.object_id,
609+
CASE
610+
WHEN so.is_ms_shipped = 1 THEN 1
611+
WHEN o.schema_name IN (N'sys', N'INFORMATION_SCHEMA') THEN 1
612+
WHEN o.object_name IN
613+
(
614+
N'__EFMigrationsHistory',
615+
N'__MigrationHistory',
616+
N'__FlywayHistory',
617+
N'sysdiagrams'
618+
) THEN 1
619+
WHEN o.object_name LIKE N'service_broker_%' THEN 1
620+
WHEN o.object_name LIKE N'queue_messages_%' THEN 1
621+
WHEN o.object_name LIKE N'MSmerge_%' THEN 1
622+
WHEN o.object_name LIKE N'MSreplication_%' THEN 1
623+
WHEN o.object_name LIKE N'FileTableUpdates$%' THEN 1
624+
WHEN o.object_name LIKE N'graph_%' THEN 1
625+
WHEN EXISTS
626+
(
627+
SELECT 1
628+
FROM sys.tables AS t
629+
WHERE t.object_id = o.object_id
630+
AND
631+
(
632+
t.is_tracked_by_cdc = 1
633+
OR t.temporal_type > 0
634+
OR t.is_filetable = 1
635+
OR t.is_memory_optimized = 1
636+
)
637+
) THEN 1
638+
ELSE 0
639+
END AS is_system_object
640+
FROM all_tables AS o
641+
JOIN sys.objects AS so
642+
ON so.object_id = o.object_id
643+
)
644+
SELECT
645+
a.schema_name AS [schema],
646+
a.object_name AS [object],
647+
CASE
648+
WHEN LTRIM(RTRIM(ISNULL(@name_pattern, N''))) = N'' THEN a.object_name
649+
ELSE REPLACE(
650+
REPLACE(@name_pattern, N'{{schema}}', a.schema_name),
651+
N'{{object}}', a.object_name
652+
)
653+
END AS entity_name,
654+
CASE
655+
WHEN EXISTS
656+
(
657+
SELECT 1
658+
FROM sys.columns AS c
659+
JOIN sys.types AS ty
660+
ON c.user_type_id = ty.user_type_id
661+
WHERE c.object_id = a.object_id
662+
AND ty.name IN
663+
(
664+
N'geography',
665+
N'geometry',
666+
N'hierarchyid',
667+
N'sql_variant',
668+
N'xml',
669+
N'rowversion',
670+
N'vector'
671+
)
672+
) THEN 1
673+
ELSE 0
674+
END AS contains_invalid_types
675+
FROM eligible_tables AS a
676+
WHERE
677+
a.is_system_object = 0
678+
AND
679+
(
680+
NOT EXISTS (SELECT 1 FROM exclude_patterns)
681+
OR NOT EXISTS
682+
(
683+
SELECT 1
684+
FROM exclude_patterns AS ep
685+
WHERE a.full_name LIKE ep.pattern COLLATE DATABASE_DEFAULT ESCAPE '\'
686+
)
687+
)
688+
AND
689+
(
690+
NOT EXISTS (SELECT 1 FROM include_patterns)
691+
OR EXISTS
692+
(
693+
SELECT 1
694+
FROM include_patterns AS ip
695+
WHERE a.full_name LIKE ip.pattern COLLATE DATABASE_DEFAULT ESCAPE '\'
696+
)
697+
)
698+
AND
699+
(
700+
@exclude_invalid_types = 0
701+
OR NOT EXISTS
702+
(
703+
SELECT 1
704+
FROM sys.columns AS c
705+
JOIN sys.types AS ty
706+
ON c.user_type_id = ty.user_type_id
707+
WHERE c.object_id = a.object_id
708+
AND ty.name IN
709+
(
710+
N'geography',
711+
N'geometry',
712+
N'hierarchyid',
713+
N'sql_variant',
714+
N'xml',
715+
N'rowversion',
716+
N'vector'
717+
)
718+
)
719+
)
720+
ORDER BY
721+
a.schema_name,
722+
a.object_name;";
723+
724+
return query;
725+
}
726+
727+
/// <summary>
728+
/// Generates a SQL query segment for splitting include or exclude patterns.
729+
/// </summary>
730+
/// <param name="isInclude">Indicates whether to generate the include or exclude pattern query.</param>
731+
/// <returns>An SQL query segment as a string.</returns>
732+
public static string IncludeAndExcludeSplitQuery(bool isInclude)
733+
{
734+
string pattern = isInclude ? "include" : "exclude";
735+
736+
string query = $@"
737+
{pattern}_patterns AS
738+
(
739+
SELECT LTRIM(RTRIM(value)) AS pattern
740+
FROM STRING_SPLIT(ISNULL(@{pattern}_pattern, N''), N',')
741+
WHERE LTRIM(RTRIM(value)) <> N''
742+
)";
743+
744+
return query;
745+
}
563746
}
564747
}

‎src/Core/Resolvers/MsSqlQueryExecutor.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -82,7 +82,7 @@ public MsSqlQueryExecutor(
8282
_dataSourceToSessionContextUsage = new Dictionary<string, bool>();
8383
_accessTokensFromConfiguration = runtimeConfigProvider.ManagedIdentityAccessToken;
8484
_runtimeConfigProvider = runtimeConfigProvider;
85-
ConfigureMsSqlQueryEecutor();
85+
ConfigureMsSqlQueryExecutor();
8686
}
8787

8888
/// <summary>
@@ -138,7 +138,7 @@ public override SqlConnection CreateConnection(string dataSourceName)
138138
/// <summary>
139139
/// Configure during construction or a hot-reload scenario.
140140
/// </summary>
141-
private void ConfigureMsSqlQueryEecutor()
141+
private void ConfigureMsSqlQueryExecutor()
142142
{
143143
IEnumerable<KeyValuePair<string, DataSource>> mssqldbs = _runtimeConfigProvider.GetConfig().GetDataSourceNamesToDataSourcesIterator().Where(x => x.Value.DatabaseType is DatabaseType.MSSQL || x.Value.DatabaseType is DatabaseType.DWSQL);
144144

‎src/Core/Services/MetadataProviders/MsSqlMetadataProvider.cs‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -290,5 +290,38 @@ private bool TryResolveDbType(string sqlDbTypeName, out DbType dbType)
290290
return false;
291291
}
292292
}
293+
294+
/// <inheritdoc/>
295+
protected override async Task GenerateAutoentitiesIntoEntities()
296+
{
297+
await Task.CompletedTask;
298+
}
299+
300+
public async Task<JsonArray?> QueryAutoentitiesAsync(Autoentity autoentity)
301+
{
302+
string include = string.Join(",", autoentity.Patterns.Include);
303+
string exclude = string.Join(",", autoentity.Patterns.Exclude);
304+
string namePattern = autoentity.Patterns.Name;
305+
string getAutoentitiesQuery = SqlQueryBuilder.BuildGetAutoentitiesQuery();
306+
Dictionary<string, DbConnectionParam> parameters = new()
307+
{
308+
{ $"{BaseQueryStructure.PARAM_NAME_PREFIX}include_pattern", new(include, null, SqlDbType.NVarChar) },
309+
{ $"{BaseQueryStructure.PARAM_NAME_PREFIX}exclude_pattern", new(exclude, null, SqlDbType.NVarChar) },
310+
{ $"{BaseQueryStructure.PARAM_NAME_PREFIX}name_pattern", new(namePattern, null, SqlDbType.NVarChar) }
311+
};
312+
313+
_logger.LogInformation("Query for Autoentities is being executed with the following parameters.");
314+
_logger.LogInformation($"Autoentities include pattern: {include}");
315+
_logger.LogInformation($"Autoentities exclude pattern: {exclude}");
316+
_logger.LogInformation($"Autoentities name pattern: {namePattern}");
317+
318+
JsonArray? resultArray = await QueryExecutor.ExecuteQueryAsync(
319+
sqltext: getAutoentitiesQuery,
320+
parameters: parameters,
321+
dataReaderHandler: QueryExecutor.GetJsonArrayAsync,
322+
dataSourceName: _dataSourceName);
323+
324+
return resultArray;
325+
}
293326
}
294327
}

‎src/Core/Services/MetadataProviders/SqlMetadataProvider.cs‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -307,6 +307,11 @@ public string GetEntityName(string graphQLType)
307307
public async Task InitializeAsync()
308308
{
309309
System.Diagnostics.Stopwatch timer = System.Diagnostics.Stopwatch.StartNew();
310+
if (GetDatabaseType() == DatabaseType.MSSQL)
311+
{
312+
await GenerateAutoentitiesIntoEntities();
313+
}
314+
310315
GenerateDatabaseObjectForEntities();
311316
if (_isValidateOnly)
312317
{
@@ -686,6 +691,15 @@ private void GenerateDatabaseObjectForEntities()
686691
}
687692
}
688693

694+
/// <summary>
695+
/// Creates entities for each table that is found, based on the autoentity configuration.
696+
/// This method is only called for tables in MsSql.
697+
/// </summary>
698+
protected virtual Task GenerateAutoentitiesIntoEntities()
699+
{
700+
throw new NotSupportedException($"{GetType().Name} does not support Autoentities yet.");
701+
}
702+
689703
protected void PopulateDatabaseObjectForEntity(
690704
Entity entity,
691705
string entityName,

‎src/Service.Tests/UnitTests/SqlMetadataProviderUnitTests.cs‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -588,5 +588,68 @@ private static async Task SetupTestFixtureAndInferMetadata()
588588
await ResetDbStateAsync();
589589
await _sqlMetadataProvider.InitializeAsync();
590590
}
591+
592+
/// <summary>
593+
/// Ensures that the query that returns the tables that will be generated
594+
/// into entities from the autoentities configuration returns the expected result.
595+
/// </summary>
596+
[DataTestMethod, TestCategory(TestCategory.MSSQL)]
597+
[DataRow(new string[] { "dbo.%book%" }, new string[] { }, "{schema}.{object}.books", new string[] { "book" }, "")]
598+
[DataRow(new string[] { "dbo.%publish%" }, new string[] { }, "{schema}.{object}", new string[] { "publish" }, "")]
599+
[DataRow(new string[] { "dbo.%book%" }, new string[] { "dbo.%books%" }, "{schema}_{object}_exclude_books", new string[] { "book" }, "books")]
600+
[DataRow(new string[] { "dbo.%book%", "dbo.%publish%" }, new string[] { }, "{object}", new string[] { "book", "publish" }, "")]
601+
[DataRow(new string[] { }, new string[] { "dbo.%book%" }, "{object}", new string[] { "" }, "book")]
602+
public async Task CheckAutoentitiesQuery(string[] include, string[] exclude, string name, string[] includeObject, string excludeObject)
603+
{
604+
// Arrange
605+
DatabaseEngine = TestCategory.MSSQL;
606+
TestHelper.SetupDatabaseEnvironment(DatabaseEngine);
607+
RuntimeConfig runtimeConfig = SqlTestHelper.SetupRuntimeConfig();
608+
Autoentity autoentity = new(new AutoentityPatterns(include, exclude, name), null, null);
609+
Dictionary<string, Autoentity> dictAutoentity = new()
610+
{
611+
{ "autoentity", autoentity }
612+
};
613+
RuntimeConfig configWithAutoentity = runtimeConfig with
614+
{
615+
Autoentities = new RuntimeAutoentities(dictAutoentity)
616+
};
617+
RuntimeConfigProvider runtimeConfigProvider = TestHelper.GenerateInMemoryRuntimeConfigProvider(configWithAutoentity);
618+
SetUpSQLMetadataProvider(runtimeConfigProvider);
619+
620+
await _sqlMetadataProvider.InitializeAsync();
621+
622+
// Act
623+
MsSqlMetadataProvider metadataProvider = (MsSqlMetadataProvider)_sqlMetadataProvider;
624+
JsonArray resultArray = await metadataProvider.QueryAutoentitiesAsync(autoentity);
625+
626+
// Assert
627+
Assert.IsNotNull(resultArray);
628+
foreach (JsonObject resultObject in resultArray)
629+
{
630+
bool includedObjectExists = false;
631+
foreach (string included in includeObject)
632+
{
633+
if (resultObject["object"].ToString().Contains(included))
634+
{
635+
includedObjectExists = true;
636+
Assert.AreNotEqual(name, resultObject["entity_name"].ToString(), "Name returned by query should not include {schema} or {object}.");
637+
if (include.Length > 0)
638+
{
639+
Assert.AreEqual(expected: "dbo", actual: resultObject["schema"].ToString(), "Query does not return expected schema.");
640+
}
641+
642+
if (exclude.Length > 0)
643+
{
644+
Assert.IsTrue(!resultObject["object"].ToString().Contains(excludeObject), "Query returns pattern that should be excluded.");
645+
}
646+
}
647+
}
648+
649+
Assert.IsTrue(includedObjectExists, "Query does not return expected object.");
650+
}
651+
652+
TestHelper.UnsetAllDABEnvironmentVariables();
653+
}
591654
}
592655
}

0 commit comments

Comments
 (0)