Hi Julian, all,
Per your request, here are the requirements spelled out, followed by string-in
→ SQL-out tests.
Language X = a tiny ACBP DSL for model definitions (equivalent to a JSON form).
Translator is stateless: (model, options, dialect) → SELECT/CASE. No separate
runtime query language.
Requirements (ACBP → Calcite)
Contract
* Input: ACBP model (DSL or JSON), an entry point name, and options.
* Output: One deterministic SELECT ... CASE ... END AS action_id string.
* Determinism: Same inputs produce identical SQL.
Scope (what the SELECT may contain)
* Predicates: =, <>, >=, AND/OR/NOT, IN (...), BETWEEN, parentheses.
* Subqueries: IN (SELECT ...), EXISTS (SELECT ...)
* Literals: strings, integers. No UDFs in the core examples.
* Time window: last N days via options.windowDays; rendered per dialect.
* Joins: kept out of these minimal examples (staging happens upstream).
Ref-table membership uses IN (SELECT ...) or EXISTS.
Semantics (governance & decision)
* Categories: finite enums enum('A','B',...) or enum(select code from
ref_...). Used for coverage/soundness/dedup proofs; affect SQL only when
referenced in predicates.
* Flags: named boolean predicates over columns/categories/ref tables.
* Decision: ordered rules; first-match-wins; exactly one integer action_id;
a default is required.
* NULLs: models use explicit IS NULL/IS NOT NULL when needed (kept out of
these minimal tests).
Dialects Targeted (Initial)
* PostgreSQL 16+: now() - interval 'N days'
* BigQuery StdSQL: TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL N DAY)
* ClickHouse: now() - INTERVAL N DAY
* Only the SELECT is in scope here; CTAS/REPLACE is templated outside
Calcite.
Verification
* Compare expected vs actual via a simple normalize (collapse whitespace +
lowercase),
* or parse both to SqlNode and compare trees if you prefer.
Sample Tests (Simple → Moderate), Plus Table-Driven Variant
Test A (Simple) — PostgreSQL (Inline Categories + Flags)
Input (ACBP DSL):
model hl7_v1 {
from hl7_messages
time_column event_ts
category message_type := enum('ADT','ORM','ORU')
category trigger_event := enum('A01','A04','A03','O01','R01')
category patient_class := enum('E','I','O')
flag is_admission := message_type = 'ADT' and trigger_event in ('A01','A04')
flag is_emergency := patient_class = 'E'
decision action_id {
when is_admission and is_emergency -> 2
else -> 1
}
}
Options:
windowDays = 2
dialect = PostgresqlSqlDialect.DEFAULT
entryPoint = "decision_space_hl7"
Expected SQL (PostgreSQL):
SELECT
msg_id,
event_ts,
CASE
WHEN (message_type = 'ADT' AND trigger_event IN ('A01','A04') AND patient_class
= 'E') THEN 2
ELSE 1
END AS action_id
FROM hl7_messages
WHERE event_ts >= now() - interval '2 days';
Test B (Moderate) — BigQuery (Inline Categories + Ref-Table Membership Flag)
Input (ACBP DSL):
model hl7_v1 {
from hl7_messages
time_column event_ts
category message_type := enum('ADT','ORM','ORU')
category obs_abn_flag := enum('H','L','N')
ref critical_loinc := "ref_critical_loinc"
flag abnormal_result := message_type = 'ORU' and obs_abn_flag in ('H','L')
flag critical_analyte := loinc_code in (select code from ref_critical_loinc)
decision action_id {
when abnormal_result and critical_analyte -> 3
when abnormal_result -> 2
else -> 1
}
}
Options:
windowDays = 2
dialect = BigQuerySqlDialect.DEFAULT
entryPoint = "decision_space_hl7"
Expected SQL (BigQuery):
SELECT
msg_id,
event_ts,
CASE
WHEN (message_type = 'ORU' AND obs_abn_flag IN ('H','L')
AND loinc_code IN (SELECT code FROM ref_critical_loinc)) THEN 3
WHEN (message_type = 'ORU' AND obs_abn_flag IN ('H','L')) THEN 2
ELSE 1
END AS action_id
FROM hl7_messages
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY);
Test C (Moderate) — ClickHouse (Inline Categories + STAT)
Input (ACBP DSL):
model hl7_v1 {
from hl7_messages
time_column event_ts
category message_type := enum('ADT','ORM','ORU')
category order_priority := enum('STAT','ROUTINE')
category obs_abn_flag := enum('H','L','N')
flag is_stat_order := message_type = 'ORM' and order_priority = 'STAT'
flag abnormal_result := obs_abn_flag in ('H','L')
decision action_id {
when is_stat_order and abnormal_result -> 3
when is_stat_order -> 2
else -> 1
}
}
Options:
windowDays = 2
dialect = ClickHouseSqlDialect.DEFAULT
entryPoint = "decision_space_hl7"
Expected SQL (ClickHouse):
SELECT
msg_id,
event_ts,
CASE
WHEN (message_type = 'ORM' AND order_priority = 'STAT'
AND obs_abn_flag IN ('H','L')) THEN 3
WHEN (message_type = 'ORM' AND order_priority = 'STAT') THEN 2
ELSE 1
END AS action_id
FROM hl7_messages
WHERE event_ts >= now() - INTERVAL 2 DAY;
Test D (Table-Driven) — PostgreSQL (Categories and Flags via Reference Tables)
Here, categories and a flag are governed by reference tables.
enum(select ...) allows ACBP to prove coverage/soundness against live
vocabularies; it only affects SQL when referenced.
Input (ACBP DSL):
model hl7_v1 {
from hl7_messages
time_column event_ts
// categories via reference tables
category message_type := enum(select code from ref_message_type) // e.g.,
('ADT'),('ORM'),('ORU')
category trigger_event := enum(select code from ref_trigger_event) // e.g.,
('A01'),('A04'),('A03'),...
category patient_class := enum(select code from ref_patient_class) // e.g.,
('E'),('I'),('O')
// flag driven by ref-table membership (governance-controlled)
ref emergency_classes := "ref_patient_class_emergency" // e.g.,
('E')
flag is_emergency := patient_class in (select code from
ref_patient_class_emergency)
// admission flag inline
flag is_admission := message_type = 'ADT' and trigger_event in ('A01','A04')
decision action_id {
when is_admission and is_emergency -> 2
else -> 1
}
}
Options:
windowDays = 2
dialect = PostgresqlSqlDialect.DEFAULT
entryPoint = "decision_space_hl7"
Expected SQL (PostgreSQL):
SELECT
msg_id,
event_ts,
CASE
WHEN (message_type = 'ADT'
AND trigger_event IN ('A01','A04')
AND patient_class IN (SELECT code FROM ref_patient_class_emergency)) THEN 2
ELSE 1
END AS action_id
FROM hl7_messages
WHERE event_ts >= now() - interval '2 days';
Minimal Test Harness (Illustrative)
@Test
void test_admission_emergency_postgres() {
var dsl = """
model hl7_v1 {
from hl7_messages
time_column event_ts
category message_type := enum('ADT','ORM','ORU')
category trigger_event := enum('A01','A04','A03','O01','R01')
category patient_class := enum('E','I','O')
flag is_admission := message_type = 'ADT' and trigger_event in ('A01','A04')
flag is_emergency := patient_class = 'E'
decision action_id { when is_admission and is_emergency -> 2 else -> 1 }
}
""";
String sql = acbpToSql(dsl, "decision_space_hl7",
PostgresqlSqlDialect.DEFAULT, Map.of("windowDays", 2));
String expected = """
SELECT msg_id, event_ts,
CASE
WHEN (message_type = 'ADT' AND trigger_event IN ('A01','A04') AND patient_class
= 'E') THEN 2
ELSE 1
END AS action_id
FROM hl7_messages
WHERE event_ts >= now() - interval '2 days'
""";
assertEquals(normalize(expected), normalize(sql));
}
private static String normalize(String s) {
return s.replaceAll("\\s+", " ").trim().toLowerCase();
}
** If preferred, both strings can instead be parsed to SqlNode and
tree-compared.
If this shape looks right, I'll share a tiny repo with:
* RelBuilder construction for these plans,
* Golden expected SQL for PostgreSQL, BigQuery, and ClickHouse,
* And a normalization helper. Any dialect edge we hit can become a Jira
with a failing test.
Best,
Muteb (DotK)
________________________________
From: Julian Hyde <[email protected]>
Sent: Wednesday, August 20, 2025 7:24 PM
To: [email protected] <[email protected]>
Subject: Re: [DISCUSS] ACBP × HL7: Calcite IR → multi-dialect SQL for
deterministic policy artifacts (Why ACBP, benefits, MWE, questions)
As I said, what would be most helpful to me would be to understand the
requirements. I’d love to see a simple or moderately complex test case - e.g.
this tool can transform this string in language X to this SQL string - than
read through the code that implements it.
Julian
> On Aug 19, 2025, at 17:42, Muteb Alanazi <[email protected]> wrote:
>
> Subscribed now ([email protected]). CC no longer needed.
>
> Happy to proceed with the HL7/ACBP test cases I shared. I can also attach:
> - a tiny repo with RelBuilder → RelToSql → SqlDialect producing the expected
> strings, and
> - a normalization helper (compare SqlNode trees or whitespace-collapsed SQL)
> if that helps reviewers.
>
> —Muteb
>
