Thx so much for your (quick) replies. It really helps to have a conversation sometimes.
On Jul 20, 12:40 pm, Yarko Tymciurak <[email protected]> wrote: > On Mon, Jul 20, 2009 at 2:27 PM, rb <[email protected]> wrote: > > > No, what is an "ordering" table? > > YOU create a db table where YOU impose your order, e.g. > > db.define_table('mystuf', db.Field('name'), db.Field('comment')) > db.define_table('myorder_mystuff', db.Field( 'my_sequence', integer #? or > whatever), db.Field( 'mystuff_id', db.mustuff)) > > ... That sort of thing. an order translation table. Now when you need the > next 20 items, you get the list of id's of mystuff you want, and build your > select for the next page from that. Oh... use a table to map my primary key to the table's id. I don't think that would work, unfortunately. Page 139 of the web2py manual says: " Notice that you should not declare a field called "id" because one is created by web2py anyway. Every table has a field called "id" by default. It is an auto-increment integer field (starting at 1) used for cross-reference and for making every record unique, so "id" is a primary key." Sigh. *Every* table's primary key is this insertion-ordered 'id' field. > > > The DAL assumes an id field as the > > primary key in all tables. Thus the internal ordering is always by > > this id field value. The natural ordering would then be by insertion > > The assumption you're making here is that either the DB will return in key > order (I don't know this is true for all db's), or the DAL will create a > table for you in id-order (don't think it does anything specfic). As noted above, the DAL *does* create tables in id-order. The id field *is* the primary key. The record order can be assumed to be a *random* order of records, unless it is specified by the 'orderby' argument. Now, the difficulty here is that row selection is performed by the argument to the db() SQLSet creator function ('db' here is a placeholder for the name of your db returned by the SQLDB function). So the records are selected by: 1>>> mySet = db (db.myTbl.field== XX) function. At this point, however, no records have been *collected* into a resultset. The collection occurs when the 'select' function is called on the SQLSet above: 2>>> rows = mySet.select (list-of-columns-wanted, orderby=YY) That is, the argument to the SQLSet creator function in (1) above gives the WHERE clause of the SQL SELECT. The argument (of list-of- columns) in (2) above gives the column selection clause of the SQL SELECT statement. However, it is also here that the 'orderby' is specified. Thus I cannot order my resultset until I *collect* it. However this is not a problem if your key is a single column value. One can just specify the record comparison (ie, records with key > X or key < Y) in (1) above and then use an 'orderby' phrase in the (2) statement. My app is a thick (python) client which is remote-controlling these db queries via xmlrpc and many of my keys are multiple-column keys. Specifying how to select records which have key segment values greater than the current, while specifying the key segment order, while specifying the 'orderby' statement was giving me grief. I have to build up a string of python code to represent the arguments in the row selection and column selection and then send it over to the controller where I stick it into an 'eval' statement. Eventually I realized that I need to craft a multi-column comparison generic function in the controller and then call it (by making a string of its call which will get eval'd) when doing the row selection. Gosh, I hope this works. I'm just touching the DAL for the very first time. In fact, I've just discovered web2py. I really hope that the developers keep its unique strength of >>>SIMPLICITY<<< as that is WHY I chose it. If I wanted powerful but !...@#$ing complicated, there are other more well established choices. I really despise SQL, it is such a pigpen of an inconsistent language. I don't know enough about the DAL to know if it is any kind of improvement over raw SQL but the DAL does offer a way for the app to be db agnostic. I'm starting with SQLite but may move to postgres. I'm hoping that the DAL will save me from having to recode for postres. But it also means that I must write to the least powerful db - and that is SQLite. The DAL does not, at this time, support creating INDICES (at least I can do this outside of the DAL). It doesn't support multi-column table constraints. Maybe these kinds of things can be a part of web2py's evolution. > > order (later insertions would have a higher id value than earlier > > insertions). After some deletions and insertions even this ordering > > would get completely out of wack. Thus don't I need to provide the > > orderby argument in each and every select statement? > > Again, you're making assumptions which I don't think are necessarily true. > T-SQL, for example, will not re-use a deleted primary key; the keys just > keep incrementing, and the deleted one is just a "hole" in the list. The problem is, to be db agnostic I *have* to assume the worst. I wonder if this don't-fill-in-the-id-holes holds true for SQLite and postgres. --- Rb --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
