Update another database when a condition is met

Run a statement on another database the moment a condition becomes true. A Thing does this: it keeps a running total per key and runs an UPDATE on MariaDB once, when the condition becomes true. The example below marks an order as ready when every one of its lines is picked.

1. Name the connection

Add the database as a profile in /opt/aios/root/sql_db/connections.ini:

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

things_write=1 lets Things and Cells write through this profile. The file is read when the server starts, so restart it after editing: sudo systemctl restart aios-compute. Connections are plain TCP or a Unix socket (socket_path=); for an encrypted link, run a tunnel and point host and port at it.

2. Create the Thing in shadow mode

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 WHERE orderno = KEY
  TO orders SHADOW;

KEY is replaced with the order number, and THING.<column> with a value from the Thing. With SHADOW, nothing is sent to the database: each time an order becomes ready, the exact statement it would have run is recorded.

3. Check what it would do

SHOW THING SHADOW order_ready;            -- the statements it would have run
WHAT IF UPDATE lines SET picked = qty WHERE orderno = 1001;   -- preview a change; nothing is written

4. Turn it on

SET THING order_ready LIVE;

From now on, each order runs the UPDATE once when it becomes fully picked. If a line is un-picked, the condition goes false and the Thing can fire again the next time it becomes true. SET THING order_ready SHADOW turns it back off.

More: Acting on a Thing.