FractalDataWorks.Configuration.MsSql 0.6.0-rc.1

FractalDataWorks.Configuration.MsSql

SQL Server configuration provider for loading and persisting FractalDataWorks configuration using DataCommands.

Overview

This package provides SQL Server-specific configuration capabilities for the FractalDataWorks framework. It enables:

• Loading configuration from SQL Server into Microsoft.Extensions.Configuration
• Persisting DataStore and DataSet schemas to the configuration database
• Runtime configuration loading into DataGateway providers
• Multi-tenant and service-category filtering

The package uses FractalDataWorks DataCommands for all database operations. No EF Core or Dapper dependencies.

Installation

<PackageReference Include="FractalDataWorks.Configuration.MsSql" />

Core Components

MsSqlConfigurationOptions

From ServiceCollectionExtensions.cs:109-143:

public sealed class MsSqlConfigurationOptions
{
    /// <summary>
    /// The configuration section name for binding from IConfiguration.
    /// </summary>
    public const string SectionName = "FractalDataWorks:ConfigurationDatabase";

    /// <summary>
    /// Gets or sets the connection name to use for the configuration database.
    /// This references a connection defined in the "Connections" section.
    /// </summary>
    public string ConnectionName { get; set; } = "ConfigurationDatabase";

    /// <summary>
    /// Gets or sets the direct connection string to the configuration database.
    /// Use this OR ConnectionName, not both.
    /// </summary>
    public string? ConnectionString { get; set; }

    /// <summary>
    /// Gets or sets the polling interval for configuration changes.
    /// </summary>
    public TimeSpan PollingInterval { get; set; } = TimeSpan.FromSeconds(30);

    /// <summary>
    /// Gets or sets whether to enable hot reload from the configuration database.
    /// </summary>
    public bool EnableHotReload { get; set; } = true;

    /// <summary>
    /// Gets or sets the options loader type for this configuration.
    /// Valid values: "Singleton", "Scoped", "Reloadable".
    /// </summary>
    public string OptionsLoaderType { get; set; } = "Reloadable";
}

MsSqlConfigurationSourceOptions

From ConfigurationBuilderExtensions.cs:76-102:

public sealed class MsSqlConfigurationSourceOptions
{
    /// <summary>
    /// Gets or sets the section name prefix for configuration keys.
    /// </summary>
    public string SectionName { get; set; } = string.Empty;

    /// <summary>
    /// Gets or sets the service category filter.
    /// </summary>
    public string? ServiceCategory { get; set; }

    /// <summary>
    /// Gets or sets the tenant ID for multi-tenant scenarios.
    /// </summary>
    public string? TenantId { get; set; }

    /// <summary>
    /// Gets or sets the user ID for user-specific configuration.
    /// </summary>
    public string? UserId { get; set; }

    /// <summary>
    /// Gets or sets the logger factory.
    /// </summary>
    public ILoggerFactory? LoggerFactory { get; set; }
}

Service Registration

Basic Registration

From ServiceCollectionExtensions.cs:20-27:

public static IServiceCollection AddMsSqlConfiguration(this IServiceCollection services)
{
    services.TryAddSingleton<ISchemaHashService, SchemaHashService>();
    services.TryAddScoped<DataStoreConfigurationService>();
    services.TryAddScoped<IConfigurationWriter, MsSqlConfigurationWriter>();

    return services;
}

Registration with Options

From ServiceCollectionExtensions.cs:39-60:

public static IServiceCollection AddMsSqlConfiguration(
    this IServiceCollection services,
    Action<MsSqlConfigurationOptions> configure,
    string optionsLoaderType = "Reloadable")
{
    // Configure options using standard Options pattern
    services.AddOptions<MsSqlConfigurationOptions>()
        .Configure(configure)
        .ValidateDataAnnotations()
        .ValidateOnStart();

    // Get the loader type from TypeCollection (default to Reloadable if not found)
    var loaderType = OptionsLoaderTypes.ByName(optionsLoaderType) ?? OptionsLoaderTypes.Reloadable;

    // Register IConfigurationOptionsLoader<MsSqlConfigurationOptions>
    services.TryAddSingleton(typeof(IConfigurationOptionsLoader<MsSqlConfigurationOptions>),
        sp => loaderType.CreateLoader<MsSqlConfigurationOptions>(sp));

    services.AddMsSqlConfiguration();

    return services;
}

Registration from Configuration Section

From ServiceCollectionExtensions.cs:72-93:

