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.