Preload the schema at service setup: keys only, data types, Strict

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.AdvancedCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new Advanced6_DynamicTableSchemaServiceSetup().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Create.AdvancedCases;

/// <summary>
/// The schema preloaded when the service is set up, in four scenarios: what a preload of "account" finds,
/// including the contact table it added because account.primary_contact_id references it (with that reason, in the
/// JSON); the convenient setup, keys only; the typed setup, data types for the columns you name; and Strict, where the
/// preloaded columns are an allow list.
/// </summary>
/// <remarks>
/// <para><c>new ArxPreLoadTable(tables)</c> names the tables: an empty array loads every column, a list loads only those
/// columns (the id is always loaded). <c>LoadAsync</c> reads them from the database once, at startup, and returns an
/// ordinary <see cref="ArxSchemaCatalog"/>; no operation reads the database's catalog afterwards.</para>
/// <para>Two modes, the developer's choice. <b>KeysOnly</b> (convenient): the id, the business keys and the lookups,
/// what you would otherwise declare by hand. <b>DataTypes</b> (the default, typed): those plus every loaded column's
/// type, so a value of the wrong type is refused before the database (SQL Server would otherwise convert many of them
/// silently: '42' into an int, a number into a bit). <c>Strict</c> needs DataTypes.</para>
/// </remarks>
public sealed class Advanced6_DynamicTableSchemaServiceSetup : ISample
{
    public string Name => "create-advanced-6-dynamic-table-schema-service-setup";

    public string Title => "Preload the schema at service setup: keys only, data types, Strict";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_PreloadAccountAndSeeWhatWasAddedAsync(dbContext);

        await Scenario2_TheConvenientSetupKeysOnlyAsync(dbContext);

        await Scenario3_TheTypedSetupDataTypesAsync(dbContext);

