Syntax¶

Named arguments¶

Synalog uses named arguments only: there are no positional arguments. In Predicate(column_name: variable), the left side of : is the column name in the predicate, and the right side is your variable name:

# column "amount" bound to variable "total"
Orders(amount: total)

# shorthand: column and variable share the same name
Orders(amount:)

Left side is the column, right side is the variable

Orders(total: amount) does not bind the amount column to total; it looks for a column named total. When in doubt, write the column name on the left.

Variables and expressions¶

Variables are defined with ==:

OrderWithTax(order_id:, total:) :-
  Orders(order_id:, amount:),
  total == amount * 1.10;

Operators¶

Category	Operators
Arithmetic	`+` `-` `` `/` `^` (power) `%` (remainder, with the sign of the dividend: `-7 % 3` is `-1`, `7.5 % 2` is `1.5`); `/` divides exactly (`7 / 2` is `3.5`); a division or remainder by zero has no value (null); a number with a decimal point is a double (`0.1 + 0.2 == 0.3` does not hold); `-` negates also right after another operator (`2 -3`)
String concatenation	`++`
Comparison	`==` `!=` `<` `>` `<=` `>=`
Boolean	`&&` `\\|\\|` `!`; a boolean variable or field alone is a condition (`Active(id:) :- Person(id:, active:), active;`), as `!active` is
Membership	`x in [1, 2, 3]`, `x in l` (see Lists)
Null tests	`x is null`, `x is not null`

Strings¶

A string is written in double quotes, "north", single quotes, 'north', or triple double quotes, """north""". Only single-quoted strings take backslash escapes ('it\'s', 'a\tb'); a double-quoted string ends at the next ", so to put a double quote in a string, single-quote it or triple-quote it:

Quote(a: 'say "hi"', b: """say "hi" there""", c: 'it\'s');

Never compare against null with !=

x != null is silently broken (it follows SQL three-valued logic and never matches). Always use x is null / x is not null.

Logical operators¶

Conjunction (AND): comma , joins predicates:

Result(x:, y:) :- TableA(x:), TableB(x:, y:);

Disjunction (OR): pipe | combines results (UNION ALL semantics; add distinct to deduplicate):

Combined(x:) distinct :- SourceA(x:) | SourceB(x:);

A disjunction gives one row per alternative that holds: a row matching both alternatives comes out twice, which matters to an aggregate (n? += 1) and to a rule without distinct. Alternatives can be conjunctions in parentheses, and a variable can be bound by either alternative:

Related(x:) distinct :- Pair(a: 1, b: x) | Pair(a: x, b: 1);
Picked(id:) distinct :- Item(id:, tag:, price:), ((tag == "x", price > 20) | (tag is null, price < 20));

Negation (NOT): tilde ~:

Inactive(user_id:) :- Users(user_id:), ~Logins(user_id:);

Multiple rule definitions: defining the same predicate several times unions the bodies:

HighValue(user_id:) :- Orders(user_id:, amount:), amount > 10000;
HighValue(user_id:) :- Referrals(user_id:, tier: "vip");

A practical combination: contact customers by email when available, otherwise by phone:

@OrderBy(ContactableCustomer, "customer_id");
ContactableCustomer(customer_id:, channel:) distinct :-
  Customers(customer_id:, email:), email is not null, channel == "email" |
  Customers(customer_id:, phone:), phone is not null, channel == "phone";

Null handling¶

MissingEmail(user_id:) :- Users(user_id:, email:), email is null;
HasEmail(user_id:, email:) :- Users(user_id:, email:), email is not null;
UserDisplay(user_id:, name:) :- Users(user_id:, full_name:), name == Coalesce(full_name, "Anonymous");

A null equals nothing, another null included: a join on a column finds no row where the key is null (A(k:), B(k:) with both k null), and neither does a negation (~B(k:) holds). Replace a null key before joining when it should match (B(k: Coalesce(k, 0))).

Conditionals¶

if … then … else expressions, chainable with else if:

OrderSize(order_id:, size:) :-
  Orders(order_id:, amount:),
  size == (if amount > 1000 then "large"
           else if amount > 100 then "medium"
           else "small");

Lists¶

A list is written [1, 2, 3], and a column can hold one. x in l, with x not bound elsewhere, gives a row for each element of l: an element repeated in l gives as many rows, and an empty or null list gives none. With x bound, it holds once for each element equal to x, so add distinct when a value may repeat:

