Skip to content

SQL Compatibility Report

Jump to bottom
Matthew Barker edited this page Jul 31, 2026 · 3 revisions

SQL Compatibility Report

Wondering whether A3SQL handles your mod's SQL before you commit to it? Run your queries through the compatibility report and get an instant answer.

What it is

tools/sql_corpus/ holds realistic SQL workloads modeled on what real Arma mods do — stat trackers, admin command systems, loadout managers, player persistence. Each statement runs through the real extension binary (the same C ABI Arma uses), so the report reflects actual behavior, not theory.

tools/sql_corpus/
├── 01_stats.sql            # kill tracking, leaderboards, aggregates, TTL cleanup
├── 02_admin.sql            # ban/kick queues, whitelists, audit trails
├── 03_loadouts.sql         # template CRUD, fuzzy search, progression, FK cascade
└── 04_sqf_persistence.sql  # profileNamespace-style key-value, player blobs,
                            #   stat counters, event feeds, per-player state maps

Running it

# Against a built binary (auto-detects a3sql_x64.so / release .so)
python3 tools/sql_compat_report.py

# Or with an explicit binary
python3 tools/sql_compat_report.py --bin a3sql_x64.so

Current result: 71/71 statements pass across all four domains.

Testing YOUR mod's SQL

Drop your own queries into a file and run it through the same runner:

python3 tools/sql_compat_report.py my_mod.sql

No SQL in your mod? The SQF-persistence corpus (04_sqf_persistence.sql) models what mods actually do today — profileNamespace key-value stores, ACE-Arsenal-style loadout blobs, stat counters, missionNamespace event feeds, per-player state maps. Run it to see the a3sql equivalent of each pattern works before you migrate.

File format — plain SQL, one statement per line (may span lines, end with ;):

# comments are ignored
CREATE TABLE my_table (id INTEGER PRIMARY KEY, name TEXT);

# assert the next statement returns OK containing a value
# expect contains "PlayerOne"
SELECT name FROM my_table;

# assert the next statement FAILS (e.g. a UNIQUE violation)
# expect error
INSERT INTO my_table VALUES (1, 'dup');

What the corpus found (and fixed)

The corpus is not a formality — it has already caught real engine gaps:

Gap Query shape Status
Self-referential UPDATE UPDATE t SET col = col + 1 Fixed — SET now evaluates with row context
Composite primary keys PRIMARY KEY (uid, state_key) Fixed — table-level constraints now mark all columns; duplicates rejected
Auto rowid PK INSERT INTO t (v) VALUES ('x') Fixed — INTEGER PRIMARY KEY auto-assigns
Date arithmetic WHERE ts < datetime('now', '-7 days') Fixed — SQLite modifiers supported
SQLite functions instr/ltrim/rtrim/typeof/char/strftime Fixed

How it differs from the smoke test

  • Smoke test (sql_smoke_test.py): the mod's own production SQL — a regression gate for this repo's schema.
  • Compatibility report (this): realistic third-party workload shapes — a dialect-coverage gate that grows as mods surface new query patterns.

Both run in CI on every push. If your mod hits something neither covers, add it to the corpus — it protects every future user.

Clone this wiki locally