Skip to content

Commit e917ce9

Browse files
Aniruddh25abhishekkumamsseantleonard
authored
Support time data type in MsSql (#1473)
## Why make this change? - Closes #1401 ## What is this change? - `time` data type is detected as `TimeSpan` in C#. Add respective additional case for this type where ever types are handled. - Using the Hotchocolate.Types.NodaTime to use the type NodaTime.LocalTime to display the time of the day for the corresponding time stored in the DB. - Fixed some testcases to add testing for datetimeoffset_type column as well. ## Additional Fixes - In GraphQL, while parsing a values of datetime, datetime2, they should be treated as UTC, otherwise HotChocolate tries to convert into UTC and max values stored in the db then cause overflow exceptions. - Use InvariantInfo format provider while parsing ## How was this tested? Work in progress - [X] Integration Tests - in `SupportedType` - [X] Unit Tests - `StoredProcedureBuilderTests` ## Sample Request(s) ![image](https://github.com/Azure/data-api-builder/assets/102276754/bc22bff9-2e99-423d-961a-7e589c9a3143) **No Implicit Time Zone Conversion: you see exactly what you have on DB, same for insert/update** ![image](https://github.com/Azure/data-api-builder/assets/102276754/5ad8c1e1-fcb3-4e50-86c8-f0b2cca24954) ![image](https://github.com/Azure/data-api-builder/assets/102276754/5656a966-4664-44f4-aa3f-7305e21860b6) --------- Co-authored-by: Abhishek Kumar <[email protected]> Co-authored-by: abhishekkumams <[email protected]> Co-authored-by: Sean Leonard <[email protected]>
1 parent 33774c2 commit e917ce9

27 files changed

Lines changed: 388 additions & 127 deletions

‎src/Core/Azure.DataApiBuilder.Core.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111
<PackageReference Include="HotChocolate" />
1212
<PackageReference Include="HotChocolate.AspNetCore" />
1313
<PackageReference Include="HotChocolate.AspNetCore.Authorization" />
14+
<PackageReference Include="HotChocolate.Types.NodaTime" />
1415
<PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" />
1516
<PackageReference Include="Microsoft.Azure.Cosmos" />
1617
<PackageReference Include="Microsoft.Data.SqlClient" />

‎src/Core/Parsers/EdmModelBuilder.cs‎

Lines changed: 2 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -67,30 +67,9 @@ SourceDefinition sourceDefinition
6767
// each column represents a property of the current entity we are adding
6868
foreach (string column in sourceDefinition.Columns.Keys)
6969
{
70-
// need to convert our column system type to an Edm type
7170
Type columnSystemType = sourceDefinition.Columns[column].SystemType;
72-
EdmPrimitiveTypeKind type = EdmPrimitiveTypeKind.None;
73-
if (columnSystemType.IsArray)
74-
{
75-
columnSystemType = columnSystemType.GetElementType()!;
76-
}
77-
78-
type = columnSystemType.Name switch
79-
{
80-
"String" => EdmPrimitiveTypeKind.String,
81-
"Guid" => EdmPrimitiveTypeKind.Guid,
82-
"Byte" => EdmPrimitiveTypeKind.Byte,
83-
"Int16" => EdmPrimitiveTypeKind.Int16,
84-
"Int32" => EdmPrimitiveTypeKind.Int32,
85-
"Int64" => EdmPrimitiveTypeKind.Int64,
86-
"Single" => EdmPrimitiveTypeKind.Single,
87-
"Double" => EdmPrimitiveTypeKind.Double,
88-
"Decimal" => EdmPrimitiveTypeKind.Decimal,
89-
"Boolean" => EdmPrimitiveTypeKind.Boolean,
90-
"DateTime" or "DateTimeOffset" => EdmPrimitiveTypeKind.DateTimeOffset,
91-
"Date" => EdmPrimitiveTypeKind.Date,
92-
_ => throw new ArgumentException($"Column type {columnSystemType.Name} not yet supported."),
93-
};
71+
// need to convert our column system type to an Edm type
72+
EdmPrimitiveTypeKind type = TypeHelper.GetEdmPrimitiveTypeFromSystemType(columnSystemType);
9473

9574
// The mapped (aliased) field name defined in the runtime config is used to create a representative
9675
// OData StructuralProperty. The created property is then added to the EdmEntityType.

‎src/Core/Parsers/ODataASTVisitor.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -143,6 +143,8 @@ private static object GetParamWithSystemType(string param, IEdmTypeReference edm
143143
return DateTimeOffset.Parse(param);
144144
case EdmPrimitiveTypeKind.String:
145145
return param;
146+
case EdmPrimitiveTypeKind.TimeOfDay:
147+
return TimeOnly.Parse(param);
146148
default:
147149
// should never happen due to the config being validated for correct types
148150
throw new NotSupportedException($"{edmType} is not supported");

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

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
// Licensed under the MIT License.
33

44
using System.Data;
5+
using System.Globalization;
56
using System.Net;
67
using Azure.DataApiBuilder.Auth;
78
using Azure.DataApiBuilder.Config.DatabasePrimitives;
@@ -360,10 +361,12 @@ protected static object ParseParamAsSystemType(string param, Type systemType)
360361
"Double" => double.Parse(param),
361362
"Decimal" => decimal.Parse(param),
362363
"Boolean" => bool.Parse(param),
363-
"DateTime" => DateTimeOffset.Parse(param),
364-
"DateTimeOffset" => DateTimeOffset.Parse(param),
364+
"DateTime" => DateTimeOffset.Parse(param, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AssumeUniversal),
365+
"DateTimeOffset" => DateTimeOffset.Parse(param, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AssumeUniversal),
365366
"Date" => DateOnly.Parse(param),
366367
"Guid" => Guid.Parse(param),
368+
"TimeOnly" => TimeOnly.Parse(param),
369+
"TimeSpan" => TimeOnly.Parse(param),
367370
_ => throw new NotSupportedException($"{systemType.Name} is not supported")
368371
};
369372
}

‎src/Core/Services/ResolverMiddleware.cs‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
// Copyright (c) Microsoft Corporation.
22
// Licensed under the MIT License.
33

4+
using System.Globalization;
45
using System.Text.Json;
56
using Azure.DataApiBuilder.Core.Authorization;
67
using Azure.DataApiBuilder.Core.Models;
@@ -9,8 +10,10 @@
910
using HotChocolate.Execution;
1011
using HotChocolate.Language;
1112
using HotChocolate.Resolvers;
13+
using HotChocolate.Types.NodaTime;
1214
using Microsoft.AspNetCore.Http;
1315
using Microsoft.Extensions.Primitives;
16+
using NodaTime.Text;
1417
using static Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLTypes.SupportedTypes;
1518

1619
namespace Azure.DataApiBuilder.Core.Services
@@ -196,8 +199,9 @@ private static object PreParseLeaf(IMiddlewareContext context, string leafJson)
196199
{
197200
ByteType => byte.Parse(leafJson),
198201
SingleType => Single.Parse(leafJson),
199-
DateTimeType => DateTimeOffset.Parse(leafJson),
202+
DateTimeType => DateTimeOffset.Parse(leafJson, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AssumeUniversal),
200203
ByteArrayType => Convert.FromBase64String(leafJson),
204+
LocalTimeType => LocalTimePattern.ExtendedIso.Parse(leafJson).Value,
201205
_ => leafJson
202206
};
203207
}

