Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17395

REPLACE/INSERT ODKU: support WITHOUT OVERLAPS

    XMLWordPrintable

    Details

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nikitamalyavin Nikita Malyavin
              Reporter:
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: