SQL tooling, the ranking

When you need to get up and running quickly with your database, the tooling becomes very important. When developing jOOQ and adding integrations for new databases, I really love those ones that provide me with simple ways to create new databases, schemata, users, roles, grants, whatever is needed, using simple dialogs where I can click next next next. After all, we’re in the 21st century, and I don’t want to configure my software with punchcards anymore.

Database tooling categories

So with jOOQ development, I’ve seen a fair share of databases and their toolings. I’d like to divide them into three categories. Please note, that this division is subjective, from the point of view of jOOQ development. With most of these databases, I have no productive experience (except Oracle and MySQL). Things may change drastically when you go into production. So here are the categories:

The “all-you-can-wish-for” ones

These friends of mine ship with excellent tooling already integrated into their standard deliverable for free. It is easy to start the tooling and use it right away, without any configuration. The tooling is actually an intuitive rich client and I don’t have to read thousands of manual pages and google all around, or pay extra license fees to get the add-on. This category contains (in alphabetical order):

  • CUBRID with its Eclipse-RCP based CUBRID Manager. This is a very nice tool for a newcomer.
  • DB2 with its Eclipse-RCP based IBM Data Studio. IBM created Eclipse. It would’ve been a shame if they hadn’t created the Data Studio.
  • Postgres with pgAdminIII. Very very nice looking and fast.
  • SQL Server with its SQL Server Management Studio. This is probably the most complete of all. You can lose yourself in its myriads of properties and configuration popups.
  • Sybase SQL Anywhere and Sybase ASE, both share the same tooling called Sybase Central. It looks a bit out of date, but all administrative operations can be done easily.

The ones with sufficient tooling

These databases have tooling that is “sufficient”. This means that they ship with some integrated scripting-enabled console. Some of them are also generally popular, such that there exist free open source tools to administer those databases. This includes MySQL and Oracle. Here are to “OK” ones:

  • H2. Its web-based console is actually quite nice-looking. It features DHTML-based auto-completion and scripting. I can live with that.
  • Ingres. This dinosaur seems not to have upgraded UI components since Windows 95, but it works as good as it has to.
  • MySQL, with phpMyAdmin. This is a very nice, independent, open source PHP application for MySQL administration. You can install it easily along with MySQL using XAMPP, a nice Apache, MySQL, PHP, Perl distribution. Yes, I like installing complete things using the next next next pattern!
  • Oracle. It has sql*plus for scripting and there are many commercial and open source products with user interfaces. My favourite ones are Toad and Toad Extensions, a really nice and free Eclipse plugin. It is worth mentioning, that if you pay the extra license fee, you will have access to Oracle Enterprise Manager and other very very fancy tools. With money, you clearly can’t complain here.

The other ones…

Here, you’re back to loading *.sql files with DDL all along. No help from the vendors, here.

  • Derby. I’m not aware of any tooling. Correct me if I’m wrong
  • HSQLDB. Its integrated console can execute SQL, but it doesn’t provide syntax highlighting, checking, autocompletion, etc. I’m probably better off using SQuirreL SQL, or any other generic SQL tool.
  • SQLite. Good luck there! This database is really minimal!

Screenshots (ordered by database, alphabetically)

A very refreshing point of view about language design

Have fun:

http://joshondesign.com/2012/03/09/open-letter-language-designers

Which database are you using jOOQ with?

I’d like to “feel” the needs of my users. That’s why it’s interesting to know for which databases I should add features next. Which database are you using jOOQ with?

Blog has “moved”

The jOOQ blog has moved to a new domain:

https://blog.jooq.org/

The old wordpress domain will still be active, though

The Visitor Pattern Re-visited

The visitor pattern is one of the most overrated and yet underestimated patterns in object-oriented design. Overrated, because it is often chosen too quickly (possibly by an architecture astronaut), and then bloats an otherwise very simple design, when added in the wrong way. Underestimated, because it can be very powerful, if you don’t follow the school-book example. Let’s have a look in detail.

Problem #1: The naming

Its biggest flaw (in my opinion) is its naming itself. The “visitor” pattern. When we google it, we most likely find ourselves on the related Wikipedia article, showing funny images like this one:

Wikipedia Visitor Pattern example
Wikipedia Visitor Pattern example

