Design Patterns in PL/SQL – Interface Injection for even looser coupling

In a previous article, I discussed Dependency Injection (aka Inversion of Control and the Hollywood principle) and more specifically an implementation of Dependency Injection in PL/SQL. In that we have looked at injecting values. Our objective was to remove the dependencies of our components on hard-coded values or external objects. The responsibility for getting hold of the required values has been taken out of the component.

We can take dependency injection to an even higher level. To allow for loosely coupled, component based development or assembly, we can start injecting entire components. Injecting a component means providing one component with access to another component that will perform tasks for it. Both components are independent of each other. We can just as easily switch the injected component for another one that can perform the same task.

In this article, we will take a look at how we could implement this Interface Injection in PL/SQL. As before, it is primarily meant to trigger further brainstorm and discussion, about applying best practices from other technology arenas to PL/SQL development. As before, your feedback is very welcome.

Say our package has built in hooks for Logging, Sending Emails and Security. It allows us to plug in our own implementations for each of these functions. If we do not provide our own implementation, it either falls back on its own default implementation or it does not perform Logging, Email Sending and Security Enforcement at all.

Above I have used the terms provide and plugin. If we substitute them with inject we stumble across another type of Dependency Injection, called Interface Injection. With interface injection we make implementations of a specific interface available to the injectee. Let’s take a closer look at an example.

Our package HRM_SALARY_RULES has indicated that it allows a logger component to be injected through its set_logger(p_logger in varchar2) procedure. It states that the value that is injected should refer to a procedure – stand-alone or packaged – that takes the following input parameters: p_log_level, p_log_origin and p_log_message. If a logger has been injected, the package will write logging to it. If no logger has been set, it will perform no logging at all. Note that the HRM_SALARY_RULES package knows nothing whatsoever about the implementation of the logger. It just calls it.

Design Patterns in PL/SQL - Interface Injection for even looser coupling interfaceInj

create or replace package HRM_SALARY_RULES is
  /* Logger interface:
     p_log_level in varchar2
   , p_log_origin in varchar2
   , p_log_message in varchar2
   */
  procedure set_logger (p_logger in varchar2);


  procedure set_salessalarycap( p_salessalarycap in number);

  function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
  return boolean;

end HRM_SALARY_RULES;

And the package body:

CREATE OR REPLACE PACKAGE BODY "HRM_SALARY_RULES" is

  g_salessalarycap number:= 4;
  g_logger         varchar2(120);

  procedure set_logger (p_logger in varchar2)
  is
  begin
    g_logger := p_logger;
  end set_logger;

  procedure log
  ( p_log_message in varchar2
  , p_log_level   in varchar2 default 'debug'
  , p_log_origin  in varchar2 default 'HRM_SALARY_RULES'
  ) is
  begin
    if g_logger is not null
    then
      execute immediate
      'begin
         '||g_logger
         ||' ( p_log_level => :1
             , p_log_origin => :2
             , p_log_message => :3
             );
          end;'
       using in p_log_level
       ,     in p_log_origin
       ,     in p_log_message
       ;
    end if;
  end log;

  procedure set_salessalarycap( p_salessalarycap in number)
  is
  begin
    g_salessalarycap:= p_salessalarycap;
  end set_salessalarycap;

  function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
  return boolean
  is
    l_start_time number:= dbms_utility.get_time;
    l_end_time   number;
    l_result     boolean:= true;
  begin
    log('verify_salary_raise for job='||p_job||' and old salary '||p_old_salary||' changing into new salary '||p_new_salary, p_log_level=> 'info');
    -- among a lot of other tests...
    if instr(lower(p_job),'sales') > 0
    then
      log( '- salarycap = '||g_salessalarycap);
      if (p_new_salary - p_old_salary)/p_old_salary > g_salessalarycap/100
      then
        log('- FALSE: this change in salary for this job is not in accordance with the rules', p_log_level=> 'info');
        l_result:= false;
      end if;
      log('- TRUE: this change in salary for this job is in accordance with the rules');
    end if;
    return l_result;
  end verify_salary_raise;

