Archive | TDD RSS feed for this section

Dependency Injection pattern in database development

24 Jun

To get yourself familiar with dependency injection
pattern please check Wikipedia link here:
http://en.wikipedia.org/wiki/Dependency_injection

The primary database focus is to persist data and guaranty data integrity… well, at list we can say it about many databases. As a result database development evolved in its own procedural-way, and it is hard to argue against such a way or to change it, because in most cased database designs have tendency to harden, solidify, and resist a change after they get built. Besides, who want a change when over the years masses of applications got built on that-old-table. Just try to change it and wait for something to fail…
Whatever reason you have for a change try the Dependency Injection pattern. This pattern injects the “depended-on” code to the destination code automatically by knowing the requirement of the destination code and altering an expected outcome of the destination code. Let’s take very popular BOO and FOO templates for our example:

CREATE PROCEDURE dbo.sp_BOO_manager
AS
BEGIN
   IF dbo.FOO() < dbo.FOOTheshold()
      EXEC BOO 'Have to do BOO logic';
      ELSE MOO 'Have to do MOO logic, instead';
END

Here is how to introduce dependency injection into our code:

  • Create following two schemas: INTERFACE, PROD_IMPLEMENTATION
CREATE SCHEMA INTERFACE;
CREATE SCHEMA PROD_IMPLEMENTATION;
  • Move actual functions and procedures with business logic to the PROD_IMPLEMENTATION schema, like this one:
CREATE PROCEDURE PROD_IMPLEMENTATION.BOO
@Message VARCHAR(50)
AS
BEGIN
    PRINT 'Very complicated proprietary logic';
END
  • Create synonyms in the INTERFACE schema that will point to the functions and stored procedures in the PROD_IMPLEMENTATION schema. Note that synonyms can have the same names, because they are located in the different schemas than actual business logic. For instance:
CREATE SYNONYM INTERFACE.BOO
   FOR PROD_IMPLEMENTATION.BOO;
CREATE SYNONYM INTERFACE.FOO
   FOR PROD_IMPLEMENTATION.FOO;
CREATE SYNONYM INTERFACE.FOOTheshold
   FOR PROD_IMPLEMENTATION.FOOTheshold;
  • Then change dbo.sp_BOO_manager stored procedure to use synonyms from the INTERFACE schema instead of objects themselves. Here is what you will get:
CREATE PROCEDURE dbo.sp_BOO_manage
AS
BEGIN
   IF INTERFACE.FOO() < INTERFACE.FOOTheshold()
      EXEC INTERFACE.BOO 'Have to do BOO logic';
      ELSE INTERFACE.MOO 'Have to do MOO logic';
END

Now our sample code does not bounds to the actual business logic directly, instead it calls an abstracted logic through an interface (which we can override when needed), allowing the calling process to inject desired functionality.
Let’s do it…
Make a new object somewhere in another database:

CREATE PROCEDURE SOMEOTHERDB.MOCK_OBJECTS.BOO
@Message VARCHAR(50)
AS
BEGIN
   DECLARE @OverrideName VARCHAR(100)
     = 'SOMEOTHERDB.MOCK_OBJECTS.BOO'
   PRINT 'Very simple log message';
   INSERT INTO TEST_MESSAGES
      (OverrideName, [Message], EventDate)
      VALUES (@OverrideName,@TmpMessage, GETDATE());
END

Then (on your development server) change BOO synonym to point to the mocked object:

DROP SYNONYM INTERFACE.BOO;
CREATE SYNONYM INTERFACE.BOO
   FOR SOMEOTHERDB.MOCK_OBJECTS.BOO;

After this change, you can call business logic and verify that expected message got logged in to a test table.

--call logic
EXEC dbo.sp_BOO_manager;
--assert result
EXEC DBTD_ASSERT_COLUMN_HAVE_VALUE
   @v_TableName = 'TEST_MESSAGES',
   @v_ColumnName = 'OverrideName',
   @v_Value = 'SOMEOTHERDB.MOCK_OBJECTS.BOO',
   @v_ValueDataType = 'VARCHAR(100)',
   @v_UserMessage = 'BOO functionality should run';

There are a few ways to introduce dependency injection pattern in databases, we have chosen SYNONYMS, because of the following benefit:

  • Synonyms are very simple database objects;
  • Synonym does not change actual business logic they represents;
  • Synonym provides less invasive way to perform injection;
  • When creating synonym developer only need the name of the synonym (already known from consumer side) and the name of the object it represents (which is also known by developer from a provider side );
  • Locating synonyms and business logic in the different schemas allows synonym to have the same name as the object it abstracts, this preserve an existing naming convention used by development team;
  • Synonym can be created on the following objects:
    CLR Stored Procedures, Table-valued Functions, Scalar Functions, and Aggregate Functions, SQL Stored Procedure, Replication-filter-procedures, Extended Stored Procedures, SQL Scalar Functions, SQL Table-valued Functions, SQL Inline-table-valued Function, Views, User-defined Tables, includes local and global temporary tables

Well, here you go, now you have one more tool in your development toolbox. Use injection wisely, make sure you have basic infrastructure tests to go with it, and don’t forget about security.

Global Day of Code Retreat: A Great Way to Spend a Saturday

10 Dec

On Saturday I attended the second annual Global Day of Code Retreat. It was quite possibly the best developer day/conference I have attended. This is not to diminish some great conferences and code camps like MADExpo and Nova Code Camp, but this experience is different in an extremely important way.

Most conferences and code camps contain sessions aimed at keeping developers up on the latest and greatest technologies. A code retreat focuses solely on your development skills. No frameworks, no shiny new tools, its just you, a partner, your favorite IDE, test runner and some simple business rules.

For 45 minutes, you and a partner use TDD and pair programming to implement the rules for Conway’s game of life. At the end of 45 minutes, you get a 15 minute break. Oh, and you delete the code you just spent the last 45 minutes writing.

“What?” you exclaim, “Delete my code?”

Yes. Code Retreat is not about the solution, but the journey. Most of us spend day after day executing, but many of us spend very little time practicing our craft. So after the first round of programming we knew the problem better, the next 45 minutes should be a breeze.

“This time your methods may contain only four statements.”

With each 45 minute session, the facilitator presented us with different constraints. This prevented us from regurgitating the same code over and over, but more importantly it reminded me how many ways we developers have to solve the same problem.

“This time you may not use any conditional statements.”

These constraints were challenging and every green test was exhilarating.

The Global Day of Code Retreat reminded me that writing code is fun, pair programming is awesome (its been a year since I paired) and that I need to stop and take some time to practice the art of writing code, not just watching the latest TekPub video.

If you live in the D.C. area and want to spend more time practicing your craft, join the local Software Craftsmanship User Group, or better yet, facilitate your own Code Retreat.