Right. For the 98% of us thinking in wheels and engines and bodies in their every day software engineering work, this is immediately clear, because we know that the mechanic billing us several 1000$ for mending our car will first visit the wheels, then the engine, before eventually visiting our wallet and accepting our cash. If we’re unfortunate, he’ll also visit our wife while we’re at work, but she’ll never accept, that faithful soul.

But what about the 2% that solve other problems in their worklife? Like when we code complex data structures for E-Banking systems, stock exchange clients, intranet portals, etc. etc. Why not apply a visitor pattern to a truly hierarchical data structure? Like folders and files? (ok, not so complex after all)

OK, so we’ll “visit” folders and every folder is going to let its files “accept” a “visitor” and then we’ll let the visitor “visit” the files, too. What?? The car lets its parts accept the visitor and then let the visitor visit itself? The terms are misleading. They’re generic and good for the design pattern. But they will kill your real-life design, because no one thinks in terms of “accepting” and “visiting”, when in fact, you read/write/delete/modify your file system.

Problem #2: The polymorphism

This is the part that causes even more headache than the naming, when applied to the wrong situation. Why on earth does the visitor know everyone else? Why does the visitor need a method for every involved element in the hierarchy? Polymorphism and encapsulation claim that the implementation should be hidden behind an API. The API (of our data structure) probably implements the composite pattern in some way, i.e. its parts inherit from a common interface. OK, of course, a wheel is not a car, neither is my wife a mechanic. But when we take the folder/file structure, aren’t they all java.util.File objects?

Understanding the problem

The actual problem is not the naming and horrible API verbosity of visiting code, but the mis-understanding of the pattern. It’s not a pattern that is best suited for visiting large and complex data structures with lots of objects of different types. It’s the pattern that is best suited for visiting simple data structures with few different types, but visiting them with hundreds of visitors. Take files and folders. That’s a simple data structure. You have two types. One can contain the other, both share some properties. Various visitors could be:

  • CalculateSizeVisitor
  • FindOldestFileVisitor
  • DeleteAllVisitor
  • FindFilesByContentVisitor
  • ScanForVirusesVisitor
  • … you name it

I still dislike the naming, but the pattern works perfectly in this paradigm.

So when is the visitor pattern “wrong”?

I’d like to give the jOOQ QueryPart structure as an example. There are a great many of them, modelling various SQL query constructs, allowing jOOQ to build and execute SQL queries of arbitrary complexity. Let’s name a few examples:

  • Condition
    • CombinedCondition
    • NotCondition
    • InCondition
    • BetweenCondition
  • Field
    • TableField
    • Function
    • AggregateFunction
    • BindValue
  • FieldList

There are many more. Each one of them must be able to perform two actions: render SQL and bind variables. That would make two visitors each one knowing more than… 40-50 types…? Maybe in the faraway future, jOOQ queries will be able to render JPQL or some other query type. That would make 3 visitors against 40-50 types. Clearly, here, the classic visitor pattern is a bad choice. But I still want to “visit” the QueryParts, delegating rendering and binding to lower levels of abstraction.

How to implement this, then?

It’s simple: Stick with the composite pattern! It allows you to add some API elements to your data structure, that everyone has to implement.

So by intuition, step 1 would be this

interface QueryPart {
  // Let the QueryPart return its SQL
  String getSQL();

  // Let the QueryPart bind variables to a prepared
  // statement, given the next bind index, returning
  // the last bind index
  int bind(PreparedStatement statement, int nextIndex);
}

With this API, we can easily abstract a SQL query and delegate the responsibilities to lower-level artefacts. A BetweenCondition for instance. It takes care of correctly ordering the parts of a [field] BETWEEN [lower] AND [upper] condition, rendering syntactically correct SQL, delegating parts of the tasks to its child-QueryParts:

class BetweenCondition {
  Field field;
  Field lower;
  Field upper;

  public String getSQL() {
    return field.getSQL() + " between " +
           lower.getSQL() + " and " +
           upper.getSQL();
  }

  public int bind(PreparedStatement statement, int nextIndex) {
    int result = nextIndex;

    result = field.bind(statement, result);
    result = lower.bind(statement, result);
    result = upper.bind(statement, result);

    return result;
  }
}

