SEO

Christopher
> > - Present a nifty date selector to choose the records from any
day,
> > hour, minute, second
> > - show them, with "next day" and "previous day" buttons
> >
> > - It's more useful to the user (most likely he wants to know
what
> > happened on 01/05/2005 rather than view page 2857)
> > - It's faster (no more limit/offset ! just "date BETWEEN a AND
b",
> > indexed of course)
> > - no more new items pushing old ones to the next page while you
> browse
> > - you can pretend to your boss it's just like a paginated list
>
> All very well and good, but now do it generically...

I've done it...
First of all I totally agree with PFC's rant regarding absolute
positioning while browsing datasets. Among other things, it has serious
problems if you have multiple updating your table. Also it's kind of
silly to be doing this in a set based data paradigm.

The 'SQL' way to browse a dataset is by key. If your key has multiple
parts or you are trying to sort on two or more fields, you are supposed
to use the row constructor:

select * from t where (x, y) > (xc, yc) order by x,y;

Unfortunately, this gives the wrong answer in postgresql .

The alternative is to use boolean logic. Here is a log snippit from my
ISAM driver (in ISAM, you are *always* browsing datasets):

prepare system_read_next_menu_item_favorite_file_0 (character varying,
int4, int4, int4)
as select from system.menu_item_favorite_file
where mif_user_id >= $1 and
(mif_user_id > $1 or mif_menu_item_id >= $2) and
(mif_user_id > $1 or mif_menu_item_id > $2 or
mif_sequence_no > $3)
order by mif_user_id, mif_menu_item_id, mif_sequence_no
limit $4

This is a Boolean based 'get next record' in a 3 part key plus a
parameterized limit. You can do this without using prepared statements
of course but with the prepared version you can at least do

execute system_read_next_menu_item_favorite_file_0('abc', 1, 2, 1);

Merlin


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> I've done it...
> First of all I totally agree with PFC's rant regarding absolute
> positioning while browsing datasets. Among other things, it has serious
> problems if you have multiple updating your table. Also it's kind of
> silly to be doing this in a set based data paradigm.

Recently I've been browsing some site and it had this problem : as users
kept adding new entries as I was browsing the list page after page, when I
hit "next page" I got on the next page half of what I already saw on the
previous page. Of course the webmaster has set the "visited links" color
the same as "unvisited links", so I couldn't tell, and had to use my
brain, which was quite upsetting XDDD

And bookmarking a page to resume browsing at some later time does not
work either, because if I bookmark page 15, then when I come back, users
have added 10 pages of content and what I bookmarked is now on page 25...

>> All very well and good, but now do it generically...

Hehe. I like ranting...
It is not possible to do it in a generic way that works in all cases. For
instance :

Forum topic case :
- posts are added at the bottom and not at the top
- page number is relevant and meaningful

However, in most cases, you can use a multipart key and get it right.
Suppose that, for instance, you have a base of several million records,
organized according to :

- date (like the original poster)
or :
- country, region, city, customer last name, first name.

You could ask for the first three, but then you'll get 50000 Smiths in
New York and 1 Van Bliezinsky.

Or you could precalculate, once a week, a key interval distribution that
creates reasonable sized intervals (for instance, 100 values in each),
maybe asking that each interval should only contain only one city. So, you
would get :

Country Region City LastName FirstName
USA NYC NY Smith, ''
USA NYC NY Smith, Albert
USA NYC NY Smith, Bernard
.....
USA NYC NY Smith, William
...
USA NYC NY Von Braun
...

So you'd predetermine your "page breaks" ahead of time, and recompute
them once in a while. You won't get identically sized pages, but if the
statistical distribution of the data plays nice, you should get evenly
sized pages.

The interesting part is that you can present the user with a selector
which presents meaningful and useful data, AND is fast to compute, AND is
fast to use.
In this case, it would amount to "Select country, region, city", then,
display a list like this :
Smith, ...Albert
Smith, Albus...Bernard
...
Smith, William...
...
Von Braun...Von Schwarts
...

So Jeannette Smith would be easy to find, being in the link "Smith,
Jean...John" for instance.

If the aim is to quickly locate a particular record, I like
javascript-powered autocompletion better ; but for browsing, this
pagination method is cool.

Regards !


























---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match