At 04:48 PM 6/5/2001 +0200, Paulo Gaspar wrote:

>I want to use Firebird for Internet work... but what I am getting is
>a lot of people saying that Firebird does not want to be an Internet
>database. (Man, the Internet is really out of hype this days!)

Firebird does want to be an Internet database - some of us working on
it have doubts about the efficiency of the feature you propose. If
your URL maintains enough "state" to establish where the "next" set of
rows should stop, restarting the query with a reasonable restriction
would (in general and assuming the presence of a reasonable number of
indexes) be faster than asking the engine to read and discard the rows
already returned.

You're also suffering a bit from the old-guard relational types who
find the fact that there is no inherent ordering of rows in a SQL
database to be liberating.

> For most cases there is no simple way to saying what is the "thiskey" and
> the "nextkey". There are requests that can be translated by things like:
> Page 3 with 20 records by page of all the movie sessions running this
> week in my region (e.g.: 50 km around this city), ordered by movie name,
> city, cinema, room, session date/time.

My inclination would be to construct an initial query like this:

select <whatever> from movies m
join schedules s on s.movie = m.movie
join cinemas c on c.cinema = s.cinema
join towns t on t.town = c.town
where 50 <= distance (mycity, city)
and s.date >= CURRENT_DATE and s.date < CURRENT_DATE + 7
order by m.movie, t.town, c.cinema, s.room, s.date, s.time

I'd get the first 20, noticing the values of the last row retrieved.
When the user hits next, I'd issue this query:

select <whatever> from movies m
join schedules s on s.movie = m.movie
join cinemas c on c.cinema = s.cinema
join towns t on t.town = c.town
where 50 <= distance (mycity, city)
and s.date >= CURRENT_DATE and s.date < CURRENT_DATE + 7
and m.movie >= :saved_movie
and t.town >= :saved_town
and c.cinema >= :saved_cinema
and s.room >= :saved_room
and s.date >= :saved_date
and s.time >= :saved_time
order by m.movie, t.town, c.cinema, s.room, s.date, s.time

I'd get 21 of these, throw out the first one as a duplicate, and
return those values. Prior is left as an exercise for the student.

If I offered the user the ability to click on an arbitrary page
rather than just go through hitting next and prior, I'd retrieve
enough data to fill the range of pages I offered (as google
appears to do) and get more if the user showed signs of moving
out of that range.

Actually, and in fact, I'd write an intermediate application
designed to cache significant quantities of data. This is a
place where cheap memory is a real win.



Regards,

Ann
www.ibphoenix.com
We have answers.