28 April 2016

Comparison Operators and Between in Relational Databases

Sometimes our techie-brains convert a concept clearly articulated in plain English into unnecessarily mathematically expressed code.

For example, "I want all records with an amount between 1000 and 1999" can become "x >= 1000 AND x <= 1999"

But modern RDBMS database systems give us an English equivalent that saves translating the concept "between" into "greater than or equal to this, and less than or equal to that."

It's the BETWEEN function, and it is the same across SQL Server, PostgreSQL, MySQL, and Oracle. Maybe others, too, but those are the ones that I have used in the past.

BETWEEN is of the form:
MyTestValue BETWEEN StartValue AND EndValue
It is an inclusive comparison, equivalent to using >= and <= So these generate the same results:
SELECT * FROM MyTable WHERE MyValue >= 100 AND MyValue <= 1000

What are the advantages of BETWEEN? It's a little less typing; it's more intuitive to understand, without losing any precision in the expression.
And it avoids angle-brackets. Which can sound trivial, but was an issue on a recent bug report I had to deal with. Here's the story:

26 April 2016

Using Java 8 to Refactor an Iteration over a Collection

On a recent project, I came across an ideal algorithm to (re-)write using Java 8's Streams API and Lambda expressions. Here is the original code, modified to protect the client:

public String extractAllToAccountsAsCSV(List<AccountPair> collectionOfPairedAccounts)
String result = "";
for (AccountPair pair : collectionOfPairedAccounts)
if (result.length() > 0)
result += ",";
result += String.valueOf(pair.getToAccount());
return result;

This simple algorithm iterates over a collection of AccountPairs, which are objects of a data structure that associates two accounts. Its goal is to produce a comma-separated String output of all values of one kind of account in the AccountPair, a CSV that the calling class will consume in some way.

The description sounds right in the wheelhouse of Java 8. It would not be a refactoring that alters the structure of the class, per se, but it does change the detailed design inside the public-facing API, makes it cleaner and more ready for parallelization.

19 April 2016

How to do a Case-Sensitive SQL Server Query

When selecting data in SQL Server, the WHERE-clause has, in my experience, ignored the case and done a case-insensitive comparison. For example, my Individuals table may have a mix of upper and lower cases, depending on how people entered their names:

SELECT firstname, lastname 
FROM Individuals
WHERE lastname = 'Timmins'

firstname lastname
--------- --------
Tellie timmins
Tommy TimMinS
Tubby Timmins

My query selected all four people with the last name "Timmins" without caring if it was all capitals, all lower case, or a mixture. I rely on that behavior all the time.

But once in a while, we need to do a case-sensitive query. How can we, for example, find Tommy TimMinS and correct it to Tommy Timmins?

11 April 2016

Testing-in-the-Trenches: Spin-off a New Class

Testing in the Trenches (TinT) is an occasional series based on my experiences promoting and coaching Unit Testing on real projects, ones where the team or management do not always embrace the philosophy or practices of Unit-Testing.

Technique: Spinning-off a New Class

Imagine you believe in the principles of automated unit testing. You want to write tests to prove the correctness of your code.
But imagine that you are working in a code base with pockets, sections, even whole packages of code that has no tests, possibly even code whose design is hostile to being put into a test harness.
Imagine that the enhancement or bug fix on your plate involves changing existing code that has no current test coverage, or not very much, or maybe some tests but they are poor quality. Maybe they have a lot of dependencies, or it takes a lot of work to set them up, or they do too many things on construction.
This imaginary scenario is very common on some projects.

Faced with such a scenario, as practitioners of the craft of unit-testing, we want to at the very least provide tests that cover our new code. We want to ensure that, when we add code for an enhancement or bug fix, our contribution is unit-tested.

One effective technique is to separate our new code into a new class. Like a TV show that spins-off from another one, we want to spin-off our new work into a new class, one that the original class will call for our new fix or feature. The idea is to put your changes into the new class and use it from the original class.

This testing technique has some immediate Advantages: