Automatically infer primary keys in SQL views
Closed this issue · 4 comments
Generally, not all SQL views have primary keys. However, in many workloads, SQL views map 1-1 to underlying tables and have a column with all the properties of a primary key -- in effect, a primary key for the view. We should recognize these cases and add in the primary key information, letting us use functionality such as @fold and @recurse on such views.
I don't see a way of doing this without parsing the SQL view definitions, which consist of SQL text, to identify the cases where the mapping is one-to-one. This does not seem like it is worth the effort.
Interesting. SQLAlchemy does not offer any tooling around this?
I submitted an issue sqlalchemy/sqlalchemy#4981 asking just in case but I am pretty sure it doesn't.
The most relevant tooling that I found was https://github.com/pglass/sqlitis
It converts sql text to sqlalchemy expressions. I think it might be doable to infer primary keys from a sqlalchemy expression if the expression is simple enough.
However, this tooling is only tested for sqllite. It also cannot parse "create view" expressions. So if we used it, we would use it more as inspiration than anything else.
Closing this issue since the consensus seemed to be that it would be too difficult to do this.