end HRM_SALARY_RULES;

My very simple Logger implementation – note we could create a far more advanced one that hooks into log4plsql or writes to an external file, table or database pipe. For the purpose of this article, a simple Logger will do:

create or replace package LOGGER is

  -- Created : 3/8/2006 2:02:12 PM
  -- Purpose : provides basic logging functionality; might hook into Log4PLSQL in the future

  procedure log_message
  ( p_log_level in varchar2
  , p_log_origin in varchar2
  , p_log_message in varchar2
  );

  procedure set_loglevel_threshold
  ( p_log_level in varchar2
  );

  procedure set_file_writer
  ( p_file_writer in varchar2
  );
end LOGGER;
create or replace package body LOGGER is

 g_loglevel_threshold varchar2(20):= 'info'; -- any message with a lower level than this threshold is not written
 g_file_writer        varchar2(120);

 type hashmap is table of number(1) index by varchar2(15);
 g_log_levels hashmap;

 procedure log_message
  ( p_log_level in varchar2
  , p_log_origin in varchar2
  , p_log_message in varchar2
  ) is
  begin
    if g_log_levels( p_log_level) >= g_log_levels(g_loglevel_threshold)
    then
      dbms_output.put_line( substr(p_log_origin||': '||p_log_message, 1,255));
    end if;
  end log_message;

  procedure set_loglevel_threshold
  ( p_log_level in varchar2
  ) is
  begin
    g_loglevel_threshold:= p_log_level;
  end set_loglevel_threshold;

  procedure set_file_writer
  ( p_file_writer in varchar2
  ) is
  begin
    g_file_writer:= p_file_writer;
  end set_file_writer;

begin
  g_log_levels('fine'):= 0;
  g_log_levels('debug'):= 1;
  g_log_levels('info'):= 2;
  g_log_levels('warning'):= 3;
  g_log_levels('error'):= 4;
  g_log_levels('fatal'):= 5;
end LOGGER;

Now before we invoke the HRM_SALARY_RULES package for verifying salary raises, we should inject a logger component. If we do not, the validations will run okay, but we will not see any logging.

A simple test:

declare
   procedure test_job_sal_raise
   ( p_job in varchar2
   , p_old_sal in number
   , p_new_sal in number
   ) is
   begin
     if hrm_salary_rules.verify_salary_raise(p_job => p_job,p_old_salary => p_old_sal ,p_new_salary => p_new_sal)
     then
       dbms_output.put_line( 'Salaryraise for '||p_job||'  from '||p_old_sal||' to '||p_new_sal||' is approved.');
     else
       dbms_output.put_line( 'Salaryraise for '||p_job||'  from '||p_old_sal||' to '||p_new_sal||' is NOT approved.');
     end if;
   end;

 begin
   hrm_salary_rules.set_logger('LOGGER.LOG_MESSAGE');
   logger.set_loglevel_threshold('info');
   test_job_sal_raise('SALESMAN', 1000, 1020);
   test_job_sal_raise('SALESMAN', 1000, 1200);
   dbms_output.put_line('========= changed loglevel to debug ============================');
   logger.set_loglevel_threshold('debug');
   test_job_sal_raise('SALESMAN', 1000, 1020);
   test_job_sal_raise('SALESMAN', 1000, 1200);
end;

yields the following output:

Design Patterns in PL/SQL - Interface Injection for even looser coupling interfaceInj4

The name Interface Injection is derived from the Interface construct in the Java programming language. In Java, an interface is a specification in its own right, with its own source file and runtime existence. An interface is like a contract that classes should follow in order to qualify for certain manipulations, such as interface injection. The Java Compiler will verify whether a class that claims to implement an interface really sticks to the contract. As a result, the strongly typed interface injection – strongly typed because the setter used for injecting the interface will only accept an object based on a class that implements the interface – is guaranteed at runtime. In PL/SQL – even though the Object Relational Extension supports Methods, Inheritance – we do not have the concept of an interface. We can only at runtime find out whether the injected component is actually one that the injected package can properly invoke.

