Table of Contents

Date range handling of tables

Module RNGMOD Date range handling of tables

Description

The Module RNGMOD supplies functionality to maintain and access database tables with validity ranges of the different entries.

To support this functionality the database table, which is supposed to hold the entries for the different dates, need to have the following fields:

  1. BEGDAT This date defines the start of the validity of this entry (including date). If the validity of an entry starts at the very beginning, the field must have the value as supplied by “SdbRngGetMinDat”.
  2. ENDDAT This date defines the end of the validity (excluding date). Thus up to the day before this entry is valid. If the validity of an entry ends at the very end, the field must have the value as supplied by “SdbRngGetMaxDat”.
  3. INR This field is used do identify a single entry uniquely. This field is used primarily for internal purpose, like update and delete.
  4. Additional Key fields are used to identify the objects to which the additional information with the date range validity are stored. These fields are usually a code field and/or an object type plus an object INR. These key fields have to uniquely identify an object where the entries are associated to.

There is support for a special case of entries with no validity. If it is necessary to store entries with no validity, these entries might be identified by the same contents of the fields ENDDAT and BEGDAT which has the logical meaning the validity ends one day before the start of the validity. In this case the default sequence of browsing entries with the same BEGDAT is defined by the sequence of the INR.

If there exist more then one entry for a single day, only the last entry (i.e. the entry which contains the value valid at the end of the day) will contain a value ENDDAT > BEGDAT. This entry will be supplied as last entry for the day in any selection created by any RNGMOD function.

All other entries for the same day (those that contain only intermediate entries) contain ENDDAT=BEGDAT and will be read before the one entry which is valid at the end of the day.

The module supplies the following functions:

Rule Arguments type
SdbRngInsert inout Rec module sub
SdbRngSet inout Rec module sub
SdbRngDelete inout Rec module sub
SdbRngRead inout Rec module, in PerDat datetype sub
SdbRngSelectAfterDate inout Rec module, in BegDat datetype sub
SdbRngSelectAfterEntry inout Rec module sub
SdbRngSelectValidInRange inout Rec module, in BegDat datetype, in EndDat datetype sub
CreateDatRngSql in ArgDat datetype function : text
CreateDatRngSqlIns in ArgIns text, in ArgFld text function : text
CreateDatRngSqlPerDat in ArgIns text, in ArgDat datetype function : text
CreateKeySqlCus in ArgClass text, in ArgMod text, inout ArgCusRul text function : text
SdbRngGetMaxDat function : datetype
SdbRngGetMinDat function : datetype
CreateAddSqlCus in ArgClass text, in ArgMod text function : text
CreateKeySql in Rec module, out ArgModPth text, out ArgCusRul text function : text

SdbRngSet

SdbRngInsert

SdbRngRead

This procedure extracts the contents of the key fields out of the passed module and reads the entry, valid at the specified date. If no entry is valid at this date, no record is read. To use this procedure the key fields in the record have to be set to values identifying the object and then the procedure is called.

SdbRngDelete

The entry identified by the INR stored in the passed module is deleted. If there is another entry with an ending validity directly at the beginning validity of the deleted entry, the validity of this other entry is automatically extended to the ending validity of the deleted entry.
Usually the entry to be deleted has been read in advance to use this procedure. At least the INR field has to be set to identify the entry to be deleted.

Internally the module uses the following functions:

1. “CreateSetSql” (date) - This function creates the standard SQL expression used to extend an existing where-clause to select the valid entry at the specified date.

2. “CreateRngSql” (module) - This function creates the SQL expression used to identify the key fields of an entry. As the different tables use different key fields, this function has the built-in logic to extract the associated fields out of the passed argument according to the different tables. This function has to be maintained when adding new tables with date range functionality.

Sample

To extract the list of all valid rates per today, the following logic might be used: 

# get the RNGMOD filter SQL for the XRT table
$RngCon = CreateDatRngSqlPerDat( “XRT”, Today )

# Add the multi entity filter 
$SqlCon = SdbEtyGenSql( XRT, $RngCon )

# build the overall SQL
$Sql = “Select CUR, BEGDAT, MIDRAT from XRT “ + $SqlCon + “ order by CUR” 

DbExecuteSql( $Sql )

DbFetchStream( $XrtStm )

Properties

Property YES/NO
Single page frame No
Show toolbar No
Show menu No
Show statusbar No
Show hint No
Fit panel size No

Rules

Rule Name Additional
function CreateAddSqlCus text
function CreateDatRngSql text static
function CreateDatRngSqlIns text global
function CreateDatRngSqlPerDat text global
function CreateKeySql text
function CreateKeySqlCus text
function SdbRngGetMaxDat datetype static
function SdbRngGetMinDat datetype global
sub DeleteSet
sub SdbRngDelete global
sub SdbRngInsert global
sub SdbRngRead global
sub SdbRngSelectAfterDate global
sub SdbRngSelectAfterEntry global
sub SdbRngSelectValidInRange global
sub SdbRngSet global

Rules Description and Comments

function CreateAddSqlCus( in ArgClass text, in ArgMod text ) : text

function to add customer specific sql condition

function CreateDatRngSql( in ArgDat datetype ) : text static

