FatGeekUK

Bending sql to your will. Crossing an sql table with immediate data.

Posted by Peter Morris Fri, 20 Jun 2008 13:46:00 GMT

Well, you are probably wondering what prompted my last post about ‘sql ghettos’. And well you might. The incident that spawned the term happened over a year ago.

But, I thought I should get my personal stand on the matter documented before I offer this little tid-bit of sql twistedness.

In a current application I am working on, I need to produce a list of items for which work has NOT been done.

Work being done is recorded against the item by attaching a tag with the name of the work task.

Gathering a list of items to be worked on next therefore means looking for items which do NOT have specific tag values associated with them.

Previously, it was good enough to find items for which not all the tags where defined and then spit out a list of the items. That was fine, and handled by a simple piece of code that just counted up how many tags of specific names each item had, and if it had less than the number I was looking for, include it in the list. Fine, jobs a good un, put your feet up, have a cuppa.

But NOW, I need to start keeping track of which tasks have been released to which systems running the jobs.

In order to do this, I need the end result to be a list of items, and where that item does not have a tag, I want a ROW returned with the id of the item and the tag THAT IS NOT THERE.

This is a vastly different argument, and requires you to start searching not just for a low count, but searching AND FINDING things that are not there.

This can be split up into two steps…

1. generating a list of the items crossed by the tags you are looking for.
2. decimating that list on finding the tag present.

The first step sounds simple, until you remember that the tag might be referencing a totally new piece of work that has never been done before, and so will not be resident anywhere in the tag table.

So, what you are asking to do is to be able to ‘cross’ an sql table with an array of string literals.

Now, stop, go away and try to do it.

I will discuss my solution after the break…

Posted in Coding | Tags ghetto, sql | 1 comment

Web development frameworks and SQL ghettos.

Posted by Peter Morris Fri, 20 Jun 2008 13:34:00 GMT

My platform of choice is Ruby on Rails.

In an early project, I implemented some reports by coding special purpose sql statements embedded within model methods. This worked well, and allowed the reports to run reasonably speedily.

As I continued to maintain the application and requirements changed, I found that I was reluctant to change the way the model objects interacted as it would require not only changing the model code, but sifting through the mounds of complex and convoluted sql.

What had been an efficient way to handle a problem (generating the reports quickly but in coding time and in runtime) had resulted in ‘scar tissue’, or maybe a fossilisation of my object model structure within the body of the sql.

Over time, this caused me to change the way I was altering the design, by forcing me to add layers around classes instead of refactoring within them.

After I finished on that project, I reviewed what I had done and vowed never to create sql ghettos again.

I think it is an apt term ‘sql ghetto’. An area in your application that you almost dare not go. that hinders future development, forcing it to work around it.

Resist the temptation to make ghettos, for they will surely come back to haunt you.

Posted in Coding | Tags ghetto, rails, ruby, sql | 2 comments