# The ARX.SDK sample application for MySQL
The samples run inside a small application of their own. Their code refers to these files, which aren't part of the ARX packages: `dbContext` (SampleContext.cs) is what each sample is given, `dbContext.Service` the application's ARX service, and the tables' ids, keys, lookups and display names are declared in the schema catalog below. In your application, declare your own tables the same way and create the service once.
`dbContext.Direct` (SampleContext.Direct.cs and the provider's DirectHelper) is the samples' checking code on the plain driver: it resets the sample database and reads rows back to show them. It isn't part of ARX and isn't how an application uses ARX: leave those lines out of code you copy.
## SampleContext.cs
What every sample is given: the connection string (and the ISample interface the samples implement).
```csharp
namespace ArxSamples.MySQL;
/// One documentation sample: a scenario a real application has, shown end to end.
public interface ISample
{
/// The name to run it alone: dotnet run -- <name>.
string Name { get; }
/// One line on what it shows.
string Title { get; }
Task RunAsync(SampleContext dbContext);
}
///
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain MySqlConnector read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
///
public sealed partial class SampleContext
{
public SampleContext(string connectionString)
{
ConnectionString = connectionString;
}
public string ConnectionString { get; }
}
```
## SampleOutput.cs
How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.
```csharp
using ARX.SDK;
namespace ArxSamples.MySQL;
/// How the samples print: a numbered step for each scenario, then what it did.
public static class SampleOutput
{
/// The start of a scenario: Step("1. By the id Create returned").
public static void Step(string text) => Console.WriteLine($" > {text}");
/// What the scenario did.
public static void Show(string text) => Console.WriteLine($" {text}");
/// An ARX error the scenario caught on purpose.
public static void ShowError(ArxException exception)
=> Console.WriteLine($" caught {exception.GetType().Name}: {exception.Message}");
}
```
## SampleContext.Application.cs
The application's ARX service and its schema catalog, created once and shared by the samples.
```csharp
using ARX.SDK.MySQL.Services;
using ARX.SDK.Services;
namespace ArxSamples.MySQL;
/// The application's ARX service and its schema catalog, shared by the real and advanced samples.
public sealed partial class SampleContext
{
private ArxSchemaCatalog? _catalog;
private ArxMySqlDbService? _service;
/// The application's schema catalog: table ids, keys and selection sets (SampleCatalog.cs).
public ArxSchemaCatalog Catalog => _catalog ??= SampleCatalog.Build();
///
/// One service for the whole application. It holds no connection: every call opens one from the MySqlConnector pool and
/// closes it, unless you pass your own connection or transaction.
///
public ArxMySqlDbService Service => _service ??= new ArxMySqlDbService(ConnectionString, Catalog);
}
```
## SampleCatalog.cs
The application's schema catalog: every table's id column and keys, and its selection sets.
```csharp
using ARX.SDK.Services;
using System.Text.Json;
using System.Text.Json.Serialization;
namespace ArxSamples.MySQL;
/// An account's industry. Stored in account.industry (SMALLINT) as the enum's number.
public enum Industry : short
{
Retail = 1,
Manufacturing = 2,
Healthcare = 3,
Finance = 4,
Technology = 5
}
///
/// The application's schema catalog: every table's id column and keys, and every picklist (selection set),
/// defined once.
///
///
///
/// Ids. Every table the application writes is declared with its one id column: its name, its .NET type, and
/// whether the database generates it (ArxIdColumn.Generated: identity, sequence, default) or the caller supplies
/// it (ArxIdColumn.Supplied: a natural id, a client-generated Guid). A table with no id column, such as a log or a
/// table identified by several columns together, is declared ArxIdColumn.NoIdColumn. ARX never reads ids from
/// the database: the developer knows the schema, and an undeclared table is refused (ARX1016).
///
///
/// Keys. Optional business keys: one or more columns whose values identify a record, such as an account number,
/// or a region and a SKU together. Create doesn't use them (the database's unique constraints do the enforcing); Update
/// and Delete will be able to find rows by them. ValidateSchemaAsync checks ids and keys against the database
/// when you ask it to.
///
///
/// Selection sets. /// A selection set (picklist, option set) is a column that only accepts a fixed list of codes, each with a display
/// text: an account's status (ACTIVE, ON_HOLD, CLOSED), a contact's preferred channel (E, P, S). The catalog tells ARX,
/// for each set:
///
///
/// - Name: what an refers to, for example new ArxSelectionSetValue("AccountStatus", "ON_HOLD").
/// - Kind: what the codes are, matching the column: Integer, String, Character, Boolean, Decimal or Guid.
/// (A fixed list of text values is a String set, whether the column is an ENUM or a VARCHAR that a CHECK guards.)
/// - Multiplicity: Single (one code), or several codes stored as bit flags (Flags), a delimited string
/// (Delimited) or a JSON array (Json). MySQL has no array columns.
/// - Options: each code with its display text and order. A retired option (IsActive: false) stays
/// readable in old rows, but can't be written any more.
///
///
/// Build the catalog once at startup and give it to the service: new ArxMySqlDbService(connectionString, SampleCatalog.Build()).
/// ARX checks every against it in memory, before any SQL is sent, and stores the code
/// itself in the column: no lookup table, no extra query. A plain value (a string or number that isn't an
/// ArxSelectionSetValue) isn't checked.
///
///
/// The catalog is plain code, so the options can come from wherever you keep them: written out here, taken from a .NET
/// enum (AddEnum), loaded from JSON ( and below), or loaded at
/// startup from your own option table and passed to new ArxSelectionSetDefinition(name, kind, options). ARX
/// never reads option tables or files itself.
///
///
public static class SampleCatalog
{
///
/// Selection sets kept as JSON, as an application might keep them in a file, a settings store or a database column.
/// Here the JSON is in the class itself; in an application, read it from wherever you keep it
/// (File.ReadAllText("selection-sets.json"), configuration, an HTTP call) and pass it to .
/// "multiplicity" and "active" are optional (Single and true).
///
public const string SetsJson = """
[
{
"name": "LeadSource",
"kind": "String",
"options": [
{ "value": "WEB", "text": "Website", "order": 0 },
{ "value": "REFERRAL", "text": "Referral", "order": 1 },
{ "value": "EVENT", "text": "Trade show", "order": 2 },
{ "value": "COLD_CALL", "text": "Cold call", "order": 3, "active": false }
]
},
{
"name": "ContactRating",
"kind": "Integer",
"multiplicity": "Single",
"options": [
{ "value": 1, "text": "Poor" },
{ "value": 2, "text": "Fair" },
{ "value": 3, "text": "Good" },
{ "value": 4, "text": "Very good" },
{ "value": 5, "text": "Excellent" }
]
}
]
""";
public static ArxSchemaCatalog Build()
{
var builder = ArxSchemaCatalog.Create()
// ---------- tables: id column and keys ----------
// Generated BIGINT ids (AUTO_INCREMENT): the database fills them in. The keys are the unique columns that also
// identify a record.
.Table("account", ArxIdColumn.Generated("id")).Key("account", "account_number")
.Table("contact", ArxIdColumn.Generated("id")).Key("contact", "email")
// Retrieve: a contact's account_id reads back as a reference to its account, named by the account's
// name; a contact is named by its first and last names.
.Lookup("contact", "account_id", "account")
.DisplayName("account", "name")
.DisplayName("contact", new[] { "first_name", "last_name" }, " ")
.Table("product_category", ArxIdColumn.Generated("id")).Key("product_category", "name")
.Table("product", ArxIdColumn.Generated("id")).Key("product", "sku")
.Table("sales_order", ArxIdColumn.Generated("id")).Key("sales_order", "order_number")
.Table("order_line", ArxIdColumn.Generated("id"))
.Table("data_type_showcase", ArxIdColumn.Generated("id"))
// A natural id: the caller gives the ISO country code.
.Table("country", ArxIdColumn.Supplied("code"))
// A Guid id the application gives (CHAR(36)): MySQL can only hand back generated integer ids.
.Table("device", ArxIdColumn.Supplied("id")).Key("device", "serial_number")
// No id column: a price is identified by its region and SKU together, a compound key.
.Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
// No primary key at all: the same action can be logged twice.
.Table("activity_log", ArxIdColumn.NoIdColumn)
// A table without an id: a visit is a fact, never updated (Retrieve Advanced8).
.Table("web_visit", ArxIdColumn.NoIdColumn)
// ---------- selection sets ----------
// From a .NET enum: the set is named after the enum ("Industry"), the codes are its numbers (Retail = 1, …),
// the display texts are its member names. Column: account.industry SMALLINT.
.AddEnum()
// Written out: String codes with display texts, single choice. Column: account.status VARCHAR(10).
.Add("AccountStatus", ArxSelectionSetKind.String, ("ACTIVE", "Active"), ("ON_HOLD", "On hold"), ("CLOSED", "Closed"))
// Multiple choice, stored as delimited text ("ENTERPRISE,PUBLIC"). RESELLER is retired: rows that have it still
// read back, but new writes can't use it. Column: account.segments VARCHAR(200).
.Add(new ArxSelectionSetDefinition("CustomerSegment", ArxSelectionSetKind.String, new[]
{
new ArxSelectionSetOption("SMB", "Small business", 0),
new ArxSelectionSetOption("ENTERPRISE", "Enterprise", 1),
new ArxSelectionSetOption("PUBLIC", "Public sector", 2),
new ArxSelectionSetOption("RESELLER", "Reseller", 3, IsActive: false)
}, ArxSelectionSetMultiplicity.Delimited))
// Single-character codes. Column: contact.preferred_channel CHAR(1).
.Add("ContactChannel", ArxSelectionSetKind.Character, ('E', "Email"), ('P', "Phone"), ('S', "Text message"))
// A fixed list of text values, in a MySQL ENUM column, which allows only its labels.
// Column: data_type_showcase.mood ENUM('happy', 'neutral', 'sad').
.Add(new ArxSelectionSetDefinition("Mood", ArxSelectionSetKind.String, new[]
{
new ArxSelectionSetOption("happy", "Happy", 0),
new ArxSelectionSetOption("neutral", "Neutral", 1),
new ArxSelectionSetOption("sad", "Sad", 2)
}));
// Loaded from JSON: LeadSource (contact.lead_source VARCHAR(50)) and ContactRating (contact.rating SMALLINT).
foreach (var set in FromJson(SetsJson))
{
builder.Add(set);
}
return builder.Build();
}
/// Reads selection set definitions from JSON (the shape of ).
public static IEnumerable FromJson(string json)
{
var options = new JsonSerializerOptions
{
PropertyNameCaseInsensitive = true,
Converters = { new JsonStringEnumConverter() } // "String", "Integer", "Array", … as names
};
var sets = JsonSerializer.Deserialize>(json, options)
?? throw new InvalidOperationException("The selection set JSON is empty.");
foreach (var set in sets)
{
yield return new ArxSelectionSetDefinition(
set.Name,
set.Kind,
set.Options.Select(x => new ArxSelectionSetOption(Code(x.Value, set.Kind), x.Text, x.Order, x.Active)),
set.Multiplicity,
set.DatabaseTypeName);
}
}
/// A JSON value as a code of the set's kind: JSON has no char, Guid or 64-bit integer of its own.
private static object Code(JsonElement value, ArxSelectionSetKind kind) => kind switch
{
ArxSelectionSetKind.Integer => value.GetInt64(),
ArxSelectionSetKind.Decimal => value.GetDecimal(),
ArxSelectionSetKind.Boolean => value.GetBoolean(),
ArxSelectionSetKind.Guid => value.GetGuid(),
ArxSelectionSetKind.Character => value.GetString()![0],
_ => value.GetString()! // String and DatabaseEnum
};
private sealed record JsonSet(
string Name,
ArxSelectionSetKind Kind,
List Options,
ArxSelectionSetMultiplicity Multiplicity = ArxSelectionSetMultiplicity.Single,
string? DatabaseTypeName = null);
private sealed record JsonOption(JsonElement Value, string Text, int Order = 0, bool Active = true);
}
```
## SecurityGuard.cs
A security check around ARX calls: suspected injection logged as a security event, strikes, blocking.
```csharp
using ARX.SDK;
namespace ArxSamples.MySQL;
/// Where the names (tables, attributes, match columns) in a call come from. It decides what an ARX1017 means.
public enum NameSource
{
/// A name built from what a user sent (a form field, a file header, a query string): ARX1017 is a suspected attack.
UserInput,
/// A name from your code or configuration: ARX1017 is a bug in your code, not an attack. Nobody is blocked.
Code,
}
/// What the guard did with a call.
public enum GuardOutcome
{
/// The call ran.
Done,
/// ARX refused the call with ARX1017; the event is logged and the user got a plain message.
Refused,
/// The user was already blocked: the call wasn't attempted.
Blocked,
}
/// One entry of the security log: a suspected attack, or a bug when the names came from code.
public sealed record SecurityEvent(DateTimeOffset At, string User, string Action, string Kind, string Detail);
/// The outcome, the call's result when it ran, and the message to show the user (never the detection details).
public sealed record GuardResult(GuardOutcome Outcome, T? Value, string ForTheUser);
///
/// A security check around ARX calls: the pattern to copy into an application, kept small so it can be read in one
/// go. It lets every ARX error through except ARX1017 SuspectedInjection, which it turns into:
///
/// - an entry in the security log (), with the user, the action and ARX's message (the name is
/// escaped and cut to 64 characters by ARX, so it is safe to log);
/// - a plain message for the user that doesn't say what was detected;
/// - a strike against the user. After blockAfter strikes the user is blocked and their calls aren't attempted.
/// One strike never blocks anyone, so a single false positive only costs one refused request;
/// - no strike when the names came from your own code (): that's a bug to fix, logged
/// as one.
///
/// A false positive (a harmless name such as a file header E-mail (work) flagged as SQL) is handled by triage:
/// read the event, fix the mapping that let the name through, and the user if needed.
/// In a real application the log goes to your SIEM or security monitoring and the strikes live in a shared store
/// (Redis, the database), not in memory.
///
public sealed class SecurityGuard
{
private const string ATTACK = "suspected attack";
private const string BUG = "bug";
private readonly object _gate = new();
private readonly int _blockAfter;
private readonly Dictionary _strikes = new(StringComparer.Ordinal);
private readonly List _events = new();
public SecurityGuard(int blockAfter = 3)
{
_blockAfter = blockAfter;
}
/// The security log, oldest first.
public IReadOnlyList Events
{
get
{
lock (_gate)
{
return _events.ToArray();
}
}
}
public bool IsBlocked(string user)
{
lock (_gate)
{
return _strikes.TryGetValue(user, out var strikes) && strikes >= _blockAfter;
}
}
/// After triage found the events were a false positive: clears the user's strikes (the log is kept).
public void Unblock(string user)
{
lock (_gate)
{
_strikes.Remove(user);
}
}
/// Runs for unless they are blocked, and handles ARX1017 as described above.
public async Task> RunAsync(string user, string action, NameSource names, Func> call)
{
if (IsBlocked(user))
{
return new GuardResult(GuardOutcome.Blocked, default, "Your access is suspended. Contact support.");
}
try
{
return new GuardResult(GuardOutcome.Done, await call(), "Done.");
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
lock (_gate)
{
if (names == NameSource.Code)
{
_events.Add(new SecurityEvent(DateTimeOffset.UtcNow, user, action, BUG, error.Message));
return new GuardResult(GuardOutcome.Refused, default, "Something went wrong on our side. It has been reported.");
}
_strikes[user] = _strikes.GetValueOrDefault(user) + 1;
_events.Add(new SecurityEvent(DateTimeOffset.UtcNow, user, action, ATTACK, error.Message));
}
return new GuardResult(GuardOutcome.Refused, default, "The request couldn't be processed.");
}
}
}
```