I worked on a really fun project where I ended up writing a compiler that, for lack of a better term, added materialized views to SQLite by taking an input language and spitting out a collection of tables and triggers. Inserts into primary tables triggered inserts into interior tables, which had triggers that inserted rows into additional interior tables, and so on, until the final materialized view was updated. The interior tables stored/cached partial join results to avoid recalculating them each time.
It was used for building efficient and dependable embedded event correlation engines. It used a variation of the Rete algorithm implemented as a SQLite trigger program generated from the input language. It had neat things like justification (“why was this fact asserted? Because X, Y, Z. Why was Z asserted? Because A, B, C…”) and automatic consistency (removing any support for an event/fact would cause it to lose support and be withdrawn), and some fun stuff with recursive rules and action-selection mechanisms (“this rule says to withdraw fact A if I see B, but this rule says to assert A if I see C…what do I do if I see B and C?”)
It was part of a DARPA project
This is so cool. Where was this in 2006, when I was struggling with the explosion in complexity of the iTunes video pipeline?