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[*];
  (…)
end;

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[*];
begin
    return Account extent.select(
        (a : Account) : Boolean {return a.balance < 0}
    );
end;

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[*];
begin
    return (Account extent.select(
          (a : Account) : Boolean { return a.balance >= minBalance }
    ).collect(
          (a : Account) : Customer { return a->AccountOwner->owner }
    ) as Customer);
end;

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?