ewernli

Database independent SQL: a checklist

Pure SQL data layers tend to disappear in favour of O/R mapper which embed they own query language. The languages of today’s O/R mapper (e.g: Hibernate, JPA, etc.) is still close to the underlying target SQL syntax. Actually, some keywords in Hibernate will be taken as-is form HQL to SQL which means that using an O/R mapper does not provide 100% database independence necessary. There are also frequently a few native queries here and there to perform data-intensive operation that are easily realized with pure SQL, and do not match well with the object approach. Therefore it is still relevant to think about SQL-92 convention and to keep the SQL and HQL the more independent as possible. Below is a checklist I wrote a few years back when I was involved in the migration of a project from Informix to Oracle.

Outer join & inner join
Review the syntax of your joins.
AS keyword
There is no need of a special keyword to alias a column.
Function (SQL92 or not)
Try to avoid using database function. If it’s absolutely necessary prefer the SQL92 subset and make your code easily portable.
Operator ==
The SQL92 standard uses = for equality.
Case type: num vs char
Complex SQL using case statement can be rewritten to be SQL92 compliant
lock columm name (reserved words)
Each RDMS has a list of reserved keywords. Try to avoid such name to make your SQL portable.
No usage of *
Do never use * to return rows with select. The order of your column is part of the physical database model and your code should work with the logical database model. Chances are, that the column order with change over time due to migration, or extension of the database model.

Keep your desktop clean: a checklist

I found a post-it on my desk this morning that was actually there since several months (if not year). It’s a short checklist with questions to answer before deciding whether I will trash or retain a document.

I tend to retain too many documents (electronic and paper) that consumes space and are useless — I will anyway never read them. This short post-it helps me in my cleaning activity. It helps me take some distance with the material and its importance.

is it hard to find the document again?
is the document important regarding legal aspects?
is the document up to date?
is the document currently/frequently used?

Then if all answers are “no”, trash it!

It’s amazing how many documents can actually be trashed according to this checklist. Free desktop again!

Introduction to Reliable Distributed Programming

This book discusses distributed algorithms in the context of reliable application development. The algorithms are described intuitively and presented in pseudo-code as well. Even though this is an academic book, it is not too theoretical and is easy to follow. Theoretical complexity of the algorithms was for instance omitted on purpose.

The book presents the programming abstraction incrementally. It starts first with a recap of time abstractions and then builds a stack of algorithms in the following way: network and link abstractions, broadcast abstractions, shared memory abstractions, and consensus abstractions. The last chapter is about programming models used in real systems, for instance group communication or state machine replication.

If you are eager to learn how stuff work under the hood or want to have solid foundations to address reliable distributed programming, I recommend this book. I enjoyed reading it, but would have appreciated a bit more coverage of programming models in the last chapter.

97 Things Every Software Architect Should Know

This book is a collection of 97 articles, written by various authors, about software engineering and architecture. The articles are short (no more than 2 pages) and easy to read. Each one is focused on one principle.

The book is not a definitive receipe on how to conduct a project and be sucessful. It’s rather a set of – more or less generic – advices to think about: did I had similar experience? Should I remember this advice next time I start a project? Is this advice relevant in my position? etc.

What is nice about the book is that it covers a wide spectrum of problems faced when building a piece of software. There are not only reflexions about technology, but also communication issues, risk management, managing people motivation, taste and opinions, the necessity of having a vision and a leader, etc. What matters utlimately is the delivery of a solution that addresses the real customer problems.

My top 10 favorites are:

“Perfect” is the Enemy of “Good Enough”

Great software is not built, it is grown

If there is only one solution, get a second opinion

Make sure the simple stuff is simple

Shortcuts now are paid back with interest later

Prefer principles, axioms and analogies to opinion and taste

Simplify essential complexity; diminish accidental complexity

Your system is legacy, design for it.

Simplicity before generality, use before reuse

Everything will ultimately fail