SQL queries in UML

I strongly believe queries are an essential part of a domain model. As such, in our quest to have (UML) models that can fully (yet abstractly) describe object models for the common enterprise applications, we cannot leave out first class support for queries.

But how do you do queries in UML? The obvious answer seems to be OCL, but that is not the approach I am taking as OCL and UML have serious interoperability/duplication issues. Instead, I took the  middleweight extension approach.

First, we model a protocol for manipulating collections of objects (showing only a subset here):

class Collection specializes Basic
  operation includes(object : T) : Boolean;
  operation isEmpty() : Boolean;
  operation size() : Integer;
  operation exists(predicate : {(:T) : Boolean}) : Boolean;
  operation any(predicate : {(:T) : Boolean}) : T;
  operation select(filter : {(:T) : Boolean}) : T[*];
  operation collect(mapping : {(:T) : any}) : any[*];
  operation forEach(predicate : {(:T)});
  operation union(another : T[*]) : T[*];

That protocol is available against any collection of objects, which in UML can be obtained by navigating an association, reading an attribute, invoking an operation, obtaining the extent of a class (remember Smalltalk’s allInstances), anything where the resulting value has multiplicity greater than one.

Note most of the operations in the Collection protocol take blocks/closures as arguments. Closures are used in this context to define the filtering criterion for a select, or the mapping function for a collect.

For instance, for obtaining all accounts that currently do not have sufficient funds, this method would do it:

static operation findNSFAccounts() : Account[*];
    return Account extent.select(
        (a : Account) : Boolean {return a.balance < 0}

Note the starting collection is the extent of the Account class. That is very similar to what is done in the context of query languages for object-oriented databases, such as OQL or JDOQL. We then filter the class extent by selecting only those accounts that have a negative balance, by passing a block to the select operation.

When mapping that behavior to SQL, we could end up with a query like this:

select _account_.* from Account _account_ where _account_.balance < 0

Another example: we want to obtain all customers with a balance above a given amount, let’s say, to send them a letter to thank them for their business. The following method specifies that logic:

static operation findBestCustomers(minBalance : Real) : Customer[*];
    return (Account extent.select(
          (a : Account) : Boolean { return a.balance >= minBalance }
          (a : Account) : Customer { return a->AccountOwner->owner }
    ) as Customer);

Note that we start off with the extent of Account class, filter it down to the accounts with good balance using select, and then map from that collection to a collection with the respective account owners by traversing an association using collect.

If that was going to be mapped to SQL, one possible mapping would be:

select _customer_.* from Account _account_
    inner join Customer _customer_
        on  _account_._accountID_ = _customer_._customerID_
    where _account_.balance >= ?

Much of this can be already modeled if you try it out with the TextUML Toolkit 1.2. But, you might ask, once you model that, what can you do with UML models containing queries like the ones shown here?

Since the models are complete (include structure and behavior), you can:

  1. Execute them. Imagine writing automated tests against your models, or letting your customer play with them before you actually start working on the implementation.
  2. Generate complete code. The generated code will include even your custom queries, not only those basic ones (findAll, findByPK) code generators can usually produce for you.

If you would like to see tools that support that vision, keep watching this blog.

So, what is your opinion?
Do you see value in being able to specify queries in your models? Is this the right direction? What would you do differently?

Email this to someoneShare on FacebookShare on LinkedInShare on Google+Tweet about this on Twitter

2 thoughts on “SQL queries in UML

  1. [...] JC – Have you thought in extending TextUML with some basic OCL support (i.e. mixing simple OCL constraints inline with the textual definition of the class diagram as a way to simplify the writing of basic OCL constraints) RC – There is some overlap between Action Semantics and OCL and at some point I was trying to see if it was possible to merge them or use both of them at the same time but a lot of effort is needed to bridge them. The corresponding metamodels do not work together that well. I wanted to allow using OCL expressions within Action Semantics specifications but I realised that would require changing/extending the metamodels. For instance, the concept of variable in OCL is not equivalent to the concept of variable in Action Semantics. Also, I was interested in the concept of OCL collections (and all operations defined to manipulate them) since because I needed that kind of support and Action Semantics has no direct support for them. In Action Semantics it is very convoluted to apply an operation on all instances of a class and things like this so as a workaround I ended up avoiding the Action Semantics support for building sets of objects and trying to mimic what you have in OCL by having some predefined classes in TextUML that represent collections and provide some operations for them (for more details on this check this blog post [...]

Comments are closed.