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
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_definitiontable that somehow stores the query (as a string), and then also have a
filter_resulttable that is periodically updated by running the
filter_definitionquery (e.g. whenever a new
filter_definitionis 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 also
filter_resultcan become out of date if not updated recently enough.
- I treat each filter as a view in the database, each with their own associated
SELECTstatement. 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.