OrderItem(order_id:, item:) :- Orders(order_id:, items:), item in items;
HasGift(order_id:) distinct :- Orders(order_id:, items:), "gift" in items;

See Array functions for Size, Element and the others, and combine to aggregate a row's list.

Records¶

Build nested record values with {field:, field:}:

UserInfo(user_id:, info:) :- Users(user_id:, name:, email:), info == {name:, email:};

Read a field with a dot, also of a nested record: info.name, person.home.city. A field is a value like any other: in a condition, a group key, a join key, an aggregate. Two records are equal when their fields are (person.home == {city: "Paris"}), and a list of records can be collected (List= {name:, age:}) and unnested back (p in people, name == p.name):

SameCity(a:, b:) :- Person(id: a, info: x), Person(id: b, info: y), a < b,
  x.home.city == y.home.city;

Complete example¶

Variables, disjunction, negation, conditionals and null handling in one runnable program:

# run: OrderWithTax, ContactableCustomer, Inactive, OrderSize, UserDisplay
@Engine("duckdb");

Customers(customer_id: 1, full_name: "Ada Lovelace", email: "ada@example.com", phone: null);
Customers(customer_id: 2, full_name: null, email: null, phone: "555-0102");
Customers(customer_id: 3, full_name: "Grace Hopper", email: "grace@example.com", phone: "555-0103");

Orders(order_id: 10, customer_id: 1, amount: 1500);
Orders(order_id: 11, customer_id: 2, amount: 240);
Orders(order_id: 12, customer_id: 3, amount: 40);

Logins(customer_id: 1);
Logins(customer_id: 3);

# Variables are defined with ==.
@OrderBy(OrderWithTax, "order_id");
OrderWithTax(order_id:, total:) :-
  Orders(order_id:, amount:),
  total == amount * 1.10;

# Disjunction (|) with null tests: email when available, otherwise phone.
@OrderBy(ContactableCustomer, "customer_id");
ContactableCustomer(customer_id:, channel:) distinct :-
  Customers(customer_id:, email:), email is not null, channel == "email" |
  Customers(customer_id:, phone:), phone is not null, channel == "phone";

# Negation (~): customers that never logged in.
@OrderBy(Inactive, "customer_id");
Inactive(customer_id:) :- Customers(customer_id:), ~Logins(customer_id:);

# Conditional expression.
@OrderBy(OrderSize, "order_id");
OrderSize(order_id:, size:) :-
  Orders(order_id:, amount:),
  size == (if amount > 1000 then "large"
           else if amount > 100 then "medium"
           else "small");

# Coalesce for null handling.
@OrderBy(UserDisplay, "customer_id");
UserDisplay(customer_id:, name:) :-
  Customers(customer_id:, full_name:),
  name == Coalesce(full_name, "Anonymous");

Generated SQL and execution results

$ synalog.check('syntax.l')
No errors found.

$ synalog.compile('syntax.l', 'OrderWithTax')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Orders AS (SELECT * FROM (

    SELECT
      10 AS order_id,
      1 AS customer_id,
      1500 AS amount
   UNION ALL

    SELECT
      11 AS order_id,
      2 AS customer_id,
      240 AS amount
   UNION ALL

    SELECT
      12 AS order_id,
      3 AS customer_id,
      40 AS amount

) AS UNUSED_TABLE_NAME  )
SELECT
  Orders.order_id AS order_id,
  ((Orders.amount) * (1.10E0)) AS total
FROM
  t_0_Orders AS Orders ORDER BY order_id;

-- Executed on DuckDB:
| order_id | total              |
|----------|--------------------|
| 10       | 1650.0000000000002 |
| 11       | 264.0              |
| 12       | 44.0               |
(3 rows)

$ synalog.compile('syntax.l', 'ContactableCustomer')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_Customers AS (SELECT * FROM (

    SELECT
      1 AS customer_id,
      'Ada Lovelace' AS full_name,
      'ada@example.com' AS email,
      null AS phone
   UNION ALL

    SELECT
      2 AS customer_id,
      null AS full_name,
      null AS email,
      '555-0102' AS phone
   UNION ALL

    SELECT
      3 AS customer_id,
      'Grace Hopper' AS full_name,
      'grace@example.com' AS email,
      '555-0103' AS phone

) AS UNUSED_TABLE_NAME  ),
t_0_ContactableCustomer_MultBodyAggAux AS (SELECT * FROM (

    SELECT
      Customers.customer_id AS customer_id,
      'email' AS channel
    FROM
      t_1_Customers AS Customers
    WHERE
      (Customers.email IS NOT null)
   UNION ALL

    SELECT
      t_2_Customers.customer_id AS customer_id,
      'phone' AS channel
    FROM
      t_1_Customers AS t_2_Customers
    WHERE
      (t_2_Customers.phone IS NOT null)

) AS UNUSED_TABLE_NAME  )
SELECT
  ContactableCustomer_MultBodyAggAux.customer_id AS customer_id,
  ContactableCustomer_MultBodyAggAux.channel AS channel
