> implement a realtime analytics dashboard for an application with over a billion event rows.
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.
There would also need to be some python[2] to provide the PL/SQL logic shown.
[1] https://sqlite.org/windowfunctions.html
[2] https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...
SQLite3 very much does support user-defined aggregate functions, just not with a CREATE AGGREGATE statement. You have to use one of the `sqlite3_create_function*()` C functions[0][1][2] to create a user-defined aggregate function:
The sixth, seventh and eighth parameters
passed to the three "sqlite3_create_function*"
functions, xFunc, xStep and xFinal, are pointers
to C-language functions that implement the SQL
function or aggregate. A scalar SQL function
requires an implementation of the xFunc callback
only; NULL pointers must be passed as the xStep
and xFinal parameters. An aggregate SQL function
requires an implementation of xStep and xFinal
and NULL pointer must be passed for xFunc. To
delete an existing SQL function or aggregate,
pass NULL pointers for all three function
callbacks.
The sixth, seventh, eighth and ninth parameters
(xStep, xFinal, xValue and xInverse) passed to
sqlite3_create_window_function are pointers
to C-language callbacks that implement the
new function. xStep and xFinal must both be
non-NULL. xValue and xInverse may either both be
NULL, in which case a regular aggregate function
is created, or must both be non-NULL, in which
case the new function may be used as either an
aggregate or aggregate window function. More
details regarding the implementation of aggregate
window functions are available here.
Ah, so you do know about the `sqlite3_create_function*()` functions. You don't have to use Python, though you can of course.
[0] https://sqlite.org/c3ref/create_function.html
[1] https://sqlite.org/appfunc.html#the_aggregate_function_callbacks
[2] https://sqlite.org/windowfunctions.html#udfwinfunc*Also, if you have a column to store the current/newest state of each order then I believe you can use plain SQL for the trigger function instead of PlPgSQL since you can then rely on a CHECK() to raise an exception when the new state would be 'error'.
(I bet TFA is looking for comments like these since as you can see at the bottom of TFA they are hiring.)
I mean I never met a system that had a graph of states that wouldn't require working across this graph in certain situations. Hardwiring it to database would even worsen handling such edge cases.
michelpp•9mo ago
https://github.com/michelp/pgfsm
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.