Whereas BindValue on the other hand, would mainly take care of variable binding

class BindValue {
  Object value;

  public String getSQL() {
    return "?";
  }

  public int bind(PreparedStatement statement, int nextIndex) {
    statement.setObject(nextIndex, value);
    return nextIndex + 1;
  }
}

Combined, we can now easily create conditions of this form: ? BETWEEN ? AND ?. When more QueryParts are implemented, we could also imagine things like MY_TABLE.MY_FIELD BETWEEN ? AND (SELECT ? FROM DUAL), when appropriate Field implementations are available. That’s what makes the composite pattern so powerful, a common API and many components encapsulating behaviour, delegating parts of the behaviour to sub-components.

Step 2 takes care of API evolution

The composite pattern that we’ve seen so far is pretty intuitive, and yet very powerful. But sooner or later, we will need more parameters, as we find out that we want to pass state from parent QueryParts to their children. For instance, we want to be able to inline some bind values for some clauses. Maybe, some SQL dialects do not allow bind values in the BETWEEN clause. How to handle that with the current API? Extend it, adding a “boolean inline” parameter? No! That’s one of the reasons why the visitor pattern was invented. To keep the API of the composite structure elements simple (they only have to implement “accept”). But in this case, much better than implementing a true visitor pattern is to replace parameters by a “context”:

interface QueryPart {
  // The QueryPart now renders its SQL to the context
  void toSQL(RenderContext context);

  // The QueryPart now binds its variables to the context
  void bind(BindContext context);
}

The above contexts would contain properties like these (setters and render methods return the context itself, to allow for method chaining):

interface RenderContext {
  // Whether we're inlining bind variables
  boolean inline();
  RenderContext inline(boolean inline);

  // Whether fields should be rendered as a field declaration
  // (as opposed to a field reference). This is used for aliased fields
  boolean declareFields();
  RenderContext declareFields(boolean declare);

  // Whether tables should be rendered as a table declaration
  // (as opposed to a table reference). This is used for aliased tables
  boolean declareTables();
  RenderContext declareTables(boolean declare);

  // Whether we should cast bind variables
  boolean cast();

  // Render methods
  RenderContext sql(String sql);
  RenderContext sql(char sql);
  RenderContext keyword(String keyword);
  RenderContext literal(String literal);

  // The context's "visit" method
  RenderContext sql(QueryPart sql);
}

The same goes for the BindContext. As you can see, this API is quite extensible, new properties can be added, other common means of rendering SQL can be added, too. But the BetweenCondition does not have to surrender its encapsulated knowledge about how to render its SQL, and whether bind variables are allowed or not. It’ll keep that knowledge to itself:

class BetweenCondition {
  Field field;
  Field lower;
  Field upper;

  // The QueryPart now renders its SQL to the context
  public void toSQL(RenderContext context) {
    context.sql(field).keyword(" between ")
           .sql(lower).keyword(" and ")
           .sql(upper);
  }

  // The QueryPart now binds its variables to the context
  public void bind(BindContext context) {
    context.bind(field).bind(lower).bind(upper);
  }
}

Whereas BindValue on the other hand, would mainly take care of variable binding

class BindValue {
  Object value;

  public void toSQL(RenderContext context) {
    context.sql("?");
  }

  public void bind(BindContext context) {
    context.statement().setObject(context.nextIndex(), value);
  }
}

Conclusion: Name it Context-Pattern, not Visitor-Pattern

Be careful when jumping quickly to the visitor pattern. In many many cases, you’re going to bloat your design, making it utterly unreadable und difficult to debug. Here are the rules to remember, summed up:

  1. If you have many many visitors and a relatively simple data structure (few types), the visitor pattern is probably OK.
  2. If you have many many types and a relatively small set of visitors (few behaviours), the visitor pattern is overkill, stick with the composite pattern
  3. To allow for simple API evolution, design your composite objects to have methods taking a single context parameter.
  4. All of a sudden, you will find yourself with an “almost-visitor” pattern again, where context=visitor, “visit” and “accept”=”your proprietary method names”

The “Context Pattern” is at the same time intuitive like the “Composite Pattern”, and powerful as the “Visitor Pattern”, combining the best of both worlds.