lagod

This is a me too post inspired by a post by BarneyB. It drives me nuts the number of weird APIs that exist to “abstract” SQL when SQL is already one of the most elegant and widely known DSL’s around.

OK, usually I’d just cheer from the sidelines/comments thread, but I might actually be able to add some value here by fleshing out Barney’s point about the Structured aspect of Structured Query Language. The natural home for Barney’s Lego pieces is in SQL views (unless you’re using an old version of MySQL, in which case – sorry). How to best organize views? I define these sorts of objects I might use in a query:

• A base table
• A view that embodies a business rule.
• A view that pulls some data for display

And the rules are these:

• Business rule views must return only primary keys
• Data views must not be re-used by other views

Plus a couple of supporting observations regarding performance. What reallly stumps the query optimizer (at least for SQL Server) is:

• A large number of joins in a single statement. The optimizer copes well with very deeply nested views, as long as each view is relatively small (3-4 joins).
• Text data – whether as join criteria, in WHERE clauses, or even just in the SELECT list

Time for a fully worked example. I won’t write out the DDL, but hopefully you can fill in the blanks. Let’s say we have an access control system with these entities:

Person
Group
GroupMember (joins Person with Group)
MembershipStatus (pending, denied or approved)

Just to add spice, we’re using soft deletes for the Person table, so we have a deleted flag as well. Our task is to write a query that will give us the email address of all the people whose membership has been approved.

First, we need to sort out the soft deletes:

create view vwActivePerson
as
select
  personID
from
  Person
where
  deleted = 0

Approved memberships look like this:

create view vwApprovedMemberships as
select
 personID,
 groupID
from
 GroupMembership
 inner join Status
   on GroupMembership.statusID = Status.statusID
where
 Status.code = "approved"

We use the text status code here so we have a human-readable query, but we limit its damage by only using it in this one view. Every other query that needs this concept simply joins to this view. So this is a business-rule view that defines the concept of an approved membership.

Finally, to pull our email addresses we do this:

create view vwData_approvedMembershipEmails
as
select
 Person.email
from
 Person
 inner join vwApprovedMemberships
   on Person.personID = vwApprovedMemberships.personID
 inner join vwActivePerson
   on Person.personID = vwActivePerson.personID

Ok, seeing as I’m having such fun with the ul tag, here’s a list of things to note about this query:

• There’s no WHERE clause. WHERE clauses embodying business rules often end up sprinkled around ad hoc query text, but we’ve avoided this by using business-rule views as filtering mechanisms. In SQL terms, I’m setting up a bunch of set intersections.
• Similarly, note that no fields from the business-rule views appear in the select list. There’s no reason they shouldn’t, but this does highlight the fact that those views are there for filtering, not for data extraction.
• The Person table is involved in this query in two places, once directly and once via the view. This is the typical tension between DRY and encapsulation, and in this case encapsulation has prevailed. However, I can reassure you that if your business rules return only foreign and primary keys, you can have dozens of them in a query without much impacting performance. You could have another view that filters for e.g. people with more than one denied group membership if that’s a useful concept. Maybe it’s a bit of a stretch, but I think of this as multiple inheritance for SQL.
• I’ve named this as a data view to remind programmers not to base other views on this one.

The no-reuse rule for data views has a couple of nice bonus side-effects. Because the view contains no intrinsic business logic all it is doing is marshalling the exact data needed for a specific widget or batch process. That means you can refactor these data views with gay abandon – if you delete the widget, just delete the view – and you can tune the data returned by the view for maximum efficiency without regard to generality.

Barney used mostly subquery syntax instead of joins, but the basic idea doesn’t change. You’ll notice Barney’s subqueries all return primary keys only. I like joins because they highlight the set-based nature of SQL whereas subqueries look like a for-loop in disguise, but that’s just personal preference.

Finally, what about stored procedures? I don’t consider stored procs to be part of the SQL DML (Data Manipulation Language) – they just provide somewhere for DML to live, plus that nasty procedural stuff that you sometimes can’t avoid. So if you’re a stored proc shop, everything above still holds. You just might be invoking your data views via a proc rather than directly.

