REPLACE/INSERT ODKU: support WITHOUT OVERLAPS

Replace and Insert ODKU work differently, but both need to cope with Application periods

Let's start from use cases.

Say, there's an web site hosting hundreds of small online games (like, flash games).

Typical ODKU use case (that doesn't use WITHOUT OVERLAPS): count number of players per game. The table could be

CREATE TABLE stats (game VARCHAR(100), count INT, UNIQUE (game));

And for every player of a specific game we do

INSERT stats VALUES ($game_name, 1) ON DUPLICATE KEY UPDATE count=count+1;

Now having WITHOUT OVERLAPS we can count number of players at any specific time. The table could be

CREATE TABLE stats (

  game VARCHAR(100),

  count INT,

  start_time TIMESTAMP,

  end_time TIMESTAMP,

  PERIOD FOR playtime(start_time, end_time),

  UNIQUE (game, playtime WITHOUT OVERLAPS));

and then, for every player

INSERT stats VALUES ($game_name, 1, $start_time, $end_time) ON DUPLICATE KEY UPDATE count=count+1;

Another example, a hotel booking site, where one can specify a hotel and the date range and then proceed to booking. More often than not visitors don't become customers and don't complete the booking, but, say, we will want to track the demand and record these incomplete bookings. So for every abandoned booking we increment the counter, much like above

INSERT stats VALUES ($hotel, 1, $arrival, $departure) ON DUPLICATE KEY UPDATE count=count+1;

This defines the behavior of ODKU. It has to do the INSERT for non-overlapping part of the range and UPDATE for the overlapping part. For example (with hotels):

INSERT stats ("Footel", 1, '2010-01-01', '2010-03-01');

INSERT stats ("Footel", 1, '2010-04-01', '2010-06-01');

INSERT stats ("Footel", 1, '2010-02-01', '2010-05-01') ON DUPLICATE KEY UPDATE count=count+1;

the result should be

Footel    1    2010-01-01    2010-02-01

Footel    2    2010-02-01    2010-03-01

Footel    1    2010-03-01    2010-04-01

Footel    2    2010-04-01    2010-05-01

Footel    1    2010-05-01    2010-06-01

wanted: use cases for REPLACE