Acting on a Thing

A Thing can run SQL on another database when a condition on its values becomes true.

CREATE THING order_ready KEY orderno READS lines
  AS SELECT orderno, COUNT(*) AS line_count, SUM(qty) AS qty, SUM(picked) AS picked
     FROM lines GROUP BY orderno
  WHEN picked >= qty
  DO UPDATE salesorders SET orderstatusid = 21, lines = THING.line_count WHERE orderno = KEY
  TO orders SHADOW;

• WHEN cond uses the Thing's own columns.
• DO takes one or more statements, separated by ;. KEY becomes the key and THING.<column> a value from the Thing; a THING.<column> the Thing does not have is refused when you create it.
• TO orders names a profile in <root>/sql_db/connections.ini. The statements run in one transaction on that server. The profile must be another server: an action runs inside the write that set it off, so one pointed back at the same ASQL server waits on itself and the write times out.
• It fires once each time the condition goes from false to true, and only on committed data. When the condition goes false again, it can fire again later.
• Actions need a Thing keyed and partitioned on the same column (the default).

Shadow mode

With SHADOW, nothing is sent. Each time it would act, the filled-in statements are recorded instead:

SHOW THING SHADOW order_ready;   -- what it would have run, and when
SET THING order_ready LIVE;      -- start acting
SET THING order_ready SHADOW;    -- stop again

The connection profile

[profile]
id=orders
host=10.0.0.5
port=3306
user=asql_writer
password_file=/etc/aios/orders.pass
database=orders
things_write=1

The file is read at start; restart after editing it. SSL and SSH settings are refused rather than ignored: run a tunnel and point host and port at it.

Checking

SHOW THING ALERTS order_ready;    -- actions that failed
SHOW THING HISTORY order_ready '1001';   -- includes each action taken or recorded in shadow mode