You cannot do any of these things in any relational database. You can only do
this in navigational databases. There are various kludges which permit you to
simulate navigational abilities on top of relational databases, but they are
all kludges which are performed by various forms of fakery to impose
navigability on top of a model which inherently is not navigable.
For example, many database drivers can kludge you up what is called a "keyset"
driven cursor. It does this by executing the query and storing a temporary
table containing the primary keys of each table in the query for each result
row (this is stored either in the driver (for a client-driven keyset) or on the
server (for a server driven keyset). When you ask for a row from the keyset,
the primary keys are used to issue a bunch of queries to "reconstruct" the
"present view" of the result that would be at that navigational location for
you. There are also, usually in these same drivers, what are called
"scrollable" cursors. These differ from a "keyset" cursor in that the query
result set is stored in a temporary table (rather than a keyset table). The
driver can then "pretend" there is ordering and record numbers on the results
and can internally reissue queries against the temporary table and its row
numbers so that it appears you can scroll forwards and backwards and access rand
om rows of the result set (this type of cursor is almost always implemented on
the server as a temp table and the only information sent to the client are the
extents of the rowset). The key difference is that keyset cursors can be used
to update the database (since you have the primary keys for the original data
rows stored away) whereas plain scrollable cursors are read only.
In some systems these types of capabilities exist solely in the drivers. In
others, there is inherent support in the database engine itself. There are
even cases where there is a combination of both, or where you can select
whether the support should be implemented server-side or client-side. In some
cases the choice of implementation method is taken away from you in order to
protect you from doing something "abysmal", such as retrieving the primary keys
for a billion row keyset into driver storage.)
This is a kludge to give the appearance of navigational capabilities where they
inherently do not exist.
The other way to do it is the way primitive folks do it -- retrieve the entire
result set into a big list of records in your programs storage, and then
navigate through your list in memory. This is popular with Microsoft tools,
for example, and is why most Microsoft tools take aeons to open (try to open
the event viewer on a busy Microsoft server, or open DSA against a domain with
a several hundred thousand objects. You come in to work and sign on, then open
the tool and lock the screen, then go for breakfast, and meetings, and lunch.
When you return after lunch the tool is ready to use. Or it has crashed
because it ran out of memory.)
It is also quite common for "bitty system" developers to do this. Everything
works swimmingly well on their test database with 5 customers and 3 products.
However, once the "production" database is loaded that contains a few thousand
customers and several millions of products and components, the system craps out
or is abysmally slow. You then read stories in the newspaper about how some
company (or government) spent hundreds of millions or billions of dollars on a
failed computer system.
There are still other products which do not provide drivers which kludge up any
of these illusions for you, and the database engine does not have the baked in
complication to assist with the creation of these illusions. For these systems
you have to do all the skull-duggery yourself. SQLite falls into this
category. I don't think anyone was written a "driver" which implements this in
automated fashion either, so you have to "roll your own" as it were.
>I'm racking my brain trying to figure out how to get directly to the last
>item in a (potentially) sorted or ordered table. At least oe of the
>tables will be ordered by a name and a date, so uising the rtowid won't work.
Read all the results until you run out of results. At this point the last
result you successfully retrieved was the last. If this takes too long then,
>Also, how to traverse a table or cursor in a reverse direction.
Issue the same query again, and "reverse" the sort order of each column in the
group by clause. You will now retrieve the result set in the opposite order
and the "first" row will be the "last" and the "last" shall be "first". If you
wish to start "in the middle" devise some constraints to add (to the where
clause) which determine the appropriate slicing of the result set.
>Going from first to last is easy in pysqlite, just use fetchone().
>Geting directly to the first record is also easy, just open the
>desired