Note that we can interchange implementations of the logger interface without any impact on the HRM_SALARY_RULES package. Just inject the new logger, even when previously another implementation has been used.

Design Patterns in PL/SQL - Interface Injection for even looser coupling interfaceInj2

Using the Adapter Pattern to enable Interface Injection

The package has also indicated that it is ready to accept an emailer. Through the set_emailer(p_emailer in varchar2) procedure, we can specify a procedure that the package can call for sending an email. The procedure should have the signature:

p_sender     in varchar2

, p_recipient  in varchar2

, p_header     in varchar2

, p_message    in varchar2

, p_importance in varchar2

If we inject an emailer, the HRM_SALARY_RULES package will use it to send emails at appropriate times. The package already contains the logic for deciding when to send an email, to whom and with what contents. It does not have or want to know how to send an email, other than by call an injected emailer with the specified interface.

We happen to have an emailer available: EMAILER.send_email. Unfortunately, it has the wrong signature!

procedure send_email
 ( p_sender    in varchar2
 , p_addressee in varchar2
 , p_subject   in varchar2
 , p_message   in varchar2
 );

Now we have the opportunity to apply another design pattern: the adapter pattern. See for example: http://www.exciton.cs.rice.edu/JavaResources/DesignPatterns/adapter.htm or http://en.wikipedia.org/wiki/Adapter_pattern . The emailer we injected should abide by the interface specified by the HRM_SALARY_RULES package. So we create an adapter that makes the emailer we have look like that interface.

 

Design Patterns in PL/SQL - Interface Injection for even looser coupling interfaceInj3

create or replace package EMAILER_ADAPTER is
  -- Purpose : implements the emailer interface using the available mail sender package

 procedure send_email
 ( p_sender     in varchar2
 , p_recipient  in varchar2
 , p_header     in varchar2
 , p_message    in varchar2
 , p_importance in varchar2
 );

end EMAILER_ADAPTER;

create or replace package body EMAILER_ADAPTER is

 procedure send_email
 ( p_sender     in varchar2
 , p_recipient  in varchar2
 , p_header     in varchar2
 , p_message    in varchar2
 , p_importance in varchar2
 ) is
 begin
   emailer.send_email
   ( p_sender => p_sender
   , p_addressee => p_recipient
   , p_subject => p_header
   , p_message => p_message
   );
 end send_email;

end EMAILER_ADAPTER;

And the following test demonstrates how to inject both interfaces into the HRM_SALARY_RULES  package:

 

declare
   procedure test_job_sal_raise
   ( p_job in varchar2
   , p_old_sal in number
   , p_new_sal in number
   ) is
   begin
     if hrm_salary_rules.verify_salary_raise(p_job => p_job,p_old_salary => p_old_sal ,p_new_salary => p_new_sal)
     then
       dbms_output.put_line( 'Salaryraise for '||p_job||'  from '||p_old_sal||' to '||p_new_sal||' is approved.');
     else
       dbms_output.put_line( 'Salaryraise for '||p_job||'  from '||p_old_sal||' to '||p_new_sal||' is NOT approved.');
     end if;
   end;

 begin
   hrm_salary_rules.set_logger('LOGGER.LOG_MESSAGE');
   hrm_salary_rules.set_emailer('EMAILER_ADAPTER.SEND_EMAIL');
   dependency_injector.inject_dependencies;
   test_job_sal_raise('SALESMAN', 1000, 1020);
   test_job_sal_raise('SALESMAN', 1000, 1200);
end; 

 

In addition to logging as implemented in our own LOGGER the reusable component HRM_SALARY_RULES now also sends emails through our own EMAILER component. Interface Injection has allowed for all of this.

Resources

Download all sources for this article: InterfaceInjection.zip

The initial article on Dependency Injection in PL/SQL: Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code


 

4 Comments

  1. JulesLt November 17, 2009
  2. JulesLt November 17, 2009
  3. Justin DeWind May 13, 2009
  4. APC March 13, 2006