Synopsis: Use SQL and a bit (sorry) of bit comparison to create a “Feature Framework”
Is there a good way to add what amounts to nothing more than a HACK to your application? Probably not, but there are ways to mitigate the damage.
I was recently given the task of modifying a well-designed client-server application to make it do things contrary to its nature…for only one client. Essentially, I needed to add a dozen pieces of functionality for one client’s usage without interrupting hundreds of other current users. In some cases, the changes were diametrically opposed to the application’s current work flow. Of course, it needed to be done yesterday.
Faced with this issue, I needed to find a good/fast way to add “silly” functionality to a robust product while minimizing bugs and not creating a maintenance nightmare.
Option 1: Start over.
Obviously, management was not going to approve this suggestion, but to do the job properly would require a substantial redesign of the system’s workflow and navigation.
Option 2: Spaghetti Code.
Add lots of brutal conditional hacks throughout the entire code base? This option might seem the most expedient from a product delivery or resource standpoint, but any developer who has had to maintain the result of this approach can attest to the dangers involved.
Option 3: ??
In classical fashion, it was suggested that for the sake of expedience, I should “just add a bunch of ‘if’ statements” and deploy. Taking pity on any developer (i.e. me) who would have to support this system in the future, I decided that a better solution was needed.
Disclaimer: It should be noted that there are certainly other (probably better) ways to implement new pieces of functionality to an application than my solution, but it is/was a decent solution for the problem given the requested timeframe. It has occurred to me that my solution is really just a glorified version of Option 2 above.
I figured that, at the very least, I would need self-contained, self-documenting changes that should have minimal impact on existing functionality…and at best, I could get a mechanism that would allow me to add future pieces of functionality as needed.
My solution was to build in a “Feature Framework” using a little SQL and some bit comparison.
Below is a simplified version of a “company” table used for the the application.
CREATE TABLE [dbo].[company](
[co_id] [int] IDENTITY(1,1) NOT NULL,
[co_name] [varchar](50) NOT NULL,
[settings] [int] NULL,
CONSTRAINT [PK_company] PRIMARY KEY CLUSTERED
(
[co_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In order to “assign” features to a specific company, we will also need a “settings” table:
CREATE TABLE [dbo].[co_settings](
[setting_id] [int] IDENTITY(1,1) NOT NULL,
[setting_name] [varchar](100) NOT NULL,
[setting_bits] [int] NOT NULL,
[setting_desc] [varchar](500) NULL
) ON [PRIMARY]
…And, of course, a function to check the settings:
Create FUNCTION [dbo].[fn_check_setting] (@setting_name varchar(100), @co_id int)
RETURNS bit
AS
Begin
Declare
@setting_bit_value int = 0,
@co_settings_bit_value int = 0,
@co_has_setting bit = 0
--Get the bit value for the setting
Select
@setting_bit_value = setting_bits
from
co_settings
Where
setting_name = @setting_name
--Get the company specific bit sum
Select
@co_settings_bit_value = settings
From
company
where
co_id = @co_id
If(@co_settings_bit_value & @setting_bit_value > 0)
BEGIN
SET @co_has_setting = 1
END
Return (@co_has_setting)
End
…Throw in some sample data:
company settings data
company data
It might not look like much, but this little bit of SQL allowed me to add portions of functionality for use by individual clients without compromising other client’s expected functionionality. Basically, using the data and SQL above, I used bit comparison to enable/disable features instead of adding company specific hacks.
There are a couple of different ways that the data can be used.
To generate a comma delimited list of features using SQL:
Declare @co_id int = 1,
@co_settings varchar(255) = ''
--Loop through possible settings and return company specific list separated by ,
Declare @settings table (setting varchar(100), has_setting bit)
Insert into @settings
Select setting_name, dbo.fn_check_setting(setting_name, @co_id) as has_setting
from co_settings
Select @co_settings = @co_settings + ', ' + setting from @settings where has_setting = 1
The @co_settings variable will contain something like this: “Hack1, Hack3, Hack4”. I found this to be useful when I needed to check whether or not soomething needed to be enabled/disabled. I just passed the concatenated string around and checked to see if, for example, “Hack1” existed. If so, enable Hack1…Voila.
If I need to check for a single specific feature in, say, a Stored Proc:
Declare @do_special_hack bit = dbo.fn_check_setting('SpecialHack', @co_id)
Optionally, you can just return the company.settings bit sum value to your code and do the bit comparison there.
The main benefit to my approach to the problem is that pieces of functionality can be given to one or more clients on a need basis without modifying code. So, the next time a client wants a particular feature developed for another client, it only requires a single record in the DB. Additionally, adding new features is relatively painless.
The biggest drawback is that every time you need to add a new feature to the framework, you still have to add additional code to the application that checks for the existence of the feature setting. That having been said, it is still better than the alternative.
Res Technicum 