Re: [sqlalchemy] Re: hierarchical data storage and searching
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. and here for links to more than you really want to know on the subject: http://troels.arvin.dk/db/rdbms/links/#hierarchical Hah, excellent, thanks :-) SQLAlchemy support any of these approaches well enough that I don't think you need to factor SQLAlchemy into your choice of relational design at all. Check out /examples/nested_sets/ and /examples/ adjacency_list/ (in the SQLAlchemy distribution) for what are probably the two most common approaches. Cool, I'd forgotten about them, although the nested sets example doesn't appear to show the delete or modify implementations, which are the tricky bits ;-) cheers, Chris PS: I appreciate that while I need to get any solution working with SQLAlchemy, this is a generic relational database question; if anyone can point me at a more correct forum to ask, I'll be happy to do so there :-) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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.
Re: [sqlalchemy] Re: hierarchical data storage and searching
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.
Re: [sqlalchemy] Re: hierarchical data storage and searching
Conor wrote: 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. Ah, of course, many thanks! :-) Chris -- 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.
[sqlalchemy] Re: hierarchical data storage and searching
Hi Chris, this is more of a relational design question than SQLAlchemy-related, but take a look at this for an at-a-glance summary of different approaches and their pros and cons: http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/ ... here for some illustration and examples: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://communities.bmc.com/communities/docs/DOC-9902 and here for links to more than you really want to know on the subject: http://troels.arvin.dk/db/rdbms/links/#hierarchical SQLAlchemy support any of these approaches well enough that I don't think you need to factor SQLAlchemy into your choice of relational design at all. Check out /examples/nested_sets/ and /examples/ adjacency_list/ (in the SQLAlchemy distribution) for what are probably the two most common approaches. Regards, - Gulli On Sep 8, 8:22 am, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I'm trying to solve a hierarchical access control problem, both on the storage and querying side. So, say I have a tree of content: / /a/ /a/1 /a/2 /b/ /b/1 /b/2 I want to be able to express and search on the following types of requirements: User X should be able to access all content in /a and content in /b/1 The storage side just needs to be usable, speed wise, but obviously the query side needs to be lighting fast as it'll be hit *hard* and often. What schema/indexes would people recommend for this? How would I query that schema fast in SQLAlchemy to be able to answer the above type of questions. I guess the API that needs to be fast would be along the lines of: def can_access(user_id,path): return True or False The grant python API would look like: def grant_access(user_id,*paths): ... Any help much appreciated! cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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.
