Mapping temporal data for easy querying using time dimension
Adam Dziendziel
I am trying to implement versioning of a structure of interconnected
objects so that I can see how the content looked at some point of
time.
Probably I would need to use a temporal database with effective_from/
effective_to timestamps telling the time period when the information
held in the record was true: http://martinfowler.com/ap2/timeNarrative.html
If I had a flat object structure, I would simply pass a filter on the
query:
session.query(Object).filter(Object.effective_from >= time & time <
Object.effective_to)
However, in my schema, the Object has related entities which is also
temporal data. The mapped relations ideally should have the 'time'
parameter embedded in the ON part of JOIN clause.
I know that I can declare all relations as lazy='dynamic' and always
pass 'time'. But I would then lost the possibility to eagerly load
data. It would be inefficient to retrieve a record set and for each
record issue another queries to get values of temporal properties
( http://martinfowler.com/eaaDev/TemporalProperty.html )
What I am looking for is a parameter, which value is specified once
during a query, that could be used in mapper declarations. Attribute
loaders would then use that parameter automatically. Is this possible
in SQLAlchemy?
Regards,
Adam
Michael Bayer
this answer to this required enough creativity and testing that I made it into a usage recipe. Please give it a road test at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter and let me know if its effective.
Adam Dziendziel
> On Dec 27, 2009, at 12:01 PM, Adam Dziendziel wrote:
>
It is almost ideal. Everything seems to work except the case when I
use query.get().
This works:
session.query(m.Course).options(timepoint(datetime(2000,10,11)),
eagerload('v')).filter_by(id=1).one()
But that doesn't:
session.query(m.Course).options(timepoint(datetime(2000,10,11)),
eagerload('v')).get(1)
'timepoint' is my TemporalOption and 'v' holds a version with
effectivity timestamps.
When I turn on SQL logging I see 'None' in place of expected parameter
value.
Michael Bayer
query.get() doesn't work with filtering criterion. This because it looks
up in the current session by primary key, and if present issues no SQL.
If it were filtered, you'd get different results based on whether or not
the object were already loaded or not. It actually should be raising an
error as get() should be asserting that no existing filter criterion is
set up.
Adam Dziendziel
>
> query.get() doesn't work with filtering criterion. This because it looks
> up in the current session by primary key, and if present issues no SQL.
> If it were filtered, you'd get different results based on whether or not
> the object were already loaded or not. It actually should be raising an
> error as get() should be asserting that no existing filter criterion is
> set up.
Have you considered making get(*primary_key_as_list) a synonym for
filter_by(**primary_key_as_dict)? Then if a filtering criterion
contains only a primary key, do a look up in the session.
Michael Bayer
A query in the general can be against any number of individual columns,
mapped entities, or aggregate functions. It can be selecting from many
tables at once, as well as from other Query objects as subqueries.
Trying to guess if filter_by() happens to be selecting exactly a single
row primary key from all of that seems complicated. It would also be a
surprise that it very occasionally pulls from cache, but in most cases not
- all based on the particular structure of a query.
If filter_by() did do that, in this specific case it still would not pull
from cache since the criterion would be against more than just the primary
key columns. I'd say the direction here is to not use get() if you would
like any filtering to be applied, including from this new option.
If you're looking for general caching of results, that's a different
issue. There are some recipes for that in examples/.
>
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
Adam Dziendziel
>
> A query in the general can be against any number of individual columns,
> mapped entities, or aggregate functions. It can be selecting from many
> tables at once, as well as from other Query objects as subqueries.
> Trying to guess if filter_by() happens to be selecting exactly a single
> row primary key from all of that seems complicated. It would also be a
> surprise that it very occasionally pulls from cache, but in most cases not
> - all based on the particular structure of a query.
>
> If filter_by() did do that, in this specific case it still would not pull
> from cache since the criterion would be against more than just the primary
> key columns. I'd say the direction here is to not use get() if you would
> like any filtering to be applied, including from this new option.
I understand.
Thank you very much for the elaborate answer and the recipe. That was
exactly what I was looking for. Thanks!
Best regards,
Adam