feat: Update modern SQLServer, add some DBBuilder extensions

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
         /// <param name="record">The record to add to the store</param>
         /// <param name="cancellation">A cancellation token to cancel the operation</param>
         /// <returns>A task the resolves the result of the operation</returns>
+        /// <exception cref="ArgumentNullException"></exception>
         public static async Task<ERRNO> AddOrUpdateAsync<T>(this IDataStore<T> 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<ERRNO> GetCollectionAsync<T>(this IDataStore<T> store, ICollection<T> 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
         /// <param name="store"></param>
         /// <param name="collection">The collection to add entires to</param>
         /// <param name="limit">The maximum number of elements to retrieve</param>
-        /// <param name="args"></param>
+        /// <param name="constraints">An array of string contraints to pass to the query builder</param>
         /// <returns>A Task the resolves to the number of items added to the collection</returns>
-        public static async Task<ERRNO> GetCollectionAsync<T>(this IDataStore<T> store, ICollection<T> collection, int limit, params string[] args)
+        /// <exception cref="ArgumentNullException"></exception>
+        public static Task<ERRNO> GetCollectionAsync<T>(this IDataStore<T> store, ICollection<T> collection, int limit, params string[] constraints)
               where T : class, IDbModel
         {
-            int previous = collection.Count;
+            return GetCollectionAsync(store, collection, limit, constraints, CancellationToken.None);
+        }
 
+        /// <summary>
+        /// Fills a collection with enires retireved from the store using a variable length specifier
+        /// parameter
+        /// </summary>
+        /// <param name="store"></param>
+        /// <param name="collection">The collection to add entires to</param>
+        /// <param name="limit">The maximum number of elements to retrieve</param>
+        /// <param name="constraints">An array of string contraints to pass to the query builder</param>
+        /// <param name="cancellation">A token to cancel the operation</param>
+        /// <returns>A Task the resolves to the number of items added to the collection</returns>
+        /// <exception cref="ArgumentNullException"></exception>
+        public static async Task<ERRNO> GetCollectionAsync<T>(
+            this IDataStore<T> store, 
+            ICollection<T> 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<ERRNO> DeleteAsync<T>(this IDataStore<T> 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
         /// <param name="store"></param>
         /// <param name="specifiers">A variable length array of specifiers used to delete one or more entires</param>
         /// <returns>A task the resolves the number of records removed(should evaluate to false on failure, and deleted count on success)</returns>
-        public static async Task<ERRNO> DeleteAsync<T>(this IDataStore<T> store, params string[] specifiers)
+        public static Task<ERRNO> DeleteAsync<T>(this IDataStore<T> store, params string[] specifiers)
+              where T : class, IDbModel
+        {
+           
+            return DeleteAsync(store, specifiers, CancellationToken.None);
+        }
+
+
+        /// <summary>
+        /// Deletes one or more entires from the store matching the supplied specifiers
+        /// </summary>
+        /// <param name="store"></param>
+        /// <param name="specifiers">A variable length array of specifiers used to delete one or more entires</param>
+        /// <param name="cancellation">A cancellation token to cancel the operation</param>
+        /// <returns>A task the resolves the number of records removed(should evaluate to false on failure, and deleted count on success)</returns>
+        public static async Task<ERRNO> DeleteAsync<T>(this IDataStore<T> 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<T> 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<int> GetPageAsync<T>(this IDataStore<T> store, ICollection<T> 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,9 +528,32 @@ namespace VNLib.Plugins.Extensions.Data.Extensions
         /// <param name="limit">The maximum number of items to retrieve from the store</param>
         /// <param name="constraints">A params array of strings to constrain the result set from the db</param>
         /// <returns>A task that resolves the number of items added to the collection</returns>
-        public static async Task<int> GetPageAsync<T>(this IDataStore<T> store, ICollection<T> collection, int page, int limit, params string[] constraints)
+        public static Task<int> GetPageAsync<T>(this IDataStore<T> store, ICollection<T> collection, int page, int limit, params string[] constraints)
             where T : class, IDbModel
         {
+            return GetPageAsync(store, collection, page, limit, constraints, CancellationToken.None);
+        }
+
+        /// <summary>
+        /// Gets a collection of records using a pagination style query with constraint arguments, and adds the records to the collecion
+        /// </summary>
+        /// <param name="store"></param>
+        /// <param name="collection">The collection to add records to</param>
+        /// <param name="page">Pagination page to get records from</param>
+        /// <param name="limit">The maximum number of items to retrieve from the store</param>
+        /// <param name="constraints">A params array of strings to constrain the result set from the db</param>
+        /// <param name="cancellation">A token to cancel the operation</param>
+        /// <returns>A task that resolves the number of items added to the collection</returns>
+        public static async Task<int> GetPageAsync<T>(
+            this IDataStore<T> store, 
+            ICollection<T> 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);
         }
+
+        /// <summary>
+        /// A helper method to define a table for a <see cref="IDbContextBuilder"/>
+        /// </summary>
+        /// <typeparam name="T"></typeparam>
+        /// <param name="builder"></param>
+        /// <param name="tableName">The optional name of the table to create</param>
+        /// <param name="callback">The table creation callback function</param>
+        /// <returns>The original context builder instance</returns>
+        /// <exception cref="ArgumentNullException"></exception>
+        public static IDbContextBuilder DefineTable<T>(this IDbContextBuilder builder, string tableName, Action<IDbTableBuilder<T>> callback)
+        {
+            ArgumentNullException.ThrowIfNull(builder);
+            ArgumentNullException.ThrowIfNull(callback);
+
+            callback(builder.DefineTable<T>(tableName));
+            return builder;
+        }
+
+        /// <summary>
+        /// A helper method to define a table for a <see cref="IDbContextBuilder"/>
+        /// </summary>
+        /// <typeparam name="T"></typeparam>
+        /// <param name="builder"></param>
+        /// <param name="callback">The table creation callback function</param>
+        /// <returns>The original context builder instance</returns>
+        /// <exception cref="ArgumentNullException"></exception>
+        public static IDbContextBuilder DefineTable<T>(this IDbContextBuilder builder, Action<IDbTableBuilder<T>> callback)
+        {
+            ArgumentNullException.ThrowIfNull(builder);
+            ArgumentNullException.ThrowIfNull(callback);
+
+            callback(builder.DefineTable<T>());
+            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)",