[sqlalchemy] Re: Automatically filtering all queries
On 26 май, 20:50, Michael Bayer mike...@zzzcomputing.com wrote: However, its quite easy to achieve. Just use this. class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Automatically filtering all queries
On May 27, 2009, at 4:25 AM, Denis S. Otkidach wrote: On 26 май, 20:50, Michael Bayer mike...@zzzcomputing.com wrote: However, its quite easy to achieve. Just use this. class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) you'd have to hack .count() as well in a similar fashion. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Automatically filtering all queries
On 27 май, 18:22, Michael Bayer mike...@zzzcomputing.com wrote: On May 27, 2009, at 4:25 AM, Denis S. Otkidach wrote: class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) you'd have to hack .count() as well in a similar fashion. Probably I have to hack something to insure proper subqueries construction. I believe this is wrong way. Hacked .get() and .from_statement() method guarantee that if missed something I'll get an exception, while in this case I have to come over hard to detect bug first (and even seeing the bug doesn't point me to yet another method I have to fix). I'll try to use my query with get() and from_statement() hacked and fallback to filtering everything manually if it won't work. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Automatically filtering all queries
On May 27, 2009, at 12:20 PM, Denis S. Otkidach wrote: Probably I have to hack something to insure proper subqueries construction. uh yeah if subqueries are happening, that makes things more complicated too. But the other DB tools you're comparing us against probably have a lot less subquery capability than we do. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Automatically filtering all queries
On 26 май, 18:24, Michael Bayer mike...@zzzcomputing.com wrote: Denis S. Otkidach wrote: such ability in SQLAlchemy. There is a suggestion ( http://groups.google.com/group/sqlalchemy/browse_thread/thread/bcd10e... ) to provide custom query_cls. This probably worked a year ago, but doesn't work now. that should absolutely work now. doesn't work is never a very useful explanation of a problem. Sure, my report is not verbose enough. Here is a part of traceback when my QueryPublic is used: [... here is code equivalent to: for link in doc.links: link.ref_doc.events_sections[0].slug # - failes here ...] File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 158, in __get__ return self.impl.get(instance_state(instance), instance_dict (instance)) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 374, in get value = callable_() File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/strategies.py, line 559, in __call__ return q.get(ident) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 442, in get return self._get(key, ident) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 1396, in _get q.__no_criterion_condition(get) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 271, in __no_criterion_condition raise sa_exc.InvalidRequestError(Query.%s() being called on a Query with existing criterion. % meth) InvalidRequestError: Query.get() being called on a Query with existing criterion. And when I redefine get() it comes a bit farther: [... here is code equivalent to: for link in doc.links: link.ref_doc.events_sections[0].slug link.ref_doc.short_title # - failes here ...] File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 158, in __get__ return self.impl.get(instance_state(instance), instance_dict (instance)) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 374, in get value = callable_() File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/state.py, line 171, in __call__ attr.impl.key in unmodified File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/mapper.py, line 1834, in _load_scalar_attributes result = session.query(mapper).from_statement(statement)._get (None, only_load_props=attribute_names, refresh_state=state) File string, line 1, in lambda File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 51, in generate assertion(self, fn.func_name) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 281, in __no_clauseelement_condition self.__no_criterion_condition(meth) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 271, in __no_criterion_condition raise sa_exc.InvalidRequestError(Query.%s() being called on a Query with existing criterion. % meth) InvalidRequestError: Query.from_statement() being called on a Query with existing criterion. Condition is: class Doc([...base declarative model...]): [...] state = Column(Integer, nullable=False, default=0, index=True) [...] PUBLISHED = 4 public_condition = (state==PUBLISHED) Here doc (retrieved from session by id) and doc_ref (via 2 relations) are instances of Doc class. Other models here don't have public_condition yet. Here is query class I pass as query_cls argument to sessionmaker: def QueryPublic(entities, session=None): query = Query(entities, session) # XXX I haven't ever seen examples with several entities, so I can test # this case. assert len(entities)==1, entities # XXX Sometimes it's not a model class or mapper, so the following fails. cls = _class_to_mapper(entities[0]).class_ public_condition = getattr(cls, 'public_condition', None) if public_condition is not None: query = query.filter(public_condition) return query This works for simple queries, but any access to relation gives an about Query.get() used for query with condition (this wasn't a case for 0.4.x). get() should never be called when there's any filtering applied. its a nonsensical operation so 0.5 likes to tell you about it. I don't call it explicitly, but it is called internally when I access property defined with relation(). OK, i've redefined get() method to be less restrictive in cost of performance: class HackedQuery(Query): def get(self, ident): # XXX This works for case when primary key is constructed of id field # only. if isinstance(ident, (tuple, list)): assert len(ident)==1 ident = ident[0] return self.filter_by(id=ident).first() This helped for some
[sqlalchemy] Re: Automatically filtering all queries
Denis S. Otkidach wrote:
Do you mean I have to create separate model definitions for public and
private parts of site? It doesn't seem sane to me. I've used such
filtering across all tables for over 10 years with other DB tools, and
it saved me a lot of work. Why not using it with SA?
I've never heard of such a feature in a tool like Hibernate, for example.
However, its quite easy to achieve. Just use this.
class LimitingQuery(Query):
def get(self, ident):
return Query.get(self.populate_existing(), ident)
def __iter__(self):
return Query.__iter__(self.private())
@_generative()
def private(self):
crit = (self._entities[0].mapper.class_.public == True)
if self._criterion:
self._criterion = crit
else:
self._criterion = crit
full test case attached.
0.5.5 will have a new feature query.disable_assertions(*names) such that
you can stick to non-underscore names without any errors raised by
limit/one/etc.:
class LimitingQuery(Query):
def get(self, ident):
return Query.get(self.populate_existing(), ident)
def __iter__(self):
return
Query.__iter__(self.disable_assertions('limit_offset').filter_by(public=True))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
test.py
Description: Binary data
