可修改的连接视图是合理的设计选择吗？
需要明确的是，通过可修改连接视图我的意思是通过连接两个或多个表构建的视图，该视图允许修改任何/所有表的插入/更新/删除操作组件表。
这可能是 postgres 特定的问题，不确定。我也很感兴趣其他 DBMS 是否具有可修改连接视图的特殊功能，因为据我所知，它们在标准 SQL 中是不可能的。
我正在研究 postgres 模式，我最近的一些阅读表明可以使用替代规则（CREATE RULE ... DO INSTEAD ...）构造可修改的联接视图。可修改的连接视图似乎是可取的，因为它允许在接口后面隐藏强规范化，从而提供经典抽象的机制。规则是实现的唯一选项，因为目前 无法在视图上设置触发器。
然而，我尝试设计的第一个可修改视图遇到了问题，我发现许多人认为不平凡的规则是有害的（请参阅这个答案）。另外，我在网络上找不到任何可修改连接视图的示例。
问题（编辑以对问题提出更详细的观点）：
- 您是否有可修改连接视图的经验，您能否提供具有选择/插入/删除/更新功能的具体示例？
- 它们实用吗？即可以透明地处理它们，而不必小心翼翼地绕过地雷/黑洞吗？
- 就功能/工作量比和可维护性而言，它们是否是一个好的设计选择？
非常感谢有关此主题的任何示例/讨论的链接。谢谢。
To be clear, by modifiable join view I mean a view constructed from the joining of two or more tables that allows insert/update/delete actions that modify any/all of the component tables.
This may be a postgres specific question, not sure. I am also interested if other DBMSs have idiosyncratic features for modifiable join views, since as far as I can tell, they are not possible in standard SQL.
I'm working on a postgres schema, and some of my recent reading has suggested that it is possible to construct modifiable join views using instead rules (CREATE RULE ... DO INSTEAD ...). Modifiable join views seem desirable since it would allow for hiding strong normalization behind an interface, providing a mechanism for classic abstraction. Rules are the only option for implementation, since currently triggers cannot be set on views.
However, the first modifiable view I tried to design ran into problems, and I find out that many consider non-trivial rules to be harmful (see links in comments to this SO answer). Also, I can't find any examples of modifiable join views on the web.
Questions (Edit to put finer points on the questions):
- Do you have any experience with modifiable join views and can you provide a concrete example with select/insert/delete/update ability?
- Are they practical, i.e. can they be treated transparently without having to tiptoe around mines/black holes?
- Are they ever a good design choice, in terms of functionality/effort ratio and maintainability?
Would greatly appreciate links to any examples/discussions on this topic. Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
是的，我对一般可更新视图有一些经验。我认为它们在 PostgreSQL 中很实用。像所有的设计选择一样，它们可能是一个好的选择，也可能是一个糟糕的选择。
我发现它们在处理超类型/子类型表时特别有用。我为每种子类型创建一个视图；视图将子类型连接到超类型。撤销对基表的权限，为视图编写规则，并仅授予客户端代码对视图的访问权限。客户端代码完成的所有数据操作都会通过视图和在视图上定义的规则。
我认为规则与任何其他环境中的任何其他功能都没有真正的不同。我所说的环境，是指 C、C++、Java、Ruby、Python、Erlang 和 BASIC，而不仅仅是 dbms 环境。
利用语言的优点。避免不好的建议。
“不要使用 malloc()”是一个不好的建议。 “始终检查 malloc() 的返回值”是个好建议。 “永远不要使用规则”是个坏建议。 “避免以已知会产生可疑行为的方式使用规则”是个好建议。超类型/子类型表视图所需的规则简单且易于理解。他们不会行为不端。
在理论层面上，视图提供逻辑数据独立性。但这只有在视图可更新的情况下才有可能。 （许多视图应该可以由数据库引擎直接更新，而不需要任何规则或触发器。）
Yes, I have some experience with updatable views in general. I think they're practical in PostgreSQL. Like all design choices, they can be a good choice, and they can be a bad choice.
I find them particularly useful in dealing with supertype/subtype tables. I create one view for each subtype; the view joins the subtype to the supertype. Revoke permissions on the base tables, write rules for the view, and give client code access only to the views. All data manipulation done by client code then goes through the view and the rules defined on them.
I don't think rules are really different from any other feature in any other environment. And by environment, I mean C, C++, Java, Ruby, Python, Erlang, and BASIC, not just dbms environments.
Use the good features of a language. Avoid the bad ones.
"Don't use malloc()" is bad advice. "Always check the return value of malloc()" is good advice. "Never use rules" is bad advice. "Avoid using rules in ways that are known to have questionable behavior" is good advice. The rules you need for views on supertype/subtype tables are simple and easy to understand. They don't misbehave.
At the theoretical level, views provide logical data independence. But that's only possible if the views are updatable. (And many views should be updatable directly by the database engine, without any need of rules or triggers.)
我用它们来替代 ORM。我认为只要你不把它们在数据库中到处散布，它们就很容易理解。我为应用程序定义一个架构，然后该架构中的任何视图都是该应用程序的方法和操作。之后客户端代码大部分可以自动化，因为视图提供了我需要编写通用客户端代码的抽象。
人们指出，规则重写不是一个真正的表（但它冒充一个表），这使得编写会破坏的东西成为可能。这是可能的，但我还没有遇到过。这个想法是隐藏重写中的复杂性，然后只进行简单的删除和更新，无连接。如果事实证明需要联接 - 则需要重写规则，而不是顶级查询。
最后，我发现这是一种非常紧凑的数据库编写方式。所有与之交互的方式都被编写为规则。任何连接都不应访问真实的表。你的业务逻辑非常明确。如果视图没有 UPDATE 规则 - 它不能更新。由于您已经在数据库级别而不是客户端级别编写了所有这些内容，因此它不依赖于 Web 框架或特定语言。这为您连接数据库的方式提供了很大的灵活性。想象一下，您使用了 Web 框架，但随着时间的推移，您需要直接访问另一个来源的数据库。直接访问还将绕过您辛苦制定的所有 ORM 业务规则。通过规则编写接口，您可以公开该接口，而不必担心新连接会损坏数据。
如果人们说你真的可以用他们搞砸数据库 - 那么当然 - 当然可以。但你也可以处理其他一切。如果人们说你根本不能使用它们而不把事情搞砸，那么我不同意。
I use them as a replacement for ORMs. I think as long as you do not run-a-muck sprinkling them everywhere through the database they can be easy enough to understand. I define a schema for an application and then whatever views are in that schema are the methods and operations of that app. The client code can be mostly automated after that since the views give the abstraction I need to write generic client code.
People point out that the rule rewrite is not a real table (but it is posing as one) which makes it possible to write things that will break. This is possible but I have yet to come across it yet. The idea is to hide the complexity in the rewrite and then only do simple deletes and update with no joins. If it turns out that a join is needed - it is time to rewrite the rule, not the top level query.
At the end, I find it a very compact way to write the database. All the ways of interfacing with it are written as rules. No connection should have access to a real table. Your business logic is very explicit. If a view does not have an UPDATE rule for it - it can not be updated period. Since you have written all this in the database level instead of the client level, it is not tied to a web framework or a particular language. This leads to a lot of flexibility in how you want to connect to the database. Imagine you used web framework, but as time goes on you need direct access to the database for another source. Direct access will also bypass all of ORM business rules you worked so hard on. With a rule writing interface you can expose, the interface without fear that the new connection will corrupt the data.
If people say you can really F UP a database with them - then sure - of course you can. But you can with everything else too. If people say you can not use them at all with out mucking things up, then I would disagree.
两个快速链接：
Two quick links:
我个人的偏好是仅使用视图来读取数据，（实际上）从不用于插入或更新。通过本质上重新规范化数据库中的数据（这听起来像您正在做的事情），您可能会创建一个很难长期测试和维护的系统。
如果可能的话，考虑将非规范化数据映射回应用程序代码中某处的正常模式，并在单个事务中以这种方式将其提供给数据库（恕我直言，提供给各个表）。
My personal preference is to use views only for reading data, (virtually) never for inserting or updating. By essentially re-normalizing data (which sounds like what you are doing) in your database, you are likely creating a system that will be very difficult to test and maintain in the long term.
If at all possible, look at mapping your denormalized data back to a normal schema somewhere in your application code, and providing it to the database that way (to individual tables IMHO) in a single transaction.
我知道在 SQL Server 中，如果您更新视图，则无论如何都必须将更改限制为仅一张表，这使得使用视图进行更新在我看来毫无用处，因为您必须知道哪些字段与哪些表相关。
如果您想要提取信息并且不必担心插入和更新的数据库结构，那么 ORM 可能比视图更适合您。
I know in SQL Server if you update a view you must limit the change to only one table anyway which makes using views for updating useless in my mind as you have to know which fields go with which tables anyway.
If you want to abstract the information out and not have to worry about the database structure for inserts adn updates, an ORM mught do a better job for you than views.
我从未使用过任何类型的可修改视图，但当您询问它们是否是“合理的设计选择”时，我可以建议一种替代设计选择，它具有许多不需要可修改视图的优点：a 事务 API
基本上这相当于：
insert、update、delete语句I have never used modifiable views of any sort but as you are asking whether they are a "reasonable design choice", can I suggest an alternative design choice with many benefits where modifiable views are not needed: a Transactional API
Basically what this amounts to is:
insert,update,deletestatements at all我通常以“最后有效记录”的形式进行视图，只是隐藏和跟踪修改（如维基）
我认为这样做的唯一缺点是：然后您将视图用作表格，将其与任何内容连接，然后在“wheres”上使用它，然后在其上插入记录，依此类推，但在您身后与针对真实表（更大、更复杂）的相同操作相比，性能损失很大。我认为这取决于有多少人必须了解模式。确实，一些 DBMS 也承认对视图进行索引，但我认为无论如何你都会损失大量的性能。对不起我的英语。
I do usually do views in the form of "last-valid-record" just hidding and tracking modifications (like a wiki)
The only drawback that I see to this is: then you use your view as a table, and you join it with anything, and and you use it on "wheres", and you insert records on it, and so on, but behinds you have made lot of performance lost compared to the same acctions against a real table (more bigger and more complex). I think it depends on how many people must understud de schema. Its true that some DBMS also admit to index the views, but I think you lose an important amount of performance anyway. Sorry about my english.