public static IServiceCollection AddMsSqlConfigurationFromSection(
    this IServiceCollection services,
    string sectionName = "FractalDataWorks:ConfigurationDatabase",
    string optionsLoaderType = "Reloadable")
{
    // Configure options from IConfiguration section
    services.AddOptions<MsSqlConfigurationOptions>()
        .BindConfiguration(sectionName)
        .ValidateDataAnnotations()
        .ValidateOnStart();

    // Get the loader type from TypeCollection (default to Reloadable if not found)
    var loaderType = OptionsLoaderTypes.ByName(optionsLoaderType) ?? OptionsLoaderTypes.Reloadable;

    // Register IConfigurationOptionsLoader<MsSqlConfigurationOptions>
    services.TryAddSingleton(typeof(IConfigurationOptionsLoader<MsSqlConfigurationOptions>),
        sp => loaderType.CreateLoader<MsSqlConfigurationOptions>(sp));

    services.AddMsSqlConfiguration();

    return services;
}

Configuration Source

Adding to IConfigurationBuilder

From ConfigurationBuilderExtensions.cs:21-41:

public static IConfigurationBuilder AddMsSqlConfiguration(
    this IConfigurationBuilder builder,
    IDataConnection connection,
    Action<MsSqlConfigurationSourceOptions>? configure = null)
{
    var options = new MsSqlConfigurationSourceOptions();
    configure?.Invoke(options);

    var source = new MsSqlConfigurationSource
    {
        Connection = connection,
        SectionName = options.SectionName,
        ServiceCategory = options.ServiceCategory,
        TenantId = options.TenantId,
        UserId = options.UserId,
        LoggerFactory = options.LoggerFactory
    };

    builder.Add(source);
    return builder;
}

Configuration Bootstrap

ConfigurationBootstrap Class

From ConfigurationBootstrap.cs:21-97:

public sealed class ConfigurationBootstrap
{
    private readonly IDataConnection _configConnection;
    private readonly ILoggerFactory _loggerFactory;

    public ConfigurationBootstrap(
        IDataConnection configConnection,
        ILoggerFactory loggerFactory)
    {
        _configConnection = configConnection ?? throw new ArgumentNullException(nameof(configConnection));
        _loggerFactory = loggerFactory ?? throw new ArgumentNullException(nameof(loggerFactory));
    }

    /// <summary>
    /// Imports a DataStore into the configuration database.
    /// </summary>
    public async Task<IGenericResult<Guid>> ImportDataStore(
        string name,
        IDataStore dataStore,
        bool forceRefresh = false,
        CancellationToken cancellationToken = default)
    {
        // ... implementation
    }

    /// <summary>
    /// Gets an instance of the DataStore configuration service.
    /// </summary>
    public DataStoreConfigurationService GetDataStoreService()
    {
        return new DataStoreConfigurationService(
            _configConnection,
            _loggerFactory.CreateLogger<DataStoreConfigurationService>());
    }

    /// <summary>
    /// Gets an instance of the schema hash service.
    /// </summary>
    public static ISchemaHashService GetSchemaHashService()
    {
        return new SchemaHashService();
    }
}

Configuration Loader

IConfigurationLoader Implementation

From ConfigurationLoader.cs:31-122:

public sealed class ConfigurationLoader : IConfigurationLoader
{
    private readonly IDataConnection _configConnection;
    private readonly IDataStoreProvider _dataStoreProvider;
    private readonly IDataSetProvider _dataSetProvider;
    private readonly DataSetConfigurationService _dataSetService;
    private readonly ILogger<ConfigurationLoader> _logger;

    private int _dataStoreCount;
    private int _dataSetCount;

    public ConfigurationLoader(
        IDataConnection configConnection,
        IDataStoreProvider dataStoreProvider,
        IDataSetProvider dataSetProvider,
        ILogger<ConfigurationLoader> logger)
    {
        _configConnection = configConnection ?? throw new ArgumentNullException(nameof(configConnection));
        _dataStoreProvider = dataStoreProvider ?? throw new ArgumentNullException(nameof(dataStoreProvider));
        _dataSetProvider = dataSetProvider ?? throw new ArgumentNullException(nameof(dataSetProvider));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        // ...
    }

    /// <inheritdoc/>
    public int DataStoreCount => _dataStoreCount;

    /// <inheritdoc/>
    public int DataSetCount => _dataSetCount;

    /// <inheritdoc/>
    public async Task<IGenericResult> LoadAll(CancellationToken cancellationToken = default)
    {
        // ... loads DataStores and DataSets from configuration database
    }

    /// <inheritdoc/>
    public Task<IGenericResult> Reload(CancellationToken cancellationToken = default)
    {
        // Clear existing registrations and reload
        _dataStoreProvider.ClearAllCaches();
        // ...
        return LoadAll(cancellationToken);
    }

    /// <inheritdoc/>
    public async Task<IGenericResult> LoadDataStore(string name, CancellationToken cancellationToken = default)
    {
        // ... loads specific DataStore
    }

