Thanks Keith, We have taken the liberty to forward your answer to our whole dev team, your insights on the subject are useful to all of us.
Cheers Pavlos On 18 September 2014 07:14, 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 -- Pavlos Christoforou Point Nine Financial Technologies Ltd Mobile: +44 (0)20 7193 5843 pavlos.christofo...@p9ft.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