‎src/Core/Services/TypeHelper.cs‎

Lines changed: 56 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
using System.Net;
66
using Azure.DataApiBuilder.Core.Services.OpenAPI;
77
using Azure.DataApiBuilder.Service.Exceptions;
8+
using Microsoft.OData.Edm;
89

910
namespace Azure.DataApiBuilder.Core.Services
1011
{
@@ -16,6 +17,10 @@ public static class TypeHelper
1617
{
1718
/// <summary>
1819
/// Maps .NET Framework types to DbType enum
20+
/// Not Adding a hard mapping for System.DateTime to DbType.DateTime as
21+
/// Hotchocolate only has Hotchocolate.Types.DateTime for DbType.DateTime/DateTime2/DateTimeOffset,
22+
/// which throws error when inserting/updating dateTime values due to type mismatch.
23+
/// Therefore, seperate logic exists for proper mapping conversion in BaseSqlQueryStructure.
1924
/// </summary>
2025
private static Dictionary<Type, DbType> _systemTypeToDbTypeMap = new()
2126
{
@@ -34,21 +39,10 @@ public static class TypeHelper
3439
[typeof(string)] = DbType.String,
3540
[typeof(char)] = DbType.StringFixedLength,
3641
[typeof(Guid)] = DbType.Guid,
42+
[typeof(DateTimeOffset)] = DbType.DateTimeOffset,
3743
[typeof(byte[])] = DbType.Binary,
38-
[typeof(byte?)] = DbType.Byte,
39-
[typeof(sbyte?)] = DbType.SByte,
40-
[typeof(short?)] = DbType.Int16,
41-
[typeof(ushort?)] = DbType.UInt16,
42-
[typeof(int?)] = DbType.Int32,
43-
[typeof(uint?)] = DbType.UInt32,
44-
[typeof(long?)] = DbType.Int64,
45-
[typeof(ulong?)] = DbType.UInt64,
46-
[typeof(float?)] = DbType.Single,
47-
[typeof(double?)] = DbType.Double,
48-
[typeof(decimal?)] = DbType.Decimal,
49-
[typeof(bool?)] = DbType.Boolean,
50-
[typeof(char?)] = DbType.StringFixedLength,
51-
[typeof(Guid?)] = DbType.Guid,
44+
[typeof(TimeOnly)] = DbType.Time,
45+
[typeof(TimeSpan)] = DbType.Time,
5246
[typeof(object)] = DbType.Object
5347
};
5448

@@ -77,6 +71,7 @@ public static class TypeHelper
7771
[typeof(Guid)] = JsonDataType.String,
7872
[typeof(byte[])] = JsonDataType.String,
7973
[typeof(TimeSpan)] = JsonDataType.String,
74+
[typeof(TimeOnly)] = JsonDataType.String,
8075
[typeof(object)] = JsonDataType.Object,
8176
[typeof(DateTime)] = JsonDataType.String,
8277
[typeof(DateTimeOffset)] = JsonDataType.String
@@ -108,14 +103,51 @@ public static class TypeHelper
108103
[SqlDbType.SmallInt] = typeof(short),
109104
[SqlDbType.SmallMoney] = typeof(decimal),
110105
[SqlDbType.Text] = typeof(string),
111-
[SqlDbType.Time] = typeof(TimeSpan),
106+
[SqlDbType.Time] = typeof(TimeOnly),
112107
[SqlDbType.Timestamp] = typeof(byte[]),
113108
[SqlDbType.TinyInt] = typeof(byte),
114109
[SqlDbType.UniqueIdentifier] = typeof(Guid),
115110
[SqlDbType.VarBinary] = typeof(byte[]),
116111
[SqlDbType.VarChar] = typeof(string)
117112
};
118113

114+
/// <summary>
115+
/// Given the system type, returns the corresponding primitive type kind.
116+
/// </summary>
117+
/// <param name="columnSystemType">Type of the column.</param>
118+
/// <returns>EdmPrimitiveTypeKind</returns>
119+
/// <exception cref="ArgumentException">Throws when the column</exception>
120+
public static EdmPrimitiveTypeKind GetEdmPrimitiveTypeFromSystemType(Type columnSystemType)
121+
{
122+
if (columnSystemType.IsArray)
123+
{
124+
columnSystemType = columnSystemType.GetElementType()!;
125+
}
126+
127+
EdmPrimitiveTypeKind type = columnSystemType.Name switch
128+
{
129+
"String" => EdmPrimitiveTypeKind.String,
130+
"Guid" => EdmPrimitiveTypeKind.Guid,
131+
"Byte" => EdmPrimitiveTypeKind.Byte,
132+
"Int16" => EdmPrimitiveTypeKind.Int16,
133+
"Int32" => EdmPrimitiveTypeKind.Int32,
134+
"Int64" => EdmPrimitiveTypeKind.Int64,
135+
"Single" => EdmPrimitiveTypeKind.Single,
136+
"Double" => EdmPrimitiveTypeKind.Double,
137+
"Decimal" => EdmPrimitiveTypeKind.Decimal,
138+
"Boolean" => EdmPrimitiveTypeKind.Boolean,
139+
"DateTime" => EdmPrimitiveTypeKind.DateTimeOffset,
140+
"DateTimeOffset" => EdmPrimitiveTypeKind.DateTimeOffset,
141+
"Date" => EdmPrimitiveTypeKind.Date,
142+
"TimeOnly" => EdmPrimitiveTypeKind.TimeOfDay,
143+
"TimeSpan" => EdmPrimitiveTypeKind.TimeOfDay,
144+
_ => throw new ArgumentException($"Column type" +
145+
$" {columnSystemType.Name} not yet supported.")
146+
};
147+
148+
return type;
149+
}
150+
119151
/// <summary>
120152
/// Converts the .NET Framework (System/CLR) type to JsonDataType.
121153
/// Primitive data types in the OpenAPI standard (OAS) are based on the types supported
@@ -151,6 +183,15 @@ public static JsonDataType GetJsonDataTypeFromSystemType(Type type)
151183
/// <returns>DbType for the given system type. Null when no mapping exists.</returns>
152184
public static DbType? GetDbTypeFromSystemType(Type systemType)
153185
{
186+
// Get the underlying type argument if the 'systemType' argument is a nullable type.
187+
Type? nullableUnderlyingType = Nullable.GetUnderlyingType(systemType);
188+
189+
// Will not be null when the input argument 'systemType' is a closed generic nullable type.
190+
if (nullableUnderlyingType is not null)
191+
{
192+
systemType = nullableUnderlyingType;
193+
}
194+
154195
if (!_systemTypeToDbTypeMap.TryGetValue(systemType, out DbType dbType))
155196
{
156197
return null;

‎src/Directory.Packages.props‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@
99
<PackageVersion Include="HotChocolate" Version="12.18.0" />
1010
<PackageVersion Include="HotChocolate.AspNetCore" Version="12.18.0" />
1111
<PackageVersion Include="HotChocolate.AspNetCore.Authorization" Version="12.18.0" />
12+
<PackageVersion Include="HotChocolate.Types.NodaTime" Version="12.18.0" />
1213
<PackageVersion Include="Humanizer" Version="2.14.1" />
1314
<PackageVersion Include="Humanizer.Core" Version="2.14.1" />
1415
<PackageVersion Include="DotNetEnv" Version="2.5.0" />
@@ -19,7 +20,7 @@
1920
<PackageVersion Include="Microsoft.AspNetCore.TestHost" Version="6.0.14" />
2021
<PackageVersion Include="Microsoft.Azure.Cosmos" Version="3.20.0" />
2122
<!--When updating Microsoft.Data.SqlClient, update license URL in scripts/notice-generation.ps1-->
22-
<PackageVersion Include="Microsoft.Data.SqlClient" Version="5.0.1" />
23+
<PackageVersion Include="Microsoft.Data.SqlClient" Version="5.1.1" />
2324
<PackageVersion Include="Microsoft.Extensions.Configuration.Binder" Version="6.0.0" />
2425
<PackageVersion Include="Microsoft.Extensions.Configuration.Json" Version="6.0.0" />
2526
<PackageVersion Include="Microsoft.NET.Test.Sdk" Version="17.3.2" />

‎src/Service.GraphQLBuilder/Azure.DataApiBuilder.Service.GraphQLBuilder.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
<ItemGroup>
2121
<PackageReference Include="HotChocolate" />
2222
<PackageReference Include="HotChocolate.AspNetCore.Authorization" />
23+
<PackageReference Include="HotChocolate.Types.NodaTime" />
2324
<PackageReference Include="Humanizer" />
2425
<PackageReference Include="Newtonsoft.Json" />
2526
<PackageReference Include="StyleCop.Analyzers">

‎src/Service.GraphQLBuilder/GraphQLTypes/DefaultValueType.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33

44
using Azure.DataApiBuilder.Service.GraphQLBuilder.CustomScalars;
55
using HotChocolate.Types;
6+
using HotChocolate.Types.NodaTime;
67
using static Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLTypes.SupportedTypes;
78

89
namespace Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLTypes
@@ -24,6 +25,7 @@ protected override void Configure(IInputObjectTypeDescriptor descriptor)
2425
descriptor.Field(DECIMAL_TYPE).Type<DecimalType>();
2526
descriptor.Field(DATETIME_TYPE).Type<DateTimeType>();
2627
descriptor.Field(BYTEARRAY_TYPE).Type<ByteArrayType>();
28+
descriptor.Field(LOCALTIME_TYPE).Type<LocalTimeType>();
2729
}
2830
}
2931
}

‎src/Service.GraphQLBuilder/GraphQLTypes/SupportedTypes.cs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,10 @@ public static class SupportedTypes
1818
public const string STRING_TYPE = "String";
1919
public const string BOOLEAN_TYPE = "Boolean";
2020
public const string DATETIME_TYPE = "DateTime";
21-
public const string DATETIME_NONUTC_TYPE = "DateTimeNonUTC";
21+
public const string DATETIMEOFFSET_TYPE = "DateTimeOffset";
2222
public const string BYTEARRAY_TYPE = "ByteArray";
2323
public const string GUID_TYPE = "Guid";
24+
public const string LOCALTIME_TYPE = "LocalTime";
25+
public const string TIME_TYPE = "Time";
2426
}
2527
}

0 commit comments

Comments
 (0)