In my last post I discovered that ColdFusion

true

is the string

"true"

. You can compare it to a java boolean true using “is”, but not using .equals(). Fair enough, one is a string and the other isn’t. “is” is ColdFusion magic, and .equals() is just poor dumb java. And if you’re wondering, “eq” is just as magic as “is”.

Does that mean all ColdFusion booleans are actually strings. No! The result of a boolean expression in ColdFusion is actually a java boolean.

So it turns out that

true

is not, as I thought, a built-in boolean constant, it’s actually a special syntactical case for creating a string – that can then be used in ColdFusion boolean expressions. This is reminding me of my perl days (that’s a good thing – I loved perl).

If you never had to interoperate with Java none of this would matter. The booleans and strings intermix seamlessly within ColdFusion. If you do, it’s important to know that ColdFusion actually does not have any built-in boolean constants that Java will recognise.

I’d tend to go with the third form using javacast. Use the second form only if you want to mess with your junior programmers heads Reminds me of those crazy funsters who put “and 1=1″ at the end of all their SQL queries.

None of these ColdFusion constructs strictly equate to a true-valued Java boolean (or Boolean):

true
"true"
1
YES

But this does:

JavaCast("java.lang.Boolean", true);

Makes me wonder what ColdFusion’s literal true (no quotes) actually is. Probably just a string.

Edit prompted by Todd:
Definitely just a string. And definitely not a boolean. Also not particularly suprising but something I’d never thought about is the fact that ColdFusion “is” is not the same as Java “equals”.

gives this:

a = true;
b = CreateObject("java","java.lang.Boolean").init("true");
c = "true";

java.lang.String

b.equals(a) NO
a.equals(b) NO
b.equals(JavaCast("boolean",a)) YES
b is a YES
a is b YES

a.equals(c) YES
c.equals(a) YES
a is c YES
c is a YES

b.equals(c) NO
c.equals(b) NO
b is c YES
c is b YES

This is the first and almost definitely the last time you’ll see me getting sentimental about a piece of hardware.

Not long ago we switched off MESWEB, the server that had been our main web server for ten years, and remained online for another three running legacy software and sites. That’s (you guessed it) roughly 113,000 hours up and running. The server that was bought to replace it four years ago has already been decomissioned – in fact it was decomissioned before its predecessor. These servers are all now virtual, so we will never again know what hardware we’re running, let alone see it or give it a name.

Coincidentally, MESWEB’s lifespan very closely matches my employment with the company, which was also unusually long-lived for our industry. MESWEB was commissioned only a few months before I was hired, and decomissioned a few months after I started my new role with our parent organization, the University of Queensland.

While I’m getting sentimental, this is the end of an era in another way – my involvement with the mighty and sadly defunct Digital Equipment Corporation. Nobody made machines that just kept going like DEC, and MESWEB, while it burnt a few (very expensive, SCSI, hot-swap) disks never had a fault. In fact it’s still in good running order. However, there will be no more DECs, and while carting MESWEB out the door I couldn’t help remembering:

• The very first program I wrote was about ten lines of Pascal on a PDP-11 terminal in the CS101 lab. Can you imagine a PDP-11 running about 100 terminals? On assignment days the compilation queue stretched out to nearly two hours.
• My first job was at an aerial survey firm. We were working on PCs (286!) but the survey guys still had their PDP-11 going. I remember the two book-cases of manuals. You want to re-wire the read head on your tape drive? Here’s the circuit diagram. And here’s the assembly code for the BIOS. All that documentation came in handy, as for some reason I got to write the software to read and write 9-track reel-to-reel tapes on the PC.
• My first job back in the full-time workforce after child-rearing was with a UQ department with a gaggle of epidemiologist’s and a long tradition of hardcore numbercrunching. SAS code makes 8086 assembler look like child’s play! Their DECSystem 10 was long gone but the name plate still hung proudly on the wall, the MicroVaxen sat in the corner desultorily forming quorums, and the new kid on the block was the DEC HX Pentium Pro server. The HX was a couple of model years before MESWEB and had the identical case. With the sole exception that the HX had a power-down on case intrusion “feature” – don’t ask me how I know

