--- In firebird-support@yahoogroups.com, "unordained" wrote:
> It's not like I'm under oath not to reveal what I'm doing, so I
> might as well share. However, the question has been answered -- no,
> views are not visible until you start a new transaction. So I'll
> just live with it.
>
> Note: it's 3 in the morning, and I'm a bad communicator on a good
> day.

Get some sleep, and then try to find my inline comments when you wake
up again!

> --Background--
> I have a user who's complained about not being able to run her own
> queries, right? Okay, so first off, I'm making a query builder for
> her. Second, we have a lot of queries coming up for various
> healthcare-related grants that involve things like "people who match
> all of the following conditions" or "people who match two of the
> following three conditions", etc. Now, if we just had a few, here
> and there, I wouldn't mind doing it by hand. But when you've got
> dozens, and the specs change constantly, you want an easy way to do
> things like "none of the following", "all of the following", etc.
> on re-usable queries -- it's a time-saver even for programmers.
>
> So I've got it built. My first attempt involved pulling all the data
> from the database, for each bit of the query, and doing in-memory
> joins of id's, pushing stuff back out to the database to do joins
> (as the "IN" clause can't take more than 1500 elements, and OR
> isn't terribly efficient, and it's nasty regardless, doing an
> indexed join seemed smart) ... it worked, technically. I think the
> dead rabbit I saw on the road is probably faster. Enormous memory
> usage, lots of network traffic, generally ... just ... bad. (And
> BCB5's memory management problems weren't helping. I swear I know
> how to use the STL efficiently.)
>
> --How it now works--
> So I redesigned it (over the weekend) so each query element would
> return the name of a view that expressed its result. It could be a
> view directly to the data (no cache) or it could be to a temporary
> table that contains cached data in the form of id values. (query_id
> + id). Regardless, everything uses view names to express stuff.
> Those names then get piped into components ("all of", "none of",
> "some of", "any of", as well as changing-table elements such as
> "people who have diseases" -- which returns people id's, but
> receives diagnosis record id's) and ... so forth. And it has a
> really ugly background color.
>
> The point is, this has the potential to be really quite efficient,
> considering how general-purpose it is. I can let Firebird optimize
> statements, so long as I keep everything as the result of views.
> If I force things into temp tables, I tie its hands ... but that
> can't always be safely avoided.
>
> Things like "any of" can be expressed as the union of all the views,
> "none of" takes a "whole" view and left-joins to all the other
> views, and checks to make sure none match (this is a bit slow, my
> test case takes about 10 seconds -- if anyone has a better way to do
> a "NONE" query, I'd be very interested),

a "NONE" query I normally solve by using NOT EXISTS(SELECT ...)
Normally, such a select executes surprisingly quickly.

> "all of" does inner joins between the first view (doesn't matter
> which) and the rest, and "some of" either does one of the above, or
> winds up using a temp table and doing a group by, counting how many
> of the views a particular record matched, returning only those id's
> that match the correct number ...
>
> (Example of "some of" query:)
> People that
> - Match 1 to 2 of the following queries:
> - Is 65 or older
> - Has diabetes
> - Lives in our area
> - Has had at least two procedures that
> - All of:
> - Is one of the following codes [.......]
> - Any of:
> - Was performed this year
> - Was performed more than three years ago

Understandably this seems quite difficult, but rather than go the
'view route', I think I'd save 'query parts' somewhere (possibly one
table with a text varchar or blob containing the interesting parts.

I.e. I would start off splitting things into two - which tables to be
queried and which values to check for. Hence, for a subset of your
query, I'd do

- Is 65 or older
Table: PERSON
Value: PERSON.BIRTHDATE < ('TODAY' - 23741)
(65 years is approximately 23741 days)

- Has diabetes
Table: DISEASES
Value: DISEASES.ICD_10 BETWEEN 'E10' AND 'E14'
(not bothering to include diabetes amongst pregnant women, infants
etc.)

- Lives in our area
Table: PERSON
Value: PERSON.AREACODE = '0301'
(I never knew you lived in Oslo)

And then link all of them together when requested, avoiding addressing
PERSON more than once. I should already know how to join the tables
together (more or less a constant, maybe saved in another table).

SELECT <whatever>
FROM PERSON
JOIN DISEASES ON DISEASES.FK_PERSON = PERSON.PK
WHERE (PERSON.BIRTHDATE < ('TODAY' - 23741))
AND (DISEASES.ICD_10 BETWEEN 'E10' AND 'E14')
AND (PERSON.AREACODE = '0301')

> Since I'm mostly doing joins, and firebird doesn't yet let me put
> selects directly in the FROM clause, and since I'd like to avoid (as
> much as possible -- I've tried it, and it -usually- slows me down)
> selecting each query into the temp table (indexed) and then always
> joining against the temp table ... views were just a natural
> solution? Yes, you could probably rig something where only one sql
> statement was built, with every query object touching it, but ...
> I'm just not smart enough for that.

Yep, temp tables doesn't sound ideal (but then I've never used a temp
table). And splitting things up as I've done above couldn't be much
more difficult than creating temporary views?

> But it still would have been handy to auto-rollback those views,
> particularly when this goes into production and connections could
> fail for any reason, leaving the database with random view names
> that might collide -- and the client application might not be able
> to reconnect to delete them.

Well, if you do rather store parts in a table to be assembled to a
query in the end, then this wouldn't be a problem at all.

> And the view definitions cannont possibly affect database integrity,
> right? No other transaction could use them until I committed, and
> views can't let me do anything to the database that I shouldn't ...
> which means ... temporary views should be safe?

I've never tried, but what if an administrator for some reason wants
to modify or delete a table that has such a temporary view attached to
it? I know I wouldn't be too happy if I had to find and delete several
'temporary views' that have been built the last few years.

Set