Read only scrollable snapshot cursors can be easily implemented in the same way
as they are implemented in other database engines. You just have to do the
work yourself. Example:
cn = sqlite3.connection(...)
cr = cr.cursor()
# create the scrollable cursor
cr.execute('create temporary table myscrollablecursor as select [columns] from
[various tables] where [join conditions] order by [columns];')
maxrow = cr.execute('select max(rowid) from myscrollablecursor;').fetchone()[0]
# to retrieve the data forward from row do
cr.execute('select rowid, * from myscrollablecursor where rowid >= ? order by
rowid;', (row,))
# to retrieve rows backward from row do
cr.execute('select rowid, * from myscrollablecursor where rowid <= ? order by
rowid desc;', (row,))
# to close the scrollable cursor do
cr.execute('drop temporary table myscrollablecursor;')
You retrieve the data from the cursor as you now do (cr.fetchone, cr.fetchmany,
iterator, whatever). item 0 of the result tuple is the current row number.
You can even have random access to the rows based on the row number if you so
desire. If you need multiple scrollable cursors, at the same time you just
need to have different cursor() objects and temporary table names for each.
These are what other drivers might term a SERVER-SIDE SCROLLABLE SNAPSHOT. If
you wish to see updates you need to drop and re-create the snapshot. Be aware
that while you have the cursor() open and are navigating about, the database is
locked from updates.
You can do things in chunks if you want to by using queries of the form:
rows = cr.execute('select rowid, * from myscrollablecursor where rowid >= ?
limit ? order by rowid;', (page * pagesize, pagesize)).fetchall()
rows = cr.execute('select rowid, * from myscrollablecursor where rowid <= ?
limit ? order by rowid desc'', ((page+1)*pagesize-1, pagesize)).fetchall()
where maxpage = int((maxrows + pagesize - 1) / pagesize)
which will retrieve you pagesize chunks to play with and reset the cursor
between page retrievals (thus unlocking the database). Your last row is, of
course, rows[-1] when reading forward the page = maxpage.
Doing a keyset cursor covering multiple tables is more complicated because you
need to either hand craft it for each keyset query or do significant work to
parse the database schema to store and retrieve the desired information in
order to generalize it. Even for a keyset driven cursor, only the data will
see committed updates. The keyset is a snapshot and new rows will not appear
until you regenerate the keyset snapshot, and you have to be prepared to deal
with rows that have been deleted from the database but still show up in the
keyset (but cannot be retrieved -- same if the primary key of a row in a table
referenced in the snapshot is updated which will make it look as if the result
was deleted). That is why keyset cursors usually have all the complicated bits
implemented within the database engine itself.
-----Original Message-----
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 table and the first fetch is the first record.
No, the first fetch returns the first row of the result set. If and only if
you issued a query with an order by clause giving a specific fully determined
ordering to the results is ordering guaranteed. It is the order by clause
which determines the order. If you do not have an order by clause in the
query, then the row that you are calling the "first" row is only that by
happenstance and the gyrational flux of subatomic particles at the time of the
observation. Be aware, however, that like Schroedingers Cat, your observation
of the subatomic gyrations will fix the duality in a random fashion that may be
different for each observation.
>But, if I'm, for example, at the 10th record of an ordered table
>(cursor), how do I go backward one or more times?
Issue an appropriate query to generate a result set containing the set of
result rows that you want in the order that you desire, and retrieve them.
>And how do I go directly to the last?
Re-issue the query with the order by clause inverted (change ASC to DESC and
DESC to ASC for every item in the order by clause). Now when you retrieve the
"first" row you will get what was the "last" when ordered in the opposite
direction, and the "last" will now be "first".
Set theory, relational algebra, and the higher mathematics on which relational
databases are built is both extremely complex and amazingly simple all at the
same time. The first thing you have to do is realize that there is no such
thing as "order". If you want "order", you must ask for it explicitly.
Relying on happenstance is not a substitute and is unwise. Also, the
row-by-row access wherein you think you are "stepping through" a result set is
an illusion which is created by the technological limitations resulting from
adapting relational and set operations to the unfortunately linear basis of
human procedural implementation of the technology.
See: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for simple examples.
There are certainly lots of others.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