        await Scenario4_StrictTheColumnsAreAnAllowListAsync(dbContext);
    }

    /// <summary>1. Preload "account" with every column: contact comes too, because a column of account references it.</summary>
    private static async Task Scenario1_PreloadAccountAndSeeWhatWasAddedAsync(SampleContext dbContext)
    {
        Step("1. Preload \"account\": what it found, and the table it added");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account");

        var preload = new ArxPreLoadTable(tables);
        var schema = await preload.LoadAsync(dbContext.ConnectionString);

        foreach (var table in schema.Tables)
        {
            Show($"{table.Name,-8} {table.Reason}");
            Show($"         id {table.IdColumn}; keys {string.Join(" ", table.Keys)}; {table.Columns.Count} columns");
            Show($"         lookups {string.Join(", ", table.Lookups.Select(x => $"{x.Key} -> {x.Value}"))}");
        }

        // The catalog as JSON: keep it in a file or a settings store and reload it with AddJson (RetrieveMultiple Advanced10).
        var json = schema.ToJson();
        Show($"ToJson: {json.Length:N0} characters; contact's entry starts:");

        foreach (var line in json.Split('\n').SkipWhile(x => !x.Contains("\"name\": \"contact\"")).Take(3))
        {
            Show("  " + line.TrimEnd());
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, OBJECT_NAME(parent_object_id) AS from_table, OBJECT_NAME(referenced_object_id) AS to_table
         * FROM sys.foreign_keys
         * WHERE name = 'account_primary_contact_fkey';
         * -- Expect: 1 row, account -> contact: the reference the preload followed to add contact
         * --         (reason "added: referenced by account.primary_contact_id").
         * ===========================================================
         */
    }

    /// <summary>2. Convenient: keys only. The ids, keys and lookups you would otherwise declare by hand.</summary>
    private static async Task Scenario2_TheConvenientSetupKeysOnlyAsync(SampleContext dbContext)
    {
        Step("2. The convenient setup: ArxPreLoadMode.KeysOnly");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account");

        var schema = await new ArxPreLoadTable(tables, ArxPreLoadMode.KeysOnly).LoadAsync(dbContext.ConnectionString);
        var service = new ArxMsSqlDbService(dbContext.ConnectionString, schema);

        var contact = new ArxEntity("contact");
        contact["first_name"] = "Paz";
        contact["last_name"] = "Preload";
        contact["email"] = "paz.preload@example.com";
        var contactId = await service.CreateAsync(contact);

        // primary_contact_id is a lookup the preload found: the reference is checked against contact's id type first.
        var account = new ArxEntity("account");
        account["name"] = "Preloaded Pantry";
        account["account_number"] = "ACC-PRE81";
        account["primary_contact_id"] = new ArxEntityReference("contact", contactId);
        var accountId = await service.CreateAsync(account);
        Show($"created contact {contactId} and account {accountId}, its primary contact");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT a.name, c.first_name, c.last_name
         * FROM account a JOIN contact c ON c.id = a.primary_contact_id
         * WHERE a.account_number = 'ACC-PRE81';
         * -- Expect: 1 row: Preloaded Pantry, Paz Preload.
         * ===========================================================
         */
    }

    /// <summary>3. Typed: data types for the columns you name. A wrong type is refused before the database.</summary>
    private static async Task Scenario3_TheTypedSetupDataTypesAsync(SampleContext dbContext)
    {
        Step("3. The typed setup: ArxPreLoadMode.DataTypes for four columns");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account", ["name", "account_number", "employee_count", "founded_on"]);

        var schema = await new ArxPreLoadTable(tables).LoadAsync(dbContext.ConnectionString);
        var service = new ArxMsSqlDbService(dbContext.ConnectionString, schema);

        schema.TryGetTable("account", out var table);
        Show("loaded: " + string.Join(", ", table!.Columns.Values));

        var account = new ArxEntity("account");
        account["name"] = "Typed Tailors";
        account["account_number"] = "ACC-PRE82";
        account["employee_count"] = 12;
        account["founded_on"] = new DateOnly(2019, 4, 1);
        Show($"created account {await service.CreateAsync(account)}");

        var wrong = new ArxEntity("account");
        wrong["name"] = "Wrongly Typed";
        wrong["account_number"] = "ACC-PRE83";
        wrong["employee_count"] = "twelve";

        try
        {
            await service.CreateAsync(wrong);
        }
        catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.DataTypeMismatch)
        {
            ShowError(exception);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, employee_count, founded_on
         * FROM account
         * WHERE account_number IN ('ACC-PRE82', 'ACC-PRE83');
         * -- Expect: 1 row, ACC-PRE82, 12, 2019-04-01. ACC-PRE83 was refused before the database (ARX4004).
         * ===========================================================
         */
    }

    /// <summary>4. Strict: a column that wasn't preloaded is refused (ARX1018); the same service not Strict writes it.</summary>
    private static async Task Scenario4_StrictTheColumnsAreAnAllowListAsync(SampleContext dbContext)
    {
        Step("4. Strict: the preloaded columns are an allow list");

        // A sign-up endpoint may write these three columns, nothing else.
        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account", ["name", "account_number", "website"]);

        var schema = await new ArxPreLoadTable(tables).LoadAsync(dbContext.ConnectionString);
        var strict = new ArxMsSqlDbService(dbContext.ConnectionString, schema) { Strict = true };

        var signUp = new ArxEntity("account");
        signUp["name"] = "Strict Stationers";
        signUp["account_number"] = "ACC-PRE84";
        signUp["website"] = "https://stationers.example.com";
        signUp["credit_limit"] = 1_000_000m;

        try
        {
            await strict.CreateAsync(signUp);
        }
        catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
        {
            ShowError(exception);
        }

        // Not Strict (the default): the preload is what ARX knows ahead of time, and other columns still work.
        var relaxed = new ArxMsSqlDbService(dbContext.ConnectionString, schema);
        Show($"not Strict: created account {await relaxed.CreateAsync(signUp)} with its credit limit");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, website, credit_limit
         * FROM account
         * WHERE account_number = 'ACC-PRE84';
         * -- Expect: 1 row, credit_limit 1000000.00: written by the service that isn't Strict; the Strict one refused
         * --         credit_limit before the database (ARX1018).
         * ===========================================================
         */
    }
}

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.MSSQL;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain SqlClient read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.MSSQL;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}

  > 1. Preload "account": what it found, and the table it added
      account  requested
               id id (Int64, generated); keys (account_number); 17 columns
               lookups primary_contact_id -> contact, parent_account_id -> account
      contact  added: referenced by account.primary_contact_id
               id id (Int64, generated); keys (email); 16 columns
               lookups account_id -> account
      ToJson: 7,086 characters; contact's entry starts:
              "name": "contact",
              "reason": "added: referenced by account.primary_contact_id",
              "id": {
  > 2. The convenient setup: ArxPreLoadMode.KeysOnly
      created contact 1 and account 1, its primary contact
  > 3. The typed setup: ArxPreLoadMode.DataTypes for four columns
      loaded: id bigint (long), name nvarchar (string), account_number varchar (string), employee_count int (int), founded_on date (DateOnly)
      created account 2
      caught ArxValidationException: ARX4004 DataTypeMismatch: Attribute 'employee_count' on 'account' has a String value, but its column is int (int): refused before the database.
  > 4. Strict: the preloaded columns are an allow list
      caught ArxValidationException: ARX1018 NotPreloaded: Column 'credit_limit' of 'account' wasn't preloaded, and the service is Strict. Preloaded: id, name, account_number, website. Add it to the ArxPreLoadTable, or turn Strict off.
      not Strict: created account 3 with its credit limit