FROM
  t_0_ContactableCustomer_MultBodyAggAux AS ContactableCustomer_MultBodyAggAux
GROUP BY ContactableCustomer_MultBodyAggAux.customer_id, ContactableCustomer_MultBodyAggAux.channel ORDER BY customer_id;

-- Executed on DuckDB:
| customer_id | channel |
|-------------|---------|
| 1           | email   |
| 2           | phone   |
| 3           | email   |
| 3           | phone   |
(4 rows)

$ synalog.compile('syntax.l', 'Inactive')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Customers AS (SELECT * FROM (

    SELECT
      1 AS customer_id,
      'Ada Lovelace' AS full_name,
      'ada@example.com' AS email,
      null AS phone
   UNION ALL

    SELECT
      2 AS customer_id,
      null AS full_name,
      null AS email,
      '555-0102' AS phone
   UNION ALL

    SELECT
      3 AS customer_id,
      'Grace Hopper' AS full_name,
      'grace@example.com' AS email,
      '555-0103' AS phone

) AS UNUSED_TABLE_NAME  ),
t_1_Logins AS (SELECT * FROM (

    SELECT
      1 AS customer_id
   UNION ALL

    SELECT
      3 AS customer_id

) AS UNUSED_TABLE_NAME  )
SELECT
  Customers.customer_id AS customer_id
FROM
  t_0_Customers AS Customers
WHERE
  ((SELECT
    MIN((CASE WHEN x_4.unnested_pod = 0 THEN 1 ELSE NULL END)) AS logica_value
  FROM
    t_1_Logins AS Logins, (select unnest([0]) as unnested_pod) as x_4
  WHERE
    (Logins.customer_id = Customers.customer_id)) IS NULL) ORDER BY customer_id;

-- Executed on DuckDB:
| customer_id |
|-------------|
| 2           |
(1 row)

$ synalog.compile('syntax.l', 'OrderSize')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Orders AS (SELECT * FROM (

    SELECT
      10 AS order_id,
      1 AS customer_id,
      1500 AS amount
   UNION ALL

    SELECT
      11 AS order_id,
      2 AS customer_id,
      240 AS amount
   UNION ALL

    SELECT
      12 AS order_id,
      3 AS customer_id,
      40 AS amount

) AS UNUSED_TABLE_NAME  )
SELECT
  Orders.order_id AS order_id,
  CASE WHEN (Orders.amount > 1000) THEN 'large' WHEN (Orders.amount > 100) THEN 'medium' ELSE 'small' END AS size
FROM
  t_0_Orders AS Orders ORDER BY order_id;

-- Executed on DuckDB:
| order_id | size   |
|----------|--------|
| 10       | large  |
| 11       | medium |
| 12       | small  |
(3 rows)

$ synalog.compile('syntax.l', 'UserDisplay')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Customers AS (SELECT * FROM (

    SELECT
      1 AS customer_id,
      'Ada Lovelace' AS full_name,
      'ada@example.com' AS email,
      null AS phone
   UNION ALL

    SELECT
      2 AS customer_id,
      null AS full_name,
      null AS email,
      '555-0102' AS phone
   UNION ALL

    SELECT
      3 AS customer_id,
      'Grace Hopper' AS full_name,
      'grace@example.com' AS email,
      '555-0103' AS phone

) AS UNUSED_TABLE_NAME  )
SELECT
  Customers.customer_id AS customer_id,
  COALESCE(Customers.full_name, 'Anonymous') AS name
FROM
  t_0_Customers AS Customers ORDER BY customer_id;

-- Executed on DuckDB:
| customer_id | name         |
|-------------|--------------|
| 1           | Ada Lovelace |
| 2           | Anonymous    |
| 3           | Grace Hopper |
(3 rows)