Which brings us back to MESWEB. Lots of memories there. Like the time Scott leant over to look at the back of the server, leant on the UPS power button, and powered down the whole server room. Then spent the next hour putting about eighty layers of sticky tape over the power button. The weeks of research, aided by DEC’s excellent documentation and spare parts service, that preceded installing a second CPU – new VRMs, BIOS and MP kernel included. And the pager. Oh, God, the pager…

So I did the only sensible thing under the circumstances. I brought MESWEB home, along with the external disk array. It’s sitting under my desk in my home office as I type this. I have fantasies of seeing if I can mount modern hardware in the DEC case and use it as my main machine, but I probably won’t – don’t know if I could bear to throw out those VRMs. So it just sits there, and when my i870 gets too cheeky it grumbles a bit and mutters “Ay, but when I were a lad…”

Now that we have techniques for making sure our tests hit the database, what are we going to test? Load by ID, load by name, load all, save, delete – sound like a reasonable starting point? I also throw in tests for what I call “finders”, which are utility classes for loading objects by anything other than primary or natural keys. Essentially these are DAOs, but I call them finders because I use the same idea for non-persistent objects as well. I must emphasise again – I’m only trying to exercise my persistence layer here. Domain-specific behaviour is tested in a different test suite.

One more concept before we get down to tin tacks. All my domain objects are accessed via a “manager” classes, which have methods like – you get guessed it – load by ID, load by name, save, delete, etc. The key to this approach to test generation is there’s a high level interface for managers, an interface for finders, and another one for domain objects. The specifics aren’t as important as the fact these interaces exist.

OK, with all of that, the test method for loading a generic ObjectType by ID looks like this:

As you can see, this is a method on an abstract base class. It’s the same for every test class for every domain object. Obviously there’s a bit more going on here, so let’s go through that.

getChildObjectManager() is the only piece that is different for each domain object under test. It specifies the the manager for this object, and once that’s provided our base classes can use our standard interfaces for everything they need to do.

domainObject was set up in our pre-test transaction that we talked about previously. Still within this same BasePersistenceTests class, we have:

doSetupBeforeTransaction() is invoked by our pre-test transaction setup code. Once again, identical for every domain object test class. In true template method style, we could override either or both of these methods for more exotic cases – for example, when we need to supply more inputs to the create() method. Although it’s amazing for how many domain objects you can create an instance with sensible defaults just by specifying a name.

hasTextIDName() and getTextIDValue(): I won’t go into the gory details. Usually hasTextIDName() just returns true (you can override it to return false), and getTextIDValue() uses reflection to look for a special annotation and get the value of the annotated field.

getID() is defined by the base domain object class – pretty obvious what this does.

So, our minimal test class looks like this:

That’s the whole thing – only the imports left out for brevity. I get 11 standard persistence tests – the getByID I showed you above, plus a few more I didn’t show – for half-a-dozen lines of code.

This simplest case is for an aggregate root. Things get slightly more complex for child objects within an aggregate, and more complex again for association objects. I top out at about a dozen overridden primitive operations, for about 60 lines of code in the test class, but get a couple of extra tests for testing things like cascades.

Now, obviously, if you’d read this far, I haven’t left you with any runnable code. For you to run my code you’d need to have your domain model set up just like mine, with all the same base classes and interfaces, and that’s not very likely, is it? At some point I might publish my base classes and interfaces, and you can port your code base over to my system . In the meantime, the take-home message is:

• You can and should test your persistence layer/mappings, as well as domain object behaviour
• Persistence layer testing has overheads and fragilities not needed by behaviour testing, so do it in a separate test suite
• A decent set of base domain classes makes it possible to write generic base test classes
• Use of the template method pattern means you only write the standard stuff once – and there’s a lot of standard stuff
• All of which makes life much less boring

Previous posts:
Spring/Hibernate unit testing part 2
Spring/Hibernate unit testing

