“与众不同"关系数据库中的对象 [英] "Diffing" objects from a relational database

问题描述

我们的win32应用程序根据MySQL关系数据库中许多表中的数据组装对象.在这样的对象中，多个修订版本存储在数据库中.

Our win32 application assembles objects from the data in a number of tables in a MySQL relational database. Of such an object, multiple revisions are stored in the database.

当存储某个版本的多个修订时，迟早会问自己一个问题，即是否可以直观地看到两个修订之间的差异:)所以我的问题是:什么是差异"两个的好方法这样的数据库对象?

When storing multiple revisions of something, sooner or later you'll ask yourself the question if you can visualize the differences between two revisions :) So my question is: what would be a good way to "diff" two such database objects?

• 您将在数据库级别进行比较吗? (听起来不是个好主意:太低级了，对模式太敏感了.)
• 您会比较这些对象吗?
  • 您会编写一个手动"比较两个对象的属性和字段的函数吗?
  • 您将如何存储差异?在单独的通用"TDiff"对象中?
  • 关于如何在用户界面中可视化此类内容的任何常规建议?
  • Would you do the comparison at the database level? (Doesn't sound like a good idea: too low-level, and too sensitive to the schema).
  • Would you compare the objects?
    • Would you write a function that "manually" compares the properties and fields of two objects?
    • How would you store the diff? In a separate, generic "TDiff" object?
    • Any general recommendations on how to visualize such things in a user interface?

    非常欢迎您提出建议或有关您自己的经历的故事；谢谢一堆！

    Advice, or stories about your own experiences with this, are very welcome; thanks a bunch!

    回复安东尼的评论:此特定应用程序用于安排由教师团队运营的培训课程.教师的日程安排存储在数据库的各个表中，并包含诸如她必须在哪一天去哪一天"，团队中的同事是谁"等信息，这些信息分布在多个地方.桌子.

    In reply to Antony's comment: this specific application is used to schedule training courses, run by teams of teachers. The schedule of a teacher is stored in various tables in the database, and contains info such as "where does she have to go on which day", "who are her colleagues in the team", etc. This information is spread out over multiple tables.

    我们有时会发布"时间表，以便教师可以在网页上看到它.每个出版物"都是一个修订，我们希望能够向用户(以及后来的老师)展示两个出版物之间的变化---如果有的话.

    Once in a while, we "publish" the schedule, so the teachers can see it on a webpage. Each "publication" is a revision, and we'd like to be able to show the users (and later also the teachers) what's changed between two publications --- if anything.

    希望这使场景更加切实:)

    Hope that makes the scenario a bit more tangible :)

    恩，赏金已经结束，所以我接受了答案.如果能够以某种方式从我的代表中减去几个额外的100，并将其提供给其他一些答案，我会毫不犹豫地这样做.大家的帮助很大，我非常感谢！ 〜小野20090519

    Well, the bounty has come to an end, so I've accepted an answer. If it'd somehow be possible to slice a couple of extra 100's off of my rep and give it to some of the other answers, I would do so without hesitation. All your guys' help has been great, and I am very grateful! ~ Onno 20090519

    推荐答案

    假定一个类具有5个已知的属性-日期，时间，主题，轮廓，位置.查看时间表时，我对这些属性的最新(即当前/准确)版本最感兴趣.知道有什么变化(如果有的话)对我也很有用. (作为一个补充说明，如果日期，时间或位置发生了变化，我还希望收到一封电子邮件/短信，以防万一我不检查更新的时间表:-))

    Assume that a class has 5 known properties - date, time, subject, outline, location. When I look at my schedule, I'm most interested in the most recent (ie current/accurate) version of these properties. It would also be useful for me to know what, if anything, has changed. (As a side note, if the date, time or location changed, I'd also expect to get an email/sms advising me in case I don't check for an updated schedule :-))

    我建议在修改时间表时执行"diff".因此，当创建该类的版本2时，记录哪些值已更改，并将其存储在版本2对象的两个"changelog"字段中(必须已经有一个父表位于所有表的顶部，请使用该表！ ).一个更改日志字段是人类可读的文本"，例如日期从5月1日星期一更改为5月2日星期二，时间从10:00 am更改为10:30 am".第二个changelog字段是一个已删除字段的变体列表，例如'date，time'.为此，在保存之前，您将遍历用户提交的值，与当前数据库值进行比较，并连接2个字符串，一个字符串易于理解，一个字符串字段名称列表.然后，更新数据，并将串联的字符串设置为更改日志"值.

    I would suggest that the 'diff' is performed at the time the schedule is amended. So, when version 2 of the class is created, record which values have changed, and store this in two 'changelog' fields on the version 2 object (there must already be one parent table that sits atop all your tables - use that one!). One changelog field is 'human readable text' eg 'Date changed from Mon 1 May to Tues 2 May, Time changed from 10:00am to 10:30am'. The second changelog field is a delimted list of changed fields eg 'date,time' To do this, before saving you would loop over the values submitted by the user, compare to current database values, and concatenate 2 strings, one human readable, one a list of field names. Then, update the data and set your concatenated strings as the 'changelog' values.

    显示时间表时，默认情况下加载当前版本.循环浏览changelog字段列表中的字段，并注释显示内容以表明该值已更改(*或突出显示等).然后，在单独的面板中显示人类可读的更改日志.

    When displaying the schedule load the current version by default. Loop through the fields in the changelog field list, and annotate the display to show that the value has changed (a * or a highlight, etc). Then, in a separate panel display the human readable change log.

    如果计划被多次修改，则您可能希望合并版本1和版本2之间的变更日志. 2和2& 3.说在版本3中，仅课程大纲已更改-如果这是您在显示时间表时唯一的更改日志，则不会显示日期和时间的更改.

    If a schedule is amended more than once, you would probably want to combine the changelogs between version 1 & 2, and 2 & 3. Say in version 3 only the course outline changed - if that was the only changelog you had when displaying the schedule, the change to date and time wouldn't be displayed.

    请注意，这种非规范化方法不适用于分析-例如，确定哪个特定位置始终发生类更改-但您可以使用E-A-V模型扩展它来存储更​​改日志.

    Note that this denormalised approach won't be great for analysis - eg working out which specific location always has classes changed out of it - but you could extend it using an E-A-V model to store the change log.

    这篇关于“与众不同"关系数据库中的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！