Function to generate the necessary sql-clause to restrict
the selection to the one valid entry for tables which are
stored via the date-range-method of RNGMOD.
ArgDat holds the date per which the software tries to retrieve
the relevant records.
obsolete function. Replaced by enhanced versions in the
corresponding functions: CreateDatRngSqlPerDat, CreateDatRngSqlIns

function CreateDatRngSqlIns( in ArgIns text, in ArgFld text ) : text global

Function to generate the necessary sql-clause to restrict the selection to the valid entry for tables which are
stored via the date-range-method of RNGMOD.
ArgIns is the optional name of the table instance to be accessed. (e.g. Call routine with “GID”)
ArgFld holds a field reference to identify the relevant date. (e.g. Call Routine with “GID.OPNDAT” or DBSqlDate( $Dat ))
The field reference is not checked and used as is without any modification.
Corresponding functions: CreateDatRngSqlPerDat

function CreateDatRngSqlPerDat( in ArgIns text, in ArgDat datetype ) : text global

Function to generate the necessary sql-clause to restrict
the selection to the valid entry for tables which are
stored via the date-range-method of RNGMOD.
ArgIns is the optional name of the table instance to be accessed.
ArgFld holds the relevant date. It has to be within the valid range.
Corresponding functions: CreateDatRngSqlIns

function CreateKeySql( in Rec module, out ArgModPth text, out ArgCusRul text ) : text

function to hold the know how which table needs which fields for
date range access
The argument should be a module. The contents of the keyfields
and the module class are used to determine the sql-expression.
the result is a sql-expression selecting the key used for the
date range access. The use in combination with the date specific
sql-expression leads to an sql-expression describing exactly one
entry in the table.

function CreateKeySqlCus( in ArgClass text, in ArgMod text, inout ArgCusRul text ) : text

function to hold the know how which customer table needs which fields for date range access
if dependent updates need to be executed just set the rulename to be called into ArgCusRul

function SdbRngGetMaxDat : datetype static

Routine to return the highest possible date within
the date range handling. As this date is an excl.
date only data of up to the day before can be stored.
(For CBS the relevant routine is GetRelevantDateForBalances)
If this routine is modified to return another date, all
tables holding data to be accessed via date range
methods need to be converted.
Corresponding function: SdbRngGetMinDat

function SdbRngGetMinDat : datetype global

Routine to return the smallest possible date
within the date range handling.
If this routine is modified to return another
date, all tables holding data to be accessed
via date range methods need to be converted.
Corresponding functions: SdbRngGetMaxDat

sub DeleteSet( inout Rec module, in SqlArg text )

routine to delete all entries of the passed table which
fit to the passed sql-where-clause. The clause has to
include the keyword WHERE when specifying a selection
criteria.
The routine does not take care for a database commit.
This has to be done in the calling environment.
The entries to be deleted are selected and entrywise
locked and then deleted.

sub SdbRngDelete( inout Rec module ) global

The entry identified by the INR in the passed module is
deleted.
The passed module is cleared upon successful execution.
If the INR or any other data is used after the call, this field
has to be saved in some way (e.g. to a register)

sub SdbRngInsert( inout Rec module ) global

Inserts Entry without deleting existing entries.
BEGDAT has to be specified.
ENDDAT might be specified. It will be reduced to the Next
existing entry If overlapping to another entry
This procedure adds the passed entry into the database. No existing records will be overwritten. The BEGDAT of the record has to be set. The ENDDAT might be set. If there are any records defining a future validity, the ENDDAT of the passed record will be adjusted accordingly. If there is an existing entry with the same BEGDAT, the existing entry will be made inaccessible by setting the ENDDAT to the same date as the BEGDAT. Only via browsing functions this entry gets visible.

sub SdbRngRead( inout Rec module, in PerDat datetype ) global

Routine to read the record relevant for a certain date in a table
organized by the date-range-method of RNGMOD.
The necessary identifying key values are passed in the passed
record. The date specifying the relevant entry is passed as
the second argument.
The result is the relevant entry or an empty entry and the
returncode TdOdbcNotFound.

sub SdbRngSelectAfterDate( inout Rec module, in BegDat datetype ) global

procedure to create a cursor for all entries with a beginning validity
after the specified date. The set is ordered by ascending date and within
a date by INR.

sub SdbRngSelectAfterEntry( inout Rec module ) global

procedure to create a cursor for all entries with a beginning validity
after the specified entry.

sub SdbRngSelectValidInRange( inout Rec module, in BegDat datetype, in EndDat datetype ) global

procedure to create a cursor for all entries with a validity
in a specified date range.
This includes the entry valid at the begin date.
This includes entries, within the daterange, which are not really
valid, because there ENDDAT is equal to BEGDAT. The application
has to decide whether to use or to skip them.

sub SdbRngSet( inout Rec module ) global

Stores Entry and deletes/updates all existing entries
within the specified daterange.
The owning object has to be locked !

This procedure inserts the passed entry into the database. All entries of the associated object, which are completely covered be the defined validity will be deleted. If entries are stored, where the validity range overlaps to the passed entry, the entries in the database are adjusted to assure, that there is never more than one valid entry per object for any given date. On using this routine be sure to set the BEGDAT and ENDDAT correctly. If both are filled with Min and Max, all records associated to the defined object are completely deleted and replaced by the single new entry.