Skip to content

Commit 641f1c5

Browse files
severussundarseverussundar
committed
adds single round trip approach for mssql
1 parent 56caa9d commit 641f1c5

14 files changed

Lines changed: 152 additions & 53 deletions

‎src/Core/Resolvers/AuthorizationPolicyHelpers.cs‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,15 @@ public static void ProcessAuthorizationPolicies(
4646
}
4747

4848
string clientRoleHeader = roleHeaderValue.ToString();
49-
List<EntityActionOperation> elementalOperations = ResolveCompoundOperationToElementalOperations(operationType);
49+
HashSet<EntityActionOperation> elementalOperations = ResolveCompoundOperationToElementalOperations(operationType).ToHashSet();
50+
51+
// add appropriate comments here
52+
if(queryStructure.ApiRequestType is ApiType.REST &&
53+
sqlMetadataProvider.GetDatabaseType() is DatabaseType.MSSQL &&
54+
sqlMetadataProvider.EntityToDatabaseObject[queryStructure.EntityName].SourceType is not EntitySourceType.StoredProcedure)
55+
{
56+
elementalOperations.Add(EntityActionOperation.Read);
57+
}
5058

5159
foreach (EntityActionOperation elementalOperation in elementalOperations)
5260
{

‎src/Core/Resolvers/BaseQueryStructure.cs‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33

44
using Azure.DataApiBuilder.Auth;
55
using Azure.DataApiBuilder.Config.DatabasePrimitives;
6+
using Azure.DataApiBuilder.Config.ObjectModel;
67
using Azure.DataApiBuilder.Core.Models;
78
using Azure.DataApiBuilder.Core.Services;
89
using Azure.DataApiBuilder.Service.GraphQLBuilder;
@@ -13,6 +14,12 @@ namespace Azure.DataApiBuilder.Core.Resolvers
1314
{
1415
public class BaseQueryStructure
1516
{
17+
18+
/// <summary>
19+
/// Type of the API request - REST or GraphQL.
20+
/// </summary>
21+
public ApiType ApiRequestType { get; set; }
22+
1623
/// <summary>
1724
/// The Entity name associated with this query as appears in the config file.
1825
/// </summary>
@@ -75,7 +82,8 @@ public BaseQueryStructure(
7582
GQLFilterParser gQLFilterParser,
7683
List<Predicate>? predicates = null,
7784
string entityName = "",
78-
IncrementingInteger? counter = null)
85+
IncrementingInteger? counter = null,
86+
ApiType apiRequestType = ApiType.GraphQL)
7987
{
8088
Columns = new();
8189
Parameters = new();
@@ -84,6 +92,7 @@ public BaseQueryStructure(
8492
MetadataProvider = metadataProvider;
8593
GraphQLFilterParser = gQLFilterParser;
8694
AuthorizationResolver = authorizationResolver;
95+
ApiRequestType = apiRequestType;
8796

8897
// Default the alias to the empty string since this base constructor
8998
// is called for requests other than Find operations. We only use

‎src/Core/Resolvers/MsSqlQueryBuilder.cs‎

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -75,13 +75,16 @@ public string Build(SqlInsertStructure structure)
7575
// Columns whose values are provided in the request body - to be inserted into the record.
7676
string insertColumns = Build(structure.InsertColumns);
7777

78+
// add appropriate comments here
79+
string selectQueryPredicates = JoinPredicateStrings(structure.GetDbPolicyForOperation(EntityActionOperation.Read));
80+
7881
// Values to be inserted into the entity.
7982
string values = dbPolicypredicates.Equals(BASE_PREDICATE) ?
8083
$"VALUES ({string.Join(", ", structure.Values)});" : $"SELECT {insertColumns} FROM (VALUES({string.Join(", ", structure.Values)})) T({insertColumns}) WHERE {dbPolicypredicates};";
8184

8285
// Final insert query to be executed against the database.
8386
StringBuilder insertQuery = new();
84-
if (!isInsertDMLTriggerEnabled)
87+
if (structure.ApiRequestType is ApiType.GraphQL && !isInsertDMLTriggerEnabled)
8588
{
8689
// When there is no DML trigger enabled on the table for insert operation, we can use OUTPUT clause to return the data.
8790
insertQuery.Append($"INSERT INTO {tableName} ({insertColumns}) OUTPUT " +
@@ -142,7 +145,12 @@ public string Build(SqlInsertStructure structure)
142145
// If there is an autogenerated column in the PK, we will add an additional WHERE condition for it.
143146
// Using SCOPE_IDENTITY() method provided by sql server,
144147
// we can get the last generated value of the autogenerated column.
145-
subsequentSelect.Append($"WHERE {tableName}.{QuoteIdentifier(autoGenPKColumn)} = SCOPE_IDENTITY()");
148+
subsequentSelect.Append($"WHERE {tableName}.{QuoteIdentifier(autoGenPKColumn)} = SCOPE_IDENTITY() ");
149+
subsequentSelect.Append($"AND {selectQueryPredicates}");
150+
}
151+
else
152+
{
153+
subsequentSelect.Append($"WHERE {selectQueryPredicates}");
146154
}
147155

148156
insertQuery.Append(subsequentSelect.ToString());
@@ -265,9 +273,11 @@ public string Build(SqlUpsertQueryStructure structure)
265273
// Predicates by virtue of PK + database policy.
266274
string updatePredicates = JoinPredicateStrings(pkPredicates, structure.GetDbPolicyForOperation(EntityActionOperation.Update));
267275

276+
string selectPredicates = JoinPredicateStrings(pkPredicates, structure.GetDbPolicyForOperation(EntityActionOperation.Read));
277+
268278
string updateOperations = Build(structure.UpdateOperations, ", ");
269279
string columnsToBeReturned =
270-
MakeOutputColumns(structure.OutputColumns, isUpdateTriggerEnabled ? string.Empty : OutputQualifier.Inserted.ToString());
280+
MakeOutputColumns(structure.OutputColumns, (isUpdateTriggerEnabled || structure.ApiRequestType is ApiType.REST ) ? string.Empty : OutputQualifier.Inserted.ToString());
271281
string queryToGetCountOfRecordWithPK = $"SELECT COUNT(*) as {COUNT_ROWS_WITH_GIVEN_PK} FROM {tableName} WHERE {pkPredicates}";
272282

273283
// Query to get the number of records with a given PK.
@@ -285,13 +295,13 @@ public string Build(SqlUpsertQueryStructure structure)
285295
$"UPDATE {tableName} " +
286296
$"SET {updateOperations} ");
287297

288-
if (isUpdateTriggerEnabled)
298+
if (structure.ApiRequestType is ApiType.REST || isUpdateTriggerEnabled)
289299
{
290300
// If a trigger is enabled on the entity, we cannot use OUTPUT clause to return the record.
291301
// In such a case, we will use a subsequent select query to get the record. By the time the subsequent select executes,
292302
// the trigger would have already executed and we get the data as it is present in the table.
293303
updateQuery.Append($"WHERE {updatePredicates};");
294-
string subsequentSelect = $"SELECT {columnsToBeReturned} FROM {tableName} WHERE {updatePredicates};";
304+
string subsequentSelect = $"SELECT {columnsToBeReturned} FROM {tableName} WHERE {selectPredicates};";
295305
updateQuery.Append(subsequentSelect);
296306
}
297307
else
@@ -321,8 +331,9 @@ public string Build(SqlUpsertQueryStructure structure)
321331

322332
bool isInsertTriggerEnabled = sourceDefinition.IsInsertDMLTriggerEnabled;
323333
// We can only use OUTPUT clause to return inserted data when there is no trigger enabled on the entity.
324-
if (!isInsertTriggerEnabled)
334+
if (!isInsertTriggerEnabled && structure.ApiRequestType is ApiType.GraphQL)
325335
{
336+
// use output clause here
326337
if (isUpdateTriggerEnabled)
327338
{
328339
// This is just an optimisation. If update trigger is enabled, then this build method had created
@@ -332,9 +343,10 @@ public string Build(SqlUpsertQueryStructure structure)
332343

333344
insertQuery.Append($"OUTPUT {columnsToBeReturned}");
334345
}
346+
335347
// If an insert trigger is enabled but there was no update trigger enabled,
336348
// we need to generate columnsToBeReturned without the 'Inserted' prefix on each column.
337-
else if (!isUpdateTriggerEnabled)
349+
else if (isInsertTriggerEnabled && !isUpdateTriggerEnabled)
338350
{
339351
// This is again just an optimisation. If update trigger was enabled, then the columnsToBeReturned would
340352
// have already been created without any prefix.
@@ -349,11 +361,11 @@ public string Build(SqlUpsertQueryStructure structure)
349361
// Append the values to be inserted to the insertQuery.
350362
insertQuery.Append(fetchColumnValuesQuery);
351363

352-
if (isInsertTriggerEnabled)
364+
if (isInsertTriggerEnabled || structure.ApiRequestType is ApiType.REST)
353365
{
354366
// Since a trigger is enabled, a subsequent select query is to be executed to get the inserted record.
355367
// By the time the subsequent select executes, the trigger would have already executed and we get the data as it is present in the table.
356-
string subsequentSelect = $"SELECT {columnsToBeReturned} from {tableName} WHERE {pkPredicates};";
368+
string subsequentSelect = $"SELECT {columnsToBeReturned} from {tableName} WHERE {pkPredicates} AND {selectPredicates};";
357369
insertQuery.Append(subsequentSelect);
358370
}
359371

‎src/Core/Resolvers/MsSqlQueryExecutor.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -289,7 +289,7 @@ public override async Task<DbResultSet> GetMultipleResultSetsIfAnyAsync(
289289
statusCode: HttpStatusCode.Forbidden,
290290
subStatusCode: DataApiBuilderException.SubStatusCodes.DatabasePolicyFailure);
291291
}
292-
292+
293293
// This is used as an identifier to distinguish between update/insert operations.
294294
// Later helps to add location header in case of insert operation.
295295
dbResultSet.ResultProperties.Add(SqlMutationEngine.IS_UPDATE_RESULT_SET, true);

‎src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs‎

Lines changed: 48 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -55,17 +55,23 @@ public abstract class BaseSqlQueryStructure : BaseQueryStructure
5555
/// </summary>
5656
public HashSet<string> FieldsReferencedInDbPolicyForCreateAction { get; set; } = new();
5757

58+
/// <summary>
59+
///
60+
/// </summary>
61+
protected string RoleName { get; set; } = "";
62+
5863
public BaseSqlQueryStructure(
5964
ISqlMetadataProvider metadataProvider,
6065
IAuthorizationResolver authorizationResolver,
6166
GQLFilterParser gQLFilterParser,
67+
ApiType apiRequestType,
6268
List<Predicate>? predicates = null,
6369
string entityName = "",
6470
IncrementingInteger? counter = null,
6571
HttpContext? httpContext = null,
6672
EntityActionOperation operationType = EntityActionOperation.None
6773
)
68-
: base(metadataProvider, authorizationResolver, gQLFilterParser, predicates, entityName, counter)
74+
: base(metadataProvider, authorizationResolver, gQLFilterParser, predicates, entityName, counter, apiRequestType)
6975
{
7076
Joins = new();
7177

@@ -74,6 +80,8 @@ public BaseSqlQueryStructure(
7480
// For GraphQL read operation, the database policy predicates are added later in the Sql{*}QueryStructure classes.
7581
if (httpContext is not null)
7682
{
83+
RoleName = httpContext.Request.Headers["X-MS-API-ROLE"];
84+
7785
AuthorizationPolicyHelpers.ProcessAuthorizationPolicies(
7886
operationType,
7987
this,
@@ -318,22 +326,49 @@ public List<string> AllColumns()
318326
protected List<LabelledColumn> GenerateOutputColumns()
319327
{
320328
List<LabelledColumn> outputColumns = new();
321-
foreach (string columnName in GetUnderlyingSourceDefinition().Columns.Keys)
329+
330+
// add comments here
331+
if(ApiRequestType is ApiType.REST &&
332+
MetadataProvider.GetDatabaseType() is DatabaseType.MSSQL &&
333+
MetadataProvider.EntityToDatabaseObject[EntityName].SourceType is not EntitySourceType.StoredProcedure)
322334
{
323-
if (!MetadataProvider.TryGetExposedColumnName(
324-
entityName: EntityName,
325-
backingFieldName: columnName,
326-
out string? exposedName))
335+
foreach (string columnName in AuthorizationResolver.GetAllowedExposedColumns(EntityName, RoleName, EntityActionOperation.Read))
327336
{
328-
continue;
337+
if (!MetadataProvider.TryGetExposedColumnName(
338+
entityName: EntityName,
339+
backingFieldName: columnName,
340+
out string? exposedName))
341+
{
342+
continue;
343+
}
344+
345+
outputColumns.Add(new(
346+
tableSchema: DatabaseObject.SchemaName,
347+
tableName: DatabaseObject.Name,
348+
columnName: columnName,
349+
label: exposedName!,
350+
tableAlias: SourceAlias));
329351
}
352+
}
353+
else
354+
{
355+
foreach (string columnName in GetUnderlyingSourceDefinition().Columns.Keys)
356+
{
357+
if (!MetadataProvider.TryGetExposedColumnName(
358+
entityName: EntityName,
359+
backingFieldName: columnName,
360+
out string? exposedName))
361+
{
362+
continue;
363+
}
330364

331-
outputColumns.Add(new(
332-
tableSchema: DatabaseObject.SchemaName,
333-
tableName: DatabaseObject.Name,
334-
columnName: columnName,
335-
label: exposedName!,
336-
tableAlias: SourceAlias));
365+
outputColumns.Add(new(
366+
tableSchema: DatabaseObject.SchemaName,
367+
tableName: DatabaseObject.Name,
368+
columnName: columnName,
369+
label: exposedName!,
370+
tableAlias: SourceAlias));
371+
}
337372
}
338373

339374
return outputColumns;

‎src/Core/Resolvers/Sql Query Structures/SqlDeleteQueryStructure.cs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,12 +23,14 @@ public SqlDeleteStructure(
2323
IAuthorizationResolver authorizationResolver,
2424
GQLFilterParser gQLFilterParser,
2525
IDictionary<string, object?> mutationParams,
26-
HttpContext httpContext)
26+
HttpContext httpContext,
27+
ApiType apiRequestType)
2728
: base(
2829
metadataProvider: sqlMetadataProvider,
2930
authorizationResolver: authorizationResolver,
3031
gQLFilterParser: gQLFilterParser,
3132
entityName: entityName,
33+
apiRequestType: apiRequestType,
3234
httpContext: httpContext,
3335
operationType: EntityActionOperation.Delete)
3436
{

‎src/Core/Resolvers/Sql Query Structures/SqlExecuteQueryStructure.cs‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@
44
using System.Net;
55
using Azure.DataApiBuilder.Auth;
66
using Azure.DataApiBuilder.Config.DatabasePrimitives;
7+
using Azure.DataApiBuilder.Config.ObjectModel;
78
using Azure.DataApiBuilder.Core.Models;
89
using Azure.DataApiBuilder.Core.Services;
910
using Azure.DataApiBuilder.Service.Exceptions;
@@ -31,8 +32,9 @@ public SqlExecuteStructure(
3132
ISqlMetadataProvider sqlMetadataProvider,
3233
IAuthorizationResolver authorizationResolver,
3334
GQLFilterParser gQLFilterParser,
34-
IDictionary<string, object?> requestParams)
35-
: base(sqlMetadataProvider, authorizationResolver, gQLFilterParser, entityName: entityName)
35+
IDictionary<string, object?> requestParams,
36+
ApiType apiRequestType)
37+
: base(sqlMetadataProvider, authorizationResolver, gQLFilterParser, entityName: entityName, apiRequestType: apiRequestType)
3638
{
3739
StoredProcedureDefinition storedProcedureDefinition = GetUnderlyingStoredProcedureDefinition();
3840
ProcedureParameters = new();

‎src/Core/Resolvers/Sql Query Structures/SqlInsertQueryStructure.cs‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,8 @@ HttpContext httpContext
4848
authorizationResolver,
4949
gQLFilterParser,
5050
GQLMutArgumentToDictParams(context, CreateMutationBuilder.INPUT_ARGUMENT_NAME, mutationParams),
51-
httpContext)
51+
httpContext,
52+
ApiType.GraphQL)
5253
{ }
5354

5455
public SqlInsertStructure(
@@ -57,12 +58,14 @@ public SqlInsertStructure(
5758
IAuthorizationResolver authorizationResolver,
5859
GQLFilterParser gQLFilterParser,
5960
IDictionary<string, object?> mutationParams,
60-
HttpContext httpContext
61+
HttpContext httpContext,
62+
ApiType apiRequestType
6163
)
6264
: base(
6365
metadataProvider: sqlMetadataProvider,
6466
authorizationResolver: authorizationResolver,
6567
gQLFilterParser: gQLFilterParser,
68+
apiRequestType: apiRequestType,
6669
entityName: entityName,
6770
httpContext: httpContext,
6871
operationType: EntityActionOperation.Create)

‎src/Core/Resolvers/Sql Query Structures/SqlQueryStructure.cs‎

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -138,7 +138,8 @@ public SqlQueryStructure(
138138
predicates: null,
139139
entityName: context.EntityName,
140140
counter: new IncrementingInteger(),
141-
httpContext: httpContext)
141+
httpContext: httpContext,
142+
apiRequestType: ApiType.REST)
142143
{
143144
IsListQuery = context.IsMany;
144145
SourceAlias = $"{DatabaseObject.SchemaName}_{DatabaseObject.Name}";
@@ -260,7 +261,8 @@ private SqlQueryStructure(
260261
gQLFilterParser,
261262
predicates: null,
262263
entityName: entityName,
263-
counter: counter
264+
counter: counter,
265+
apiRequestType: ApiType.GraphQL
264266
)
265267
{
266268
_ctx = ctx;
@@ -415,13 +417,16 @@ private SqlQueryStructure(
415417
ISqlMetadataProvider metadataProvider,
416418
IAuthorizationResolver authorizationResolver,
417419
GQLFilterParser gQLFilterParser,
420+
ApiType apiRequestType,
418421
List<Predicate>? predicates = null,
419422
string entityName = "",
420423
IncrementingInteger? counter = null,
421424
HttpContext? httpContext = null)
422425
: base(metadataProvider,
423426
authorizationResolver,
424-
gQLFilterParser, predicates,
427+
gQLFilterParser,
428+
apiRequestType,
429+
predicates,
425430
entityName,
426431
counter,
427432
httpContext,

‎src/Core/Resolvers/Sql Query Structures/SqlUpdateQueryStructure.cs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,13 +34,15 @@ public SqlUpdateStructure(
3434
ISqlMetadataProvider sqlMetadataProvider,
3535
IAuthorizationResolver authorizationResolver,
3636
GQLFilterParser gQLFilterParser,
37+
ApiType apiRequestType,
3738
IDictionary<string, object?> mutationParams,
3839
HttpContext httpContext,
3940
bool isIncrementalUpdate)
4041
: base(
4142
metadataProvider: sqlMetadataProvider,
4243
authorizationResolver: authorizationResolver,
4344
gQLFilterParser: gQLFilterParser,
45+
apiRequestType: apiRequestType,
4446
entityName: entityName,
4547
httpContext: httpContext,
4648
operationType: EntityActionOperation.Update)
@@ -100,6 +102,7 @@ public SqlUpdateStructure(
100102
metadataProvider: sqlMetadataProvider,
101103
authorizationResolver: authorizationResolver,
102104
gQLFilterParser: gQLFilterParser,
105+
apiRequestType: ApiType.GraphQL,
103106
entityName: entityName,
104107
httpContext: httpContext,
105108
operationType: EntityActionOperation.Update)

0 commit comments

Comments
 (0)