And _excellent_ reply. I'm grabbing this email to put as a "gist" on my github account. From my own experience, programmers who come from a "non-relational" background, the world view of how to _properly_ do SQL is difficult.
On Thu, Sep 18, 2014 at 12:14 AM, Keith Medcalf <kmedc...@dessus.com> 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 ra nd > 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 -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
