Friday, 1 December 2006

When "green" is not exactly #00FF00...

When one is enthusiastic about something, one has the tendency to always see the bottle half full. For example, take me, add Ruby On Rails, shake a bit, sprinkle with some J2EE frustrations and you end up saying 'yeah, sure!' to a project that is not so green-field like after all.
In my case, moving away from the current Excel spreadsheet my users have, towards a full blown web application, also means doing some integration which does not currently exist. That is the point where green becomes #40FF00.
The integration here consists of an existing database, which is read-only for me (as I have to use views) in order to trigger the inserts into my new db. To make things more interesting, the view primary key is actually a composite key made of 2 strings. When you look at Rails, that does not fit the profile of a walk through a green field ! Composite keys : not supported. String primary keys : not supported.
So I did some thinking (at least I believe I have) and came up with the following architecture.
It is sort of a NAT for the id...Let me explain:
I have a sequence that will provide me the id Rails likes. I have a table that stores the id corresponding to my view composite primary key. I have a function that does a look-up in the table, if it finds an id for my composite key elements, it returns; if it does not, it gets the next value from the sequence, inserts in my look-up table and returns the newly created id.
Then the table I work from in Rails is actually a view on my source view, which uses the function to replace the composite string primary key by a good old integer primary key, to make Rails happy. Performance wise, the 1st time, it is slow, but the second it gives fairly good response time. Et voila!
Testing is ongoing, if I find something fishy, I'll post about it.