From 40c634b0f37ce9922dbc32c86e26d5a771daeca3 Mon Sep 17 00:00:00 2001 From: vnugent Date: Sat, 30 Mar 2024 22:22:43 -0400 Subject: feat: Update modern SQLServer, add some DBBuilder extensions --- .../src/Extensions/DbStoreExtensions.cs | 100 +++++++++++++++++---- .../src/DbBuilder.cs | 2 +- .../src/SqlDbConnectionLoader.cs | 35 ++++++++ .../src/SqlServerExport.cs | 2 +- 4 files changed, 121 insertions(+), 18 deletions(-) diff --git a/lib/VNLib.Plugins.Extensions.Data/src/Extensions/DbStoreExtensions.cs b/lib/VNLib.Plugins.Extensions.Data/src/Extensions/DbStoreExtensions.cs index e3d4f6b..66225ba 100644 --- a/lib/VNLib.Plugins.Extensions.Data/src/Extensions/DbStoreExtensions.cs +++ b/lib/VNLib.Plugins.Extensions.Data/src/Extensions/DbStoreExtensions.cs @@ -48,10 +48,12 @@ namespace VNLib.Plugins.Extensions.Data.Extensions /// The record to add to the store /// A cancellation token to cancel the operation /// A task the resolves the result of the operation + /// public static async Task AddOrUpdateAsync(this IDataStore store, T record, CancellationToken cancellation = default) where T : class, IDbModel { ArgumentNullException.ThrowIfNull(store); + ArgumentNullException.ThrowIfNull(record); //Open new db context await using IDbContextHandle ctx = store.GetNewContext(); @@ -287,9 +289,10 @@ namespace VNLib.Plugins.Extensions.Data.Extensions public static async Task GetCollectionAsync(this IDataStore store, ICollection collection, string specifier, int limit, CancellationToken cancellation = default) where T : class, IDbModel { - int previous = collection.Count; - ArgumentNullException.ThrowIfNull(store); + ArgumentNullException.ThrowIfNull(collection); + + int previous = collection.Count; //Open new db context await using IDbContextHandle ctx = store.GetNewContext(); @@ -315,27 +318,51 @@ namespace VNLib.Plugins.Extensions.Data.Extensions /// /// The collection to add entires to /// The maximum number of elements to retrieve - /// + /// An array of string contraints to pass to the query builder /// A Task the resolves to the number of items added to the collection - public static async Task GetCollectionAsync(this IDataStore store, ICollection collection, int limit, params string[] args) + /// + public static Task GetCollectionAsync(this IDataStore store, ICollection collection, int limit, params string[] constraints) where T : class, IDbModel { - int previous = collection.Count; + return GetCollectionAsync(store, collection, limit, constraints, CancellationToken.None); + } + /// + /// Fills a collection with enires retireved from the store using a variable length specifier + /// parameter + /// + /// + /// The collection to add entires to + /// The maximum number of elements to retrieve + /// An array of string contraints to pass to the query builder + /// A token to cancel the operation + /// A Task the resolves to the number of items added to the collection + /// + public static async Task GetCollectionAsync( + this IDataStore store, + ICollection collection, + int limit, + string[] constraints, + CancellationToken cancellation + ) where T : class, IDbModel + { ArgumentNullException.ThrowIfNull(store); + ArgumentNullException.ThrowIfNull(collection); + + int previous = collection.Count; //Open new db context await using IDbContextHandle ctx = store.GetNewContext(); //Get the single template by its id - await store.QueryTable.GetCollectionQueryBuilder(ctx, args) + await store.QueryTable.GetCollectionQueryBuilder(ctx, constraints) .Take(limit) .Select(static e => e) .AsNoTracking() - .ForEachAsync(collection.Add); + .ForEachAsync(collection.Add, cancellation); //close db and transaction - _ = await ctx.SaveAndCloseAsync(true); + _ = await ctx.SaveAndCloseAsync(true, cancellation); //Return the number of elements add to the collection return collection.Count - previous; @@ -386,6 +413,8 @@ namespace VNLib.Plugins.Extensions.Data.Extensions public static async Task DeleteAsync(this IDataStore store, string key, CancellationToken cancellation = default) where T : class, IDbModel { + ArgumentNullException.ThrowIfNull(store); + //Open new db context await using IDbContextHandle ctx = store.GetNewContext(); @@ -414,7 +443,22 @@ namespace VNLib.Plugins.Extensions.Data.Extensions /// /// A variable length array of specifiers used to delete one or more entires /// A task the resolves the number of records removed(should evaluate to false on failure, and deleted count on success) - public static async Task DeleteAsync(this IDataStore store, params string[] specifiers) + public static Task DeleteAsync(this IDataStore store, params string[] specifiers) + where T : class, IDbModel + { + + return DeleteAsync(store, specifiers, CancellationToken.None); + } + + + /// + /// Deletes one or more entires from the store matching the supplied specifiers + /// + /// + /// A variable length array of specifiers used to delete one or more entires + /// A cancellation token to cancel the operation + /// A task the resolves the number of records removed(should evaluate to false on failure, and deleted count on success) + public static async Task DeleteAsync(this IDataStore store, string[] specifiers, CancellationToken cancellation) where T : class, IDbModel { ArgumentNullException.ThrowIfNull(store); @@ -425,7 +469,7 @@ namespace VNLib.Plugins.Extensions.Data.Extensions //Get the template by its id IQueryable query = store.QueryTable.DeleteQueryBuilder(ctx, specifiers); - T? entry = await query.SingleOrDefaultAsync(); + T? entry = await query.SingleOrDefaultAsync(cancellation); if (entry is null) { @@ -435,7 +479,7 @@ namespace VNLib.Plugins.Extensions.Data.Extensions //Add the new application ctx.Remove(entry); - return await ctx.SaveAndCloseAsync(true); + return await ctx.SaveAndCloseAsync(true, cancellation); } @@ -451,11 +495,12 @@ namespace VNLib.Plugins.Extensions.Data.Extensions public static async Task GetPageAsync(this IDataStore store, ICollection collection, int page, int limit, CancellationToken cancellation = default) where T : class, IDbModel { + ArgumentNullException.ThrowIfNull(store); + ArgumentNullException.ThrowIfNull(collection); + //Store preivous count int previous = collection.Count; - ArgumentNullException.ThrowIfNull(store); - //Open new db context await using IDbContextHandle ctx = store.GetNewContext(); @@ -483,8 +528,31 @@ namespace VNLib.Plugins.Extensions.Data.Extensions /// The maximum number of items to retrieve from the store /// A params array of strings to constrain the result set from the db /// A task that resolves the number of items added to the collection - public static async Task GetPageAsync(this IDataStore store, ICollection collection, int page, int limit, params string[] constraints) + public static Task GetPageAsync(this IDataStore store, ICollection collection, int page, int limit, params string[] constraints) where T : class, IDbModel + { + return GetPageAsync(store, collection, page, limit, constraints, CancellationToken.None); + } + + /// + /// Gets a collection of records using a pagination style query with constraint arguments, and adds the records to the collecion + /// + /// + /// The collection to add records to + /// Pagination page to get records from + /// The maximum number of items to retrieve from the store + /// A params array of strings to constrain the result set from the db + /// A token to cancel the operation + /// A task that resolves the number of items added to the collection + public static async Task GetPageAsync( + this IDataStore store, + ICollection collection, + int page, + int limit, + string[] constraints, + CancellationToken cancellation + ) + where T : class, IDbModel { ArgumentNullException.ThrowIfNull(store); ArgumentNullException.ThrowIfNull(collection); @@ -501,10 +569,10 @@ namespace VNLib.Plugins.Extensions.Data.Extensions .Take(limit) .Select(static e => e) .AsNoTracking() - .ForEachAsync(collection.Add); + .ForEachAsync(collection.Add, cancellation); //close db and transaction - await ctx.SaveAndCloseAsync(true); + await ctx.SaveAndCloseAsync(true, cancellation); //Return the number of records added return collection.Count - previous; diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs index 44d4670..a82fdfa 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs @@ -125,7 +125,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql if (maxLen.HasValue) { - col.MaxLength = maxLen.Value; + col.MaxLength(maxLen.Value); } //Store the column diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs index b48ddd1..6f150c8 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs @@ -210,5 +210,40 @@ namespace VNLib.Plugins.Extensions.Loading.Sql //All done! plugin.Log.Debug("Successfully created tables for {type}", typeof(T).Name); } + + /// + /// A helper method to define a table for a + /// + /// + /// + /// The optional name of the table to create + /// The table creation callback function + /// The original context builder instance + /// + public static IDbContextBuilder DefineTable(this IDbContextBuilder builder, string tableName, Action> callback) + { + ArgumentNullException.ThrowIfNull(builder); + ArgumentNullException.ThrowIfNull(callback); + + callback(builder.DefineTable(tableName)); + return builder; + } + + /// + /// A helper method to define a table for a + /// + /// + /// + /// The table creation callback function + /// The original context builder instance + /// + public static IDbContextBuilder DefineTable(this IDbContextBuilder builder, Action> callback) + { + ArgumentNullException.ThrowIfNull(builder); + ArgumentNullException.ThrowIfNull(callback); + + callback(builder.DefineTable()); + return builder; + } } } diff --git a/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs index 9f503ed..89769f5 100644 --- a/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs +++ b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs @@ -260,7 +260,7 @@ namespace VNLib.Plugins.Extensions.Sql DbType.Boolean => "BOOL", DbType.Currency => "MONEY", DbType.Date => "DATE", - DbType.DateTime => "DATETIME", + DbType.DateTime => "DATETIME2", DbType.Decimal => "DECIMAL", DbType.Double => "DOUBLE", DbType.Guid => "VARCHAR(@size)", -- cgit