Other sop/internals

Table Of Contents

Previous topic

Scraping source databases into offline_db

Next topic

DBI Overlay Versioning Bug

This Page

Daya Bay Links

Content Skeleton

DBI Internals

Bug hunting inside DBI, not for users.

Overlay versioning implementation

Driven by the writer Close dybgaudi:Database/DatabaseInterface/DatabaseInterface/DbiWriter.tpl

template<class T>
Bool_t DbiWriter<T>::Close(const char* fileSpec)

... snipped ...

//  Use overlay version date if required.
      if ( fUseOverlayVersionDate &&  fValidRec )
         fPacket->SetVersionDate(fTableProxy->QueryOverlayVersionDate(fValidRec,fDbNo));

//  Set SEQNO and perform I/O.
      fPacket->SetSeqNo(seqNo);
      ... snip ...
      ok = fPacket->Store(fDbNo);

From the various Open:

fUseOverlayVersionDate = vrec.GetVersionDate() == TimeStamp(0,0);

Quoting comments from QueryOverlayVersionDate of dybgaudi:Database/DatabaseInterface/src/DbiTableProxy.cxx:

TimeStamp DbiTableProxy::QueryOverlayVersionDate(const DbiValidityRec& vrec,
                                                     UInt_t dbNo)
{

//
//  Purpose:  Determine a suitable Version Date so that this validity
//            record, if written to the selected DB, will overlay
//            correctly.
//
//  Specification:-
//  =============
//
//  o Determine optimal Version Date to overlay new data.  See Program Notes.

//  Program Notes:-
//  =============


// It is normal practice, particularly for calibration data, to have
// overlapping the validity records.  Each time a new set of runs are
// processed the start time of the validity is set to the start time of
// the first run and the end time is set beyond the start time by an
// interval that characterises the stability of the constants.  So long
// as a new set of constants is created before the end time is reached
// there will be no gap.  Where there is an overlap the Version Date is
// used to select the later constants on the basis that later is better.
// However, if reprocessing old data it is also normal practice to
// process recent data first and in this case the constants for earlier
// data get later version dates and overlay works the wrong way.  To
// solve this, the version date is faked as follows:-
//
//
//   1.  For new data i.e. data that does not overlay any existing data,
//       the version date is set to the validity start time.
//
//   2.  For replacement data i.e. data that does overlay existing data,
//       the version date is set to be one minute greater than the Version
//       Date on the current best data.
//
// This scheme ensures that new data will overlay existing data at the
// start of its validity but will be itself overlaid by data that has
// a later start time (assuming validity record start times are more
// than a few minutes apart)


  //  Create a context that corresponds to the start time of the validity
  //  range.  Note that it is O.K. to use SimFlag and Site masks
  //  even though this could make the context ambiguous because the
  //  context is only to be used to query the database and the SimFlag and
  //  Site values will be ORed against existing data so will match
  //  all possible data that this validity range could overlay which is
  //  just what we want.


  const ContextRange& vr(vrec.GetContextRange());
  Context vc((Site::Site_t) vr.GetSiteMask(),
                  (SimFlag::SimFlag_t) vr.GetSimMask(),
                                       vr.GetTimeStart());

  DbiConnectionMaintainer cm(fCascader);  //Stack object to hold connections

  // Build a complete set of effective validity records from the
  // selected database.
  DbiValidityRecBuilder builder(fDBProxy,vc,vrec.GetSubSite(),vrec.GetTask(),dbNo);

  // Pick up the validity record for the current aggregate.
  const DbiValidityRec& vrecOvlay(builder.GetValidityRecFromAggNo(vrec.GetAggregateNo()));

  // If its a gap i.e. nothing is overlayed, return the start time, otherwise
  // return its Version Date plus one minute.
  TimeStamp ovlayTS(vr.GetTimeStart());
  if ( ! vrecOvlay.IsGap() ) {
    time_t overlaySecs = vrecOvlay.GetVersionDate().GetSec();
    ovlayTS = TimeStamp(overlaySecs + 60,0);
  }

  LOG(dbi,Logging::kDebug1) << "Looking for overlay version date for: "
                   << vrec << "found it would overlap: "
                   << vrecOvlay << " so overlay version date set to "
                   << ovlayTS.AsString("s") << std::endl;
  return ovlayTS;

Overlay overriding problem

Consider overlay usage in a run-by-run to EOT regime:

                                           EOT
100  ---------------------------------------
101        ---------------------------------
102                 ------------------------
103                          ---------------

Other than for the first entry (run 100) in the table there will always be pre-existing data as each subsequent run record gets written. Thus the VERSIONDATE will always get incremented off the TIMESTART of the last entry. This is will cause problems as in the case of overriding overrides there will be VERSIONDATE clashes.

Clearly the solution is to somehow distinguish between an intended overlay:

                                           EOT
100  ---------------------------------------
101        ---------------------------------
102                 ------------------------
102                 ------------------------     <<< real overlay in need of VERSIONDATE = ts102+1min
103                          ---------------

As opposed to a technical overlay:

                                           EOT
100  ---------------------------------------
101        ---------------------------------
102                 ------------------------
102                 ------------------------
103                          ---------------
104                               ----------      <<<< new entry that needs VERSIONDATE = ts104
                                                       rather than  ts103 + 1min
                                                                aka ts102 + 1min + 1min
                                                                aka ts101 + 1min + 1min + 1min
                                                                aka ts100 + 1min + 1min + 1min + 1min

Fix attempt A

Possible Workaround

Do not use overlay versioning on the first pass... instead force the versiondate to be the timestart versiondate = cr.timestart

Possible Solution

Modify the feeler query to make the distinction, maybe as simple as adding clause and VERSIONDATE >= ts Simulate this solution by applying an SqlCondition during the writer close.

if fixcondition:
    condition = "VERSIONDATE >= '%s'" % cr.timestart.AsString("s")
    log.debug( "write_ fixcondition %s during writer close " % condition  )
    gDbi.registry.SetSqlCondition(condition)    ## CAUTION THIS IS A GLOBAL CONDITION

assert wrt.Close()

if fixcondition:
    log.debug( "write_ fixcondition clear after writer close " )
    gDbi.registry.SetSqlCondition("")

This succeeds without requiring special treatment on the first pass.

Looking for preexisting manifestations

Find duplicate versiondates:

SELECT SEQNO,VERSIONDATE,COUNT(VERSIONDATE) AS dupe  FROM CalibFeeSpecVld GROUP BY VERSIONDATE HAVING ( dupe > 1 )

Added a db.py command to do this over all validity tables, usage:

db.py tmp_offline_db vdupe
db.py offline_db     vdupe        ## there are many

Problem with this fix A

Approach A will usually delay manifestation of the problem, but it does not fix it... as apparently the logic that is finding overlays is throwing up VERSIONDATEs that are duplicated within the same context.

Write single entry into empty table

Using starttime of run 11717 and EOT and forced timegate of 60s (tg):

mysql> select p.runNo, v.TIMESTART, v.TIMEEND, v.VERSIONDATE, v.INSERTDATE from DaqRunInfo as p, DaqRunInfoVld as v  where p.SEQNO = v.SEQNO and p.runNo >= 11700 and p.runNo < 11800 ;
+-------+---------------------+---------------------+---------------------+---------------------+
| runNo | TIMESTART           | TIMEEND             | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+---------------------+---------------------+
...
| 11717 | 2011-08-04 05:54:47 | 2011-08-04 05:59:51 | 2011-08-04 05:54:47 | 2011-08-04 06:09:15 |
...

The pre-write query on empty table is:

select * from DemoVld where
       TimeStart <= '2011-08-04 05:55:47'                   ##       timestart <= ts + tg
  and  TimeEnd    > '2011-08-04 05:53:47'                   ##   and   timeend > ts - tg

  and  SiteMask & 127 and SimMask & 1 and  Task = 0 and SubSite = 0
  order by VERSIONDATE desc

Source is QueryValidity from dybgaudi:Database/DatabaseInterface/src/DbiDBProxy.cxx

I suspect that the unhealthy VERSIONDATE coupling when employing overlay versioning to override priors can be avoided with an additional requirement on the feeler query:

VERSIONDATE >= '2011-08-04 05:54:47'   ##    VERSIONDATE >= ts

When all entries use TIMEEND of EOT, the pre-write query collapses to:

TIMESTART <= ts + tg

Ascii art:

                           ts+tg
                             |                         EOT
No prexisting entries        |
                             |
                             |
                             |
                       |==x==|
                       |     |
                       |     |
                       |     |
                       |  x--|-------------------------     single entry
                       |     |
                     ts-tg

Following vld peeking, some min-maxing is done... the VERSIONDATE is wideopen as no-preexisting data found by first query.

First Vld-start after gate:

select min(TIMESTART) from DemoVld where
            TIMESTART > '2011-08-04 05:55:47'                                 ##         timestart > ts + tg
      and VERSIONDATE >= '1970-01-01 00:00:00'                                ##   and versiondate >= 0
      and SiteMask & 127 and SimMask & 1 and  SubSite = 0 and  Task = 0

First Vld-end after gate:

select min(TIMEEND) from DemoVld where
           TIMEEND > '2011-08-04 05:55:47'                                   ##     timeend > ts + tg
     and VERSIONDATE >= '1970-01-01 00:00:00'                                ## and versiondate >= 0
     and SiteMask & 127 and SimMask & 1 and  SubSite = 0 and  Task = 0

Last Vld-start before gate:

select max(TIMESTART) from DemoVld where
            TIMESTART < '2011-08-04 05:53:47'                                 ##      timestart < ts - tg
      and VERSIONDATE >= '1970-01-01 00:00:00'                                ##  and versiondate >= 0
     and SiteMask & 127 and SimMask & 1 and SubSite = 0 and  Task = 0

Last Vld-end before gate:

select max(TIMEEND) from DemoVld where
               TIMEEND < '2011-08-04 05:53:47'                                ##     timeend < ts - tg
       and VERSIONDATE >= '1970-01-01 00:00:00'                               ## and versiondate >= 0
      and SiteMask & 127 and SimMask & 1 and  SubSite = 0 and  Task = 0

Source is FindTimeBoundaries from dybgaudi:Database/DatabaseInterface/src/DbiDBProxy.cxx which is driven from DbiValidityRecBuilder ctor dybgaudi:Database/DatabaseInterface/src/DbiValidityRecBuilder.cxx and is controllable by argument findFullTimeWindow

Resulting insert goes in with VERSIONDATE == TIMESTART:

INSERT INTO DemoVld VALUES      ## TIMESTART              TIMEEND                           VERSIONDATE             INSERTDATE
                           (31,'2011-08-04 05:54:47','2038-01-19 03:14:07',127,1,0,0,-1,'2011-08-04 05:54:47','2011-08-11 04:29:46')
INSERT INTO Demo VALUES
                           (31,1,10,11717)

Write two entries at same validity range ts:EOT into empty table

1st entry proceeds precisely as above. The feeler query of 2nd entry is the same but this time it yields the 1st entry:

+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|    31 | 2011-08-04 05:54:47 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2011-08-04 05:54:47 | 2011-08-11 04:29:46 |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+

The min-maxing proceeds similarly but this time with VERSIONDATE >= '2011-08-04 05:54:47'

Resulting insert goes in with VERSIONDATE == TIMESTART + 1min:

INSERT INTO DemoVld VALUES        ##                                                        VERSIONDATE
                           (32,'2011-08-04 05:54:47','2038-01-19 03:14:07',127,1,0,0,-1,'2011-08-04 05:55:47','2011-08-11 04:29:46')
INSERT INTO Demo VALUES
                           (32,1,11,11717)

Write 3 entries for different runs into empty table

Result is coupled VERSIONDATE:

mysql> select * from DemoVld ;
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|    33 | 2011-08-04 05:54:47 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2011-08-04 05:54:47 | 2011-08-11 05:21:12 |
|    34 | 2011-08-04 06:15:46 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2011-08-04 05:55:47 | 2011-08-11 05:21:12 |
|    35 | 2011-08-04 07:02:51 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2011-08-04 05:56:47 | 2011-08-11 05:21:12 |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from Demo ;
+-------+-------------+------+-------+
| SEQNO | ROW_COUNTER | Gain | Id    |
+-------+-------------+------+-------+
|    33 |           1 |   10 | 11717 |
|    34 |           1 |   10 | 11718 |
|    35 |           1 |   10 | 11719 |
+-------+-------------+------+-------+
3 rows in set (0.00 sec)

The feeler query prior to the 2nd write sees the 1st write (as effectively just doing timestart < ts + tg and timeend > ts - tg) and grabs the VERSIONDATE from the last and offsets from there:

mysql> select * from DemoVld where     TimeStart <= '2011-08-04 06:16:46' and TimeEnd    > '2011-08-04 06:14:46' and SiteMask & 127 and SimMask & 1 and  Task = 0 and SubSite = 0 order by VERSIONDATE desc ;
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|    33 | 2011-08-04 05:54:47 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2011-08-04 05:54:47 | 2011-08-11 05:21:12 |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Ascii art:

                                 ts+tg
                                   |                         EOT
-----------------------------------|-------------------------
    -------------------------------|-------------------------
          -------------------------|-------------------------
                  -----------------|-------------------------
                             |==x--|-------------------------   pre-existing entry
                             |     |   ----------------------
                             |     |
                             |     |
                             |  x--|-------------------------
                             |     |
                           ts-tg

Delving into overlay detection and DbiValidityRecBuilder

The crucial VERSIONDATE is supplied by TimeStamp DbiTableProxy::QueryOverlayVersionDate(const DbiValidityRec& vrec,UInt_t dbNo) is

// Build a complete set of effective validity records from the
 // selected database.
 DbiValidityRecBuilder builder(fDBProxy,vc,vrec.GetSubSite(),vrec.GetTask(),dbNo);

 // Pick up the validity record for the current aggregate.
 const DbiValidityRec& vrecOvlay(builder.GetValidityRecFromAggNo(vrec.GetAggregateNo()));

 // If its a gap i.e. nothing is overlayed, return the start time, otherwise
 // return its Version Date plus one minute.
 TimeStamp ovlayTS(vr.GetTimeStart());
 if ( ! vrecOvlay.IsGap() ) {
   time_t overlaySecs = vrecOvlay.GetVersionDate().GetSec();
   ovlayTS = TimeStamp(overlaySecs + 60,0);
 }

Which is primarily determined by the DbiValidityRecBuilder::GetValidityRecFromAggNo, namely

const DbiValidityRec& GetValidityRecFromAggNo(Int_t aggNo) const { return this->GetValidityRec(this->IndexOfAggno(aggNo)); }

Non-aggregated case of aggNo=-1 is treated as single-slice aggregate.

DbiValidityRecBuilder is DBIs summarization of a Validity query, revolving around fVRecs vector of DbiValidityRec for each aggregate (or only one when non-aggregate). For non-extended queries the DVRB is quite lightweight with just entries that start off as Gaps for each aggregate in the vector (contrary for first impressions and very different extended context behaviour) and are trimmed by the Vld query entries (which are not stored).

fGap : special vrec holding trim results

Created by DbiValidityRecBuilder::MakeGapRec(const Context& vc, const string& tableName,Bool_t findFullTimeWindow) essentially:

ContextRange gapVR(vc.GetSite(), vc.GetSimFlag(), startGate, endGate);    ##
fGap =  DbiValidityRec(gapVR, fSubSite, fTask, -2, 0, 0, kTRUE);
                     ## range subsite task aggNo seqNo dbNo isGap

Gate is BOT:EOT when findFullTimeWindow=True otherwise tis ts-tg:ts+tg, Dbi::GetTimeGate(tableName) defaults are big ~10days

Trimming in Builder ctor

Prior to vld row loop

const TimeStamp curVTS = vc.GetTimeStamp();
TimeStamp earliestCreate(0);     //    Set earliest version date to infinite past - the right value if in a gap.

Within the vld row loop

const DbiValidityRec* vr = dynamic_cast<const DbiValidityRec*>(result.GetTableRow(row));

//        Trim the validity record for the current aggregate number by this record and see if we have found valid data yet.
DbiValidityRec& curRec = fVRecs[index];   // curRec summarizes all the validities within an aggregate tranche

curRec.Trim( curVTS, \*vr );


     ####
     #### only while curRec is still a gap does Trim do anything
     ####       ... it becomes non gap when bracketing validity is hit
     ####       ... the ordering is VERSIONDATE desc, so that means the highest VERSIONDATE with validity
     ####       ...  becomes non-gap first, there-after no more trimming is done
     ####
     ####
     #### if curVTS is within \*vr range (ie \*vr brackets curVTS)
     ####         curRec becomes \*vr                                   (that includes the VERSIONDATE of \*vr)
     ####         range is trimmed to the overlap with the other
     ####  otherwise
     ####         range is trimmed to exclude the other
     ####
     ####


if ( ! curRec.IsGap() ) {  foundData = kTRUE; curRec.SetDbNo(dbNo); }

//      Find the earliest non-gap version date that is used
if ( curRec.GetSeqNo() == vr->GetSeqNo() &&  ( earliestCreate > vr->GetVersionDate() || earliestCreate.GetSec() == 0 )) earliestCreate = vr->GetVersionDate();

     ######### no non-gap restriction ?
     #########     .... implicitly done as while curRec is a gap it has SEQNO 0
     ######### WHY SEQNO EQUALITY ?
     #########           WILL ONLY FIND ONE ENTRY IN ENTIRE VECTOR
     #########            so earliestCreate will become just the resultant VERSIONDATE ?

//      Count the number used and sum the time windows
++numVRecIn;
const ContextRange range = vr->GetContextRange();
Int_t timeInterval =   range.GetTimeEnd().GetSec() - range.GetTimeStart().GetSec();

sumTimeWindows += timeInterval;
++numTimeWindows;

After vld loop

//    If finding findFullTimeWindow then find bounding limits
//    for the cascade and sim flag and trim all validity records

            ############### including the crucial curRec ????

if ( findFullTimeWindow ) {
    TimeStamp start, end;
    proxy.FindTimeBoundaries(vcTry,fSubSite,fTask,dbNo,earliestCreate,start,end);
    LOG(dbi,Logging::kDebug1) << "Trimming validity records to " << start << " .. " << end << std::endl;
    std::vector<DbiValidityRec>::iterator itr(fVRecs.begin()), itrEnd(fVRecs.end());
    for( ; itr != itrEnd; ++itr ) itr->AndTimeWindow(start,end);
}

AndTimeWindow : overlap range

Greater start and lower end:

st                    en
    |------------------|

         |-------|
       so        eo

         |=======|
       st'       en'

FindTimeBoundaries

Provides (start,end) representing proximity to validity regions before and after the gate, which have no overlap into the gate:

                            |
                            |                     ----------
                            |               -----------
                            |          ----------
                            |     1--------2
                            |     .        .
                       ts   |     .        .
                  |----x----|     .        .
                  sg        eg    .
                  |               .
                  |               .
     3------4     |               .
-------     .     |               .
            .     |               .
          start                  end


       1    min(ts) where ts > eg
       2    min(te) where te > eg
       3    max(ts) where ts < sg
       4    max(te) where te < sg

But with restriction: VERSIONDATE >= earliestCreate

void DbiDBProxy::FindTimeBoundaries(const Context& vc,
                                    const Dbi::SubSite& subsite,
                                    const Dbi::Task& task,
                                    UInt_t dbNo,
                                    TimeStamp earliestCreate,
                                    TimeStamp& start,
                                    TimeStamp& end) const {
//
//
//  Purpose: Find next time boundaries beyond standard time gate.
//
//  Arguments:
//    vc             in    The Validity Context for the query.
//    subsite        in    The subsite of the query.
//    task           in    The task of the query.
//    dbNo           in    Database number in cascade (starting at 0).
//    earliestCreate in    Earliest version date of data in the time gate
//    start          out   Lower time boundary or TimeStamp(0,0) if none
//    end            out   Upper time boundary or TimeStamp(0x7FFFFFFF,0) if none
//
//  Specification:-
//  =============
//
//  o Find the next time boundary (either TIMESTART or TIMEEND)
//    outside the current time gate with a version date >= earliestCreate.

  LOG(dbi,Logging::kMonitor) << "FindTimeBoundaries for table " <<  fTableName
                        << " context " << vc
                        << " subsite " << subsite
                        << " task " << task
                        << " Earliest version date " <<  earliestCreate
                        << " database " << dbNo << std::endl;

//  Set the limits wide open
  start = TimeStamp(0,0);
  end   = TimeStamp(0x7FFFFFFF,0);

//  Construct a Time Gate on the current date.

  const TimeStamp curVTS = vc.GetTimeStamp();
  Int_t timeGate = Dbi::GetTimeGate(this->GetTableName());
  time_t vcSec = curVTS.GetSec() - timeGate;

  TimeStamp startGate(vcSec,0);
  vcSec += 2*timeGate;
  TimeStamp endGate(vcSec,0);

  string earliestCreateString(Dbi::MakeDateTimeString(earliestCreate));
  string startGateString(Dbi::MakeDateTimeString(startGate));
  string endGateString(Dbi::MakeDateTimeString(endGate));

// Extract information for Context.

  Site::Site_t    detType(vc.GetSite());
  SimFlag::SimFlag_t       simFlg(vc.GetSimFlag());

// Use an auto_ptr to manage ownership of DbiStatement and TSQLStatement
  std::auto_ptr<DbiStatement> stmtDb(fCascader.CreateStatement(dbNo));

  for (int i_limit =1; i_limit <= 4; ++i_limit ) {

    DbiString sql("select ");
    if ( i_limit == 1 ) sql  << "min(TIMESTART) from " << fTableName << "Vld where TIMESTART > '" << endGateString << "' ";
    if ( i_limit == 2 ) sql  << "min(TIMEEND)   from " << fTableName << "Vld where TIMEEND > '"   << endGateString  << "' ";
    if ( i_limit == 3 ) sql  << "max(TIMESTART) from " << fTableName << "Vld where TIMESTART < '" << startGateString << "' ";
    if ( i_limit == 4 ) sql  << "max(TIMEEND)   from " << fTableName << "Vld where TIMEEND < '" << startGateString  << "' ";

    sql << " and SiteMask & " << static_cast<unsigned int>(detType) << " and SimMask & " << static_cast<unsigned int>(simFlg)
    << " and VERSIONDATE >= '" << earliestCreateString << "'"
    << " and  SubSite = " << subsite
    << " and  Task = " << task;

    LOG(dbi,Logging::kMonitor) << "  FindTimeBoundaries query no. " << i_limit  << " SQL:" <<sql.c_str() << std::endl;

    std::auto_ptr<TSQLStatement> stmt(stmtDb->ExecuteQuery(sql.c_str()));
    stmtDb->PrintExceptions(Logging::kDebug1);

//  If the query returns data, convert to a time stamp and trim the limits
    TString date;
    if ( ! stmt.get() || ! stmt->NextResultRow() || stmt->IsNull(0) ) continue;
    date = stmt->GetString(0);

    if ( date.IsNull() ) continue;
    TimeStamp ts(Dbi::MakeTimeStamp(date.Data()));

    LOG(dbi,Logging::kMonitor) << "  FindTimeBoundaries query result: " << ts << std::endl;
    if ( i_limit <= 2 && ts < end   ) end   = ts;
    if ( i_limit >= 3 && ts > start ) start = ts;

  }

  LOG(dbi,Logging::kMonitor) << "FindTimeBoundaries for table " <<  fTableName
                    << " found " << start << " .. " << end << std::endl;

}

Bracketed Trimming : effective range reduced to overlap other

Both ranges have validity, but Caution

  1. other becomes this with range chopped by the initial this

    1. ‘’‘VERSIONDATE gets transferred from other to this!!’‘’
//  If this record is not a gap then the other record can be ignore
//  as it is of lower priority.

if ( ! IsGap() ) return;

   // If entry brackets query date, then use it but with a validity that
   // is trimmed by the current record.

     if ( startOther <= queryTime && endOther > queryTime ) {
       if ( start < startOther ) start = startOther;
       if ( end   > endOther   ) end   = endOther;
       \*this = other;
       SetTimeWindow(start,end);
     }

Pictorially:

                 queryTime
                      |
start                 |     end
  --------------------|-------
         -------------|-----------------------
      startOther      |      .             endOther
         .            |      .
         .                   .
         .                   .
         |===================|
       start*               end*

Consider the equal range trim, tis bracketing so VERSIONDATE will adopt the others:

              queryTime
                |
 start          |                   end
   -------------|---------------------
   -------------|---------------------
startOther      |                   endOther
   .            |
   .
   .
   |=================================|
 start*                            end*

Non bracketed trim : effective range reduced to exclude the other

Other range is not valid for the queryTime but the current validity range is impinged by the other. Before and after overlap trims with no identity/VERSIONDATE change.

     // It doesn't bracket, so use it to trim the window

if ( endOther <= queryTime ) {
   if ( start < endOther   ) SetTimeWindow(endOther,end);
 }
 else if ( startOther > queryTime ) {
   if ( end   > startOther ) SetTimeWindow(start, startOther);
 }

Other before (will never occur with endOther at EOT) and other after:

                             queryTime
                                  |
             start                |                       end
             |--------------------|------------------------|
     |-----------------|          |
startOther          endOther      |
                                  |
             |.........|===================================|
                    start*                                 end*



                             queryTime
                                  |
             start                |                       end
             |--------------------|------------------------|
                                  |           |------------------------|
                                  |         startOther             endOther
                                  |
             |================================|............|
           start*                          end*

Double Overlay Example

Consider writing 4 runs with timeend to EOT and then going back an overlaying on top:

       ts

        1       2      3      4                                               VERSIONDATE
A       |-----------------------------------------------------------              ts
B       .       |---------------------------------------------------              ts+1min
C       .              |--------------------------------------------              ts+2min
D       .                     |-------------------------------------              ts+3min
        .
        .
E       |------------------------------------------------------------             ts+1min

        <======>     effective validity 1:2


        .       |----------------------------------------------------
                       |---------------------------------------------
                              |--------------------------------------

When checking for overlay prior to 5th write a VERSIONDATE desc loop causes gap trimming until strike validity at A.

   
D 1970-01-01 00:00:00 .. 2010-01-01 04:00:00
C 1970-01-01 00:00:00 .. 2010-01-01 03:00:00
B 1970-01-01 00:00:00 .. 2010-01-01 02:00:00
A 2010-01-01 01:00:00 .. 2010-01-01 02:00:00

Debugging:

[12 /79 ] check_write          ('b', (11717,), 'a')                              ==>
DVRB rowvr  row:0 seqNo:4 ts:2010-01-01 04:00:00.000000000Z vd:2010-01-01 01:03:00.000000000Z
DVRB rowvr  row:1 seqNo:3 ts:2010-01-01 03:00:00.000000000Z vd:2010-01-01 01:02:00.000000000Z
DVRB rowvr  row:2 seqNo:2 ts:2010-01-01 02:00:00.000000000Z vd:2010-01-01 01:01:00.000000000Z
DVRB rowvr  row:3 seqNo:1 ts:2010-01-01 01:00:00.000000000Z vd:2010-01-01 01:00:00.000000000Z
DVRB curRec SeqNo: 0  AggNo: -1  DbNo: 0 (gap)  ContextRange: |0x07f|0x  1| 1970-01-01 00:00:00 .. 2010-01-01 04:00:00
DVRB curRec SeqNo: 0  AggNo: -1  DbNo: 0 (gap)  ContextRange: |0x07f|0x  1| 1970-01-01 00:00:00 .. 2010-01-01 03:00:00
DVRB curRec SeqNo: 0  AggNo: -1  DbNo: 0 (gap)  ContextRange: |0x07f|0x  1| 1970-01-01 00:00:00 .. 2010-01-01 02:00:00
DVRB curRec SeqNo: 1  AggNo: -1  DbNo: 0        ContextRange: |0x07f|0x  1| 2010-01-01 01:00:00 .. 2010-01-01 02:00:00
Traceback (most recent call last):
  File "test_overlay_versioning.py", line 382, in <module>
    ret = fn(\*args)
  File "test_overlay_versioning.py", line 293, in check_write
    dwrite = write_( cr_(g['Id']) , **g )
  File "test_overlay_versioning.py", line 195, in write_
    assert lvd not in lvds.values(), "lvd %s is already present %r " %(lvd,lvds)
AssertionError: lvd 2010-01-01 01:01:00 is already present {1L: '2010-01-01 01:00:00', 2L: '2010-01-01 01:01:00', 3L: '2010-01-01 01:02:00', 4L: '2010-01-01 01:03:00'}

Trim In full

void DbiValidityRec::Trim(const TimeStamp& queryTime, const DbiValidityRec& other) {
//
//
//  Purpose:  Trim this validity record so that represents
//            best validity record for query.
//
//  Arguments:
//    queryTime    in    Time of query
//    other        in    DbiValidity record satisfying query
//
//  Return:    None.
//
//  Contact:   N. Tagg        Original Author: N. West, Oxford
//
//  Specification:-
//  =============
//
//  o Update this validity record so that it remains the best
//    validity record taking into account the supplied record.


//  Program Notes:-
//  =============

//  This is the function that deal with validity management.
//  It takes into account that several validity records may
//  overlap and that the best one is the one with the latest
//  version date that brackets the query date.  Other entries
//  with later version dates may trim start or end times.

//  Assumptions:-
//  ===========
//
//  That entries are submitted in strict descending priority i.e.:-
//
//  1)  Entries for a higher priority database precede those from a
//      lower priority one.
//
//  2)  Within a database entries are in descending version date order.


//  Ignore other records that are either gaps or have wrong
//  aggregate number.

  if ( fAggregateNo != other.fAggregateNo || other.IsGap() ) return;

//  If this record is not a gap then the other record can be ignore
//  as it is of lower priority.

  if ( ! IsGap() ) return;

  TimeStamp start      = fContextRange.GetTimeStart();
  TimeStamp end        = fContextRange.GetTimeEnd();
  TimeStamp startOther = other.GetContextRange().GetTimeStart();
  TimeStamp endOther   = other.GetContextRange().GetTimeEnd();

// If entry brackets query date, then use it but with a validity that
// is trimmed by the current record.

  if ( startOther <= queryTime && endOther > queryTime ) {
    if ( start < startOther ) start = startOther;
    if ( end   > endOther   ) end   = endOther;
    *this = other;
    SetTimeWindow(start,end);
  }

// It doesn't bracket, so use it to trim the window

  else {

    if ( endOther <= queryTime ) {
      if ( start < endOther   ) SetTimeWindow(endOther,end);
    }
    else if ( startOther > queryTime ) {
      if ( end   > startOther ) SetTimeWindow(start, startOther);
    }
  }

}