Re: do i need a view or procedure?

Matt,

Thank you for the feedback!

> I, too, am not a guru. Indeed, I'm so far from guru-hood that I'm not
> even clear on why it is that you need anything more complicated than a
> SELECT.

Well, this may be exactly what we need, since a view is basically just a
SELECT statement. It's just getting quite complicated so it might be
easier to do it in a procedure instead (though I'm growing more confident
that a stored procedure can't return a result set since I've yet to see any
examples).

> Then you might use a SELECT like this:
>
> "SELECT p.rpm-data
> FROM patches p, servers s
> WHERE s.id = xxx
> AND s.criteria-1 = p.criteria-1
> AND s.criteria-2 = p.criteria-2
> AND s.criteria-3 = p.criteria-3
> AND p.version > s.version
> AND ...
> "

This is what we started out doing too. The problem is that to pass a
criteria doesn't necessarily mean you have to equal it. Sometimes it's
equal or greater, sometimes it's not applicable at all, and often it
depends on the particular patch - which is why it's so hard to make a
general rule that applies to all patches and all servers. Some of the
exception cases are that often we'll be testing something new out and
release a patch destined for one and only one server whether or not it
meets the criteria (this is actually easy to handle, it's just an OR in the
above select statement, but they do get harder).

We've been developing a view whose SELECT statement grows ever more complex
as we realize new rules that must be observed. I think we'd be capable of
just growing the SELECT statement indefinitely, but its getting messy and
hard to understand and maybe even inefficient. I'm trying a new approach
this week, seeing if the names and numbers of the patches themselves can do
a lot of the legwork as to who is and is not eligible for a patch. We'll
see how that goes.

> You can even make the logic more complex, but perhaps more efficient, by
> creating another table, this one containing perhaps three fields:
>
> server-id, patch-id, patch-version

In fact, we have this exact table, which we called 'installs'. However,
it's only part of the puzzle - the last part. After we whittle down to all
of the eligible patches for a particular host, we then use this table to
say which of those they already have installed and remove those from the
result set. So, it does work very nicely for that.

Thanks for the input, if nothing else, it gets the brain thinking about it
in different ways.

Thanks,
Fran