The Quest for ORM
Many years ago, in 1995, I believe, I was still working in a huge telecom company. The main languages we used were C and C++. Relational databases were just getting mainstream.
The project I worked for, a real-time system that coordinated long-distance telephone calls in Canada, was sort of done. No major features were in the horizon, except maybe the move from X.25 to OSI. I was the main person responsible for OSI, specifically ROSE and the application layer protocols, like ACSE, etc. The main protocol we were after was CMIP.
The running system actually used ROSE over X.25. A very strange beast.
Anyhow, the OSI project was slow moving, and we started seeing how lumbering OSI would be. But, in their infinite wisdom, even though we were already using TCP/IP, for some reason it just wasn’t good enough. Whatever. (For the test environment, we actually had ROSE over X.25 on top of TCP/IP. Doubly weird thing, if you know about how these OSI, X.25,and TCP/IP send messages/packets/PDU)
So, I was loaned to this other project that was trying to build a workforce system for the telephone company. It was a lot of fun, but I knew it wasn’t going anywhere, so at the end of the loan period, I went back to the routing control project.
One of the things the workforce management system were trying to do was to use the database in an object oriented way. So, they needed a way to represent a table-based database. Yes, we were building an ORM (object-relational mapping) framework. The programmer responsible for it complained to me that it was just so tedious to write one class for each table. Well, I was a smart-ass, but after a few years of working by that time, I knew enough to keep my mouth shut; I did not offer to him how I would do it: code generation.
Ever since, until a few years ago, that was a favourite sports of mine to find a way to tame ORM. I tried many things, including hideous things like EJB, and countless other libraries. Eventually, I settled on OpenJPA, after using Hibernate for a while. I liked the JPA concept but I hated how convoluted Hibernate was. Even though I knew the “enhancement” process was going to come back to bite me in the you know where, it was still far better than EJB.
And then the trouble started. You see, you choose whether a table should be loaded eagerly or lazily. Can’t be both depending on where. And the query language is string-based. Don’t mention the “entity manager” thing.
Initially, everything was fine. Then the system I was working on, the third or fourth system I built by that time, using OpenJPA, got bigger and with more tables. And OpenJPA at that time had this bug that if you kept going to link to the next table by the foreign key, eventually it could not handle it and would give you nulls, even though that was not correct. It did remind me of Orbix (a CORBA framework) we used in 1997, I think. More than three levels down, and it could not cope, you would get nulls everywhere.
Anyhow, fine, so we made sure we would not trace too deep from the first table we accessed. But the string-based query was getting really hairy when we had more than 1,000 classes and more than 200 tables. I am a firm believer of refactoring. So, whenever something was no longer true, I would modify the system to fit the new reality. For instance, when the system got more features, inevitably, the database schema would change with new tables and modifications to the old tables. Sometimes, it was just something mundane like changing the spelling of a column. With a string-based query language, now we had to hunt down every occurrence of that old spelling in the system. That is not fun at all. I mean, if I wanted something like SQL strings in my system, I would use SQL strings directly, no?
When I discovered jOOQ, I was so happy. Once I knew its basics, enough to cover what we used JPA for, we just replaced JPA with jOOQ. It was painful doing the replacement, and it took a while. But, hey, we knew life would be better after the replacement. And, I built a system to create classes automatically for the tables. This is still a convenient way to handle tables. I no longer cared whether it was a proper ORM thing or I was using objects to mimic tables, or whatever. Mixed model it is.
I think the older I am, the less I give a damn about purity. However, this does not apply to functional programming. But, that is another story.
