MongoDb对Date范围的唯一约束 [英] MongoDb unique constraints on a Date range

问题描述

我使用MongoDb和Mongoskin。在一个集合中，我正在保存事件。
在其他字段中，这些事件有一个开始和结束，在Mongodb中保存为日期。

Im using MongoDb with Mongoskin. In a collection I'm saving events. Among other fields, these events have a start and an end, saved as Dates in Mongodb.

events { 
  start: "Date1",
  end: "Date2",
  ...
}

在此集合中插入新文档时，我需要一个禁止插入文档的约束，该文档的开始日期与创建的事件重叠。简而言之，我不希望任何事件共享相同的时间跨度。

When inserting new documents in this collection I need a constrain that forbids insertion of document which start-end dates overlapping an event alreay created. In short, I dont want any events share the same time span.

问题：有没有办法通过某种独特的索引处理MongoDb的约束？我想不是，但如果我错了，请纠正我！

Question: Is there a way to handle this constraint trough MongoDb with some kind of unique index? I think not, but please correct me if I'm wrong!

如果没有：

问题在插入新事件之前，是否必须检查可能的重叠代码？我是否需要设置某种写锁定，以便其他用户在检查重叠和插入自己的事件之间不能挤入事件？如何在MongoDb中完成？

Question Do I have to check possible overlaps trough code before inserting new events? Do I need to set up some kind of write lock, so that another user can't squeeze in an event between the time I check for overlaps and inserting my own event? How is this done in MongoDb?

编辑

这是最好的方式到目前为止我已经提出了它，它实际上似乎工作得很好。

This is the best way i have come up with so far, it actually seems to work pretty good.

var input = getPostInput();

var query = {$and: [
  {start: {$lte: input.end}},
  {end: {$gte: input.start}}
]};
db.events.findAndModify(query, {}, {$setOnInsert: input}, {new: true, upsert: true}, callback)

它使用 findAndModify 作为findOrCreate运算符的类型。 $ setOnInsert 仅当 findAndModify 找不到文档时才添加POST输入属性， upsert：true 表示如果找不到，它应该创建一个文档。组合这两个选项似乎创建了一个findOrCreate运算符。

It uses the findAndModify as a type of "findOrCreate" operator. $setOnInsert add the POST input properties only if the findAndModify don't find a document, and upsert: true says it should create an document if none is found. These two options in combination seems to create a findOrCreate operator.

编辑

更新（PUT）事件时出现问题。我不能重复使用上面的代码，因为它依赖于 upsert 和 $ setOnInsert 。

Problems arise when updating (PUT) an event. I can't reuse the code above because it's relies on upsert and $setOnInsert.

编辑

@wdberkeley：

@wdberkeley:

我仍在努力解决这个主要问题：确保范围内的唯一性。我越是想到它，似乎时间片阵列可能是最无问题的解决方案。例如，假设选择5分钟作为最小时间段，平均预订时间为45分钟。这需要我保存9个数字（可能 date s）： timespan = [0,5,10,15,20,25,30， 35,40] ，而不是两个： start = 0，end = 45 。
这比平均预订的保存数据多四倍。
我不是故意要苛刻，但你不认为这是一个问题吗？或者当保存的数据 10倍更大或 100倍更大时，它是否首先成为问题？我确实意识到这也与实际预订的总额相关...

I'm still struggling with this main problem: ensure uniqueness on a range. The more I think about it, it seems that "the array of time slices" might be the most non problematic solution. For example, lets say that 5 minutes is chosen as the smallest time period, and the average booking is 45 minutes. This would require me to save 9 numbers (probably dates): timespan = [0,5,10,15,20,25,30,35,40], instead of two: start=0, end=45. This is more than four times more saved data for the average booking. I dont mean to be harsh, but don't you see this as a problem? Or does it become a problem first when the saved data is 10 times larger or 100 times larger? I do realise that this is also relative to the totalt amount of bookings actually made...

推荐答案

没有死在MongoDB中执行此操作的简单方法。我做了一个可能适合你的替代选择。如果您的日期采用不连续的步骤，例如，如果这是针对用户按日或按小时保留对象的预订应用程序，则可以使用唯一索引和多键索引的组合。例如，假设预订是白天。约翰Q保留10月11日至10月14日（含）。这就像是一年中的第281天到第284天 - 让我们假设它到底是哪一天。将保留字段保存为保留日期的数组

There isn't a dead simple way to do this in MongoDB. I cooked up one alternative option that could work for you. If your dates come in discrete steps, like if this is for a booking application where users reserve objects by the day or the hour, then you can use a combination of unique indexes and multikey indexes. For example, suppose reservations are by day. John Q reserves October 11 to October 14, inclusive. That is something like the 281st to 284th days of the year - let's assume that's exactly which days it is. Save the reservation field as an array of the days that are reserved

> db.reservations.insert({ "span" : [ 281, 282, 283, 284 ] })

在 span 字段上添加唯一索引。

Put a unique index on the span field.

> db.reservations.ensureIndex({ "span" : 1}, { "unique" : 1 })

现在，您无法在其范围内插入具有任何这些日期的文档：

Now you can't insert a document that has any of those days in its span:

> db.reservations.insert({ "span" : [ 279, 280, 281, 282 ] })
// unique key error

这可能对您有所帮助，需要进行一些额外的调整以考虑年份，或者它可能是复合唯一索引的一部分，以使时间跨度独特，例如酒店预订 room_id 。

This might work for you with some additional tweaking to take into account the year, or it might be part of a compound unique index to make the timespans unique by e.g. room_id for hotel booking.

另一种方法是协调客户端的支票。如果你有多个客户端根本没有相互通信，我想最好的方法是在数据库中共享一个锁： findAndModify a在 lock 集合中记录以检查并获取锁定。一旦客户端通过更改该文档上的字段来获得锁定，它就可以检查与查询的重叠，然后检查插入是否一切正常，然后通过再次更改锁定文档上的标志来释放锁定。

Another way is just to coordinate the checks on the client side. If you have multiple clients that don't talk to each other at all I guess the best way to do this would be to share a "lock" in the database: findAndModify a document in a lock collection to check and acquire a lock. Once a client has the lock by changing a field on that document, it can do the check for overlaps with a query and then the insert if all is well, then release the lock by changing the flag on the lock document again.

这篇关于MongoDb对Date范围的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！