    /// <inheritdoc/>
    public async Task<IGenericResult> LoadDataSet(string name, CancellationToken cancellationToken = default)
    {
        // ... loads specific DataSet
    }
}

Usage Examples

Registering Services

// Option 1: From configuration section (recommended)
services.AddMsSqlConfigurationFromSection();

// Option 2: With inline options
services.AddMsSqlConfiguration(options =>
{
    options.ConnectionString = "Server=localhost;Database=FdwConfig;...";
    options.EnableHotReload = true;
    options.PollingInterval = TimeSpan.FromSeconds(30);
});

Adding Configuration Source

// Add SQL Server configuration source to IConfigurationBuilder
var configuration = new ConfigurationBuilder()
    .AddMsSqlConfiguration(connection, options =>
    {
        options.SectionName = "DataStores";
        options.ServiceCategory = "DataStore";
        options.TenantId = tenantId;
    })
    .Build();

Importing DataStores

// Create bootstrap instance
var bootstrap = new ConfigurationBootstrap(configConnection, loggerFactory);

// Import a DataStore (with change detection)
var result = await bootstrap.ImportDataStore(
    name: "PrimaryDatabase",
    dataStore: discoveredDataStore,
    forceRefresh: false,  // Only import if schema changed
    cancellationToken: ct);

if (result.IsSuccess)
{
    Console.WriteLine($"DataStore imported with ID: {result.Value}");
}

Loading Configuration at Startup

// During application startup
var configLoader = app.Services.GetRequiredService<IConfigurationLoader>();
var loadResult = await configLoader.LoadAll(cancellationToken);

if (!loadResult.IsSuccess)
{
    logger.LogError("Failed to load configuration: {Message}", loadResult.CurrentMessage);
}

Console.WriteLine($"Loaded {configLoader.DataStoreCount} DataStores, {configLoader.DataSetCount} DataSets");

Database Schema

Configuration is stored in the cfg schema. Each ServiceType loads its own configuration from dedicated tables rather than using a centralized header table.

Core Tables

Connection Tables

Shared PK Extension Tables

Some configuration types use a shared primary key pattern for 1:1 extension data. The extension table's Id column is both its primary key AND a foreign key to the parent table.

Example: ConnectionAuthentication

-- Parent table
cfg.Connection (Id, Name, ServiceOptionType, ...)

-- Extension table - Id is same as parent
cfg.ConnectionAuthentication (Id, Type, Username, SecretManagerName, SecretKeyName, ...)

The MsSqlConfigurationProvider automatically loads these extension tables after the parent row, prefixing properties with the extension name:

• Authentication:Type
• Authentication:Username
• Authentication:SecretManagerName
• Authentication:SecretKeyName

Currently supported extension tables:

• {ParentTableName}Authentication - Authentication settings for connections

Extension tables that don't exist are silently ignored (e.g., DataSetAuthentication won't be queried for DataSet configurations).

Configuration Loading Pattern

TODO: The ConfigureAll() pattern is being implemented where each ServiceType registers a Configure() method that loads its own configuration from the appropriate tables.

Each ServiceType is responsible for:

1. Defining its configuration table schema via [ManagedConfiguration] attribute
2. Implementing Configure() to load configuration using the appropriate service
3. Registering the loaded configuration with IConfiguration or IOptions<T>

Dependencies

• FractalDataWorks.Configuration - Base configuration framework
• FractalDataWorks.Configuration.Abstractions - Configuration interfaces
• FractalDataWorks.Results - Railway-oriented result types
• FractalDataWorks.Commands.Data - DataCommand pattern implementation
• FractalDataWorks.Data.MsSql - SQL Server data layer
• FractalDataWorks.Services.Connections.Abstractions - IDataConnection interface
• Microsoft.Extensions.Configuration.Abstractions - .NET configuration
• Microsoft.Extensions.DependencyInjection.Abstractions - DI abstractions
• Microsoft.Extensions.Logging.Abstractions - Logging abstractions

Best Practices

1. Use connection names over connection strings - Reference connections from the Connections section rather than embedding connection strings
2. Enable hot reload for database-backed config - Set EnableHotReload = true and use ReloadableOptionsLoader
3. Use service category filtering - Filter configuration by ServiceCategory to load only relevant settings
4. Call LoadAll at startup - Use IConfigurationLoader.LoadAll() during application startup to populate providers
5. Use forceRefresh sparingly - Let schema hashing detect changes rather than forcing reimports
6. Handle failures gracefully - All operations return IGenericResult - check IsSuccess before proceeding

Related Packages

• FractalDataWorks.Configuration - Base configuration framework
• FractalDataWorks.Configuration.Abstractions - Configuration interfaces
• FractalDataWorks.Hosting.Bootstrap.Configuration - High-level hosting bootstrap
• FractalDataWorks.Data.MsSql - SQL Server data layer