More Pitfalls of the MINUS comparison

When I did my presentation at APEX Connect, Erik van Roon explained another pitfall of my MINUS approach to compare the content of two views to me – something some people already tried to show me and failed (well, I failed getting the point).

Let’s start with the same situation as in the last example, a small list of Star Wars characters and the movie episodes in which they appear:

ID	Character	Episodes
1	Darth Vader	3, 4, 5, 6
2	Luke Skywalker	4, 5, 6, 7, 8
3	Rey	7, 8

Now let’s assume we are forced to do overwork by the sithlord in charge, it’s 3 am, we are terribly tired and create a new view but miss the group by statement:

Updating a Table with the Values of a Different One

I am working professionally with databases for over 15 years now and have a huge focus on Oracle – but I really keep forgetting how to update a table with values of a different one (this is one thing which is so much easier in SQL Server by the way).

Therefore let’s assume we have a table containing planets and one containing garrisons which are on these planets.

Garrison ID	Planet Name	Planet Faction
1	Korriban	imperium
2	Korriban	imperium
3	Dromund Kaas	imperium
4	Hoth	republic

We would now like to have a new column in the garrisons table which can contain a name.

alter table garrisons add name varchar2(300)

The imperial side now has a request to update all their garrisons with a name according to this schema: <PlanetName> (<Garrison ID>)

Pitfalls of the MINUS-comparison

My work includes a lot of database views because they work as a kind of public API in our case. Since I started talking about self-testing and utPLSQL, the MINUS-comparison has always had its place when assuring that a new, reconstructed view has the exact same content as the old one.

However, I did it wrong until recently when I discovered a major pitfall.

For I like to tell stories let’s start with a Star Wars scenario again: We have a table which contains all the Star Wars characters and another table which holds information in which of the “Episode”-movies they appeared.

#100CodeExamples – Constants in PL/SQL and SQL

We often deal with the same things in several places of our codebase, be it names, IDs, codes or something similiar. Problems arise when we write them slightly different, which we often don’t notice because it won’t create a compiler error.

Additionally, these things should often not be changeable once set, therefore most programming languages provide CONSTANTS.
PL/SQL does so, too, but to get these constants into SQL can be a bit tricky.

#100CodeExamples – Only one active protocol

Sometimes you have the situation that you store several different variants of an entity in the database, but you have to be absolutely sure there is only one of these variants active at a time.

Examples could be different configurations or color schemes you can choose from, the active financial year in accounting-related scenarios or a default entry.

There’s a similar thing in the deathstar, which runs on one of several protocols.

ID	LABEL	ALERT_LEVEL	DEFENSE_MODE	POWER_LEVEL
1	Everything easy	LOW	BE_KIND	80
2	Be careful	MEDIUM	BE_SUSPICIOUS	90
3	OMG the rebels!	VERY HIGH	SHOOT_FIRST_ASK_LATER	120

To make sure, there is only ever one protocol active, the database developers use simple tools most relational databases provide:

#100CodeExamples – SYSDATE vs. CURRENT_DATE

I ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.

I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.
After some investigation I found the problem.
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport_flights”-table:

BeckDesignRules for Database Developers: Reveals intention – Naming

In the previous post of this series I wrote about the importance of knowing your problem domain, understanding the requirements and defining the tests to be passed.

I am grateful for all the positive feedback I got, but a number of readers felt that it was too long and hard to absorb. So I am going to abandon my initial idea of writing one post per design rule.

By slicing my thoughts and interpretation of Beck’s design rules into smaller chunks and subtopics I also get the possibility to explore the rules more deeply, which I am looking forward to.

Today I dive into Kent Beck’s second rule for simple software design as expressed by Martin Fowler: “Reveals intention”.

That phrase is short, on point, and easy to remember. It is, however, worth taking a look at what the people involved in developing these rules wrote when they discussed the ideas on the original C2 wiki-page:

“The next most important thing [after passing the tests – ed. Note] is that the code is as easy to understand as possible, therefore we need to ensure that it expresses every idea that we need to express clearly”

— Jason Yip

It’s important to remember that we don’t write code for machines. If we did, we could (and should) write binary code. Since we write code to be understood and maintained by other people, we don’t, because we humans are not naturally able to get the meaning of huge blocks of 0 and 1.

Any code we write serves two purposes:

1. Make the machine behave in the way we want
2. Tell the reader what we expect that behaviour to be

While the first purpose seems obvious, we often forget about the second one, which is although equally important if we aim for low maintenance cost and long-term success.

So how can we make our code readable and understandable? What are the possibilities we have, particularly in database development?

BeckDesignRules for Database Developers Part 1: Passes the tests

Software design and software architecture are topics which are sometimes associated with distant, slightly mad geniuses, living in an ivory tower and regularly throwing UML diagrams to their ground crew of “normal” developers. They are seen as arcane knowledge which can only be absorbed by the most experienced, most talented “10x developers“, “rockstars” and “ninjas”.

From my experience, this point of view couldn’t be farther from reality. On the contrary I see software design and architecture as fundamental parts of any kind of development work. The moment we start writing code, we will make decisions and are creating software design, no matter if we are aware or not.
Of course there might be guidelines, there might be an architecture given by others, there might even be detailed API descriptions and an abstract design which paints the big picture, but in the end the little, daily decisions will have a huge impact on the result.

Understanding the rules of good software design is therefore a topic which affects developers of any skill and experience level.
Knowledge in that field will be extremely beneficial for every task related to software development.

Four Rules of Simple Design

Kent Beck, developer of Extreme Programming (XP) and Test-Driven Development (TDD), came up with four rules of simple software design in the late 1990s, which Martin Fowler expresses like this:

Martin gives a great, comprehensible description in his article about the “BeckDesignRules” which is also the source of the picture above.

I will try to take a deep dive into these rules, their spirit and how they can be practically done in database development. There is also much personal interpretation from my side and I’m not entirely sure whether Kent Beck would agree on all of my thoughts (of course I hope so).

I will split this up into several blog posts (you wouldn’t believe how hard it is anyway to keep my personal goal of one post per month) and today’s post will cover the first, probably most important and impactful of Beck’s design rules:

Passes the Tests

This one is clearly about Unit-Testing, Test-Driven Development and lots of green lights in your testing report, isn’t it? It means that good software design must adopt specific patterns from XP or other agile methodologies, right?

DRY SQL – Writing simple, maintainable queries

One of the strongest recommendations for clean and pragmatic development is the DRY-principle:

Don’t Repeat Yourself!

This principle is not only part of every guide about professional development but also the reason why we even have things like subroutines, config files and  – hello, database professional – data normalization.

Although normalization should be a well-known practice for database professionals, I caught myself writing the same SQL statements again and again, copy-pasting parts as sub-selects, use it in procedures and functions and ending up at having the same query logic in many different places.

We database people are very strict about redundant data in our database design – why aren’t we as strict about the code we write?