Is Open Session in View an AntiPattern?

A friend of mine pointed me to a willingly provocative article that describes Open Session in View as an AntiPattern. After sharing some thoughts with him, I've decided to post them here.

I agree on the N +1 queries problem, but unless (and until) you have devastating impacts on performances I would not worry about it, applying a YAGNI approach. On the other hand, if a view requires a lot of fetches maybe DTOs come in useful (and I would suggest Dozer). This also semplifies the objects exposed, thus hiding from the "high" layers of the application all the complexities that exist within the domain.

Personally, one of the reasons for which I found Struts 1 frustrating - yet much better than the proprietary framework we were using at the time - was the need for form beans, which lead to tons of duplications (no Dozer yet, a long time ago in a galaxy far far away we only had some Commons Beanutils) which is normally bad. OK, forget the "normally" part.

As a general rule I think that exposing domain objects can be accepted if and only if they are true domain objects, not beans with a bunch of setters and getters and transaction scripts in disguise, otherwise you could bypass many of the application logics and end up with an unmanageable mess.

And, talking about layers leaking, I would also like to quote a couple of sentences from the Domain-Driven Design Using Naked Objects book which I suggest as a very interesting and useful reading:

It takes real skill to ensure the correct separation of concerns between these layers, if indeed you can get an agreement to what these concerns really are. Even with the best intentions, it's all too easy for custom-written layers to blur the boundaries and put (for example) validation in the user interface layer when it should belong to the domain layer. At the other extreme, it's quite possible for custom layers to distort or completely subvert the underlying domain model.

The leaking of the persistence layer is not good per se, but if it does not become a problem you can live with it. As always, it depends on circumstances: why should a spend an incredible amount of resources (which means time and money) to obtain perfect isolation if I don't need it?

In general I think Open Session in View to be a good solution to manage database connections, but it would be even better leverage this strategy with join fetch queries for the most important relations. After all, when writing a service or a facade we know what objects will be needed: this does not mean that the domain or the controller should depend on the presentation layer (they must not!), but pretend that we don't know anything about it is just a waste of time. So, for example, if our facade has a result factory this could be a nice place to eagerly load all objects we know the presentation layer will need; if the view still needs more objects the open session can provide them on the fly: in this way we can optimize the most important parts and live with the rest.

The datediff function

Next week we shall roll-out an application which provided some very interesting insights for us; it will be the core of the renewed application portfolio for one of our customers, and after about one year of hard work we all think everyone is going to be very satisfied.

The problem is that the database we are going to migrate contains very dirty data, e.g. it contains data about events that last thousands of days instead of the typical maximum, which is assumed to be five, so we're driving a hard bargain to force the customer to fix all (at least, most of) the anomalies.

A practical way to point out the errors to the customer is the datediff function:

SELECT event_id, start_date, end_date
FROM events
WHERE datediff(d, start_date, end_date) + 1 > 5

The +1 is needed as passing the third parameter equal to the second one, as in events that only last one day, would yield a 0, and I think it is clearer to leave the lower limit (5, in this case) clearly visible. Of course also

WHERE datediff(d, start_date, end_date) > 4

or

WHERE datediff(d, start_date, end_date) >= 5

or even

WHERE datediff(d, start_date, end_date) > 5 - 1

would work, but IMHO they are not as clear as the first one.

File import in SQLServer 2005

After some tries, and despite of all the ultra-mega-super-lightning-integrated environment provided by Microsoft, I still think that the fastest way to bulk load a csv file into a SQLServer 2005 table is to use plain old Transact-SQL. Assuming you have a csv file with the same structure of an existing table, all you have to to do is write a few lines in your query window and fire an F5:

BULK INSERT your_table
from 'absolute/path/to/your/file'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

Obviously, you could also use different terminators, but all you need to do is to alter the specified parameter.

How to deal with collations

Sadly, the need to inspect a database to find orphaned records is not uncommon. One typical query you might write would probably look like this one:

select *
from table_one
where my_key not in
(
  select my_key
  from table_two
)

Every now and then the keys you need to compare are strings with different collations, so a query like the one above would yield error messages. To resolve the problem you can cast one of the field:

select *
from table_one
where my_key not in
(
  select cast (my_key as ${type}) collate ${collation}
  from table_two
)

Obviously, ${type} and ${collation} are just placeholders. If you are using SQL Server you can execute the Transact-SQL sp_help procedure to get the informations you need for the cast:

exec sp_help table_one

Of course, numeric keys would solve the problem at its roots. But... have you ever heard of natural keys and legacy databases?

A flexibility contest

The introduction in the Hibernate documentation reports that

A relational database is more flexible than a network programming language, so it doesn't need anything like a navigation direction - data can be viewed and retrieved in any possibile way.

As this is quite a statement, I stopped to consider it a while.

In a programming language you have to consider navigability, so if you have - as in the example - a Person class and an Event class and you want to get all events associated to a person you'll have to ask the person with something like person.getEvents(), but if you also want to get all persons associated to an event you'll have to ask the event with event.getPersons(), i.e. you have to provide accessors to both classes. And both classes hold a reference to a collection of their companion class. Moreover, when you add a person to an event you have to make sure that also the event gets modified, and vice versa.

In an RDBMS you can compose all the queries you like, starting from persons or events; the only thing you'll need is a well defined relation between the tables, which tipically translates into a bridge table. You don't even need to enforce referential integrity, though this is the path to the dark side (or is it?), as long as everybody knows how the tables should be connected. So when you add a person to an event you only have to insert a row in the bridge table.

In this sense databases are more flexible than programming languages. You can write

