Paging with different DBs

Sometimes you cannot or do not want to use an object-relational mapping tool. When not using an OR-mapper like Hibernate or Oracle Toplink you have to deal with database specifics. One common case especially for web applications is limiting the result set to a number of items that fit nicely on a web. You then often want to allow the users to navigate between these “pages” of items aka “paging”.

This type of functionality became part of SQL only as of SQL2008 in the following form:
SELECT * FROM t WHERE ... ORDER BY c OFFSET start_row FETCH count ONLY

Since most popular database management systems (DBMSes) do not yet implement this syntax you have to implement paging in propriatory ways.

My experience with an Oracle DBMS and the frustrating and comparatively long time it took to find the correct™ solution inspired me to write this post. Now I want to present you the syntax for some widely used DBMSes which we encounter frequently in our projects.

• MySQL, H2 and PostgreSQL (< 8.4 which will also implement the SQL2008 standard) use the same syntax:
  SELECT * FROM t WHERE ... ORDER BY c LIMIT count OFFSET start
• Oracle is where the fun begins. There is actually no easy and correct way of doing this. So you will end up with a mess like:
  SELECT col1 FROM (SELECT col1, ROWNUM r FROM (SELECT col1 FROM table ORDER BY col1)) WHERE r BETWEEN start AND end

• DB2 AFAIK uses the syntax proposed in SQL2008 but correct me if I am wrong because we do not yet work with DB2 databases.
• As we did not need paging with MS SQLServer as of now I did not bother to look for a solution yet. Hints are very welcome.

With all solutions the ORDER BY clause is critical because SQL does not guarantee the order of the returned rows.

Wikipedia delivers some additional and special case information but does not really explain the general, real world case the specific DBMSes.

I hope that I raised some awareness about database specifics and perhaps saved you some time trying to find a solution the problem using your favorite DBMS.

Visualizations with Flare/Prefuse

Recently we were in need of visualizing lots of data in networks. We started with a Javascript based version using RaphaelJS. RaphaelJS uses SVG/VML as the underlying graphics API. Drawing lots of nodes and edges in different shapes and colors worked like a breeze. But we quickly got performance problems when animating the transitions between different states of the network. So we had to look to an alternative technology which is performant enough and pragmatic to use. Reading a lot about Flex and its promising animation capabilities we gave it a try.
Shortly after we stumbled upon Flare/Prefuse and the stunning demos convinced us. It is very easy to use and gives remarkable results in a short time. We integrated it into our web app but suddenly the visualizations weren’t drawn upon startup. Everything went fine when the visualization was ready before the Flex app was fully completed but when the data was visualized after that the edges and nodes weren’t shown. Debugging our app and reading the Flex Sprite API wasn’t much helpful. But one comment in a superclass of all the Flare sprites solved out problem:

Internally, the DirtySprite class maintains a static list of all "dirty" sprites, and redraws each sprite in the list when a Event.RENDER event is issued. Typically, this process is performed automatically. In a few cases, erratic behavior has been observed due to a Flash Player bug that results in RENDER events not being properly issued. As a fallback, the static renderDirty() method can be invoked to manually force each dirty sprite to be redrawn.

These few cases were the default behaviour of our app, so invoking renderDirty solved all our drawing issues. I found no blog entry or hint in the docs and the demos run perfectly since all data is there before the app is shown. So the lesson here is:

• retest your assumptions (we thought the Flare sprites were all DataSprites and the next superclass is the Flex API Sprite and forgot about the DirtySprite)
• besides the demos and tutorials/docs read the API docs carefully, often there are implementation/technology specific hints

Software Craftsman Project Priority Survey

There is an upcoming and very promising book title written by Dave Hoover and Adewale Oshineye called “Apprenticeship Patterns: Guidance For The Aspiring Software Craftsman”.  It will cover all the basic rules you’ll need to become a Software Craftsman. This is a rather new term to describe professional software developers, eventually leading to the Software Craftsmanship Manifesto. The Manifesto itself reads like an addition to the Agile Manifesto:

As aspiring Software Craftsmen we are raising the bar of professional software development by practicing it and helping others learn the craft. Through this work we have come to value:

• Not only working software, but also well-crafted software
• Not only responding to change, but also steadily adding value
• Not only individuals and interactions, but also a community of professionals
• Not only customer collaboration,but also productive partnerships

That is, in pursuit of the items on the left we have found the items on the right to be indispensable.

© 2009, the undersigned. this statement may be freely copied in any form, but only in its entirety through this notice.

A very good question

When i read the blog of “Apprenticeship Patterns“, i noticed a very good question about project priorities:

Rank the following 3 project attributes in order of importance and explain why.

• Test Coverage
• Timely Delivery
• Code Quality

This question really got me hooked, because there is no single valid answer, only personal statements about values.

An informal survey

I’m in the lucky position of meeting a lot of senior developers and a great number of software engineering students. So I instantly decided to perform a survey on this question and watch out for emerging answer patterns.

I gave each project attribute an unique letter, C for “Test Coverage”, D for “Timely Delivery” and Q for “Code Quality”. There are six possible answers, here are their rates in the survey (when 58 persons gave their answers):

