> On Aug 28, 2015, at 9:22 PM, Mike Orr <[email protected]> wrote: > > On Fri, Aug 28, 2015 at 3:01 AM, Petr Blahos <[email protected]> wrote: > On Fri, Aug 28, 2015 at 9:54 AM, Mehdi <[email protected]> wrote: >>> Consider this url: >>> http://mysite.com/customers/137/orders?year=2015&month=5 >>> Now in pyramid traversal mechanism, how many queries will be run against >>> database? >>> I guess it would be three: >>> 1. /customers : select * from customers >>> 2. /customers/137 : select * from customers where >>> id = 137 >>> 3. /customers/137/orders?year=2015&month=5: select * from orders where >>> customer_id = 137 and year=:year and month=:month > >> traversing your resource tree does not necessarily mean running any queries. >> In >> your example, it wouldn't have to run any queries at all. >> Basically, it is there to bring you to the view and possibly gather some >> information >> on the way. > > That's one thing I've been going back and forth about. Should a > resource tree run SQL queries for individual records implied by the > URL? In my case I have a three-level nested structure: > /{incident}/{post}/{attachment} . Each level has a numeric ID and is > 1:many to the level on its right. I would not do the collection > queries ("/customers" in Mehdi's example), only the individual-record > queries. The collection queries would either be in the view or in a > resource method. > > The advantage of doing individual-record queries ("/customers/137") is > that I can properly raise KeyError if the record doesn't exist. It > feels wrong to raise HTTPNotFound in the view because that's not fully > using the traversal feature. Fetching the record also allows setting > ACLs based on record columns, because I do have a few cases with > record-specific permissions. But running queries during traversal has > several disadvantages: > > 1) You can't call 'request.resource_path()' without triggering > queries. In my collection view I show a list of links to child pages, > and there's also some peer and ancestor links in the site template and > breadcrumbs. But the main problem is the child links. I don't want to > trigger twenty useless queries for a pageful of links. I tried the > extra positional parameters: 'request.resource_path(context, "137")' > but that returns the URL without a trailing slash, so it's clearly > intended for view names and the like rather than children. > > 2) Navigating to a grandchild page triggers queries for the ancestor > objects. I usually need a few properties from them anyway, to create > backlinks and show a bit of their information, but by default it > fetches all ancestor columns. I've played around with SQLAlchemy > deferred to fetch the minimal columns needed for backlinks and index > entries, but I end up requiring slightly different columns in > different places so it ends up being rather complex and cumbersome. > > 3) 'request.resource_path' is not as fiexible as 'request.route_url', > because the latter can easily generate all your internal links both > near and far if you know the route name. But 'request.resource_path' > requires starting from the current context or root, or iterating back > through the ancestors (for breadcrumbs). It seems like a bunch of > unnecessary overhead. And now I have to pass various AJAX URLs to the > template for the front-end developer, so there's even more URLs to > generate. > > I finally went back to URL Dispatch because of these. This application > doesn't "need" traversal because the URL structure is fixed. And I > found a way around the record-specific-permissions issue. The > permitted record IDs are most closely related to the user object, so > rather than fetching the record and finding out who can view and edit > it, I ask the user object which record IDs it can view and edit, and I > build the ACLs from there without fetching the records. That works for > the first level. It doesn't fully work for the second-to-third level > because that depends on a second-level record property. I haven't > decided how to implement that part yet. I may just keep a cached set > of restricted record IDs in global memory or in Redis, and tweak the > ACLs based on whether the current ID is in the restricted set. > > I'm also not fully sure how to structure record-specific ACLs and > permissions. For instance, most users can view all incidents, but a > few can only view certain ones, and others can only write certain > ones. Should I do it with a principal like "incident.123.view" and > simple permissions ("incident.view", "incident.edit"). Or should I do > it with complex permissions ("incident.123.view"). But then I run into > a problem that permissions can't be OR: I can't configure a view to > allow "incident.view" OR "incident.123.view", which is what I really > want. So should I do the extra permission-checking in the view? But > that seems wrong. > > Also, should I define object-specific permissions ("incident.view"), > or should I double up on simple permissions ("view") and trust that > the ACL will set the right value, even though the same user can view > one object type but not another. In short, where and how should I > specify these record-specific principles-or-permissions? > > I am still planning to use traversal in my content-in-a-directory > site. In that case it will have different object types at arbitrary > URLs, so it's a natural for traversal, and it doesn't have complex > ACLs.
Very interesting conversation, thanks Mike for starting it up. A couple of months ago I was talking again with Shane Hathaway about my endless interest in the ZODB experience, outside of the ZODB, even if it means restricted to PostgreSQL 9.4+. He gave me a recursive CTE that would do “traversal” in one query. It pointed to all kinds of cool ideas related to permission filtering as part of a SQLAlchemy query chain. —Paul -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
