福勒时间表达的关系模式 [英] Relational Schema for Fowler's Temporal Expressions

问题描述

Martin Fowler在此处中定义了一种优雅的对象模型，用于计划重复任务很好地映射到OO代码.但是，将其映射到关系数据库架构以实现持久性是很棘手的.

Martin Fowler defines an elegant object model for the scheduling of recurring tasks here, which maps to OO code very nicely. Mapping this to a relational database schema for persistence, however, is tricky.

任何人都可以提出一种方案+ SQL组合，其中包含他描述的所有功能，尤其是在第11页的图像中.相交和并集非常明显-复杂之处在于表示时态表达式"，该表达式需要可变的参数和必须进行不同的解释，然后将它们组合成一个时间集合".

Can anyone suggest a schema + SQL combination that encapsulates all the functionality he describes, particularly in the image on page 11. Intersects and Unions are fairly obvious - the complexity lies in representing the 'Temporal Expressions', which take variable parameters and must be interpreted differently, and then combining those into a 'Temporal Set'.

要清楚，有很多方法可以表示关系数据库中重复发生的事件的概念.我希望每个人都输入有关如何映射此特定模型的信息.

To be clear, there are many ways to represent the concept of recurring events in relational databases. What I'd like everyone's input on is how to map this particular model.

一些可能的选项:

• 'Meta'表，用于定义参数的数量和用途.丑陋，但可能行得通.但是，仅可能会有数量有限的时态表达"形式，因此这种形式提供的极端灵活性可能太大了.
• Postgres(可能还有其他)RBMS支持的某种形式的表继承.

序列化参数列表并将结果存储在varchar()中不是一种解决方案，因为该方法会阻止基于集合的查询:)

Serialising the parameter list and storing the result in a varchar() is not a solution as that method prevents set-based queries :)

推荐答案

恐怕这个答案将是大量参考文献和很少的实际代码，自从我上次对此感到困惑以来已经有一段时间了，但是. ..

I'm afraid this answer will be a lot of references and very little practical code, and it has been a while since I last messed with this, but...

我认为您要在此处混合使用的两种技术是活动数据库" 和时间数据库" .

I think the two technologies you want to mix here are 'active databases' and 'temporal databases'.

第一个对评估规则等有用，第二个对存储时间数据并在特定记录有效时进行评估.这两个都是相当大的研究领域，但是您可以使用普通SQL来完成大多数时态工作(前提是您的数据库具有良好的时间支持).活动部分在SQL中比较难，但是 PostgreSQL 至少有一些规则可以对此有所帮助.我不了解其他数据库，但是大多数数据库都具有规则/触发/约束支持，可以将其转换为您要查找的内容.

The first would be useful for evaluating the rules and so on, and the second is useful to store temporal data and evaluate at when a certain record is valid. Both of these are pretty large research areas, but you can do most of the temporal stuff in plain SQL (provided your database has good time support). The active part is harder in SQL, but PostgreSQL at least has rules to help slightly with this. I don't know about the others databases, but most of them has rule/trigger/constraint support that would be able to translate to what you are looking for.

活动数据库是可以对使用规则存储的事实变化做出反应的数据库.这些规则是用实现特定的语言指定的，但每天进行讨论事件-条件-动作规则( ECA规则)很常见.有关活动数据库系统的介绍，请阅读文章活动数据库系统.有关ECA规则的更多信息，请查看活动的面向对象的数据库系统中的事件.

Active databases are databases that can react to changes in the facts that it stores using rules. These rules are specified in implementation specific languages, but for every day discussion Event-Condition-Action rules (ECA Rules) are common. For an introduction to active database systems read the articles The Active Database Management System Manifesto and Active Database Systems. For some more information on ECA rules, check out Logical Events and ECA Rules (the pages are in reverse order o_0) and Events in an Active Object-Oriented Database System.

事件处理是规则处理的特例，该规则处理如何处理复合事件并适当地触发其动作.关于此的有趣读物是活动数据库的复合事件:语义，上下文和检测和复合事件检测器的解剖学.另请参见复杂事件处理网站和复杂事件处理维基百科文章

Events processing is a special case of the rule handling dealing with how to handle composite events and trigger their actions appropriately. An interesting read regarding this is Composite Events for Active Databases: Semantics, Contexts and Detection and Anatomy of a Composite Event Detector. Also see the Complex Event Processing site and the Event Stream Processing and Complex Event Processing wikipedia articles.

时间数据库可以看作是一种可以理解时间，尤其是两种特定时间的数据库.有效时间和交易时间.记录的有效时间是该记录有效的时间段，而记录的事务处理时间是该记录在数据库中存在的时间.作为一个很好的实用介绍，我建议 这本书介绍如何使用SQL进行时态数据库:

Temporal databases can be seen as a database that can understand time, and in particular two specific kinds of time; valid-time and transaction-time. The valid-time of a record is the time period during which that record is valid, and the transaction-time of a record is the time during which it is present in the database. As a good practical introduction I'd recommand the book on how to do temporal databases in SQL: Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass.

相反，可以在中阅读有关时态数据库的所有信息. 《 Springer数据库系统百科全书》的时间数据库条目，它是一个非常全面的文档(我将从"Temporal Database"条目开始)，但是要起步更快，请查看

Oterhwise, everything you might possibly want to know about temporal databases can be read in Temporal Database Entries for the Springer Encyclopedia of Database Systems which is a pretty comprehensive document (I would start at the 'Temporal Database' entry), but to get started a bit quicker, check out the Temporal Database Glossary which is rather easier to browse and read, and the site Time Center whose publications part has (or did have...) links to most notable publications in the area.

因此，既然您已经知道了这一切，那么您很快就会看到，第11页上的图像可以表示为一个复合事件，并且可以像这样实现检测/评估，前提是您已经实现了复合事件检测器的正确必需子集. ，其余的可以表示为具有时间方面的表中的条目:)

So, now that you know all about this you see quickly that the image on page 11 can be expressed as a composite event, and can be detected/evaluated as such provided you have implemented the proper required subset of a composite event detector, and the rest could be expressed as a entries in tables with temporal aspects :)

马丁·福勒(Martin Fowler)在他的随时间变化的模式中总结了很多自己的问题许多与时间有关的模式.

Martin Fowler addresses much of this himself in his Patterns for things that change with time that summarizes many patterns that deals with time.

最后，我可能会为时间信息创建一个数据库架构，并为活动部分使用DB规则，或者在应用程序中实现该部分(尽管有龙).如果使用PostgreSQL，则规则机制将在规则系统部分中进行描述文档.

In the end, I would probably create a database schema for the temporal information and either use the DB rules for the active parts or implement that part in the application (there be dragons though). If you use PostgreSQL, the rule mechanisms are described in The Rule System part of the docs.

很多东西要阅读，但是如果您完全理解所有这些，您的专业净资产可能会增加很多:)

Much to read, but if you thoroughly understand all this your professional net worth can go up quite a bit :)

此外，对Google来说，好的术语是时间数据库"，活动数据库"，"ECA规则".

Also, good terms to google are 'temporal database', 'active database', 'ECA Rule'.

这篇关于福勒时间表达的关系模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！