On 09/09/2010 02:18 AM, Chris Withers wrote: > On 08/09/2010 19:23, Gunnlaugur Briem wrote: >> http://communities.bmc.com/communities/docs/DOC-9902 > > Thanks for all of the references, but this one in particular. > Materialized paths looks like its the closest to what I'm after. > > However, with materialized paths, I'm wondering with a structure like: > > / > /a/ > /a/b > /a/b/1 > /a/b/2 > /a/c > /b/ > /b/1 > /b/2 > > How to phrases the sql to answer the question: > > "Does the current user have access to anything in /a or below" > > ...particularly where the user is granted access only to /a/b/1, for > example.
It's hard to give specifics without knowing what kind of access control you are using (e.g. separate read/write permissions, inherited permissions, full ACLs), but here is one attempt: SELECT EXISTS (SELECT 1 FROM access_control WHERE (path = '/a' OR path LIKE '/a/%') AND user = :user AND permission = :permission) Most materialized path queries use LIKE a lot. As long as you keep the % character at the end, a good DB will be able to use an index to speed up the query. -Conor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