• CDQ: 7 percent
• CQD: 9 percent
• DCQ: 5 percent
• DQC: 7 percent
• QCD: 41 percent
• QDC: 31 percent

The vast majority of developers stated Code Quality as their highest goal. This isn’t very surprising to me, as most developers take pride in writing high quality code.

Comparing the answers

But what about the answers of only senior developers? Lets have a look at the numbers without student answers:

• CDQ: 7 percent
• CQD: 14 percent
• DCQ: 7 percent
• DQC: 14 percent
• QCD: 21 percent
• QDC: 36 percent

The big pattern still applies: Code Quality first. It’s amazing to see the other attributes gaining importance, though. To me, that’s a sign that code-centric thinking is one pattern of apprenticeship.

What’s not in the numbers

When i held the survey, the relevant group of people was gathered together, so a discussion of the results arose every time.  But the discussions followed different patterns:

• The teams (of senior developers) gave very distinct answers while working on the same project. The answers were driven by personal conviction rather than project necessities.
• The courses (of students) gave more similar answers while having a wide variety of backgrounds. The answers were mostly explained with current project necessities (like security-critical systems as reason for Test Coverage being most important).

When I have to compare the two groups, I tend to say that younger developers are more driven by extrinsic demands while more experienced developers act on their own internal values.

Our duty as Software Craftsman

In conclusion, I see a duty for experienced developers: to share their experience. Leading a discussion about “Team Values” at your current project is the least you can do. Helping others to develop their own set of internal values, even if it isn’t yours, seems crucial to me.

The upcoming “Apprenticeship Patterns” book and the brand new “97 Things Every Software Architect Should Know” are perfect starting points for this.

Make friends with your compiler

Suppose you are a C++ programmer on a project and you have the best intentions to write really good code. The one fellow that you better make friends with is your compiler. He will support you in your efforts whenever he can. Unless you don’t let him. One sure way to reject his help is to switch off all compiler warnings. I know it should be well-known by now that compiling at high warning levels is something to do always and anytime but it seems that many people just don’t do it.
Taking g++ as example, high warning levels do not mean just having “-Wall” switched on. Even if its name suggests otherwise, “-Wall” is just the minimum there. If you just spend like 5 minutes of so to look at the man page of g++ you find many many more helpful and reasonable -W… switches. For example (g++-4.3.2):


-Wctor-dtor-privacy: Warn when a class seems unusable because all the constructors or destructors in that class are private, and it has neither friends nor public static member functions.


Cool stuff! Let’s what else is there:


-Woverloaded-virtual: Warn when a function declaration hides virtual functions from a base class. Example:

class Base
{
public:
virtual void myFunction();
};

class Subclass : public Base
{
public:
void myFunction() const;
};

I would certainly like to be warned about that, but may be that’s just me.


-Weffc++: Warn about violations of the following style guidelines from Scott Meyers’ Effective C++ book


This is certainly one of the most “effective” weapons in your fight for bug-free software. It causes the compiler to spit out warnings about code issues that can lead to subtle and hard-to-find bugs but also about things that are considered good programming practice.

So suppose you read the g++ man page, you enabled all warning switches additional to “-Wall” that seem reasonable to you and you plan to compile your project cleanly without warnings. Unfortunately, chances are quite high that your effort will be instantly thwarted by third-party libraries that your code includes. Because even if your code is clean and shiny, header files of third-pary library may be not. Especially with “-Weffc++” this could result in a massive amount of warning messages in code that you have no control of. Even with the otherwise powerful, easy-to-use and supposedly mature Qt library you run into that problem. Compiling code that includes Qt headers like <QComboBox>with “-Weffc++” switched on is just unbearable.

Leaving aside the fact that my confidence in Qt has declined considerably since I noticed this, the question remains what to do ignore the shortcomings of other peoples code. With GCC you can for example add pragmas around offending includes as desribed here. Or you can create wrapper headers for third-party includes that contain


#pragma GCC system_header


AFAIK Microsoft’s compilers have similar pragmas:


#pragma warning(push, 1)
#include
#include
#pragma warning(pop)


Warning switches are a powerful tool to increase your code quality. You just have to use them!

Drawing your background with Javascript

Having complex backgrounds (like radial gradients) or even dynamic backgrounds in a web page is not an easy task using only images. Javascript helps here and the SVG/VML support in modern browsers makes drawing a reality. Looking around in the Javascript library world there are some which have APIs for crossbrowser drawing or charting. But if you are looking for a small and easy to learn on you have to search. We found raphaeljs, a project which started as a 20%er at Atlassian. It has an API which is close to SVG but also supports non SVG browsers like IE. How to make animations and complex drawings is easy to learn. The examples give a hint of what is possible.
We needed a radial gradient to have a smooth background, looking at the docs and the SVG spec, it was a four liner:

var Paper = Raphael("canvas", width, height);
var b = Paper.circle(width / 2, height / 2, Math.max(width, height));
var gradient = {type: "radial", dots: [{color: "#FFFFFF"}, {color: "#D5D5D5"}], vector: [0, 0, 0, "100%"]};
b.attr({"gradient": gradient});