With DB2 9.7’s introduction of modules and the ability to create an alias on a module, you now have the ability to program to a function or procedure’s interface rather than its implementation. Why would you want to do this? In today’s post, I will go over reasons to program to an interface and show you how to do it using modules and aliases.
Object-oriented programming languages realize their power when programmers program to an interface rather than an implementation, as it makes programs written in them more flexible. Such languages allow you to change or generalize your designs without significantly rewriting your code. However, the technique of programming to an interface is not limited to object-oriented languages. Anyone who has ever created a view on a table has some experience with the same concept applied to data.
Programming to an interface makes testing your code easier too, especially unit testing. In unit testing, you test components in isolation. If a component-under-test has explicit dependencies on other components, it can be difficult to test in isolation. If, however, these dependencies are on the interfaces of other components rather than on their implementations, you can replace the implementations with fakes or stubs to achieve isolation.
More Testable Designs
Say you have a program that reads data from a sensor and performs some calculation on it. You want to unit test that the calculation produces correct results for known values and boundary conditions. You also want this test to be repeatable so that you can rerun the test whenever you make changes to the calculation and know that you haven’t introduced a regression. The problem is that the sensor gets its data from an environment you can’t control. The system-under-test, that is, your calculation function, is coupled to sensor output that varies from one minute to the next, making it difficult or impossible to write an isolated unit test.
The solution is to program not to the sensor implementation but to its interface. Let’s say the software that handles reading the sensor takes the form of a user-defined function in SQL PL. Let’s pretend we have a sensor that gives us random data from the environment and let’s represent it with a function that returns a random number between 0 and 1000. Let’s say that the system-under-test is a function that reads the sensor six times and computes the average from those six readings.
<!--start_raw-->SET SERVEROUTPUT ON@
CREATE OR REPLACE FUNCTION read_sensor() SPECIFIC read_sensor
RETURNS INTEGER
BEGIN
DECLARE seed INTEGER;
SET seed = (INTEGER(SECOND(CURRENT TIMESTAMP)) * 1000000) + MICROSECOND(CURRENT TIMESTAMP);
RETURN 1000 * RAND(MOD(seed,2147483647));
END@
CREATE OR REPLACE FUNCTION avg_sensor_readings()
RETURNS DOUBLE
BEGIN
DECLARE sum DOUBLE DEFAULT 0;
DECLARE count INTEGER DEFAULT 0;
WHILE count < 6 DO
set sum = sum + read_sensor();
set count = count + 1;
END WHILE;
RETURN sum / 6.0;
END@
CALL DBMS_OUTPUT.PUT_LINE('avg of 6 sensor readings = ' || DECIMAL(avg_sensor_readings(),9,2))@<!--end_raw-->
If we run the above program we get something like the following:
<!--start_raw-->DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. Return Status = 0 avg of 6 sensor readings = 523.00 <!--end_raw-->
If we run it again, we get a different average. This is unacceptable for testing our avg_sensor_readings function. We would like to replace the read_sensor function with a stub that consistently returns the numbers we need to test our averaging function. However, because we have coupled the avg_sensor_readings function to the sensor’s implementation, we cannot simply replace the read_sensor with a stub when running our tests and still be able to use the real read_sensor function when running in production.
How can we decouple these two components? If the read_sensor function were instead a table, we could create an alias on the table and modify the avg_sensor_readings function to query the alias instead of the table, but there is no such thing as an alias on a function. Fortunately, DB2 9.7 provides the solution with the introduction of modules, which can be given an alias. First, let’s move our read_sensor function into a module called sensors.
<!--start_raw-->CREATE OR REPLACE MODULE sensors@ ALTER MODULE sensors PUBLISH FUNCTION read_sensor() SPECIFIC read_sensor RETURNS INTEGER BEGIN DECLARE seed INTEGER; SET seed = (INTEGER(SECOND(CURRENT TIMESTAMP)) * 1000000) + MICROSECOND(CURRENT TIMESTAMP); RETURN 1000 * RAND(MOD(seed,2147483647)); END@<!--end_raw-->
Now, we just need to create an alias for our sensors module and modify avg_sensor_readings to program to the interface (the alias):
<!--start_raw-->CREATE OR REPLACE ALIAS sensors_interface FOR MODULE sensors@
CREATE OR REPLACE FUNCTION avg_sensor_readings()
RETURNS DOUBLE
BEGIN
DECLARE sum DOUBLE DEFAULT 0;
DECLARE count INTEGER DEFAULT 0;
WHILE count < 6 DO
set sum = sum + sensors_interface.read_sensor();
set count = count + 1;
END WHILE;
RETURN sum / 6.0;
END@<!--end_raw-->
Now, if we create a stub module, we can have our avg_sensor_readings function use the stub when it is being unit-tested and use the real function when in production. Here is the stub with the alias changed to point to the stub function instead of the original function:
<!--start_raw-->CREATE OR REPLACE MODULE sensor_stubs@
ALTER MODULE sensor_stubs PUBLISH
FUNCTION read_sensor() SPECIFIC read_sensor
RETURNS INTEGER
RETURN 5@
CREATE OR REPLACE ALIAS sensors_interface FOR MODULE sensor_stubs@
CALL DBMS_OUTPUT.PUT_LINE('avg of 6 sensor readings (Expected: 5.00, Actual: ' || DECIMAL(avg_sensor_readings(),9,2) || ')')@<!--end_raw-->
This has the following output:
<!--start_raw-->DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. Return Status = 0 avg of 6 sensor readings (Expected: 5.00, Actual: 5.00)<!--end_raw-->
Now we always get the same average whenever we run the test. We can rely on this test to detect regressions in our avg_sensor_readings function. We can switch back and forth between the stub and real function without ever having to modify the avg_sensor_readings function.