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 piece called triggers, which is the SQL way of running an action in response to incoming data.

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.

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);
''');

You can think of this 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