Picklist values checked against the application's catalog

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Create.RealCases;

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

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.MySQL.Services;
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Create.RealCases;

/// <summary>
/// Selection sets (picklists, option sets), in seven scenarios: the catalog, a catalog without the sets, an
/// account's industry, status and segments, a contact's preferred channel and a survey mood, sets loaded from JSON,
/// values the catalog refuses, and a plain string that skips the catalog.
/// </summary>
/// <remarks>
/// <para>
/// <b>The catalog</b> is the list of every selection set your application has: for each set, its name, what kind of
/// code it uses (a number, a string, a character, …), whether one or several codes can be chosen, and its options
/// (code + display text, and whether the option is still active). You build it once at startup, in code, and give it
/// to the service: <c>new ArxMySqlDbService(connectionString, catalog)</c>. This sample's catalog is in SampleCatalog.cs.
/// </para>
/// <para>
/// When you write an <see cref="ArxSelectionSetValue"/>, ARX looks up its set in the catalog, checks the code, and
/// writes the code itself into the column. The check happens in memory, before any SQL is sent: no lookup table and
/// no extra query. The database never sees a code your catalog doesn't allow.
/// </para>
/// </remarks>
public sealed class RealCase4_SelectionSets : ISample
{
    public string Name => "create-real-4-selection-sets";

    public string Title => "Picklist values checked against the application's catalog";

    public async Task RunAsync(SampleContext dbContext)
    {
        Scenario1_TheCatalog(dbContext);

        await Scenario2_WithoutTheSelectionSets_AValueIsRefusedAsync(dbContext);

        var accountId = await Scenario3_IndustryStatusAndSegmentsAsync(dbContext);

        await Scenario4_ACharacterAndADatabaseEnumAsync(dbContext, accountId);

        await Scenario5_SetsLoadedFromJsonAsync(dbContext, accountId);

        await Scenario6_ValuesTheCatalogRefusesAsync(dbContext);

        await Scenario7_APlainStringSkipsTheCatalogAsync(dbContext);
    }

