Postgresql – Modelling a user-defined filter set in SQL

postgresqlview

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:

  1. I treat each filter as a row in a filter_definition table that somehow stores the query (as a string), and then also have a filter_result table that is periodically updated by running the filter_definition query (e.g. whenever a new data or filter_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 also filter_result can become out of date if not updated recently enough.
  2. 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.

Best Answer

You shouldn't allow arbitrary queries for these filters. This opens the door to a world of pain, since arbitrary queries can take arbitrarily long and cause arbitrary mischief.

Rather, you should plan for certain types of filters that correspond to pre-defined queries, perhaps with some parameters that can be chosen by the user and are stored in a table.

You can implement these with functions or views.

Whenever a new type of filter is needed, you have to plan a new release.

Related Question