While this is a good approach to implements state machines, the transition function is hardwired to one transition graph. Another approach is to generalize the state and transition graph into a table, and group those transitions by a machine id, as shown here:<p><a href="https://github.com/michelp/pgfsm">https://github.com/michelp/pgfsm</a><p>Now many machines (sub-graphs of state transitions) can be defined in general, and the transition checking function checks the validity of the next state based on the table, instead of static rules in a function.
TFA says he's using this to<p>> implement a realtime analytics dashboard for an application with over a billion event rows.<p>I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE.<p>There would also need to be some python[2] to provide the PL/SQL logic shown.<p>[1] <a href="https://sqlite.org/windowfunctions.html" rel="nofollow">https://sqlite.org/windowfunctions.html</a><p>[2] <a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function" rel="nofollow">https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...</a>