Monday, July 11, 2005

The IN thing

With Oracle 8i and below there was a classic problem that I never really got what I felt was a suitable solution to...

I've always gone for limiting the SQL that exists in both the database and the higher layers, as described (related to PHP)here.

By doing this I find I can very nicely wrap up the database tables with a layer of abstraction that protects the higher layers from changes. So I could have a 'Product' lookup that is based on a reasonably complex query what involves the joining of several tables.
But what if I wanted a list of all the products in the system, or the top 5 priced, or any of those other options that just come up, you've got limited choices.

  1. Produce a function that covers each of the lookups, and include the full SQL statement in each lookup.
  2. Provide a function that covers the return of the list of products ID to return, and pass that list into a function that returns the full details (which can be used for any lookup).
  3. Provide a single function that does the lookup in any case, and has a lot of parameters.
  4. Throw away this whole idea of encapsulation and just put the SQL where you need it.

I've always liked the second option. You can have functions that cover searching for records and then other functions that deal with the returning the details of those records.

One configuration that allows you to do this is to have the function returning the details deal with a single entity. Pass in the ID of the one you're looking for and get the full set of details back. If you need multiple records, then you call the function multiple times. It's a good solution that's clean and simple. It just doesn't perform well when you scale it up. What happens if I want 1,000 records? I call the function 1,000 times?

The alternative would appear to be to pass in a list of the IDs you want and get back a ref cursor or table of records back.
You can reasonably get back a table of results, but it means looping over the incoming IDs and reverting back to your individual lookup.
It's harder to return a ref cursor though: The problem is the inflexibility of the IN statement. It's not possible for you to specify an arbitrary list of values in a non dynamic SQL statement.
I.E. with vc_list_of_ids set to "1, 2, 3, 4", you can't say:

OPEN product_cur FOR
SELECT *
FROM product
WHERE id IN ( vc_list_of_ids );

although you can say:

OPEN product_cur FOR
'SELECT *
FROM product
WHERE id IN ( '|| vc_list_of_ids ||' )';

It's a subtle difference, but the second (the correct one) will require Oracle to reparse the statement every time it is ran. Ask any DBA and they'll say that's not exactly a great idea.

Thankfully, Oracle 9 gave us a much nicer solution. Casting to tables.
We can now use a PL/SQL table of values and treat it as a ful blown SQL table. So, if we use the table vt_list_of_ids, we can now say:

OPEN product_cur FOR
SELECT *
FROM product
WHERE id IN ( SELECT id FROM TABLE( vt_list_of_ids ) );

This has the advantage of not being re-parsed each time.

If we pass this back as an open ref cursor we need to make sure that the table definition and the variable that contains the data are both available to the outside world, but it works a treat.

I'll put a working example up here once I get a chance...

Technorati Tags: , ,

Thursday, July 07, 2005

The BBC don't like parody

I must be missing something, because something pretty minor happened that points to something fairly fundamental. And I just don't understand it.

The BBC shut down IsAGaylord.com .

Now forgive me if I'm going mad here, but a week ago this site was getting 20,000 hits a day from people who got a surprise, had an immature snigger, then noticed that it wasn't really a BBC news site after all. Maybe they forwarded it on, maybe they didn't, but they went away not even considering that it was in any way linked to the BBC and definitely not thinking any the worse of the BBC if they did. Then a couple of people didn't get it, and complained... to the BBC.

And so the lawyers got involved, put some pressure onto the host and the site got pulled. So now, for the next week that site will get its usual 20,000 hits a day, and 140,000 people will read a tirade from a guy who's had to pull a very successful site because the BBC got petty.

So, in order to keep the BBC's reputation with a handful of people, they've managed to tell 140,000 how petty they are.

It takes a lawyer to be THAT clever!

Tuesday, July 05, 2005

Blog stats made easy

Thanks to Andy Beacock's post here I've put some more logging onto the site. At the risk of looking like a right newbie, I've even left the counter on for the world to see... watch that number creep up to 10 over a matter of mere months!

Statcounter will very nicely put together some pretty comprehensive stats on page visits, unique visitors, re-visitors and the like. Split down by day, shown in a nice graph. It's basically got all the little facts about your visitors that let you know if people are actually reading what you've got to say.

As with Andy, I'm hoping that seeing the numbers will push me to get more useful text out there...

The same has, of course, been put onto BobaPhotoBlog

Technorati Tags: , , , ,

Monday, June 27, 2005

Anyone got a pencil?

I'm not normally one for posting web sites on here, but I like this...

Pencil Carvings
(especially this one, very mechanical. I likey)

A fine example of what you can do with a bit of talent, an idea and a lot of patience!

Monday, June 20, 2005

BobaPhotoBlog

Picasa and Hello are now well and truly installed on my machine, increasing the amount of free software yet again. Picasa is pretty cool and I reckon I'm going to get used to having that installed pretty quickly. But more importantly, Hello means that I can pretty easily put photos onto a blog. So bring on BobaPhotoBlog... it'll get the odd photo put up every now and again as I see fit. I may get round to putting some text on camera settings up, but more likely I'll just forget!

I'll be testing out its integration with Gmail soon enough...

Technorati Tags: , , , ,