    /// <summary>1. The catalog, built once at startup (SampleCatalog.Build()) and passed to the service: what it holds.</summary>
    private static void Scenario1_TheCatalog(SampleContext dbContext)
    {
        Step("1. The application's catalog: one entry per selection set");

        foreach (var set in dbContext.Catalog.SelectionSets.Sets)
        {
            var options = string.Join(", ", set.Options.Select(x => $"{x.Value} = {x.DisplayText}{(x.IsActive ? "" : " (retired)")}"));
            Show($"{set.Name,-16} {set.Kind,-12} {set.Multiplicity,-7} {options}");
        }

        // A set can be looked up by name, for example to fill a drop-down list in your UI.
        if (dbContext.Catalog.TryGetSet("AccountStatus", out var statusSet))
        {
            Show($"drop-down for AccountStatus: {string.Join(" | ", statusSet.Options.Where(x => x.IsActive).Select(x => x.DisplayText))}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * -- Nothing is stored: the catalog lives in memory (SampleCatalog.cs). The columns its sets write to:
         * SELECT table_name, column_name, column_type
         * FROM information_schema.columns
         * WHERE table_schema = DATABASE() AND (table_name, column_name) IN (('account', 'industry'), ('account', 'status'), ('account', 'segments'),
         *       ('contact', 'preferred_channel'), ('contact', 'lead_source'), ('contact', 'rating'), ('data_type_showcase', 'mood'))
         * ORDER BY table_name, column_name;
         * -- Expect: 7 rows.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. A service whose catalog declares the tables but no selection sets can't write selection set values: there is
    /// nothing to check them against.
    /// </summary>
    private static async Task Scenario2_WithoutTheSelectionSets_AValueIsRefusedAsync(SampleContext dbContext)
    {
        Step("2. Without the selection sets in the catalog, a selection set value is refused");

        var tablesOnly = ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id"))
            .Build();
        var noCatalog = new ArxMySqlDbService(dbContext.ConnectionString, tablesOnly);

        var notChecked = new ArxEntity("account");
        notChecked["name"] = "No catalog Ltd.";
        notChecked["industry"] = new ArxSelectionSetValue("Industry", Industry.Retail);

        try
        {
            await noCatalog.CreateAsync(notChecked);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.UnknownSelectionSet)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name = 'No catalog Ltd.';
         * -- Expect: 0: refused, the service had no selection sets to check against.
         * ===========================================================
         */
    }

    /// <summary>3. An account's industry (an enum), status (a string) and segments (several strings).</summary>
    private static async Task<ArxRecordId> Scenario3_IndustryStatusAndSegmentsAsync(SampleContext dbContext)
    {
        Step("3. Industry (an enum), status (a string) and segments (several strings)");

        var account = new ArxEntity("account");
        account["name"] = "Adventure Works";
        account["industry"] = new ArxSelectionSetValue("Industry", Industry.Manufacturing);
        account["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
        account["segments"] = ArxSelectionSetValue.Multiple("CustomerSegment", "ENTERPRISE", "PUBLIC");
        var id = await dbContext.Service.CreateAsync(account);

        var stored = await dbContext.Direct.TextRowAsync("account", id.AsInt64(), "industry", "status", "segments");
        Show($"stored: industry {stored["industry"]}, status {stored["status"]}, segments {stored["segments"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, industry, status, segments
         * FROM account
         * WHERE name = 'Adventure Works' AND status = 'ON_HOLD';
         * -- Expect: 1 row: industry 2, status ON_HOLD, segments ENTERPRISE,PUBLIC.
         * ===========================================================
         */

        return id;
    }

    /// <summary>4. A contact's preferred channel (a character) and a survey's mood (text values a CHECK guards).</summary>
    private static async Task Scenario4_ACharacterAndADatabaseEnumAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("4. A preferred channel (a character) and a mood (text values a CHECK guards)");

        var contact = new ArxEntity("contact");
        contact["account_id"] = new ArxEntityReference("account", accountId);
        contact["first_name"] = "Kenji";
        contact["last_name"] = "Mori";
        contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", 'S');
        var contactId = await dbContext.Service.CreateAsync(contact);

        var survey = new ArxEntity("data_type_showcase");
        survey["mood"] = new ArxSelectionSetValue("Mood", "neutral");
        var surveyId = await dbContext.Service.CreateAsync(survey);

        Show($"stored: preferred_channel {(await dbContext.Direct.TextRowAsync("contact", contactId.AsInt64(), "preferred_channel"))["preferred_channel"]}, "
            + $"mood {(await dbContext.Direct.TextRowAsync("data_type_showcase", surveyId.AsInt64(), "mood"))["mood"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, preferred_channel
         * FROM contact
         * WHERE first_name = 'Kenji' AND last_name = 'Mori';
         *
         * SELECT mood
         * FROM data_type_showcase
         * WHERE mood = 'neutral';
         * -- Expect: preferred_channel S; mood neutral.
         * ===========================================================
         */
    }

    /// <summary>5. Sets loaded from JSON (SampleCatalog.SetsJson) are used exactly like the ones written in code.</summary>
    private static async Task Scenario5_SetsLoadedFromJsonAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("5. A lead source and a rating, from the sets the catalog loaded from JSON");

        var lead = new ArxEntity("contact");
        lead["account_id"] = new ArxEntityReference("account", accountId);
        lead["first_name"] = "Sofia";
        lead["last_name"] = "Rossi";
        lead["lead_source"] = new ArxSelectionSetValue("LeadSource", "EVENT");
        lead["rating"] = new ArxSelectionSetValue("ContactRating", 4);
        var leadId = await dbContext.Service.CreateAsync(lead);

        var storedLead = await dbContext.Direct.TextRowAsync("contact", leadId.AsInt64(), "lead_source", "rating");
        Show($"stored: lead_source {storedLead["lead_source"]}, rating {storedLead["rating"]}");

        var coldCall = new ArxEntity("contact");
        coldCall["first_name"] = "Max";
        coldCall["last_name"] = "Weber";
        coldCall["lead_source"] = new ArxSelectionSetValue("LeadSource", "COLD_CALL");   // "active": false in the JSON

        try
        {
            await dbContext.Service.CreateAsync(coldCall);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SelectionSetValueNotAllowed)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, lead_source, rating
         * FROM contact
         * WHERE (first_name, last_name) IN (('Sofia', 'Rossi'), ('Max', 'Weber'));
         * -- Expect: 1 row, Sofia Rossi, EVENT, 4: Max Weber's retired lead source was refused.
         * ===========================================================
         */
    }

    /// <summary>6. Values the catalog refuses: not an option, a retired option, two codes for a single choice, an unknown set.</summary>
    private static async Task Scenario6_ValuesTheCatalogRefusesAsync(SampleContext dbContext)
    {
        Step("6. Values the catalog refuses");

        var notAnOption = new ArxEntity("account");
        notAnOption["name"] = "Bad status";
        notAnOption["status"] = new ArxSelectionSetValue("AccountStatus", "DELETED");

        var retiredOption = new ArxEntity("account");
        retiredOption["name"] = "Retired segment";
        retiredOption["segments"] = ArxSelectionSetValue.Multiple("CustomerSegment", "SMB", "RESELLER");

        var twoForOne = new ArxEntity("account");
        twoForOne["name"] = "Two industries";
        twoForOne["industry"] = ArxSelectionSetValue.Multiple("Industry", Industry.Retail, Industry.Finance);

        var unknownSet = new ArxEntity("account");
        unknownSet["name"] = "Typo";
        unknownSet["industry"] = new ArxSelectionSetValue("Industries", Industry.Retail);

        foreach (var refused in new[] { notAnOption, retiredOption, twoForOne, unknownSet })
        {
            try
            {
                await dbContext.Service.CreateAsync(refused);
            }
            catch (ArxValidationException error)
            {
                ShowError(error);
            }
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name IN ('Bad status', 'Retired segment', 'Two industries', 'Typo');
         * -- Expect: 0: all four were refused before anything was sent.
         * ===========================================================
         */
    }

    /// <summary>
    /// 7. A plain value (not an ArxSelectionSetValue) isn't checked against the catalog; only the table's own constraints
    /// apply. Here the CHECK on status refuses it.
    /// </summary>
    private static async Task Scenario7_APlainStringSkipsTheCatalogAsync(SampleContext dbContext)
    {
        Step("7. A plain string skips the catalog");

        var plain = new ArxEntity("account");
        plain["name"] = "Plain value";
        plain["status"] = "DELETED";

        try
        {
            await dbContext.Service.CreateAsync(plain);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.CheckViolation)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name = 'Plain value';
         * -- Expect: 0: the table's CHECK on status refused it.
         * ===========================================================
         */
    }
}

  > 1. The application's catalog: one entry per selection set
      Industry         Integer      Single  1 = Retail, 2 = Manufacturing, 3 = Healthcare, 4 = Finance, 5 = Technology
      AccountStatus    String       Single  ACTIVE = Active, ON_HOLD = On hold, CLOSED = Closed
      CustomerSegment  String       Delimited SMB = Small business, ENTERPRISE = Enterprise, PUBLIC = Public sector, RESELLER = Reseller (retired)
      ContactChannel   Character    Single  E = Email, P = Phone, S = Text message
      Mood             String       Single  happy = Happy, neutral = Neutral, sad = Sad
      LeadSource       String       Single  WEB = Website, REFERRAL = Referral, EVENT = Trade show, COLD_CALL = Cold call (retired)
      ContactRating    Integer      Single  1 = Poor, 2 = Fair, 3 = Good, 4 = Very good, 5 = Excellent
      drop-down for AccountStatus: Active | On hold | Closed
  > 2. Without the selection sets in the catalog, a selection set value is refused
      caught ArxValidationException: ARX1012 UnknownSelectionSet: Can't write a selection set value: the catalog has no set named 'Industry' (attribute 'industry' on 'account').
  > 3. Industry (an enum), status (a string) and segments (several strings)
      stored: industry 2, status ON_HOLD, segments ENTERPRISE,PUBLIC
  > 4. A preferred channel (a character) and a mood (text values a CHECK guards)
      stored: preferred_channel S, mood neutral
  > 5. A lead source and a rating, from the sets the catalog loaded from JSON
      stored: lead_source EVENT, rating 4
      caught ArxValidationException: ARX1011 SelectionSetValueNotAllowed: Selection set 'LeadSource' has this option retired; it can be read but not written (attribute 'lead_source' on 'contact').
  > 6. Values the catalog refuses
      caught ArxValidationException: ARX1011 SelectionSetValueNotAllowed: Selection set 'AccountStatus' has no option with this code (or the code is of another kind) (attribute 'status' on 'account').
      caught ArxValidationException: ARX1011 SelectionSetValueNotAllowed: Selection set 'CustomerSegment' has this option retired; it can be read but not written (attribute 'segments' on 'account').
      caught ArxValidationException: ARX1011 SelectionSetValueNotAllowed: Selection set 'Industry' is single-choice, but several values were given (attribute 'industry' on 'account').
      caught ArxValidationException: ARX1012 UnknownSelectionSet: Can't write a selection set value: the catalog has no set named 'Industries' (attribute 'industry' on 'account').
  > 7. A plain string skips the catalog
      caught ArxDatabaseException: ARX3004 CheckViolation: Create on 'account' violates check constraint 'account_status_check'.