Mehr Research

Reactive and dataflow programs using SQLite — Part 3: Reacting to data changes

—

by

Hessam Mehr
in

In part 2, we explored parallels between SQL and Prolog and saw how to use a view like a logic query, that is, to ask questions that always use the most up-to-date inputs.

In this part, we’ll introduce a new facility called triggers, which are SQLite’s way of running an action in response to incoming data. Although triggers are a powerful mechanism for interactivity, views in SQLite (and most mainstream SQL databases) are only calculated on demand and therefore cannot set off triggers autonomously, which severely limits their usefulness.

Aside: You will notice extra semicolons in Python code snippets. This is a workaround while we figure out how to make integrate the CodeMirror editor with the interactivity features of Codapi.

In the previous post, we saw how views act as an up-to-date provider of data that depends on other sources, ideal for expressing logic and computation on top of raw data within one or more tables. Imagine controlling a robot using a system continuously feeding information from input tables (e.g. sensor readings and user commands) via logic and policies to inform the robot’s next commands as well as powering a user interface for monitoring and control.

Example data flow-based architecture for robotic control/monitoring.

Ideally, we would like the logic (views) in our system to set off, or trigger, actions in the outside world, which is precisely what SQLite triggers do. To really use the power of triggers, we need to run the query within an environment where it has access to functions from a host language, like Python. Let’s set up a table with some initial data in Python:

import sqlite3;

conn = sqlite3.connect(':memory:');

conn.execute('''
CREATE TABLE fruits (fruit_type STRING, count INT);
''');

We can think of this fruits table as a list of fruit orders. By adding a SQL trigger, we can execute an arbitrary query every time there is an insertions/updates/deletions on this table. And because we are using SQLite through a host language (Python), we can be sneaky and call a user-defined function as part of this query.

python_callback = lambda fruit, count: print(f'Python: {count} new {fruit}');

conn.create_function('py_callback', 2, python_callback);

conn.execute(
'''
CREATE TRIGGER alert AFTER INSERT ON fruits BEGIN
 SELECT py_callback(new.fruit_type, new.count);
 END
'''
);

Now when we add new orders SQLite calls our py_callback function, neat!

conn.execute(
'''
INSERT INTO fruits VALUES
 ('banana', 4), ('apple', 3), ('pear', 2),
 ('banana', 1), ('apple', 2);
'''
)

This type of reactivity may seem quite basic, but you can do some fun things combining it with Python’s asyncio. Next time, we’ll look at the missing parts to create a true declarative dataflow.

Comments

Leave a Reply