When databasename is empty it should not be added to prefix. (#1605)

rohkhann · seantleonard · web-flow · commit 60e38a2a3c2c · 2023-07-28T21:47:37.000Z
## Why make this change? - Reference associated issue using `#` syntax. e.g. Closes #XX - Include summary (1-2 sentences) of linked issue to avoid redirecting reviewers to different pages. Fixes issue mentioned in : #1604 ## What is this change? - Summary of how your changes work to give reviewers context of your intent. Added check to see if databasename is null or empty then we will not add it to the table prefix. ## How was this tested? - [ ] Integration Tests existing integration tests test this path of code - [ ] Unit Tests Added new test called checkTablePrefix. Chose to go with internalsvisibleto route as the main goal was to test gettableprefix method and ROI on moqing out components of the public method initializeasync to do the test was low. --------- Co-authored-by: Sean Leonard <sean.leonard@microsoft.com>
diff --git a/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs b/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs
@@ -5,6 +5,7 @@
 using System.Data.Common;
 using System.Diagnostics.CodeAnalysis;
 using System.Net;
+using System.Runtime.CompilerServices;
 using System.Text;
 using System.Text.Json;
 using System.Text.Json.Nodes;
@@ -18,6 +19,7 @@
 using Microsoft.Extensions.Logging;
 using static Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLNaming;
 
+[assembly: InternalsVisibleTo("Azure.DataApiBuilder.Service.Tests")]
 namespace Azure.DataApiBuilder.Core.Services
 {
     /// <summary>
@@ -278,8 +280,6 @@ private async Task FillSchemaForStoredProcedureAsync(
             await QueryExecutor.SetManagedIdentityAccessTokenIfAnyAsync(conn);
             await conn.OpenAsync();
 
-            string tablePrefix = GetTablePrefix(conn.Database, schemaName);
-
             string[] procedureRestrictions = new string[NUMBER_OF_RESTRICTIONS];
 
             // To restrict the parameters for the current stored procedure, specify its name
@@ -1140,21 +1140,38 @@ private async Task<DataTable> FillSchemaForTableAsync(
             };
 
             string tablePrefix = GetTablePrefix(conn.Database, schemaName);
+            string queryPrefix = string.IsNullOrEmpty(tablePrefix) ? string.Empty : $"{tablePrefix}.";
             selectCommand.CommandText
-                = $"SELECT * FROM {tablePrefix}.{SqlQueryBuilder.QuoteIdentifier(tableName)}";
+                = $"SELECT * FROM {queryPrefix}{SqlQueryBuilder.QuoteIdentifier(tableName)}";
             adapterForTable.SelectCommand = selectCommand;
 
             DataTable[] dataTable = adapterForTable.FillSchema(EntitiesDataSet, SchemaType.Source, tableName);
             return dataTable[0];
         }
 
-        private string GetTablePrefix(string databaseName, string schemaName)
+        internal string GetTablePrefix(string databaseName, string schemaName)
         {
-            StringBuilder tablePrefix = new(SqlQueryBuilder.QuoteIdentifier(databaseName));
-            if (!string.IsNullOrEmpty(schemaName))
+            StringBuilder tablePrefix = new();
+
+            if (!string.IsNullOrEmpty(databaseName))
+            {
+                // Determine databaseName for prefix.
+                databaseName = SqlQueryBuilder.QuoteIdentifier(databaseName);
+                tablePrefix.Append(databaseName);
+
+                if (!string.IsNullOrEmpty(schemaName))
+                {
+                    // Determine schemaName for prefix.
+                    schemaName = SqlQueryBuilder.QuoteIdentifier(schemaName);
+                    tablePrefix.Append($".{schemaName}");
+                }
+            }
+            else if (!string.IsNullOrEmpty(schemaName))
             {
+                // Determine schemaName for prefix.
                 schemaName = SqlQueryBuilder.QuoteIdentifier(schemaName);
-                tablePrefix.Append($".{schemaName}");
+                // Database name is empty we just need the schema name.
+                tablePrefix.Append(schemaName);
             }
 
             return tablePrefix.ToString();
diff --git a/src/Service.Tests/Unittests/SqlMetadataProviderUnitTests.cs b/src/Service.Tests/Unittests/SqlMetadataProviderUnitTests.cs
@@ -8,10 +8,12 @@
 using Azure.DataApiBuilder.Config.ObjectModel;
 using Azure.DataApiBuilder.Core.Authorization;
 using Azure.DataApiBuilder.Core.Configurations;
+using Azure.DataApiBuilder.Core.Resolvers;
 using Azure.DataApiBuilder.Core.Services;
 using Azure.DataApiBuilder.Service.Exceptions;
 using Azure.DataApiBuilder.Service.Tests.Configuration;
 using Azure.DataApiBuilder.Service.Tests.SqlTests;
+using Microsoft.Data.SqlClient;
 using Microsoft.Extensions.Logging;
 using Microsoft.VisualStudio.TestTools.UnitTesting;
 using Moq;
@@ -83,6 +85,31 @@ public async Task CheckExceptionForBadConnectionStringForMsSql(string connection
             await CheckExceptionForBadConnectionStringHelperAsync(DatabaseEngine, connectionString);
         }
 
+        /// <summary>
+        /// <code>Do: </code> Tests with different combinations of connection string to ensure
+        /// tableprefix generated is correctly.
+        /// <code>Check: </code> Making sure table prefix matches expected prefix.
+        /// </summary>
+        [DataTestMethod]
+        [DataRow("", "", "")]
+        [DataRow("", "model", "[model]")]
+        [DataRow("TestDB", "", "[TestDB]")]
+        [DataRow("TestDB", "model", "[TestDB].[model]")]
+        public void CheckTablePrefix(string databaseName, string schemaName, string expectedPrefix)
+        {
+            TestHelper.SetupDatabaseEnvironment(TestCategory.MSSQL);
+            RuntimeConfig baseConfigFromDisk = SqlTestHelper.SetupRuntimeConfig();
+            RuntimeConfigProvider runtimeConfigProvider = TestHelper.GenerateInMemoryRuntimeConfigProvider(baseConfigFromDisk);
+
+            ILogger<ISqlMetadataProvider> sqlMetadataLogger = new Mock<ILogger<ISqlMetadataProvider>>().Object;
+            Mock<IQueryExecutor> queryExecutor = new();
+            IQueryBuilder queryBuilder = new MsSqlQueryBuilder();
+
+            SqlMetadataProvider<SqlConnection, SqlDataAdapter, SqlCommand> provider = new MsSqlMetadataProvider(runtimeConfigProvider, queryExecutor.Object, queryBuilder, sqlMetadataLogger);
+            string tableprefix = provider.GetTablePrefix(databaseName, schemaName);
+            Assert.AreEqual(tableprefix, expectedPrefix);
+        }
+
         /// <summary>
         /// <code>Do: </code> Load runtimeConfig and set connection string and db type
         /// according to data row.
