Generally people like to blog about really slick new technologies or smarty-pants ideas they have recently come up with. You don't get too many technology blogs where someone starts off with "I did this totally lame hack today..." That is why I had to come up with these introductory sentences. I had to get comfortable with the idea of sharing a real quick-fix, crappity-crap hack I did today. In my defense this lame hack was the right thing to do, and I promise to eventually right this wrong. Well, enough dancing, on with the details.
A while back I was handed a database full of data from a very large functional test suite. I was tasked with helping to create a web application for helping to visualize these test results. Several of us developers wanted to learn Rails so we came up with some good reasons to go that route and a month later we had a working tool. The problem was the database. It was legacy, and was shared with other users. About a month after we finished the tool the crumby old legacy database got rewritten by the other users who where sharing it. This was a major, catastrophic change for our application. Any Rails app using Active Record would have a similar problem. Don't get me wrong, I love Active Record, but when your legacy database is suddenly normalized and the table count doubles your entire app is pretty much screwed! While I was wringing my hands and sobbing about the demise of this glorious Rails app somebody made the joke that I should create views on top of the new DB to mimic the old table structure. I half-heartedly laughed this dumb idea off and focused on pressing matters like fixing the application, but as I imagined the complete makeover my domain model was about to receive it dawned on me that this application was a readonly app. Perhaps using SQL views to trick ActiveRecord into thinking the database hadn't changed wasn't so far fetched after all.
To make a long story short, instead of a long and tedious refactoring I mapped the new DB to the old DB using SQL. An hour later I had the app back up and working. I had simply created views to represent the old DB structure. It is sort of like doing your O/R mapping in SQL.
Note: This isn't a long term solution for fixing the application. In the end the DB refactoring that broke the application really needed to get done. And it makes sense to port the application to use the new table structure. As a stop-gap, this has been a fantastic technique. Active Record has no problems talking to views instead of tables. There haven't been any noticeable performance losses. The only unanswered question is about persistence. Can you use views if your app has to do updates?
3 comments:
Anders Norås
Whether you can use views for persistence depends on which database you’re using. Oracle has materialized views which allows updates to some extent and Microsoft SQL Server supports “updateble views”. I did some experimentation with ORM and views a while ago (http://andersnoras.com/blogs/anoras/archive/2007/03/01/nhibernate-mapping-a-class-to-several-tables.aspx) and found that you have to jump through quick a few hoops to achieve this.
Regards,
Anders
The key here was that I didn’t have to write or update the DB. Read-only, so there really weren’t any hoops. The only trick was getting the primary keys on the views right. At one point I just used the row count from my select statement to create a primary key (the key wasn’t actually being used by any other tables so I was just jumping through one of ActiveRecord’s hoops).
Post a Comment