#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.

Continue reading

#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.

1Everything easyLOWBE_KIND80

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

Continue reading

#100CodeExamples – PL/SQL BOOLEAN to INT

One of the main annoyances of Oracle databases for me has been its lack of a BOOLEAN SQL-Type.

Yes, BOOLEAN exists in Oracle, but only inside of PL/SQL, not in the SQL context, which means that you can’t select a boolean type and also can’t retreive it via JDBC.

The simplest way to overcome this is to have an easy way of converting BOOLEAN to 1/0 INTEGER – and there are several possibilities to do this:

Continue reading

#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:

Continue reading

#100CodeExamples – A challenge to learn and teach: Primary/Secondary Lookup

A while ago, Gregory Brown brought up the idea to do a “100 Code Examples” challenge:

I liked the idea very much due to several reasons:

  • The challenge will probably help me to track/be aware of what I learned
  • Breaking down what I learned into small samples will reinforce the knowledge
  • It will hopefully also exercise my skills to teach and share knowledge

I won’t put myself under pressure because that’s not the point of the challenge, but if I can manage I will post 1-2 examples on my blog here so the 100 Code Examples should be done in roughly a year.

The topics of the examples will most likely be related to my current work, so mainly SQL, PL/SQL and I will also try to add several examples around utPLSQL.
Every example will come with a very brief explanation of the goal and the critical snippet – hopefully annotated in a way that it’s understandable (I’d really appreciate your feedback on the latter part!).
If it’s possible I’ll also share a link to LiveSQL or a different environment where the example can be run immediately (won’t work with utPLSQL examples so easily).

Example: Primary/Secondary Lookup

We often have Primary/Secondary situations and sometimes we need an easy way to get all related entries based of a single entry’s ID.

In my Star Wars example, the Deathstar operates on several power nodes, some of them have secondary nodes in case the primary power node is no longer working.
We want to create a view one can query with the ID of any power node, no matter whether a primary or secondary, and get the full list of related power nodes as result.

Continue reading

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?

Continue reading

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?
Continue reading

Strongholds of confidence: self-testing your database

Software-Testing has always been a very important topic in professional development because software – unlike for example buildings – changes a lot from the first line of code to release and from there during its whole lifetime. Due to that constant change and its complexity every honest developer will have to accept someday, that there is no software without bugs.

The rise of agile methods, ideas like continuous integration and the urge to have more frequent releases led to an even stronger focus on modern testing-strategies (interesting blog post about correlation of release frequency and agile practices – especially including automated testing).

Unfortunately there is some kind of “holy war” about some programming techniques around testing (I will write a bit more on that in the last chapter of this blog post), but nonetheless the need for automated self-testing (be it system-, integration- or unit-testing) in todays software development is real and uncontroversial among the experts.¬†Therefore I will use the general term of “self-testing” in this article, concentrating on the very basic idea and trying to give an introduction on how it can be done in database projects. In my impression still many of today’s database projects don’t even know about the basics of self-testing, so let me try to show you the benefits without nit-picking about specific techniques.

Continue reading

There is no clean (database) development without Version Control

I was surprised when I did a bit of research for this current blog article and learned that SCCS, the first system for Version Control, was invented in 1972. That was 45 years ago. I wasn’t even born.

But even today, after such a long time, there are projects – especially database projects – which are not under version control. I myself remember several projects I worked on (or even was responsible for, shame on me) which were not under Version Control at all or at least the database part wasn’t. So why is this and what can we do against it?

Continue reading

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?

Continue reading