The basic Spring transactional testing approach is great for testing domain logic. If you want to test your persistence layer, though, it falls short in some important ways. Most importantly, with the usual approach you will never actually load an object from the database. All the objects you create will hang around in Hibernate’s session cache, and will always be fetched from there. Even objects you created in a @Before method will be within the one Hibernate session.

As an example of why this matters, you can misspell your primary key name in the hibernate mapping file, and these tests will never tell you. Don’t ask me how I know

The solution is to create some test data before the main test transaction kicks in. That has some significant disadvantages, so I only do it for test classes that are specifically testing the persistence layer. What are the disadvantages?

• If something goes wrong with your setup or teardown code you can be left with a dirty database and you’ll have to clean up before any further tests will work. In a CI environment that can be a real pain, so ideally you’d be regenerating your test database after each run of the persistence tests.
• Because you need three transactions per test, it’s significantly slower
• You have to write the transactional boilerplate for setup and teardown

So, it’s worth doing, but it by no means replaces the basic single-transaction approach. Most of your tests should still be using that approach.

In use, the persistence test class looks something like this:

We’re still extending the same Spring base class as before. We’ve just injected our transaction manager and added a couple of methods. The @BeforeTransaction and @AfterTransaction annotations are provided by Spring for exactly this use case. Spring will still wrap our test methods in a transaction automatically, and still roll it back after each method invocation.

So now in our test methods we can load any of the objects we created in our “before transaction” code and be sure we are hitting the database.

After writing 20 or 30 of these persistence test classes you start to notice that they all look very similar. Create an object. Make sure you can load it. Tear it down. This is even more pronounced if you use a base domain object class as I do. In my Tags: hibernate, java, spring
Category: Software development  |  Comment

Spring provides some nice tools to assist in unit testing with Hibernate, which are well covered in Spring’s excellent documentation. I want to recap some basics here, though. Later on I want to talk about persistence layer test generation, and I’ll need to refer back to these notes.

One of Spring’s convenient tools is a base test class that does a number of nifty things:

• Sets up your application context, including Hibernate SessionFactory if you have one.
• Uses that context to do DI into your test classes, which is a nice way to access your singletons within your tests.
• Wraps each test method in a transaction which gets rolled back at the end of the method. Complete test isolation with zero work on your part!

In use, it looks something like this:

This is a godsend for most test scenarios. It falls down in some ways, though, and I’ll talk about that in my next post.

It’s been stated that a weakness of CF9 ORM is that it only allows one ORM datasource. You can use other datasources via cfquery. It’s worth understanding why this is so. It certainly wasn’t left out arbitrarily.

In a nutshell, default CF9 ORM only allows one datasource because it wraps each request in a transaction. Generally speaking, this is a good idea, but it’s a characteristic of standard database transactions that each is tied to a single database connection. This is generally true, not just specific to ColdFusion datasources. It’s also why cftransaction only allows queries from one datasource within the transaction body.

I gave myself a couple of escape hatches in the preceding paragraph – I said “default” CF9 ORM and “standard” database transactions. Does that mean non-default CF9 ORM with non-standard transactions does allow multiple datasources? Why, yes it does! But be aware that this is a non-standard usage model. Not only do you need to delve into the underlying Hibernate configuration to make it work, many scenarios are not even standard practice within the Hibernate world.

I’m not going to write a how-to on CF9 ORM with multiple datasources, but I will provide some pointers to how this might be done.

1. Delve into the underlying Hibernate and turn off the transactions. Non-transactional code is fairly common in plain CFML apps. This sort of thing is a large part of why “scripting” language developers are regarded as cowboys by “serious” (read Java) developers, but it can be a valid solution in some circumstances.
2. Use XA datasources with a JTA transaction manager for true distributed transactions. There’s a succinct example for this in Java here. This is common practice in the J2EE world, but pretty outre for CF. For a start, you’ll have to find XA datasources for your database and replace ColdFusion’s built-in datasources. You may also need to configure your database server to participate in distributed transactions.
3. Roll your own transaction manager in Java and plug it into Hibernate. There are any number of people out there in the Java world with strange use cases attempting just this. Google “hibernate multiple datasources” for some interesting and somewhat desperate reading. Or not
4. Use CF9 ORM in its default transactional mode for your main datasource with non-transactional access (e.g. cfquery) to secondary datasources. You don’t have to completely give up on data integrity, though. Schedule a task to check data integrity and issue compensating transactions where necessary.

