At David’s company, management wasn’t a big fan of the whole check-in/re-compile/re-deploy process. They felt that it had too many steps and took far too long to complete. Their organization needed to adapt to software changes - especially bug fixes - much faster. So, to address this problem, they thoroughly analyzed the development process and carefully tweaked it for efficiency. I’m just kidding. Actually, their solution was to pioneer a new methodology called “SQL Sentences”.

Management figured that, since a large portion of their application dealt with saving and retrieving data from the database, then a lot of the bugs must be a result of bad SQL queries. Likely, it was things like a LEFT JOIN instead of an INNER JOIN, or a > instead of a >=. And, since their application already used a database, they figured - you know where this is going - why not store all of the SQL queries in the database?

The idea was handed off to the lead developer, who then worked long and hard to transform it into something far, far worse: the “SQL Sentences” library.

  • every single SQL query was to be stored in a table called sql_sentences
  • in order to not “hard code” the purpose of a SQL query in its identifier, SQL sentences were given 4-digit hex identifiers - 0x00F9, 0xA004, etc. - instead of GET_ALL_CUSTOMERS
  • each sentence has privileges associated with it; the GetSQLSentence() method made several other queries to make sure the logged-on user could execute the query
  • to appease the developers who were against storing SQL in the database, a configuration value called “UseHardcodedSQL” was added

All told, these changes required developers to modifiy their code to look like this... 

string sql;
// @SQLSentence=0x02f0
if ((bool)(Configuration.GetValue("UseHardcodedSQL")))
{
    sql = "SELECT cust_name, cust_phone, cust_addr " +
          "  FROM customers WHERE cust_type='Active'";
}
else
{
    sql = DataConnector.GetSQLSentence(0x02f0);
}

... and, of course, add that same query to the sql_sentences table. And, should there ever be a change to the query, maintain it in both the code and the gigantic script created the sql_sentences table and the production database.

Though David wasn't around when these changes were implemented, when he came onboard and heard the story he asked the Lead Developer if he'd ever heard of a Stored Procedure before.

“Yeah,” he replied, “but I’ve heard they’re a big pain to maintain.”

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!