The Interval RDBMS Pattern

The Interval Pattern

I kick-start the discussion of RDBMS patterns presenting the code required to implement the Interval Pattern.
This pattern deals with those attributes/fields in a table that define an interval. Here are certain examples.
From and To Date (Time, DateTime, Timestamp etc.,)
IP Address Ranges
Maximum and Minimum counts of Entities

Various functions and possibly triggers also accompany such attribute pairs.
Here are the functions and their implementation in PL/pgSQL
The functions are
Overlap_Interval
Distance-Size of Interval
Containment within the Interval

Here are the functions

Containment Function

DROP FUNCTION within_range(DATA_TYPE,DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION within_range(DATA_TYPE,DATA_TYPE,DATA_TYPE) RETURNS BOOLEAN AS '
DECLARE
bResult BOOLEAN;
BEGIN
bResult := false;
IF ( ( $1 >= $2 ) AND ( $1 <= $3 ) ) THEN
bResult := true;
END IF;

RETURN bResult;
END;
' LANGUAGE 'plpgsql';

Distance-Size of the Interval Function

DROP FUNCTION distance_range(DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION distance_range(DATA_TYPE,DATA_TYPE) RETURNS DATA_TYPE AS '
BEGIN
RETURN $2 - $1;
END;
' LANGUAGE 'plpgsql';

The Overlapping Interval Function

DROP FUNCTION overlap_range(DATA_TYPE,DATA_TYPE,DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION overlap_range(DATA_TYPE,DATA_TYPE,DATA_TYPE,DATA_TYPE) RETURNS BOOLEAN AS '
DECLARE
bResult BOOLEAN;
A DATA_TYPE;
B DATA_TYPE;
X DATA_TYPE;
Y DATA_TYPE;
BEGIN
IF ( $1 >= $2 ) THEN
A := $1;
B := $2;
X := $3;
Y := $4;
ELSE
X := $1;
Y := $2;
A := $3;
B := $4;
END IF;
bResult := false;
IF ( ( B >= X ) AND ( A <= X ) ) THEN
bResult := true;
END IF;

IF ( bResult AND ( A <= Y ) AND ( B >= Y) ) THEN
bResult := true;
END IF;

IF ( bResult AND ( A <= X ) AND ( B <= Y ) ) THEN
bResult := true;
END IF;

RETURN bResult;
END;
' LANGUAGE 'plpgsql';

We need to replace all occurrences of DATA_TYPE with the data type of the end points of the interval.

I will be back with implementations more such patterns in future as and when I encounter them.

posted by Kishore Sainath @ 6:58 PM 0 comments

This is my first blog

This is my first blog.

This is also my first posting.

This blog is called Kishore's Patterns because it is going to serve as a pattern repository. I will document different design principles that I have learned. Respecting my employer's Intellectual Property rights, I will not divulge any information that would even remotely disclose the work I do for my company.

I will only document dos and donts that I observe when I am involved in the different phases of software development including requirements gathering, design, implementation and maintenance phases.

I will include concrete examples not connected in any way with the work I do for my company.

With this disclaimer I begin to document the things that I have learned as I build up my career.

posted by Kishore Sainath @ 7:22 AM 1 comments