There are some other possibilities, but that should be enough to get my basic points across. Firstly, this isn’t really a CF9 or even a Hibernate issue. And secondly, you really only have a choice between either relaxing your transactional guarantees (options 1 and 4), or doing a lot more work (options 2 and 3).

No. Transactions are not optional.

One of the things that irked me in learning Hibernate was the amount of time spent worrying about transactions. Indeed, when I speak about Spring/Hibernate implementation models to ColdFusion developers, I get the same reaction that I initially had myself – “Why do you keep talking about transactions? We know what they are, it’s kind of interesting, but this is hardly a central concern.”

Coldfusion database connections are in autocommit mode by default. The Hibernate guys wrote an excellent article on autocommit. Essentially it means you automatically get a transaction per <cfquery>.

This is a good thing. The CF server can’t possibly decide on a transaction strategy for you, so it can only do one of two things:

• Turn on autocommit so you can get coding
• Turn off autocommit so you must implement a transaction strategy before you can run a single query. Alternatively, you can work out how to turn autocommit back on.

Given ColdFusion’s RAD focus, which do you think CF is going to choose? It’s going to let you get coding.

Unfortunately, many of us take that as permission to put off thinking about a transaction strategy indefinitely. Can you actually put your hand on your heart and say “No, data consistency is not important to my application”? Probably not. But if you’re like me, you might be able to let your application slide across the line from prototype to production without really addressing this issue.

Hibernate, which is much more interested in being right than being RAD, starts from the other position. Work out your transaction strategy. Then we’ll talk. Autocommit mode is available if you really need it, but is off by default and is considered a non-standard usage model.

So no, transactions aren’t optional. Hibernate smacks you over the head with this fact, so if you’ve been letting this aspect of your applications slide, you’ve got some catching up to do.

Note: CF9 ORM in its default configuration does in fact choose the transaction-per-page-request strategy for you. This is a bit of a philosophical departure from the default cfquery behaviour, but I think it manages to walk the line between CF RAD culture and Hibernate “serious software engineering” culture.

You’re the chief technologist of Webz R Us. You’ve been around, used a few different languages. You think in the abstract and can adapt to the specific implementation. You can make ColdFusion sing and dance. Now you’ve given yourself the task of getting Spring and Hibernate up and running with your ColdFusion web apps. You’re not using CF9 ORM – you’re going to roll your own for ultimate control.

It’s natural to think that you can take your accumulated wisdom and cherished practices and tweak them a bit for this new implementation technology. If you’re a JEE web app developer, that might be true. If you’re a ColdFusion developer, it almost certainly is not.

So after a train-wreck or two, you jump onto the forums – this is open-source software with a vibrant community, right? – and explain your cherished practices, why they’re so great, and ask for help on that one little API that you need to make everything fall into place. It’s so obvious to you that this should work. You’ve been doing it for years in ColdFusion. In Java, with all these great frameworks, your cool approach should be even better supported and easier to do.

And what you’re told is:

That’s a non-standard usage model

Let me translate that for you:

• What a dumb idea
• Even if it works, nobody cares
• Go read the documentation
• You n00b

OK, I’m being a bit harsh here. Let me try another translation:

• 99% of successful projects don’t use your technique
• Maybe you should think about why that is so
• You really do have an edge case? Time to man up and earn those big bucks. Good luck.

I love the quote below. The guy who wrote this really had put in the hard yards to understand the questioner’s use case – this was by no means a brush-off:

How about you just trust what Hibernate is doing, because it always has very good reasons for its very sophisticated caching behavior, and the people who designed this stuff have spent a lot, lot more time thinking about caching and transactions than you have.

I didn’t post this just to be amusing. I’ll be referring to this post a lot in the coming months. You won’t believe how many non-standard usages I’ve been able to come up with. Stay tuned…