Kieth, Thanks for the reply. I should have asked sooner and would have wasted less time trying to do something sqlite wasn't meant for. I will however look up rolling cursors. That may help a little. Again, thanks.
Mark On Wednesday, September 17, 2014 11:14:36 PM Keith Medcalf wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
