In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data
.
Now, my users also need to be able to create filter sets of alerts, which define some sort of problem with the data that they need to detect. For example this might be "select all points where data.value
is less than quality 50", or "select a group of consecutive points that all have the same sign", which can be answered with SQL queries. The results of these queries need to always stay up-to-date as new data
rows are added to the database.
As far as I can tell, I could model this two ways:
- I treat each filter as a row in a
filter_definition
table that somehow stores the query (as a string), and then also have afilter_result
table that is periodically updated by running thefilter_definition
query (e.g. whenever a newdata
orfilter_definition
is created). By having my filters as rows in the database it's very easy for me to create/update/delete filters using my ORM. However this is highly inefficient to update, and alsofilter_result
can become out of date if not updated recently enough. - I treat each filter as a view in the database, each with their own associated
SELECT
statement. This would be much more efficient, since nothing needs periodic updates, because the view will automatically run the query when accessed. However, allowing my users to create/update/delete a view is much trickier because it's actually deleting an entire database entity each time. In addition, querying all filters (to find all dangerous data points) becomes nearly impossible. I can union all the views for one user, but how do I know which view belongs to each user? Each view is a database entity, not a row, so it can't have a foreign key.
There might be some other solutions I haven't considered as well.
Does anyone have any advice as to how I should solve this issue?
Note: I'm using sqlalchemy
as a DBMS-agnostic ORM, so really I want a solution that will work on any DBMS. However Postgres, MySQL and SQLite are my 3 priorities to support.