Get started

dotnet --version

dotnet new console -n ArxSample -f net10.0
cd ArxSample

dotnet add package ARX.SDK.MySQL --version 1.0.0-rc.1

docker run --name arx-samples-mysql -e MYSQL_ROOT_PASSWORD=your-password -e MYSQL_DATABASE=arx_samples -p 3306:3306 -d mysql:8.4 --log-bin-trust-function-creators=1

macOS and Linux (bash, zsh)

docker exec -i arx-samples-mysql mysql -uroot -pyour-password arx_samples < sample-schema.sql

Windows PowerShell

Get-Content sample-schema.sql -Raw | docker exec -i arx-samples-mysql mysql -uroot -pyour-password arx_samples

The sample schema the samples run against.

-- Schema for the ARX.SDK MySQL documentation samples: a small CRM and product catalogue, and a table of every data type.
-- Run it in a database of its own (arx_samples). Re-runnable: it drops, by name, only the tables, views and routines it
-- creates below, so running it again starts over and nothing else is touched. Its last, optional part creates the
-- database arx_samples_reporting when it doesn't exist and replaces one view in it; it never drops a database.
-- A plain mysql client script: statements end with ;, and the procedures and functions are between DELIMITER $$
-- and DELIMITER ; (their bodies have semicolons of their own). The samples run it themselves; to run it by hand:
-- mysql -u <user> -p arx_samples < sample-schema.sql. The last part (a database of its own) is optional.
--
-- ARX conventions used here: the table name is the entity name, and a lookup column stores the referenced row's id.
-- Every table's id column is declared in SampleCatalog.cs: most are a generated BIGINT "id" (AUTO_INCREMENT); country
-- and device show a natural id and a supplied CHAR(36) Guid (MySQL can't hand back a generated Guid); price_list and
-- activity_log have no id column (NoIdColumn). Selection set columns hold the option's code itself.

DROP VIEW IF EXISTS vw_key_accounts, vw_account_overview, vw_contact_directory, vw_account_sales_summary;
DROP PROCEDURE IF EXISTS sp_recalculate_order_totals;
DROP PROCEDURE IF EXISTS sp_rename_account;
DROP PROCEDURE IF EXISTS sp_account_totals;
DROP PROCEDURE IF EXISTS sp_place_order;
DROP PROCEDURE IF EXISTS sp_order_with_lines;
DROP PROCEDURE IF EXISTS sp_account_report;
DROP PROCEDURE IF EXISTS sp_products_page;
DROP PROCEDURE IF EXISTS sp_products;
DROP PROCEDURE IF EXISTS sp_contacts_named;
DROP PROCEDURE IF EXISTS sp_accounts_with_status;
DROP PROCEDURE IF EXISTS sp_reserve_credit;
DROP PROCEDURE IF EXISTS sp_recalculate_totals;
DROP PROCEDURE IF EXISTS sp_import_progress;
DROP PROCEDURE IF EXISTS sp_slow;
DROP FUNCTION IF EXISTS fn_order_count;
DROP FUNCTION IF EXISTS fn_contact_count;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS legacy_customer, web_visit, activity_log, price_list, device, country, order_line, sales_order, data_type_showcase, product, product_category, contact, account;
SET FOREIGN_KEY_CHECKS = 1;

-- Customers. industry is an Integer selection set built from a .NET enum; status a String set the CHECK also guards;
-- segments a multiple-choice String set stored as delimited text. created_on and modified_on are TIMESTAMP(6): instants,
-- stored as UTC. A UNIQUE key allows many NULLs in MySQL, so several accounts may leave account_number empty.
CREATE TABLE account (
    id                 BIGINT         AUTO_INCREMENT PRIMARY KEY,
    guid               CHAR(36)       NOT NULL DEFAULT (UUID()),
    name               VARCHAR(200)   NOT NULL,
    account_number     VARCHAR(20)    NULL,
    industry           SMALLINT,
    status             VARCHAR(10)    NOT NULL DEFAULT 'ACTIVE',
    segments           VARCHAR(200),
    website            VARCHAR(200),
    annual_revenue     DECIMAL(18, 2),
    employee_count     INT,
    credit_limit       DECIMAL(18, 2),
    is_key_account     BOOLEAN        NOT NULL DEFAULT FALSE,
    founded_on         DATE,
    parent_account_id  BIGINT,
    primary_contact_id BIGINT,        -- references contact (added after contact below; the preload samples follow it)
    created_on         TIMESTAMP(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    modified_on        TIMESTAMP(6)   NULL,
    CONSTRAINT account_account_number_key UNIQUE (account_number),
    CONSTRAINT account_status_check CHECK (status IN ('ACTIVE', 'ON_HOLD', 'CLOSED')),
    CONSTRAINT account_employee_count_check CHECK (employee_count >= 0),
    CONSTRAINT account_parent_account_id_fkey FOREIGN KEY (parent_account_id) REFERENCES account (id)
);

-- People at a customer. account_id is a lookup (ArxEntityReference); email is unique. preferred_channel, lead_source
-- and rating are selection sets (lead_source and rating come from the catalog's JSON).
CREATE TABLE contact (
    id                BIGINT         AUTO_INCREMENT PRIMARY KEY,
    guid              CHAR(36)       NOT NULL DEFAULT (UUID()),
    account_id        BIGINT,
    first_name        VARCHAR(100)   NOT NULL,
    last_name         VARCHAR(100)   NOT NULL,
    job_title         VARCHAR(100),
    email             VARCHAR(254),
    phone             VARCHAR(30),
    preferred_channel CHAR(1),
    lead_source       VARCHAR(50),
    rating            SMALLINT,
    birthday          DATE,
    do_not_email      BOOLEAN        NOT NULL DEFAULT FALSE,
    consent_given_at  TIMESTAMP(6)   NULL,
    notes             TEXT,
    created_on        TIMESTAMP(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT contact_email_key UNIQUE (email),
    CONSTRAINT contact_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

-- account.primary_contact_id: a contact of the account, the reference a preload follows to add contact.
ALTER TABLE account ADD CONSTRAINT account_primary_contact_fkey FOREIGN KEY (primary_contact_id) REFERENCES contact (id);

CREATE TABLE product_category (
    id    BIGINT        AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100)  NOT NULL,
    CONSTRAINT product_category_name_key UNIQUE (name)
);

-- The catalogue. attributes is JSON (ArxJsonB; MySQL checks the text and stores its own form); image is binary; tags
-- is delimited text.
CREATE TABLE product (
    id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
    guid            CHAR(36)        NOT NULL DEFAULT (UUID()),
    sku             VARCHAR(32)     NOT NULL,
    name            VARCHAR(200)    NOT NULL,
    description     TEXT,
    category_id     BIGINT,
    unit_price      DECIMAL(12, 2)  NOT NULL,
    cost            DECIMAL(12, 4),
    weight_kg       DOUBLE,
    stock_quantity  INT             NOT NULL DEFAULT 0,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    tags            VARCHAR(400),
    attributes      JSON,
    image           LONGBLOB,
    launched_on     DATE,
    created_on      TIMESTAMP(6)    NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT product_sku_key UNIQUE (sku),
    CONSTRAINT product_unit_price_check CHECK (unit_price >= 0),
    CONSTRAINT product_category_id_fkey FOREIGN KEY (category_id) REFERENCES product_category (id)
);

-- An order and its lines, for the "one process, one transaction" samples.
CREATE TABLE sales_order (
    id           BIGINT          AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20)     NOT NULL,
    account_id   BIGINT          NOT NULL,
    ordered_on   TIMESTAMP(6)    NOT NULL,
    total        DECIMAL(14, 2)  NOT NULL DEFAULT 0,
    CONSTRAINT sales_order_order_number_key UNIQUE (order_number),
    CONSTRAINT sales_order_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

CREATE TABLE order_line (
    id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
    order_id    BIGINT          NOT NULL,
    product_id  BIGINT          NOT NULL,
    quantity    INT             NOT NULL,
    unit_price  DECIMAL(12, 2)  NOT NULL,
    CONSTRAINT order_line_quantity_check CHECK (quantity > 0),
    CONSTRAINT order_line_order_id_fkey FOREIGN KEY (order_id) REFERENCES sales_order (id),
    CONSTRAINT order_line_product_id_fkey FOREIGN KEY (product_id) REFERENCES product (id)
);

-- One column for every kind of value the MySQL provider writes (README, "Type mapping").
CREATE TABLE data_type_showcase (
    id                 BIGINT              AUTO_INCREMENT PRIMARY KEY,
    text_value         TEXT,
    varchar_value      VARCHAR(50),
    char_value         CHAR(3),
    tinyint_value      TINYINT,
    smallint_value     SMALLINT,
    int_value          INT,
    bigint_value       BIGINT,
    unsigned_value     INT UNSIGNED,
    decimal_value      DECIMAL(30, 6),
    float_value        FLOAT,
    double_value       DOUBLE,
    bool_value         BOOLEAN,
    uuid_value         CHAR(36),
    uuid_binary_value  BINARY(16),
    timestamp_value    TIMESTAMP(6)        NULL,
    datetime_value     DATETIME(6),
    datetime0_value    DATETIME,
    date_value         DATE,
    time_value         TIME(6),
    blob_value         LONGBLOB,
    json_value         JSON,
    mood               ENUM('happy', 'neutral', 'sad'),
    account_id         BIGINT,
    industry           SMALLINT,
    CONSTRAINT data_type_showcase_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

-- Ids that aren't a generated BIGINT: a natural text id and a Guid the application supplies. Then two tables with no id
-- column: a price identified by region and SKU together, and a log whose rows may repeat. All declared in
-- SampleCatalog.cs: ARX reads ids and keys from the database only when asked to preload them.
CREATE TABLE country (
    code  CHAR(2)       PRIMARY KEY,
    name  VARCHAR(100)  NOT NULL
);

CREATE TABLE device (
    id             CHAR(36)     PRIMARY KEY,
    serial_number  VARCHAR(50)  NOT NULL,
    account_id     BIGINT,
    CONSTRAINT device_serial_number_key UNIQUE (serial_number),
    CONSTRAINT device_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

CREATE TABLE price_list (
    region      CHAR(2)         NOT NULL,
    sku         VARCHAR(32)     NOT NULL,
    unit_price  DECIMAL(12, 2)  NOT NULL,
    valid_from  DATE            NOT NULL,
    PRIMARY KEY (region, sku),
    CONSTRAINT price_list_unit_price_check CHECK (unit_price >= 0),
    CONSTRAINT price_list_region_fkey FOREIGN KEY (region) REFERENCES country (code)
);

-- No primary key at all: the same action can be logged twice.
CREATE TABLE activity_log (
    account_id  BIGINT,
    action      VARCHAR(200)   NOT NULL,
    logged_at   TIMESTAMP(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT activity_log_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

-- A table made outside ARX, its columns named outside ARX's name rule: used through aliases (Create Advanced10).
CREATE TABLE legacy_customer (
    `Customer Code`  VARCHAR(20)     NOT NULL PRIMARY KEY,
    `first-name`     VARCHAR(100)    NOT NULL,
    `last-name`      VARCHAR(100)    NULL,
    `order.total`    DECIMAL(12, 2)  NULL
);

-- Web visits (declared NoIdColumn in SampleCatalog.cs).
CREATE TABLE web_visit (
    account_id  BIGINT,
    page        VARCHAR(200)  NOT NULL,
    visited_on  DATE          NOT NULL,
    CONSTRAINT web_visit_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (id)
);

-- Naming: views vw_, functions fn_, procedures sp_; tables have no prefix. The name says what an object is where only
-- the name is visible (a query, a log, an error message), and ARX reads them all by name.

-- Retrieve: a view an application reads like a table. MySQL has no functions that return rows: read a view, or call a
-- procedure through Execute.
CREATE VIEW vw_account_overview AS
SELECT a.id, a.name, a.account_number, a.status, COUNT(c.id) AS contacts
FROM account a
LEFT JOIN contact c ON c.account_id = a.id
GROUP BY a.id, a.name, a.account_number, a.status;

-- An account summary, computed at each read (MySQL has no materialized views).
CREATE VIEW vw_account_sales_summary AS
SELECT a.id AS account_id, a.name, a.account_number, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS revenue
FROM account a
LEFT JOIN sales_order o ON o.account_id = a.id
GROUP BY a.id, a.name, a.account_number;

DELIMITER $$

-- One value: a scalar function (the only kind MySQL has).
CREATE FUNCTION fn_order_count(p_account_id BIGINT) RETURNS BIGINT
READS SQL DATA
RETURN (SELECT COUNT(*) FROM sales_order WHERE account_id = p_account_id)$$

-- A procedure: not a row source (Execute's, not Retrieve's).
CREATE PROCEDURE sp_recalculate_order_totals()
UPDATE sales_order o SET total = COALESCE((SELECT SUM(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0)$$

DELIMITER ;

-- Views in depth: a directory of contacts without their private columns (email, phone, birthday, notes), and a view
-- built on another view.
CREATE VIEW vw_contact_directory AS
SELECT id, first_name, last_name, job_title, account_id
FROM contact;

CREATE VIEW vw_key_accounts AS
SELECT id, name, account_number, contacts
FROM vw_account_overview
WHERE contacts >= 2;

DELIMITER $$

-- Execute: procedures (sp_) and functions (fn_) the samples call.

-- Rename an account (no result); its totals for a year (IN, INOUT and OUT values). MySQL has no default parameter
-- values: every argument is passed.
CREATE PROCEDURE sp_rename_account(IN p_account_id BIGINT, IN p_name VARCHAR(200), IN p_reason VARCHAR(200))
UPDATE account SET name = p_name, modified_on = CURRENT_TIMESTAMP(6) WHERE id = p_account_id$$

CREATE PROCEDURE sp_account_totals(IN p_account_id BIGINT, INOUT p_year INT, OUT p_orders BIGINT, OUT p_revenue DECIMAL(18, 2))
BEGIN
    SET p_year = COALESCE(p_year, YEAR(UTC_TIMESTAMP()));
    SELECT COUNT(*), COALESCE(SUM(total), 0) INTO p_orders, p_revenue
    FROM sales_order WHERE account_id = p_account_id AND YEAR(ordered_on) = p_year;
END$$

-- A value; rows (a procedure, since MySQL functions return one value only).
CREATE FUNCTION fn_contact_count(p_account_id BIGINT) RETURNS BIGINT
READS SQL DATA
RETURN (SELECT COUNT(*) FROM contact WHERE account_id = p_account_id)$$

CREATE PROCEDURE sp_contacts_named(IN p_last_name VARCHAR(100))
SELECT id, first_name, job_title FROM contact WHERE last_name = p_last_name ORDER BY id$$

-- Whole account rows with a status: every column the table has, whatever a preload listed.
CREATE PROCEDURE sp_accounts_with_status(IN p_status VARCHAR(10))
SELECT * FROM account WHERE status = p_status ORDER BY id$$

-- Place an order: writes sales_order and order_line, returns the new id and the total. The SKUs are comma-separated.
CREATE PROCEDURE sp_place_order(IN p_account_id BIGINT, IN p_order_number VARCHAR(20), IN p_skus TEXT, IN p_quantity INT, OUT p_order_id BIGINT, OUT p_total DECIMAL(14, 2))
BEGIN
    INSERT INTO sales_order (order_number, account_id, ordered_on) VALUES (p_order_number, p_account_id, CURRENT_TIMESTAMP(6));
    SET p_order_id = LAST_INSERT_ID();
    INSERT INTO order_line (order_id, product_id, quantity, unit_price)
        SELECT p_order_id, p.id, p_quantity, p.unit_price FROM product p WHERE FIND_IN_SET(p.sku, REPLACE(p_skus, ' ', '')) > 0;
    SET p_total = (SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_line WHERE order_id = p_order_id);
    UPDATE sales_order SET total = p_total WHERE id = p_order_id;
END$$

-- Tables from a procedure: an order and its lines; an account report with three tables (result sets, in order).
CREATE PROCEDURE sp_order_with_lines(IN p_order_id BIGINT)
BEGIN
    SELECT o.order_number, o.ordered_on, o.total, a.name AS account FROM sales_order o JOIN account a ON a.id = o.account_id WHERE o.id = p_order_id;
    SELECT p.sku, l.quantity, l.unit_price FROM order_line l JOIN product p ON p.id = l.product_id WHERE l.order_id = p_order_id ORDER BY p.sku;
END$$

CREATE PROCEDURE sp_account_report(IN p_account_id BIGINT)
BEGIN
    SELECT first_name, last_name, job_title FROM contact WHERE account_id = p_account_id ORDER BY first_name;
    SELECT order_number, total FROM sales_order WHERE account_id = p_account_id ORDER BY order_number;
    SELECT (SELECT COUNT(*) FROM contact WHERE account_id = p_account_id) AS contacts,
           (SELECT COUNT(*) FROM sales_order WHERE account_id = p_account_id) AS orders,
           (SELECT COALESCE(SUM(total), 0) FROM sales_order WHERE account_id = p_account_id) AS revenue;
END$$

-- Paging: a procedure that pages itself; a procedure's result set ARX pages.
CREATE PROCEDURE sp_products_page(IN p_prefix VARCHAR(32), IN p_page INT, IN p_page_size INT, OUT p_total BIGINT)
BEGIN
    DECLARE v_skip INT DEFAULT (p_page - 1) * p_page_size;
    SET p_total = (SELECT COUNT(*) FROM product WHERE sku LIKE CONCAT(p_prefix, '%'));
    SELECT sku, name, unit_price FROM product WHERE sku LIKE CONCAT(p_prefix, '%') ORDER BY sku
    LIMIT p_page_size OFFSET v_skip;
END$$

CREATE PROCEDURE sp_products(IN p_prefix VARCHAR(32))
SELECT sku, name, unit_price FROM product WHERE sku LIKE CONCAT(p_prefix, '%') ORDER BY sku$$

-- Business rules the database enforces: an account on hold and a credit limit, each with its own error number (SIGNAL).
CREATE PROCEDURE sp_reserve_credit(IN p_account_id BIGINT, IN p_amount DECIMAL(18, 2))
BEGIN
    DECLARE v_limit DECIMAL(18, 2);
    DECLARE v_status VARCHAR(10);
    DECLARE v_message VARCHAR(400);
    SELECT credit_limit, status INTO v_limit, v_status FROM account WHERE id = p_account_id;

    IF v_status = 'ON_HOLD' THEN
        SET v_message = CONCAT('account ', p_account_id, ' is on hold: release the hold first');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message, MYSQL_ERRNO = 50101;
    END IF;

    IF p_amount > COALESCE(v_limit, 0) THEN
        SET v_message = CONCAT('credit limit exceeded: limit ', COALESCE(v_limit, 0), ', asked ', p_amount, '; reserve ', COALESCE(v_limit, 0), ' or less');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message, MYSQL_ERRNO = 50102;
    END IF;

    UPDATE account SET credit_limit = credit_limit - p_amount, modified_on = CURRENT_TIMESTAMP(6) WHERE id = p_account_id;
END$$

-- A nightly job that commits each batch (MySQL lets a procedure COMMIT) and reports what it did as a note.
CREATE PROCEDURE sp_recalculate_totals(IN p_prefix VARCHAR(20), IN p_batch INT)
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_rows INT DEFAULT 1;
    DECLARE v_message VARCHAR(200);

    WHILE v_rows > 0 DO
        START TRANSACTION;
        UPDATE sales_order o
        JOIN (SELECT id FROM sales_order WHERE order_number LIKE CONCAT(p_prefix, '%') AND total < 0 ORDER BY id LIMIT p_batch) AS b ON b.id = o.id
        SET o.total = COALESCE((SELECT SUM(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0);
        SET v_rows = ROW_COUNT();
        COMMIT;
        SET v_done = v_done + v_rows;
    END WHILE;

    SET v_message = CONCAT('done: ', v_done, ' orders');
    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = v_message, MYSQL_ERRNO = 1642;
END$$

-- A note at the end: SIGNAL with an SQLSTATE of class 01 is a warning, and the procedure goes on. MySQL keeps only the
-- last statement's warnings, so a procedure reports one note when it ends, not progress while it runs.
CREATE PROCEDURE sp_import_progress(IN p_rows INT)
BEGIN
    DECLARE v_message VARCHAR(128);
    SET v_message = CONCAT('imported ', p_rows, ' rows in ', FLOOR(p_rows / 250), ' steps of 250', IF(p_rows > 900, ': a large import', ''));
    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = v_message, MYSQL_ERRNO = 1642;
END$$

-- A slow procedure. SLEEP returns 1 when a timeout or a cancellation wakes it (KILL QUERY) instead of failing, so the
-- procedure raises MySQL's own "interrupted" error, as any long statement would.
CREATE PROCEDURE sp_slow(IN p_seconds INT)
BEGIN
    DECLARE v_woke INT;
    SELECT SLEEP(p_seconds) INTO v_woke;
    IF v_woke = 1 THEN
        SIGNAL SQLSTATE '70100' SET MESSAGE_TEXT = 'Query execution was interrupted', MYSQL_ERRNO = 1317;
    END IF;
END$$

DELIMITER ;

-- Optional: a view in another database (MySQL's schema), read as "arx_samples_reporting.vw_product_catalogue" by
-- RetrieveMultiple's Advanced 7 (ReadOtherRowSources). Creating a database needs the CREATE privilege on it: a user
-- without it can leave this part out (the samples skip it and say so; only that sample needs it).
CREATE DATABASE IF NOT EXISTS arx_samples_reporting;
DROP VIEW IF EXISTS arx_samples_reporting.vw_product_catalogue;

CREATE VIEW arx_samples_reporting.vw_product_catalogue AS
SELECT p.id, p.sku, p.name, p.unit_price, c.name AS category
FROM product p
LEFT JOIN product_category c ON c.id = p.category_id
WHERE p.is_active = TRUE;

macOS and Linux (bash, zsh)

export ARX_SAMPLES_MYSQL="Server=localhost;Port=3306;Database=arx_samples;User ID=root;Password=your-password"

Windows PowerShell

$env:ARX_SAMPLES_MYSQL = "Server=localhost;Port=3306;Database=arx_samples;User ID=root;Password=your-password"

dotnet run