select *
from person p
left outer join person_event pe
on p.id = pe.person_id
left outer join events e
on pe.event_id = e.id

as well as

select *
from event e
left outer join person_event pe
on e.id = pe.event_id
left outer join person p
on pe.person_id = p.id

but this comes at a cost. You need keys. And you need indexes to manage them (even if this relates to the phisical aspect rather than to the logical one). And a bridge table to manage the many-to-many relationship, which doesn't belong in the domain at all but is a pure fabrication used to represent the model. This clutters things a bit, even if we're so used to it that we don't even notice.

The Java language gives a much clearer representation of the domain (at least, the signatures of the method do):

public class Person {
  // stuff
  public List getEvents() {...}
  public void addEvent(Event event) {...}
}

public class Event {
  //stuff
  public List getPersons() {...}
  public void addPerson(Person person) {...}
}

The language shields you from how the model is represented (that's where the rigidity is hidden), and it is more natural to use.

Databases can use views to simplify data retrieval, but they don't work for inserts and updates. That's where stored procedures come in handy, but... aren't they written in a programming language? Should the latter be considered part if the database or as an external means to get a higher abstraction over SQL (which in turn is a language of its own)? How far should we push this?

My conclusion? Everyone should get their act together to make sure that the quality of what they build is the highest that they can get, either when designing a good database schema or when implementing a bidirectional association. The problem is that "the highest they can get" can sometimes be a low standard, so everyone should always try to improve, no matter how high the standard. I think this rule applies not only to programmers in their own field of competence, but to men in the broader sense.

Collected Java Practices

Here's yet another site with some collected practices for Java Programming. I just surfed over some practices without diving into details, but I decided to read the "data is king" practice, as it clearly opposes to the "database is a detail" saying.

I pretty much agree with everything but I feel a shiver down my spine when I read the following sentence:

the data is always validated before being entered into the database (this cannot be stressed enough)

What's wrong with it? well, nothing at all, on the contrary! but it gets creepy when you put it with another sentence:

applications should never assume that they "own" the data. Databases are independent processes, and are built to interact with many clients applications, not just one

That forces me to meditate. IMHO, the first sentence is a perfect example of a too unused good theory, the second one describes a too diffused bad practice.


If data must always be validated (and it should!), and databases should interact with many client applications, each application must ensure the same data validation process happens. That means duplication, and (IMHO) it smells.

One could easily agree that moving the data validation process into the database would suffice to solve the problem, for example using stored procedures, triggers and rules. But then, the database should support these means, which leaves out, for example, flat files. Moreover, switching to a different database would be very difficult.

So what? as always, a very consolidated habit of communicating between peers can come to help: as it is a fact that many applications write to the very same database, everybody who is involved should ensure that the overlapping areas are as small as possible and that they deal with data in the same way, either with "database programming" or "people synchronization", thus reducing duplications and error sprouting.

Another solution can be one application exposing services used by the other ones, but sometimes this is not desirable or applicable.

That said, I want to stress the "no silver bullet" concept: "data is king" surely is a good advice but it cannot be taken as an absolute truth but as a general rule. The same applies, for example, to GRASPs: when you have to decide which class should create a new instance of another class, should you follow the Creator pattern or the Expert pattern? it depends, but the important thing here is that they can lead to very different results, each of which would have its proper rights to stand as a correct solution. It is our responsibility to derive our decisions on the basis of all our knowledge and experience - there's no magic involved, even if sometimes a design comes out naturally, just because "you know it's right": this lucky feeling comes from experience and a very long try-and-fail process. Only keep in mind that what you do can, most of the times, be further improved :-) so be humble and ask... you might be surprised!

Star schema

OLAP (On-Line Analytical Processing) tools are among the most common front end systems for data warehouses; they allow dynamical and multidimensional analysis to be performed against a huge amount of records in order to produce a small set of data which can be used as a dashboard for business process management by the so-called the "knowledge workers".
There are two widespread approaches to OLAP implementation: ROLAP (Relational OLAP) and MOLAP (Multidimensional OLAP). Ok, there is also an hybrid solution, called... you guessed, HOLAP (Hybrid OLAP).

Why should people use the relational model to implement a multidimensional model? There are many reasons, the most important being the diffusion of advanced RDBMSs and the expertise of IT people. Moreover, ROLAP systems don't have a "sparse data" problem, thus being far more scalable than MOLAP implementations. Unluckily, the relational - and bidimensional - model, in which we find attributes, relations and integrity constraints, has a reduced expressivity when it comes to describe the multidimensional model, in which we find facts, measures, attributes, dimensions and hierarchies. That's why we have to find a workaround, which leads us to the (notorious) star schema.

The star schema consists of one (or more) fact table(s), which represents facts, referencing many dimension tables, which represent the dimensions of analysis. Fact tables typically have a lot of columns, and newbies almost always smell the rat of a very bad use of the relational model where they should see a very good compromise instead.
One of the reasons behind the star schema is the very poor performance shown by RDBMS when they have to aggregate a huge amount of records belonging to many different tables, thus involving many expensive join operations: denormalization can then improve performance at the cost of the increased disk space required. Another way to improve performance is redundancy: you materialize derived tables (views) based on the most used aggregations to speed up typical analysis. In addition, ROLAP implementations often use surrogate keys, another feature that make newbies shrug.

The star schema can have some variations, as the snowflake schema, obtained decomposing one of more dimension tables eliminating transitive functional dependencies contained in the tables. Dimension tables which keys are imported in the fact table are called primary while the others are called... why, secondary, what else?

Database on a diet

Today we stripped (and archived, of course) old data from one of our production databases... bringing it from 110GB to about 15GB. Let's say it was about time.